 |
|
Oracle Concepts by Burleson
Consulting |
Viewing SQL Execution Plans
In this exercise you will learn how to view
execution plan details and view SQL execution statistics. In almost
all relational databases, a special table called plan_table is used to
store the execution plan for an SQL statement. To give a simple
example, the following command will create a plan_table, populate the
table with the execution plan for a query, and then display the
contents of the plan table:
SQL>
@c:\oracle\ora8i\rdbms\admin\utlxplan
Table
created.
SQL> set
autotrace on explain
SQL> select
* from author;
Wed May 29
page 1
Book Report
AUTHOR_KEY
AUTHOR_LAST_NAME
AUTHOR_FIRST_NAME
----------- ----------------------------------------
--------------------
AUTHOR_PHONE AUTHOR_STREET
AUTHOR_CITY AU
------------ ----------------------------------------
-------------------- --
AUTHO AUTHOR_CONTRACT_NBR
----- -------------------
A101 Burleson
mark
303-462-1222 1401 west fourth st
st. louis
MO 47301
5601
A102 hester
alvis
523-882-1987 2503 backer view st
st. louis
MO
47301 5602
A103
weaton
erin
367-980-8622 6782 hard day dr
st. louis
MO
47301
5603
A104
jeckle
pierre
543-333-9241 3671 old fort st
north hollywood CA
91607
6602
A105
withers
lester
457-882-2642 1320 leaning tree ln
pie town
IL
57307
7896
A106
petty
juan
344-455-6572 8869 wide creek rd
happyville
TX
77304
6547
A107
clark
louis
666-555-8822 7980 shallow pond st
rose garden WI
33301
3452
A108
mee
minnie
321-543-9876 2356 empty box rd
belaire
KY
45461
7954
A109
shagger
dirk
987-654-3210 3452 dirt path way
cross trax
LA 7301
1
A110
smith
diego
564-897-3201 2567 south north st
tweedle MA
47301
2853
10 rows
selected.
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=1 Card=10 Bytes=
660)
1 0 TABLE ACCESS (FULL) OF 'AUTHOR'
(Cost=1 Card=10 Bytes=660)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
2187 bytes sent via SQL*Net to client
503 bytes received via SQL*Net
from client
2 SQL*Net
roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
Step 1 – Create a plan table - The first step
in this exercise is to connect as the pubs user and create a plan
table.
SQL> connect
pubs/pubs
SQL> @c:\oracle\ora8i\rdbms\admin\utlxplan
Remember, you must be connected as the pubs
user in order to complete this exercise. If you've done the
exercise correctly you should see the following output:
Table
created.
Step 2 – Build a test script - Now that we
have a plan table in place, we are now ready to use the autotrace
facility in order to display the execution plan for query. There
are three forms of the autotrace command, and your challenge is to try
these commands with a query and describe the differences between the
commands. Place these commands in a filed called trace_me.sql and
execute the query:
spool t.lst
set
autotrace on explain;
select *
from author;
set
autotrace on explain only;
select *
from author;
set
autotrace on;
select *
from author;
host notepad
t.lst
Run this script using each one of these three
operators and note the differences between these three permutations of
the autotrace command. The point of this exercise is to note the
different permutations of the autotrace command and see how some
autotrace commands execute the query, while others serve only to
provide statistics for the individual query.
Step 5 – Submit Listing - Submit the listing
to your instructor with a discussion of the different autotrace
options
Now that we understand how to generate and
execution plan and a trace plan for an SQL query we are now ready to
do more sophisticated analysis.
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
|
|
