Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Strange 9.2.0.5 Optimizer Decisions.

Re: Strange 9.2.0.5 Optimizer Decisions.

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Thu, 12 Aug 2004 03:39:41 -0700 (PDT)
Message-ID: <20040812103941.77658.qmail@web41507.mail.yahoo.com>


Tom:

Thank you for your traces and 10053 outputs. The major difference between those two plans are the table access (CLAIM_FACT). In 2003 this table is accessed by Index and 2004 it is accessed by FTS. From the 10053 trace of 2004, the table access cost is expensive than the index cost, but still it is using the table. I am failing to understand this and suspect this could be a bug unless I miss something obvious.

**BEGIN TRACE** SINGLE TABLE ACCESS PATH
  TABLE: CLAIM_FACT ORIG CDN: 5214450 ROUNDED CDN: 5214450 CMPTD CDN: 5214450
  Access path: tsc Resc: 27875 Resp: 27875    

  Access path: index (no sta/stp keys)

      Index: BMX_CLAIM_SSN
  TABLE: CLAIM_FACT
      RSC_CPU: 1147794333 RSC_IO: 10824   IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00   

**END TRACE*** Now coming to the Normal (!) tuning process, you may consider creating a composite index on CLAIM_EFF_DT and CLAIM_SSN and check the response time. Irrespective of the above, I am still interested in seeing the 10053 trace for the value 2003.

Thanks
Gopal

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



Have a nice day !!

Best Regards,
K Gopalakrishnan,
Co-Author: Oracle Wait Interface: Oracle Press 2004. http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/                 

Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail

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 Thu Aug 12 2004 - 05:45:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US