Parsing in remote databases

I was trying to decrease the number of parses for a 9.2.0.7 production database when I realized that the top parsing sql was coming from another database through a database link. This sql has a parse/execute ratio of 1 meaning it is parsed every time it is called. I have searched the bug database and found that there is a bug about parsing when you are joining a local table with a remote table, the bug number is 4913460. For those who are using db links extensively the following is a test case showing the parsing problem in remote databases.

In the remote database create a simple table:

SQL> create table remote_table
  2  as select level remote_a from dual connect by level <=1000;

Table created.

SQL> create index remote_ind1 on remote_table(remote_a);

Index created.

In the local database create a simple table:

SQL> create table local_table
 2  as select level local_a from dual connect by level <=5000;

Table created.

SQL> create index local_index on local_table(local_a);

Index created.

Than create a database link connecting these two databases (commands not printed here) and query the local table joining it to the remote table:

declare
v_a number;
begin
for i in 1..1000 loop
select local_a into v_a from local_table
where local_a in (select remote_a from remote_table@test.world where remote_a=i);
end loop;
end;
/

In the remote database check v$sqlarea for the part of this sql (you can first get the hash_value by querying v$sqlarea with: sql_text like '%REMOTE_TABLE%'):

SQL> COLUMN   sql_text FORMAT   a30
SQL> select hash_value,sql_text,executions,parse_calls from v$sqlarea where hash_value=1971931531;

HASH_VALUE SQL_TEXT                       EXECUTIONS PARSE_CALLS
---------- ------------------------------ ---------- -----------
1971931531 SELECT "REMOTE_A" FROM "REMOTE       1000        1000
           _TABLE" "REMOTE_TABLE" WHERE "
           REMOTE_A"=:1

The sql in the remote database is parsed for every execution. In the local database again, try with EXISTS:

declare
v_a number;
begin
for i in 1..1000 loop
select local_a into v_a from local_table
where local_a=i and exists (select null from remote_table@test.world where remote_a=local_table.local_a);
end loop;
end;
/

The remote database shows:

SQL> r
  1* select hash_value,sql_text,executions,parse_calls from v$sqlarea where hash_value=1971931531

HASH_VALUE SQL_TEXT                       EXECUTIONS PARSE_CALLS
---------- ------------------------------ ---------- -----------
1971931531 SELECT "REMOTE_A" FROM "REMOTE       2000        2000
           _TABLE" "REMOTE_TABLE" WHERE "
           REMOTE_A"=:1

This bug will be fixed in 11.1 but has a patch on 9.2.0.8.