| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: db buffer cache advisory clarification
Stephen,
It seems to me that the issue is the formatting of the output. The first three numbers in the report make more sense if you add in a leading 1,0 (or 1,00 for line 3). This makes the number sequence (sorry for the bad email formatting)
1,054,232,173 1,028,255,991 1,007,094,145 937,436,311
Which sounds reasonable.
Regards,
Daniel Fink
Stephen Anderson <st.anderson_at_gmail.com> wrote: Hi Jay,
This is 'normal expected' behaviour. I would always assume that as the pool grows larger we can store more blocks in the buffer, thereby negating many PIO's. My problem is in understanding how a reduction in the buffer pool would provide an opportunity to reduce PIO's by over 99%.
On 3/28/06, jayaraj rengarajan <jayaraj.rengarajan_at_gmail.com> wrote: Steve:
I am seeing it other way around. PIO is getting reduced with increased estimate size. Below detail from a production statspack report. We recycle the DB during weekend..
Jay
Buffer Pool Advisory for DB: WDSP Instance: WDSP End Snap: 4040
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 32 .1 3,970 33.20 1,601,419,883
D 64 .2 7,940 23.86 1,150,897,477
D 96 .3 11,910 15.87 765,448,716
D 128 .4 15,880 1.61 77,887,469
D 160 .5 19,850 1.33 63,917,781
D 192 .5 23,820 1.20 58,065,691
D 224 .6 27,790 1.13 54,488,453
D 256 .7 31,760 1.08 52,177,905
D 288 .8 35,730 1.05 50,519,436
D 320 .9 39,700 1.02 49,257,170
D 352 1.0 43,670 1.00 48,240,061
D 384 1.1 47,640 0.98 47,376,752
D 416 1.2 51,610 0.97 46,571,856
D 448 1.3 55,580 0.95 45,897,062
D 480 1.4 59,550 0.94 45,287,072
D 512 1.5 63,520 0.93 44,733,134
D 544 1.5 67,490 0.92 44,206,534
D 576 1.6 71,460 0.91 43,725,760
D 608 1.7 75,430 0.90 43,284,321
D 640 1.8 79,400 0.89 42,853,995
-------------------------------------------------------------
On 3/28/06, Stephen Anderson <st.anderson_at_gmail.com > wrote: Can anyone let me know why the advisory is saying i can so drastically reduce my PIO's by reducing my db_cache_size? I have looked around the web and have never seen an explanation for this. I also looked on metalink to see if it was a know bug. This was from a lvl 5 statspack 15 minute snap on 9.2.0.3 on Sun Solaris. The instance has been up for over 8 months. The results are the same no matter when I snap.
Buffer Pool Advisory for DB: MERLIN Instance: MERLIN End Snap: 15
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 16 .3 1,985 0.06 54,232,173
D 32 .5 3,970 0.03 28,255,991
D 48 .8 5,955 0.01 7,094,145
D 64 1.0 7,940 1.00 937,436,311
D 80 1.3 9,925 0.99 924,356,448
D 96 1.5 11,910 0.97 912,150,561
...
D 272 4.3 33,745 0.85 792,237,723
D 288 4.5 35,730 0.84 783,014,854
D 304 4.8 37,715 0.82 769,676,998
D 320 5.0 39,700 0.79 738,539,663
-------------------------------------------------------------
Regards,
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 28 2006 - 14:07:14 CST
 |
 |