| |
 |
|
Oracle Tips by Burleson |
Buffer Pool Statistics
The next section of the AWRRPTreport
deals with the db buffer cache
area. If a system utilizes more
than one cache area, such as multiple blocksizes, RECYCLE, and KEEP,
there will be a section for that information that pertains to the
specialized areas. If the multiple cache features are not being used,
only the default cache will be reported. The following listing shows
an example from the report:
Buffer Pool
Statistics DB/Inst: SSD/ssd2 Snaps: 3-4
-> Standard
block size Pools D: default, K: keep, R: recycle
-> Default Pools
for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Writ Buffer
Number of
Pool Buffer Physical Physical Buff Comp Busy
P Buffers
Hit% Gets Reads Writes Wait Wait Waits
- ----------
---- ---------- --------- --------- ---- ---- --------
D 94,080
100 6,137,790 5,495 46,182 0 0 1,860
-----------------------------------------------
Instance
Recovery Stats DB/Inst: SSD/ssd2 Snaps: 3-4
-> B:
Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log
Ckpt
MTTR MTTR Recovery
Actual Target Size Timeout Interval
(s) (s)
Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- -----
---------- --------- --------- ---------- --------- ------------
B 0
19 3734 9620 18432 18432 27168
E 0
17 324 2113 18432 18432 29421
-------------------------------------------------------------
Buffer Pool
Advisory DB/Inst: SSD/ssd2 Snap: 4
-> 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
76 .1 9,310 3.81 119,805
D
152 .2 18,620 2.94 92,654
D
228 .3 27,930 2.58 81,054
D
304 .4 37,240 2.42 76,033
D
380 .5 46,550 2.35 73,802
D
456 .6 55,860 2.29 71,892
D
532 .7 65,170 2.22 69,867
D
608 .8 74,480 1.22 38,368
D
684 .9 83,790 1.02 31,944
D
760 1.0 93,100 1.00 31,500
D
768 1.0 94,080 1.00 31,463
D
836 1.1 102,410 0.99 31,056
D
912 1.2 111,720 0.98 30,675
D
988 1.3 121,030 0.97 30,573
D
1,064 1.4 130,340 0.97 30,513
D
1,140 1.5 139,650 0.97 30,453
D
1,216 1.6 148,960 0.97 30,375
D
1,292 1.7 158,270 0.96 30,268
D
1,368 1.8 167,580 0.96 30,181
D
1,444 1.9 176,890 0.96 30,123
D
1,520 2.0 186,200 0.95 30,009
-------------------------------------------------------------
This database has a 100% hit ratio and the advisor
report indicates that the physical memory size would have to be nearly
doubled to make only a 5% change in I/O. This is expected from a fully
cached environment. Physical reads and writes are present in this
section since this is an OLTP environment being modeled. Many
transactions are doing UPDATE and DELETE type activity, as well as
INSERTs which cause cache replacement and flushes. There are also
buffer busy waits,
which were probably caused by enqueueactivity associated
with transactions needing the same rows.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
