EXPDP The slow backup speed problem

--Export analysis
--Two time report analysis, the declaration exports only 4,059,292 data, 10 minutes after the data did not continue to export
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)

--Statement not running complete
# Plan Hash Value Total Elapsed time (ms) executions 1st capture Snap ID last capture snap ID
1 2193842017 7,218 0 39397 39397

--Export resource use when problem
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-------------------------I actually get two AWRSQLRPT report analysis. Two times, that is, after 18 point 50, no data export
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 Point export time Resource use
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---How much data is exported (can be exported normally)
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

--The above questions give the statement analysis:
SQL Id SQL Text
BCJY9A9SP1UW9 Select/*+nested_table_get_refs+*/"Kdta_his". " H_ack_dividend ". * from" kdta_his "." H_ack_dividend "

--Analysis: An unexpected export occurred. Abort or wait, or lmon cause latch
--Session wait event analysis:

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              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      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 Session Analysis
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 >=
Ten to_date (' 2014-07-13 18:30:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
Sample_time <=
To_date (' 2014-07-13 18:45:00 ', ' yyyy-mm-dd hh24:mi:ss ')
a.session_id=529
The 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[email protected](Lmon)
529.1 20140713 183215 2[email protected](Lmon)

529.1 20140713 183609 1[email protected](Lmon)
529.1 20140713 184151 1[email protected](Lmon)------------
529.1 20140713 184218 2[email protected](Lmon)
529.1 20140713 184332 1[email protected](Lmon)

--dump SystemState is unable to analyze the data at that time
--Analyze the AWR report. No latch problem found

--The next time you encounter this problem is a timely operation
--1 Login Database
Sqlplus/nolog
Conn/as SYSDBA
Oradebug Setmypid
Oradebug Dump SystemState 10
Oradebug Tracefile_name

--2 can run it again.
1 Get Process ID
Ps-ef|grep EXPDP
Id
2 10046 Analysis

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>

Abnormal abort or Lmon cause wait hang, root cause analysis not come out!

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

EXPDP The slow backup speed problem