Donald K. Burleson Oracle Tips

Tracing an SQL statement

Many developers are not aware how easy it is to use the autotrace facility to see the execution plan of an SQL statement.  In addition to the execution plan, the autotrace facility gives the statistics for the SQL statement, and this can be very useful for de-bugging an SQL statement. 

The execution plan shows the access path to the data and it can be invaluable for Oracle SQL tuning.  The statistics reveal the amount of Oracle resources that are allocated to servicing the SQL.

Here is the procedure.

1. Run the PLUSTRCE.SQL script connected as the SYS database user. This script is located the in $ORACLE_HOME/sqlplus/admin directory

burleson*test1-/u01/app/oracle/product/8.1.6_64/sqlplus/admin

>svrmgrl

 

SVRMGR> connect internal;

Connected.

SVRMGR> @plustrce

2. Run utlxplan.sql from the $ORACLE_HOME/rdbms/admin directory.  This will create a plan table to hold the SQL execution plan.

SQL> @utlxplan

3. Now, you can issue the "SET AUTOTRACE ON" command in SQL*plus to trace SQL execution and provide SQL statistics.

SQL>set autotrace on;

 

SELECT PAGE_SEQ_NBR   FROM reader.PAGE  WHERE (BOOK_UNIQUE_ID = 001 ) ;

 

PAGE_SEQ_NBR

------------

           1

           2

           3

           4

           5

         302

         303

         304

 

304 rows selected.

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=208 Bytes

          =5408)

 

   1    0   INDEX (RANGE SCAN) OF 'PAGE_U1_IDX' (UNIQUE) (Cost=2 Card=

          208 Bytes=5408)

 

 

 

Statistics

----------------------------------------------------------

         18  recursive calls

          0  db block gets

         27  consistent gets

          0  physical reads

          0  redo size

       5245  bytes sent via SQL*Net to client

       1982  bytes received via SQL*Net from client

         22  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

        304  rows processed

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: