|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
If
you
are experiencing long parse times for your query and if your query has
numerous outer joins or self-outer-joins then the reason might be
this:
Having numerous outer
joins or self-outer-joins
can cause the oracle
optimizer to go through numerous permutations in coming up with the
join orders. The total number of permutations that oracle tries to
come up with is controlled by the parameter optimizer_max_permutations.
The possible fix for this will be to issue the following statement
prior to running your query :
alter session set
optimizer_max_permutations=300;
If the query is within a
package or procedure then you'll have to set it prior to the query
execution within the package body or the procedure using the execute
immediate command as follows :
execute immediate 'alter
session set optimizer_max_permutations=300'; |
