From: penttik@gmail.com   
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)