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

  • From: Tanel Põder <tanel.poder.003@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Mar 2004 21:19:03 +0200

Btw, it's possible to disable bind variable peeking by setting
_optim_peek_user_binds parameter to false, but I don't think it would help
in this scenario.

Tanel.

----- Original Message ----- 
From: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 02, 2004 6:22 PM
Subject: RE: query slow in 9i, but not slow in 8i


> 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
> -----------------------------------------------------------------
>


----------------------------------------------------------------
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