Strange 9.2.0.5 Optimizer Decisions.

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Wed, 11 Aug 2004 09:05:45 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FED11F2C05A@exchsen0a1ma>

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

|   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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 11 2004 - 08:02:05 CDT