Deep understanding of Oracle Debug Events: 10046 Explanation of events

The 10046 event is an extension of sql_trace, dubbed "the sql_trace of doping."
Effective tracking Level:

① Level 0: Sql_trace=fasle
② Class 1: sql_trace=true, which is the default level
③ Level 4: Level 1 + bound variable
④ Level 8: Level 4 + Wait Event
⑤ level 12: Level 4 + 8

For Level 4 10046, if formatted with tkprof, it hides every bit of what the SQL statement is doing and how to do it
For the 8 level of 10046, waiting events scattered everywhere is, at this time we may wish to use Tkprof to summarize the waiting events
So, understanding the format of the extended SQL trace file is an essential skill for every DBA facing performance issues or troubleshooting tasks

Why does ㈠ need 10046?

For a database user who has alter session permission but does not have an authorized DBA role
Alter session SET events is the only way to start an extended SQL Trace in his own session
In this way, the wait event or bound variable is included in the SQL trace file and then optimized or incorrectly diagnosed
10046 see the execution of the statement, know the execution plan of the statement, including the various steps, how to relate, respectively, in which steps are time-consuming, what wait events, etc.
These are the basics of optimization, knowing this and knowing how to optimize and troubleshoting



㈡ How to obtain a TRC file?

Here are the main 3 ways

① uses tracefile_identifier, such as:
Alter session set Tracefile_identifier=\ ' Think\ '

②oradebug, for more information, please refer to a blog before think: Oradebug

③ uses the following script:

[email protected]> edwrote file afiedt.buf 1 Select 2 d.value| | \ '/\ ' | | Lower (RTrim (i.instance, Chr (0))) | | \ ' _ora_\ ' | | p.spid| |            \ '. Trc\ ' Trace_file_name 3 from 4 (select P.spid 5 from V$mystat m,v$session s,v$process p 6 where m.statistic# = 1 and S.sid = M.sid and p.addr = s.paddr) p, 7 (select T.instance from V$threa              D T,v$parameter v 8 where v.name = \ ' thread\ ' and (v.value = 0 or t.thread# = To_number (v.value))) I, 9* (select value from v$parameter where name = \ ' user_dump_dest\ ') d[email protected]>/trace_file_name -----------------------------------------------------------------/u01/app/oracle/admin/orcl/udump/orcl_ora_ 4012.TRC 

㈢ How to read the 10046 event file?

① Database Calls

With 3 subcategories: parsing, execution, and acquisition
These 3 classifications are associated with subroutine Dbms_sql by calling Dbms_sql. Parse,dbms_sql. Execute,dbms_sql. Fetch_rows to run at the same pace as SQL.

Analytical
Parsing is typically represented in a trace file by two adjacent entries
The first one is the parsing in CURSOR, the second is the parse

Parsing in CURSOR #9 len=28 dep=0 uid=55 oct=2 lid=55 tim=1327904235010505 hv=119728103 ad=\ ' 2fc6ae84\ ' insert into T value S (\ ' ooxx\ ') END of Stmtparse #9: c=52003,e=65698,p=0,cr=30,cu=0,mis=1,r=0,dep=0,og=1,tim=1327904235010494

Execute and get the same parsing in the format is the same, here is not to repeat the

②commit and rollback and xctend entry formats

Xctend rlbk=0, rd_only=0

Oracle does not require client-side display to start a transaction, and the DBMS automatically opens a transaction after the first data item is modified or a distributed operation executes
For example, execute select from a table by Dblink
The boundary of the transaction in TRC is marked by the Xctend entry, in the following format:
Xctend Rlbk=[0-1],rd_only=[0-1]

③ Execution Plan, statistical information and stat entry format

The stat entry reports the execution plan and statistical information

Stat #6 id=1 cnt=0 pid=0 pos=1 obj=18 op=\ ' TABLE ACCESS by INDEX ROWID obj$ (cr=2 pr=0 pw=0 time=194 us) \ ' STAT #6 id=2 CNT =0 pid=1 pos=1 obj=37 op=\ ' INDEX RANGE SCAN i_obj2 (cr=2 pr=0 pw=0 time=95 us) \ '  

Each row of a set of stat entries represents the row source that forms the result of the statement
A so-called row source, which is the intermediate result of data retrieved from an index or table or a multi-table connection (because two tables must be connected first)

After 10g, the stat entry is only written at Timed_statistics=true and Sql_trace=true
Note that if Statistics_level=basic (the default is typical), it is implicitly set Timed_statistics=fasle

④ Wait event and wait entry format

WAIT #9: nam=\ ' sql*net message to client\ ' ela= 4 driver id=1650815232 #bytes =1 p3=0 obj#=52523 tim=1327922883350249wait # 9:nam=\ ' sql*net message from client\ ' ela= 301 driver id=1650815232 #bytes =1 p3=0 obj#=52523 tim=1327922883350743wait #11 : nam=\ ' db file sequential read\ ' ela= 253 file#=1 block#=420 blocks=1 obj#=355 tim=1327923455671258wait #11: nam=\ ' db fil E sequential read\ ' ela= 7073 file#=1 block#=43998 blocks=1 obj#=355 tim=1327923455678537wait #11: nam=\ ' db file Sequentia  L read\ ' ela= file#=1 block#=43999 blocks=1 obj#=355 tim=1327923455678836wait #11: nam=\ ' db file sequential read\ ' ela= 14433 file#=1 block#=53521 Blocks=1 obj#=355 tim=1327923455693393

⑤ binding variables and binds entry formats

The details of the bound variable include the data type and value of the bound variable
With this information we can get a maximum diagnosis.
For example, the data type of an indexed column does not match the data type of the bound variable, causing the index to expire and CPU usage to increase because there is an implicit data type conversion
The structure of a binds entry consists of a word binds followed by a cursor number and a separate sub-part of each bound variable

Binds #9: KKSCOACD bind#0  oacdty=02 mxl=22 (a) mxlc=00 mal=00 scl=00 pre=00  oacflg=08 fl2=0001 frm=00 csi=00 siz=2 4 off=0  kxsbbbfp=b7ee5a5c  bln=22  avl=02  flg=05  value=20 bind#1 oacdty=02  mxl=22 (mxlc=) XX mal=00 scl=00 pre=00  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0  kxsbbbfp=b7ee5a38  bln=24  avl=  flg=05  value=3

When you associate a bound variable with a child part, you do not care about the number, they are included in the name of the bound variable, for example ": B1"

[Email protected]> Select Dump (employee_id) from Employees where Rownum=1;dump (employee_id)----------------------- -----------------------------------------------------------------------------typ=2 len=2:194,2

The Typ here is the data type number.

The next think the optimization or diagnosis of the case to be attached, this article is not finished to be continued Oh,

Deep understanding of Oracle Debug Events: 10046 Explanation of events