Re: PARTITION HASH INLIST -- QUERY

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Wed, 23 Dec 2009 22:29:40 -0800 (PST)
Message-ID: <db1884f5-9061-4bf2-ac88-49b575bd58c7_at_15g2000prz.googlegroups.com>



On Dec 23, 11:49 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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
>
> ...
>
> read more »

Thanks Steve for your update. I could not have avoided big post, otherwise it would have been tough to explain the issue.

Here's the SQL with hint:

select /*+ gather_plan_statistics use_concat 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;

And Plan for the same, I have mentioned in my previous post:

Search for text
"If I use the hint USE_CONCAT, I get the following plan"

To your update:

" 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. "

Actually The PK index is of driving table (even if I remove this hint, issue still persists), so records from that table (not much i.e. 997) will drive the big table.
Driving table: CMPGN.AD_Y
Driven table: CMPGN.TE_Y
Driving Key: ad_grp_id

The table that is causing high LIO is CMPGN.TE_Y.

With USE_CONCAT hint, the plan changed from PARTITION HASH INLIST to PARTITION HASH ITERATOR for CMPGN.TE_Y table. That seemed to have issue resolved.

I want to understand, what actually caused the issue? Was it stats(my strongest doubt) -- CMPGN.TE_Y is huge and we have our package that collect stats (written by guys before us). Received on Thu Dec 24 2009 - 00:29:40 CST

Original text of this message