Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof)

Last updated on September 14th, 2016 at 09:08 pm

We are presenting here All information about Oracle Performance Tuning Tools like Oracle Explain Plan,Auto-trace,tkprof. We will take a deep dive into each of them

Oracle Explain Plan
-Oracle database internally creates a query execution plan in order to fetch the desired data from the physical tables. The query execution plan is nothing but a set of methods on how the database will access the data from the tables. This query execution plan is crucial as different execution plans will need different cost and time for the query execution

-It shows the execution path of a SQL statement
-indicates what tables it will read first and in what order
-indicates what indexes it will use, or if new indexes are needed
-Helps verify that Oracle will retrieve the data the way you expect it to

How to find the Oracle Explain Plan?
Must have full access to a PLAN_TABLE or create PLAN_TABLE with utlxplan.sql delivered by Oracle

Insert the following command at the top of your SQL statement

explain plan
set statement_id = ‘x’
into plan_table
select lpad(‘ ‘,2*level) || operation operations,options,object_name
from plan_table
where statement_id = ‘x’
connect by prior id = parent_id and statement_id = ‘x’
start with id = 1 and statement_id = ‘x’
order by id;


SQL> set linesize 132

SQL> SELECT * FROM TABLE(dbms_xplan.display);

How to Read?
-Read innermost out, top/down

-Join operations always require two sets. The order you read the sets is top down, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table.
-One join is performed at a time, so you only need to consider two sets and their join operation at any one time.

What to look for ?

-Look for TABLE ACCESS… (FULL) …
-Costly if table is big
-Costly if performed many times
-Look for INDEX… (RANGE SCAN)…
-Expected for non-unique indexes
-Suspicious for unique indexes

Some info about joins
Nested Loops-Good for on-line screens and reports
-Read all rows from table 1
-Then access table 2 once for each row returned from table 1
-Fastest if:
rows returned from table 1 are small
access to table2 is inexpensive. This means either a UNIQUE lookup or a SMALL Range Scan.

Merge Join
-Better than nested loops for joining a large number of rows

Hash Join-Common in Parallel Queries
-Better than Merge Join
-Smallest table is passed over and a hashing algorithm is applied to each row to create a hash table and a bitmap.
Second table is passed over and a hashing algorithm applied and check for matchs (ie. joins)
The bitmap is used as a quick lookup and is especially useful when hash table is too large to fit in memory.

Sort Merge Join
-Rows are produced from table 1 and are then sorted
-Rows are produced from table 2 and sorted by the same sort key as table 1
-Table 1 and 2 are NOT accessed concurrently
-Sorted rows from both sides are then merged together(joined)

Autotrace(  Another Oracle Performance Tuning Tool)
Similar to Explain Plan
Provides plan – without having to perform separate query from PLAN_TABLE
Provides statistics
Note: Does not support bind variables

set autotrace { off | on | traceonly }
[explain] [statistics]
shows only the optimizer execution path
shows only execution statistics
includes both the plan and the statistics
same as ON but suppresses query output

Autotrace-Execution Plan produced by Autotrace contains the following:
Line number of each execution step
The relationship number between each step and its parent
Each step of the plan
shows any database links or parallel servers used
-TKPROF discussed next will show row counts whereas Autotrace does not!
Autotrace: Example

Trace Files and TKPROF

Regular Trace
Contains SQL, execution statistics, and execution plan

Provides execution path
Provides row counts
Produces smallest flat file

Cannot tell what values were supplied at run time to SQL
If statistics for query are low but run time is long cannot tell what events caused the long wait

Trace with Binds(level 4)
Regular Trace plus values supplied to the SQL statement via the local variables (i.e. binds)
Provides execution path
Provides row counts
Can tell what values the statement was run for

Again if run time is long and statistics are low it will be hard to tell why in this type of trace.
Produces a bigger flat file that the regular trace because bind variable information has to be stored.

Trace with Waits (level 8)
Regular Trace plus database operation timings that the SQL waited to have done in order to complete. For example: disk access timings.
Provides execution path
Provides row counts
Can tell timings for all events associated with SQL.

Trace file may easily max out due to all the information Oracle must write to the trace file and then only partial information is available in trace file.
Bind variable information is not available

Trace with Binds and Waits(level 12)
Regular trace with the both the wait and bind information. Contains the most complete information and will produce the largest trace file.

Provides execution path
Provides row counts
Can tell timings for all events associated with SQL.
Can tell what values the SQL was run with

Trace file may easily max out due to all the information Oracle must write to the trace file and then only partial information is available in trace file.

Formatting Your SQL Trace File with TKPROF
Trace Files are unformatted dumps of data
TKPROF is tool used to format trace files for reading
tkprof {tracefile} {outputfile}
[explain={user/passwd} ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
TKPROF Usage “Best Practices”
The following is the recommended usage:
tkprof {trace file} {output file} explain={usernm/passwd} print=? sort=prsela,exeela,fchela
Use “explain=…” to get Plan in the report
Use “print=…” to only report on the first “?” statements
Use “sort=…” for sorting the statements with the longest elapsed times first (works with timed_statistics=true)

Some more command related to tracing
1. To trace any sid from outside
sys.dbms_system.set_ev(sid, serial#, , , ”)
Examples: SQL> execute sys.dbms_system.set_ev(8, 219, 10046, 12, ”);

2.Gathering stats for any object in APPS

3. Using oradebug
oradebug setospid
oradebug close_trace

oradebug setospid
oradebug event 10046 trace name context off;
oradebug event 10046 trace name context forever, level 4;

4.Using tkprof
This print 10 sql only
tkprof .trc elaps.prf sys=no explain=apps/ sort=(prsela,exeela,fchela) print=10
This print all the sql
tkprof .trc elaps.prf sys=no explain=apps/apps sort=prsela,exeela,fchela

Leave a Reply