expdp備份緩慢問題分析

--導(dǎo)出問題分析 --兩個(gè)時(shí)間語句分析，該語句只導(dǎo)出4,059,292 數(shù)據(jù)，10分鐘后數(shù)據(jù)沒有繼續(xù)導(dǎo)出 Snap Id Snap Time Sessions Cursors/Session Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7 End Snap: 39407 13-Jul-14 20:20:07 556 1.7 Elapsed: 109.58 (min

--導(dǎo)出問題分析
--兩個(gè)時(shí)間語句分析，該語句只導(dǎo)出4,059,292 數(shù)據(jù)，10分鐘后數(shù)據(jù)沒有繼續(xù)導(dǎo)出
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7
End Snap: 39407 13-Jul-14 20:20:07 556 1.7
Elapsed: 109.58 (mins)
DB Time: 16.76 (mins)

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7
End Snap: 39398 13-Jul-14 18:50:37 557 1.7
Elapsed: 20.09 (mins)
DB Time: 3.51 (mins)

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7
End Snap: 39417 13-Jul-14 22:00:18 559 1.7
Elapsed: 209.77 (mins)
DB Time: 27.15 (mins)

Begin Snap Time Sessions Cursors/Session
39396 13-Jul-14 18:30:32 558 1.7
End Snap: 39397 13-Jul-14 18:40:34 557 1.7
Elapsed: 10.03 (mins)
DB Time: 1.64 (mins)

--語句沒執(zhí)行完畢
# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
1 2193842017 7,218 0 39397 39397

--問題時(shí)候的導(dǎo)出資源使用
Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 7,218 0.72
CPU Time (ms) 3,035 0.35
Executions 0
Buffer Gets 139,102 0.34
Disk Reads 0 0.00
Parse Calls 0 0.00

Rows 4,059,292 -------------------------其實(shí)我獲取兩個(gè) awrsqlrpt報(bào)告分析，兩個(gè)時(shí)間，也就是18點(diǎn)50 以后，沒有數(shù)據(jù)導(dǎo)出
User I/O Wait Time (ms) 0
Cluster Wait Time (ms) 0
Application Wait Time (ms) 0
Concurrency Wait Time (ms) 0
Invalidations 0
Version Count 2
Sharable Mem(KB) 44

--22點(diǎn)導(dǎo)出時(shí)候資源使用
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 39421 13-Jul-14 22:40:22 557 1.7
End Snap: 39422 13-Jul-14 22:50:23 558 1.7
Elapsed: 10.02 (mins)
DB Time: 1.38 (mins)

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 18,405 18,404.69 22.24
CPU Time (ms) 7,555 7,554.87 10.43
Executions 1
Buffer Gets 354,583 354,583.00 35.03
Disk Reads 0 0.00 0.00
Parse Calls 1 1.00 0.05

Rows 10,555,042 10,555,042.00 ---一共導(dǎo)出怎么多數(shù)據(jù)（可以正常導(dǎo)出）
User I/O Wait Time (ms) 0
Cluster Wait Time (ms) 0
Application Wait Time (ms) 0
Concurrency Wait Time (ms) 0
Invalidations 0
Version Count 2
Sharable Mem(KB) 44

--以上問題給予語句分析：
SQL Id SQL Text
bcjy9a9sp1uw9 SELECT /*+NESTED_TABLE_GET_REFS+*/ "KDTA_HIS"."H_ACK_DIVIDEND".* FROM "KDTA_HIS"."H_ACK_DIVIDEND"

--分析：導(dǎo)出發(fā)生意外，中止或者等待，或者LMON 引起latch
--會話等待事件分析：

SQL> select substrb(session_id,1,6)||'.'||substrb(session_serial#,1,4),
2 substrb(event,1,15),
3 BLOCKING_SESSION,
4 to_char(SAMPLE_TIME, 'yyyymmdd hh24miss'),
5 INSTANCE_NUMBER,
6 sql_id,
7 (select username
8 from dba_users b
9 where b.user_id = a.USER_ID
10 and rownum = 1) username,
11 substrb(program,1,10)

12 from DBA_HIST_ACTIVE_SESS_HISTORY a
13 where (SAMPLE_TIME >=
14 to_date('2014-07-13 01:00:00', 'yyyy-mm-dd hh24:mi:ss') and
15 SAMPLE_TIME <=
16 to_date('2014-07-14 01:10:00', 'yyyy-mm-dd hh24:mi:ss'))
17 and a.module like '%exp%'
18 order by session_id,SAMPLE_TIME;

SUBSTRB(SES SUBSTRB(EVENT,1 BLOCKING_SESSION TO_CHAR(SAMPLE_ INSTANCE_NUMBER SQL_ID USERNAME SUBSTRB(PR
----------- --------------- ---------------- --------------- --------------- ------------- ------------------------------ ----------

7638.4126 20140713 184020 1 88jm6j85t5b8z KDTA_HIS exp.exe
7638.4126 latch free 529 20140713 184151 1 KDTA_HIS exp.exe--------
7638.4126 20140713 184643 1 g4kubvga4gnxc KDTA_HIS exp.exe
7638.4126 SQL*Net more da 20140713 185145 1 dvjmxz20wf8xz KDTA_HIS exp.exe

--529 會話分析
SQL> select substrb(session_id,1,6)||'.'||substrb(session_serial#,1,4),
2 substrb(event,1,15),
3 BLOCKING_SESSION,
4 to_char(SAMPLE_TIME, 'yyyymmdd hh24miss'),
5 INSTANCE_NUMBER,
6 sql_id,
7 substrb(program,1,30)
8 from DBA_HIST_ACTIVE_SESS_HISTORY a

9 where (SAMPLE_TIME >=
10 to_date('2014-07-13 18:30:00', 'yyyy-mm-dd hh24:mi:ss') and
11 SAMPLE_TIME <=
12 to_date('2014-07-13 18:45:00', 'yyyy-mm-dd hh24:mi:ss'))
13 and a.session_id=529
14 order by session_id,SAMPLE_TIME;

SUBSTRB(SES SUBSTRB(EVENT,1 BLOCKING_SESSION TO_CHAR(SAMPLE_ INSTANCE_NUMBER SQL_ID SUBSTRB(PROGRAM,1,30)
----------- --------------- ---------------- --------------- --------------- ------------- ------------------------------
529.1 20140713 183204 2 oracle@KXYEB02 (LMON)
529.1 20140713 183215 2 oracle@KXYEB02 (LMON)

529.1 20140713 183609 1 oracle@KXYEB01 (LMON)
529.1 20140713 184151 1 oracle@KXYEB01 (LMON)------------
529.1 20140713 184218 2 oracle@KXYEB02 (LMON)
529.1 20140713 184332 1 oracle@KXYEB01 (LMON)

--dump systemstate 已經(jīng)無法分析當(dāng)時(shí)數(shù)據(jù)
--分析awr報(bào)告，沒有發(fā)現(xiàn)latch 問題

-- 下次遇到這個(gè)問題的是及時(shí)進(jìn)行一下操作
--1 登錄數(shù)據(jù)庫
sqlplus /nolog
conn / as sysdba
oradebug setmypid
oradebug dump systemstate 10
oradebug tracefile_name

--2 可以的話再執(zhí)行一次
1 獲取process id
ps -ef|grep expdp
id
2 10046分析

SQL> select to_char(a.last_analyzed,'yyyymmdd hh24miss') from dba_tables a where table_name='H_ACK_DIVIDEND';

TO_CHAR(A.LAST_
---------------
20140713 181130

SQL>

異常中止或者lmon引起等待掛起，根源原因分析不出來!

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com