PARTITION HASH INLIST -- QUERY
Ind-dba
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> @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> @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.
Steve Howard
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?
Ind-dba
>
> 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).
Jonathan Lewis
"Ind-dba" <oracl...@googlemail.com> wrote in message
news:378b724d-ca44-46f2...@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 Lewis
http://jonathanlewis.wordpress.com
Ind-dba
wrote:
> "Ind-dba" <oraclear...@googlemail.com> wrote in message
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 @ 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)
Jonathan Lewis
It looks like it might be an accidental side-effect (i.e. bug).
The use_concat hint isn't actually working - or you'd see a
CONCATENATION operator - but it seems to have stopped
transitive closure from taking place. (Your in-list shows up on
only one of the tables).
With transitive closure in place I think Oracle has used the list
of values to access each partition in turn in its "partition selection"
phase on the second table, then found that the join predicate works
for only one partition. The excessive selection of partitions looks
like the bug which use_concat has accidentally blocked.
So, with transitive closure you get starts = number of rows in first
table * number of partitions referenced by inlist.
With transitive closue block you get starts = rows selected from
first table.
Jonathan Lewis
news:X4WdnWZNdY-Byq7W...@bt.com...
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
Ind-dba
wrote:
> "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk> wrote in message
>
> news:X4WdnWZNdY-Byq7W...@bt.com...
>
>
>
>
>
>
>
>
> > 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
Ind-dba
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?
ddf
>
> - Show quoted text -
Jonathan covers your first question in his book 'Cost-Based Oracle
Fundamentals'; basically if you write a query like this:
select count(*)
from t1, t2
where t1.x = 5
and t2.x = t1.x;
Oracle can produce a query plan that looks like this:
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=303 Card=1
Bytes=6)
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN (CARTESIAN) (Cost=303 Card=10000 Bytes=60000)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=3 Card=100
Bytes=300)
4 2 SORT (JOIN) (Cost=300 Card=100 Bytes=300)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=100
Bytes=300)
The merge join cartesian appears and in the predicate information you
see:
3 - filter("T1.X" = 5)
5 - filter("T2.X" = 5)
but no predicate joining the two tables. Oracle has deduced that
since t1.x = 5 and t2.x = t1.x that (rightly so) t2.x = 5 and has
rewritten the query to reflect that revelation (which eliminates the
join condition). The act of making the leap from this:
where t1.x = 5 and t2.x = t1.x
to
where t1.x = 5 and t2.x = 5
is called transitive closure. Jonathan discusses the sometimes
'interesting' side effects of this behaviour in this same book. His
comment about the in-lists proves that transitive closure does not
happen because, if it did, you'd see two in-lists, not one, in the
predicate section.
Jonathan will have to respond to the rest of your questions and I
await his responses.
David Fitzjarrell
Ind-dba
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?
Jonathan Lewis
"Ind-dba" <oracl...@googlemail.com> wrote in message
news:c18c2018-d228-40f4...@15g2000prz.googlegroups.com...
> 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?
I am assuming that it is a bug because the behaviour is clearly
unreasonable. Really I shouldn't call it a bug until it's been
acknowledged as such by Oracle. Raise an SR
In another post you asked how to count the number of partitions
identified by the IN-list. There's a clue in another of my blog posts
http://jonathanlewis.wordpress.com/2009/11/25/counting/
select count(distinct dbms_mview.pmarker(rowid)
from {your_table}
where {youe_column} in ({your list of values})
;
Event 10195 is about generating predicates from constraints
using transitive closure (at least in the 10.2.0.1 oraus.msg,
so ity won't help.
I think if you could block transitive closure by changing the
join predicate to
table2.colx = table1.coly + 0
Ind-dba
wrote:
> "Ind-dba" <oraclear...@googlemail.com> wrote in message
Awesome results!
I'm able to block the transitive closures by the trick mentioned.
I'm yet to try the count of partition trick that you mentioned in your
blog.
But -- Its satisfying to have an answer of a complex issue :)
Many thanks to you !!
Merry X'mas and new year to all!!!
Regards,
Sachin