作者 ：白玉山
东方龙马 · 上海
我们经常碰到过的生产环境迁移、构建测试环境等场景需要使用impdp导入数据，经常有人遇到impdp导入效率慢，导入挂起，导入索引部分长时间的等待等等问题，对于这些由于各种原因引起的性能问题现象，你是否深入进行分析过，思考过如何确保以后此类导入业务稳定高效？下面我来跟大家讲述一个东方龙马上海团队曾经碰到过的一个案例。
背 景
近日，东方龙马上海团队接到了一个客户的紧急报障电话，在使用impdp进行业务数据迁移时，导入动作迟迟未完成，已经超过了预定的停机时间窗口；我们的工程师立即前往支持，首先了解了客户环境为:
从windows+oracle10g单实例使用expdp导出，在linux+oracle11.2.0.4单实例上使用impdp导入;业务用户数据中有大量表只有很少数据或无数据，这些用户的表对应的索引有25000多个；目前impdp进度处于导入表后的：
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
对于有经验的DBA来说，看到这一步大多会依据经验初步判断是由于串行建索引或者导入并行度不够，所以比较慢；对于这们本次的问题场景，客户已经提供信息这些索引对应的表数据量非常小，通过在原环境的查看也确认了这一点；目前需要创建的索引约25000个，从dba_objects统计索引总数量的增量来计算，大约5秒左右创建一个，在此次的创建20000多个很小索引的场景，按目前速度索引创建过程需要耗费10几个小时了，大大超过预定停机窗口。
接下来我们按照正常的性能问题排查步骤在目标环境进行排查：
1.系统资源：CPU/MEMORY/IO，使用正常
2.查看DB中的活动会话等待事件，无明显异常
3.redo log切换正常，业务表空间、临时表空间等使用正常
4.增大了shared pool/streams_pool_size 后，无明显改善
5.查看目标环境数据库AWR报告，也未发现明显异常
这里首选使用10046 TRACE方式来查看导入进程的具体操作与对应时间。
1.找到IMPDP的进程OSPID:
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
2 s.status, s.username, d.job_name, p.spid, s.serial#
3 from v$session s, v$process p, dba_datapump_sessions d
4 where p.addr=s.paddr and s.saddr=d.saddr;
DATE PROGRAM SID STATUS USERNAME JOB_NAME SPID SERIAL#
------------------- -------------------------------- ------- -------- ---------- ------------------------------ ------- -------
2018-01-11 11:13:48 oracle@test111 (DW02) 157 ACTIVE SYSTEM SYS_IMPORT_FULL_01 9919 1
2018-01-11 11:13:48 oracle@test111 (DW01) 133 ACTIVE SYSTEM SYS_IMPORT_FULL_01 9917 5
2018-01-11 11:13:48 oracle@test111 (DW03) 182 ACTIVE SYSTEM SYS_IMPORT_FULL_01 9921 1
2018-01-11 11:13:48 oracle@test111 (DW00) 28 ACTIVE AAA SYS_IMPORT_FULL_01 9750 3
2018-01-11 11:13:48 oracle@test111 (DM00) 3 ACTIVE SYSTEM SYS_IMPORT_FULL_01 9748 1
2018-01-11 11:13:48 udi@test111 (TNS V1-V3) 178 ACTIVE SYSTEM SYS_IMPORT_FULL_01 9744 5
6 rows selected.
2.使用oradebug 10046方法跟踪IMPDP进程：
SQL> oradebug setospid 9750
Oracle pid: 31, Unix process pid: 9750, image: oracle@test111 (DW00)
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL>
…………等一段时间
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/AAORA/AAORA/trace/AAORA_dw00_9750.trc
'
3.tkprof格式化并分析trace
$ tkprof u01/app/oracle/diag/rdbms/AAORA/AAORA/trace/AAORA_dw00_9750.trc
output = dm2.log
从格式化的trace最后查看，总的执行信息：
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3034
从10046的TRACE中分析可以发现有两个涉及基表的SQL执行效率不太好，这个时候首选方案是收集系统及数据字典基表统计信息，这里我使用如下过程将相关的统计信息一并收集。
GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_SCHEMA_STATS Procedures
收集后查看sys.kupc$que_int.receive(:2)，收集数据字典信息后恢复正常。但是另一条SQL b7qwhm1b31mv2的执行情况仍未改善，执行计划：
SQL> select SQL_ID,EXECUTIONS,CHILD_NUMBER,HASH_VALUE,ELAPSED_TIME/EXECUTIONS from v$sql where sql_id='b7qwhm1b31mv2';
SQL_ID EXECUTIONS CHILD_NUMBER HASH_VALUE ELAPSED_TIME/EXECUTIONS
------------- ---------- ------------ ---------- -----------------------
b7qwhm1b31mv2 716 0 1446039394 2452111.12
b7qwhm1b31mv2 487 1 1446039394 3340427.51
在收集系统及数据字典基表统计信息后有一个SQL执行计划效率仍较低，在此情况下也可以考虑使用人为加hint、本库查看更大范围的AWR数据中是否有此SQL正确 执行计划或者从其它同版本的数据库环境中查看此SQLID的执行计划等方式，得到一个效率高的执行计划，并在目标环境进行固定执行计划的方式来使SQL执行计划效率提升。
关于跨数据库绑定执行计划：
跨数据库的绑定、替换执行计划有多种方式，一般可以使用官方SQLT系列脚本中的coe*系列来简化操作，如不同数据库中的SQLID文本完全一致，在核对相关OPTIMIZER参数后也可以直接使用coe_xfr_sql_profile脚本在测试环境得到正确的绑定执行计划用的sql文件，传输到目标环境运行即可绑定；另外的coe_load_sql_profile脚本 coe _ load _ sql _ baseline 脚本均可以实现不同数据库中执行计划的绑定替换并能够支持做一定的SQL文本修改等操作。
本次我们即使用在同版本的测试库中查看同样SQL的执行计划效率较高，使用其执行计划在目标环境对异常SQL进行执行计划的固定。固定执行计划后此SQL的执行效率：
SQL>select SQL_ID,EXECUTIONS,CHILD_NUMBER,PLAN_HASH_VALUE,SQL_PROFILE,ELAPSED_TIME/EXECUTIONS/1000 "microseconds/1000" from v$sql where sql_id='b7qwhm1b31mv2';
SQL_ID EXECUTIONS CHILD_NUMBER PLAN_HASH_VALUE SQL_PROFILE microseconds/1000
------------- ---------- ------------ --------------- ------------------------------ -----------------
b7qwhm1b31mv2 4955 0 3049612612 coe_b7qwhm1b31mv2_3049612612 1.42824622
AWRSQL的报告查看：
通过前面的处理，查看索引创建速度已经有大幅提高：
① 处理前：37分钟，创建336个，平均一分钟9个
② 处理后：14分钟，创建3022个，平均一分钟215个
处理前：
SQL> select count(*),to_char(sysdate,'yyyymmdd hh24:mi:ss') from dba_objects where object_type='INDEX' and owner in ('AAA','bbb','ccc');
COUNT(*) TO_CHAR(SYSDATE,'
-------- -----------------
19218 20180111 11:35:44
SQL> select count(*),to_char(sysdate,'yyyymmdd hh24:mi:ss') from dba_objects where object_type='INDEX' and owner in ('AAA','bbb','ccc');
COUNT(*) TO_CHAR(SYSDATE,'
---------- -----------------
19554 20180111 12:12:38
处理后：
SQL> select count(*),to_char(sysdate,'yyyymmdd hh24:mi:ss') from dba_objects where object_type='INDEX' and owner in ('AAA','bbb','ccc');
COUNT(*) TO_CHAR(SYSDATE,'
---------- -----------------
20107 20180111 12:40:15
---
SQL>select count(*),to_char(sysdate,'yyyymmdd hh24:mi:ss') from dba_objects where object_type='INDEX' and owner in ('AAA','bbb','ccc');
COUNT(*) TO_CHAR(SYSDATE,'
---------- -----------------
23129 20180111 12:54:10：
| 北京 | 上海 | 广州 | 成都 |
4008-906-960
