database link 导致二次解析

具体可以参照：Query Through DBLINK Generates Parse-Only "SELECT * FROM" Statements (Doc ID 453194.1)

 

通过database link查询的时候，oracle会首先会全parse一下需要的表，但不会执行，然后才会对具体的语句再parse一次，然后执行。

多分析一次的代价很高，当大量会话通过database link查的时候，可能会出现latch: row cache object等待。

开始还以为是oracle的bug。

 

所以，应用设计的时候，不建议大量的使用database link。

 

Sample:

Statement executed on Source site:

SELECT COL1 FROM MYTAB@dblink

WHERE 1=1 AND COL2 LIKE 'VALUE%'

On a SQL Trace on target site we can see that previous statement generates 2 cursors

SELECT * FROM "MYTAB"  -- Note this one is only parsed

SELECT "A1"."COL1" FROM "MYTAB" "A1" WHERE 1=1 AND "A1"."COL2" LIKE
'VALUE%'

 

This is the expected behaviour when working with database link.

The second query parse/executed/fetched shown above corresponds to the real execution requested by the source site

Regarding the first cursor this have to do with the way that the source site can manage the object definition.

When no dblink is involved and a database object is referenced in the database it is necessary to load that object definition in the shared pool, to do this the dictionary is checked. Meanwhile that object definition is in memory, it is not necessary to retrieve further information from dictionary, because in the case the object is modified the object is directly modified in memory.

But when working with dblinks, the db that executes the statement (source) does not have in its data dictionary that object definition, so that information have to be retrieved from the owning db (target) and that is externalized by using the "select * from table_name" cursor. Also note that this have to be done every time the table is referenced because source site is not aware to know if the object definition has changed or not, so it is needed to retrieve that information again to keep the memory
information up to date.