Re: buffer advisor

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 12 Sep 2014 10:51:16 +0100
Message-ID: <CABe10sb727VhqMcQFwR_pGfPbFJJze8di4NDJLKNr4HUG6oAsg_at_mail.gmail.com>

Although the subject line reads buffer advisor, I was commenting on the suggestion in the thread that using CURSOR_SHARING=FORCE might be a useful tactic to deal with the tendency of the shared pool to grow even when SQL isn't going to be reusable. I specifically wasn't commenting on the buffer advisor because I can't recall the last time I used it.

Some examples of what I mean't now I'm not on a train.

SPM - SQLBASELINES (SPM) NOT WORKING WITH CURSOR_SHARING=FORCE AND ORDER BY
XMLDB - WRONG RESULTS WITH EXISTSNODE AND CURSOR_SHARING=FORCE DB LINK - ORA-00979 REPORTED USING GROUP BY TO_CHAR AND CURSOR_SHARING=FORCE
Pro*Cobol - CURSOR_SHARING=FORCE CAUSES QUERIES TO FAIL WITH ORA-907

these all affect "current" versions. Over the years I've just seen too many issues as a side effect of CURSOR_SHARING=FORCE to be happy with it other than as a temporary fix.

On Fri, Sep 12, 2014 at 8:14 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> I think the biggest problem with the buffer advisor is that it's
> reporting stats since the instance started up, NOT for the interval. A
> piece of advice that "on average" looks okay for the instance might be bad
> for the critical period you're examining. I am also a little suspicious
> about how smart the advisor can be regarding tablescans and "small tables"
> - for example, if you increase the buffer then a large table can become a
> medium table (without changing size) and end up being buffered more
> aggressively than you'd like, kicking more useful data out of the cache.
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Niall Litchfield [niall.litchfield_at_gmail.com]
> *Sent:* 12 September 2014 08:01
> *To:* andrew.kerber_at_gmail.com
> *Cc:* ORACLE-L; Mark Powell; Mark W. Farnham
> *Subject:* Re: buffer advisor
>
> The feature also has a long, long history of not playing nicely with
> other Oracle features. A search of the MOS bug database will reveal a
> number of reasons that might preclude it in your environment.
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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

Received on Fri Sep 12 2014 - 11:51:16 CEST