Analysis of slow expdp backup

-- Export Problem Analysis
-- Two time Statement Analysis: This statement exports only 4,059,292 of the Data. After 10 minutes, the data does not continue to be exported.
Snap ID snap time sessions cursors/session
Begin snap: 39396 13-jul14 18:30:32 558 1.7
End snap: 39407 13-jul14 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-jul14 18:30:32 558 1.7
End snap: 39398 13-jul14 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-jul14 18:30:32 558 1.7
End snap: 39417 13-jul14 22:00:18 559 1.7
Elapsed: 209.77 (mins)
DB time: 27.15 (mins)

Begin snap time sessions cursors/session
39396 13-jul14 18:30:32 558 1.7
End snap: 39397 13-jul14 18:40:34 557 1.7
Elapsed: 10.03 (mins)
DB time: 1.64 (mins)

 

-- The statement has not been executed.
# 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 usage in case of problems
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 cballs 0 0.00

Rows 4,059,292 --------------------------- in fact, I have obtained two awrsqlrpt reports for analysis. There are no data exported after.
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

 

-- Resource usage during export
Snap ID snap time sessions cursors/session
Begin snap: 39421 13-jul14 22:40:22 557 1.7
End snap: 39422 13-jul14 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 cballs 1 1.00 0.05

Rows 10,555,042 10,555,042.00 --- How to export a total of multiple data records (which 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

-- Analyze the preceding statements:
SQL id SQL text
Bcjy9a9sp1uw9 select/* + nested_table_get_refs + */"kdta_his". "h_ack_dividend". * from "kdta_his". "h_ack_dividend"

-- Analysis: if an export accident occurs, stop or wait, or lmon causes latch.
-- Session wait event analysis:

SQL> select substrb (session_id,) | '.' | substrb (session_serial ),
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
13 where (sample_time> =
14 to_date ('2017-07-13 01:00:00 ', 'yyyy-mm-dd hh24: MI: ss') and
15 sample_time <=
16 to_date ('2017-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 session Analysis
SQL> select substrb (session_id,) | '.' | substrb (session_serial ),
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

9 where (sample_time> =
10 to_date ('2017-07-13 18:30:00 ', 'yyyy-mm-dd hh24: MI: ss') and
11 sample_time <=
12 to_date ('2017-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 [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 cannot analyze the current data
-- The AWR report is analyzed and no latch problems are found.

-- The next time you encounter this problem, perform the operation in a timely manner.
-- 1 log on to the database
Sqlplus/nolog
Conn/As sysdba
Oradebug setmypid
Oradebug dump systemstate 10
Oradebug tracefile_name

 

-- 2. Execute the command again.
1. Obtain the process ID.
PS-Ef | grep expdp
ID
2 10046 Analysis

SQL> select to_char (A. last_analyzed, 'yyyymmdd hh24mis') from dba_tables A where table_name = 'H _ ack_dividend ';

To_char (A. Last _
---------------
20140713 181130

SQL>

 

An exception is aborted or lmon causes a pending suspension. The root cause cannot be analyzed!