Re: PARTITION HASH INLIST -- QUERY

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Thu, 24 Dec 2009 12:22:26 -0800 (PST)
Message-ID: <84393595-547b-47e9-887a-72921b539acd_at_d4g2000pra.googlegroups.com>

On Dec 25, 12:38 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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

Hi Jonathan,

I just read your article on Transitive closure - so no need to explain that.

Yes - if you could shed more light on why this bug occur in first place. Could this be due to STATS on table?

Or due to constraints on the 2 tables in picture? Received on Thu Dec 24 2009 - 14:22:26 CST