数据泵debug

来源：互联网发布：淘宝网孕妇装秋装编辑：程序博客网时间：2024/04/30 15:01

一．概述

我在之前的Blog里写过2篇有关Oracle 数据泵的Blog，如下：

Oracle 10gData Pump Expdp/Impdp 详解

http://blog.csdn.net/tianlesoftware/article/details/4674224

Oracleexpdp/impdp 使用示例

http://blog.csdn.net/tianlesoftware/article/details/6260138

这两篇都是介绍数据泵的使用。

那么在实际的使用中，我们也会遇到一个问题，发生一些ORA-xx的错误，有具体的错误，我们都可以去google去分析，但是还有情况，就是也不错误，而是在某一步停住了，而且很长时间不动。

这是时候，我们是不好判断数据泵的操作是否正常。

在数据泵卡住的时候，有一个很好的方法来判断：

1．在expdp的时候，我们要观察dump 文件的变化，只要dump 文件大小在变化，那就说明expdp是正常的。

2．在impdp的时候，我们可以及时查看表空间的变化，只要我们的表空间在变化，说明我们的impdp是正常的。

如果在数据泵操作的时候，表空间和dump都没有变化，数据泵操作也停止在某一步不动。那么我们就只能对数据泵进行debug操作。

二．如何对数据泵进行debug？

trace 生成的文件可能很大，所以在进行trace之前，必须先检查dump文件的大小：max_dump_file_size。

[oracle@asm trace]$ orz param max_dump

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

---------------------- --------- -------------- ---------------

max_dump_file_size TRUE TRUE IMMEDIATE unlimited

如果不是unlimited，就进行修改：

ALTER SYSTEM SETmax_dump_file_size = unlimited SCOPE = both;

2.1 使用Data Pump的TRACE 参数

2.1.1 TRACE 说明

启动trace 功能只需要在expdp/impdp 命令后加上一个trace 参数，该参数由一个7位的16进制数据组成。

前三位指定Data Pump组件的代码，后四位一般是:0300。

任何已0开头的trace的值都会被忽略，trace值不区分大小写。

如：

TRACE = 04A0300 或者 TRACE=4a0300

trace值的一些注意事项：

（1） trace 值不要超过7位十六进制数字。

（2）不要添加十六进制的0x符号。

（3）不要将16进制转换成10进制。

（4）会忽略最前面的0，即使长度不满足7位。

（5）参数不区分大小写。

在使用trace 参数时，执行数据泵操作的用户需要具有DBA 角色或者EXP_FULL_DATABASE /IMP_FULL_DATABASE的角色，如果权限不足，就会报ORA-31631的错误。

ORA-31631: privileges are required

解决方法：给用户赋权。如：

GRANT exp_full_database TO tianlesoftware;

操作完成之后，在收回权限即可：

revoke exp_full_database from tianlesoftware;

使用TRACE的示例：

expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log TABLES=empTRACE=480300

2.1.2 TRACE值的具体计算规则

在前面我们讲了TRACE 值的前3位表示的是数据泵的组件代码，具体如下：

-- Summary of Data Pump trace levels:

-- ==================================

Trace DM DW ORA Lines

level trc trc trc in

(hex) file file file trace Purpose

------- ---- ---- ---- -----------------------------------------------------

10300 x x x SHDW: To trace the Shadowprocess (API) (expdp/impdp)

20300 x x x KUPV: To trace Fixed table

40300 x x x 'div' To trace Process services

80300 x KUPM: To trace Master ControlProcess (MCP) (DM)

100300 x x KUPF: To trace File Manager

200300 x x x KUPC: To trace Queue services

400300 x KUPW: To trace Workerprocess(es) (DW)

800300 x KUPD: To trace DataPackage

1000300 x META: To trace Metadata Package

--- +

1FF0300 x x x 'all' To trace all components (full tracing)

如果想trace 所有的数据泵组件，只需要指定trace的值为1ff0300即可。

如果我们想通知trace 多个数据泵组件，就把这些组件的代码叠加起来即可，如：

-- Example of combination(last 4 digits are usually 0300):

40300 to trace Process services

80300 to trace Master Control Process (MCP)

400300 to trace Worker process(es)

-- +

4C0300 to trace Process services and MasterControl and Worker processes

叠加的时候，最后4位不变，把前面3位的值相加。

注意：

Oracle 建议使用480300的trace值，设置该值将会trace Master Control process (MCP) 和 theWorker process(es)。

2.1.3 TRACE 文件的log 位置

2个trace 文件在BACKGROUND_DUMP_DEST目录下：

Master Process trace file: <SID>_dm<number>_<process_id>.trc
Worker Process trace file: <SID>_dw<number>_<process_id>.trc

还有一个在USER_DUMP_DEST目录：

Shadow Processtrace file: <SID>_ora_<process_id>.trc

[oracle@asm u01]$ orzparam background

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

----------------------- --------- -------------- ----------------------------------------

background_core_dump TRUE FALSE FALSE partial

background_dump_dest TRUE FALSE IMMEDIATE /u01/app/oracle/diag/rdbms/dave/dave/trace

[oracle@asm u01]$ orzparam user_dump

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

--------------------- --------- -------------- ----------------------------------------

user_dump_dest TRUE FALSE IMMEDIATE/u01/app/oracle/diag/rdbms/dave/dave/trace

2.1.4 TRACE 使用示例

SQL> set lin 160 pages 200

SQL> col owner for a10

SQL> col DIRECTORY_PATH for a50

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- --------------------------------------------------------------------------------

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/11.2.0/db_1/ccr/state

SYS DATA_PUMP_DIR /u01/app/oracle/admin/dave/dpdump/

SYS XMLDIR /u01/app/oracle/11.2.0/db_1/rdbms/xml

SQL>

SQL> create directory backup as'/u01/backup';

Directory created.

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- --------------------------------------------------------------------------------

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/11.2.0/db_1/ccr/state

SYS DATA_PUMP_DIR /u01/app/oracle/admin/dave/dpdump/

SYS XMLDIR /u01/app/oracle/11.2.0/db_1/rdbms/xml

SYS BACKUP /u01/backup

--创建测试表：tianlesoftware：

SQL> conn system/oracle;

Connected.

SQL> create table tianlesoftware asselect * from dba_objects;

Table created.

--使用trace：480300进行导出：

[oracle@asm u01]$ expdp system/oracleDIRECTORY=backup DUMPFILE=dave.dmp LOGFILE=dave.log TABLES=tianlesoftwareTRACE=480300

Export: Release 11.2.0.3.0 - Production onMon May 27 19:50:49 2013

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

Starting"SYSTEM"."SYS_EXPORT_TABLE_01": system/******** DIRECTORY=backupDUMPFILE=dave.dmp LOGFILE=dave.log TABLES=tianlesoftware TRACE=480300

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 9 MB

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object typeTABLE_EXPORT/TABLE/PRE_TABLE_ACTION

. . exported"SYSTEM"."TIANLESOFTWARE" 7.215 MB 74608 rows

Master table"SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01is:

/u01/backup/dave.dmp

Job"SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at19:51:22

--查看trace文件：

-rw-r----- 1 oracle asmadmin 1433 May 27 19:51 dave_dw00_7486.trm

-rw-r----- 1 oracle asmadmin 32109 May 27 19:51 dave_dw00_7486.trc

-rw-r----- 1 oracle asmadmin 1417 May 27 19:51 dave_dm00_7484.trm

-rw-r----- 1 oracle asmadmin 25025 May 27 19:51 dave_dm00_7484.trc

-rw-r----- 1 oracle asmadmin 2908 May 27 19:50 dave_ora_7480.trc

[oracle@asm trace]$ head -50 dave_dw00_7486.trc

Trace file/u01/app/oracle/diag/rdbms/dave/dave/trace/dave_dw00_7486.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/11.2.0/db_1

System name: Linux

Node name: asm

Release: 2.6.32-100.34.1.el6uek.x86_64

Version: #1 SMP Wed May 25 17:46:45 EDT 2011

Machine: x86_64

Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 42

Unix process pid: 7486, image: oracle@asm(DW00)

*** 2013-05-27 19:50:55.052

*** SESSION ID:(24.127) 2013-05-2719:50:55.052

*** CLIENT ID:() 2013-05-27 19:50:55.052

*** SERVICE NAME:(SYS$BACKGROUND) 2013-05-2719:50:55.052

*** MODULE NAME:() 2013-05-27 19:50:55.052

*** ACTION NAME:() 2013-05-27 19:50:55.052

KUPP:19:50:55.051: Current trace/debugflags: 00480300 = 4719360

*** MODULE NAME:(Data Pump Worker)2013-05-27 19:50:55.054

*** ACTION NAME:(SYS_EXPORT_TABLE_01)2013-05-27 19:50:55.054

KUPW:19:50:55.054: 0: ALTER SESSION ENABLEPARALLEL DML called.

KUPW:19:50:55.054: 0: ALTER SESSION ENABLEPARALLEL DML returned.

KUPC:19:50:55.098: Setting remote flag forthis process to FALSE

prvtaqis - Enter

prvtaqis subtab_name upd

prvtaqis sys table upd

KUPW:19:50:55.665: 0: KUPP$PROC.WHATS_MY_IDcalled.

KUPW:19:50:55.665: 1: KUPP$PROC.WHATS_MY_IDreturned.

KUPW:19:50:55.666: 1: worker max messagenumber: 1000

KUPW:19:50:55.668: 1: Full cluster accessallowed

KUPW:19:50:55.669: 1: Original job starttime: 13-MAY-27 07:50:51 PM

KUPW:19:50:55.671: 1: Seqno 16 isTABLE_EXPORT/TABLE/TABLE_DATA

KUPW:19:50:55.671: 1: Seqno 63 isTABLE_EXPORT/TABLE/INDEX/TABLE_DATA

KUPW:19:50:55.671: 1:KUPP$PROC.WHATS_MY_NAME called.

KUPW:19:50:55.671: 1:KUPP$PROC.WHATS_MY_NAME returned. Process name: DW00

KUPW:19:50:55.671: 1:KUPV$FT_INT.GET_INSTANCE_ID called.

KUPW:19:50:55.674: 1:KUPV$FT_INT.GET_INSTANCE_ID returned. Instance name: dave

KUPW:19:50:55.679: 1: ALTER SESSION ENABLERESUMABLE called.

KUPW:19:50:55.679: 1: ALTER SESSION ENABLERESUMABLE returned.

KUPW:19:50:55.682: 1: KUPF$FILE.INITcalled.

KUPW:19:50:55.883: 1: KUPF$FILE.INITreturned.

KUPW:19:50:55.886: 1:KUPF$FILE.GET_MAX_CSWIDTH called.

KUPW:19:50:55.886: 1:KUPF$FILE.GET_MAX_CSWIDTH returned.

[oracle@asm trace]$

[oracle@asm trace]$ head -50 dave_dm00_7484.trc

Trace file/u01/app/oracle/diag/rdbms/dave/dave/trace/dave_dm00_7484.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/11.2.0/db_1

System name: Linux

Node name: asm

Release: 2.6.32-100.34.1.el6uek.x86_64

Version: #1 SMP Wed May 25 17:46:45 EDT 2011

Machine: x86_64

Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 35

Unix process pid: 7484, image: oracle@asm(DM00)

*** 2013-05-27 19:50:50.817

*** SESSION ID:(66.5) 2013-05-2719:50:50.817

*** CLIENT ID:() 2013-05-27 19:50:50.817

*** SERVICE NAME:(SYS$USERS) 2013-05-2719:50:50.817

*** MODULE NAME:() 2013-05-27 19:50:50.817

*** ACTION NAME:() 2013-05-27 19:50:50.817

KUPP:19:50:50.816: Current trace/debugflags: 00480300 = 4719360

*** MODULE NAME:(Data Pump Master)2013-05-27 19:50:50.823

*** ACTION NAME:(SYS_EXPORT_TABLE_01)2013-05-27 19:50:50.823

KUPC:19:50:50.823: Setting remote flag forthis process to FALSE

prvtaqis - Enter

prvtaqis subtab_name upd

prvtaqis sys table upd

KUPM:19:50:50.884: Attached to controlqueue as MCP

KUPM:19:50:50.884: While starting, controlqueue subscriber count is: 2

KUPP:19:50:50.884: Initialization completefor master process DM00

KUPM:19:50:50.916: Entered main loop

KUPM:19:50:50.922: ****IN DISPATCH at71450, request type=1001

KUPM:19:50:50.922: Current user is: SYSTEM

KUPM:19:50:50.922: hand :=DBMS_DATAPUMP.OPEN ('EXPORT', 'TABLE', '', 'SYS_EXPORT_TABLE_01', '', '2');

*** 2013-05-27 19:50:51.719

KUPM:19:50:51.719: Resumable enabled

KUPM:19:50:51.734: Entered state: DEFINING

KUPM:19:50:51.734: initing file system

KUPM:19:50:51.749: ****OUT DISPATCH,request type=1001, response type =2041

kwqberlst !retval block

kwqberlst rqan->lagno_kwqiia 5

kwqberlst rqan->lascn_kwqiia > 0 block

kwqberlst rqan->lascn_kwqiia 5

kwqberlst ascn 1644468 lascn 22

KUPM:19:50:51.764: ****IN DISPATCH at71451, request type=1035

[oracle@asm trace]$ head -50 dave_ora_7480.trc

Trace file/u01/app/oracle/diag/rdbms/dave/dave/trace/dave_ora_7480.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/11.2.0/db_1

System name: Linux

Node name: asm

Release: 2.6.32-100.34.1.el6uek.x86_64

Version: #1 SMP Wed May 25 17:46:45 EDT 2011

Machine: x86_64

Instance name: dave

Redo thread mounted by this instance: 1

Oracle process number: 36

Unix process pid: 7480, image: oracle@asm(TNS V1-V3)

*** 2013-05-27 19:50:49.871

*** SESSION ID:(21.359) 2013-05-2719:50:49.871

*** CLIENT ID:() 2013-05-27 19:50:49.871

*** SERVICE NAME:(SYS$USERS) 2013-05-2719:50:49.871

*** MODULE NAME:(ude@asm (TNS V1-V3))2013-05-27 19:50:49.871

*** ACTION NAME:() 2013-05-27 19:50:49.871

KUPP:19:50:49.871: Input trace/debug flags:00480300 = 4719360

KUPP:19:50:49.873: Current trace/debugflags: 00480300 = 4719360

*** 2013-05-27 19:50:50.633

KUPC:19:50:50.633: Setting remote flag forthis process to FALSE

prvtaqis - Enter

prvtaqis subtab_name upd

prvtaqis sys table upd

prvtaqis - Enter

prvtaqis subtab_name upd

prvtaqis sys table upd

kwqberlst rqan->lascn_kwqiia > 0block

kwqberlst rqan->lascn_kwqiia 5

kwqberlst ascn 1644478 lascn 22

kwqberlst !retval block

kwqberlst rqan->lagno_kwqiia 5

*** 2013-05-27 19:50:51.760

kwqberlst rqan->lascn_kwqiia > 0block

kwqberlst rqan->lascn_kwqiia 5

kwqberlst ascn 1644478 lascn 22

kwqberlst !retval block

kwqberlst rqan->lagno_kwqiia 5

kwqberlst rqan->lascn_kwqiia > 0block

kwqberlst rqan->lascn_kwqiia 5

kwqberlst ascn 1644478 lascn 22

kwqberlst !retval block

[oracle@asm trace]$

2.2 使用22923 事件

--具体步骤如下：

1. 设置22923 event trace expdp操作：

SQL> alter system set events '22923 trace nameerrorstack level 3';

2. 执行expdp 命令。

3. 取消22923 event：

SQL> alter system set events '22923trace name errorstack off';

--设置event：

SQL> alter system set events '22923trace name errorstack level 3';

System altered.

--执行expdp：

[oracle@asm backup]$expdp system/oracle DIRECTORY=backup DUMPFILE=dave.dmp LOGFILE=dave.logTABLES=tianlesoftware

Export: Release 11.2.0.3.0 - Production onMon May 27 20:15:59 2013

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

Starting"SYSTEM"."SYS_EXPORT_TABLE_02": system/******** DIRECTORY=backupDUMPFILE=dave.dmp LOGFILE=dave.log TABLES=tianlesoftware

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 9 MB

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object typeTABLE_EXPORT/TABLE/PRE_TABLE_ACTION

. . exported"SYSTEM"."TIANLESOFTWARE" 7.215 MB 74608 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_02"successfully loaded/unloaded

******************************************************************************

Dump file set forSYSTEM.SYS_EXPORT_TABLE_02 is:

/u01/backup/dave.dmp

Job"SYSTEM"."SYS_EXPORT_TABLE_02" successfully completed at20:16:15

--查看trace,其生成的trace 和我们2.1 节一样。

2个trace 文件在BACKGROUND_DUMP_DEST目录下：

Master Process trace file: <SID>_dm<number>_<process_id>.trc
Worker Process trace file: <SID>_dw<number>_<process_id>.trc

还有一个在USER_DUMP_DEST目录：

Shadow Processtrace file: <SID>_ora_<process_id>.trc

--取消event：

SQL> alter system set events'22923 trace name errorstack off';

System altered.

2.3 使用10046 事件

10046 事件有如下级别：

event 10046, level 1 = enable standardSQL_TRACE functionality
event 10046, level 4 = as level 1, plus trace the BIND values
event 10046, level 8 = as level 1, plus trace the WAITs
event 10046, level 12 = as level 1, plus trace the BIND values and the WAITs

不同级别的使用情况如下：

level 1: lowest level tracing - not alwayssufficient to determine cause of errors;

level 4: useful when an error in DataPump's worker or master process occurs;

level 12: useful when there is an issuewith Data Pump performance.

注意：

当我们设置10046的级别高于8或者12的时候，需要将TIMED_STATISTICS设置为TRUE. 临时的将这个参数设置为true，可以将trace数据性能的影响降到最低。

一般只有遇到性能问题时，才会使用8或者12的level。

[oracle@asm trace]$ orz param timed_statistics

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

------------------- --------- -------------- ----------

timed_statistics TRUE TRUE IMMEDIATE TRUE

在11gR2里，该参数默认为true。

如果不是，调整SQL 如下：

ALTER SYSTEM SET timed_statistics = TRUE SCOPE = memory;
ALTER SYSTEM SET timed_statistics = FALSE SCOPE = memory;

2.3.1 在数据库级别设置

可能的expdp/impdp 异常循序结束了，这时候我们就没有办法根据进程进行信息的捕捉。因此为了分析，可能就需要重新运行一次，这时候就可以在数据库级别设置10046事件。

--设置：

CONNECT / as sysdba
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';

注意：在数据库级别设置，会trace所有进程。所以在生产系统慎用，除非有停机窗口，否则会影响系统性能。

--启动Export/Impdp:

[oracle@asm backup]$ expdp system/oracleDIRECTORY=backup DUMPFILE=dave.dmp LOGFILE=dave.log TABLES=tianlesoftware

--在数据泵完成后，立即取消10046的设置：

ALTER SYSTEM SET EVENTS '10046 trace name context off';

2.3.2 对当前正在运行的数据泵进程进行10046 trace

2.3.2.1 查看expdp进程

--进行expdp：

[oracle@asm trace]$ expdp system/oracleDIRECTORY=backup DUMPFILE=dave.dmp LOGFILE=dave.log TABLES=tianlesoftware

Export: Release 11.2.0.3.0 - Production onMon May 27 20:56:52 2013

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic StorageManagement, OLAP, Data Mining

and Real Application Testing options

Starting"SYSTEM"."SYS_EXPORT_TABLE_02": system/******** DIRECTORY=backupDUMPFILE=dave.dmp LOGFILE=dave.log TABLES=tianlesoftware

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 9 MB

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object typeTABLE_EXPORT/TABLE/PRE_TABLE_ACTION

--查看数据泵进程的信息：

set lines 150 pages 100 numwidth 7
col username for a10
col spid for a7

SQL> col program for a25

SQL> select to_char(sysdate,'YYYY-MM-DDHH24:MI:SS') "DATE", s.program, s.sid,

s.status, s.username, d.job_name, p.spid, s.serial#, p.pid

2 3 from v$session s, v$process p,dba_datapump_sessions d

4 where p.addr=s.paddr ands.saddr=d.saddr;

DATE PROGRAM SID STATUS USERNAME JOB_NAME SPID SERIAL# PID

------------------- --------------------- ------- -------- ---------------------------------- ------- ------- -------

2013-05-27 20:59:30 oracle@asm (DW00) 23 ACTIVE SYSTEM SYS_EXPORT_TABLE_02 8173 311 23

2013-05-27 20:59:30 oracle@asm (DM00) 58 ACTIVE SYSTEM SYS_EXPORT_TABLE_02 8171 85 36

2013-05-27 20:59:30 ude@asm (TNS V1-V3) 22 ACTIVE SYSTEM SYS_EXPORT_TABLE_02 8167 1077 34

2.3.2.2 使用sys.dbms_system.set_ev设置10046

在上节的查询结果里：

Data Pump Master process (DM00)的SID 是58，serial#是85.

Data Pump Worker process (DW01)的SID 是23，serial#是311.

使用10046 跟踪活动session的语法如下：

Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'')

--在level 4跟踪Worker process进程(Bind values)：

execute sys.dbms_system.set_ev(23,311,10046,4,'');

-- stop tracing:
execute sys.dbms_system.set_ev(23,311,10046,0,'');

--在level 8 跟踪Master进程（Waits）：

execute sys.dbms_system.set_ev(143,50,10046,8,'');

-- stop tracing:
execute sys.dbms_system.set_ev(143,50,10046,0,'');

2.3.2.3 使用oradebug 设置10046

上节使用的是SQL中操作，其实用oradebug操作更方便。

可以在oradebug中设置SPID，来进行trace：

--在level 4跟踪Worker process进程(Bind values)：

oradebug setospid 8173
oradebug unlimit
oradebug event 10046 trace name context forever, level 4
oradebug tracefile_name

--在level 8 跟踪Master进程（Waits）：

oradebug setospid 8171
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name

--stop tracing:

oradebug event 10046 trace name context off

2.3.2.4 使用tkprof 分析trace文件

10046 生成的trace 文件可读性并不好，所有我们可以使用tkprof工具进行格式化，方便阅读。

如：

$ tkprof orcl_dm00_17292.trctkprof_orcl_dm00_17292.out waits=y sort=exeela
$ tkprof orcl_dw01_17294.trctkprof_orcl_dw01_17294.out waits=y sort=exeela

[oracle@asm dave]$ tkprof

Usage: tkprof tracefile outputfile[explain= ] [table= ]

[print= ] [insert= ] [sys= ][sort= ]

table=schema.tablename Use'schema.tablename' with 'explain=' option.

explain=user/password Connectto ORACLE and issue EXPLAIN PLAN.

print=integer List only the first 'integer' SQLstatements.

aggregate=yes|no

insert=filename List SQLstatements and data inside INSERT statements.

sys=no TKPROF does notlist SQL statements run as user SYS.

record=filename Record non-recursivestatements found in the trace file.

waits=yes|no Record summaryfor any wait events found in the trace file.

sort=option Set of zero ormore of the following sort options:

prscnt number of times parse wascalled

prscpu cpu time parsing

prsela elapsed time parsing

prsdsk number of disk readsduring parse

prsqry number of buffers forconsistent read during parse

prscu number of buffers forcurrent read during parse

prsmis number of misses inlibrary cache during parse

execnt number of execute wascalled

execpu cpu time spent executing

exeela elapsed time executing

exedsk number of disk readsduring execute

exeqry number of buffers forconsistent read during execute

execu number of buffers forcurrent read during execute

exerow number of rows processedduring execute

exemis number of library cachemisses during execute

fchcnt number of times fetch wascalled

fchcpu cpu time spent fetching

fchela elapsed time fetching

fchdsk number of disk readsduring fetch

fchqry number of buffers forconsistent read during fetch

fchcu number of buffers forcurrent read during fetch

fchrow number of rows fetched

userid userid of user that parsedthe cursor

from:http://blog.csdn.net/tianlesoftware/article/details/9003303