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