How to Obtain Tracing of Optimizer Computations (EVENT 10053) (文档 ID 225598.1)

来源：互联网发布：nba2konline奥登数据编辑：程序博客网时间：2024/06/07 08:05

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
Checked for relevance on 03-Jan-2012

PURPOSE

To provide instructions on obtaining optimizer tracing to enable Oracle Support Engineers to diagnose optimizer decisions and behaviour.

SCOPE

Customers needing to obtain 10053 trace under the direction of Oracle Support Engineers. The article explain a straightforward method of obtaining this trace.

DETAILS

Occasionally, support may wish to examine the internal decisions made by the Cost Based Optimizer (CBO) and will request a trace of optimizer decisions using event 10053. Some methods detailing how to gather this trace are illustrated below.

Note: This event has no impact on queries optimized by the Rule Based Optimizer (RBO).

Note: In 11g. there is an open bug as explained in Document 730159.1 Optimizer Event 10053 Disabled in Oracle11g when TRACE_ENABLED=FALSE
Please set TRACE_ENABLED=TRUE in order to enable 10053 tracing

Also, make sure that max_dump_file_size=unlimited or the trace file may be truncated

DUMP_TRACE: DBMS_SQLDIAG packaged procedure (11g R2 onwards)

Oracle Database 11g, introduced a new diagnostic events infrastructure, which greatly simplifies the task of generating a 10053 trace for a specific SQL statement.

Starting in 11g Release 2, you can use this to generate an Optimizer trace for any SQL statement in the cursor cache without having to execute it. The DBMS_SQLDIAG package has been extended to include a procedure called DUMP_TRACE. This procedure, just requires SQL_ID of the statement you wish to generate an Optimizer trace for but it doesn’t require you to re-execute the statement. The procedure will automatically trigger a hard parse of the statement to generate the trace.

To use this Package, first find the SQL_ID:

SQL> column sql_text format a30SQL> select '10053_test' from dual;SQL>  select sql_id, child_number, sql_text from v$sql       where sql_text like '%10053_test%';SQL_ID        CHILD_NUMBER SQL_TEXT------------- ------------ ------------------------------cjk13xfm8ybh7            0 select '10053_test' from dual

Then you can use the following syntax using the SQL_ID for your SQL (in my case 'cjk13xfm8ybh7' above):

SQL> execute DBMS_SQLDIAG.DUMP_TRACE(-
    p_sql_id=>'cjk13xfm8ybh7', -
    p_child_number=>0, -
    p_component=>'Optimizer', -
    p_file_id=>'TEST');

PL/SQL procedure successfully completed.

You can use the following to identify the tracefile produced:

SQL> ALTER SESSION SET tracefile_identifier = 'OPTIMIZER_TRACE';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/..../v11203_ora_1827_OPTIMIZER_TRACE.trc

Note: Be sure to only use valid characters in the p_file_id string - numeric characters are not allowed.

NOTE: The parse environment uses information captured in V$SQL_OPTIMIZER_ENV which does not record all information about the environment that parsed the query such as NLS settings. The result of this is that trace extracted from this may not always generate a trace that is truly representative of what happens when parsed from an application client. For example, if you parse from a client with NLS_SORT set differently to the Database then the application plan may be different to the database and so a trace generated from V$SQL_OPTIMIZER_ENV may cause confusion when the plan for a given cursor in the application is different to the one extracted.

For more details see: Capturing 10053 trace files continued

Alter Session Methods

Note: When using Alter session methods you need to ensure that the statement is parsed while the event it set. If this is not done then trace may not be generated or may be missing and useful information. You can ensure a re-parse this by either changing the formatting of the SQL (add spaces, make some characters uppercase) or adding a comment to the SQL. e.g:

SQL> select /* 10053 trace #1 */

Using New Diagnostic Events Infrastructure (11g Onwards)

The new diagnostic events infrastructure, allows tracing to be enabled for a specific SQL ID instead of turning it on for the entire session. Oracle will then capture a 10053 trace for the corresponding SQL statement when it is issued in that session.

Using the SQL_ID from before:

SQL> alter session set max_dump_file_size = unlimited;
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:cjk13xfm8ybh7]'; 

SQL> --Execute the query --

To disable the trace

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';

See: How do I capture a 10053 trace for a SQL statement called in a PL/SQL package?

Traditional Alter Session Methods

Query With Literals

Ensure that a PLAN_TABLE exists in the schema of the user that will be used to trace the query. If the PLAN_TABLE does not exist then it can be created by running the utlxplan.sql script which resides in the rdbms/admin under the Oracle home $ORACLE_HOME/rdbms/admin/utlxplan.sql (on Unix systems).

Connect to Oracle using SQL*Plus as the appropriate user and issue the following series of commands:

SQL> alter session set max_dump_file_size = unlimited;
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered.

SQL> EXPLAIN PLAN FOR --SQL STATEMENT--;

Explained.

SQL> exit

Query With Binds

Connect to Oracle using SQL*Plus as the appropriate user and issue the following series of commands. Set up as many binds as are needed by the query:

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered.

-- set up binds. 

SQL> variable a number
SQL> variable b varchar2(10)

-- assign values to binds

SQL> begin
2 :a:=20;
3 :b:='CLERK';
4 end;
5 /

SQL> select empno, ename, mgr
2 from emp
3 where deptno = :a
4 and job = :b
/

SQL> exit

Finding the trace file

With either case, a trace file will be generated in the default trace location. You can find the location where trace would go if you traced the current sesion using a select such as:

  SELECT value
  FROM v$diag_info
  WHERE name='Default Trace File';

On earlier versions the location is defined by the parameter <Parameter:user_dump_dest>.

To identify the correct trace file, search for the the relevant --SQL STATEMENT--.
This will be followed by a section headed "PARAMETERS USED BY THE OPTIMIZER". e.g:

*** SESSION ID:(15.7070) 2003-01-07 12:10:11.308
QUERY
SELECT * FROM EMP
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 10240
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 16
...

Explanation of the PLAN_TABLE requirement:

For a 10053 trace to be produced, the QUERY must be using the CBO and must be re-parsed with the event in place. The PLAN_TABLE is not actually required for this trace to work. It is only there to facilitate the EXPLAIN PLAN command. The EXPLAIN PLAN is the Alter Session Method (Literals)re because it forces a reparse of the statement. EXPLAIN PLAN will fail without presence of the PLAN_TABLE.

Explanation of the behaviour when using binds :

Note that EXPLAIN PLAN and SQL*Plus have limitations in the way they treat certain bind types. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan. Additionally, SQL*Plus does not support DATE datatypes. If using binds, use method 2 but bear in mind this might not give the exact same execution plan as when the SQL is run from within in your application.

REFERENCES

NOTE:68735.1 - * Diagnostics for Query Tuning Problems

相关内容
产品
Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Generic SQL Performance, SQL Execution, Query Optimizer
关键字
CBO;TRACE

0 0