Strange 9.2.0.5 Optimizer Decisions.
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Aug 2004 09:05:45 -0400
All,
First, Oracle 9.2.0.5 on Sun.
I have a query against our data warehouse that, when we change a literal
value for the year in the where clause, generates two different explain
plans. The query below shows where we change this value. When we quwery
against the year 2003, we get the first explain plan below. When we query
against the year 2004, we get the second explain plan. The difference is
the use of one index or the other. The table is question (Ledger_Fact) is
partitioned by year (Tran_Date_Key which is a valued of yyyymmdd). The
table has four populated partitions for the 2003 year, but only threee
populated partitions for the 2004 year - the fourth quarter has no records
in it yet. Statistics are current for all tables and indexes.
The problem is that the 2003 query completes in about 25 minutes, while the
2004 query was still running after more than an hour.
My guess is that the optimizer is taking a separate path because the 4th
2004 partition has no records in it. Does this make sense to anyone?
My apologies for the format of the query and the length of the question.
The query is generated by Cognos - so we have little or no control over it.
Thanks for any help.
Tom Mercadante
Oracle Certified Professional
Query:
select count(distinct T1."CLAIM_SSN") "c1",
DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98' ,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION" ,DECODE(T2."STATE_FIPS",'36',
T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
"c2", '2003' "c3", case when
DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98' ,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
not in ('98', '99') then
DECODE(T3."LMA_FIPS",T2."COUNTY_FIPS",T2."COUNTY_DESC" ) else NULL end
"c4",
SUBSTR(T4."MONTH",1 ,3) "c5", '2003' "c6",
DECODE(DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98' ,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99'))),
'98','INVALID/UNKNOWN COUNTIES'
,DECODE(DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98' ,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99'))),
'99','OUT-OF-STATE RESIDENTS' ,T3."LMA_REGION")) "c7", '2003' "c8"
from
"DWOWN"."PERSON_DIM" T2,
"DWOWN"."LABOR_MARKET_AREAS" T3, "DWOWN"."MIDWEEK_DATE_REF" T4,
"DWOWN"."LEDGER_FACT" T1 LEFT OUTER JOIN "DWOWN"."CLAIM_FACT" T5 on
T1."CLAIM_SSN"=T5."SSN" and
T1."CLAIM_EFF_DT"=T5."EFDT"
where
T5."PERSON_KEY"=T2."PERSON_KEY" and
T3."LMA_FIPS"=T2."COUNTY_FIPS" and
----------------------------------- LITERAL VALUES CHANGED BELOW
T4."YEAR"='2004' and
T1."TRAN_DATE_KEY" between 20040101 and 99999999
----------------------------------- LITERAL VALUES CHANGED ABOVE
T1."TRAN_DATE_KEY" between T4."REF_BEG_TRANS" and T4."REF_END_TRANS" and
T1."ER_NUMBER" not in ('0000471020', '0000471021', '0000471022',
'0000471023') and
T1."AMOUNT">0 and T1."TRAN_CODE" in ('43', '47', '50') and
T4."REF_STAT_WEEK"=T1."STAT_WEEK" and
group by DECODE(T2."STATE_FIPS",NULL,
DECODE(T2."COUNTY_FIPS",NULL,'98' ,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99'))),
DECODE(DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98'
,DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION" ,
DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION" ,
DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION"
,NULL,T3."LMA_REGION",'99'))),'98','INVALID/UNKNOWN COUNTIES' ,
DECODE(DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98'
,DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION" ,
DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION" ,
DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION"
,NULL,T3."LMA_REGION",'99'))),'99','OUT-OF-STATE RESIDENTS' ,
T3."LMA_REGION")), case when
DECODE(T2."STATE_FIPS",NULL,DECODE(T2."COUNTY_FIPS",NULL,'98' ,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
,
DECODE(T2."STATE_FIPS",NULL,T3."LMA_REGION"
,DECODE(T2."STATE_FIPS",'36',T3."LMA_REGION" ,NULL,T3."LMA_REGION",'99')))
not in ('98', '99') then
DECODE(T3."LMA_FIPS",T2."COUNTY_FIPS",T2."COUNTY_DESC" ) else NULL end ,
SUBSTR(T4."MONTH",1 ,3) order by "c2" asc, "c7" asc, "c4" asc, "c5" asc
;
Year 2003 Explain Plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
------------------------
----------------------------------------------------------------------------
------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT |
| 1 | 136 | 1801 (2)| | |
| 1 | SORT GROUP BY |
| 1 | 136 | 1801 (2)| | |
|* 2 | HASH JOIN |
| 1 | 136 | 1800 (2)| | |
| 3 | NESTED LOOPS |
| 3 | 360 | 1797 (2)| | |
| 4 | NESTED LOOPS OUTER |
| 3 | 297 | 1794 (2)| | |
| 5 | NESTED LOOPS |
| 3 | 225 | 851 (2)| | |
|* 6 | TABLE ACCESS FULL |
MIDWEEK_DATE_REF | 3 | 78 | 3 (34)| | |
| 7 | PARTITION RANGE ITERATOR |
| | | | KEY | KEY |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | LEDGER_FACT
| 1 | 49 | 851 (2)| KEY |
| 9 | BITMAP CONVERSION TO ROWIDS |
| | | | | |
| 10 | BITMAP AND |
| | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE |
BMX_LEDGER_STAT_WEEK | | | | KEY | |
| 12 | BITMAP MERGE |
| | | | | |
|* 13 | BITMAP INDEX RANGE SCAN |
BMX_LEDGER_TRAN_DATE_KEY | | | | KEY |
| 14 | PARTITION RANGE ITERATOR |
| | | | KEY | KEY |
|* 15 | TABLE ACCESS BY LOCAL INDEX ROWID | CLAIM_FACT
| 1 | 24 | 1794 (2)| KEY |
| 16 | BITMAP CONVERSION TO ROWIDS |
| | | | | |
|* 17 | BITMAP INDEX SINGLE VALUE | BMX_CLAIM_SSN
| | | | KEY | KEY |
| 18 | TABLE ACCESS BY GLOBAL INDEX ROWID | PERSON_DIM
| 1 | 21 | 2 (50)| ROWID | RO
|* 19 | INDEX UNIQUE SCAN |
PK_PERSON_DIM | 1 | | | | |
| 20 | TABLE ACCESS FULL |
LABOR_MARKET_AREAS | 62 | 992 | 3 (34)| | |
----------------------------------------------------------------------------
------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."LMA_FIPS"="T2"."COUNTY_FIPS")
6 - filter("T4"."REF_END_TRANS">=20030101 AND "T4"."YEAR"='2003' AND
"T4"."REF_BEG_TRANS"<=999999
8 - filter("T1"."AMOUNT">0 AND ("T1"."TRAN_CODE"='43' OR
"T1"."TRAN_CODE"='47' OR "T1"."TRAN_CODE
"T1"."ER_NUMBER"<>'0000471020' AND
"T1"."ER_NUMBER"<>'0000471021' AND "T1"."ER_NUMBER"<>'0000
"T1"."ER_NUMBER"<>'0000471023')
11 - access("T4"."REF_STAT_WEEK"="T1"."STAT_WEEK")
13 - access("T1"."TRAN_DATE_KEY">="T4"."REF_BEG_TRANS" AND
"T1"."TRAN_DATE_KEY"<="T4"."REF_END_TRA
filter("T1"."TRAN_DATE_KEY"<=99999999 AND
"T1"."TRAN_DATE_KEY">=20030101)
15 - filter("T1"."CLAIM_EFF_DT"="T5"."EFDT"(+))
17 - access("T1"."CLAIM_SSN"="T5"."SSN"(+))
19 - access("T5"."PERSON_KEY"="T2"."PERSON_KEY")
41 rows selected.
Year 2004 Explain Plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
------------------------
----------------------------------------------------------------------------
------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Pstart|
----------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT |
| 1 | 136 | 1776 (10)| |
| 1 | SORT GROUP BY |
| 1 | 136 | 1776 (10)| |
| 2 | SORT GROUP BY |
| 1 | 136 | 1776 (10)| |
| 3 | SORT GROUP BY |
| 1 | 136 | 1776 (10)| |
|* 4 | HASH JOIN |
| 1 | 136 | 1775 (10)| |
| 5 | NESTED LOOPS |
| 1 | 120 | 1768 (10)| |
| 6 | NESTED LOOPS OUTER |
| 1 | 99 | 1767 (10)| |
| 7 | NESTED LOOPS |
| 1 | 75 | 923 (2)| |
|* 8 | TABLE ACCESS FULL |
MIDWEEK_DATE_REF | 2 | 52 | 3 (34)| |
| 9 | PARTITION RANGE ITERATOR |
| | | | KEY |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | LEDGER_FACT
| 1 | 49 | 923 (2)| KEY |
| 11 | BITMAP CONVERSION TO ROWIDS |
| | | | |
| 12 | BITMAP AND |
| | | | |
|* 13 | BITMAP INDEX SINGLE VALUE |
BMX_LEDGER_STAT_WEEK | | | | KEY |
| 14 | BITMAP OR |
| | | | |
|* 15 | BITMAP INDEX SINGLE VALUE |
BMX_LEDGER_TRANCODE | | | | KEY
|* 16 | BITMAP INDEX SINGLE VALUE |
BMX_LEDGER_TRANCODE | | | | KEY
|* 17 | BITMAP INDEX SINGLE VALUE |
BMX_LEDGER_TRANCODE | | | | KEY
| 18 | BITMAP MERGE |
| | | | |
|* 19 | BITMAP INDEX RANGE SCAN |
BMX_LEDGER_TRAN_DATE_KEY | | | | KEY |
| 20 | PARTITION RANGE ITERATOR |
| | | | KEY |
|* 21 | TABLE ACCESS FULL |
CLAIM_FACT | 1 | 24 | 845 (19)
| 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | PERSON_DIM
| 1 | 21 | 2 (50)|
|* 23 | INDEX UNIQUE SCAN |
PK_PERSON_DIM | 1 | | |
|
| 24 | TABLE ACCESS FULL |
LABOR_MARKET_AREAS | 62 | 992 | 3 (34)| |
----------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T3"."LMA_FIPS"="T2"."COUNTY_FIPS")
8 - filter("T4"."REF_END_TRANS">=20040101 AND "T4"."YEAR"='2004' AND
"T4"."REF_BEG_TRANS"<=999999
10 - filter("T1"."AMOUNT">0 AND ("T1"."TRAN_CODE"='43' OR
"T1"."TRAN_CODE"='47' OR "T1"."TRAN_CODE
"T1"."ER_NUMBER"<>'0000471020' AND
"T1"."ER_NUMBER"<>'0000471021' AND "T1"."ER_NUMBER"<>'0000
"T1"."ER_NUMBER"<>'0000471023')
13 - access("T4"."REF_STAT_WEEK"="T1"."STAT_WEEK")
15 - access("T1"."TRAN_CODE"='43')
16 - access("T1"."TRAN_CODE"='47')
17 - access("T1"."TRAN_CODE"='50')
19 - access("T1"."TRAN_DATE_KEY">="T4"."REF_BEG_TRANS" AND
"T1"."TRAN_DATE_KEY"<="T4"."REF_END_TRA
filter("T1"."TRAN_DATE_KEY"<=99999999 AND
"T1"."TRAN_DATE_KEY">=20040101)
21 - filter("T1"."CLAIM_EFF_DT"="T5"."EFDT"(+) AND
"T1"."CLAIM_SSN"="T5"."SSN"(+))
23 - access("T5"."PERSON_KEY"="T2"."PERSON_KEY")
47 rows selected.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
