Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Examine Different Optimizer Modes

After reading tutorial 2, and the reading assignments, you are now ready to prepare a hands-on experiment in the use of SQL optimizations on your own Oracle database.  This assignment will allow you to see the differences in execution plan's based upon your current optimizer mode.

This exercise is conducted in several steps:

Step 1 – Create a plan table - From exercise 3-1, make sure that you have a plan table within your Oracle database to store in the execution plans.  This is done by executing the utlxplan.sql utility in the c:\oracle\ora81\rdbms\admin directory.

SQL > @c:\oracle\ora81\rdbms\admin\utlxplan

Table created.

Step 2 – Analyze your schema - Once you have a plan table and place, you should then be able to view the execution plan for the following query against the sample database. 

SQL > exec dbms_utility.analyze_schema('PUBS', 'COMPUTE');

PL/SQL Procedure Successfully Completed.

Step 3 - Save a query – In this step, you copy a sample query onto your c:\Burleson directory and save it as book_sales.sql.  Here is the script to copy:

set pages 999;

column c0 heading 'Publisher|Name' format a20
column c1 heading 'Book|Title'     format a30   
column c2 heading 'Total|Sales'    format $9,999,999.99

break on c0 skip 1

compute sum of c2 on c0

select
  pub_name                          c0,
  book_title                        c1,
  sum(quantity)*book_retail_price   c2
from
  sales            s,
  book             b,
  publisher        p
where
  b.book_key = s.book_key
and
  p.pub_key = b.pub_key
group by
   pub_name,
   book_title,
   book_retail_price
order by
   pub_name,
   book_title
;

Step 4 – Run a test script – Your assignment is to change the overall optimizer mode for your Oracle database.  As we recall, the optimizer mode can be changed at the system level, session level or query level. We will now change the optimizer mode for our session with these commands:

alter session set optimizer_goal=rule;
alter session set optimizer_goal=first_rows;

Create a script called test_modes.sql that contains the following statements.  Note that we are changing the optimizer goal to rule, running the query, and then change the optimizer goal to first_rows and re-run the query.

spool t.lst

set autotrace traceonly explain

set timing on

set echo on

alter session set optimizer_goal = rule;

@book_sales

alter session set optimizer_goal = first_rows;

@book_sales

spool off;

host notepad t.lst

Step 5 – Run the script and view the execution plans – The final step is to run the test_modes.sql script and e-mail the results to your instructor. 

Your deliverables will be:

1 - Submit the execution plans of the test_modes script to your instructor.

2 – Note the execution times for each query.

3 – Speculate about why the first_rows optimization did not have the same execution plan as the rule-based execution plan.

4 – Was there a significant difference in the execution times for the query?  Speculate about the differences (or lack thereof) in the execution times.

ANSWER

The first_rows mode knows that the tables are tiny and that a full-table scan is the most efficient method for servicing the query.  Hence, the first_rows mode will invoke full-table scans.  The rule-based query is instructed to use an index if one exists, but since no indexes exist, we see a sort merge join when we run the query in rule mode.

The execution times for both queries are very fast, and there is no significant difference in execution times.  This is because the tables are tiny and because the data blocks are cached inside the RAM data buffer.

SQL> alter session set optimizer_goal = rule;

Session altered.

SQL>
SQL> @book_sales
SQL> set pages 999;
SQL>
SQL> column c0 heading 'Publisher|Name' format a20
SQL> column c1 heading 'Book|Title'     format a30
SQL> column c2 heading 'Total|Sales'    format $9,999,999.99
SQL>
SQL> break on c0 skip 1
SQL>
SQL> compute sum of c2 on c0
SQL>
SQL> select
  2    pub_name                        c0,
  3    book_title                      c1,
  4    sum(quantity)*book_retail_price c2
  5  from
  6    sales     s,
  7    book      b,
  8    publisher p
  9  where
 10    b.book_key = s.book_key
 11  and
 12    p.pub_key = b.pub_key
 13  group by
 14  pub_name,
 15  book_title,
 16  book_retail_price
 17  order by
 18  pub_name,
 19  book_title
 20  ;

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=RULE                                      
   1    0   SORT (GROUP BY)                                                     
   2    1     MERGE JOIN                                                       
   3    2       SORT (JOIN)                                                    
   4    3         MERGE JOIN                                                    
   5    4           SORT (JOIN)                                                
   6    5             TABLE ACCESS (FULL) OF 'PUBLISHER'                       
   7    4           SORT (JOIN)                                                
   8    7             TABLE ACCESS (FULL) OF 'BOOK'                            
   9    2       SORT (JOIN)   
                                                
  10    9         TABLE ACCESS (FULL) OF 'SALES'                               

SQL>
SQL> alter session set optimizer_goal = first_rows;

Session altered.

SQL>
SQL> @book_sales
SQL> set pages 999;
SQL>
SQL> column c0 heading 'Publisher|Name' format a20
SQL> column c1 heading 'Book|Title'     format a30
SQL> column c2 heading 'Total|Sales'    format $9,999,999.99
SQL>
SQL> break on c0 skip 1
SQL>
SQL> compute sum of c2 on c0
SQL>
SQL> select
  2    pub_name                        c0,
  3    book_title                      c1,
  4    sum(quantity)*book_retail_price c2
  5  from
  6    sales     s,
  7    book      b,
  8    publisher p
  9  where
 10    b.book_key = s.book_key
 11  and
 12    p.pub_key = b.pub_key
 13  group by
 14  pub_name,
 15  book_title,
 16  book_retail_price
 17  order by
 18  pub_name,
 19  book_title
 20  ;

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=10 Card=100 Bytes=5600)
   1    0   SORT (GROUP BY) (Cost=10 Card=100 Bytes=5600)                      
   2    1     HASH JOIN (Cost=5 Card=100 Bytes=5600)                           
   3    2       HASH JOIN (Cost=3 Card=20 Bytes=980)                           
   4    3         TABLE ACCESS (FULL) OF 'PUBLISHER' (Cost=1 Card=10 Bytes=150)
   5    3         TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=680)
   6    2         TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=700)
 

 
For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright ฉ 1996 -  2014 by Burleson Inc. All rights reserved.

Oracleฎ is the registered trademark of Oracle Corporation.