用10046跟踪exp

用10046跟踪exp

之前写过一个blog，Oracle expdp为什么比exp快，原理是什么，是从官方文档中获知的，现在通过10046来分析exp的过程。

C:\Users\Administrator>exp LCAMTEST/LCAMTEST@10.10.15.25_LCAM file=d:/test.dmp tables=(BPMS_RU_ACTIVE_INS)

SQL> SELECT p.spid,p.addr,p.pid,s.sid,SUBSTR(s.username,1,15) "USERNAME",
SUBSTR(s.program,1,15) "PROGRAM"
FROM v$process p,v$session s
WHERE s.paddr=p.addr
AND addr=(SELECT paddr FROM v$session WHERE UPPER(program) LIKE 'EXP%');
SPID ADDR PID SID USERNAME PROGRAM
------- ---------------- ----- ---- ---------- --------
15067 000000018C4FFD10 24 10 LCAMTEST exp.exe


要以sys登录，否则oradebug setospid 15067会报ORA-01031: 权限不足。
SQL> oradebug setospid 15067
Oracle pid: 18, Unix process pid: 15067, image: oracle@oracle (S000)
SQL> oradebug unlimit
已处理的语句
SQL> oradebug Event 10046 trace name context forever, level 12
已处理的语句

D:\>tkprof lcamtest_s000_15067.trc 15067.txt
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************


SQL ID: 9c4gmqy4k7a7q
Plan Hash: 3521625488
SELECT /*+NESTED_TABLE_GET_REFS+*/ "LCAMTEST"."BPMS_RU_ACTIVE_INS".*
FROM
"LCAMTEST"."BPMS_RU_ACTIVE_INS"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 460025 18.25 13.83 31870 485572 0 2300122
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 460025 18.25 13.83 31870 485572 0 2300122

Misses in library cache during parse: 0
Parsing user id: 87


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
virtual circuit wait 919983 0.25 309.81
SQL*Net message from client 460026 0.01 36.02
SQL*Net message to client 460025 0.00 0.67
db file scattered read 250 0.07 1.68
********************************************************************************