Oracle数据库expdp导出分区表异常缓慢问题分析

安呀智数据坊

于 2025-02-07 17:30:00 发布

阅读量1.3k

点赞数 16

分类专栏： Oracle 文章标签： oracle 数据库 expdp 分区表 oracle 11g 缓慢

本文链接：https://blog.youkuaiyun.com/2403_87251975/article/details/145492541

版权

Oracle 专栏收录该内容

36 篇文章

订阅专栏

一、问题描述

客户反馈：

某客户在使用expdp（Data Pump Export）工具导出某分区表时，耗时远超预期（原预计20分钟，实际耗时超过1小时）。

执行的导出日志如下：

starting "SYS"."SYS_EXPORT_TABLE_01":'******** AS SYSDBA" PART_TAB001.10g tabTeS=TEST.PART_TAB001
Estimate in progress using BLocks method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
...等待中....

二、故障分析过程

注：以下涉及的表名均已脱敏

2.1 环境信息

数据库版本：Oracle 11g

表信息：

表名：TEST.PART_TAB001

分区类型：范围分区（Range Partition）

数据量：约800GB（共200个分区）

硬件配置：存储为SAN，CPU 64核，内存512GB

2.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 */ 1
FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUAL

3）OS监控：

查看操作系统性能指标

观察到CPU的空闲率所有下降。

2.3 根本原因定位

2.3.1 SQL执行计划分析

通过DBMS_XPLAN.DISPLAY_CURSOR获取SQL执行计划：

******************************************************************************
SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ 1
FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUAL

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.00       0.00          0          0          0          0
Fetch        1     5.68      70.26      42616      42673          0          1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3     5.68      70.26      42616      42673          0          1

Rows 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.3.2 故障分析

在执行分区表导出的任务中，Oracle内部会递归调用查询SQL。这本次案例中，对导出的分区表"TEST"."PART_TAB001"所有分区分进行查询。由于该SQL走的索引全扫描，为单块读扫描方式，导致效率低下，需要70秒才能完成，那么对于所有200个子分区，需要200 x 70秒，这意味着近4小时才能完成导出。

三、解决方案

现已确认由于EXPDP调用的递归SQL导致的问题,优化SQL执行效率即可。官方建议最好的选择是升级到数据库的支持版本，并应用最新的可用补丁集，当然也可以通过调整参数或收集统计信息处理。

3.1优化器参数

当前优化器相关参数：

optimizer_index_caching = 80
optimizer_index_cost_adj = 20

这些参数迫使优化器选择差的执行计划。

将以下参数设置默认值：

SQL> connect / as sysdba
SQL> alter system set optimizer_index_caching = 0;
SQL> alter system set optimizer_index_cost_adj = 100;

3.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
);

四、验证结果

4.1 执行计划验证：

优化后执行计划显示TABLE ACCESS FULL。

全表扫描（TABLE ACCESS FULL）取代索引全扫描。

********************************************************************************
SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ 1
FROM "TEST"."PART_TAB001" SUBPARTITION ("SYS_SUBP2107") WHERE ROWNUM = 1), 0) FROM DUAL

call     count cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.00       0.00          0          0          0          0
Fetch        1     0.00       0.00          6          4          0          1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3     0.00       0.00          6          4          0          1

Rows 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)
********************************************************************************