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