Oracle: Viewing SQL execution plans

Remote DBA

Remote DBA Plans

Oracle: Viewing SQL execution plans

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

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

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

ANSWERS

Set autotrace on explain; - This executes the query and then shows the execution plan.

Set autotrace on explain only; - This only shows the execution plan and does not run the query.

Set autotrace on; - This executes the query, shows the exe3cution plan and also displays the execution statistics for the query.”

Burleson is the American Team

American Flag

BC Remote Oracle Support
P.O. Box 511 • Kittrell, NC, 27544

Remote DBA

Remote DBA Services