Re: buffer advisor
Date: Fri, 19 Sep 2014 21:12:14 +0200
Message-ID: <CAJ2-Qb_RCLw0THsoDc-8LQTMx+=NCx7m4tOAnYL7DpzgtqarKw_at_mail.gmail.com>
Yes the biggest problem is it look for data since the instance started.
This is the database we increased db cache a few days ago, data before and after. Same database, same workload.
when cache is 1856M:
Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 176 .1 21,769 2.9 121,197,660,943 D 352 .2 43,538 1.9 77,922,496,445 D 528 .3 65,307 1.3 53,612,976,321 D 704 .4 87,076 1.1 48,031,960,873 D 880 .5 108,845 1.1 46,029,874,156 D 1,056 .6 130,614 1.1 44,569,756,075 D 1,232 .7 152,383 1.0 43,712,140,268 D 1,408 .8 174,152 1.0 43,089,579,069 D 1,584 .9 195,921 1.0 42,527,214,431 D 1,760 .9 217,690 1.0 42,051,993,063 D 1,856 1.0 229,564 1.0 41,821,430,364 D 1,936 1.0 239,459 1.0 41,629,213,204 D 2,112 1.1 261,228 1.0 41,254,099,176 D 2,288 1.2 282,997 1.0 40,912,390,848 D 2,464 1.3 304,766 1.0 40,600,923,889 D 2,640 1.4 326,535 1.0 40,315,187,239 D 2,816 1.5 348,304 1.0 40,049,795,229 D 2,992 1.6 370,073 1.0 39,799,479,629 D 3,168 1.7 391,842 0.9 39,564,216,944 D 3,344 1.8 413,611 0.9 39,339,944,819 D 3,520 1.9 435,380 0.9 38,925,250,582 when cache is 11680M: Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 1,168 .1 144,467 2.9 107,361,779 D 2,336 .2 288,934 2.3 87,297,968 D 3,504 .3 433,401 1.9 69,030,111 D 4,672 .4 577,868 1.6 60,553,871 D 5,840 .5 722,335 1.4 53,904,798 D 7,008 .6 866,802 1.3 48,103,583 D 8,176 .7 1,011,269 1.2 44,187,918 D 9,344 .8 1,155,736 1.1 41,096,739 D 10,512 .9 1,300,203 1.1 39,103,791 D 11,680 1.0 1,444,670 1.0 37,279,957 D 11,712 1.0 1,448,628 1.0 37,239,087 D 12,848 1.1 1,589,137 1.0 35,855,521 D 14,016 1.2 1,733,604 0.9 34,560,730 D 15,184 1.3 1,878,071 0.9 32,986,834 D 16,352 1.4 2,022,538 0.8 30,907,059 D 17,520 1.5 2,167,005 0.8 29,720,858 D 18,688 1.6 2,311,472 0.8 28,602,200 D 19,856 1.7 2,455,939 0.7 27,440,080 D 21,024 1.8 2,600,406 0.7 24,843,319 D 22,192 1.9 2,744,873 0.6 23,712,251 D 23,360 2.0 2,889,340 0.5 20,340,357
If we look first advisory one would say why we need to increased the db cache if est phy read factor data is used, but if we look the advisory in the new cache size one would say if we can put twice more cache it would even be better! And this is because the advisory does not use delta statistics.
So IMHO advisor is not very useful
Thanks
On Fri, Sep 12, 2014 at 9: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.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 19 2014 - 21:12:14 CEST