如何获取并行查询的10046 Trace (Doc ID 1102801.1)_alter sessionset evnets'10046 trace name context f-CSDN博客

How to Get 10046 Trace for Parallel Query (Doc ID 1102801.1)

GOAL

The Note gives instruction how to gather 10046 traces for parallel queries. 本文说明如何收集并行查询的10046 Trace。

For use by Developers and everyone who is interested in tuning.It also explains where you find the generated trace files. 供开发人员和对调优感兴趣的每个人使用，还说明了在何处找到生成的跟踪文件。

I assume that the reader is familiar with Parallel execution and the terminology.Here is a short overview, more information are in the documentation. 我假设读者熟悉并行执行和术语，这里是简短概述，更多信息在文档中。

Parallel execution coordinator/query coordinator (QC) : The user background process that controls the query. 控制查询的用户后台进程
Parallel execution servers/slave (QS) : Do the work and pass results back to the QC 进行工作并将结果传递回QC
Parallel execution server set : Collection of QS that execute one operation 执行一项操作的QS的集合

SOLUTION

Background

In Note 376442.1 about the recommended methods for getting 10046 trace for tuning we 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. 在有关获取用于调试的10046 Trace 的推荐方法的Note 376442.1中(https://blog.csdn.net/u010692693/article/details/103081054)，我们说明了如何为串行查询获取10046 Trace。大多数指令对并行查询有效，但是有一些重要的区别。

In a parallel query the query coordinator and the slaves execute the SQL command and every process traces the work that it does. That means for 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. 在并行查询中，查询协调器和从属服务器执行SQL命令，并且每个进程都跟踪它所做的工作。这意味着，例如，如果从属设备正在等待I/O并且存在等待事件，则等待事件仅在从属设备Trace文件中可见，而在QC Trace文件中不可见。

If the 10046 trace event is executed before the parallel query is started then the event is automatically propagated to the slaves. This means the slaves also create trace files.In 10.2 and previous versions of the database the 10046 trace files are created in the locations pointed by the value of the DB parameters of user_dump_dest and background_dump_dest for the user processes and background processes respectively. Slaves are background processes and the traces can be found in the location of background_dump_dest. To show the location of the traces files, the following command can be used in SQLPlus or the v$parameter view can be queried:
如果在启动并行查询之前执行了10046跟踪事件，则该事件将自动传播到从站。这意味着从站也会创建Trace文件。在数据库的10.2和早期版本中，分别在用户进程和后台进程的user_dump_dest和background_dump_dest的DB参数值所指向的位置中创建10046 Trace文件。从站是后台进程，可以在background_dump_dest的位置找到跟踪。要显示Trace文件的位置，可以在SQLPlus中使用以下命令，或者可以查询v$parameter视图：

show parameter dump_dest

In 11.1 and newer version of the database the traces are in one folder. 在数据库的11.1和更高版本中，Trace位于一个文件夹中。

You can use the adrci tool to get an overview of the latest created trace files. You can use the following command: 您可以使用adrci工具获取最新创建的Trace文件的概述。您可以使用以下命令：

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: 要在Session级别收集10046跟踪

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;

Remember: If the 10046 trace event is executed before the parallel query is started then the event is automatically propagated to the slaves. This means the slaves also create trace files. 切记：如果在启动并行查询之前执行了10046跟踪事件，则该事件将自动传播到从站。这意味着从站也会创建Trace文件。

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: 在10.2和更低版本中，tracefile_identifier和dbms_application_info软件包的过程不会更改从属Trace名称，也不会将信息放入Trace文件中。例如：

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 这样您就不会在从属Trace中看到类似以下内容的行

*** ACTION NAME:(PX Trace)

Where can you find the slave traces 在哪里可以找到从属跟踪

In some case it might not be obvious what slaves are involved in parallel query and on what node(s) in a RAC system the trace files are created. 在某些情况下，并行查询中涉及哪些从属设备以及在RAC系统中的哪个节点上创建Trace文件可能并不明显。

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: 在这种情况下，QC Trace文件包含以下行

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. 这显示了并行查询中涉及哪个实例上的哪些从属，在此示例中，我们必须查看在跟踪文件夹中实例1上的文件名包含单词..p001..trc，.. p002..trc，.. p003..trc和..p004..trc。

Tracing a process after it has started

To start tracing after the parallel query started use one of the following commands: 要在并行查询开始后开始跟踪，请使用以下命令之一

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

If you only started to trace the QC, there will be no traces created by the slaves. Turning on tracing for the QC in such a case will not turn on tracing for the slaves. You have to turn on tracing for each slave processes you are interested in. 如果仅开始跟踪QC，则从站将不会创建任何跟踪。在这种情况下，打开QC的跟踪将不会打开从站的跟踪。您必须为您感兴趣的每个从属进程打开跟踪。

The following topics from Note 376442.1 can be used in the same way for parallel execution: Note 376442.1中的以下主题可以相同的方式用于并行执行：

Instance wide tracing
Initialisation parameter setting
Via a Logon Trigger

Tracing in 11g and higher versions 在11g及更高版本中进行跟踪

In 11g, there is a new tracing interface. This not only allows us to do the tracing in the same way we did it in previous versions, but we can trace only some SQL command or processes. 在11g中，有一个新的跟踪界面。这不仅允许我们以与以前版本相同的方式进行跟踪，而且只能跟踪某些SQL命令或进程。

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 sql_id <SQL_ID1> and <SQL_ID2>. Then we can use the following command: 我们还可以将跟踪限制为某些SQL。让我们假设我们想使用sql_id <SQL_ID1>和<SQL_ID2>跟踪2个SQL。然后我们可以使用以下命令：

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:<SQL_ID1>|<SQL_ID2>] level 12';

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

Note 1627387.1 describes how to determine the SQL_ID for a SQL statement 描述了如何确定SQL语句的SQL_ID