注: 本文为安丫科技焱焱枫的原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
某客户在使用expdp（Data Pump Export）工具导出某分区表时，耗时远超预期（原预计20分钟，实际耗时超过1小时）。
01
问题描述
客户反馈
某客户在使用expdp（Data Pump Export）工具导出某分区表时，耗时远超预期（原预计20分钟，实际耗时超过1小时）。
执行的导出日志如下：
starting "SYS"."SYS_EXPORT_TABLE_01":'******** AS SYSDBA" PART_TAB001.10g tabTeS=TEST.PART_TAB001Estimate in progress using BLocks method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATA...等待中....
02
故障分析过程
注意！以下涉及的表名均已脱敏。
1. 环境信息
数据库版本：Oracle 11g
表信息：
表名：TEST.PART_TAB001
分区类型：范围分区（Range Partition）
数据量：约800GB（共200个分区）
硬件配置：存储为SAN，CPU 64核，内存512GB
2. 初步排查
1）导出状态分析：
检查expdp导出状态：
expdp \'\/ as sysdba\' attach=SYS_EXPORT_TABLE_01
结果显示任务均在正常执行中。
2）会话监控
当前等待事件显示主要的等待在CPU上
通过v$session和v$sql 进一步跟踪长事务SQL，发现一条查询导出表的SQL
SELECT NVL((SELECT *+ NESTED_TABLE_GET_REFS */ 1FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUAL
3）OS监控：
查看操作系统性能指标
观察到CPU的空闲率所有下降。
3. 根本原因定位
1）SQL执行计划分析
通过DBMS_XPLAN.DISPLAY_CURSOR获取SQL执行计划：
******************************************************************************SELECT NVL((SELECT *+ NESTED_TABLE_GET_REFS */ 1FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUALcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 5.68 70.26 42616 42673 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 5.68 70.26 42616 42673 0 1Rows Row Source Operation------- ---------------------------------------------------1 COUNT STOPKEY (cr=42673 pr=42616 pw=0 time=70266787 us)1 INDEX FULL SCAN PART_TAB001_UK (cr=42673 pr=42616 pw=0 time=70266777 us)(object id 80566)1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)********************************************************************************
问题点：
索引全扫描：执行计划显示对分区表进行了索引全扫描（INDEX FULL SCAN），返回一条数据。
2）故障分析：
在执行分区表导出的任务中，Oracle内部会递归调用查询SQL。这本次案例中，对导出的分区表"TEST"."PART_TAB001"所有分区分进行查询。
由于该SQL走的索引全扫描，为单块读扫描方式，导致效率低下，需要70秒才能完成，那么对于所有200个子分区，需要200 x 70秒，这意味着近4小时才能完成导出。
03
解决方案
现已确认由于EXPDP调用的递归SQL导致的问题,优化SQL执行效率即可。
官方建议最好的选择是升级到数据库的支持版本，并应用最新的可用补丁集，当然也可以通过调整参数或收集统计信息处理。
1. 优化器参数
当前优化器相关参数：
optimizer_index_caching = 80optimizer_index_cost_adj = 20
这些参数迫使优化器选择差的执行计划。
将以下参数设置默认值：
SQL> connect as sysdbaSQL> alter system set optimizer_index_caching = 0;SQL> alter system set optimizer_index_cost_adj = 100;
2. 更新统计信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',tabname => 'PART_TAB001',estimate_percent => 30,method_opt => 'for all columns size repeat',degree => 8,granularity => 'ALL',cascade => TRUE);
04
验证结果
1. 执行计划验证：
优化后执行计划显示TABLE ACCESS FULL。
全表扫描（TABLE ACCESS FULL）取代索引全扫描。
********************************************************************************SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ 1FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUALcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.00 0.00 6 4 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.00 0.00 6 4 0 1Rows Row Source Operation------- ---------------------------------------------------1 COUNT STOPKEY (cr=4 pr=6 pw=0 time=215 us)1 PARTITION COMBINED ITERATOR PARTITION: KEY KEY (cr=4 pr=6 pw=0 time=209 us)1 TABLE ACCESS FULL PART_TAB001 PARTITION: 279 279 (cr=4 pr=6 pw=0 time=203 us)1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)********************************************************************************
2.导出时间对比：
优化前：4小时
优化后：25分钟
写在最后
通过对问题的深入分析，明确了导致 expdp 导出分区表速度缓慢的主要原因是某个关键SQL语句的执行效率低下。
针对这一问题提出了一系列优化措施，包括但不限于优化SQL执行计划、调整 expdp 参数设置以及对数据库补丁进行必要的升级。
关键词回复(可见相应文章):
oracle、mysql、pg、postgresql、sql、性能优化、故障处理、数据迁移、备份恢复、版本升级、补丁管理、深度巡检、解决方案、架构设计......
有任何问题或疑问，欢迎加V进群探讨哦～
