Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade

On Tue, Nov 17, 2009 at 3:59 PM, Neil Kodner <nkodner_at_gmail.com> wrote:
> Do you really think this is a problem with the statistics itself? It ran
> well in 9 using the same statistics gathering methods. I've tried computing
> histograms on pm_intake.pcn (its the PK of wfs_reg_intake_workers).

At this point probably not, but it certainly is the first and foremost step when troubleshooting execution plans. And often times the response is "the stats are recent" but (recent != representative) always.

The "it ran fine in <previous version>" statement is common, but sometimes it falls under the case of dumb luck. More and more optimizations around execution plans are added so it becomes more and more dependent on representative (accurate) statistics.

In both queries the access and order is nearly identical. The first example when OFE=10.2.0.4, it chooses HASH GROUP BY over SORT GROUP BY (this is the hash group-by optimization) new in 10g (10.2 I believe) and SORT UNIQUE + HASH JOIN in OFE=9.2 changes to HASH JOIN RIGHT SEMI. My guess (and I hate to be a guesser) is that it is the HASH JOIN RIGHT SEMI that is the slow part in that one.

One easy test is just to break the query into parts and check the cardinality and execution times. There is only 1 unique join in the entire query. That being this:

select count(*)

from     wfs_reg_intake_workers,
         pm_intake x
where    pcn = x.intake_pcn
and      dro = x.intake_dro

I would suggest seeing what the cardinality estimates are for that guy compared to what he actually returns. Perhaps there is some correlation or something. The rest of the plan is the same in case #1 (COUNT STOPKEY rowsource and higher in id#).

> I am taking a closer look at the SQL and think it's quite horrible. For
> starters, I can't even see why the group by is even in the query. Removing
> the group by clause removes the hash group by and then I'm back to my
> regular execution times.

I assume you mean the "select max (total_cases)" correlated subquery.  This is very bizarre. Given it uses a "group by team,caseload" it will return the max(total_cases) value distinct{team,caseload} times. The group by should be removed and it changed to an equality (=) from an IN.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l