Re: PARTITION HASH INLIST -- QUERY

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Thu, 24 Dec 2009 12:44:07 -0800 (PST)
Message-ID: <c18c2018-d228-40f4-a7ba-9e332387f390_at_15g2000prz.googlegroups.com>



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

I just happen to see a way at metalink to disable transitive closures by setting event 10195 level 1(I tried level 3, 10 also - though they were not documented anywhere).
Still I got the same plan (bad one) without USE_CONCAT HINT.

I have set the event at session level by : ALTER SESSION SET EVENTS '10195 trace name context forever, level 1';

Can we have any other way to stop optimizer make unwanred transitive closures? Received on Thu Dec 24 2009 - 14:44:07 CST

Original text of this message