Northern California Oracle Users Group

Home » Articles posted by vbarun

Author Archives: vbarun

SQL Mini-challenge entry: 1 buffer for 1st execution

by Vlado Barun

RESULTS

This solution has buffers=1 for the first execution, and buffers=0 for the second execution (using results cache as Timo Raitalaakso demonstrated previously).

SUMMARY

The solution uses the query rewrite feature to retrieve the rows from an index on a materialized view. Since the index has blevel=0 and leaf_blocks=1, the execution plans shows buffers=1.

DISCLAIMER

1. I do NOT consider this solution appropriate for PRODUCTION environments. It is too narrow and complex and thus expensive to maintain.
2. I work for Oracle Corporation (not that it matters for this, but thought I should mention it, just in case).

SOLUTION

HR> /********** Prerequisites *************
HR> 1. ASSM tablespace 16k or 32k blocksize i.e. create tablespace bb blocksize 32k;
HR> 2. buffer cache with the above blocksize i.e. alter system set DB_32K_CACHE_SIZE=100 scope=memory sid='MYSID';
HR> */
HR>
HR>
HR> set appinfo off
HR> set serverout off
HR>
HR> alter session set statistics_level=all;

Session altered.

HR>
HR> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

HR>
HR>
HR> create materialized view employee_job_history
  2  (
  3  	   current_job_title, last_name, first_name, past_job_title, city, location_id
  4  )
  5  enable query rewrite
  6  as
  7  select  ej.job_title as current_job_title, last_name, first_name, jh.job_title as past_job_title, city, l.location_Id as location_id
  8    from  locations	 l
  9  	   , departments d
 10  	   , (
 11  		    select department_id, job_title
 12  		      from jobs        j
 13  			 , job_history jh
 14  		     where jh.job_id = j.job_id
 15  	     ) jh -- past job titles by department
 16  	   , (
 17  		    select department_id, job_title, last_name, first_name
 18  		      from jobs      j
 19  			 , employees e
 20  		     where e.job_id = j.job_id
 21  	     ) ej -- current job title, employee name and department_id for each current employee
 22    where l.location_id   = d.location_id
 23  	 and d.department_id = jh.department_id (+)
 24  	 and d.department_id = ej.department_id (+)
 25  ;

Materialized view created.

HR>
HR>
HR> create index i1 on employee_job_history
  2  (
  3    current_job_title, past_job_title, last_name, first_name, location_id, city
  4  )
  5  tablespace bb
  6  ;

Index created.

HR>
HR> exec dbms_stats.gather_table_stats(user, 'EMPLOYEE_JOB_HISTORY', no_invalidate=>false);

PL/SQL procedure successfully completed.

HR>
HR> select blevel, leaf_blocks from dba_indexes where index_name='I1';

    BLEVEL LEAF_BLOCKS
---------- -----------
         0           1

HR>
HR> alter session set result_cache_mode = force;

Session altered.

HR>
HR> select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as location_id, city
  2    from  locations	 l
  3  	   , departments d
  4  	   , (
  5  		    select department_id, job_title
  6  		      from jobs        j
  7  			 , job_history jh
  8  		     where jh.job_id = j.job_id
  9  	     ) jh -- past job titles by department
 10  	   , (
 11  		    select department_id, job_title, last_name, first_name
 12  		      from jobs      j
 13  			 , employees e
 14  		     where e.job_id = j.job_id
 15  	     ) ej -- current job title, employee name and department_id for each current employee
 16    where l.location_id   = d.location_id
 17  	 and d.department_id = jh.department_id (+)
 18  	 and d.department_id = ej.department_id  (+)
 19  	 and (
 20  		   jh.job_title   = 'President' --    past president
 21  	       or (				-- or Steven Kind
 22  		       last_name  = 'King'
 23  		   and first_name = 'Steve'
 24  		  )
 25  	       or  ej.job_title   = 'President' -- or current president
 26  	     )
 27  ;

LOCATION_ID CITY
----------- ------------------------------
       1700 Seattle

HR>
HR> select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------
SQL_ID  6qckafghm6rf2, child number 2
-------------------------------------
select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as
location_id, city   from  locations   l       , departments d       , (
               select department_id, job_title                  from
jobs        j                     , job_history jh
where jh.job_id = j.job_id         ) jh -- past job titles by
department       , (                select department_id, job_title,
last_name, first_name                  from jobs      j
    , employees e                 where e.job_id = j.job_id         )
ej -- current job title, employee name and department_id for each
current employee   where l.location_id   = d.location_id     and
d.department_id = jh.department_id (+)     and d.department_id =
ej.department_id  (+)     and (               jh.job_title   =
'President' --    past president           or (
    -- or Steven Kind                   last_name  = 'King'
  and first_name = 'Steve'              )

Plan hash value: 3143307749

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |      1 |        |      1 |00:00:00.01 |       1 |       |       |          |
|   1 |  RESULT CACHE     | 90fk8p43hbyj77p9wy91s02f5k |      1 |        |      1 |00:00:00.01 |       1 |       |       |          |
|   2 |   HASH UNIQUE     |                            |      1 |     32 |      1 |00:00:00.01 |       1 |  1646K|  1646K|  461K (0)|
|*  3 |    INDEX FULL SCAN| I1                         |      1 |     32 |      2 |00:00:00.01 |       1 |  1025K|  1025K|          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("EMPLOYEE_JOB_HISTORY"."PAST_JOB_TITLE"='President' OR "EMPLOYEE_JOB_HISTORY"."CURRENT_JOB_TITLE"='President'
              OR ("EMPLOYEE_JOB_HISTORY"."LAST_NAME"='King' AND "EMPLOYEE_JOB_HISTORY"."FIRST_NAME"='Steve')))

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

40 rows selected.

HR>
HR> -- execute query one more time for result caching to kick in
HR> select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as location_id, city
  2    from  locations	 l
  3  	   , departments d
  4  	   , (
  5  		    select department_id, job_title
  6  		      from jobs        j
  7  			 , job_history jh
  8  		     where jh.job_id = j.job_id
  9  	     ) jh -- past job titles by department
 10  	   , (
 11  		    select department_id, job_title, last_name, first_name
 12  		      from jobs      j
 13  			 , employees e
 14  		     where e.job_id = j.job_id
 15  	     ) ej -- current job title, employee name and department_id for each current employee
 16    where l.location_id   = d.location_id
 17  	 and d.department_id = jh.department_id (+)
 18  	 and d.department_id = ej.department_id (+)
 19  	 and (
 20  		   jh.job_title   = 'President' --    past president
 21  	       or (				-- or Steven Kind
 22  		       last_name  = 'King'
 23  		   and first_name = 'Steve'
 24  		  )
 25  	       or  ej.job_title   = 'President' -- or current president
 26  	     )
 27  ;

LOCATION_ID CITY
----------- ------------------------------
       1700 Seattle

HR>
HR> select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------
SQL_ID  0u3wxbaa19msv, child number 0
-------------------------------------
select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as
location_id, city   from  locations   l       , departments d       , (
               select department_id, job_title                  from
jobs        j                     , job_history jh
where jh.job_id = j.job_id         ) jh -- past job titles by
department       , (                select department_id, job_title,
last_name, first_name                  from jobs      j
    , employees e                 where e.job_id = j.job_id         )
ej -- current job title, employee name and department_id for each
current employee   where l.location_id   = d.location_id     and
d.department_id = jh.department_id (+)     and d.department_id =
ej.department_id (+)     and (               jh.job_title   =
'President' --    past president           or (
    -- or Steven Kind                   last_name  = 'King'
  and first_name = 'Steve'              )

Plan hash value: 3143307749

------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |      1 |        |      1 |00:00:00.01 |
|   1 |  RESULT CACHE     | 90fk8p43hbyj77p9wy91s02f5k |      1 |        |      1 |00:00:00.01 |
|   2 |   HASH UNIQUE     |                            |      0 |     32 |      0 |00:00:00.01 |
|*  3 |    INDEX FULL SCAN| I1                         |      0 |     32 |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("EMPLOYEE_JOB_HISTORY"."PAST_JOB_TITLE"='President' OR
              "EMPLOYEE_JOB_HISTORY"."CURRENT_JOB_TITLE"='President' OR
              ("EMPLOYEE_JOB_HISTORY"."LAST_NAME"='King' AND
              "EMPLOYEE_JOB_HISTORY"."FIRST_NAME"='Steve')))

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

42 rows selected.

HR>
HR> spool off