Re: Why isn't Oracle Using My Index

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 22 Dec 2006 11:20:16 -0600
Message-ID: <458C13D0.1050507@jcpenney.com>

('binary' encoding is not supported, stored as-is)

Hi Dan

Yes, I know and that's the first thing we tried ;-) In fact, William changed the whole statement, still no 10053 trace. Same 10053 trace worked correctly for me in 10g, even after setting nls_sort and nls_comp parameters.

Then, I thought, may be somehow he had index stats alone, but no table stats [ I have seen use of RBO in these cases ]. That was not the case either. Optimizer_mode was set to choose and definitely CBO must have been used.

Thanks
Riyaj

Daniel W. Fink wrote:
> 10053 traces are only generated when the statement is hard parsed. I
> use comments, change case, etc. to make sure the statement is
> considered 'new' by the optimizer.
>
>
> Riyaj Shamsudeen wrote:
>> Hi All
>>
>> Due to some issues with mail server DNS entries, my posting to
>> the list bounces. I am trying other options ;-)
>>
>> So, I was working with William off-line and here are the highlights:
>>
>> 1. I thought, autotrace had dumped incorrect statistics, asked
>> for 10046/10053 trace files.
>> 2. 10053 trace files were never generated for this SQL. Still,
>> don't understand, why ? I think, this must be a bug with 9i. I can't
>> test it.
>> 3. explain plan filter predicates clearly pointed out the issue.
>>
>> Of course, metalink documents mentioned here and reference books
>> are showing the issues. I recreated the issue before telling William.
>> And Wolfgang, as usual, has done a wonderful job of recreating the
>> issue.
>>
>

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 22 2006 - 11:20:16 CST