RE: Updates with correlated subqueries slow after 9.2->10.2 upgrade
Date: Wed, 18 Nov 2009 09:16:02 +0100
Message-Id: <934105293_at_web.de>
> Altering the session and setting optimizer_index_cost_adj=100 and
> optimizer_index_caching=0 didn't make a difference.
> A common denominator is that the session-altered plans have a SORT UNIQUE
> step and the default 10g plans do not.
Neil,
one thing that hasn't been mentioned yet I think is the potential effect of the SORT UNIQUE on the FILTER optimization built into the Oracle runtime engine: Since your statement obviously uses FILTER operations that are potentially executed for each row produced by the parent row source, the performance of the statement might heavily depend on how often the filter subquery actually has to be executed.
Oracle attempts to cache the results of filter subqueries in a small in-memory table. The logic used tries to play clever, e.g. if the next input value to the subquery returned by the parent row source is the same as the previous one then Oracle even doesn't perform a lookup to the in-memory table but simply re-uses the output from the previous row.
So obviously if the parent row source is:
- sorted
- and has many repeating input values
then is algorithm is highly efficient, since has to execute the subquery only once per distinct input value in the parent row source
If the EXPLAIN PLAN output is correct and the second FILTER subquery is actually nested into the first FILTER subquery (there are bugs in the EXPLAIN PLAN output sometimes when dealing with multiple FILTER subqueries) then this might exaggerate the issue - an unsorted parent row source might result in a vast number of actual subquery executions.
So the new 10g option of transforming this into a right hash join instead of a regular join requiring a unique sort might have made things worse here. As usual, not all new optimizations will actually show improvements in every circumstance, although in general they might provide benefit (and the option to have a right hash join is definitely a good thing in general).
You can try to do the following to find out if this FILTER subquery caching is the issue here:
Run the good performing statement with STATISTICS_LEVEL set to ALL, and check the actual number of operation executions using DBMS_XPLAN.DISPLAY_CURSOR with the format option "ALLSTATS LAST"
This will show you two important things:
- The number of rows generated by the parent row source (the HASH JOIN) (If this number is small it's unlikely that the subquery caching could help a lot, since it drives the potential number of executions)
- The number of STARTS of the FILTER subqueries - are these way smaller than the number of rows produced by the parent row source, then subquery caching has helped to reduce the number of subquery executions
You could also try to run the bad performing query with STATISTICS_LEVEL = ALL and if it doesn't come back simply kill it - usually the (incomplete) statistics will still be available with ALLSTATS LAST from the shared pool (obviously you would need to identify the SQL_ID and CHILD_NUMBER to run DISPLAY_CURSOR from a different session then).
These incomplete statistics might be sufficient to give you the information that the subqueries were executed many more times than with the good performing statement.
By the way, as mentioned by others, if you have a chance to re-write the statement, it might perform much better (and even better than the original three minutes) - a correlated IN query will always be turned into a FILTER subquery I think and never be transformed into something potentially more efficient like a join.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter http://movieflat.web.de
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 18 2009 - 02:16:02 CST