Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle:

 
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 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 pubs 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: 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 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.


 

     

Remote DBA Service
 

Oracle Tuning Book

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright ฉ 1996 -  2013 by Burleson. All rights reserved.

Oracleฎ is the registered trademark of Oracle Corporation.