oracle 11gr2 trace文件,Oracle11gR2中使用dbms_sqldiag.dump_trace来获得10053跟踪文件

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.

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网