对使用dblink的10046事件跟踪

如果网络带宽不够大的情况下，使用dblink导数据就会非常的慢，我们使用10046事件来跟踪一下两边数据库的等待事件，来推测下dblink慢的原因.

在源短执行建表语句
create table xx_test as (select * from yy_test@to_remote_db);

远端表yy_test@to_remote_db的大小约为9G，上面的语句执行一段时间后将它中止，我们来看看两边的trace文件.

源端的trace数据:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 2 0.54 40.44 1512 1498 0 52708
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.54 40.44 1512 1498 0 52708

Misses in library cache during parse: 0
Parsing user id: 71

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data to client 4787 3.54 63.86
db file scattered read 19 0.07 0.86
SQL*Net message from client 2 0.02 0.03
SQL*Net message to client 3 0.00 0.00
SQL*Net break/reset to client 1 0.00 0.00

目标端的trace数据:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from dblink 12633 1.52 177.21
log buffer space 37 0.10 0.73
SQL*Net message to dblink 6 0.00 0.00
SQL*Net message from dblink 6 0.00 0.02
SQL*Net break/reset to dblink 3 1.11 1.11
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 179 0.01 0.02 0 0 45 0
Execute 245 0.16 0.16 0 452 120 112
Fetch 3179 0.07 0.10 20 6393 0 3335
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3603 0.24 0.29 20 6845 165 3447

Misses in library cache during parse: 14
Misses in library cache during execute: 14

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 20 0.01 0.07
log buffer space 1 0.00 0.00

我们看到，大部分的等待事件都是SQL*NET的等待，这说明SQL执行的时间大部分都消耗在了网络上，而不是在数据库上.
而我们观察原始的trace文件，可以看到源端回间歇性的出现"db file scattered read"的等待;目标端则间歇性的出现"log buffer space"等待事件.说明dblink在取数据都是一部

分一部分的取然后插入.

我们对比exp的10046事件信息:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 48107 0.00 0.02
SQL*Net more data to client 48089 0.00 10.41
SQL*Net message from client 48107 3.64 27.49
direct path read 23695 0.19 3.02
db file sequential read 2 0.00 0.00
SQL*Net break/reset to client 1 0.00 0.00

好像exp的方式，相对来说，空闲等待相对比较少一些.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23850820/viewspace-1042008/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-1042008/