X

How to Get a 10046 trace for a Parallel Query

++++++++++++++++++++++++++++++++++++++++++++++++++++
The complete and the most recent version of this article can be viewed
from My Oracle Support Knowledge Section.

How to Get a 10046 trace for a Parallel Query (Doc ID 1102801.1)
++++++++++++++++++++++++++++++++++++++++++++++++++++

Applies to:


Oracle Server - Enterprise Edition - Version: 8.1.5.0 and later   [Release: 8.1.5 and later ]
Information in this document applies to any platform.

Goal



The Notes gives instruction how to gather 10046 traces for parallel queries.
For use by Developers and everyone who is interested in tuning.
It also expain where you find this trace files.

I assume that the reader is familar with Parallel execution and the Terminology.
Here is a short overview, more information are in the documentation.

Parallel execution coordinator/query corridantor (QC):
The user background process that controls the query.
Parallel execution servers/slave (QS);
Do the work and pass results back to the QC
Parallel execution server set:
Collection of QS that execute one operation


Solution


In Note 376442.1; Master Note: Recommended Method for Obtaining 10046 trace
for TuningWe
explain how to get a 10046 trace for a serial query. Most of the
instructions are valid for a parallel query, but there are some
important differences.

In a parallel query the query corridantor and the
slaves execute the SQL command and every process traces the work that
he does. That means as example if a slave waits for I/O and there are
wait events then the wait events are only visible in the slave trace
file and not in the QC trace file.

If the 10046 trace event is executed before the parallel
query is started then the events is automatically propagate to the
slaves. This means the slaves also create trace files.

In
10.2 and previous versions of the database the 10046 trace files will
created in the locations of user_dump_dest for the user processes and
background_dump_dest for the background processes. Slave are background
processes and the traces can be found in the location of
background_dump_dest;
To show the locaction of the traces files, the following command can be
used:
show parameter dump_dest

In 11.1 and newer version of the database the traces are in one folder.
You can use the adrci tool to get an overview over the last creates trace files.You can use the following command:

adrci> show tracefile -t

Session Tracing
This tracing can be used where the session is accessible to the user prior to the start of the statement(s) to be traced.
To gather 10046 trace at the session level:

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12';

-- Execute the queries or operations to be traced here --
alter session set events '10046 trace name context off';
select * from dual;
exit;



In 10.2 and previous version the tracefile_identifier and the
procedures of the package dbms_application_info will not change the
slave trace names nor it puts information in the trace file. As example:


exec dbms_application_info.set_action ('PX Trace')
alter session set tracefile_identifier='10046';

Then you will not see a line like the following in the slave traces

*** ACTION NAME:(PX Trace)


Where I find the slave traces:

In
some case it might be not obviously what slaves are involved in
parallel query and on what node(s) in a RAC system the traces files are
created.
In this situation the following tracing can help

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set "_px_trace" = low , messaging;
alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12';

-- Execute the queries or operations to be traced here --
alter session set events '10046 trace name context off';
alter session set "_px_trace" = none;
select * from dual;
exit


In this case the QC trace file contains lines like:



GROUP GET 
Acquired 4 slaves in 1 set q serial 3073
  P000 inst 1 spid 22332
  P001 inst 1 spid 22334
  P003 inst 1 spid 22336
  P004 inst 1 spid 22336

This shows what slaves on what instance are involved in the parallel query and in this example we have to look
on
instance 1 in the trace folder for filenames that contain the words
..p001..trc,..p002..trc,..p003..trc and ..p004..trc.


Tracing a process after it has started

If you start tracing after the parallel query started with one of the following commands:

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(..)
SQL> oradebug event 10046 trace name context forever, level 12
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(..)
SQL> exec DBMS_SYSTEM.SET_EV(..)


then
you have to start tracing for all slaves and the QC, If you only
started  to trace the QC there are no traces created by the slaves.

The following topic from Note 376442.1 can be used in the same way for parallel eexcution.
Instance wide tracing
Initialisation parameter setting
Via a Logon Trigger

Tracing in 11g and higher versions
In 11 g there is a new tracing interface. This allows us to trace only some SQL command or processes.
or do the tracing in the same way we did it in previous versions.

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

alter session set events 'sql_trace level 12';

-- Execute the queries or operations to be traced here --
alter session set events 'sql_trace off';
select * from dual;
exit;


We can also limit  the trace to certain SQL's. Let us assume we would like to trace 2 SQL's with the slq_id
g3yc1js3g2689 and 7ujay4u33g337.Then we can use the following command:


alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

alter session set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] level 12';

-- Execute the queries or operations to be traced here --
alter session set events 'sql_trace off';

select * from dual;
exit;


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha