Donald K. Burleson Oracle Tips

Secrets of cross-database SQL joins

When doing cross-database joins, Oracle will always assume that the schema has been invalidated since the last execution of the SQL statement, and re-parse the SQL statement every time that the statement is executed. 

This will cause huge overhead whenever your database is doing a high-volume of distributed database joins.

For example, the following query will be re-parsed every time that it is executed:

Select

   Customer_name, order_number

From

   Customer,

   order@new_york

Where

   Customer_number = :hostvar;

Oracle re-parses the above query because the leftover SQL is marked as invalid in the SQL cache as soon as the query has completed.  According to Oracle, this is a safety mechanism to ensure that the schema has not changed since the last execution.  Hence, the only solution to this problem is the use of remote procedure calls to replace the SQL statement.  In cases where a distributed join is unavoidable, frequent flushing of the shared pool may be appropriate.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm