Re: db buffer cache advisory clarification
- From: Daniel Fink <danielwfink@xxxxxxxxx>
- To: st.anderson@xxxxxxxxx, jayaraj rengarajan <jayaraj.rengarajan@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 28 Mar 2006 12:07:14 -0800 (PST)
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
924,356,448
912,150,561
Which sounds reasonable.
Regards,
Daniel Fink
Stephen Anderson <st.anderson@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx > 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,
Steve Anderson
Other related posts:
