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.
Parameter:TIMED_STATISTICS
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.
Parameter:MAX_DUMP_FILE_SIZE
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.
Parameter:USER_DUMP_DEST
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 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:
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.
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.
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.
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.3As
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
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]
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
No comments:
Post a Comment