 |
|
Running a Fast SQL Trace
Oracle Tips by
Burleson
|
Many developers are not aware how easy it is to
use the autotrace facility to see the execution plan of a 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 debugging a SQL statement.
The execution plan shows the access path to the
data and 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 while connected as the SYS database user. This script is
located the in $ORACLE_HOME/sqlplus/admin directory.
SQL>
connect internal; [have to already be in sqlplus to run this command
so why not just do sqlplus sys?]
Connected.
SQL> $ORACLE_HOME/sqlplus/admin/@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
The trace facility is very useful when you want
to know the details about the execution of a SQL query. Next let’s
look at a tool that provides even more detail about SQL execution.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
