Thanks for the question, Gunjan.
Asked: February 07, 2003 - 2:55 pm UTC
Last updated: February 08, 2003 - 11:33 am UTC
Version: 8.1.7
Viewed 1000+ times
You Asked
Hi Tom, Your site is great and you are doing great service to DBA community.
Now, Give the following information (database Size & Statspack)
, What is the recommended size of db_block_buffers.
Also, I would greatly appreciate your insite on the following situation:
a. If the Data in the Database is 100M and
if I make db_block_buffers*db_block_size=110M
Does it mean I would be getting 100% buffer Hit Ration
1. Database Size
Tablespace Total Total % Contg. Chunks
Name Size Free Free Free Free
-------------------- ----------- ----------- ---- ----------- -----------
CORP_DATA 10 10 96 10 1
CORP_INDX 10 10 96 9 2
MGR_DATA 100 42 42 41 2
MGR_INDX 25 10 39 6 24
MGR_LOB_DATA 200 115 57 115 1
MGR_LOB_INDX 200 200 100 200 1
PERFSTAT 250 202 81 202 1
PFM_DATA 50 16 32 16 1
PFM_INDX 15 4 28 2 4
RBS 50 30 59 30 1
SYSTEM 150 70 47 70 2
TEMP 20 20 100 20 1
TOOLS 10 7 74 7 2
USERS 10 8 82 8 1
WEB_DATA 50 18 36 18 1
WEB_INDX 10 5 45 2 6
---------------------------------------------------------------------------
1,160 766 66
--------------------------------------------------------------------------
2. Statspack Report
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 361 05-Feb-03 09:00:06 53
End Snap: 362 05-Feb-03 10:00:05 53
Elapsed: 59.98 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 5000 log_buffer: 8192
db_block_size: 8192 shared_pool_size: 25600000
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 636.09 1,705.89
Logical reads: 1,724.92 4,625.93
Block changes: 3.83 10.27
Physical reads: 1,355.48 3,635.16
Physical writes: 0.42 1.12
User calls: 18.99 50.93
Parses: 4.84 12.99
Hard parses: 0.99 2.64
Sorts: 2.79 7.48
Logons: 0.09 0.25
Executes: 5.21 13.96
Transactions: 0.37
% Blocks changed per Read: 0.22 Recursive Call %: 51.28
Rollback per transaction %: 0.00 Rows per Sort: 38.43
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.95 Redo NoWait %: 99.97
Buffer Hit %: 21.42 In-memory Sort %: 99.82
Library Hit %: 89.40 Soft Parse %: 79.64
Execute to Parse %: 6.98 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 55.54 % Non-Parse CPU: 100.00
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 71.03 72.63
% SQL with executions>1: 91.35 85.03
% Memory for SQL w/exec>1: 69.20 65.14
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 858,262 54,360 67.63
log file sync 1,400 15,384 19.14
db file sequential read 290,377 4,686 5.83
log file parallel write 1,458 2,137 2.66
buffer busy waits 2,954 1,103 1.37
-------------------------------------------------------------
Thanks in Advance
Gunjan
and Tom said...
read the paper on www.hotsos.com:
Are you still using cache hit ratios?
as for (a) the answer is: yes, no, maybe, so what? All of those are valid answers to that. You may or may not get 100% cache hit over time -- it depends on thousands of variables but at the end of the day -- the real answer is "yawn, so what". Your goal is not to achieve 100% cache hit (i can do that easily -- just through into a hard loop a query against dual. eventually you'll have 100% cache hit -- but so what?)
Now, you have a 39meg buffer cache which I would put at the "small end if the spectrum" as far as size goes.
You also have a 21% buffer hit, which is pretty small. You would probably benefit from increasing the buffer cache -- but you still want to look at your TOP SQL and make sure they are doing as little IO (logical and physical) as possible.
the best way to fix an io problem? reduce io (too many people try to fix the symptoms - the wait for IO by moving files, striping whatnot. they ignore the easier path "fix the low hanging fruit, the queries")....
But in your case, you would probably benefit from a little more ram in the buffer cache unless your system is already at capacity memorywise.
More alarming to me are the
soft parse %
execute to parse %
soft parse %'s are the only ratio I really pay attention to -- and they should generally be at 99% or higher! You need to beat the people generating the queries about the head and shout "bind variables -- use them"....
execute to parse % is computed as
100 * (1-parses/executes). This means you have an almost 1 to 1 relationship between PARSE and EXECUTE -- which probably means you have some statements that you parse MORE then you execute -- which probably means you are using some tool to generate code and it uses the bad practice of "parse a statement" just to describe the outputs and then "parse the statement again" in order to execute it. You want to parse ONCE and execute many times...
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment