在SQL*PLUS下使用10046事件例子
sys@ORCL> select spid from v$process p, v$session s where p.addr=s.paddr and sid=(select sid from v$mystat where rownum=1);
SPID
------------------------
6411
------------------------
6411
NUMBER
sys@ORCL> oradebug setospid 6411
Oracle pid: 32, Unix process pid: 6411, image: oracle@zjtdbrac1 (TNS V1-V3)
sys@ORCL> oradebug unlimit
Statement processed.
sys@ORCL> oradebug event 10046 trace name context forever,level 12
Statement processed.
sys@ORCL> select count(*) from t;
Oracle pid: 32, Unix process pid: 6411, image: oracle@zjtdbrac1 (TNS V1-V3)
sys@ORCL> oradebug unlimit
Statement processed.
sys@ORCL> oradebug event 10046 trace name context forever,level 12
Statement processed.
sys@ORCL> select count(*) from t;
COUNT(*)
----------
73285
----------
73285
sys@ORCL> oradebug event 10046 trace name context off
Statement processed.
sys@ORCL> oradebug tracefile_name
/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trc
sys@ORCL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@zjtdbrac1 ~]vi /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trcc
Statement processed.
sys@ORCL> oradebug tracefile_name
/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trc
sys@ORCL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@zjtdbrac1 ~]vi /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trcc
Trace file /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11g/dbname_1
System name: Linux
Node name: zjtdbrac1
Release: 2.6.32-279.el6.x86_64
Version: #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine: x86_64
Instance name: orcl
E486: Pattern not found: count(*) 1,11 Top
Trace file /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11g/dbname_1
System name: Linux
Node name: zjtdbrac1
Release: 2.6.32-279.el6.x86_64
Version: #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 6411, image: oracle@zjtdbrac1
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11g/dbname_1
System name: Linux
Node name: zjtdbrac1
Release: 2.6.32-279.el6.x86_64
Version: #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine: x86_64
Instance name: orcl
E486: Pattern not found: count(*) 1,11 Top
Trace file /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6411.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11g/dbname_1
System name: Linux
Node name: zjtdbrac1
Release: 2.6.32-279.el6.x86_64
Version: #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 6411, image: oracle@zjtdbrac1
*** 2012-12-16 11:24:48.160
*** SESSION ID:(47.10) 2012-12-16 11:24:48.160
*** CLIENT ID:() 2012-12-16 11:24:48.160
*** SERVICE NAME:(orcl) 2012-12-16 11:24:48.160
*** MODULE NAME:(OEM.CacheModeWaitPool) 2012-12-16 11:24:48.160
*** ACTION NAME:() 2012-12-16 11:24:48.160
--------Dumping Sorted Master Trigger List --------
Trigger Owner : SYSMAN
Trigger Name : MGMT_JOB_EXEC_UPDATE
--------Dumping Trigger Sublists --------
/count
Oradebug command 'unlimit' console output:
Trigger Owner : SYSMAN
Trigger Name : MGMT_JOB_EXEC_UPDATE
--------Dumping Trigger Sublists --------
/count
Oradebug command 'unlimit' console output:
*** 2012-12-30 15:29:34.689
Processing Oradebug command 'event 10046 trace name context forever,level 12'
Processing Oradebug command 'event 10046 trace name context forever,level 12'
*** 2012-12-30 15:29:34.883
Oradebug command 'event 10046 trace name context forever,level 12' console output:
WAIT #0: nam='SQL*Net message to client' ela= 16 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1356852574883890
Oradebug command 'event 10046 trace name context forever,level 12' console output:
WAIT #0: nam='SQL*Net message to client' ela= 16 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1356852574883890
*** 2012-12-30 15:29:47.672
WAIT #0: nam='SQL*Net message from client' ela= 12788609 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1356852587672589
=====================
PARSING IN CURSOR #12 len=22 dep=0 uid=0 ct=3 lid=0 tim=1356852587837435 hv=2763161912 ad='850f0cd8' sqlid='cyzznbykb509s'
select count(*) from t
END OF STMT
PARSE #12:c=7999,e=164516,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1356852587837415
EXEC #12:c=1999,e=10441,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1356852587848245
WAIT #12: nam='SQL*Net message to client' ela= 15 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1356852587848566
WAIT #12: nam='db file sequential read' ela= 431 file#=1 block#=89064 blocks=1 obj#=75659 tim=1356852587866841
WAIT #12: nam='db file scattered read' ela= 700 file#=1 block#=89065 blocks=7 obj#=75659 tim=1356852587877279
WAIT #12: nam='db file scattered read' ela= 18818 file#=1 block#=89072 blocks=8 obj#=75659 tim=1356852587897304
WAIT #12: nam='db file scattered read' ela= 1576 file#=1 block#=89120 blocks=8 obj#=75659 tim=1356852587899573
WAIT #12: nam='db file scattered read' ela= 531 file#=1 block#=89128 blocks=8 obj#=75659 tim=1356852587900974
WAIT #12: nam='db file scattered read' ela= 4781 file#=1 block#=89136 blocks=8 obj#=75659 tim=1356852587906362
WAIT #12: nam='db file scattered read' ela= 16435 file#=1 block#=89144 blocks=8 obj#=75659 tim=1356852587923824
WAIT #12: nam='db file scattered read' ela= 1279 file#=1 block#=89152 blocks=8 obj#=75659 tim=1356852587925925
WAIT #12: nam='db file scattered read' ela= 300 file#=1 block#=89160 blocks=8 obj#=75659 tim=1356852587927015
WAIT #12: nam='db file scattered read' ela= 58690 file#=1 block#=89168 blocks=8 obj#=75659 tim=1356852587986584
WAIT #12: nam='db file scattered read' ela= 1706 file#=1 block#=89184 blocks=8 obj#=75659 tim=1356852587989030
WAIT #12: nam='db file scattered read' ela= 172 file#=1 block#=89192 blocks=8 obj#=75659 tim=1356852587990140
WAIT #12: nam='db file scattered read' ela= 550 file#=1 block#=89200 blocks=8 obj#=75659 tim=1356852587991474
WAIT #12: nam='db file scattered read' ela= 845 file#=1 block#=89208 blocks=8 obj#=75659 tim=1356852587992800
WAIT #12: nam='db file scattered read' ela= 27550 file#=1 block#=89984 blocks=8 obj#=75659 tim=1356852588021143
WAIT #12: nam='db file scattered read' ela= 14293 file#=1 block#=89992 blocks=8 obj#=75659 tim=1356852588036282
WAIT #12: nam='db file scattered read' ela= 7858 file#=1 block#=90000 blocks=8 obj#=75659 tim=1356852588044796
WAIT #12: nam='db file scattered read' ela= 162263 file#=1 block#=90112 blocks=65 obj#=75659 tim=1356852588208739
WAIT #0: nam='SQL*Net message from client' ela= 12788609 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1356852587672589
=====================
PARSING IN CURSOR #12 len=22 dep=0 uid=0 ct=3 lid=0 tim=1356852587837435 hv=2763161912 ad='850f0cd8' sqlid='cyzznbykb509s'
select count(*) from t
END OF STMT
PARSE #12:c=7999,e=164516,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1356852587837415
EXEC #12:c=1999,e=10441,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1356852587848245
WAIT #12: nam='SQL*Net message to client' ela= 15 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1356852587848566
WAIT #12: nam='db file sequential read' ela= 431 file#=1 block#=89064 blocks=1 obj#=75659 tim=1356852587866841
WAIT #12: nam='db file scattered read' ela= 700 file#=1 block#=89065 blocks=7 obj#=75659 tim=1356852587877279
WAIT #12: nam='db file scattered read' ela= 18818 file#=1 block#=89072 blocks=8 obj#=75659 tim=1356852587897304
WAIT #12: nam='db file scattered read' ela= 1576 file#=1 block#=89120 blocks=8 obj#=75659 tim=1356852587899573
WAIT #12: nam='db file scattered read' ela= 531 file#=1 block#=89128 blocks=8 obj#=75659 tim=1356852587900974
WAIT #12: nam='db file scattered read' ela= 4781 file#=1 block#=89136 blocks=8 obj#=75659 tim=1356852587906362
WAIT #12: nam='db file scattered read' ela= 16435 file#=1 block#=89144 blocks=8 obj#=75659 tim=1356852587923824
WAIT #12: nam='db file scattered read' ela= 1279 file#=1 block#=89152 blocks=8 obj#=75659 tim=1356852587925925
WAIT #12: nam='db file scattered read' ela= 300 file#=1 block#=89160 blocks=8 obj#=75659 tim=1356852587927015
WAIT #12: nam='db file scattered read' ela= 58690 file#=1 block#=89168 blocks=8 obj#=75659 tim=1356852587986584
WAIT #12: nam='db file scattered read' ela= 1706 file#=1 block#=89184 blocks=8 obj#=75659 tim=1356852587989030
WAIT #12: nam='db file scattered read' ela= 172 file#=1 block#=89192 blocks=8 obj#=75659 tim=1356852587990140
WAIT #12: nam='db file scattered read' ela= 550 file#=1 block#=89200 blocks=8 obj#=75659 tim=1356852587991474
WAIT #12: nam='db file scattered read' ela= 845 file#=1 block#=89208 blocks=8 obj#=75659 tim=1356852587992800
WAIT #12: nam='db file scattered read' ela= 27550 file#=1 block#=89984 blocks=8 obj#=75659 tim=1356852588021143
WAIT #12: nam='db file scattered read' ela= 14293 file#=1 block#=89992 blocks=8 obj#=75659 tim=1356852588036282
WAIT #12: nam='db file scattered read' ela= 7858 file#=1 block#=90000 blocks=8 obj#=75659 tim=1356852588044796
WAIT #12: nam='db file scattered read' ela= 162263 file#=1 block#=90112 blocks=65 obj#=75659 tim=1356852588208739
*** 2012-12-30 15:29:48.309
WAIT #12: nam='db file scattered read' ela= 97295 file#=1 block#=90177 blocks=63 obj#=75659 tim=1356852588309942
WAIT #12: nam='db file scattered read' ela= 135833 file#=1 block#=90240 blocks=65 obj#=75659 tim=1356852588449849
WAIT #12: nam='db file scattered read' ela= 660 file#=1 block#=90305 blocks=8 obj#=75659 tim=1356852588453572
FETCH #12:c=117982,e=605374,p=329,cr=332,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1356852588454007
STAT #12 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=332 pr=329 pw=0 time=0 us)'
STAT #12 id=2 cnt=73285 pid=1 pos=1 bj=75659 p='TABLE ACCESS FULL T (cr=332 pr=329 pw=0 time=15859 us cost=61 size=0 card=73285)'
WAIT #12: nam='SQL*Net message from client' ela= 342 driver id=1650815232 #bytes=1 p3=0 obj#=75659 tim=1356852588574230
FETCH #12:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1356852588574563
WAIT #12: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=75659 tim=1356852588574608
WAIT #12: nam='SQL*Net message from client' ela= 402 driver id=1650815232 #bytes=1 p3=0 obj#=75659 tim=1356852588575085
=====================
PARSING IN CURSOR #6 len=52 dep=0 uid=0 ct=47 lid=0 tim=1356852588575215 hv=1029988163 ad='7fb99238' sqlid='9babjv8yq8ru3'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #6:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1356852588575214
WAIT #12: nam='db file scattered read' ela= 97295 file#=1 block#=90177 blocks=63 obj#=75659 tim=1356852588309942
WAIT #12: nam='db file scattered read' ela= 135833 file#=1 block#=90240 blocks=65 obj#=75659 tim=1356852588449849
WAIT #12: nam='db file scattered read' ela= 660 file#=1 block#=90305 blocks=8 obj#=75659 tim=1356852588453572
FETCH #12:c=117982,e=605374,p=329,cr=332,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1356852588454007
STAT #12 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=332 pr=329 pw=0 time=0 us)'
STAT #12 id=2 cnt=73285 pid=1 pos=1 bj=75659 p='TABLE ACCESS FULL T (cr=332 pr=329 pw=0 time=15859 us cost=61 size=0 card=73285)'
WAIT #12: nam='SQL*Net message from client' ela= 342 driver id=1650815232 #bytes=1 p3=0 obj#=75659 tim=1356852588574230
FETCH #12:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1356852588574563
WAIT #12: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=75659 tim=1356852588574608
WAIT #12: nam='SQL*Net message from client' ela= 402 driver id=1650815232 #bytes=1 p3=0 obj#=75659 tim=1356852588575085
=====================
PARSING IN CURSOR #6 len=52 dep=0 uid=0 ct=47 lid=0 tim=1356852588575215 hv=1029988163 ad='7fb99238' sqlid='9babjv8yq8ru3'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #6:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1356852588575214
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14359/viewspace-751879/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何使用SQL_TRACE和10046事件SQL事件
- sql_trace and 10046事件SQL事件
- 使用oracle的10046事件跟蹤SQL語句Oracle事件SQL
- 跟蹤SQL - SQL Trace 及 10046 事件SQL事件
- Oracle SQL Trace 和 10046 事件OracleSQL事件
- Oracle SQL Trace 和10046 事件OracleSQL事件
- 10046事件跟蹤會話sql事件會話SQL
- 【SQL*Plus】在SQL*Plus中謹慎使用Ctrl+S快捷鍵SQL
- oracle 10046 事件使用方法Oracle事件
- 使用sql*plus就要show一下SQL
- 利用10046事件收集SQL的trace檔案事件SQL
- 10046事件事件
- 使用SQL*PlusSQL
- sqlplus中glogin.sql的例子SQL
- SQL效能的度量 - 利用10046事件擴充套件SQL跟蹤SQL事件套件
- 10046事件概述事件
- 10046事件演示事件
- 10046事件(轉)事件
- 在windows上,Vim 與 SQL*Plus 結合使用WindowsSQL
- 【SQL*Plus】使用BREAK和COMPUTE在SQL*Plus中得到分組統計結果SQL
- sql loader使用例子SQL
- 使用10046跟蹤sql語句SQL
- 在sql*plus裡編輯SQL命令SQL
- 對使用dblink的10046事件跟蹤事件
- 10046事件詳解事件
- 設定10046事件事件
- 關於10046事件事件
- 10046 事件 與 10053 事件事件
- 【EVENT】使用10046事件獲取SQL語句中繫結變數的具體值事件SQL變數
- solaris下配置使用SQL*Plus歷史命令記錄SQL
- 【sqlplus】SQL*Plus命令使用大全SQL
- SQL Server遊標使用例子SQLServer
- linux下使用者使用sql*plus的環境配置LinuxSQL
- 使用sql*plus編輯sql文字SQL
- 拜年+散分貼《Oracle SQL_TRACE和10046事件優化SQL例項》OracleSQL事件優化
- 使用10046事件跟蹤分析執行計劃事件
- 使用10046事件檢視oracle執行計劃事件Oracle
- 轉貼_使用10046事件獲取SQL語句中繫結變數的具體值事件SQL變數