469,921 Members | 2,176 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,921 developers. It's quick & easy.

SQL re-parsing on query executed against a remote database usingdatabase links

Can anyone help to understand why re-parsing occurs on a remote database
(using database links), even though we are using a prepared statement on
the local database:

Scenario:
========

We have an schema (s1) on an Oracle 9i database with database links
pointing to a schema (s2) on another Oracle 9i database.

We have a SQL statement which joins tables from schemas s1 to tables in
schema s2 by the use of database links (tablename@linkname). The query
is executed against schema s1.

The SQL statement is prepared and re-executed over many iterations
(using bind variables - Java/jdbc). The statement is closed only when we
have completed the iterations.

For both databases, the optimizer mode is CHOOSE and ALL the tables have
been analyzed.

Results:
=======

We have found that when we monitor / analyse the database, the parsing
rate is high, which is impacting the performance of the process.

NOTE: We also ran the same tests using Oracle 8i (for both databases)
instead of 9i. In these tests the parsing rate was very low (zero). This
is what I would have regarded as the expected behaviour.

The same high parsing rate is also seen when both schemas reside on the
same database, and database links are used to join tables from the 2
schemas. If however, the SQL query is amended to refer to the remote
tables in schema s2 by schema.tablename rather than tablename@linkname,
then the parsing rate is again very low (as expected) and the process
runs much more quickly.
Oct 14 '05 #1
0 3877

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Nel | last post: by
4 posts views Thread by Craig Bailey | last post: by
4 posts views Thread by Alan Walkington | last post: by
1 post views Thread by John Ryan | last post: by
10 posts views Thread by James | last post: by
2 posts views Thread by Topspin | last post: by
3 posts views Thread by presspley | last post: by
2 posts views Thread by sky2070 | last post: by
1 post views Thread by Brian | last post: by
2 posts views Thread by JW | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.