Query Tuning Overview

Purpose

This document gives a overview of the SQL tuning issues.

Scope & Application

For all DBAs

SQL Query Handing in Oracle

Every statement submitted to the Oracle engine passes through a number of phases that determine for example the statements validity or if it has run before etc. In terms of SQL Tuning the most important area is Query optimization which is nominally part of the PARSE phase.

@ See Unpublished Note 199273.1 Overview of SQL Statement Processing Phases for more information on the stages a query passes through.

Query Optimization

For each SQL statement, there is a multitude of different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient way to retrieve this data based upon the evaluation of a number of different criteria. Oracle currently (as of Oracle9i Release 2) provides 2 different optimization approaches. These are Cost Based Optimization (CBO) and Rule Based Optimization (RBO). The CBO bases optimization choices on pre-gathered table and index statistics while the RBO makes it's decisions based on a set of 'best practice' rules and does not rely on any statistical information. CBO's reliance on statistics makes it vastly more flexible than the RBO since as long as up to date statistics are maintained it will accurately reflect real data volumes. The RBO is Oracle's legacy optimizer and is to be desupported in Oracle10i.

Oracle Chooses between optimizers on a number of criteria: See Note 66484.1 Which Optimizer is Used? for details

For more information on the Oracle optimizer see:

Note 66484.1 Which Optimizer is Used? This article is a checklist for determining which optimizer a particular statement will use
Note 10626.1 Cost Based Optimizer (CBO) Overview This article is a comprehensive description about the CBO. Most suitable for beginners in the tuning area. It was written for version 7 so does not consider the new methods.
Note 35934.1 Cost Based Optimizer-Common Misconceptions and Issues This reference article covers many areas of query optimization and predominately focuses on solving common issues.
Note 43065.1 Rule Based Optimizer -- Changing Query Access Path. This article explain how to modify the access paths of queries that use the RBO
Note 189702.1 Rule Based Optimizer is to be Desupported in Oracle10i Announces the pending Desupport of the RBO

Oracle9i Database Performance Guide and Reference Chapter 1: Understanding the Optimizer

Query Tuning

The vast majority of queries submitted to the Oracle optimizer(s) perform adequately and consistently return correct results. Sometimes, however, the optimizer is presented with information that, when it is compared to the real information, is incorrect or insufficient for determination of the optimal access method for the query. In these rare cases, manual intervention may be necessary to obtain the desired performance. Support has created a large number of articles to help guide analysts tune queries. A number of these are listed below together with brief descriptions of their contents.

Helpful Query Tuning Articles:

Note 46234.1 Interpreting Explain plan This article details, with explain plan examples, all possible access methods, join methods, view usage, sorting, filtering, parallel execution and handling of bind variables and remote queries. It provides a short (but expert level) introduction to Oracle's SQL processing and gives deep insight in to each step that can be seen in explain plan output.
Note 29236.1 QREF: SQL Statement HINTS Reference article explaining hint syntax. Hints provide a mechanism to direct the optimizer to choose a certain query execution plan.
Note 50607.1 How to specify an INDEX Hint Explains how to specify working index hints in queries with worked SQL and explain plan examples.

Oracle9i Database Performance Guide and Reference Chapter 5: Optimizer Hints

Troubleshooting Query Tuning Issues

The following articles can assist with troubleshooting any SQL Tuning issues that may be encountered:

Note 163563.1 Resolving Query Tuning Issues Provides step by step guidelines for dealing with Query Tuning problems
Note 179668.1 Suggested Query Tuning Workflow Focuses in on best practices for tuning queries
Note 67522.1 Why is my index not used? Explains why queries may not be able to fully utilise indexes
Note 69992.1 Why is my hint ignored? Explains why supplied hints may not appear to be used
Note 122812.1 Tuning Suggestions When Query Cannot be Modified Suggests methods that can be used to modify the performance of queries that cannot be changed
Note 160089.1 Why are my queries slow after upgrading my database? Suggests reasons why performance may degrade after making system changes
Note 150895.1 Handling Wrong Results Issues Describes steps to work through when it is suspect that a query has returned an incorrect result.
Note 33089.1 TROUBLESHOOTING GUIDE: SQL Tuning Presents a number of common Solutions to SQL Tuning Issues
Note 68735.1 Information required to diagnose a Query Performance Problem Outlines all the information that you should gather in order to successfully diagnose query performance issues

Query Tuning Frequently Asked Questions

Purpose

This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statememts.

Scope & Application

For all DBAs

Query Tuning FAQ

Why is an index not used?
Why is a particular query slow?
Why is a particular query slower than it used to be?
Why is a particular query slower since upgrading?
Why does a particular query's runtime vary?
Why does the execution plan for a particular query change?
Why does a particular query's runtime vary with bind variables rather than literals?
Which optimizer is a query using?
Can Optimizer Parameters be hinted?
Why is partition elimination not occuring?
What is the importance of Global Statistics?
Diagnostics
How to Gather Information for Query Tuning Problems
How to Gather Optimizer Statistics
How to Gather Access Path Information for Queries
How to Store and Apply Query Outlines
How to Gather Trace for Query Tuning Issues
Why is an index not used?
The following note is a comprehensive discussion of a number of reasons why indexes may not be selected for certain queries:
Note 67522.1 Diagnosing Why a Query is Not Using an Index and Why it is Using a Full Table Scan?

Why is a particular query slow?
If a new query has been created that is slow on 10g and above, then Oracle Support advises users to utilise the 10g SQL Access Advisor to suggest some suitable modifications.

See:
Oracle10g Database Performance Tuning Guide
10g Release 2 (10.2) Part Number B14211-01
Chapter 17 SQL Access Advisor

Alternatively, various tuning articles are available to assist with manual tuning on all releases:


Note 372431.1 TROUBLESHOOTING: Tuning a New Query
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries

Why is a particular query slower than it used to be?
There are a number of factors that can change a query's response time. If a query's performance has changed then it follows that something in the environment has changed to initiate this. There could me more data involved, incomplete or inaccurate statistics, i/o performance changes, access path changes among others. The following articles address various query tuning issues:

Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Note 163563.1 TROUBLESHOOTING: Advanced Query Tuning

Occasionally, changes in Oracle software through upgrading or patching can cause execution sub-optimal execution plans.
See the section: " Why is this query slower since upgrading?" below for help with this kind of problem.
Why is a particular query slower since upgrading?
As Oracle continues to develop the Database Software, changes are introduced in the optimizer that are designed to give better performance. Occasionally, changes that provides improved performance for many, can have an adverse effect for a small number of others. New versions can also require different approaches in system management to maintain, or achieve, better perfomance.

Bug fixes or security patches may also mean a previous fast execution plan is no longer possible. For example, queries may have being using a bugged access method that could cause problems or security issues in specific circumstances. In resolving the bug, and closing the loophole, the access path that was previously used is no longer available.

The following notes give guidance on what to do when upgrading to ensure good performance is maintained and what to do if a performance regression is encountered:


Note 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

Note 258167.1 Upgrading from 8.1.X to 9.X - Potential Query Tuning Related Issues
Note 258945.1 Upgrading from 8.1.X to 9.X - Subquery Issues - Diagnosing and Resolving
Note 258946.1 Upgrading from 8.1.X to 9.X - View Issues - Diagnosing and Resolving
Note 259126.1 Upgrading from 8.1.X to 9.X - Btree Bitmap Plan Issues - Diagnosing and Resolving

Note 295819.1 Upgrading from 9i to 10g - Potential Query Tuning Related Issues

Note 223806.1 Query with unchanged execution plan is slower after database upgrade

Why does a particular query's runtime vary?
Given the same inputs, a statement will always return the same outputs. For performance to vary, one of the inputs must have changed. There are a very large number of variables that can affect the performance of a query.
See:
Note 372431.1 TROUBLESHOOTING: Tuning a New Query
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries

Why does the execution plan for a particular query change?
The explain plan for a query can change if one of the factors used by the CBO has changed. These can include:


Initialisation parameters
Object statistics
System statistics
Predicate values

You can use the following script to compare differences that might be causing the execution plan to change. Run the script for each case and compare the outputs.
Note 215187.1 SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement:

If explain plans change despite no differences being evident, then it is possible that you are hitting an issue discussed in the following article:
Note 338113.1 Plans can change despite no stats being regathered


@ This could mean that UNPUBLISHED Bug 4567767 is being encountered.
Why does a particular query's runtime vary with bind variables rather than literals?
For details on this issue see:
Note 401068.1 Possible Poor Runtime Performance for Bind Variables when Compared with Literal Values

Which optimizer is a query using?
Often it is important to be able to determine which optimizer is being used by a query. The following article helps with that:

Note 66484.1 Which Optimizer is Being Used ?

Can Optimizer Parameters be hinted?
In 10g Release 2, optimizer parameters can be set with a hint. See:

Note 377333.1 OPT_PARAM Hint in 10g R2

Why is partition elimination not occuring?
Partition elimination or Pruning occurs when the predicates and join order for a query provide sufficient information to allow a set of partitions to be eliminated from the result set without accessing the data. Details of different pruning methods can be found in the following articles:
Note 179518.1 Partition Pruning and Joins
Note 166118.1 Partition Pruning/Elimination
Note 209070.1Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables

Please also see the Why are Global Statistics Required? section.
What is the importance of Global Statistics?
It is important to collect Global Statistics as well as partition level statistics when gathering statistics against partitioned tables. Oracle Corporation recommends setting the GRANULARITY parameter to AUTO to gather both types of partition statistics.
See:
Note 236935.1 Global statistics - An Explanation

Oracle(R) Database Performance Tuning Guide
10g Release 2 (10.2)
Part Number B14211-01
Chapter 14 Managing Optimizer Statistics
Section 14.3.1.3 Statistics on Partitioned Objects

Diagnostics
How to Gather Information for Query Tuning Problems
Note 68735.1 Diagnostics for Query Tuning Problems

How to Gather Optimizer Statistics
Note 114671.1 Gathering Statistics for the Cost Based Optimizer

How to Gather Access Path Information for Queries
Note 235530.1 Recommended Method for Obtaining a Formatted Explain Plan
Note 46234.1 Interpreting Explain plan

How to Store and Apply Query Outlines
Note 67536.1 Stored Outline Quick Reference
Note 102311.1 How to Move Stored Outlines for One Application from One Database to Another

How to Gather Trace for Query Tuning Issues
Note 376442.1 Recommended Methods for Obtaining 10046 trace for Tuning



@ Note 225598.1 How to Obtain Tracing of Optimizer Computations (EVENT 10053)
@ Note 242489.1 Transferring Optimizer Statistics to Support
RELATED DOCUMENTS

Note 199083.1 Query Tuning Overview
Note 402983.1 Database Performance FAQ
@ Note 399630.1 INCOMPLETE TOPICS to be added .

Overview Reference for SQL_TRACE, TKProf and Explain Plan

Purpose
This document gives an overview of some of the tools that can be used to discover more about SQL execution.
The article provides information covering SQL_TRACE, TKProf, Explain Plan and Autotrace.

Scope & Application

For all DBAs

Overview Reference for SQL_TRACE, TKProf and Explain Plan

Oracle provides extensive tracing and interpretation tools to assist analysts diagnose and resolve sub-optimal performance issues. This article covers the most important of these and points to detailed reference articles.

Tracing Related Initialization Parameters

Prior to tracing, there are a number of parameters that need to be set so that the trace information is complete.
These parameter should be set up in the "init.ora" file for the particular instance (SID) where you wish to use SQL Trace although they can also be set individually at the session level.


Enable/Disable the collection of timed statistics, such as CPU and elapsed times.

TRUE - Enable timing
FALSE - Disable timing (Default value).
Note that much of the tracing information that can be gathered is rendered useless if TIMED_STATISTICS is set to False and timings are not collected.

Specifies the maximum size of trace files in operating system blocks. The default value for this was 10000 OS blocks in 8i version, and
limited only by the space available in 9i version. If your trace file is truncated then you will see a message similar to:

*** DUMP FILE SIZE IS LIMITED TO 12345 BYTES***
and the size of this parameter should be increased.

Specifies the destination for the trace file. The default value for this parameter is the default destination for oracle dumps on your operating system. These parameters can be dynamically altered using alter system/alter session commands (Note that USER_DUMP_DEST can only be modified at the system level). For example TIMED_STATISTICS can be enabled/disabled dynamically by using the following SQL statement:

ALTER SYSTEM/SESSION SET TIMED_STATISTICS = TRUE/FALSE;
SQL_TRACE

SQL_TRACE is the main method for collecting SQL Execution information in Oracle collecting a wide range of information and statistics that can be used to tune SQL operations.

Enabling SQL_TRACE

The SQL Trace facility can be enabled/disabled for an individual session or at the instance level. If the initialisation Parameter SQL_TRACE is set to TRUE in the init.ora of an instance, then all sessions will be traced.

SQL_TRACE can be set at the instance level by using the initialisation parameter SQL_TRACE:

: Enable/Disable SQL Trace instance wide.
TRUE - Enable statistics to be collected for all sessions.
FALSE - Disable statistics to be collected for all sessions.
SQL_TRACE can also be enabled/disabled at the system/session by issuing the following SQL statement:

ALTER SYSTEM/SESSION SET SQL_TRACE = TRUE/FALSE;
Trace can also be enabled/disabled on other sessions (as well as your own) using the DBMS_SUPPORT package. See Note 62160.1 Tracing Sessions in Oracle7/8 for details.
For more information on how to setup tracing, refer to:

Note 15160.1 Setting SQL Trace in the Oracle Tools.
Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits).


There is also extensive information in the
Oracle9i Database Performance Tuning Guide and Reference Chapter 10: Using SQL Trace and TKProf
Trace Files
Oracle will generate trace (.trc) files for every session where the value of SQL_TRACE=TRUE and write them to the USER_DUMP_DEST destination. If tracing has been enabled for the instance then individual trace files will be generated for each session, unless otherwise disabled. Note that the generated files may be owned by an operating system user other than your own so the necessary privileges will need to be put in place before they can be formatted with TKProf.

Explain Plan


The Explain Plan command generates information that details the execution plan that will be used on a particular query.
It uses a precreated table (PLAN_TABLE) in the current shema to store information about the execution plan chosen by the optimizer.

Creating the Plan Table

The plan table is created using the script utlxplan.sql. This script is typically found under the Oracle Home in the rdbms/admin directory.

On Unix its location will be:

$ORACLE_HOME/rdbms/admin

On WindowsNT/2000:
%ORACLE_HOME%\rdbms\admin

This script creates the output table, called PLAN_TABLE, for holding the output of the Explain plan Command.
Note that the exact structure of the plan table can change with different release as new features are introduced.

Populating the Plan Table

The plan table is populated using the explain plan command:

SQL> EXPLAIN PLAN for select * from emp where empno=1000;
This command inserts the execution plan of the SQL statement into the plan table. It is also possible to adds the name tag to the explain information by using the set statement_id clause.
Displaying the Execution Plan

Once the table has been populated, the explain information needs to be retrieved and formatted. There are a large number of scripts available to format the plan table data. Some of the most popular are noted below:

Supplied Scripts:

$ORACLE_HOME/rdbms/admin/utlxpls.sql: script to format serial explain plans
$ORACLE_HOME/rdbms/admin/utlxplp.sql: script to format parallel explain plans
Articles:
Note 31101.1 Obtaining Formatted Explain Plan Output
Note 39294.1 Formatted Select of PLAN_TABLE for EXPLAIN PLAN command
Note 39341.1 Automatic Explain Plan
Note 1019631.6 SCRIPT: SCRIPT TO SIMPLIFY THE USE OF EXPLAIN PLAN
Interpretation of Explain Plan
This is a complex topic and is covered in detail in the following article:

Note 46234.1 Interpreting Explain Plan
There is also extensive information in the
Oracle9i Database Performance Tuning Guide and Reference Chapter 9: Using EXPLAIN PLAN
AUTOTRACE

The autotrace facility in SQL*Plus allows analysts to view the execution plan and some useful statistics for a SQL statement within a SQL*Plus session.
This option was introduced with 7.3 version of Oracle.

Autotrace needs to be initiated in the SQL*Plus session prior to executing the statement.
The Autotrace command is:

SET AUTOTRACE [OPTIONS] [EXPLAIN/STATISTICS]
For a detailed explanation of AUTOTRACE functions see:
Note 43214.1 AUTOTRACE option in 7.3
As with the EXPLAIN PLAN command, to obtain an execution plan the PLAN_TABLE must be created in the user's schema prior to autotracing.
Example

SQL> set autotrace traceonly explain
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
To enable viewing of STATISTICS data, the autotracing user must have access to dynamic performance tables. To achieve this, grant PLUSTRACE role to the user. The PLUSTRACE role is created by the plustrce.sql script.
On Unix the location is:

$ORACLE_HOME/sqlplus/admin

On WindowsNT/2000:

%ORACLE_HOME%\sqlplus\admin

This script must be run by the SYS user. A DBA user can then grant the role to the users who wish to use the the AUTOTRACE option.

Refer to:

Note 1055431.6 ORA-01919 usingAUTOTRACE in SQL*Plus
Extensive reference information regarding the autotrace facility can be found in the
SQL*Plus Users Guide and Reference Release 9.0.1
TKProf

The TKProf facility accepts as input a SQL trace file and produces a formatted output file. For the full syntax of TKProf see the Oracle Server Tuning Manual. If TKProf is invoked with no arguments, an online help is displayed.

Basic Syntax of TKProf

TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename]
filename_source The trace file that needs to be formatted
filename_output The output file where the formatted file will be written
'EXPLAIN' This option causes TKProf to generate an execution plan for each SQL statement in the trace file as though it was been parsed at the time the TKProf is being run. TKProf connects as the user [username] and uses the EXPLAIN PLAN command to generate an execution plan for each traced SQL statement. These explain plans are written to the output file. Note that in later releases, the raw trace file automatically records the explain plan that was used at execution time and TKProf will automatically output this plan. If the EXPLAIN option is selected then two explain plans appear in the TKProf output. The first explain plan is the plan that was used at execution time. The second plan the plan that was generated dynamically at the time the TKProf runs. If session settings etc have changed between the execution time and when the trace file is TKProffed then the first and second plans may differ.
'TABLE' Specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table already exists, TKProf deletes its rows then uses it for the EXPLAIN PLAN command and then deletes its rows. If this table does not exist, TKProf creates, uses, then drops it. This parameter is ignored if the EXPLAIN parameter isn't used.
'SYS' Enables and disables the listing of SQL statements issued by the user SYS (recursive SQL statements) into the output file. The default value is YES.


For more information on using TKProf see:

Note 32951.1 TKPROF Interpretation
Note 29012.1 Quick Reference TKPROF usage
Note 41634.1 TKPROF and Problem Solving
There is also extensive information in the
Oracle9i Database Performance Tuning Guide and Reference Chapter 10: Using SQL Trace and TKProf

Tkprof Interpretation

PURPOSE
This document gives general advice on the use of TKPROF.

SCOPE & APPLICATION
For users needing advice on how to use TkProf.

Tkprof is an executable that 'parses' Oracle trace files to produce more
readable output. Remember that all the information in TkProf is available
from the base trace file.

If you have a system that is performing badly, a good way to identify
problem SQL statements is to trace a typical user session and then use TkProf
to format the output using the sort functions on the tkprof command
line.

There are a huge number of sort options that can be accessed by simply
typing 'TkProf' at the command prompt. A useful starting point is the
'fchela' sort option which orders the output by elapsed time fetching (remember
that timing information is only available with timed_statistics set to true
in the "init.ora" file). The resultant .prf file will contain the most time
consuming SQL statement at the start of the file.

Another useful parameter is sys. This can be used to prevent SQL statements
run as user SYS from being displayed. This can make the output file much
shorter an easier to manage.

Remember to always set the TIMED_STATISTICS parameter to TRUE when tracing
sessions as otherwise no time based comparisons can be made.


Interpreting TkProf Output Guidelines
=====================================

Column Meanings
===============

call : Statisics for each cursor's activity are divided in to 3 areas:

Parse: statisitics from parsing the cursor. This
includes information for plan generation etc.
Execute: statisitics for the exection phase of a cursor
Fetch : statistics for actually fetching the rows

count : number of times we have performed a particular activity on this
particular cursor

cpu: cpu time used by this cursor

elapsed: elapsed time for this cursor

disk: This indicates the number of blocks read from disk. Generally you want
to see blocks being read from the buffer cache rather than disk.

query : This column is incremented if a buffer is read in Consistent mode.
A Consistent mode buffer is one that has been generated to give
a consistent read snapshot for a long running transaction. The buffer
actually contains this status in its header.

current: This column is incremented if a buffer found in the buffer cache
that is new enough for the current transaction and is in current mode
(and it is not a CR buffer). This applies to buffers that have been
read in to the cache as well as buffers that already exist in the
cache in current mode.

rows: Rows retrieved by this step


Explain plan
============

Firstly, we advise that the autotrace feature of SQL*Plus be used
on statements rather than using TkProf mainly because the TkProf output
can be confusing with regard to whether the Rule or Cost Based optimizer
has been used.

Because TkProf explain plan does not show any costs or statistics, it is
sometimes not possible to tell definitively which optimizer has been used.

That said, the following output from Tkprof explain plan is useful.
The Rows column next to the explain plan output shows the number of
rows processed by that particular step. The information is gathered from the
STAT lines for each cursor in the raw trace output.

Remember that if the cursor is not closed then you will not see any output.
Setting SQL_TRACE to false DOES NOT close PL/SQL child cursors.
Cursors are closed in SQL*Plus immediately after execution.


TkProf Examples and Discussion
==============================

Examples:

Step 1 - Look at the totals at the end of the tkprof output
===========================================================

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

| call | count | cpu | elapsed | disk | query | current | rows |
|---------|-------|------|---------|---------|--------|---------|--------|
| Parse | [A] 7 | 1.87 | 4.53 | 385 |[G] 553 | 22 | 0 |
| Execute | [E] 7 | 0.03 | 0.11 | [P] 0 |[C] 0 | [D] 0 | [F] 0 |
| Fetch | [E] 6 | 1.39 | 4.21 | [P] 128 |[C] 820 | [D] 3 | [F] 20 |
--------------------------------------------------------------------------

Misses in library cache during parse: 5
Misses in library cache during execute: 1

8 user SQL statements in session.
12 internal SQL statements in session.
[B] 54 SQL statements in session.
3 statements EXPLAINed in this session.

1. Compare [A] & [B] to spot over parsing. In this case we
have 7 parses for 54 statements which is ok.

2. You can use [P], [C] & [D] to determine the hit ratio.

Hit Ratio is logical reads/physical reads:

Logical Reads = Consistent Gets + DB Block Gets
Logical Reads = query + current
Logical Reads = Sum[C] + Sum[D]
Logical Reads = 0+820 + 0+3
Logical Reads = 820 + 3
Logical Reads = 823

Hit Ratio = 1 - (Physical Reads / Logical Reads)
Hit Ratio = 1 - (Sum[P] / Logical Reads)
Hit Ratio = 1 - (128 / 823)
Hit Ratio = 1 - (0.16)
Hit Ratio = 0.84 or 84%

3. We want fetches to be less than the number of rows as this
will mean we have done less work (array fetching).
To see this we can compare [E] and [F].

[E] = 6 = Number of Fetches
[F] = 20 = Number of Rows

So we are doing 6 fetches to retrieve 20 rows - not too bad.
If arrayfetching was configured then rows could be retrieved with
less fetches.

Remember that an extra fetch will be done at the end to check that
the end of fetch has been reached.

4. [G] Shows reads on the Dictionary cache for the statements.

- this should not be a problem on Oracle7.
In this case we have done 553 reads from the
Library cache.

STEP 2 - Examine statements using high resource
===============================================

update ...
where ...

| call | count | cpu | elapsed | disk | query | current | rows |
|---------|-------|-----|---------|------|---------|---------|--------|
| Parse | 1 | 7 | 122 | 0 | 0 | 0 | 0 |
| Execute | 1 | 75 | 461 | 5 | [H] 297 | [I] 3 | [J] 1 |
| Fetch | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
-----------------------------------------------------------------------

[H] shows that this query is visiting 297 blocks to find the rows to
update
[I] shows that only 3 blocks are visited performing the update
[J] shows that only 1 row is updated.

297 block to update 1 rows is a lot.
Possibly there is an index missing?


STEP 3 - Look for over parsing
==============================

select ...

| call | count | cpu | elapsed | disk | query | current | rows |
|---------|-------|---------|---------|------|--------|---------|-------|
| Parse | [M] 2 | [N] 221 | 329 | 0 | 45 | 0 | 0 |
| Execute | [O] 3 | [P] 9 | 17 | 0 | 0 | 0 | 0 |
| Fetch | 3 | 6 | 8 | 0 | [L] 4 | 0 | [K] 1 |
-------------------------------------------------------------------------

Misses in library cache during parse: 2 [Q]

[K] is shows that the query has returned 1 row.
[L] shows that we had to read 4 blocks to get this row back.
This is fine.

[M] show that we are parsing the statement twice - this is not desirable
especially as the cpu usage is high [N] in comparison to the execute
figures : [O] & [P]. [Q] shows that these parses are hard parses. If
[Q] was 1 then the statemnent would have had 1 hard parse followed by
a soft parse (which just looks up the already parsed detail in the
library cache). See Note 32895.1 for more details.


This is not a particularly bad example since the query has only been
executed a few times. However excessive parsing should be avoided as far
as possible by:

o Ensuring that code is shared:

- use bind variables
- make shared pool large enough to hold query definitions in memory
long enough to be reused.


NOTE:
====
We need to ensure, we do not parse a >=9i version 10046 raw trace with an <= 8i tkprof utility.
This is because the timings in raw trace file for <=8i versions and >=9i versions are in
centiseconds and microseconds respectively.


SEARCH WORDS
============

EXPLAIN; PARSE; PLAN; STATEMENT; TKPROF;

AUTOTRACE Option in sqlplus

Tracing Statements
------------------

You can automatically get a report on the execution path used by the SQL
optimizer and the statement execution statistics. The report is generated
after successful SQL DML (Data Manipulation Language - that is, SELECT, DELETE,
UPDATE and INSERT) statements. It is useful for monitoring and tuning the
performance of these statements.


Controlling the Report
----------------------

You can control the report by setting the AUTOTRACE system variable.

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the
default.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer
execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
statement execution statistics.
SET AUTOTRACE ON - The AUTOTRACE report includes both the
optimizer execution path and the SQL
statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the
printing of the user's query output, if any.

To use this feature, you must have the PLUSTRACE role granted to you and a
PLAN_TABLE table created in your schema. For more information on the PLUSTRACE
role and PLAN_TABLE table, see the AUTOTRACE variable of the SET command in
Chapter 6 of the SQL*Plus Guide.


Execution Plan
--------------

The Execution Plan shows the SQL optimizer's query execution path.
Each line of the Execution Plan has a sequential line number. SQL*Plus also
displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order:

Column Name Description
------------------------------------------------------------------------

ID_PLUS_EXP Shows the line number of each execution step.
PARENT_ID_PLUS_EXP Shows the relationship between each step and its
parent. This column is useful for large reports.
PLAN_PLUS_EXP Shows each step of the report.
OBJECT_NODE_PLUS_EXP Shows the database links or parallel query servers
used.

The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter:

SQL> COLUMN PARENT_ID_PLUS_EXP NOPRINT

The default formats can be found in the site profile (for example, glogin.sql).

The Execution Plan output is generated using the EXPLAIN PLAN command. For
information about interpreting the output of EXPLAIN PLAN, see the
Oracle7 Server Tuning guide.

The following is an example of tracing statements for performance statistics and
query execution path.

If the SQL buffer contains the following statement:

SQL> SELECT D.DNAME, E.ENAME, E.SAL, E.JOB
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO

The statement can be automatically traced when it is run:

SQL> SET AUTOTRACE ON
SQL> /

DNAME ENAME SAL JOB
-------------- ---------- ---------- ---------
ACCOUNTING CLARK 2450 MANAGER
ACCOUNTING KING 5000 PRESIDENT
ACCOUNTING MILLER 1300 CLERK
RESEARCH SMITH 800 CLERK
RESEARCH ADAMS 1100 CLERK
RESEARCH FORD 3000 ANALYST
RESEARCH SCOTT 3000 ANALYST
RESEARCH JONES 2975 MANAGER
SALES ALLEN 1600 SALESMAN
SALES BLAKE 2850 MANAGER
SALES MARTIN 1250 SALESMAN
SALES JAMES 950 CLERK
SALES TURNER 1500 SALESMAN
SALES WARD 1250 SALESMAN

14 rows selected.

Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'DEPT'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'EMP'

Statistics
----------------------------------------------------------
148 recursive calls
4 db block gets
24 consistent gets
6 physical reads
43 redo size
591 bytes sent via SQL*Net to client
256 bytes received via SQL*Net from client
33 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

Note: The output may vary depending on the version of the server to
which you are connected and the configuration of the server.


References
-----------------------------------------------------------

SQL*PLus User's Guide and Reference Release 3.3.
Autotrace documented on Page 6-77. Part No. A42562-1.

Oracle7 Server Tuning Release 7.3. Part No. A32537-1.
Explain Plan documented on Page 6-18.

TKProf Simplistic Overview

TKPROF and Problem Solving
==========================

Purpose
-------
This document discusses the use of the following:
* SQL TRACE
* EXPLAIN PLAN
* TKPROF


Scope & Application
--------------------
For users wanting to obtain a query plan and/or execution times for their
queries.


Related Documents
------------------
The majority of the material covered in this document can be found in
the following manuals:
* Oracle7 Server SQL Reference
* Oracle7 Server Tuning


Introduction
------------
Much information can be gathered about the performance of an Oracle
database from the dynamic 'V$' views, as these are documented in the
Oracle7 Server Manual. Oracle also provides additional performance
diagnostic tools to assist in monitoring and tuning the server.

The reader should be familiar with the concepts of the Oracle Server
initialization file and startup parameters. Most of the suggestions
described in this document should be conducted by an experienced DBA.


Overview Of SQL TRACE
---------------------
The diagnostic tool 'sql trace' provides performance information about
individual SQL statements and generates the following statistics for
each statement:

* parse, execute, and fetch counts
* CPU and elapsed times
* physical reads and logical reads
* number of rows processed
* misses on the library cache

This information is input to a trace (.trc) file and sql trace can be
enabled/disabled for a session or an instance.


Setting Initialization Parameters
---------------------------------
The following parameters need to be set up in the "init.ora" file
for the particular instance (SID) that you wish to use SQL Trace:

* SQL_TRACE
+ Enable/Disable SQL Trace for the instance.

Values
------
TRUE Enable statistics to be collected
for all sessions.
FALSE Disable statistics to be collected
for all sessions.

* TIMED_STATISTICS
+ Enable/Disable the collection of timed
statistics, such as CPU and elapsed times.

Values
------
TRUE Enable timing (we usually recommend this)
FALSE Default value.

* MAX_DUMP_FILE_SIZE
+ Specifies the maximum size of trace
files operating system blocks.

Values
------
The default value for this is 500 but if your
trace file is truncated then increase this value.

* USER_DUMP_DEST
+ Specifies the destination for the trace file.

Values
------
The default value for this parameter is the default
destination for system dumps on your operating system.


Enabling SQL Trace
------------------
The SQL Trace facility can either be enabled/disabled for an individual
session or the instance.

* To enable the SQL trace facility for your session
issue the following SQL statement:

ALTER SESSION
SET SQL_TRACE = TRUE;

* To disable the SQL trace facility for your session
issue the following SQL statement:

ALTER SESSION
SET SQL_TRACE = FALSE;


Enabling TIMED_STATISTICS
-------------------------
The parameter TIMED_STATISTICS can be enabled/disabled dynamically by using

ALTER SYSTEM
SET TIMED_STATISTICS = TRUE;

or
ALTER SESSION
SET TIMED_STATISTICS = TRUE;

To disable set TIMED_STATISTICS = FALSE:

SQL Trace Facility for an Instance
----------------------------------
Note that if the initialization parameter SQL_TRACE=TRUE, then statistics
will be gathered for ALL sessions. If the facility has been enabled for
the instance, it may be disabled for an individual session by issuing by
the above SQL statement.


Trace Files
-----------
Oracle will generate trace (.trc) files for every session where the value
of SQL_TRACE = TRUE and write them to the USER_DUMP_DEST destination.
If tracing has been enabled for the instance then individual trace files
will be generated for each session, unless otherwise disabled (see above).
Note, that the generated files may be owned by an operating system user
other than your own so you may have to get this user to grant you access
before you can use TKPROF to format them.


Using TKPROF
------------
The TKPROF facility accepts as input an SQL trace file and produces a
formatted output file. Note that TKPROF can be run on individual or
appended trace files to produce the formatted output file.

* For the full syntax of TKPROF see
Oracle7 Server Tuning Manual.

* Invoke TKPROF with no arguments
and online help is displayed.


Syntax of TKPROF
----------------

TKPROF command ::=

>>-- TKPROF filename1 filename2 ---------------------------------------------->
| |
+- SORT = ---------------------------------+
| |
+-- OPTION --+
| |
| +---- , ----+ |
| V | |
|__( OPTION )__|

>----------------------------------------------------------------------------->
| | | | | |
+-- PRINT = integer --+ +-- INSERT = filname3 --+ +-- SYS = ---------+
| |
+- YES -+
| |
+- NO --+

>----------------------------------------------------------------------------->
| |
+---------------------------------------- EXPLAIN = user/password ------+
| |
+---- TABLE = schema.table ----+

>----------------------------------------------------------------------------><
| |
+---- RECORD = filname ----+


The Basics Of TKPROF
--------------------

For the purpose of this document we will only describe some of the
fundamental arguments for TKPROF:

* 'filename1'
Specifies the input file, a trace file containing
statistics produced by the SQL trace facility.
This file can be either a trace file produced for a
single session or a file produced by appending together
together individual trace files from multiple sessions.

* 'filename2'
Specifies the file to which TKPROF writes its
formatted output.

* 'EXPLAIN'
Determines the execution plan for each SQL statement in
in the trace file and writes these execution plans to
the output file. TKPROF determines execution plans by
issuing the EXPLAIN PLAN command after connecting to
Oracle with the user and password specified in this
parameter. The specified user must have CREATE SESSION
privileges.

* 'TABLE'
Specifies the schema and name of the table into which
TKPROF temporarily places execution plans before writing
them to the output file. If the specified table already
exists, TKPROF deletes its rows then uses it for the
EXPLAIN PLAN command and then deletes its rows. If this
table does not exist, TKPROF creates, uses, then drops it.
The specified user must be able to issue INSERT, SELECT,
and DELETE statements against the table. If the table does
not already exist, the user must also be able to issue
CREATE TABLE and DROP TABLE statements. This option
allows multiple individuals to run TKPROF concurrently with
the same user in the EXPLAIN value. These individuals can
specify different TABLE values and avoid destructively
interfering with each other's processing on the temporary
plan table. If you use the EXPLAIN parameter without the
TABLE parameter, TKPROF uses the table PROF$PLAN_TABLE in
the schema of the user specified by the EXPLAIN parameter.
If you use the TABLE parameter without the EXPLAIN parameter,
TKPROF ignores the TABLE parameter.

* 'SYS'
Enables and disables the listing of SQL statements issued
by the user SYS, or recursive SQL statements into the
output file. The default value of YES causes TKPROF to
list these statements. The value of NO causes TKPROF to
to omit them.


Simple Example
--------------

This example shows TKPROF being run to format a trace file named
"dsdb2_ora_18468.trc" and writing it to a formatted output file
named "dsdb2_trace.out".

TKPROF dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER

Note that if the command is likely to be longer than a single line on your
terminal screen you may have to use continuation characters.


The Parameters In This Example:
-------------------------------

The EXPLAIN value causes TKPROF to connect as the user SCOTT and use the
EXPLAIN PLAN command to generate the execution plan for each traced SQL
statement. The SYS parameter with the value of NO causes TKPROF to omit
recursive SQL statements from the output file.


Interpretting TKPROF Output
===========================

The EXPLAIN PLAN Command
------------------------

The EXPLAIN PLAN command displays the execution plan chosen by the
Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements.
A statement's execution plan is the sequence of operations that Oracle
performs to execute the statement. By examining the execution plan, you
can see exactly how Oracle executes your SQL statement. This information
can help you determine whether the SQL statement you have written takes
advantage of the indexes available. For the syntax of the EXPLAIN PLAN
command, see the Oracle7 Server SQL Reference Manual.


Creating the Output Table
-------------------------

Before you can issue an EXPLAIN PLAN statement, there must exist a table
to hold its output, you do either of the following:

* Run the SQL script "UTLXPLAN.SQL" to create a sample
output table called PLAN_TABLE in your schema.

* Issue a CREATE TABLE statement to create an output
with any name you choose. You can then issue an
EXPLAIN PLAN statement and direct its output to this
table. Any table used to store the output of the
EXPLAIN PLAN command must have the same column names
and datatypes as the PLAN_TABLE.


SQL Trace Facility Statistics
-----------------------------

TKPROF lists the statistics for a SQL statement returned by the SQL
trace facility in rows and columns. Each row corresponds to one of
three steps of SQL statement processing:

* PARSE
This step translates the SQL statement into an execution plan.
This includes checks for proper security authorization and
checks or the existence of tables, columns, and other
referenced objects.

* EXECUTE
This step is the actual execution of the statement by Oracle.
For INSERT, UPDATE, and DELETE statements, this step modifies
the data. For SELECT statements, the step identifies the
selected rows.

* FETCH
This step retrieves rows returned by a query.
Fetches are only performed for SELECT statements.

The step for which each row contains statistics is identified by the value of
the call column. The other columns of the SQL trace facility output are
combined statistics for all parses, all executes, and all fetches of a
statement:

COUNT
Number of times a statement was parsed, executed, or fetched.

CPU
Total CPU time in seconds for all parse, execute, or fetch calls
for the statement.

ELAPSED
Total elapsed time in seconds for all parse, execute, or fetch calls
for the statement.

DISK
Total number of data blocks physically read from the datafiles on
disk for all parse, execute, or fetch calls.

QUERY
Total number of buffers retrieved in consistent mode for all parse,
execute, or fetch calls. Buffers are usually retrieved in consistent
mode for queries.

CURRENT
Total number of buffers retrieved in current mode. Buffers are often
retrieved in current mode for INSERT, UPDATE, and DELETE statements.

+ The sum of QUERY & CURRENT is he total number of buffers accessed.

ROWS
Total number of rows processed by the SQL statement. This total does
not include rows processed by subqueries of the SQL statement.

For SELECT statements, the number of rows returned appears for the fetch step.
For UPDATE, DELETE, and INSERT statements, the number of rows processed
appears for the execute step.


Example Output (Partial) File
-----------------------------

TKPROF: Release 7.2.2.3.0 - Production on Wed Aug 7 11:29:43 1996

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

Trace file: .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
********************************************************************************

DELETE FROM RM$HASH_ELMS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 29 12.04 12.61 6786 6853 108 19
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 12.04 12.61 6786 6853 108 19

Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 9 (DES12A) (recursive depth: 3)

Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT HINT: CHOOSE
16 TABLE ACCESS (FULL) OF 'RM$HASH_ELMS'

*****************************END-OF-EXAMPLE-FILE********************************


Resolution of Statistics
------------------------

Since timing statistics have a resolution of one hundredth of a second,
any operation on a cursor that takes a hundredth of a second or less may
not be timed accurately. Keep this in mind when interpreting statistics.
In particular, be careful when interpreting the results from simple queries
that execute very quickly.


Recursive Calls
---------------

Sometimes to execute a SQL statement issued by a user, Oracle must issue
additional statements. Such statements are called 'recursive calls' or
'recursive SQL statements'. For example, if you insert a row into a table
that does not have enough space to hold that row, Oracle makes recursive
calls to allocate the space dynamically. Recursive calls are also generated
when data dictionary information is not available in the data dictionary
cache and must be retrieved from disk.

If recursive calls occur while the SQL trace facility is enabled, TKPROF
produces statistics for the recursive SQL statements and clearly marks them
as recursive SQL statements in the output file. Note that the statistics for
a recursive SQL statement are included in the listing for that statement,
not in the listing for the SQL statement that caused the recursive call. So
when you are calculating the total resources required to process a SQL
statement, you should consider the statistics for that statement as well as
those for recursive calls caused by that statement.

+ Note that setting the TKPROF command line parameter to NO
suppresses the listing of recursive calls in the output in
the output file.


Execution Plan
--------------

If you specify the EXPLAIN parameter on the TKPROF command line, TKPROF
uses the EXPLAIN PLAN command to generate the execution plan of each SQL
statement traced. TKPROF also displays the number of rows processed by
each step of the execution plan.