Re: PARTITION HASH INLIST -- QUERY
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 23 Dec 2009 10:49:18 -0800 (PST)
Message-ID: <b02dce21-6a3c-4998-87b1-e4f017207e32_at_n13g2000vbe.googlegroups.com>
On Dec 23, 1:22 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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;
>
> PLAN:
> ======
> --------------------------------------------------------------------------- ---------------------------------------------------
> | Id | Operation | Name |
> Starts | E-Rows | A-Rows | A-Time | Buffers |
> Reads |
> --------------------------------------------------------------------------- ---------------------------------------------------
> | 1 | SORT AGGREGATE |
> | 1 | 1 | 1 |00:00:20.72 | 2406K|
> 36 |
> | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TE_Y | 1
> | 1 | 15988 |00:00:20.64 | 2406K|
> 36 |
> | 3 | NESTED LOOPS |
> | 1 | 790 | 16986 |00:02:46.81 | 2403K|
> 36 |
> | 4 | INLIST ITERATOR |
> | 1 | | 997 |00:00:00.22 | 2991 |
> 4 |
> | 5 | PARTITION HASH ITERATOR | |
> 997 | 784 | 997 |00:00:00.19 | 2991 |
> 4 |
> | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y | 997
> | 784 | 997 |00:00:00.16 | 2991 |
> 4 |
> |* 7 | INDEX UNIQUE SCAN | PK_AD_Y | 997
> | 303 | 997 |00:00:00.03 | 1994 |
> 0 |
> | 8 | PARTITION HASH INLIST | |
> 997 | 1 | 15988 |00:00:11.45 | 2400K|
> 32 |
> |* 9 | INDEX RANGE SCAN | IDX_TE_Y_UQ1 |
> 788K| 1 | 15988 |00:00:16.43 | 2400K|
> 32 |
> --------------------------------------------------------------------------- ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 7 - access(("AG"."AD_GRP_ID"=16741340929 OR
> "AG"."AD_GRP_ID"=16741341429 OR
> "AG"."AD_GRP_ID"=16741341929 OR
> "AG"."AD_GRP_ID"=16741342429 OR
> "AG"."AD_GRP_ID"=16741342929 OR
> "AG"."AD_GRP_ID"=16741343429 OR
> "AG"."AD_GRP_ID"=16741343929 OR
> "AG"."AD_GRP_ID"=16741444429 OR
> "AG"."AD_GRP_ID"=16741444929 OR
> "AG"."AD_GRP_ID"=16741445429 OR
> "AG"."AD_GRP_ID"=16741445929 OR
> "AG"."AD_GRP_ID"=16741446429 OR
> "AG"."AD_GRP_ID"=16741446929 OR
> "AG"."AD_GRP_ID"=16741447429 OR
> "AG"."AD_GRP_ID"=16741447929 OR
> "AG"."AD_GRP_ID"=16741448429 OR
> "AG"."AD_GRP_ID"=16741448929 OR
> "AG"."AD_GRP_ID"=16741449429 OR
> "AG"."AD_GRP_ID"=16741449929 OR
> "AG"."AD_GRP_ID"=16741450429 OR
> "AG"."AD_GRP_ID"=16741450929 OR
> "AG"."AD_GRP_ID"=16741451429 OR
> "AG"."AD_GRP_ID"=16741451929 OR
> "AG"."AD_GRP_ID"=16741452429 OR
> "AG"."AD_GRP_ID"=16741452929 OR
> "AG"."AD_GRP_ID"=16741453429 OR
> "AG"."AD_GRP_ID"=16741453929 OR
> "AG"."AD_GRP_ID"=16741454429 OR
> "AG"."AD_GRP_ID"=16741454929 OR
> "AG"."AD_GRP_ID"=16741455429 OR
> "AG"."AD_GRP_ID"=16741455929 OR
> "AG"."AD_GRP_ID"=16741456429 OR
> "AG"."AD_GRP_ID"=16741456929 OR
> "AG"."AD_GRP_ID"=16741457429 OR
> "AG"."AD_GRP_ID"=16741457929 OR
> "AG"."AD_GRP_ID"=16741458429 OR
> "AG"."AD_GRP_ID"=16741458929 OR
> "AG"."AD_GRP_ID"=16741459429 OR
> "AG"."AD_GRP_ID"=16741459929 OR
> "AG"."AD_GRP_ID"=16741460429 OR
> "AG"."AD_GRP_ID"=16741460929 OR
> "AG"."AD_GRP_ID"=16741461429 OR
> "AG"."AD_GRP_ID"=16741461929 OR
> "AG"."AD_GRP_ID"=16741462429 OR
> "AG"."AD_GRP_ID"=16741462929 OR
> "AG"."AD_GRP_ID"=16741463429 OR
> "AG"."AD_GRP_ID"=16741463929 OR
> "AG"."AD_GRP_ID"=16741464429 OR
> "AG"."AD_GRP_ID"=16741464929 OR
> "AG"."AD_GRP_ID"=16741465429 OR
> "AG"."AD_GRP_ID"=16741465929 OR
> "AG"."AD_GRP_ID"=16741466429 OR
> "AG"."AD_GRP_ID"=16741466929 OR
> "AG"."AD_GRP_ID"=16741467429 OR
> "AG"."AD_GRP_ID"=16741467929 OR
> "AG"."AD_GRP_ID"=16741468429 OR
> "AG"."AD_GRP_ID"=16741468929 OR
> "AG"."AD_GRP_ID"=16741469429 OR
> "AG"."AD_GRP_ID"=16741469929 OR
> "AG"."AD_GRP_ID"=16741470429 OR
> "AG"."AD_GRP_ID"=16741470929 OR
> "AG"."AD_GRP_ID"=16741471429 OR
> "AG"."AD_GRP_ID"=16741471929 OR
> "AG"."AD_GRP_ID"=16741472429 OR
> "AG"."AD_GRP_ID"=16741472929 OR
> "AG"."AD_GRP_ID"=16741473429 OR
> "AG"."AD_GRP_ID"=16741473929 OR
> "AG"."AD_GRP_ID"=16741474429 OR
> "AG"."AD_GRP_ID"=16741474929 OR
> "AG"."AD_GRP_ID"=16741475429 OR
> "AG"."AD_GRP_ID"=16741475929 OR
> "AG"."AD_GRP_ID"=16741476429 OR
> "AG"."AD_GRP_ID"=16741476929 OR
> "AG"."AD_GRP_ID"=16741477429 OR
> "AG"."AD_GRP_ID"=16741477929 OR
> "AG"."AD_GRP_ID"=16741478429 OR
> "AG"."AD_GRP_ID"=16741478929 OR
> "AG"."AD_GRP_ID"=16741479429 OR
> "AG"."AD_GRP_ID"=16741479929 OR
> "AG"."AD_GRP_ID"=16741480429 OR
> "AG"."AD_GRP_ID"=16741480929 OR
> "AG"."AD_GRP_ID"=16741481429 OR
> "AG"."AD_GRP_ID"=16741481929 OR
> "AG"."AD_GRP_ID"=16741482429 OR
> "AG"."AD_GRP_ID"=16741482929 OR
> "AG"."AD_GRP_ID"=16741483429 OR
> "AG"."AD_GRP_ID"=16741483929 OR
> "AG"."AD_GRP_ID"=16741484429 OR
> "AG"."AD_GRP_ID"=16741484929 OR
> "AG"."AD_GRP_ID"=16741485429 OR
> "AG"."AD_GRP_ID"=16741485929 OR
> "AG"."AD_GRP_ID"=16741486429 OR
> "AG"."AD_GRP_ID"=16741486929 OR
> "AG"."AD_GRP_ID"=16741487429 OR
> "AG"."AD_GRP_ID"=16741487929 OR
> "AG"."AD_GRP_ID"=16741488429 OR
> "AG"."AD_GRP_ID"=16741488929 OR
> "AG"."AD_GRP_ID"=16741489429 OR
> "AG"."AD_GRP_ID"=16741489929 OR
> "AG"."AD_GRP_ID"=16741490429 OR
> "AG"."AD_GRP_ID"=16741490929 OR
> "AG"."AD_GRP_ID"=16741491429 OR
> "AG"."AD_GRP_ID"=16741491929 OR
> "AG"."AD_GRP_ID"=16741492429 OR
> "AG"."AD_GRP_ID"=16741492929 OR
> "AG"."AD_GRP_ID"=16741493429 OR
> "AG"."AD_GRP_ID"=16741493929 OR
> "AG"."AD_GRP_ID"=16741494429 OR
> "AG"."AD_GRP_ID"=16741494929 OR
> "AG"."AD_GRP_ID"=16741495429 OR
> "AG"."AD_GRP_ID"=16741495929 OR
> "AG"."AD_GRP_ID"=16741496429 OR
> "AG"."AD_GRP_ID"=16741496929 OR
> "AG"."AD_GRP_ID"=16741497429 OR
> "AG"."AD_GRP_ID"=16741497929 OR
> "AG"."AD_GRP_ID"=16741498429 OR
> "AG"."AD_GRP_ID"=16741498929 OR
> "AG"."AD_GRP_ID"=16741499429 OR
> "AG"."AD_GRP_ID"=16741499929 OR
> "AG"."AD_GRP_ID"=16741500429 OR
> "AG"."AD_GRP_ID"=16741500929 OR
> "AG"."AD_GRP_ID"=16741501429 OR
> "AG"."AD_GRP_ID"=16741501929 OR
> "AG"."AD_GRP_ID"=16741502429 OR
> "AG"."AD_GRP_ID"=16741502929 O)
> 9 - access("T"."AD_GRP_ID"="AG"."AD_GRP_ID")
> filter(("T"."AD_GRP_ID"=16741340929 OR
> "T"."AD_GRP_ID"=16741341429 OR
> "T"."AD_GRP_ID"=16741341929 OR
> "T"."AD_GRP_ID"=16741342429 OR
> "T"."AD_GRP_ID"=16741342929 OR
> "T"."AD_GRP_ID"=16741343429 OR
> "T"."AD_GRP_ID"=16741343929 OR
> "T"."AD_GRP_ID"=16741444429 OR
> "T"."AD_GRP_ID"=16741444929 OR
> "T"."AD_GRP_ID"=16741445429 OR
> "T"."AD_GRP_ID"=16741445929 OR
> "T"."AD_GRP_ID"=16741446429 OR
> "T"."AD_GRP_ID"=16741446929 OR
> "T"."AD_GRP_ID"=16741447429 OR
> "T"."AD_GRP_ID"=16741447929 OR
> "T"."AD_GRP_ID"=16741448429 OR
> "T"."AD_GRP_ID"=16741448929 OR
> "T"."AD_GRP_ID"=16741449429 OR
> "T"."AD_GRP_ID"=16741449929 OR
> "T"."AD_GRP_ID"=16741450429 OR
> "T"."AD_GRP_ID"=16741450929 OR
> "T"."AD_GRP_ID"=16741451429 OR
> "T"."AD_GRP_ID"=16741451929 OR
> "T"."AD_GRP_ID"=16741452429 OR
> "T"."AD_GRP_ID"=16741452929 OR
> "T"."AD_GRP_ID"=16741453429 OR
> "T"."AD_GRP_ID"=16741453929 OR
> "T"."AD_GRP_ID"=16741454429 OR
> "T"."AD_GRP_ID"=16741454929 OR
> "T"."AD_GRP_ID"=16741455429 OR
> "T"."AD_GRP_ID"=16741455929 OR
> "T"."AD_GRP_ID"=16741456429 OR
> "T"."AD_GRP_ID"=16741456929 OR
> "T"."AD_GRP_ID"=16741457429 OR
> "T"."AD_GRP_ID"=16741457929 OR
> "T"."AD_GRP_ID"=16741458429 OR
> "T"."AD_GRP_ID"=16741458929 OR
> "T"."AD_GRP_ID"=16741459429 OR
> "T"."AD_GRP_ID"=16741459929 OR
> "T"."AD_GRP_ID"=16741460429 OR
> "T"."AD_GRP_ID"=16741460929 OR
> "T"."AD_GRP_ID"=16741461429 OR
> "T"."AD_GRP_ID"=16741461929 OR
> "T"."AD_GRP_ID"=16741462429 OR
> "T"."AD_GRP_ID"=16741462929 OR
> "T"."AD_GRP_ID"=16741463429 OR
> "T"."AD_GRP_ID"=16741463929 OR
> "T"."AD_GRP_ID"=16741464429 OR
> "T"."AD_GRP_ID"=16741464929 OR
> "T"."AD_GRP_ID"=16741465429 OR
> "T"."AD_GRP_ID"=16741465929 OR
> "T"."AD_GRP_ID"=16741466429 OR
> "T"."AD_GRP_ID"=16741466929 OR
> "T"."AD_GRP_ID"=16741467429 OR
> "T"."AD_GRP_ID"=16741467929 OR
> "T"."AD_GRP_ID"=16741468429 OR
> "T"."AD_GRP_ID"=16741468929 OR
> "T"."AD_GRP_ID"=16741469429 OR
> "T"."AD_GRP_ID"=16741469929 OR
> "T"."AD_GRP_ID"=16741470429 OR
> "T"."AD_GRP_ID"=16741470929 OR
> "T"."AD_GRP_ID"=16741471429 OR
> "T"."AD_GRP_ID"=16741471929 OR
> "T"."AD_GRP_ID"=16741472429 OR
> "T"."AD_GRP_ID"=16741472929 OR
> "T"."AD_GRP_ID"=16741473429 OR
> "T"."AD_GRP_ID"=16741473929 OR
> "T"."AD_GRP_ID"=16741474429 OR
> "T"."AD_GRP_ID"=16741474929 OR
> "T"."AD_GRP_ID"=16741475429 OR
> "T"."AD_GRP_ID"=16741475929 OR
> "T"."AD_GRP_ID"=16741476429 OR
> "T"."AD_GRP_ID"=16741476929 OR
> "T"."AD_GRP_ID"=16741477429 OR
> "T"."AD_GRP_ID"=16741477929 OR
> "T"."AD_GRP_ID"=16741478429 OR
> "T"."AD_GRP_ID"=16741478929 OR
> "T"."AD_GRP_ID"=16741479429 OR
> "T"."AD_GRP_ID"=16741479929 OR
> "T"."AD_GRP_ID"=16741480429 OR
> "T"."AD_GRP_ID"=16741480929 OR
> "T"."AD_GRP_ID"=16741481429 OR
> "T"."AD_GRP_ID"=16741481929 OR
> "T"."AD_GRP_ID"=16741482429 OR
> "T"."AD_GRP_ID"=16741482929 OR
> "T"."AD_GRP_ID"=16741483429 OR
> "T"."AD_GRP_ID"=16741483929 OR
> "T"."AD_GRP_ID"=16741484429 OR
> "T"."AD_GRP_ID"=16741484929 OR
> "T"."AD_GRP_ID"=16741485429 OR
> "T"."AD_GRP_ID"=16741485929 OR
> "T"."AD_GRP_ID"=16741486429 OR
> "T"."AD_GRP_ID"=16741486929 OR
> "T"."AD_GRP_ID"=16741487429 OR
> "T"."AD_GRP_ID"=16741487929 OR
> "T"."AD_GRP_ID"=16741488429 OR
> "T"."AD_GRP_ID"=16741488929 OR
> "T"."AD_GRP_ID"=16741489429 OR
> "T"."AD_GRP_ID"=16741489929 OR
> "T"."AD_GRP_ID"=16741490429 OR
> "T"."AD_GRP_ID"=16741490929 OR
> "T"."AD_GRP_ID"=16741491429 OR
> "T"."AD_GRP_ID"=16741491929 OR
> "T"."AD_GRP_ID"=16741492429 OR
> "T"."AD_GRP_ID"=16741492929 OR
> "T"."AD_GRP_ID"=16741493429 OR
> "T"."AD_GRP_ID"=16741493929 OR
> "T"."AD_GRP_ID"=16741494429 OR
> "T"."AD_GRP_ID"=16741494929 OR
> "T"."AD_GRP_ID"=16741495429 OR
> "T"."AD_GRP_ID"=16741495929 OR
> "T"."AD_GRP_ID"=16741496429 OR
> "T"."AD_GRP_ID"=16741496929 OR
> "T"."AD_GRP_ID"=16741497429 OR
> "T"."AD_GRP_ID"=16741497929 OR
> "T"."AD_GRP_ID"=16741498429 OR
> "T"."AD_GRP_ID"=16741498929 OR
> "T"."AD_GRP_ID"=16741499429 OR
> "T"."AD_GRP_ID"=16741499929 OR
> "T"."AD_GRP_ID"=16741500429 OR
> "T"."AD_GRP_ID"=16741500929 OR
> "T"."AD_GRP_ID"=16741501429 OR
> "T"."AD_GRP_ID"=16741501929 OR
> "T"."AD_GRP_ID"=16741502429 OR
> "T"."AD_GRP_ID"=16741502929 OR
> "T"."AD_GRP_ID"=16741503429 OR
> "T"."AD_GRP_ID"=16741503929 OR
> "T"."AD_GRP_ID"=16741504429 OR
> "T"."AD_GRP_ID"=16741504929 OR)
>
> 118 rows selected.
>
> What I'm not able to understand is 788k "Starts" to Step 9.Why is this
> step executed so many times? The INLIST has 997 values. It seems like
> 788k comes from 997*791
> Because if I revrese the NL, I see following plan:
>
> select /*+ gather_plan_statistics index(ag,PK_AD_Y) leading(t,ag)
> use_nl(t,ag) */ 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;
>
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
> | Id | Operation | Name |
> Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
> 1Mem | Used-Mem |
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
> | 1 | SORT AGGREGATE | |
> 1 | 1 | 1 |00:00:33.37 | 3978K| 3 | |
> | |
> | 2 | PARTITION HASH INLIST | |
> 1 | 790 | 15988 |00:00:55.12 | 3978K| 3 | |
> | |
> |* 3 | HASH JOIN | |
> 791 | 790 | 15988 |00:00:23.24 | 3978K| 3 | 2958K|
> 1691K| 1021K (0)|
> | 4 | INLIST ITERATOR | |
> 791 | | 15988 |00:00:06.57 | 2400K| 0 | |
> | |
> |* 5 | INDEX RANGE SCAN | IDX_TE_Y_UQ1 |
> 788K| 67225 | 15988 |00:00:10.02 | 2400K| 0 | |
> | |
> | 6 | INLIST ITERATOR | |
> 791 | | 997 |00:00:18.62 | 1578K| 3 | |
> | |
> | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y |
> 788K| 784 | 997 |00:00:16.67 | 1578K| 3 | |
> | |
> |* 8 | INDEX UNIQUE SCAN | PK_AD_Y |
> 788K| 303 | 997 |00:00:08.07 | 1577K| 0 | |
> | |
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
>
> Do you notice 791 in "starts" here.
> I see some correlation but not able to form a conclusive statement
> from this.
>
> I have been able to tune the SQL by using use_concat hint. But the
> solution finding was kind of luck.
>
> I would like if any one can explain me how Oracle would have
> calculated the "Starts" to 788k and how use_concat hint solved the
> issue.
>
> If I use the hint USE_CONCAT, I get the following plan:
>
> --------------------------------------------------------------------------- ---------------------------------------------------
> | Id | Operation | Name |
> Starts | E-Rows | A-Rows | A-Time | Buffers |
> Reads |
> --------------------------------------------------------------------------- ---------------------------------------------------
> | 1 | SORT AGGREGATE |
> | 1 | 1 | 1 |00:00:00.40 | 9047 |
> 3 |
> | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TE_Y | 1
> | 126 | 15988 |00:00:00.50 | 9047 |
> 3 |
> | 3 | NESTED LOOPS |
> | 1 | 99200 | 16986 |00:00:00.19 | 6140 |
> 3 |
> | 4 | INLIST ITERATOR |
> | 1 | | 997 |00:00:00.06 | 2991 |
> 1 |
> | 5 | PARTITION HASH ITERATOR | |
> 997 | 784 | 997 |00:00:00.11 | 2991 |
> 1 |
> | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y | 997
> | 784 | 997 |00:00:00.09 | 2991 |
> 1 |
> |* 7 | INDEX UNIQUE SCAN | PK_AD_Y | 997
> | 303 | 997 |00:00:00.02 | 1994 |
> 0 |
> | 8 | PARTITION HASH ITERATOR | |
> 997 | 126 | 15988 |00:00:00.34 | 3149 |
> 2 |
> |* 9 | INDEX RANGE SCAN | IDX_TE_Y_UQ1 | 997
> | 126 | 15988 |00:00:00.25 | 3149 |
> 2 |
> --------------------------------------------------------------------------- ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 7 - access(("AG"."AD_GRP_ID"=16741340929 OR
> "AG"."AD_GRP_ID"=16741341429 OR
> "AG"."AD_GRP_ID"=16741341929 OR
> "AG"."AD_GRP_ID"=16741342429 OR
> "AG"."AD_GRP_ID"=16741342929 OR
> "AG"."AD_GRP_ID"=16741343429 OR
> "AG"."AD_GRP_ID"=16741343929 OR
> "AG"."AD_GRP_ID"=16741444429 OR
> "AG"."AD_GRP_ID"=16741444929 OR
> "AG"."AD_GRP_ID"=16741445429 OR
> "AG"."AD_GRP_ID"=16741445929 OR
> "AG"."AD_GRP_ID"=16741446429 OR
> "AG"."AD_GRP_ID"=16741446929 OR
> "AG"."AD_GRP_ID"=16741447429 OR
> "AG"."AD_GRP_ID"=16741447929 OR
> "AG"."AD_GRP_ID"=16741448429 OR
> "AG"."AD_GRP_ID"=16741448929 OR
> "AG"."AD_GRP_ID"=16741449429 OR
> "AG"."AD_GRP_ID"=16741449929 OR
> "AG"."AD_GRP_ID"=16741450429 OR
> "AG"."AD_GRP_ID"=16741450929 OR
> "AG"."AD_GRP_ID"=16741451429 OR
> "AG"."AD_GRP_ID"=16741451929 OR
> "AG"."AD_GRP_ID"=16741452429 OR
> "AG"."AD_GRP_ID"=16741452929 OR
> "AG"."AD_GRP_ID"=16741453429 OR
> "AG"."AD_GRP_ID"=16741453929 OR
> "AG"."AD_GRP_ID"=16741454429 OR
> "AG"."AD_GRP_ID"=16741454929 OR
> "AG"."AD_GRP_ID"=16741455429 OR
> "AG"."AD_GRP_ID"=16741455929 OR
> "AG"."AD_GRP_ID"=16741456429 OR
> "AG"."AD_GRP_ID"=16741456929 OR
> "AG"."AD_GRP_ID"=16741457429 OR
> "AG"."AD_GRP_ID"=16741457929 OR
> "AG"."AD_GRP_ID"=16741458429 OR
> "AG"."AD_GRP_ID"=16741458929 OR
> "AG"."AD_GRP_ID"=16741459429 OR
> "AG"."AD_GRP_ID"=16741459929 OR
> "AG"."AD_GRP_ID"=16741460429 OR
> "AG"."AD_GRP_ID"=16741460929 OR
> "AG"."AD_GRP_ID"=16741461429 OR
> "AG"."AD_GRP_ID"=16741461929 OR
> "AG"."AD_GRP_ID"=16741462429 OR
> "AG"."AD_GRP_ID"=16741462929 OR
> "AG"."AD_GRP_ID"=16741463429 OR
> "AG"."AD_GRP_ID"=16741463929 OR
> "AG"."AD_GRP_ID"=16741464429 OR
> "AG"."AD_GRP_ID"=16741464929 OR
> "AG"."AD_GRP_ID"=16741465429 OR
> "AG"."AD_GRP_ID"=16741465929 OR
> "AG"."AD_GRP_ID"=16741466429 OR
> "AG"."AD_GRP_ID"=16741466929 OR
> "AG"."AD_GRP_ID"=16741467429 OR
> "AG"."AD_GRP_ID"=16741467929 OR
> "AG"."AD_GRP_ID"=16741468429 OR
> "AG"."AD_GRP_ID"=16741468929 OR
> "AG"."AD_GRP_ID"=16741469429 OR
> "AG"."AD_GRP_ID"=16741469929 OR
> "AG"."AD_GRP_ID"=16741470429 OR
> "AG"."AD_GRP_ID"=16741470929 OR
> "AG"."AD_GRP_ID"=16741471429 OR
> "AG"."AD_GRP_ID"=16741471929 OR
> "AG"."AD_GRP_ID"=16741472429 OR
> "AG"."AD_GRP_ID"=16741472929 OR
> "AG"."AD_GRP_ID"=16741473429 OR
> "AG"."AD_GRP_ID"=16741473929 OR
> "AG"."AD_GRP_ID"=16741474429 OR
> "AG"."AD_GRP_ID"=16741474929 OR
> "AG"."AD_GRP_ID"=16741475429 OR
> "AG"."AD_GRP_ID"=16741475929 OR
> "AG"."AD_GRP_ID"=16741476429 OR
> "AG"."AD_GRP_ID"=16741476929 OR
> "AG"."AD_GRP_ID"=16741477429 OR
> "AG"."AD_GRP_ID"=16741477929 OR
> "AG"."AD_GRP_ID"=16741478429 OR
> "AG"."AD_GRP_ID"=16741478929 OR
> "AG"."AD_GRP_ID"=16741479429 OR
> "AG"."AD_GRP_ID"=16741479929 OR
> "AG"."AD_GRP_ID"=16741480429 OR
> "AG"."AD_GRP_ID"=16741480929 OR
> "AG"."AD_GRP_ID"=16741481429 OR
> "AG"."AD_GRP_ID"=16741481929 OR
> "AG"."AD_GRP_ID"=16741482429 OR
> "AG"."AD_GRP_ID"=16741482929 OR
> "AG"."AD_GRP_ID"=16741483429 OR
> "AG"."AD_GRP_ID"=16741483929 OR
> "AG"."AD_GRP_ID"=16741484429 OR
> "AG"."AD_GRP_ID"=16741484929 OR
> "AG"."AD_GRP_ID"=16741485429 OR
> "AG"."AD_GRP_ID"=16741485929 OR
> "AG"."AD_GRP_ID"=16741486429 OR
> "AG"."AD_GRP_ID"=16741486929 OR
> "AG"."AD_GRP_ID"=16741487429 OR
> "AG"."AD_GRP_ID"=16741487929 OR
> "AG"."AD_GRP_ID"=16741488429 OR
> "AG"."AD_GRP_ID"=16741488929 OR
> "AG"."AD_GRP_ID"=16741489429 OR
> "AG"."AD_GRP_ID"=16741489929 OR
> "AG"."AD_GRP_ID"=16741490429 OR
> "AG"."AD_GRP_ID"=16741490929 OR
> "AG"."AD_GRP_ID"=16741491429 OR
> "AG"."AD_GRP_ID"=16741491929 OR
> "AG"."AD_GRP_ID"=16741492429 OR
> "AG"."AD_GRP_ID"=16741492929 OR
> "AG"."AD_GRP_ID"=16741493429 OR
> "AG"."AD_GRP_ID"=16741493929 OR
> "AG"."AD_GRP_ID"=16741494429 OR
> "AG"."AD_GRP_ID"=16741494929 OR
> "AG"."AD_GRP_ID"=16741495429 OR
> "AG"."AD_GRP_ID"=16741495929 OR
> "AG"."AD_GRP_ID"=16741496429 OR
> "AG"."AD_GRP_ID"=16741496929 OR
> "AG"."AD_GRP_ID"=16741497429 OR
> "AG"."AD_GRP_ID"=16741497929 OR
> "AG"."AD_GRP_ID"=16741498429 OR
> "AG"."AD_GRP_ID"=16741498929 OR
> "AG"."AD_GRP_ID"=16741499429 OR
> "AG"."AD_GRP_ID"=16741499929 OR
> "AG"."AD_GRP_ID"=16741500429 OR
> "AG"."AD_GRP_ID"=16741500929 OR
> "AG"."AD_GRP_ID"=16741501429 OR
> "AG"."AD_GRP_ID"=16741501929 OR
> "AG"."AD_GRP_ID"=16741502429 OR
> "AG"."AD_GRP_ID"=16741502929 O)
> 9 - access("T"."AD_GRP_ID"="AG"."AD_GRP_ID")
>
> Notice there is NO FILTER in Predicate information after step 9.
>
> Also the PARTITION HASH INLIST will change to PARTITION HASH ITERATOR.
>
> More than solution, I'm curious to know why in first place we were
> doing INDEX scan 788k.
>
> Oracle version: 10.2.0.4
> Linux zebra.mm.com 2.6.9-89.ELsmp #1 SMP Mon Apr 20 10:33:05 EDT 2009
> x86_64 x86_64 x86_64 GNU/Linux
> Both the tables TE_Y and AD_Y are hash partitioned by AD_GRP_ID. No of
> partitions 2048.
> Index info:
>
> SQL> _at_index
> Enter table name: TE_Y
>
> COLUMN_POSITION COLUMN_NAME INDEX_NAME
> --------------- ------------------------------
> ------------------------------
> 1 TERM_ID IDX_TE_Y1
> 1 ACCT_ID IDX_TE_Y3
> 2 URL_ID IDX_TE_Y3
> 1 AD_GRP_ID IDX_TE_Y_UQ1
> 2 CANON_SEARCH_TEXT IDX_TE_Y_UQ1
> 3 DEL_TMSTMP IDX_TE_Y_UQ1
>
> 6 rows selected.
>
> INDEX_NAME PAR UNIQUENES NUM_ROWS LEAF_BLOCKS
> DISTINCT_KEYS BLEVEL CLUSTERING_FACTOR
> ------------------------------ --- --------- ---------- -----------
> ------------- ---------- -----------------
> IDX_TE_Y1 YES NONUNIQUE 534607689 5011688 534603138 2
> 300072
> IDX_TE_Y3 YES NONUNIQUE 566383554 3026998 113693664
> 2 1512625
> IDX_TE_Y_UQ1 YES UNIQUE 534035467 5803793 534035467
> 2 803940
>
> SQL> _at_index
> Enter table name: AD_Y
>
> COLUMN_POSITION COLUMN_NAME INDEX_NAME
> --------------- ------------------------------
> ------------------------------
> 1 CRTV_OPT_ENABLED_LAST_UPD IDX_AD_Y2
> 1 LAST_CMPGN_OPT_UPD IDX_AD_Y3
> 1 ACCT_ID IDX_AD_Y4
> 1 STATUS IDX_AD_Y6
> 2 SEARCH_ENABLED IDX_AD_Y6
> 3 CRTV_OPT_ENABLED IDX_AD_Y6
> 4 NEXT_CRTV_OPT_CHK IDX_AD_Y6
> 5 AD_GRP_ID IDX_AD_Y6
> 6 CMPGN_ID IDX_AD_Y6
> 1 STATUS IDX_AD_Y7
> 2 SEARCH_ENABLED IDX_AD_Y7
> 3 CRTV_OPT_ENABLED_LAST_UPD IDX_AD_Y7
> 4 AD_GRP_ID IDX_AD_Y7
> 5 CMPGN_ID IDX_AD_Y7
> 1 CMPGN_ID IDX_AD_Y_UQ1
> 2 AD_GRP_NAME IDX_AD_Y_UQ1
> 3 DEL_TMSTMP IDX_AD_Y_UQ1
> 1 AD_GRP_ID PK_AD_Y
>
> 18 rows selected.
>
> INDEX_NAME PAR UNIQUENES NUM_ROWS LEAF_BLOCKS
> DISTINCT_KEYS BLEVEL CLUSTERING_FACTOR
> ------------------------------ --- --------- ---------- -----------
> ------------- ---------- -----------------
> IDX_AD_Y2 YES NONUNIQUE 24940544 102400 22863872
> 1 9419
> IDX_AD_Y3 NO NONUNIQUE 40959 2422 1718 2
> 39396
> IDX_AD_Y4 YES NONUNIQUE 32981324 134718 260963 2
> 206827
> IDX_AD_Y6 YES NONUNIQUE 31682560 2869248 31682560
> 2 12788
> IDX_AD_Y7 YES NONUNIQUE 31569920 301056 31569920
> 1 12259
> IDX_AD_Y_UQ1 YES UNIQUE 32983623 341326 32983623
> 2 57062
> PK_AD_Y YES UNIQUE 31569920 75776 31569920 1
> 11922
>
> 7 rows selected.
>
> I understand that I may have missed some important information that
> could be necessary to come to some sort of conclusion.
> Please ask the same. I will revert back at the earliest.
Date: Wed, 23 Dec 2009 10:49:18 -0800 (PST)
Message-ID: <b02dce21-6a3c-4998-87b1-e4f017207e32_at_n13g2000vbe.googlegroups.com>
On Dec 23, 1:22 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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;
>
> PLAN:
> ======
> --------------------------------------------------------------------------- ---------------------------------------------------
> | Id | Operation | Name |
> Starts | E-Rows | A-Rows | A-Time | Buffers |
> Reads |
> --------------------------------------------------------------------------- ---------------------------------------------------
> | 1 | SORT AGGREGATE |
> | 1 | 1 | 1 |00:00:20.72 | 2406K|
> 36 |
> | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TE_Y | 1
> | 1 | 15988 |00:00:20.64 | 2406K|
> 36 |
> | 3 | NESTED LOOPS |
> | 1 | 790 | 16986 |00:02:46.81 | 2403K|
> 36 |
> | 4 | INLIST ITERATOR |
> | 1 | | 997 |00:00:00.22 | 2991 |
> 4 |
> | 5 | PARTITION HASH ITERATOR | |
> 997 | 784 | 997 |00:00:00.19 | 2991 |
> 4 |
> | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y | 997
> | 784 | 997 |00:00:00.16 | 2991 |
> 4 |
> |* 7 | INDEX UNIQUE SCAN | PK_AD_Y | 997
> | 303 | 997 |00:00:00.03 | 1994 |
> 0 |
> | 8 | PARTITION HASH INLIST | |
> 997 | 1 | 15988 |00:00:11.45 | 2400K|
> 32 |
> |* 9 | INDEX RANGE SCAN | IDX_TE_Y_UQ1 |
> 788K| 1 | 15988 |00:00:16.43 | 2400K|
> 32 |
> --------------------------------------------------------------------------- ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 7 - access(("AG"."AD_GRP_ID"=16741340929 OR
> "AG"."AD_GRP_ID"=16741341429 OR
> "AG"."AD_GRP_ID"=16741341929 OR
> "AG"."AD_GRP_ID"=16741342429 OR
> "AG"."AD_GRP_ID"=16741342929 OR
> "AG"."AD_GRP_ID"=16741343429 OR
> "AG"."AD_GRP_ID"=16741343929 OR
> "AG"."AD_GRP_ID"=16741444429 OR
> "AG"."AD_GRP_ID"=16741444929 OR
> "AG"."AD_GRP_ID"=16741445429 OR
> "AG"."AD_GRP_ID"=16741445929 OR
> "AG"."AD_GRP_ID"=16741446429 OR
> "AG"."AD_GRP_ID"=16741446929 OR
> "AG"."AD_GRP_ID"=16741447429 OR
> "AG"."AD_GRP_ID"=16741447929 OR
> "AG"."AD_GRP_ID"=16741448429 OR
> "AG"."AD_GRP_ID"=16741448929 OR
> "AG"."AD_GRP_ID"=16741449429 OR
> "AG"."AD_GRP_ID"=16741449929 OR
> "AG"."AD_GRP_ID"=16741450429 OR
> "AG"."AD_GRP_ID"=16741450929 OR
> "AG"."AD_GRP_ID"=16741451429 OR
> "AG"."AD_GRP_ID"=16741451929 OR
> "AG"."AD_GRP_ID"=16741452429 OR
> "AG"."AD_GRP_ID"=16741452929 OR
> "AG"."AD_GRP_ID"=16741453429 OR
> "AG"."AD_GRP_ID"=16741453929 OR
> "AG"."AD_GRP_ID"=16741454429 OR
> "AG"."AD_GRP_ID"=16741454929 OR
> "AG"."AD_GRP_ID"=16741455429 OR
> "AG"."AD_GRP_ID"=16741455929 OR
> "AG"."AD_GRP_ID"=16741456429 OR
> "AG"."AD_GRP_ID"=16741456929 OR
> "AG"."AD_GRP_ID"=16741457429 OR
> "AG"."AD_GRP_ID"=16741457929 OR
> "AG"."AD_GRP_ID"=16741458429 OR
> "AG"."AD_GRP_ID"=16741458929 OR
> "AG"."AD_GRP_ID"=16741459429 OR
> "AG"."AD_GRP_ID"=16741459929 OR
> "AG"."AD_GRP_ID"=16741460429 OR
> "AG"."AD_GRP_ID"=16741460929 OR
> "AG"."AD_GRP_ID"=16741461429 OR
> "AG"."AD_GRP_ID"=16741461929 OR
> "AG"."AD_GRP_ID"=16741462429 OR
> "AG"."AD_GRP_ID"=16741462929 OR
> "AG"."AD_GRP_ID"=16741463429 OR
> "AG"."AD_GRP_ID"=16741463929 OR
> "AG"."AD_GRP_ID"=16741464429 OR
> "AG"."AD_GRP_ID"=16741464929 OR
> "AG"."AD_GRP_ID"=16741465429 OR
> "AG"."AD_GRP_ID"=16741465929 OR
> "AG"."AD_GRP_ID"=16741466429 OR
> "AG"."AD_GRP_ID"=16741466929 OR
> "AG"."AD_GRP_ID"=16741467429 OR
> "AG"."AD_GRP_ID"=16741467929 OR
> "AG"."AD_GRP_ID"=16741468429 OR
> "AG"."AD_GRP_ID"=16741468929 OR
> "AG"."AD_GRP_ID"=16741469429 OR
> "AG"."AD_GRP_ID"=16741469929 OR
> "AG"."AD_GRP_ID"=16741470429 OR
> "AG"."AD_GRP_ID"=16741470929 OR
> "AG"."AD_GRP_ID"=16741471429 OR
> "AG"."AD_GRP_ID"=16741471929 OR
> "AG"."AD_GRP_ID"=16741472429 OR
> "AG"."AD_GRP_ID"=16741472929 OR
> "AG"."AD_GRP_ID"=16741473429 OR
> "AG"."AD_GRP_ID"=16741473929 OR
> "AG"."AD_GRP_ID"=16741474429 OR
> "AG"."AD_GRP_ID"=16741474929 OR
> "AG"."AD_GRP_ID"=16741475429 OR
> "AG"."AD_GRP_ID"=16741475929 OR
> "AG"."AD_GRP_ID"=16741476429 OR
> "AG"."AD_GRP_ID"=16741476929 OR
> "AG"."AD_GRP_ID"=16741477429 OR
> "AG"."AD_GRP_ID"=16741477929 OR
> "AG"."AD_GRP_ID"=16741478429 OR
> "AG"."AD_GRP_ID"=16741478929 OR
> "AG"."AD_GRP_ID"=16741479429 OR
> "AG"."AD_GRP_ID"=16741479929 OR
> "AG"."AD_GRP_ID"=16741480429 OR
> "AG"."AD_GRP_ID"=16741480929 OR
> "AG"."AD_GRP_ID"=16741481429 OR
> "AG"."AD_GRP_ID"=16741481929 OR
> "AG"."AD_GRP_ID"=16741482429 OR
> "AG"."AD_GRP_ID"=16741482929 OR
> "AG"."AD_GRP_ID"=16741483429 OR
> "AG"."AD_GRP_ID"=16741483929 OR
> "AG"."AD_GRP_ID"=16741484429 OR
> "AG"."AD_GRP_ID"=16741484929 OR
> "AG"."AD_GRP_ID"=16741485429 OR
> "AG"."AD_GRP_ID"=16741485929 OR
> "AG"."AD_GRP_ID"=16741486429 OR
> "AG"."AD_GRP_ID"=16741486929 OR
> "AG"."AD_GRP_ID"=16741487429 OR
> "AG"."AD_GRP_ID"=16741487929 OR
> "AG"."AD_GRP_ID"=16741488429 OR
> "AG"."AD_GRP_ID"=16741488929 OR
> "AG"."AD_GRP_ID"=16741489429 OR
> "AG"."AD_GRP_ID"=16741489929 OR
> "AG"."AD_GRP_ID"=16741490429 OR
> "AG"."AD_GRP_ID"=16741490929 OR
> "AG"."AD_GRP_ID"=16741491429 OR
> "AG"."AD_GRP_ID"=16741491929 OR
> "AG"."AD_GRP_ID"=16741492429 OR
> "AG"."AD_GRP_ID"=16741492929 OR
> "AG"."AD_GRP_ID"=16741493429 OR
> "AG"."AD_GRP_ID"=16741493929 OR
> "AG"."AD_GRP_ID"=16741494429 OR
> "AG"."AD_GRP_ID"=16741494929 OR
> "AG"."AD_GRP_ID"=16741495429 OR
> "AG"."AD_GRP_ID"=16741495929 OR
> "AG"."AD_GRP_ID"=16741496429 OR
> "AG"."AD_GRP_ID"=16741496929 OR
> "AG"."AD_GRP_ID"=16741497429 OR
> "AG"."AD_GRP_ID"=16741497929 OR
> "AG"."AD_GRP_ID"=16741498429 OR
> "AG"."AD_GRP_ID"=16741498929 OR
> "AG"."AD_GRP_ID"=16741499429 OR
> "AG"."AD_GRP_ID"=16741499929 OR
> "AG"."AD_GRP_ID"=16741500429 OR
> "AG"."AD_GRP_ID"=16741500929 OR
> "AG"."AD_GRP_ID"=16741501429 OR
> "AG"."AD_GRP_ID"=16741501929 OR
> "AG"."AD_GRP_ID"=16741502429 OR
> "AG"."AD_GRP_ID"=16741502929 O)
> 9 - access("T"."AD_GRP_ID"="AG"."AD_GRP_ID")
> filter(("T"."AD_GRP_ID"=16741340929 OR
> "T"."AD_GRP_ID"=16741341429 OR
> "T"."AD_GRP_ID"=16741341929 OR
> "T"."AD_GRP_ID"=16741342429 OR
> "T"."AD_GRP_ID"=16741342929 OR
> "T"."AD_GRP_ID"=16741343429 OR
> "T"."AD_GRP_ID"=16741343929 OR
> "T"."AD_GRP_ID"=16741444429 OR
> "T"."AD_GRP_ID"=16741444929 OR
> "T"."AD_GRP_ID"=16741445429 OR
> "T"."AD_GRP_ID"=16741445929 OR
> "T"."AD_GRP_ID"=16741446429 OR
> "T"."AD_GRP_ID"=16741446929 OR
> "T"."AD_GRP_ID"=16741447429 OR
> "T"."AD_GRP_ID"=16741447929 OR
> "T"."AD_GRP_ID"=16741448429 OR
> "T"."AD_GRP_ID"=16741448929 OR
> "T"."AD_GRP_ID"=16741449429 OR
> "T"."AD_GRP_ID"=16741449929 OR
> "T"."AD_GRP_ID"=16741450429 OR
> "T"."AD_GRP_ID"=16741450929 OR
> "T"."AD_GRP_ID"=16741451429 OR
> "T"."AD_GRP_ID"=16741451929 OR
> "T"."AD_GRP_ID"=16741452429 OR
> "T"."AD_GRP_ID"=16741452929 OR
> "T"."AD_GRP_ID"=16741453429 OR
> "T"."AD_GRP_ID"=16741453929 OR
> "T"."AD_GRP_ID"=16741454429 OR
> "T"."AD_GRP_ID"=16741454929 OR
> "T"."AD_GRP_ID"=16741455429 OR
> "T"."AD_GRP_ID"=16741455929 OR
> "T"."AD_GRP_ID"=16741456429 OR
> "T"."AD_GRP_ID"=16741456929 OR
> "T"."AD_GRP_ID"=16741457429 OR
> "T"."AD_GRP_ID"=16741457929 OR
> "T"."AD_GRP_ID"=16741458429 OR
> "T"."AD_GRP_ID"=16741458929 OR
> "T"."AD_GRP_ID"=16741459429 OR
> "T"."AD_GRP_ID"=16741459929 OR
> "T"."AD_GRP_ID"=16741460429 OR
> "T"."AD_GRP_ID"=16741460929 OR
> "T"."AD_GRP_ID"=16741461429 OR
> "T"."AD_GRP_ID"=16741461929 OR
> "T"."AD_GRP_ID"=16741462429 OR
> "T"."AD_GRP_ID"=16741462929 OR
> "T"."AD_GRP_ID"=16741463429 OR
> "T"."AD_GRP_ID"=16741463929 OR
> "T"."AD_GRP_ID"=16741464429 OR
> "T"."AD_GRP_ID"=16741464929 OR
> "T"."AD_GRP_ID"=16741465429 OR
> "T"."AD_GRP_ID"=16741465929 OR
> "T"."AD_GRP_ID"=16741466429 OR
> "T"."AD_GRP_ID"=16741466929 OR
> "T"."AD_GRP_ID"=16741467429 OR
> "T"."AD_GRP_ID"=16741467929 OR
> "T"."AD_GRP_ID"=16741468429 OR
> "T"."AD_GRP_ID"=16741468929 OR
> "T"."AD_GRP_ID"=16741469429 OR
> "T"."AD_GRP_ID"=16741469929 OR
> "T"."AD_GRP_ID"=16741470429 OR
> "T"."AD_GRP_ID"=16741470929 OR
> "T"."AD_GRP_ID"=16741471429 OR
> "T"."AD_GRP_ID"=16741471929 OR
> "T"."AD_GRP_ID"=16741472429 OR
> "T"."AD_GRP_ID"=16741472929 OR
> "T"."AD_GRP_ID"=16741473429 OR
> "T"."AD_GRP_ID"=16741473929 OR
> "T"."AD_GRP_ID"=16741474429 OR
> "T"."AD_GRP_ID"=16741474929 OR
> "T"."AD_GRP_ID"=16741475429 OR
> "T"."AD_GRP_ID"=16741475929 OR
> "T"."AD_GRP_ID"=16741476429 OR
> "T"."AD_GRP_ID"=16741476929 OR
> "T"."AD_GRP_ID"=16741477429 OR
> "T"."AD_GRP_ID"=16741477929 OR
> "T"."AD_GRP_ID"=16741478429 OR
> "T"."AD_GRP_ID"=16741478929 OR
> "T"."AD_GRP_ID"=16741479429 OR
> "T"."AD_GRP_ID"=16741479929 OR
> "T"."AD_GRP_ID"=16741480429 OR
> "T"."AD_GRP_ID"=16741480929 OR
> "T"."AD_GRP_ID"=16741481429 OR
> "T"."AD_GRP_ID"=16741481929 OR
> "T"."AD_GRP_ID"=16741482429 OR
> "T"."AD_GRP_ID"=16741482929 OR
> "T"."AD_GRP_ID"=16741483429 OR
> "T"."AD_GRP_ID"=16741483929 OR
> "T"."AD_GRP_ID"=16741484429 OR
> "T"."AD_GRP_ID"=16741484929 OR
> "T"."AD_GRP_ID"=16741485429 OR
> "T"."AD_GRP_ID"=16741485929 OR
> "T"."AD_GRP_ID"=16741486429 OR
> "T"."AD_GRP_ID"=16741486929 OR
> "T"."AD_GRP_ID"=16741487429 OR
> "T"."AD_GRP_ID"=16741487929 OR
> "T"."AD_GRP_ID"=16741488429 OR
> "T"."AD_GRP_ID"=16741488929 OR
> "T"."AD_GRP_ID"=16741489429 OR
> "T"."AD_GRP_ID"=16741489929 OR
> "T"."AD_GRP_ID"=16741490429 OR
> "T"."AD_GRP_ID"=16741490929 OR
> "T"."AD_GRP_ID"=16741491429 OR
> "T"."AD_GRP_ID"=16741491929 OR
> "T"."AD_GRP_ID"=16741492429 OR
> "T"."AD_GRP_ID"=16741492929 OR
> "T"."AD_GRP_ID"=16741493429 OR
> "T"."AD_GRP_ID"=16741493929 OR
> "T"."AD_GRP_ID"=16741494429 OR
> "T"."AD_GRP_ID"=16741494929 OR
> "T"."AD_GRP_ID"=16741495429 OR
> "T"."AD_GRP_ID"=16741495929 OR
> "T"."AD_GRP_ID"=16741496429 OR
> "T"."AD_GRP_ID"=16741496929 OR
> "T"."AD_GRP_ID"=16741497429 OR
> "T"."AD_GRP_ID"=16741497929 OR
> "T"."AD_GRP_ID"=16741498429 OR
> "T"."AD_GRP_ID"=16741498929 OR
> "T"."AD_GRP_ID"=16741499429 OR
> "T"."AD_GRP_ID"=16741499929 OR
> "T"."AD_GRP_ID"=16741500429 OR
> "T"."AD_GRP_ID"=16741500929 OR
> "T"."AD_GRP_ID"=16741501429 OR
> "T"."AD_GRP_ID"=16741501929 OR
> "T"."AD_GRP_ID"=16741502429 OR
> "T"."AD_GRP_ID"=16741502929 OR
> "T"."AD_GRP_ID"=16741503429 OR
> "T"."AD_GRP_ID"=16741503929 OR
> "T"."AD_GRP_ID"=16741504429 OR
> "T"."AD_GRP_ID"=16741504929 OR)
>
> 118 rows selected.
>
> What I'm not able to understand is 788k "Starts" to Step 9.Why is this
> step executed so many times? The INLIST has 997 values. It seems like
> 788k comes from 997*791
> Because if I revrese the NL, I see following plan:
>
> select /*+ gather_plan_statistics index(ag,PK_AD_Y) leading(t,ag)
> use_nl(t,ag) */ 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;
>
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
> | Id | Operation | Name |
> Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
> 1Mem | Used-Mem |
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
> | 1 | SORT AGGREGATE | |
> 1 | 1 | 1 |00:00:33.37 | 3978K| 3 | |
> | |
> | 2 | PARTITION HASH INLIST | |
> 1 | 790 | 15988 |00:00:55.12 | 3978K| 3 | |
> | |
> |* 3 | HASH JOIN | |
> 791 | 790 | 15988 |00:00:23.24 | 3978K| 3 | 2958K|
> 1691K| 1021K (0)|
> | 4 | INLIST ITERATOR | |
> 791 | | 15988 |00:00:06.57 | 2400K| 0 | |
> | |
> |* 5 | INDEX RANGE SCAN | IDX_TE_Y_UQ1 |
> 788K| 67225 | 15988 |00:00:10.02 | 2400K| 0 | |
> | |
> | 6 | INLIST ITERATOR | |
> 791 | | 997 |00:00:18.62 | 1578K| 3 | |
> | |
> | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y |
> 788K| 784 | 997 |00:00:16.67 | 1578K| 3 | |
> | |
> |* 8 | INDEX UNIQUE SCAN | PK_AD_Y |
> 788K| 303 | 997 |00:00:08.07 | 1577K| 0 | |
> | |
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
>
> Do you notice 791 in "starts" here.
> I see some correlation but not able to form a conclusive statement
> from this.
>
> I have been able to tune the SQL by using use_concat hint. But the
> solution finding was kind of luck.
>
> I would like if any one can explain me how Oracle would have
> calculated the "Starts" to 788k and how use_concat hint solved the
> issue.
>
> If I use the hint USE_CONCAT, I get the following plan:
>
> --------------------------------------------------------------------------- ---------------------------------------------------
> | Id | Operation | Name |
> Starts | E-Rows | A-Rows | A-Time | Buffers |
> Reads |
> --------------------------------------------------------------------------- ---------------------------------------------------
> | 1 | SORT AGGREGATE |
> | 1 | 1 | 1 |00:00:00.40 | 9047 |
> 3 |
> | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TE_Y | 1
> | 126 | 15988 |00:00:00.50 | 9047 |
> 3 |
> | 3 | NESTED LOOPS |
> | 1 | 99200 | 16986 |00:00:00.19 | 6140 |
> 3 |
> | 4 | INLIST ITERATOR |
> | 1 | | 997 |00:00:00.06 | 2991 |
> 1 |
> | 5 | PARTITION HASH ITERATOR | |
> 997 | 784 | 997 |00:00:00.11 | 2991 |
> 1 |
> | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y | 997
> | 784 | 997 |00:00:00.09 | 2991 |
> 1 |
> |* 7 | INDEX UNIQUE SCAN | PK_AD_Y | 997
> | 303 | 997 |00:00:00.02 | 1994 |
> 0 |
> | 8 | PARTITION HASH ITERATOR | |
> 997 | 126 | 15988 |00:00:00.34 | 3149 |
> 2 |
> |* 9 | INDEX RANGE SCAN | IDX_TE_Y_UQ1 | 997
> | 126 | 15988 |00:00:00.25 | 3149 |
> 2 |
> --------------------------------------------------------------------------- ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 7 - access(("AG"."AD_GRP_ID"=16741340929 OR
> "AG"."AD_GRP_ID"=16741341429 OR
> "AG"."AD_GRP_ID"=16741341929 OR
> "AG"."AD_GRP_ID"=16741342429 OR
> "AG"."AD_GRP_ID"=16741342929 OR
> "AG"."AD_GRP_ID"=16741343429 OR
> "AG"."AD_GRP_ID"=16741343929 OR
> "AG"."AD_GRP_ID"=16741444429 OR
> "AG"."AD_GRP_ID"=16741444929 OR
> "AG"."AD_GRP_ID"=16741445429 OR
> "AG"."AD_GRP_ID"=16741445929 OR
> "AG"."AD_GRP_ID"=16741446429 OR
> "AG"."AD_GRP_ID"=16741446929 OR
> "AG"."AD_GRP_ID"=16741447429 OR
> "AG"."AD_GRP_ID"=16741447929 OR
> "AG"."AD_GRP_ID"=16741448429 OR
> "AG"."AD_GRP_ID"=16741448929 OR
> "AG"."AD_GRP_ID"=16741449429 OR
> "AG"."AD_GRP_ID"=16741449929 OR
> "AG"."AD_GRP_ID"=16741450429 OR
> "AG"."AD_GRP_ID"=16741450929 OR
> "AG"."AD_GRP_ID"=16741451429 OR
> "AG"."AD_GRP_ID"=16741451929 OR
> "AG"."AD_GRP_ID"=16741452429 OR
> "AG"."AD_GRP_ID"=16741452929 OR
> "AG"."AD_GRP_ID"=16741453429 OR
> "AG"."AD_GRP_ID"=16741453929 OR
> "AG"."AD_GRP_ID"=16741454429 OR
> "AG"."AD_GRP_ID"=16741454929 OR
> "AG"."AD_GRP_ID"=16741455429 OR
> "AG"."AD_GRP_ID"=16741455929 OR
> "AG"."AD_GRP_ID"=16741456429 OR
> "AG"."AD_GRP_ID"=16741456929 OR
> "AG"."AD_GRP_ID"=16741457429 OR
> "AG"."AD_GRP_ID"=16741457929 OR
> "AG"."AD_GRP_ID"=16741458429 OR
> "AG"."AD_GRP_ID"=16741458929 OR
> "AG"."AD_GRP_ID"=16741459429 OR
> "AG"."AD_GRP_ID"=16741459929 OR
> "AG"."AD_GRP_ID"=16741460429 OR
> "AG"."AD_GRP_ID"=16741460929 OR
> "AG"."AD_GRP_ID"=16741461429 OR
> "AG"."AD_GRP_ID"=16741461929 OR
> "AG"."AD_GRP_ID"=16741462429 OR
> "AG"."AD_GRP_ID"=16741462929 OR
> "AG"."AD_GRP_ID"=16741463429 OR
> "AG"."AD_GRP_ID"=16741463929 OR
> "AG"."AD_GRP_ID"=16741464429 OR
> "AG"."AD_GRP_ID"=16741464929 OR
> "AG"."AD_GRP_ID"=16741465429 OR
> "AG"."AD_GRP_ID"=16741465929 OR
> "AG"."AD_GRP_ID"=16741466429 OR
> "AG"."AD_GRP_ID"=16741466929 OR
> "AG"."AD_GRP_ID"=16741467429 OR
> "AG"."AD_GRP_ID"=16741467929 OR
> "AG"."AD_GRP_ID"=16741468429 OR
> "AG"."AD_GRP_ID"=16741468929 OR
> "AG"."AD_GRP_ID"=16741469429 OR
> "AG"."AD_GRP_ID"=16741469929 OR
> "AG"."AD_GRP_ID"=16741470429 OR
> "AG"."AD_GRP_ID"=16741470929 OR
> "AG"."AD_GRP_ID"=16741471429 OR
> "AG"."AD_GRP_ID"=16741471929 OR
> "AG"."AD_GRP_ID"=16741472429 OR
> "AG"."AD_GRP_ID"=16741472929 OR
> "AG"."AD_GRP_ID"=16741473429 OR
> "AG"."AD_GRP_ID"=16741473929 OR
> "AG"."AD_GRP_ID"=16741474429 OR
> "AG"."AD_GRP_ID"=16741474929 OR
> "AG"."AD_GRP_ID"=16741475429 OR
> "AG"."AD_GRP_ID"=16741475929 OR
> "AG"."AD_GRP_ID"=16741476429 OR
> "AG"."AD_GRP_ID"=16741476929 OR
> "AG"."AD_GRP_ID"=16741477429 OR
> "AG"."AD_GRP_ID"=16741477929 OR
> "AG"."AD_GRP_ID"=16741478429 OR
> "AG"."AD_GRP_ID"=16741478929 OR
> "AG"."AD_GRP_ID"=16741479429 OR
> "AG"."AD_GRP_ID"=16741479929 OR
> "AG"."AD_GRP_ID"=16741480429 OR
> "AG"."AD_GRP_ID"=16741480929 OR
> "AG"."AD_GRP_ID"=16741481429 OR
> "AG"."AD_GRP_ID"=16741481929 OR
> "AG"."AD_GRP_ID"=16741482429 OR
> "AG"."AD_GRP_ID"=16741482929 OR
> "AG"."AD_GRP_ID"=16741483429 OR
> "AG"."AD_GRP_ID"=16741483929 OR
> "AG"."AD_GRP_ID"=16741484429 OR
> "AG"."AD_GRP_ID"=16741484929 OR
> "AG"."AD_GRP_ID"=16741485429 OR
> "AG"."AD_GRP_ID"=16741485929 OR
> "AG"."AD_GRP_ID"=16741486429 OR
> "AG"."AD_GRP_ID"=16741486929 OR
> "AG"."AD_GRP_ID"=16741487429 OR
> "AG"."AD_GRP_ID"=16741487929 OR
> "AG"."AD_GRP_ID"=16741488429 OR
> "AG"."AD_GRP_ID"=16741488929 OR
> "AG"."AD_GRP_ID"=16741489429 OR
> "AG"."AD_GRP_ID"=16741489929 OR
> "AG"."AD_GRP_ID"=16741490429 OR
> "AG"."AD_GRP_ID"=16741490929 OR
> "AG"."AD_GRP_ID"=16741491429 OR
> "AG"."AD_GRP_ID"=16741491929 OR
> "AG"."AD_GRP_ID"=16741492429 OR
> "AG"."AD_GRP_ID"=16741492929 OR
> "AG"."AD_GRP_ID"=16741493429 OR
> "AG"."AD_GRP_ID"=16741493929 OR
> "AG"."AD_GRP_ID"=16741494429 OR
> "AG"."AD_GRP_ID"=16741494929 OR
> "AG"."AD_GRP_ID"=16741495429 OR
> "AG"."AD_GRP_ID"=16741495929 OR
> "AG"."AD_GRP_ID"=16741496429 OR
> "AG"."AD_GRP_ID"=16741496929 OR
> "AG"."AD_GRP_ID"=16741497429 OR
> "AG"."AD_GRP_ID"=16741497929 OR
> "AG"."AD_GRP_ID"=16741498429 OR
> "AG"."AD_GRP_ID"=16741498929 OR
> "AG"."AD_GRP_ID"=16741499429 OR
> "AG"."AD_GRP_ID"=16741499929 OR
> "AG"."AD_GRP_ID"=16741500429 OR
> "AG"."AD_GRP_ID"=16741500929 OR
> "AG"."AD_GRP_ID"=16741501429 OR
> "AG"."AD_GRP_ID"=16741501929 OR
> "AG"."AD_GRP_ID"=16741502429 OR
> "AG"."AD_GRP_ID"=16741502929 O)
> 9 - access("T"."AD_GRP_ID"="AG"."AD_GRP_ID")
>
> Notice there is NO FILTER in Predicate information after step 9.
>
> Also the PARTITION HASH INLIST will change to PARTITION HASH ITERATOR.
>
> More than solution, I'm curious to know why in first place we were
> doing INDEX scan 788k.
>
> Oracle version: 10.2.0.4
> Linux zebra.mm.com 2.6.9-89.ELsmp #1 SMP Mon Apr 20 10:33:05 EDT 2009
> x86_64 x86_64 x86_64 GNU/Linux
> Both the tables TE_Y and AD_Y are hash partitioned by AD_GRP_ID. No of
> partitions 2048.
> Index info:
>
> SQL> _at_index
> Enter table name: TE_Y
>
> COLUMN_POSITION COLUMN_NAME INDEX_NAME
> --------------- ------------------------------
> ------------------------------
> 1 TERM_ID IDX_TE_Y1
> 1 ACCT_ID IDX_TE_Y3
> 2 URL_ID IDX_TE_Y3
> 1 AD_GRP_ID IDX_TE_Y_UQ1
> 2 CANON_SEARCH_TEXT IDX_TE_Y_UQ1
> 3 DEL_TMSTMP IDX_TE_Y_UQ1
>
> 6 rows selected.
>
> INDEX_NAME PAR UNIQUENES NUM_ROWS LEAF_BLOCKS
> DISTINCT_KEYS BLEVEL CLUSTERING_FACTOR
> ------------------------------ --- --------- ---------- -----------
> ------------- ---------- -----------------
> IDX_TE_Y1 YES NONUNIQUE 534607689 5011688 534603138 2
> 300072
> IDX_TE_Y3 YES NONUNIQUE 566383554 3026998 113693664
> 2 1512625
> IDX_TE_Y_UQ1 YES UNIQUE 534035467 5803793 534035467
> 2 803940
>
> SQL> _at_index
> Enter table name: AD_Y
>
> COLUMN_POSITION COLUMN_NAME INDEX_NAME
> --------------- ------------------------------
> ------------------------------
> 1 CRTV_OPT_ENABLED_LAST_UPD IDX_AD_Y2
> 1 LAST_CMPGN_OPT_UPD IDX_AD_Y3
> 1 ACCT_ID IDX_AD_Y4
> 1 STATUS IDX_AD_Y6
> 2 SEARCH_ENABLED IDX_AD_Y6
> 3 CRTV_OPT_ENABLED IDX_AD_Y6
> 4 NEXT_CRTV_OPT_CHK IDX_AD_Y6
> 5 AD_GRP_ID IDX_AD_Y6
> 6 CMPGN_ID IDX_AD_Y6
> 1 STATUS IDX_AD_Y7
> 2 SEARCH_ENABLED IDX_AD_Y7
> 3 CRTV_OPT_ENABLED_LAST_UPD IDX_AD_Y7
> 4 AD_GRP_ID IDX_AD_Y7
> 5 CMPGN_ID IDX_AD_Y7
> 1 CMPGN_ID IDX_AD_Y_UQ1
> 2 AD_GRP_NAME IDX_AD_Y_UQ1
> 3 DEL_TMSTMP IDX_AD_Y_UQ1
> 1 AD_GRP_ID PK_AD_Y
>
> 18 rows selected.
>
> INDEX_NAME PAR UNIQUENES NUM_ROWS LEAF_BLOCKS
> DISTINCT_KEYS BLEVEL CLUSTERING_FACTOR
> ------------------------------ --- --------- ---------- -----------
> ------------- ---------- -----------------
> IDX_AD_Y2 YES NONUNIQUE 24940544 102400 22863872
> 1 9419
> IDX_AD_Y3 NO NONUNIQUE 40959 2422 1718 2
> 39396
> IDX_AD_Y4 YES NONUNIQUE 32981324 134718 260963 2
> 206827
> IDX_AD_Y6 YES NONUNIQUE 31682560 2869248 31682560
> 2 12788
> IDX_AD_Y7 YES NONUNIQUE 31569920 301056 31569920
> 1 12259
> IDX_AD_Y_UQ1 YES UNIQUE 32983623 341326 32983623
> 2 57062
> PK_AD_Y YES UNIQUE 31569920 75776 31569920 1
> 11922
>
> 7 rows selected.
>
> I understand that I may have missed some important information that
> could be necessary to come to some sort of conclusion.
> Please ask the same. I will revert back at the earliest.
That is a big post :)
I know this doesn't answer your question, but the only thing I would note is that you are forcing the PK index to be used with your hint, which may not be the best way if it going to have read the vast majority of the table, anyway. I would be interested to see how you used the USE_CONCAT hint, as I don't see the SQL? That will force everything in the list to be aggregated in a bunch of UNION queries, which may be a more brute force effective way to resolve the query rather than OR'ing each value using the PK.
What happens if you don't hint it at all? Received on Wed Dec 23 2009 - 12:49:18 CST