Re: buffer advisor
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 12 Sep 2014 10:51:16 +0100
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@xxxxxxxxxxxxxxxxxx
> 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
> @jloracle
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Niall Litchfield [niall.litchfield@xxxxxxxxx]
> *Sent:* 12 September 2014 08:01
> *To:* andrew.kerber@xxxxxxxxx
> *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
Other related posts:
