Re: PARTITION HASH INLIST -- QUERY

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Thu, 24 Dec 2009 11:38:43 -0800 (PST)
Message-ID: <3cdb6a8a-8798-4f2b-ab78-b0719b5f92c7_at_z3g2000prd.googlegroups.com>

On Dec 24, 5:01 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in message
>
> news:X4WdnWZNdY-Byq7WnZ2dnUVZ8ludnZ2d_at_bt.com...
>
>
>
>
>
>
>
> > "Ind-dba" <oraclear..._at_googlemail.com> wrote in message
> >news:6db294bb-0f4b-446c-9c75-8e2ddb86b957_at_x25g2000prf.googlegroups.com...
> > On Dec 24, 12:41 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> > wrote:
> >> "Ind-dba" <oraclear..._at_googlemail.com> wrote in message
>
> >>news:378b724d-ca44-46f2-b3b8-37ea58c33945_at_x5g2000prf.googlegroups.com...
>
> >> >I need your help in a SQL tuning exercise:
>
> >> > SQL:
> >> > =======
> >> > select /*+ gather_plan_statistics index(ag,PK_AD_Y) */ count
> >> > (t.EDIT_STATUS)
> >> > , count(ag.cr_date)
> >> > from CMPGN.TE_Y t
> >> > join CMPGN.AD_Y ag on ag.ad_grp_id=t.ad_grp_id
> >> > and ag.ad_grp_id in
> >> > (
> >> > ..,
> >> > ..,
> >> > 997 values
> >> > )
> >> > order by t.acct_id, ag.cmpgn_id, t.ad_grp_id, t.term_id;
>
> >> What version of Oracle
>
> >> Can we see the execution plans without the 'allstats last' option so
> >> that
> >> we can see the predicted cost, and the pstart/pstop information.
>
> >> For each table - how many partitions, and how which column is
> >> it partitioned on.
>
> >> --
> >> Regards
>
> >> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> > What version of Oracle
> > 10.2.0.4 on Linux
>
> > Can we see the execution plans without the 'allstats last' option so
> > that
> > we can see the predicted cost, and the pstart/pstop information.
>
> > Sure: I have posted the execution plans _at_http://pastebin.com/d28ee5395
>
> > For each table - how many partitions, and how which column is
> > it partitioned on.
>
> > TE_Y: no of partitions: 2048  PARTITION BY HASH (ad_grp_id)
> > AD_Y: no of partitions: 2048 PARTITION BY HASH (ad_grp_id)
>
> The clue, by the way is in the KEY-KEY pstart/pstop
> compared to the KEY(I)/KEY(I), which confirms the
> oddity with the change between partition hash iterator
> and partition hash inlist
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com

Thanks a bunch Jonathan for the deep-dive analysis!

Could you please shed some more light (or probably redirect) on term: "Transitive closure"

Another point that you mentioned that "The excessive selection of partitions looks
like the bug which use_concat has accidentally blocked." -- Have you come across such bug? Or you recommend me filing a new one with Oracle Support.

Another point .. how do we confirm that the relevant data in TE_Y table for this SQL is in 791 partitions. Is there a SQL that can help me do so.

Thanks much for your inputs so far .. appreciate your help.

Regards,
Sachin Received on Thu Dec 24 2009 - 13:38:43 CST