expdp备份缓慢问题分析(_2)

最新推荐文章于 2025-02-07 17:30:00 发布

原创最新推荐文章于 2025-02-07 17:30:00 发布 · 929 阅读

0 ·

CC 4.0 BY-SA版权

Oracle Troubleshooting 专栏收录该内容

37 篇文章

订阅专栏

本文通过对数据库导出过程的详细跟踪与分析，发现导出速度受其他并发操作的影响较大，特别是内部表访问导致导出效率降低。通过使用Oracle提供的工具进行深入调查，总结了解决导出慢的问题的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--10046 分析发现等待时间，不过很小，况且是全表数据导出，这个等待应该没影响
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       19      0.08       0.12          0          4          0           0
Execute     19      1.81       1.82          0          2          0           2
Fetch    60073      0.75       3.76          0      76133          0      599867
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total    60111      2.64       5.70          0      76139          0      599869

Misses in library cache during parse: 17
Misses in library cache during execute: 8

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait Total Waited
----------------------------------------   Waited ---------- ------------
SQL*Net message from client                 60128      146.06        183.24
SQL*Net message to client                   60127        0.00          0.05
library cache lock                             35        0.00          0.01
library cache pin                              35        0.00          0.01
DFS lock handle                                17        0.00          0.00
gc current grant busy                           1        0.00          0.00
Disk file operations I/O                       15        0.00          0.00
gc current block 2-way                        110        0.00          0.05

--没有问题的导出，其它库
--10046 exp-------------------------
SELECT /*+NESTED_TABLE_GET_REFS+*/ "TA4"."TCUSTINFO".*
FROM
"TA4"."TCUSTINFO"

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    59978      0.68       3.70          0      75417          0      599780
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total    59980      0.69       3.70          0      75417          0      599780

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 98

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait Total Waited
----------------------------------------   Waited ---------- ---------- SQL*Net message to client                   59978        0.00          0.05
SQL*Net message from client                 59978        0.00         14.11
gc current grant busy                           1        0.00          0.00
Disk file operations I/O                       15        0.00          0.00
gc current block 2-way                        110        0.00          0.05

--benji daoru
正常跟踪文件
getrusage(RUSAGE_SELF, {ru_utime={0, 905862}, ru_stime={0, 577912}, ...}) = 0 <0.000008>
times(NULL) = 1472946855 <0.000007>
getrusage(RUSAGE_SELF, {ru_utime={0, 905862}, ru_stime={0, 577912}, ...}) = 0 <0.000007>
getrusage(RUSAGE_SELF, {ru_utime={0, 905862}, ru_stime={0, 577912}, ...}) = 0 <0.000007>

write(11, "\0\26\0\0\6\0\0\0\0\0\10\5\0\0\0\t\1\0\0\0\366\3", 22) = 22 <0.000009>
read(8, "\0\264\0\0\6\0\0\0\0\0\3\0030\5\0\0\0\0\0\0\0`\233H\0\0\0\0\0\212\0\0"..., 2064) = 180 <0.001102>
getrusage(RUSAGE_SELF, {ru_utime={0, 905862}, ru_stime={0, 577912}, ...}) = 0 <0.000008>

--yuancheng daoru
times(NULL) = 1472974763 <0.000008>
write(14, "\0W\0\0\6\0\0\0\0\0\10\2\0\262\22\251\1\0\0\0\0\4\1\0\0\0\25\2\0\0\0\0"..., 87) = 87 <0.000020>
read(14, "\1\33\0\0\6\0\0\0\0\0\3GAP\200\0\0\10\0\0\0\0\0\0\0\0\0\0\0\0\0\1"..., 2064) = 283 <0.008278>
getrusage(RUSAGE_SELF, {ru_utime={1, 358793}, ru_stime={0, 574912}, ...}) = 0 <0.000053>

getrusage(RUSAGE_SELF, {ru_utime={1, 358793}, ru_stime={0, 574912}, ...}) = 0 <0.000051>
getrusage(RUSAGE_SELF, {ru_utime={1, 358793}, ru_stime={0, 574912}, ...}) = 0 <0.000055>
getrusage(RUSAGE_SELF, {ru_utime={1, 358793}, ru_stime={0, 574912}, ...}) = 0 <0.000052>

通过导出测试的ash发现，如果没有其他top会话，导出很快，如果有其他top sql 或者内部表访问，导出将会很慢！
如果排除网络因素，数据库导出有时候块有时候慢，一个原因可能是数据库对内部表的访问（应该和监控有关）
--今天也排除了

ASH发现，导出慢的时候有几个内部表访问，比对awr报告语句执行应该有问题
SQL ID PlanHash Sampled # of Executions % Activity Row Source % RwSrc Top Event % Event SQL Text
g4kubvga4gnxc
1708793667 1 81.44 TABLE ACCESS - FULL 81.44 SQL*Net more data to client 81.44 SELECT /*+NESTED_TABLE_GET_REF...

1595974771 2 1.70 FIXED TABLE - FULL 1.70 CPU + Wait for CPU 1.70 select summary.name as name, ...
6dfrrws2xrc1v
3098130600 1 1.52 FIXED TABLE - FULL 1.33 CPU + Wait for CPU 1.33 INSERT INTO QUEST_ADV_SEGMENT_...
生产环境，不方便测试，也就到此结束了
该问题始终没搞清楚为什么导出时间有变化！
不过在这个过程中熟练应用了一些脚本

@?/rdbms/amdin/awrrpt
@?/rdbms/admin/awrsqrpt
@?/rdbms/admin/ashrpt
strace -Ttt -p id -o

还有就是生产环境大量的比对测试！希望以后看到有人可以给这个问题写说法