Oracle 10046 event, oracle10046

Oracle 10046 event, oracle10046

10046 events
Oracle 10046 is an Oracle internal event. The most common method is to set SQL _trace (alter Session set SQL _trace = true) at the session level, that is, to enable Level 1.
10046 debugging events. After a 10046 event is set, Oracle generates a dump file. Through further analysis of the dump file, detailed trace information such as parsing, calling, waiting, and binding variables of the Oracle internal execution system can be obtained, which plays an important role in analyzing the system performance.
When SQL statement operations have performance problems, we can use SQL _TRACE or 10046 to track events. If the overall performance of the database is degraded, you need to use statspack or AWR to analyze the database.

1.10046 event-related parameters

Some parameters need to be set for this event to control the output of the dump file:
TIMED_STATISTICS
Used to control timing information. It can be set to true or false. When set to true, the timing information will be added to the trace file.

MAX_DUMP_FILE_SIZE
Used to control the maximum size of a trace file. When using the 10046 event, we recommend that you set this parameter to unlimited.

USER_DUMP_DEST
Used to set the directory to which the trace file is written.

STATISTICS_LEVEL
Used to control the collection of statistical information. This parameter has three options: baisc, typical, and all.
Basic: only collect the most basic information that meets the trace requirements, such as Timed statistics, Object level statistics, and some advisory will be ignored.
Typical: This is the default value. This configuration will add some additional statistics on the basis of basic, such as the Statistical Information of the time consumed by the operating system, the statistical information of the Execution Plan will be collected
All: when it is set to all, all information related to this session will be collected.

TRACEFILE_IDENTIFIER
It is used to set the string used to identify the Trace file, so that the generated Trace file can be found more quickly.
The preceding parameters can be modified based on the system level and session level.
Alter session/system set timed_statistics = true
Alter session/system set max_dump_file_size = unlimited

Alter session set tracefile_identifier = 'trace _ SQL _example '--> only session level

2. 10046 levels of debugging events

10046 debugging events can be divided into multiple levels, and different levels output different trace information.
Level Function
0. Disable debugging events.
1. the debugging event is active. For each processed database call, the output SQL statement, APPNAME (Application name), parsing in cursor, and parse error (SQL PARSING)
, EXEC (execution), FETCH (obtain data), UNMAP, sort unmap (sorting, temporary segment), ERROR, STAT (Execution Plan), XCTEND (transaction) and other rows.
4 include level 1 Output and BIND line (BIND Variable information)
8 includes Level 1 Output and WAIT line (WAIT for event information ). For each wait in the processing process, provide the following information: The name of the wait time, duration, and some additional
Parameter, indicating the waiting resources.
12 output all information of level 4 and Level 8

 

3. Generate a trace file

[Oracle @ server8 trace] $ sqlplus/as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Tue Feb 10 16:20:39 2015

 

Sys @ GHSJDB> alter session set tracefile_identifier = '000000 ';

Session altered.

Sys @ GHSJDB> alter session set events '10046 trace name context forever, level 12'; (if no permission is granted to alter session)

Session altered.

Sys @ GHSJDB> select code, name, to_char (parent_code) parent, to_char (connect_by_isleaf) isleaf, lev
2 from ghsj_xm.v_organdataset
3 where level = 1 connect by prior code = parent_code start with code = '000000' order siblings by code
4;

CODE NAME
Certificate -----------------------------------------------------------------------------------------------------------------------------
PARENT ISLEAF
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
14142 State Grid Hubei Electric Power Company
14000

 

Sys @ GHSJDB> alter session set events '10046 trace name context off ';

Session altered.

 

4. Get the trace file

Method 1:

Sys @ GHSJDB> show parameter dump

NAME TYPE VALUE
-----------------------------------------------------------------------------
Background_core_dump string partial
Background_dump_dest string/home/u01/app/oracle/diag/rdbm
S/ghsjdb/trace
Core_dump_dest string/home/u01/app/oracle/diag/rdbm
S/ghsjdb/cdump
Max_dump_file_size string unlimited
Shadow_core_dump string partial
User_dump_dest string/home/u01/app/oracle/diag/rdbm
S/ghsjdb/trace

Go to the corresponding directory:

 

Cd/home/u01/app/oracle/diag/rdbms/ghsjdb/trace

Search for the identifier 10046:

 

Ls | grep 10046

Ghsjdb_ora_94821310046.trc
Ghsjdb_ora_94821310046.trm

 

Method 2:

Obtains the sid of the current user and obtains the tracefile address based on the ID.

 

Sys @ GHSJDB> select * from v $ mystat where rownum = 1;

Sid statistic # VALUE
-------------------------
247 0 ##########

 

Sys @ GHSJDB> SELECT tracefile FROM v $ process WHERE addr IN (SELECT paddr FROM v $ session WHERE sid = '2013 ');

TRACEFILE
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/Home/u01/app/oracle/diag/rdbms/ghsjdb/trace/ghsjdb_ora_94821310046.trc

 

5. Format and output tkprof

[Oracle @ server8 ~] $ Tkprof
Usage: 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 of user that parsed the cursor

 

Tkprof/home/u01/app/oracle/diag/rdbms/ghsjdb/trace/ghsjdb_ora_94821310046.trc 10046.log sys = no

 

[Oracle @ server8 ~] $ More 10046.log

TKPROF: Release 11.2.0.3.0-Development on Tue Feb 10 17:11:04 2015

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.

Trace file:/home/u01/app/oracle/diag/rdbms/ghsjdb/trace/ghsjdb_ora_11266_10046.trc
Sort options: default

**************************************** ****************************************
Count = number of times OCI procedure was executed
Cpu = cpu time in seconds executing
Elapsed = elapsed time in seconds executing
Disk = number of physical reads of buffers from disk
Query = number of buffers gotten for consistent read
Current = number of buffers gotten in current mode (usually for update)
Rows = number of rows processed by the fetch or execute call

 

**************************************** ****************************************

Overall totals for all NON-RECURSIVE STATEMENTS

Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 2
Fetch 2 0.25 0.26 0 22123 0 1
-----------------------------------------------------------------------
Total 10 0.25 0.26 0 22123 3

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ----------------------
SQL * Net message to client 5 0.00 0.00
SQL * Net message from client 5 18.65 27.67

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
-----------------------------------------------------------------------
Total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

3 user SQL statements in session.
0 internal SQL statements in session.
3. SQL statements in session.
**************************************** ****************************************
Trace file:/home/u01/app/oracle/diag/rdbms/ghsjdb/trace/ghsjdb_ora_11266_10046.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3. SQL statements in trace file.
3 unique SQL statements in trace file.
115 lines in trace file.
27 elapsed seconds in trace file.