SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference (Doc ID 199081.1)

In this Document Purpose Scope Details Overview Reference for SQL_TRACE, TKProf and Explain Plan Communities Tracing Related Initialization Parameters SQL_TRACE (10046 trace) Enabling SQL_TRACE Trace Files TKProf Basic Syntax of TKProf Explain Plan Creating the Plan Table Populating the Plan Table Displaying the Execution Plan Interpretation of Explain Plan Autotrace References APPLIES TO: Oracle Database - Standard Edition - Version 7.0.16.0 and later Oracle Database - Enterprise Edition - Version 7.0.16.0 and later Oracle Database - Personal Edition - Version 7.1.4.0 and later Information in this document applies to any platform. ***Checked for relevance on 13-Jan-2014*** 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. This information is provided to supplement information in: Oracle® Database SQL Tuning Guide 12c Release 1 (12.1) E15858-15 18.3 Understanding SQL Trace and TKPROF http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_trace.htm#TGSQL792 SCOPE For all DBAs DETAILS NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. 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. Communities This article outlines a number of different areas with regard to tracing, but you might find that someone in the community at large has investigated specific areas of interest for you. Communities have been set up for My Oracle Support (MOS) users moderated by Oracle. The goal of this community is to exchange database related Tuning knowledge and concepts. For performance related issues we have two communities: Database Tuning and SQL Performance. The SQL Performance community is likely to be the best match for TKProf discussions. 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 "initSID.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. Used to 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. Note: Most of these parameters can be dynamically altered using alter system/alter sessioncommands For example TIMED_STATISTICS can be enabled/disabled dynamically by using the following SQL statement: ALTER SYSTEM/SESSION SET TIMED_STATISTICS = TRUE/FALSE; (Note that USER_DUMP_DEST can only be modified at the system level). SQL_TRACE (10046 trace) SQL_TRACE is the main method for collecting SQL Execution information in Oracle. It records 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. Note that using this initialization parameter to enable the SQL trace facility for the entire instance can have a severe performance impact. See: Oracle® Database Reference 12c Release 1 (12.1) E17615-20 SQL_TRACE http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10208.htm#REFRN10208 Generally, Oracle recommends that you use the DBMS_MONITOR and DBMS_SESSION packages to enable/disable Tracing on other sessions (as well as your own). See: Document 62160.1 Tracing Sessions in Oracle Using the DBMS_SUPPORT Package 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; In 11g and higher, sql_trace is also an event and can be set with event syntax: SQL> oradebug doc event name sql_trace sql_trace: event for sql trace Usage ------- sql_trace wait < false | true >, bind < false | true >, plan_stat < never | first_execution | all_executions | adaptive >, level So you could use it as follows to enable SQL_TRACE requesting bind information: alter session set events 'sql_trace bind=true'; or bind and wait information (notice comma separated): alter session set events 'sql_trace bind=true, wait=true'; Further trace can be limited to a set of SQL_IDs if you include a filter for it. E.g. alter session set events 'sql_trace [sql: sql_id=g3yc1js3g2689 | sql_id=7ujay4u33g337]bind=true, wait=true'; See "oradebug doc event" for the full event syntax: SQL> oradebug doc event Event Help: *********** ... 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. For more information on how to setup tracing, refer to: Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning Document 15160.1 Setting SQL Trace in the Oracle Tools. Document 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits) There is also extensive information in the: Oracle® Database SQL Tuning Guide 12c Release 1 (12.1) E15858-15 18.3 Understanding SQL Trace and TKPROF http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_trace.htm#TGSQL792 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. 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 is 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: Document 199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference Document 41634.1 TKProf Basic Overview Document 29012.1 QREF: TKPROF Usage - Quick Reference Document 760786.1 TKProf Interpretation (9i and above) Document 214106.1 Using TKProf to compare actual and predicted row counts There is also extensive information in : Oracle® Database SQL Tuning Guide 12c Release 1 (12.1) E15858-15 18.3 Understanding SQL Trace and TKPROF http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_trace.htm#TGSQL792 As an alternative to TKProf, Trace Analyzer (TRCANLZR) can be used. See: Document 224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 Explain Plan The Explain Plan command generates information that details the execution plan that will be used on a particular query. The use of the explain plan command itself has largely been superceded by tools such as SQLT. See: Document 215187.1 SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly But is still useful for looking at access paths for queries. Note that the EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements only. For more details see: Oracle® Database SQL Tuning Guide 12c Release 1 (12.1) E15858-15 Chapter 8 Generating and Displaying Execution Plans http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL271 The Explain plan command uses a precreated table (PLAN_TABLE) in the current schema 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_idclause. 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: Document 235530.1 How to Obtain a Formatted Explain Plan - Recommended Methods Document 31101.1 Obtaining Formatted Explain Plan Output Document 39294.1 Formatted Select of PLAN_TABLE for EXPLAIN PLAN command Document 39341.1 Automatic Explain Plan Document 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: Document 46234.1 Interpreting Explain Plan There is also extensive information in the: Oracle® Database SQL Tuning Guide 12c Release 1 (12.1) E15858-15 Chapter 8 Generating and Displaying Execution Plans http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_genplan.htm#TGSQL271 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: Document 43214.1 AUTOTRACE option in 7.3 Document 31101.1 SCRIPT: DIAG - Obtaining Formatted Explain Plan Output with Autotrace 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.sqlscript. 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: Document 1055431.6 ORA-01919 Using AUTOTRACE in SQL*Plus Extensive reference information regarding the autotrace facility can be found in: SQL*Plus® User's Guide and Reference Release 12.1 E18404-12 Chapter 8 Tuning SQL*Plus Section: Controlling the Autotrace Report http://docs.oracle.com/cd/E16655_01/server.121/e18404/ch_eight.htm#SQPUG535 REFERENCES NOTE:32951.1 - TKProf Interpretation (9i and below) NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues NOTE:43214.1 - AUTOTRACE Option in SQL*Plus NOTE:46234.1 - Interpreting Explain plan NOTE:62160.1 - Tracing Sessions in Oracle Using the DBMS_SUPPORT Package NOTE:760786.1 - TKProf Interpretation (9i and above) NOTE:199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference NOTE:39341.1 - SCRIPT: Automatic Explain plan NOTE:1019631.6 - Script: Script to Simplify the Use of Explain Plan NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)" NOTE:215187.1 - All About the SQLT Diagnostic Tool NOTE:235530.1 - * How to Obtain a Formatted Explain Plan - Recommended Methods NOTE:29012.1 - QREF: TKPROF Usage - Quick Reference