----------------------------------------------------------------------
-- Hands on exercises                                         
--                                                            
-- 1. Use of autotrace                                        
-- 2. Tuning problems                                         
--
-- Instructions:
-- Please start with loading a tuneemp table (100k records, 
-- some 26MB of data). Use imp tool:
--
--   G:\Applic~1\Oracle\OraInst\v5\Rdbms9i\bin\IMP.EXE oradbXX/oracle@trainingdb FILE=G:\Applications\Oracle\Training\Workshop\Tuning\tuneemp.dmp TABLES=tuneemp
-- 
-- This exercise consists of two parts. First, read explanation on
-- AUTOTRACE, wchich follows. Second, look at three queries and
-- try to make them run faster. Solutions and explanations are
-- provided at the end of this text file.
-- 
-- AUTOTRACE is a sqlplus option which lets you easily see 
-- expalin plan of every sql satement you run and provides you with
-- meaningful statistics. To turn on AUTOTRACE, PLAN_TABLE needs to
-- exist in your schema. Normally, you need to be granted the PLUSTRACE
-- role, but this has been done for you.
--
-- To autotrace in your sqlplus session, type:
--
--   SET AUTOTRACE ON
--
-- From now on, sqlplus will show explain plan and statement statistics
-- after each statement you execute. For more options, see:
--
--   http://oradoc/ora9ir2/server.920/a90842/ch13.htm#1012209
-- 
-- The meaning of the statistics is explained at the end of this file.
--
-- At this moment, the import you initianted at the beginning, has
-- hopefully finished.
--
-- Please use the AUTOTRACE output to improve performance of the 
-- queries below (problems 1 to 3). 
--
-- Before you start, set up your environment.
----------------------------------------------------------------------

----------------------------------------------------------------------
-- Set up your environment 
----------------------------------------------------------------------

SET AUTOTRACE ON
SET TIMING ON
SET LINESIZE 150

----------------------------------------------------------------------
-- Problem 1: Who earns the most in each department?                  
-- Try to make it faster!
-- See hint 1 (at the end of this file) to help you.
----------------------------------------------------------------------

select ename, sal, deptno
from tuneemp e
where sal = (
  select max(sal) from tuneemp e2 where e2.deptno = e.deptno
);

----------------------------------------------------------------------
-- Problem 2: How many people are there in each department?          
-- Try to make it faster!
-- See hint 2 (at the end of this file) to help you.
----------------------------------------------------------------------

select dname, (
  select count(*) from tuneemp e where e.deptno = d.deptno
) count
from dept d;

----------------------------------------------------------------------
-- Problem 3: How many employees earn more than their manager?           
-- Try to make it faster!
-- See hint 3 (at the end of this file) to help you.
----------------------------------------------------------------------

select count(*) 
from tuneemp e
inner join tuneemp m on e.mgr = m.empno
where e.sal>m.sal;

----------------------------------------------------------------------
-- Clean up - but not untill you solved the 3 problems ;-)
----------------------------------------------------------------------

drop table tuneemp;

----------------------------------------------------------------------
-- Solutions and explanations
--    
-- Hint 0 (general): You should make sure that table statistics 
-- are up to date:
--    
     BEGIN
       DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>USER
         ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
         ,block_sample=>TRUE
         ,method_opt=>'for all columns size AUTO'
         ,cascade=>TRUE
         ,granularity=>'ALL'); 
     END; 
--
--  Hint 1: Create an index on column sal:
-- 
     create index tuneemp_sal_ix on tuneemp(sal);
--
--  Hint 2: Get rid of the related subquery:
--    
      select d.dname, count(*) 
      from dept d inner join tuneemp e on e.deptno = d.deptno
      group by d.dname;
--
--  Hint 3: If your statistics are up to date, then this query should
--  perform better:
--  
      select count(*) 
      from tuneemp e where sal > (
        select sal from tuneemp m
        where m.empno = e.mgr
      );
--
--  Why using a related subquery should be better than a self join?
--  Well, retrieval of each row by primary key is very fast, and it 
--  has to be done anyway. it pays off to get rid of the nested loops.
--  Even enforsing the join using HASH is not of much help!
--
----------------------------------------------------------------------

----------------------------------------------------------------------
-- Meaning of AUTOTRACE statistics:
--
-- * Recursive calls: Number of SQL statemnts executed in order to 
--   execute your SQL statement. Might be bigger than 1 for example
--   if you use a function which runs some sql statemnts. However,
--   it is normally higher that 1 when you run any sql statement for
--   the first time, parsing of your statemnts calls for many 
--   additional sql statemnts to be berformed and those are counted in.
-- * DB block gets: Total number of blocks read from the buffer cache. 
-- * Consistent gets: Number of times a consistent read was requested 
--   for a block in the buffer cache. It includes read asides from 
--   UNDO (rollback segment). This represents logical I/O 
--   and is the most important statistic here.
-- * Physical reads: Number of physical reads from the datafiles into 
--   the buffer cache. This represents physical I/O.
-- * The total amount of redo generated in bytes during the execution of 
--   this statemnt.
-- * Sorts (memory): sorts done in the user's session memory (sort area)
-- * Sorts (disk) Sorts that use the disk (temporary tablespace) because 
--   the sort exceeded the user's sort area size.
--   
-- The other statistics are self-explanatory.
----------------------------------------------------------------------