RE: buffer advisor

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 12 Sep 2014 09:53:32 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E0EABE_at_exmbx05.thus.corp>

Sorry, Niall, I just picked the top of the heap to reply to and chopped the bottom off it without checking whether it was one of the ones that had moved the subject on.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: Niall Litchfield [niall.litchfield_at_gmail.com] Sent: 12 September 2014 10:51
To: Jonathan Lewis
Cc: andrew.kerber_at_gmail.com; ORACLE-L; Mark Powell; Mark W. Farnham Subject: Re: buffer advisor

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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Niall Litchfield [niall.litchfield_at_gmail.com<mailto:niall.litchfield_at_gmail.com>] Sent: 12 September 2014 08:01
To: andrew.kerber_at_gmail.com<mailto: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:53:32 CEST