APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
GOAL
 
For some kind of problems it is helpful to run a parallel query in serial with the parallel execution plan.

As example when someone would like to trace a query with the event 10046 to compare the estimated rows against the actual rows. If the query runs in parallel then the the row numbers from all slave trace files needs to added to get the complete number of actual rows, this is sometimes a time consuming task.
This document shows an easy way to run a parallel query in serial mode.
SOLUTION
To run a parallel query in serial mode with the same plan, you have to use event 10384 with level  16384.

As example
alter session set events '10384 trace name context forever , level 16384';
<your parallel query goes here>
alter session set events '10384 trace name context off';

With the event 10384 the query has still a parallel execution plan, but we force that only the query coordinator (QC)  runs this SQL command and no parallel slave is started. So only 1 process is executed, what makes it easier to gather some information like the actual rows..
You should not executed 

alter session disable parallel query;

The last command creates a serial plan and the plan is different to the parallel execution plan.
Here is an example.  This example uses the  gather_plan_statistics hint to show the difference.

SQL> set linesize 150
SQL> set pagesize 2000
SQL> alter session set events '10384 trace name context forever , level 16384';
SQL>  select /*+  gather_plan_statistics  */ * from emp e;
SQL> select * from TABLE(dbms_xplan.display_cursor( null, null, 'ALL IOSTATS LAST'))
SQL> alter session set events '10384 trace name context off';
Here is the output with the event 16384
------------------------------------------------------------------------------------------
|Id|Operation            |Name|Start|E-Row|E-Byte|Cost| TQ  |IN-OU|PQ Distr |A-Row|Buffer|
------------------------------------------------------------------------------------------
| 1| PX COORDINATOR      |    | 1   |     |      |    |     |     |         | 14  |   9  |
| 2|  PX SEND QC (RANDOM)|:TQ1| 1   | 14  | 518  |  2 |Q1,00|P->S |QC (RAND)| 14  |   9  |
| 3|   PX BLOCK ITERATOR |    | 1   | 14  | 518  |  2 |Q1,00|PCWC |         | 14  |   9  |
|*4|    TABLE ACCESS FULL|EMP | 1   | 14  | 518  |  2 |Q1,00|PCWP |         | 14  |   9  |
------------------------------------------------------------------------------------------
and here is the output without the event 10384
 
------------------------------------------------------------------------------------------
|Id|Operation            |Name|Start|E-Row|E-Byte|Cost| TQ  |IN-OU|PQ Distr |A-Row|Buffer|
------------------------------------------------------------------------------------------
| 1| PX COORDINATOR      |    | 1   |     |      |    |     |     |         | 14  |   2  |
| 2|  PX SEND QC (RANDOM)|:TQ1| 0   | 14  | 518  |  2 |Q1,00|P->S |QC (RAND)|  0  |   0  |
| 3|   PX BLOCK ITERATOR |    | 0   | 14  | 518  |  2 |Q1,00|PCWC |         |  0  |   0  |
|*4|    TABLE ACCESS FULL|EMP | 0   | 14  | 518  |  2 |Q1,00|PCWP |         |  0  |   0  |
------------------------------------------------------------------------------------------

The difference is that without the event you see in step 2 until 4 the column A-Row shows the number 0. The  hint  /*+ gather_plan_statistics */  gathers only information form the query coordinator and do not combine the statistics from the parallel slaves and the query coordinator. The steps 2 until 4 are executed by the slaves and no work is done by the query coordinator that is the reason why you see 0 in the A-Row column.. Here is helps to use the event 10384 with level 16384 then only the QC works and the A-Rows column show the actual number of rows.
Some data like the A-Time can change between a parallel run and the run in serial with event 10384. That means the event can help to get easier information like the actual rows from each row step. It might not help as example when you analyze slow parallel problems where the root cause is not a bad execution plan.. 
 
Tracing a session with 10046
If you trace a session with a the event 10046 and 10384 you have to do:
alter session set events '10384 trace name context forever , level 16384';
alter session set events '10046 trace name context forever , level 8';
<your parallel query goes here>
alter session set events '10046 trace name context off';
alter session set events '10384 trace name context off';
You see the same effect as in case with the hint /*+ gather_plan_statistics */. The QC trace file shows 0 for the row source where the work is done by the slaves if you do not use the event 10384 level 16384.

If you use  event 10384 level 16384. then only 1 trace file from the query coordinator is created.

APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
 



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
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
 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 a30

SQL> select '10053_test' from dual;

SQL>  select sql_id, child_number, sql_text from v$sql 
      where sql_text like '053_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.

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.:
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');
 
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: How to Capture 10053 trace?

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. Please note if it is not being executed in a specific session you can still use alter system... Example: alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:cjk13xfm8ybh7]';

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 to Capture 10053 trace

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 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.
 

이 문서에서
목적
질문과 답변
  Gathering 10046 trace
참고
적용 대상:
Oracle Database Cloud Schema Service
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - 버전 N/A 과(와) 그 후속
Oracle Cloud Infrastructure - Database Service
Oracle Database Backup Service
Oracle Database Exadata Express Cloud Service
이 문서의 내용은 모든 플랫폼에 적용됩니다.
목적
 이 문서는 쿼리 성능 문제에 사용되는 10046 트레이스를 효과적으로 수집하는 다양한 절차를 설명합니다.
DBA, 개발자, 지원인력을 위한 것입니다

질문과 답변
Gathering 10046 trace
10046 이벤트는 오라클 세션들의 확장된 SQL_TRACE 정보를 얻는 표준 방법입니다.


이 이벤트의 자세한 내용은 다음을 보십시오:


Document 21154.1  EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

질의 성능 문제의 전형적인 요구사항은 질의의 대기와 바인드 변수 정보를 기록하는 것입니다. 이것은 10046 레벨 12 를 사용하면 가능합니다. 다음의 예제들은 다양한 시나리오에서 이벤트를 설정하는 방법을 설명합니다:

트레이스 위치
세션 추적
프로세스 시작 후의 추적
인스턴스 범위의 추적
초기화 파라미터 설정
로그온 트리거를 사용한 세션 추적
SQLT 를 사용한 트레이스 수집
DBMS_MONITOR 를 사용한 추적
특정한 경우에서의 대체 추적 방법
트레이스 해석

트레이스 위치

11g R1 이상:

오라클 데이터베이스 11g 릴리즈 1 에서의 새로운 진단 기반구조 도입으로, 트레이스와 코어 파일들은 초기화 매개변수 DIAGNOSTIC_DEST 에 의해 제어되는 위치에 존재합니다.
DIAGNOSTIC_DEST 의 위치를 보기 위해서는 다음의 명령어를 사용합니다:
  SQL> show parameter diagnostic_dest

11g R1 이전:

10046 이벤트 추적은 <user_dump_dest> 에 사용자 프로세스, 그리고 <background_dump_dest> 에 백그라운드 프로세스의 트레이스 파일을 생성할 것입니다.
user_dump_dest 의 위치를 보기 위해서는 다음의 명령어를 사용합니다:
  SQL> show parameter user_dump_dest

노트: 몇몇 예제들은 생성된 트레이스를 찾는데 도움을 주기 위한 'tracefile_identifier' 의 설정을 포함합니다.

세션 추적

이 추적은 추적 대상 문장의 시작 전에 사용자가 세션에 접근이 가능할 때 사용될 수 있습니다.
세션 레벨에서 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';

-- 여기에 추적할 문장이나 작업을 수행 --

select * from dual;
exit;
만일 세션이 세션이 종료되지 않으면 다음과 같이 추적을 비활성화 할 수 있습니다:
alter session set events '10046 trace name context off';
세션이 깨끗하게 종료되지 않고 추적이 비활성화되면, 중요한 추적 정보가 추적 파일 내에 포함되지 않을 수 있다는 점을 참고하십시오.




노트: 여기서의 "statistics_level=all" 설정은 파라미터가 기본 권장 레벨인 "TYPICAL" 에서 (BASIC 으로) 변경되는 경우에 일정 레벨의 통계를 수집하기 위하여 설정하였습니다. 성능 문제의 분석을 위해서는 일정 레벨의 통계가 필요합니다. "ALL" 값이 절대적으로 필요하지 않을 수 있지만 분석에 가장 용이한 정보를 얻기 위해 "TYPICAL" 보다 우선적으로 선택됩니다.




프로세스 시작 후의 추적

기존의 세션에 대한 추적이 필요하면 세션에 붙어서 10046 추적을 시작하기 위해 oradebug 를 사용합니다.

첫번째 단계는 어떻게든 추적할 세션을 확인하는 것입니다:

예를 들어, SQL*Plus 에서 대상 세션의 OS 프로세스 id (spid)를 찾기 위하여 세션을 시작합니다:
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
SPID 는 운영체제의 프로세스 식별자 (os pid)
PID 는 오라클의 프로세스 식별자 (ora pid)

세션 ID 를 모르는 경우 다음과 비슷한 select 문으로 target 세션을 확인할 수 있습니다:
column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
 
12c 의 멀티 스레드 프로세스에 대한 문서에서는, 오라클이 여러 프로세스를 하나의 ospid 에 결합시키므로 특정 스레드를 찾을 때는 v$process 에 새롭게 추가된 stid 컬럼을 사용하도록 하고 있습니다. 특정 스레드를 찾을 때는 다음의 방법을 사용합니다:

oradebug setospid <spid> <stid>

프로세스의 OS 프로세스 id 를 알아냈으면 다음과 같이 추적을 초기화합니다:

추적할 프로세스가 os pid 9834 를 가진다고 가정합니다.
SQL*Plus 에서 dba 로 로그인한 후 다음을 수행합니다:
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
예제의 ‘9834’ 값을 실제 os pid 로 대체해야 함을 기억하십시오.

노트: oradebug 의 ‘setorapid’ 를 사용하여 세션에 붙는 것도 가능함을 참조하십시오.

이 경우 (‘SPID’대신에) PID (오라클 프로세스 식별자) 가 사용될 것이며 oradebug 텍스트는 다음과 같이 변경될 것입니다:
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
예제의 ‘9834’ 값을 실제 ora pid 로 대체해야 함을 기억하십시오.

추적이 끝나서 oradebug 추적을 비활성화하려면:
oradebug event 10046 trace name context off
         12c 의 멀티 스레드 프로세스는 오라클이 여러 프로세스를 하나의 ospid 에 결합시키므로 특정 스레드를 찾을 때는 v$process 에 새롭게 추가된 stid 컬럼을 사용하도록 하고 있습니다. 다음의 방법을 사용합니다:

          oradebug setospid <spid> <stid>oradebug unlimit

         트레이스 파일 이름은 다음과 같을 것입니다 <instance><spid>_<stid>.trc.

인스턴스 범위의 추적

노트: 시스템 범위의 설정시 모든 세션이 추적되기 때문에 성능에 영향을 줄 수 있으므로 주의하십시오.

이 설정은 파라미터 설정 이후에 생성된 모든 세션을 추적할 것입니다. 기존의 세션들은 추적되지 않습니다.

시스템 범위의 10046 추적 설정은 문제의 세션은 알고 있지만 사전에 식별할 수 없는 경우에 유용합니다.
이 경우 추적을 짧은 시간 동안 활성화 할 수 있으며, 문제가 재현된 후 추적을 비활성화하고 생성된 트레이스들에서 문제의 증거를 찾습니다.

시스템 범위의 추적은 다음과 같이 활성화 합니다:
alter system set events '10046 trace name context forever,level 12';
이 설정은 다음의 명령어를 사용하여 모든 세션에 대해 비활성화 합니다:
alter system set events '10046 trace name context off';

초기화 파라미터 설정

이 설정은 재시작후 인스턴스내의 모든 세션을 추적할 것입니다.
event="10046 trace name context forever,level 12"
이 설정은 파라미터 제거후 인스턴스 재시작이나 다음의 alter system 명령어로 비활성화합니다:
alter system set events '10046 trace name context off';

로그온 트리거를 사용한 세션 추적

특정 사용자의 동작을 추적할 필요가 있을 수 있습니다. 이 경우 로그온 트리거를 사용할 수 있습니다.
다음에 예제가 있습니다:
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
  /
 
세션을 추적하기 위하여, 트리거를 수행하는 사용자는 ‘alter session’ 권한을 명시적으로 부여받아야 한다는 점을 참조하십시오. 예:

grant alter session to <USERNAME> ;
 

SQLT  를 사용한 트레이스 수집

SQLTXPLAIN 을 “Xecute” 옵션과 함께 사용하면 SQLT 출력의 일부로 10046 트레이스가 생성됩니다. XECUTE 이름이 암시하듯, SQLT 는 분석할 SQL 을 수행하고 진단 파일들을(10046 트레이스 포함) 만들어 냅니다. 참조:

Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)

이 방법으로 생성된 트레이스는 SQLT 결과 패키지에 다음의 형식으로 포함됩니다:
sqlt_s12345_10046_execute.trc

12345 는 SQLT 리포트 식별자


DBMS_MONITOR 를 사용한 추적

DBMS_MONITOR 패키지가 추적을 활성화하는 많은 방법을 제공함을 참조하십시오. 자세한 내용은 다음을 보십시오:

Document 293661.1 Tracing Enhancements In 10g Using DBMS_MONITOR

Oracle Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)
Part Number B14258-02
Chapter 60 DBMS_MONITOR
특정한 경우에서의 대체 추적 방법

Document 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Document 1274511.1 General SQL_TRACE / 10046 trace Gathering Examples

Document 160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Document 371678.1 Capture 10046 Traces Upon User Login (without using a trigger)

Document 1102801.1 How to Get 10046 Trace for Parallel Query


Document 258418.1 Getting 10046 Trace for Export and Import

PL/SQL 프로시저나 패키지를 사용한다면, PL/SQL 프로파일러의 사용이 PL/SQL 루틴 사용시의 시간을 측정하는데 유용할 수 있습니다. 프로파일러 사용 설명은 다음의 글에서 확인할 수 있습니다:

Document 243755.1 Implementing and Using the PL/SQL Profiler



트레이스 해석

Document 199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
Document 39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

Document 224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

Document 41634.1 - TKProf Basic Overview
Document 32951.1 - TKProf Interpretation (9i and below)
Document 760786.1 - TKProf Interpretation (9i and above)
Document 214106.1 - Using TKProf to compare actual and predicted row counts

Document 199083.1 * Primary Document SQL Query Performance Overview
Document 398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions
 

 

 

 

커뮤니티 토론

아직 질문이 있으신가요? 아래 커뮤니티 창을 사용하여 유사한 토론을 검색하거나 이 주제에 대한 새 토론을 시작하십시오. (창은 스크린샷이 아닌 라이브 커뮤니티입니다)

기본 브라우저 창에서 열려면 여기를 클릭하십시오

 

 

Event:10046
Text:   enable SQL statement timing
-------------------------------------------------------------------------------
For details of interpreting 10046 output see: Note:39817.1

Explanation:
        This is a special EVENT code. 
        It can be used to signal Oracle to perform SQL_TRACE type actions.

        The 10046 trace is the equivalent of setting SQL_TRACE=TRUE.
        The advantage of using the event is that extra details may be
        output to the trace file depending on the level specified with the
        event.

        As of 11g one can also set the "10046" event type trace using a 
        new event syntax which specifies "sql_trace" as the event.
        eg: alter session set events 'sql_trace wait=false, bind=true';'

For Customers:
     The event here is often requested to be set by Oracle Support
when gathering information for specific SQL issues.

        The notes below describe the levels for this event.
        
10046 EVENT levels:  (the new sql_trace values are included in [..])
  These are bit values so can be ORed together to get different mixes
        1  - Enable standard SQL_TRACE functionality (Default)
        4  - As Level 1 PLUS trace bind values           [ bind=true ]
        8  - As Level 1 PLUS trace waits                 [ wait=true ]
             This is especially useful for spotting latch wait etc. 
             but can also be used to spot full table scans and index scans.

  As of 11g these additional bit levels are available:
        16 - Generate STAT line dumps for each execution [ plan_stat=all_executions ]
        32 - Never dump execution statistics             [ plan_stat=never ]

  As of 11.2.0.2 this additional bit level is available:
        64 - Adaptive dump of STAT lines.                [ plan_stat=adaptive ]
             This dumps the STAT information if a SQL took more than about 1 minute thereby 
             giving information for the more expensive SQLs and for different executions of such
             SQLs.

  eg: A common event level is 12 which includes standard SQL_TRACE output, binds, waits and 
        default STAT line tracing.

Notes:
 STAT dumping has been amended in 11g so that they are not aggregated across all executions but are 
 dumped after execution.  This  has been done to address cases where the cursor is not closed and the 
 STAT information is therefore not dumped. Now we guarantee to capture the STAT information 
 following the execution. See the above bit levels to have finer control over STAT lines. 


Example Syntax:

   Session level:
alter session set events '10046 trace name context forever';
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';

      11g alternatives:
alter session set events 'sql_trace';
alter session set events 'sql_trace wait=true';
alter session set events 'sql_trace off'';


   Init.ora:
event="10046 trace name context forever,level 4"
        
        WARNING: This will trace ALL database sessions
        

    From oradebug (7.3+):
        oradebug event 10046 trace name context forever, level 4

     11g alternative:
        oradebug event sql_trace bind=true


    From oradbx (pre 7.3):
event 10046 trace name context forever, level 4


Articles:
     Interpreting RAW 10046 output                            Note:39817.1
     How to enable 10046 for another session using ORADEBUG   Note:1058210.6



EXAMPLES:
~~~~~~~~~
Wait Information:
        nam is what is being waited for
        ela is the elapsed time for the operation
            In Oracle7 / 8 this is in units of hundredths of a second
            In Oracle9i onwards this is in microseconds
        p1 is the file number
        p2 is the block number 
        p3 is the number of blocks read by the operation

Waiting for a Full Table Scan: 
        WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25
        WAIT #1: nam="db file scattered read" ela= 7 p1=4 p2=1461 p3=32
        WAIT #1: nam="db file scattered read" ela= 7 p1=4 p2=1493 p3=32

        The first wait indicates a mutliblock read was issued for 25 blocks
        starting at file 4 block 1435. This took 5/100th of a second.
        The next 2 lines also show multiblock reads of 32 blocks (which is
        probably the value of <Parameter:db_file_multiblock_read_count> )

Waiting for an Index Scan:
        WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1
        WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1788 p3=1

        This is the sort of output you would see on an index scan or an
        individual block access to table block.