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

From: Neil Kodner <nkodner_at_gmail.com>
Date: Tue, 17 Nov 2009 17:27:38 -0700
Message-ID: <ae2c46ba0911171627n3bae40c6r3fc43e8a9890fd07_at_mail.gmail.com>



I re-analyzed both tables using

method_opt for all columns skew only size auto estimate_percent 100

and received the same plan with the hash group by but different cardinality results. I'm not sure how much better my stats could be at this point.

I'd like to try the gather_plan_statistics hint mentioned on Greg's blog but I dont think I can wait around for the query to finish, assuming it ever will.

Granted this SQL isn't the best and could probably be optimized(see my last post) but I'm determined to understand the difference in performance!

On Tue, Nov 17, 2009 at 4: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).
>
> 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.
>
> The second query I included wasn't the best example. At the time I thought
> it pertained to all updates with correlated queries. I now believe that it
> only pertains to updates with correlated queries that have a subquery
> containing a group by clause. What a mouthful!
>
>
>
> On Tue, Nov 17, 2009 at 4:00 PM, Greg Rahn <greg_at_structureddata.org>wrote:
>
>> A 100% sample will generally be OK for single table cardinality, but
>> it may not mean the join cardinality is accurate.
>>
>> Look at the plan line #2 in the first example.
>> The HASH JOIN version has a cardinality of 8775, the HASH JOIN RIGHT
>> SEMI version has a cardinality of 19. That's a difference of 462x.
>> Pretty big!
>> How many rows are really returned?
>>
>> In the second example the access order and path is identical, the only
>> difference being lines 6 & 9 where one is SORT UNIQUE and the other is
>> HASH UNIQUE. I'm wondering how much difference there really is
>> between these two. I would think very little, but what is it?
>>
>> On Tue, Nov 17, 2009 at 2:28 PM, Neil Kodner <nkodner_at_gmail.com> wrote:
>> > I'm using
>> > estimate_percent=>100 and method_opt=>'FOR ALL COLUMNS SIZE 1' for these
>> > tables
>>
>> --
>> Regards,
>> Greg Rahn
>> http://structureddata.org
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 17 2009 - 18:27:38 CST

Original text of this message