dblink相关的等待事件和执行计划简介

群里的朋友提到一个问题,用dblink插入数据比较慢,给出了10046 trace后的tkprof输出信息如下图:(2个实例,实例1上执行insert into table1 select * from table1@link_实例2)

<a href="http://www.dbaxiaoyu.com/wp-content/uploads/2014/02/1.png"><img src="http://www.dbaxiaoyu.com/wp-content/uploads/2014/02/1.png" alt="" title="1" width="744" height="500" class="aligncenter size-full wp-image-1790" /></a>

这里我们看出经过tkprof输出后的信息,一致性读query有177710 block,而current(db block gets)当前读则有9062474 block,而logical reads=consistent gets+db block gets,也就是一致性读加上当前模式读,这里接近有10M的block需要读取,这里的IO成本是很高的。
再来看看下面的elapsed times include waiting on following events信息,其中的主要的消耗SQL*Net more data from dblink,而这个等待事件主要是关于服务端接受远程db link传输的数据消耗的时间,还有几个关于db link的等待事件SQL*Net more data to dblink、SQL*Net message from dblink和SQL*Net message to dblink。

如下是关于oracle 11GR2官档关于上述db link几个主要等待事件的描述:
SQL*Net more data to dblink
The event indicates that the server is sending data over a database link again. The previous operation over this database link was also a send.
Wait Time: The actual time it takes to send the data to the other server
Parameter   Description
driver id   See "driver id"

#bytes  The number of bytes that are sent over the database link to the other server process

SQL*Net more data to dblink表示的是服务端发送数据到db link产生的等待

SQL*Net more data from dblink
The foreground process is expecting more data from a data base link.
Wait Time: The total time it takes to read the data from the database link (including the waiting time for the data to arrive)
Parameter   Description
driver id   See "driver id"

#bytes  The number of bytes received
SQL*Net more data from dblink表示的是后台进程接收远程db link传输的数据的等待。

SQL*Net message from dblink
The session waits while the server process (foreground process) receives messages over a database link from another server process.
Wait Time: The time it took for a message to arrive from another server (foreground process) since a message was sent to the other foreground process.
Parameter   Description
driver id   See "driver id"

#bytes  The number of bytes received by the server (foreground process) from another foreground process over a database link.
SQL*Net message from dblink表示的是服务器端后台进程接受远程dblink发送而来的消息产生的等待。

SQL*Net message to dblink
The server process (foreground process) is sending a message over a database link to another server process.
Wait Time: The actual time the send takes
Parameter   Description
driver id   See "driver id"

#bytes  The number of bytes sent by the server process to another server process over a database link

SQL*Net message to dblink则表示的是服务器后台进程发送消息到远程dblink产生的等待

关于cbo解析含有dblink的sql生成执行计划时,oracle通常会有两种选择,一种是在本地执行sql语句,此时会先将远程所需要的数据发送到本地端,然后再行执行,另一种是在目标端执行sql语句,此时会先将本地所需要的数据发送到目标端,然后再在目标端执行sql语句,而具体采取何种方式,是由cbo根据消耗选择最低的执行计划,当然我们也可以用driving_site hint来强制指定sql具体在本地还是远程端执行。
下面我们来看看有关dblink的执行计划:
SQL>  explain plan for select * from tab01@ora10g a,tab001 b where a.object_id=b.object_id;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264860609

--------------------------------------------------------------------------------
---------------------

| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
    | Inst   |IN-OUT|

--------------------------------------------------------------------------------
---------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    10G|   518G|       | 50648  (99)| 00:10
:08 |        |      |

|*  1 |  HASH JOIN         |        |    10G|   518G|  2552K| 50648  (99)| 00:10
:08 |        |      |

|   2 |   TABLE ACCESS FULL| TAB001 | 72471 |  1698K|       |   103   (1)| 00:00
:02 |        |      |

|   3 |   REMOTE           | TAB01  |   144K|  4104K|       |   169   (1)| 00:00
:03 | ORA10G | R->S |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
---------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Remote SQL Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------

   3 - SELECT "OBJECT_ID","OBJECT_NAME" FROM "TAB01" "A" (accessing 'ORA10G' )

21 rows selected.

看出这里有个remote的执行计划,对应的table是tab01,而且还有一个ORA10G | R->S这个,其实这个表示的是tab01是在远dblink ora10g的上面读取的,然后将数据发送到了本地的客户端,所以最后是SELECT STATEMENT,而不是SELECT STATEMENT REMOTE。

SQL> explain plan for select /*+driving_site(a)*/* from tab01@ora10g a,tab001 b where a.object_id=b.object_id;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2377594790

--------------------------------------------------------------------------------
-------------------------

| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| T
ime     | Inst   |IN-OUT|

--------------------------------------------------------------------------------
-------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|        |    10G|   518G|       | 87925 (100)| 0
0:17:36 |        |      |

|*  1 |  HASH JOIN             |        |    10G|   518G|  2904K| 87925 (100)| 0
0:17:36 |        |      |

|   2 |   REMOTE               | TAB001 | 72471 |  2052K|       |    78   (2)| 0
0:00:01 |      ! | R->S |

|   3 |   TABLE ACCESS FULL    | TAB01  |   144K|  3397K|       |   170   (2)| 0
0:00:03 | RDUTF8 |      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")

Remote SQL Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------

   2 - SELECT "OBJECT_ID","OBJECT_NAME" FROM "TAB001" "A1" (accessing '!' )


Note
-----
   - fully remote statement

25 rows selected.

而我们看加了hint的driving_site(a)的sql执行计划,强制让sql的执行地方为a表所对应的数据库端也就是远程的dblink端。

我们发觉其中执行路径REMOTE对应的表明为TAB001,这里是表示将本地的table001发送到了远程的dblink端,然后直接和远程的tab01表走hash join,最后将查询的结果返回到本地端,所以执行计划的最后一步是SELECT STATEMENT REMOTE。

而关于dblink的优化方式也不是很多,无非就是优化sql,然后调整网络,当然cbo可能有时候会选择不合理的目标端执行sql,这个可以收集统计信息,或者用hint强制指定,而依据的原则还是减少网络发送数据量。

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in oracle. Bookmark the permalink.