Introduction to Oracle 10046 event (i)

Working with Oracle has been a long time, often listening to 10046 of incidents, especially when it comes to SQL tuning. What is the 10046 incident, first of all, a brief introduction.

1, what is the 10046 event

The 10046 event is a tool that Oracle provides to analyze performance, which helps us to parse the running state of one/more SQL, PL/s statements, including: Wait events encountered in the Parse/fetch/execute three phases, physical and logical reads consumed, CPU time, execution plan, and so on.

Level of event 2, 10046

Different levels correspond to different level of tracking

• 1 The Enable standard Sql_trace feature (default) contains SQL statements, response time, service time, number of rows processed, number of physical reads and writes, execution plan, and other additional information. The condition that the execution plan writes to trace in version 10.2 is only when the relevant cursor has been closed, and the execution statistics associated with it are the sum of all executions. To version 11.1, the execution plan is only written to the trace after the first execution of each cursor, and the execution statistics are only related to this first execution

• 4 The trace of the binding variable is more than Level 1 o'clock

• 8 more wait events than level 1, especially useful for latchfree wait events indicated in 9i, useful for analyzing full table scans and index scans

• 12 more than level 1 out bound variables and wait events

Version 11g and above

• 16 Generate stat information for each execution in 11g

• 32 Less than Level 1 execution plan

Version 11.2.0.2 and above

• 64 and Level 1 may also generate execution plan information after the first execution, provided that a cursor runs for a minute longer than the previous execution.

3. Enable 10046 Events

1) Enable 10046 events for this session

A.

Alter session SET Events ' 10046 Trace name context forever,level 12 '

B.

Oradebug Setmypid

Oradebug Event 10046 Trace name context, level 12

Only level levels that can be modified

2) Enable 10046 event for other session

Oradebug setospid|setorapid XXX

Oradebug Event 10046 Trace name context, level 12

4. Deactivate 10046 Events

respectively, corresponding to the above different activation mode

Alter session SET Events ' 10046 Trace name context forever off '

Oradebug Event 10046 Trace name context off

or quit the session with the 10046 event enabled

5. Get the trace file generated by the 10046 event

A. For versions 11g and above, use the following statement to easily get

Select value from V$diag_info where Name= ' Default Trace File ';

B. The following SQL is required for 10g and previous versions

select d.value | |   '  | |  lower (RTRIM (I.INSTANCE, CHR (0))  | |   ' _ora_ '  | |        p.spid | |   ' TRC '  TRACE_FILE_NAME  FROM  (select p.spid           from sys. V$mystat m, sys. V$session s, sys. V$process p         where m.statistic# = 1            AND S.SID = M.SID            AND P.ADDR = S.PADDR)  P,         (select t.instance           from sys. V$thread t, sys. v$parameter v         where v.name =  ' thread '            and  (V.value = 0 or t.thread# = to_number (V. VALUE))  I,        (Select value from sys. v$parameter where name =  ' user_dump_dest ')  D;

C. If you use the Oradebug command, use the corresponding Oradebug tracefile_name to get the trace file

6. Formatting trace files

The original trace file generated by the 10046 event is often referred to as a bare trace file (raw trace), and Oracle records the contents of the bare trace file at a glance that doesn't look so easy to understand. In order for the naked trace file to be presented in a more intuitive and easy-to-understand manner, Oracle provides the TKPROF command, which is brought by Oracle and can be used to translate naked trace files.

The syntax for tkprof is as follows:

[[email protected] 10046]$ tkprofusage: tkprof tracefile outputfile [ explain= ] [table= ]               [print= ] [insert= ] [sys= ] [sort= ]  table= schema.tablename   use  ' schema.tablename '  with  ' explain= '  option.   explain=user/password    Connect to ORACLE and issue  explain plan.  print=integer    list only the first  ' Integer '  sql statements.  aggregate=yes|no  insert=filename  list  SQL statements and data inside INSERT statements.  sys=no            TKPROF does not list SQL  Statements run as user syS.  record=filename  record non-recursive statements found in the  trace file.  waits=yes|no     Record summary for  any wait events found in the trace file.  sort=option       set of zero or more of the following sort  options:    prscnt  number of times parse was  called    prscpu  cpu time parsing    prsela   elapsed time parsing    prsdsk  number of disk  Reads during parse    prsqry  number of buffers for  consistent read during parse    prscu   number of  buffers for current read during parse    prsmis  number of  Misses in library cache during parse    execnt  number  of execute was called    execpu  cpu time spent  executing    exeela  elapsed time executing     exedsk  number of disk reads during execute     Exeqry  number of buffers for consistent read during execute     execu   number of buffers for current read  during execute    exerow  number of rows processed  during execute    exemis  number of library cache  misses during execute    fchcnt  number of times fetch was called     fchcpu  cpu time spent fetching    fchela   Elapsed time fetching    fchdsk  number of disk reads  during fetch    fchqry  number of buffers for  consistent read during fetch    fchcu   number of  Buffers for current read during fetch    fchrow  number  of rows fetched    userid  userid of user that  parsed the cursor

7, simple example, database version 11.2.0.4

[email protected]>alter session set events  ' 10046 trace name  Context forever,level 12 '; Session altered. [email protected]>select * from scott.emp;     empno  ename  job     mgr hiredate    sal        comm     deptno---------- ------------------------ ------ --------------------------- ---------- ------------------- ---------- -------- -- ----------      7369 SMITH  CLERK      7902 1980-12-17 00:00:00    800    20       7499 ALLEN  SALESMAN    7698 1981-02-20  00:00:00   1600        300   30       14 rows selected. [email protected]>alter session set events  ' 10046 trace name  Context off '; Session altered. [Email protected]>select value from v$diag_info where name= ' Default  Trace file '; VALUE-------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------/u02/app/oracle/diag/rdbms/orcl/orcl/trace/ orcl_ora_3239.trc[email protected]>! [[Email protected] trace]$ tkprof /u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_ 3239.trc 10046.trctkprof: release 11.2.0.4.0 - development on thu feb  16 21:38:57 2017Copyright  (c)  1982, 2011, oracle and/or its  affiliates.  all rights reserVed. [[email protected] trace]$ cat 10046.trctkprof: release 11.2.0.4.0 -  development on thu feb 16 21:38:57 2017copyright  (c)  1982, 2011,  oracle and/or its affiliates.  all rights reserved. trace file: /u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trcsort options:  default********************************************************************************count     = number of times OCI procedure was executedcpu       = cpu time in seconds executing elapsed  =  Elapsed time in seconds executingdisk     = number of  physical reads of buffers from diskquery    = number  of buffers gotten for consistent readcurrent  = number of buffers gotten in  current mode  (usually for update) rows     = number of  rows processed by the fetch or execute call************************** ******************************************************...... Omit part of Content ********************************************************************************sql id:  ggqns3c1jz86c plan hash: 3956160932select * from scott.empcall      count       cpu    elapsed        disk      query    current         rows------- ------  -------- ----------  ---------- ---------- ----------  ----------Parse        1      0.00        0.00          0           0          0            0Execute      1       0.00       0.00           0          0           0           0fetch         2      0.00        0.00          0           7          0           14------- ------  -------- ---------- ---------- - --------- ----------  ----------total        4       0.00       0.00           0          7           0           14misses in library cache during parse: 0optimizer mode: all_ Rowsparsing user id: sysnumber of plan statistics captured: 1rows   (1st)  Rows  (avg)  Rows  (max)   row source operation----------  ---------- ----------  ---------------------------------------------------        14          14         14   TABLE ACCESS FULL EMP  (cr=7 pr=0 pw=0 time=81 us cost =3 SIZE=532 CARD=14) Elapsed times include waiting on following events:   Event waited on                              times    Max. Wait  Total Waited  ------------------------------------- ---   Waited  ----------  ------------  sql*net message  to client                        2        0.00           0.00  SQL*Net message from client                      2         0.00           0.00******************************************************************************** ... Omit part of the content

Reference Document: Https://blogs.oracle.com/askmaclean/entry/maclean teaches you to read Oracle_10046_sql_trace

MOS document event:10046 "Enable SQL statement tracing (including binds/waits)" (document ID 21154.1)

How to Collect 10046 Trace (sql_trace) Diagnostics for performance issues (document ID 376442.1)

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e41573/sqltrace.htm#PFGRF94981

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1898624

Introduction to Oracle 10046 event (i)