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:
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 a30
SQL> 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=>'TRACE_10053');
PL/SQL procedure successfully completed.
To disable the trace
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:
NOTE:68735.1 - * Diagnostics for Query Tuning Problems
=====================================================
USING THE 10053 EVENT TO UNDERSTAND INDEX BEHAVIOR
http://www.tuningknife.com/wp-content/uploads/10053IndexBehavior.pdf
===========================================================
Note---> This informationmation taken from oracle metalink. all copy rights oracle only.
p_sql_id=>'cjk13xfm8ybh7', -
p_child_number=>0, -
p_component=>'Optimizer', -
p_file_id=>'TRACE_10053');
PL/SQL procedure successfully completed.
The trace file will include the text as stated in "p_file_id":
ORCL_ora_16986_TRACE_10053.trc
NOTE: The value passed as argument P_FILE_ID must start with an alphabetic character and must not contain any leading or trailing white space.
NOTE: When using NLS settings such as NLS_SORT, this information is not captured in the cursor information. This means that when DBMS_SQLDIAG.DUMP_TRACE generates the trace from an existing cursor that was generated with such NLS setting, the parse environment does not have that information. The result of this is that trace extracted from a cursor in memory 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 and so a trace generated from V$SQL_OPTIMIZER_ENV may cause confusion. Be aware that the plan generated for a given cursor in the application may be different to the one extracted by this method of tracing in this circumstance.
e.g.:
Trace from the Current session shows:
Trace generated from another session shows no NLS settings.
For details of Linguistic sorting see:
e.g.:
create index bigemp_job on bigemp(job,ename);
alter session set nls_comp='LINGUISTIC';
alter session set nls_sort = 'GERMAN';
select job,ename from bigemp where job in ('PRESIDENT');
alter session set nls_comp='LINGUISTIC';
alter session set nls_sort = 'GERMAN';
select job,ename from bigemp where job in ('PRESIDENT');
Trace from the Current session shows:
NLSSORT("BIGEMP"."JOB",'nls_sort=''GERMAN''') ....
Trace generated from another session shows no NLS settings.
For details of Linguistic sorting see:
Document 227335.1 Linguistic Sorting - Frequently Asked Questions
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:
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 --
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';
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:
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
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
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';
FROM v$diag_info
WHERE name='Default Trace File';
On earlier versions the location is defined by the parameter .
To identify the correct trace file, search for the relevant --SQL STATEMENT--.
This will be followed by a section headed "PARAMETERS USED BY THE OPTIMIZER". e.g:
To identify the correct trace file, search for 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
...
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
=====================================================
USING THE 10053 EVENT TO UNDERSTAND INDEX BEHAVIOR
http://www.tuningknife.com/wp-content/uploads/10053IndexBehavior.pdf
===========================================================
Note---> This informationmation taken from oracle metalink. all copy rights oracle only.
Comments
Post a Comment
Oracle DBA Information