Understanding SQL Tuning Tools
The foundation tools for SQL tuning are:
1. The
EXPLAIN PLAN command
2. The
SQL Trace facility
3. The
tkprof trace file formatter
To diagnose Problem
1- Enable
the trace
2- Take the
explain plan
3- Get the
real time explain plan from v$sql_plan
4- Check
for full table scan
5- Is it
picking the index or not
6- get the
physical,logical reads and executions stats
7- check
for the wait events caused by your sql stmt
8- run
tkprof on the trace file generated
Explain Plan
— To
execute a SQL statement ,Oracle might
need to perform many steps. So Execution
plan is a list of steps that Oracle will follow in order to execute a SQL
statement.
— The
EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer
for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan
is the sequence of operations Oracle performs to run the statement.
There are two
methods for looking at the execution plan
1. EXPLAIN PLAN
command: Displays an execution plan for a SQL statement without actually executing the statement
2. V$SQL_PLAN A
dictionary view introduced in Oracle 9i that shows the execution plan for a SQL
statement that has been compiled into a cursor in the cursor cache
What we will check in Execution Plan
It shows the
following information:
• An
ordering of the tables referenced by the statement
• An
access method for each table mentioned in the statement
• A
join method for tables affected by join operations in the statement
• Data
operations like filter, sort, or aggregation
In addition to the row source tree, the plan table
contains information about the following:
• Optimization,
such as the cost and cardinality of each operation
• Partitioning,
such as the set of accessed partitions
• Parallel
execution, such as the distribution method of join inputs
Explain Plan
An explain
plan is a representation of the access path that is taken when a query is
executed within Oracle. Query processing can be divided into 7 phases:
- Syntactic – checks the syntax of the
query
- Semantic – checks that all objects exist
and are accessible
- View Merging – rewrites query as join
on base tables as opposed to using views
- Statement Transformation – rewrites query
transforming some complex constructs into simpler ones where appropriate
(e.g. subquery unnesting, in/or transformation). Some transformations use
rules while others are costed based upon statistics.
- Optimization – determines the optimal
access path for the query to take. The Cost Based Optimizer (CBO) uses
statistics to analyze the relative costs of accessing objects.
- Query Evaluation Plan(QEP)
Generation
- QEP Execution
Steps
[1]-[6] are sometimes grouped under the term ‘Parsing‘
Step [7] is the Execution of the statement.
The explain
plan is a representation of the access path produced in
step 6
EXPLAIN PLAN COMMAND
Perform the following to check it:
Example
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
Finally use the DBMS_XPLAN.DISPLAY function to display the execution
plan:
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 1 | 57
| 3 |
| 1 | NESTED LOOPS |
| 1 | 57 | 3 |
|* 2 | TABLE ACCESS FULL |
EMP | 1 | 37 | 2
|
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1
| 20 | 1 |
|* 4 | INDEX UNIQUE SCAN |
PK_DEPT | 1 | |
|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
SELECT *
FROM table(dbms_xplan.display('plan_table',null,'basic'));
Display
execution plan for last SQL statements executed in session
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'basic'));
How To Read Query Plans?
— The
execution order in EXPLAIN PLAN output begins with the line that is the
furthest indented to the right.The next step is the parent of that line.
If two lines are indented equally, then the top line is normally executed
first.
------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 | 11 |
53 (2)| 00:00:01 |
| 1 | SORT
AGGREGATE
|
| 1 | 11
|
| |
| 2 | TABLE ACCESS BY INDEX ROWID|
SKEW | 53 | 583
| 53 (2)| 00:00:01 |
|* 3 | INDEX RANGE
SCAN | SKEW_COL1
| 54 |
| 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Interpreting Explain Plan
SQL>
set autotrace traceonly explain
SQL> select * from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 14 | 532 |
3 (0)| 00:00:01 |
| 1
| TABLE ACCESS FULL| EMP | 14
| 532 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
The steps
in the explain plan are indented to indicate the hierarchy of operations
and which
steps are dependent on which other steps.
Most
indented is executed first.In this example this would be the operation with
Id=1 (TABLE ACCESS FULL).
---------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0
| SELECT
STATEMENT | | 10
| 650 | 7 (15) | 00:00:01 |
|* 1
| HASH JOIN
| | 10
| 650 | 7 (15) | 00:00:01 |
| 2 | TABLE
ACCESS FULL| JOB | 4
| 160
| 3 (0) | 00:00:01 |
| 3 | TABLE
ACCESS FULL| EMP | 10 | 250
| 3 (0) | 00:00:01 |
---------------------------------------------------------------------------
— Full table scan operation on the job table
will execute first
— order of operations is 2,3, 1.
EXPLAIN PLAN COMMAND
1) DISPLAY: to
format and display the contents of a PLAN_TABLE. Parameters: table_name,
sql_id, format, filter_preds
2) DISPLAY_CURSOR: to format and display the contents
of the execution plan of any loaded cursor available in V$SQL.
Parameters: sql_id, child_number, format
3) DISPLAY_AWR: to format and display the contents of
the execution plan of a stored SQL statement in the AWR in DBA_HIST_SQLPLAN.
Parameters: sql_id, plan_hash_value, db_id, format
4) DISPLAY_SQLSET: to format and display the contents
of the execution plan of statements stored in a SQL tuning set, used in
conjunction with the package DBMS_SQLTUNE. Parameters: sqlset_name,
sql_id, plan_hash_value, format, sqlset_owner
— The DBMS_XPLAN.DISPLAY function
can accept 3 parameters:
- table_name - Name of plan table, default value
'PLAN_TABLE'.
- statement_id - Statement id of the plan to be displayed, default
value NULL.
- format - Controls the level of detail displayed, default value
'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'.
— The DBMS_XPLAN.DISPLAY_CURSOR takes
3 parameters
– SQL_ID (default last statement executed in this session),
– Child number (default 0),
– format (default 'TYPICAL')
Columns
in the explain plan provide various pieces of useful information in determining
why the plan was chosen:
Rows – It tells us the estimated
number of rows that the optimizer expects this line of the execution plan to
return
Bytes – It tells us the estimated
number of bytes that the optimizer expects this line of the execution plan will
return
Cost (%CPU) – The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects the execution plan with the lowest cost, where cost represents the estimated resource usage for that plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query
Cardinality
: estimate
of the number of rows coming out of each of the operation means distinct rows.
Access Method : The way in which data is being
accessed, via either a table scan or index access
Joind Method : the Method (e.g hash,sort-merge etc)
used to join tables with each other
Join Type : The type of join (eg, outer,semi etc)
Join order : The order in which table are joined each
other
What are Oracle Access Paths or Methods?
Access Methods or Access Paths are the techniques
used by Oracle to access the data needed to answer a query. Access Methods can
be divided into two categories; data accessed via a table scan or index
access. You can find the individual Access Methods chosen by the Optimizer
in the Operations column of an Execution table.
How Many Access Paths are available to the Optimizer
Operations
that Retrieve Rows (Access Paths)
— Full Table Scan -A full table scan reads all rows
from a table, and then filters out those rows that do not meet the selection
criteria (if there is one). Contrary to what one could think, full table scans
are not necessarily a bad thing. There are situations where a full table scan
would be more efficient than retrieving the data using an index.
— Table Access by Rowid-A rowid is an internal
representation of the storage location of data. The rowid of a row specifies
the data file and data block containing the row and the location of the row in
that block. Locating a row by specifying its rowid is the fastest way to
retrieve a single row because it specifies the exact location of the row in the
database.
In most cases, the database accesses a table by rowid after a scan of one or
more indexes.
— Index Unique Scan:An index unique scan returns at most 1 rowid, and thus, after an index unique scan you will typically see a table access by rowid (if the desired data is not available in the index). Index unique scans can be used when a query predicate references all of the columns of a unique index, by using the equality operator.
— Index Range Scan -An index range scan is an ordered
scan of values, and it is typically used when a query predicate references some
of the leading columns of an index, or when for any reason more than one value
can be retrieved by using an index key. These predicates can include equality
and non-equality operators (=, <. >, etc).
— Index Full Scan:An index full scan reads the entire
index in order, and can be used in several situations, including cases in which
there is no predicate, but certain conditions would allow the index to be used
to avoid a separate sorting operation.
— Index Fast Full Scan:An index fast full scan reads the
index blocks in unsorted order, as they exist on disk. This method is used when
all of the columns the query needs to retrieve are in the index, so the
optimizer uses the index instead of the table.
— Index Join Scan:An index join scan is a hash join of
multiple indexes that together return all columns requested by a query. The
database does not need to access the table because all data is retrieved from
the indexes.
What is an Oracle Join Method?
Join Methods are the techniques used by the Oracle
Optimizer to join data coming from two data producing operators in an execution
plan. You can find the individual Join Methods chosen by the Optimizer in the
Operations column of an Execution table.
There is three join method
1) Nested Loops Join
Nested loops joins are useful when small subsets
of data are being joined and if there is an efficient way of
accessing the second table (for example an index lookup)
For every row in the first table (the outer table), Oracle
accesses all the rows in the second table (the inner table) looking for a
match.
You can think of it as a set of embedded FOR loops.
NOTE: In Oracle Database 11g the internal implementation for
nested loop joins changed to reduce overall latency for physical
You may see two NESTED LOOPS operators in the plan’s
operations column, where you previously only saw one on earlier versions of
Oracle.
2) Hash Joins
Hash joins are used for joining large data sets. The
Optimizer uses the smaller of the two tables or data sources to build a hash
table, based on the join key, in memory. It then scans the larger table and
performs the same hashing algorithm on the join column(s). It then probes the
previously built hash table for each value and if they match, it returns a row
3) Sort Merge Joins
Sort Merge joins are useful when the join condition between
two tables is an in-equality condition such as, <, <=, >, or >=.
Sort merge joins can perform better than nested loop joins for large data sets.
The join consists of two steps:
- Sort
join operation: Both the inputs are sorted on the join key.
- Merge
join operation: The sorted lists are merged together.
A Sort Merge join is more likely to be chosen if there is an
index on one of the tables that will eliminate one of the sorts.
Operations
that Manipulate Data
The most
common operations in this group are sorts and joins.
Sorts:A sort operation is performed when the rows coming out of the step
need to be returned in some specific order. This can be necessary to comply
with the order requested by the query, or to return the rows in the order in
which the next operation needs them to work as expected, for example, when the
next operation is a sort merge join.
Joins:When you run a query that includes more than one table in the FROM
clause the database needs to perform a join operation, and the job of the
optimizer is to determine the order in which the data sources should be joined,
and the best join method to use in order to produce the desired results in the
most efficient way possible.
Both of these decisions are made based on the available statistics.
Here is a small explanation for the different join methods the optimizer can
decide to use:
Nested Loops Joins: When this method is used, for each row in the first
data set that matches the single-table predicates, the database retrieves all
rows in the second data set that satisfy the join predicate. As the name
implies, this method works as if you had 2 nested for loops in a procedural
programming language, in which for each iteration of the outer loop the inner
loop is traversed to find the rows that satisfy the join condition.
As you can imagine, this join method is not very efficient on large data sets,
unless the rows in the inner data set can be accessed efficiently (through an
index).
In general, nested loops joins work best on small tables with indexes on the
join conditions.
— Hash Joins:The database uses a hash
join to join larger data sets. In summary, the optimizer creates a hash
table (what is a hash
table?) from one of the data sets (usually the smallest one) using the
columns used in the join condition as the key, and then scans the other data
set applying the same hash function to the columns in the join condition to see
if it can find a matching row in the hash table built from the first data set.
You don’t really need to understand how a hash table works. In general, what
you need to know is that this join method can be used when you have an
equi-join, and that it can be very efficient when the smaller of the data sets
can be put completely in memory.
On larger data sets, this join method can be much more efficient than a nested
loop.
— Sort Merge Joins:A sort merge join is a variation of
a nested loops join. The main difference is that this method requires the 2
data sources to be ordered first, but the algorithm to find the matching rows
is more efficient.
This method is usually selected when joining large amounts of data when the
join uses an inequality condition, or when a hash join would not be able to put
the hash table for one of the data sets completely in memory.
Plan
Generation Method
There are two method
— Generate EXECUTION PLAN from
PLAN_TABLE.
— Generate EXECUTION PLAN using
DBMS_XPLAN.
— 2.1 Using function DISPLAY().
— 2.2 Using function DISPLAY_CURSOR().
— Generate EXECUTION PLAN from
PLAN_TABLE.
We can fetch the execution plan from
PLAN_TABLE by running a conventional SELECT statement with proper formatting
which can look alike the output of DBMS_XPLAN package.
SQL>
set lines 100
SQL>
col EXECUTION_PLAN for a50
SQL>
explain plan for
select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);
Explained.
SQL>
select rtrim(lpad(‘ ‘,2*LEVEL) || rtrim(operation) || ‘ ‘ || rtrim(options) ||
‘ ‘ || object_name) EXECUTION_PLAN, cost, cardinality from plan_table connect
by prior id=parent_id start with id=0;
EXECUTION_PLAN
COST CARDINALITY
————————————————–
———- ———–
SELECT
STATEMENT
298 31225
TABLE ACCESS FULL
T1
298 31225
Alternate Approach
— Use
this approach if you are unable to capture the plan using the preferred
approach. This approach may be used to collect plans reliably from queries that
don't have bind variables.
— a.
Generate the execution plan:
SQL> EXPLAIN PLAN FOR
< your query goes here >
b. Display the execution plan:
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> select plan_table_output from
table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
SQL> spool off
Plan
Generation Method
— Using function DISPLAY_CURSOR().To generate execution plan of a
query using DISPLAY_CURSOR, you will require SQL ID and cursor child number of
that query
SQL>
select * from table(dbms_xplan.display_cursor(’54skb3zk6qux7′,0,’TYPICAL’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID
54skb3zk6qux7, child number 0
————————————-
select *
from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’)
Plan
hash value: 3617692013
————————————————————————–
|
Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
|
0 | SELECT STATEMENT |
| |
| 298 (100)|
|
|*
1 | TABLE ACCESS FULL| T1 | 31225 | 2988K|
298 (1)| 00:00:04 |
PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate
Information (identified by operation id):
—————————————————
1 – filter((“OWNER”=’DBSNMP’ OR “OWNER”=’SYS’ OR “OWNER”=’SYSTEM’))
18 rows
selected.
SQL>
set linesize 150
SQL>
set pagesize 2000
SQL> select * from
TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL'));
If the cursor happened to be executed when plan statistics
were gathered, then use
"RUNSTATS_LAST" instead of just
"ALL".sql_id:
using below
query to see which all plan_hash_value are being used by my sql_id...
select ss.snap_id,
ss.instance_number node,
begin_interval_time,
sql_id,
plan_hash_value,
nvl(executions_delta,0) execs,
round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000,2)
avg_etime,
round((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)),2)
avg_lio
from DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
where sql_id = 'xxxxxxxx'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1 desc, 2, 3;
Some Example
with Explanation
EXPLAIN PLAN
SET statement_id =
'ex_plan1' FOR
SELECT phone_number FROM employees WHERE phone_number LIKE
'650%';
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | ACCESS FULL| EMPLOYEES |
---------------------------------------
This plan shows execution of a SELECT statement. The table
employees is accessed using a full table scan.
— Every
row in the table employees is accessed, and the WHERE clause criteria is
evaluated for every row.
— The
SELECT statement returns the rows meeting the WHERE clause criteria.
Some Example
with Explanation
EXPLAIN PLAN
SET statement_id
= 'ex_plan2' FOR
SELECT last_name FROM employees
WHERE last_name
LIKE 'Pe%';
SELECT PLAN_TABLE_OUTPUT
FROM
TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
----------------------------------------
| Id |
Operation | Name |
----------------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | INDEX RANGE SCAN| EMP_NAME_IX |
----------------------------------------
This plan shows execution of a SELECT statement.
— Index
EMP_NAME_IX is used in a range scan operation to evaluate the WHERE clause
criteria.
— The
SELECT statement returns rows satisfying the WHERE clause conditions.
Explain Plan Limitations
— The
EXPLAIN PLAN statement provides a good faith estimate of the execution plan
that Oracle would use. The real plan that gets used may differ from what
EXPLAIN PLAN tells you for many reasons:
— Optimizer
stats, cursor sharing,, bind variable peeking, dynamic instance parameters make
plans less stable.
— EXPLAIN
PLAN does not peek at bind variables.
— EXPLAIN
PLAN does not check the library cache to see if the statement has already been
parsed.
— EXPLAIN
PLAN does not work for some queries:
— ORA-22905:
cannot access rows from a non-nested table item
Viewing Actual Execution Plans
— The
v$sql view shows statements in the library cache. Here you can find the
address, hash value, and child number for a statement of interest.
— The
v$sql_plan view shows the actual execution plan for each statement, given its
address, hash value, and child number. The columns are similar to the plan
table.
— The
v$sql_plan_statistics view shows actual statistics (rows, buffer gets, elapsed
time, etc.) for each operation of the execution plan.
— The
v$sql_plan and v$sql_plan_statistics views are available starting in Oracle 9i.
v$sql_plan_statistics is not populated by default.
SQL_TRACE and tkprof
The Oracle server process managing a database session writes
a verbose trace file when SQL trace is enabled for the session.
ALTER SESSION SET SQL_TRACE TRUE causes a trace of SQL
execution to be generated.
TKPROF is a utility provided by Oracle that formats SQL
trace files into very helpful and readable reports. TKPROF is installed
automatically when the database server software is installed. You invoke TKPROF
from the operating system command line; there is no graphical interface for
TKPROF. Starting in Oracle 9i TKPROF can read extended SQL trace files and report
on wait events statistics
Tkprof output contains breakdown of execution statistics, execution plan and rows returned for each step. These stats are not available from any other source.
we will collect data to help verify whether the suspected
query is the one that should be tuned. We should be prepared to identify the
specific steps in
the application that cause the slow query to execute. We will trace the
database session while the application executes this query
— The
extended SQL trace (10046 trace at level 12) will capture execution statistics
of all SQL statements issued by a session
— during
the trace. It will show us how much time is being spent per statement, how much
of the time was due to CPU or waitevents, and what the bind values were. We
will be able to verify if the "candidate" SQL statement is truly
among the SQL issued by a typical session.
Choose a session to trace
— Users
that are experiencing the problem most severely; e.g., normally the transaction
is complete in 1 sec, but now it takes 30 sec.
— Users
that are aggressively accumulating time in the database
Trace a Connected Session
Ideally, start the trace as soon as the user logs on and
begins the operation or transaction. Continue tracing until the operation is
finished.
Try to avoid starting or ending the trace in the middle of a
call unless you know the call is not important to the solution
This is the most common way to get a trace file.
l Start tracing on a connected session
l Coordinate with the user to start the
operation
l Let
the trace collect while the operation is in progress
l Stop
tracing when the operation is done
l Gather
the trace file from the "user_dump_dest" location (you can usually
identify the file just by looking at the timestamp).
Generate a TKProf report and sort the SQL statements in
order of most elapsed time using the following command:
l tkprof
<trace file name> <output file name> sort=fchela,exeela,prsela
Enabling SQL Trace
- At
the instance level:
— sql_trace
= true
— timed_statistics
= true (optional)
- In your own session:
— ALTER
SESSION SET sql_trace = TRUE;
— ALTER
SESSION SET timed_statistics = TRUE; (optional)
- In another session:
— SYS.dbms_system.set_sql_trace_in_session
(<SID>, <serial#>, TRUE)
— Finding
the Trace File
— Look
in the user dump destination. On OFA compliant systems this will be
$ORACLE_BASE/admin/$ORACLE_SID/udump
— Check
timestamps and file contents to see which trace file is yours
— If
non-DBAs need access to trace files, add _trace_files_public = true to the
parameter file to avoid permissions problems on Unix platforms
— Use
a dedicated server connection when tracing, if possible.
To Identify OS Process id
select p.PID,p.SPID,s.SID
from v$process
p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID
To indentify Oracle session ID from OS Process id
select
p.PID,p.SPID,s.SID,s.serial# from v$process p,v$session s
where s.paddr =
p.addr
and p.SPID = 29179
SPID is the operating system Process identifier (os pid).PID
is the Oracle Process identifier (ora pid) Once the OS process id for the
process has been determined then the trace can be initialized as follows:
Lets assume that the process to be traced has an os pid of
9834.
Login to SQL*Plus as a dba and execute the following:
connect / as sysdba
oradebug setospid
9834
oradebug unlimit
oradebug event 10046
trace name context forever,level 12
oradebug
tracefile_name
Remember to replace the example '9834' value with the actual
os pid.
Note that it is also possible to attach to a session via
oradebug
using the 'setorapid'.
In this case the PID (Oracle Process identifier ) would be
used
(rather than the 'SPID') and the oradebug text would change
to:
connect / as sysdba
oradebug setorapid
9834
oradebug unlimit
oradebug event 10046
trace name context forever,level 12
To identify trace location
select c.value || '/' || d.instance_name || '_ora_' ||
a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = 4459 and c.name = 'user_dump_dest'
=======================
Other method to trace
----------------------------------------------
RUN THIS PACKAGE FIRST
----------------------------------------------
dbmssupp.sql
----------------------------------------------
FOR SQL TRACING
exec dbms_support.start_trace_in_session
(4361,2072,binds=>true,waits=>true);
— exec
dbms_support.stop_trace_in_session (4361,2072); Invoke TKPROF from the operating system prompt
like this:
— tkprof
<trace file> <output file> \
— [explain=<username/password>] \
— [sys=n] [sort=<keyword>]
— TKPROF
Command-line Arguments
— tkprof
<trace file> <output file> \
— [explain=<username/password>] \
— [sys=n] [sort=<keyword>]
— trace
file The SQL trace file to be
formatted
— output
file The formatted output to be
written by TKPROF
— explain= Database login to be used if you
want the output to include
execution plans
— sys=n Omit “recursive SQL”
performed by the SYS user
— sort= List traced SQL statement in
the output file in a specific order
exec dbms_system.set_sql_trace_in_session(2473,1,true);
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123,
serial#=>1234, sql_trace=>FALSE);
tkprof
rcc1_ora_3695308.trc rcc1_ora_3695308.prf SYS=NO SORT= EXECPU,FCHCPU
or
tkprof rcc2_ora_12.trc tkp_rcc2.txt waits=yes sys=no
sort=exeela,fchela explain='system/'
tkprof user_sql_001.trc user1.prf explain=hr/hr
table=hr.temp_plan_table_a sys=no
sort=exeela,prsela,fchela
Formatting a Trace File with TKPROF
— Report
heading
— TKPROF
version, date run, sort option, trace file
— One
entry for each distinct SQL statement in trace file
— Listing
of SQL statement
— OCI
call statistics: count of parse, execute, and fetch calls, rows processed, and
time and I/O used
— Parse
information: parsing user, recursive depth, library cache misses, and optimizer
mode
— Row
source operation listing
— Execution
plan listing (optional)
— Wait
event listing (optional)
— Report
Summary
— OCI
call statistics totals
— Counts of how many statements were found in the trace file, how many were distinct, and how many were explained in the report.
Tkprof output
— TKPROF:
Release 8.1.6.1.0 - Production on Wed Aug 9 19:06:36 2000
— (c)
Copyright 1999 Oracle Corporation. All
rights reserved.
— Trace
file: example.trc
— Sort
options: default
— ************************************************************************
— count = number of times OCI procedure was
executed
— cpu = cpu time in seconds executing
— elapsed = elapsed time in seconds executing
— disk =
number of physical reads of buffers from disk
— query = number of buffers gotten for consistent
read
— current = number of buffers gotten in current mode
(usually for update)
— rows = number of rows processed by the fetch or
execute call
count2 cpu3 elapsed4 disk5 query6 current7 rows8
------
------ ------ -------- ------- --------
-------- ------
Parsea 1d 0.02
0.01 0 0
0 0
Executeb 1e 0.00
0.00 0 0
0 0
Fetchc 20j 141.10
141.65 1237 1450011
386332 99i
------ ------ ------ -------- ------- --------
-------- ------
total 22
141.12 141.66 1237k 1450011f 386332g 99h
Rowsl Execution Planm
-------
---------------------------------------------------
0
SELECT STATEMENT GOAL: CHOOSE
99
FILTER
96681
TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'CUSTOMERS'
96582
TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'EMPLOYEES'
SELECT table_name FROM user_tables ORDER BY table_name
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ---------
--------- --------- ---------
Parse 1
0.01 0.02 0 0 0 0
Execute 1
0.00 0.00 0 0 0 0
Fetch 14
0.59 0.99 0
33633 0 194
-------
------ -------- ---------- ---------
--------- --------- ---------
total 16
0.60 1.01 0
33633 0 194
Misses
in library cache during parse: 1
Optimizer
goal: CHOOSE
Parsing
user id: RSCHRAG [recursive depth: 0]
The
application called on Oracle to parse this statement once while SQL trace was
enabled.
The parse took 0.01 CPU seconds, 0.02 elapsed seconds.
No
disk I/Os or buffer gets took place during the parse, suggesting that no misses
in the dictionary cache.
Oracle
was called on to execute this statement once.
The
execution took under 0.01 CPU seconds.
No
disk I/Os or buffer gets took place during the execution. (Queries often defer
the work to the fetch phase.)
Oracle
was called on 14 times to perform a fetch, and a total of 194 rows were
returned.
Fetching
took 0.59 CPU seconds, 0.99 elapsed seconds.
Fetching
required 33,633 buffer gets in consistent mode, but no physical reads were
required.
The
statement was not in the library cache (shared pool) when the parse call came
in.
The
cost-based optimizer and a goal of “choose” were used to parse the statement.
The
RSCHRAG user was connected to the database when the parse occurred.
This
statement was executed directly by the application; it was not invoked
recursively by the SYS user or a database trigger.
Execution
plans are only included in TKPROF reports if the explain= parameter is
specified when TKPROF is invoked
TKPROF
will create and drop its own plan table if one does not already exist
The row counts on each step are actuals—not estimates. This can be very helpful when troubleshooting queries that perform poorly.
When
TKPROF runs the EXPLAIN PLAN statement for a query, a different execution plan
could be returned than was actually used in the traced session.
TKPROF
Reports: More Than Just Execution Plans
Listing
of SQL statements and library cache miss information helps you determine if
applications are using Oracle’s shared SQL facility effectively.
Parse,
execute, and fetch call counts help you determine if applications are using
Oracle APIs effectively.
CPU
and I/O statistics help you zero in on resource-intensive SQL statements.
Row
counts on individual steps of the execution plans help you rework inefficient
execution plans.
3. Does the query spend most of its time in the
execute/fetch phases (not parse phase)?
Rationale: If the query spends most its time parsing, normal
query tuning techniques that alter the
execution plan to reduce logical I/O during execute or fetch
calls probably won't help. The focus of
the tuning should be in reducing parse times; see the
"Parse Reduction" strategy.
— For
example, here is an excerpt from a TKProf for a query:
SELECT
* FROM ct_dn dn, ds_attrstore store . . .
call count
cpu elapsed disk query current rows
------- ------ -------- -------- ----- -------- --------
-------
Parse 555
100.09 300.83 0
0 0 0
Execute 555
0.42 0.78 0
0 0 0
Fetch 555
14.04 85.03 513
1448514 0 11724
-------
------ -------- -------- ----- -------- -------- -------
total 1665
114.55 386.65 513 1448514 0 11724
— The
elapsed time spent parsing was 300.83 seconds compared to only 85.03 seconds
for fetching.
— This
query is having trouble parsing - tuning the query's execution plan to reduce
the number of buffers read during the fetch
call will not give the greatest performance gain (in fact only about 85 out
— of 386 seconds could be improved in the fetch call).
— 2.
Does the time spent parsing, executing, and fetching account for most of the
elapsed time in the trace.
— If
so, continue to the next question.
— If
not, check client waits ("SQLNet Message from Client") time between
calls
— l
Are the client waits occurring in between fetch calls for the same cursor ?
— m
If so, update the problem statement to note this fact and continue with the
next
— question.
— m
If most of the time is spent waiting in between calls for different cursors,
the
— bottleneck
is in the client tier or network - SQL tuning may not improve the
— performance
of the application.
— This
is no longer a query tuning issue but requires analysis of the client or network.
— Detailed
Explanation
— The
goal of query tuning is to reduce the amount of time a query takes to parse,
execute, and/or
— fetch
data. If the trace file shows that these operations occur quickly relative to
the total elapsed
— time,
then we may actually need to tune the client or network.
No comments:
Post a Comment