--导出问题分析 --两个时间语句分析，该语句只导出4,059,292 数据，10分钟后数据没有继续导出 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
--导出问题分析
--两个时间语句分析，该语句只导出4,059,292 数据，10分钟后数据没有继续导出
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)
--语句没执行完毕
# plan hash value total elapsed time(ms) executions 1st capture snap id last capture snap id
1 2193842017 7,218 0 39397 39397
--问题时候的导出资源使用
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 -------------------------其实我获取两个 awrsqlrpt报告分析，两个时间，也就是18点50 以后，没有数据导出
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点导出时候资源使用
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 ---一共导出怎么多数据（可以正常导出）
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
--分析：导出发生意外，中止或者等待，或者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 已经无法分析当时数据
--分析awr报告，没有发现latch 问题
-- 下次遇到这个问题的是及时进行一下操作
--1 登录数据库
sqlplus /nolog
conn / as sysdba
oradebug setmypid
oradebug dump systemstate 10
oradebug tracefile_name
--2 可以的话再执行一次
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引起等待挂起，根源原因分析不出来!