【性能优化】数据泵impdp导入时间特别久及导入中断后继续导入案例分享

导入命令：
impdp system/password dumpfile=expdat.dmp directory=dmp cluster=no logfile=impdp.log
复制

col owner_name for a20
col job_name for a20
col state for a20
set linesize 1000
col operation for a20
col job_mode for a20
select * from dba_datapump_jobs where job_name = 'SYS_IMPORT_FULL_01';
复制

show parameter sga_target
show parameter memory_target
show parameter shared_pool_size


select shrink_phase_knlasg from X$KNLASG;
复制

方法一：查询dba_hist_active_sess_history视图
（1）创建一个临时表
SQL> conn  as sysdba
SQL> create table system.m_ash as select * from dba_hist_active_sess_history
where SAMPLE_TIME between TO_TIMESTAMP ('2024-02-25 18:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and TO_TIMESTAMP ('2024-02-26 08:00:00', 'YYYY-MM-DD HH24:MI:SS');
（2）基于临时表进行快速查询
select event,count(*) from m_ash group by event order by 2 asc
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read 17158
KSV master wait 46015
Streams AQ: enqueue blocked on low memory 84427
（3）查看“Streams AQ: enqueue blocked on low memory”等待事件对应的会话信息
select session_id,count(*) from m_ash where event='Streams AQ: enqueue blocked on low memory' 
group by session_id order by 2;
SESSION_ID COUNT(*)
---------- ----------
384 4147
4088 4165
99 4242
3144 5172
3519 5233
576 7726
4374 8690
3 36834
（4）检查这些会话都是与"Data Pump Worker"相关。


方法二：提取导入期间某一个小时的AWR性能的报告
@?/rdbms/admin/awrrpt.sql


方法三：开启10046事件进行SQL跟踪。
开：ALTER SYSTEM SET events '10046 trace name context forever, level 12'
-- run import
关：ALTER SYSTEM SET events '10046 trace name context off';
The Data Pump Master and Worker trace file were analyzed with:
$ tkprof <DW_TRACE_FILE>.trc <DW_OUTPUT_FILE>.out waits=y sort=exeela
复制

文档 ID 1596645.1
Buffered messages memory is not freed quickly which can result in enqueue blocked on low memeory.
If the size of the streams_pool is being modified, then this can result in excessive waits 
for 'Streams AQ: enqueue blocked on low memory'.
Every occurrence for this event causes a 1 minutes delay.


文档 ID 2386566.1
A load in the buffer cache and streams pool memory is being moved to buffer cache.
复制

connect  as sysdba
select shrink_phase_knlasg from X$KNLASG;
“1”。该值表示 streams pool 处于收缩阶段。当 streams pool 完成收缩时，该值应返回”0”，


alter system set events 'immediate trace name mman_create_def_request level 6';
复制

CONNECT  as sysdba
ALTER SYSTEM SET streams_pool_size=300m SCOPE=spfile;
ALTER SYSTEM SET "_disable_streams_pool_auto_tuning"=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP
复制

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "PETER"."OBJ2" 163.5 MB 1236887 rows
. . imported "PETER"."OBJ1" 153.8 MB 1164128 rows 《〈〈〈〈〈〈这里数据库重起
----数据库重启
UDI-01089: operation generated ORACLE error 1089
ORA-01089: immediate shutdown or close in progress - no operations are permitted
ORA-06512: at "SYS.DBMS_AQ", line 1127
ORA-06512: at "SYS.KUPC$QUE_INT", line 556
ORA-06512: at "SYS.KUPC$QUE_INT", line 1703
ORA-06512: at line 1
Process ID: 9138
Session ID: 73 Serial number: 27898
复制

当impdp的 job中断之后，可以通过impdp attache=JOBName    ===>   continue_client的形式重新运行。impdp system/oracle job_name=SYSTEM.SYS_IMPORT_FULL_01 


我们看到，继续运行导入
Import> continue_client <<<<<<<<<<<<continue_client
Job SYS_IMPORT_FULL_01 has been reopened at Wed Jul 4
Restarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=expdat.dmp directory=dmp
. . imported "PETER"."TEST1" 76.91 MB 581776 rows
. . imported "PETER"."TEST" 9.616 MB 72641 rows
. . imported "PETER"."M_ASH" 35.54 MB 84313 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed
复制

connect  as sysdba
create or replace directory tmp as '/tmp';
create tablespace test_tbs datafile '/tmp/test_tbs_01.dbf' size 100m autoextend on;
create user test_usr identified by test_usr default tablespace test_tbs temporary tablespace temp;
grant connect, resource to test_usr;
alter user test_usr quota unlimited on test_usr;


connect test_usr/test_usr
-- create one partitioned table with 5 partitions
create table parttab001
(
   col001 number,
   col002 varchar2(1000)
) partition by range (col001)
(
   partition p001 values less than (1000001),
   partition p002 values less than (2000001),
   partition p003 values less than (3000001),
   partition p004 values less than (4000001),
   partition p005 values less than (5000001)
);


-- populate table, 1000000 rows per partition
declare
  stmt varchar2(2000);
begin
  for j in 1..3000000 loop
    stmt := 'insert into parttab001 values ('||to_char (j)||', lpad (to_char ('||to_char (j)||'), 1000, '||'''0'''||'))';
    execute immediate stmt;
    -- commit after every 100000 rows
    if mod (j, 100000) = 0 then
      commit;
    end if;
  end loop;
  commit;
end;
/
复制

#> expdp system/<password> directory=tmp dumpfile=parttab001_%u.dmp logfile=expdp_parttab001.log 
tables=test_usr.parttab001 job_name=test_usr parallel=2


connect  as sysdba
col owner_name for a20
col job_name for a20
col state for a20
set linesize 1000
col operation for a20
col job_mode for a20
select * from dba_datapump_jobs where job_name = 'TEST_USR';
查询执行状态为：EXECUTING


ctrl-c中断导出进入以下窗口：Export> stop_job
查询执行状态为：STOP PENDING，


立即中止导出：Export> STOP_JOB=IMMEDIATE
查询执行状态为：NOT RUNNING


继续中断导出：#> expdp system/<password> attach=test_usr
Export> continue_client
复制

impdp system/<password> job_name=test_usr directory=tmp dumpfile=parttab001_%u.dmp tables=test_usr.parttab001 logfile=impdp_parttab001.log parallel=2


ctrl-c中断导入进入以下窗口：Import> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): y


继续中断导入：#> impdp system/<password> attach=test_usr
Import> continue_client
SQL> connect test_usr/test_user
SQL> select count (*) from parttab001 partition (p001);
COUNT(*)
----------
   1000000


SQL> select count (*) from parttab001 partition (p002);
COUNT(*)
----------
   1000000


SQL> select count (*) from parttab001 partition (p003);
COUNT(*)
----------
   1000000
复制

参数LOGTIME=ALL，控制台和日志记录都有时间戳
]$ expdp test/test DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log SCHEMAS=test LOGTIME=ALL
复制