對使用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/，如需轉載，請註明出處，否則將追究法律責任。