RE: Strange 9.2.0.5 Optimizer Decisions.

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Thu, 12 Aug 2004 08:51:52 -0700 (PDT)
Message-ID: <20040812155152.71589.qmail@web41503.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

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!?
New and Improved Yahoo! Mail - Send 10MB messages! 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 - 10:47:34 CDT