RE: Why isn't Oracle Using My Index

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Thu, 21 Dec 2006 14:32:18 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08989@EXCNYSM0A1AJ.nysemail.nyenet>

Bill,

What we are all getting at is this.

You need to debug the functionality, not a specific sql. End-to-end debugging of a canned (non changeable) 3rd party application usually involves more than just one sql. I'm not sure how you focused in on this one sql as being the culprit. Maybe you can share that discovery process with us.

All I'm saying is that it may be something else. And tracing the application or performing a 10046 trace as Jared suggests while the application is running is a very useful exercise to determine what exactly the application is sending to Oracle.

And I'm glad Wolfgang has time to respond to you. He is the best for debugging why the optimizer is doing what it is doing.

Tom

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Thursday, December 21, 2006 2:20 PM To: Mercadante, Thomas F (LABOR)
Cc: wjwagman_at_ucdavis.edu; oracle-l_at_freelists.org Subject: Re: Why isn't Oracle Using My Index

On 12/21/06, Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us> wrote:

Bill,

When you run the query, how fast is the response?

Just to add a bit to Tom's response:

Why not do a 10046 trace on the query?

Run it both with and without the index hint and compare the results.

An excellent method for comparing the 2 would be to use runstats - http://asktom.oracle.com/tkyte/runstats.html

HTH

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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

Received on Thu Dec 21 2006 - 13:32:18 CST