Examine different optimizer modes
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.
exercise is conducted in several steps:
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
SQL > @c:\oracle\ora81\rdbms\admin\utlxplan
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
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
set pages 999;
column c0 heading 'Publisher|Name'
column c1 heading 'Book|Title'
column c2 heading 'Total|Sales'
break on c0 skip 1
compute sum of c2 on c0
b.book_key = s.book_key
p.pub_key = b.pub_key
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
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.
set autotrace traceonly explain
set timing on
set echo on
alter session set optimizer_goal = rule;
alter session set optimizer_goal =
host notepad t.lst
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.
deliverables will be:
1 - Submit
the execution plans of the test_modes script
to your instructor.
the execution times for each query.
Speculate about why the first_rows
optimization did not have the same execution
plan as the rule-based execution plan.
there a significant difference in the
execution times for the query? Speculate
about the differences (or lack thereof) in
the execution times.