Re: buffer advisor
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 19 Sep 2014 21:12:14 +0200
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@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.
>
>
Other related posts:
