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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 02 Mar 2004 09:22:46 -0700

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004