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

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 2 Mar 2004 21:19:03 +0200
Message-ID: <04c301c4008b$39b083a0$b8fb23d5@porgand>

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_at_centrexcc.com> To: <oracle-l_at_freelists.org> 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_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
> -----------------------------------------------------------------
>

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 - 13:16:00 CST