獲得執行計畫方法四-使用10046事件

標籤：執行計畫 sql最佳化 oracle

我們也可以通過設定10046事件來進行SQL跟蹤，並且可以設定不同的追蹤層級，比使用SQL_TRACE獲得更多的資訊。

10046事件不但可以跟蹤使用者會話(trace檔案位於USER_DUMP_DEST )，也可以跟蹤background進程(trace檔案位於BACKGROUND_DUMP_DEST )。trace檔案的大小決定於4個因素：追蹤層級，跟蹤時間長度，會話的活動層級和MAX_DUMP_FILE_SIZE參數。

啟用跟蹤事件10046

1.在全域設定

修改初始化參數

EVENT = "10046 trace name context forever, level 8"

2.在當前session設定

alter session set events ‘10046 trace name context forever, level 8‘;alter session set events ‘10046 trace name context off‘;

3  dbms_system.set_ev

exec dbms_system.set_ev( 666,888, 10046, 8, ‘‘);exec dbms_system.set_ev( 666,888 , 10046, 0, ‘‘);

P

ROCEDURE SET_EV Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SI                             BINARY_INTEGER           IN SE                             BINARY_INTEGER          IN EV                             BINARY_INTEGER          IN LE                             BINARY_INTEGER          IN NM                            VARCHAR2                    IN

其中level 為：

Level 0   停用SQL跟蹤，相當於SQL_TRACE=FALSE

Level 1   標準SQL跟蹤，相當於SQL_TRACE=TRUE

Level 4   在level 1的基礎上增加綁定變數的資訊

Level 8   在level 1的基礎上增加等待事件的資訊

Level 12 在level 1的基礎上增加綁定變數和等待事件的資訊

SQL> alter session set events ‘10046 trace name context forever, level 8‘;Session altered.SQL> select count(*) from scott.emp;  COUNT(*)----------        14SQL> alter session set events ‘10046 trace name context off‘;Session altered.SQL> select  d.value || ‘/‘ || lower(rtrim(i.instance, chr(0))) || ‘_ora_‘ || p.spid || ‘.trc‘ trace_file_name  2     from (select p.spid  3             from sys.v$mystat m, sys.v$session s, sys.v$process p  4            where m.statistic# = 1  5              and s.sid = m.sid  6              and p.addr = s.paddr) p,  7          (select t.instance  8             from sys.v$thread t, sys.v$parameter v  9            where v.name = ‘thread‘ 10              and (v.value = 0 or t.thread# = to_number(v.value))) i, 11          (select value from sys.v$parameter where name = ‘user_dump_dest‘) d 12   /TRACE_FILE_NAME--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/u01/oracle/admin/orcl/udump/orcl_ora_3858.trc

查看trace檔案

[[email protected] ~]$ cat /u01/oracle/admin/orcl/udump/orcl_ora_3858.trc/u01/oracle/admin/orcl/udump/orcl_ora_3858.trcOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsORACLE_HOME = /u01/oracle/product/OraHomeSystem name:    LinuxNode name:      sunblaze.comRelease:        2.6.9-42.ELsmpVersion:        #1 SMP Wed Jul 12 23:27:17 EDT 2006Machine:        i686Instance name: orclRedo thread mounted by this instance: 1Oracle process number: 18Unix process pid: 3858, image: [email protected] (TNS V1-V3)*** 2013-04-05 16:08:33.022*** SERVICE NAME:(SYS$USERS) 2013-04-05 16:08:33.021*** SESSION ID:(149.1) 2013-04-05 16:08:33.021=====================PARSING IN CURSOR #1 len=68 dep=0 uid=0 oct=42 lid=0 tim=1333153625997662 hv=740818757 ad=‘30d77bc8‘alter session set events ‘10046 trace name context forever, level 8‘END OF STMTEXEC #1:c=0,e=332,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1333153625997642WAIT #1: nam=‘SQL*Net message to client‘ ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1333153625998424WAIT #1: nam=‘SQL*Net message from client‘ ela= 851 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1333153625999436WAIT #0: nam=‘SQL*Net message to client‘ ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1333153626000038*** 2013-04-05 16:08:55.143WAIT #0: nam=‘SQL*Net message from client‘ ela= 21600691 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1333153647600805=====================...............................................