oracle 11gr2开始在不执行sql语句的情况下可以使用dbms_sqldiag.dump_trace来生成10053跟踪文件。
oracle 11gr2开始在不执行sql语句的情况下可以使用dbms_sqldiag.dump_trace来生成10053跟踪文件。它的操作步骤如下:
1.先执行sql语句
sql> column slq_text format a30
sql> select sysdate from dual;
sysdate
------------
15-aug-14
2.通过sql语句的文本来搜索v$sql找到该语句相应的sql_id.
sql> select sql_id from v$sql where sql_text like 'select sysdate from dual%';
sql_id
-------------
7h35uxf5uhmm1
3.执行dbms_sqldiag.dump_trace过程来生成10053跟踪文件
sql> execute dbms_sqldiag.dump_trace(p_sql_id=>'7h35uxf5uhmm1',p_child_number=>0,p_component=>'compiler',p_file_id=>'diag');
pl/sql procedure successfully completed.
sql> show parameter user_dump_dest
name type value
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/jyc
s/jycs/trace
4.找到生成的10053跟踪文件
sql> host ls -lrt /u01/app/oracle/diag/rdbms/jycs/jycs/trace/*diag*.trc
-rw-r----- 1 oracle oinstall 66194 aug 15 09:49 /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_8474_diag.trc
5.查看10053跟踪文件的内容
sql> host cat /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_8474_diag.trc
trace file /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_8474_diag.trc
oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
oracle_home = /u01/app/oracle/11.2.0/db
system name: linux
node name: jyrac1
release: 2.6.18-164.el5
version: #1 smp tue aug 18 15:51:48 edt 2009
machine: x86_64
instance name: jycs
redo thread mounted by this instance: 1
oracle process number: 33
unix process pid: 8474, image: oracle@jyrac1 (tns v1-v3)
*** 2014-08-15 09:49:11.244
*** session id:(146.49619) 2014-08-15 09:49:11.244
*** client id:() 2014-08-15 09:49:11.244
*** service name:(sys$users) 2014-08-15 09:49:11.244
*** module name:(sqlplus@jyrac1 (tns v1-v3)) 2014-08-15 09:49:11.244
*** action name:() 2014-08-15 09:49:11.244
enabling tracing for cur#=7 sqlid=84zghzsc8b7rj recursive
parsing cur#=7 sqlid=84zghzsc8b7rj len=50
sql=/* sql analyze(146,0) */ select sysdate from dual
end parsing of cur#=7 sqlid=84zghzsc8b7rj
semantic analysis cur#=7 sqlid=84zghzsc8b7rj
optimizer information
******************************************
----- current sql statement for this session (sql_id=84zghzsc8b7rj) -----
/* sql analyze(146,0) */ select sysdate from dual
----- pl/sql stack -----
----- pl/sql call stack -----
object line object
handle number name
0x7f6236e8 145 package body sys.dbms_sqltune_internal
0x7f6236e8 12043 package body sys.dbms_sqltune_internal
0x854a3268 1276 package body sys.dbms_sqldiag
0x758e9c58 1 anonymous block
*******************************************
................省略
kkfdapdml
oct:3 pgadep:1 pdml mode:0 px allowed dml allowed rowlock is not intent
=> not allowed
/* sql analyze(146,0) */ select sysdate from dual
registered qb: sel$1 0xfb907cb0 (parser)
---------------------
query block signature
---------------------
signature (): qb_name=sel$1 nbfros=1 flg=0
fro(0): flg=4 objn=116 hint_alias=dual@sel$1
spm: statement not found in smb
spm: statement not a candidate for auto-capture
kkfdpaforceprm return false
kkfdpaprm: use dictionary dop(1) on table
kkfdpaprm:- the table : 116
kkfdpaprm:dop = 1 (computed from hint/dictionary/autodop)
kkfdpaprm:- returns false, i.e (serial)
qksbgcreatesessionenv: inherit from system bgc:0x2b4afb8f0888
qksbgcreatecursorenv: create a new one and copy from the session bgc:0x2b4afb90aef0
**************************
automatic degree of parallelism (adop)
**************************
kkfdisautodopsupported: yes, ctxoct is 3
automatic degree of parallelism is disabled: parameter.