Shulou(Shulou.com)06/01 Report--

I've been working on Oracle for a long time, and I often hear about 10046 events, especially when it comes to SQL tuning. What exactly is the 10046 incident? let's first give a brief introduction.

1. What is the 10046 event

10046 event is a tool provided by Oracle to analyze performance. It can help us parse the running status of one or more SQL and PL/SQL statements, including wait events encountered in the three phases of Parse/Fetch/Execute, consumed physical and logical reads, CPU time, execution plan, and so on.

2. Level of the 10046 event

Different Level corresponds to different tracking levels.

1 enable the 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 for the execution plan to be written to trace in version 10.2 is that only if the relevant cursor has been closed and the associated execution statistics are the sum of all execution times. In version 11.1, the execution plan is written to trace only after the first execution of each cursor, and the execution statistics are only relevant to this first execution.

4 has more trace of bound variables than level 1

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

12 has more bind variables and wait events than level 1

11g and above

16 generate STAT information for each execution in 11g

32 has fewer execution plans than level 1

11.2.0.2 and above

64 may also generate execution plan information after the first execution compared to level 1; provided that a cursor takes one minute longer to run on the premise of the previous execution.

3. Enable the 10046 event

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

The only thing that can be modified is the level level

2) enable 10046 events for other session

Oradebug setospid | setorapid xxx

Oradebug event 10046 trace name context, level 12

4. Deactivate the 10046 event

Correspond to the different enabling methods above.

Alter session set events' 10046 trace name context forever off'

Oradebug event 10046 trace name context off

Or exit the session with 10046 events enabled

5. Get the trace file generated by the 10046 event

a. For versions of 11g and above, you can easily get it using the following statement

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, you can get the trace file by using the corresponding oradebug tracefile_name

6. Format trace files

The original trace file generated by the 10046 event is used to be called a naked trace file (raw trace). The content of the Oracle record in the naked trace file does not look so good at first glance, nor is it so easy to understand. In order to render trace files in a more intuitive and easy-to-understand way, Oracle provides the tkprof command, which is native to Oracle and can be used to translate trace files.

The syntax of tkprof is as follows:

[oracle@rhel6 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

Zx@ORCL > alter session set events' 10046 trace name context forever,level 12 session altered.zx@ORCL > 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.zx@ORCL > alter session set events' 10046 trace name context off' Session altered.zx@ORCL > select value from v$diag_info where name='Default Trace File' VALUE- -/ u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3239.trczx@ORCL >! [oracle@rhel6 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. [oracle@rhel6 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 the content * SQL ID: ggqns3c1jz86c Plan Hash: 3956160932select * from scott.empcall count cpu elapsed disk query current Rows--Parse 1 0.00 0.00 0 0Execute 1 0.00 0.00 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.005 hours per day. Omit part of the content

Reference documentation: 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.