RE: query slow in 9i, but not slow in 8i

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 02 Mar 2004 09:22:46 -0700
Message-Id: <6.0.3.0.2.20040302091809.02bba928@pop.centrexcc.com>

Try (in test first) setting the density on that column to something in the neighbourhood of 1.0e-5 or 1.0e-6 and see if the optimizer doesn't use the index now.
Of course, you only can do that successfully if there are no queries that have a predicate = 'Y' on that column. But if you have, you'll be in trouble in 9i with bind variable peeking as well.

At 08:39 AM 3/2/2004, you wrote:
>My opinion is that I'm not at all good at coming up with tangible costs
>associated with poor design. So now we have a program that checks a 2M-row
>table every 15 minutes to see if there are any new 'N' values in a Y/N
>column. And since we're on 8i using CS=F to compensate for a lack of binds
>(my ignorance and lack of emphasis in Oracle classes are to blame for that
>one), we get an FTS on that table every 15 minutes. You can imagine what's
>happening to our buffer cache. I'm hoping to compensate once again in 9i by
>allowing the optimizer to peek at the binds while providing a histogram on
>that indexed column, so that it'll use it to get the 5-10 rows out of the
>2M.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

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 Tue Mar 02 2004 - 10:21:03 CST