Hit Ratio and Cache in Oracle using snap.sql
set linesize 75;
set pagesize 9999;
set pause off;
set echo off;
set termout on;
set showmode off;
set feedback off;
set newpage 1;
set verify off;
spool snap.lst
--spool /tmp/snap;
prompt **********************************************************
prompt Hit Ratio Section
prompt **********************************************************
prompt
prompt =========================
prompt BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
-- (sum(decode(name,'db block gets',value,0))+
-- (sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
prompt
prompt
prompt =========================
prompt REDO LOG BUFFER
prompt =========================
prompt (should be near 0, else increase size of LOG_BUFFER in init.ora)
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),
value
from v$sysstat where name = 'redo log space requests';
set heading on
prompt
prompt
prompt **********************************************************
prompt Free memory should be > 1,000
prompt **********************************************************
prompt
column bytes format 999,999,999
select name, bytes from v$sgastat where name = 'free memory';
prompt
prompt **********************************************************
prompt SQL Summary Section
prompt **********************************************************
prompt
column "Tot SQL run since startup" format 999,999,999
column "SQL executing now" format 999,999,999
select sum(executions) "Tot SQL run since startup",
sum(users_executing) "SQL executing now"
from v$sqlarea;
prompt
prompt
prompt **********************************************************
prompt Lock Section
prompt **********************************************************
prompt
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
prompt =========================
prompt
select substr(username,1,12) "User",
substr(lock_type,1,18) "Lock Type",
substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers or other DDL
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,15) "Name",
substr(a.type,1,20) "Type",
substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt =========================
prompt DML LOCKS - These are table and row locks...
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,20) "Name",
substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt
prompt **********************************************************
prompt Latch Section
prompt **********************************************************
prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
prompt
column "miss_ratio" format 999.99
column "immediate_miss_ratio" format 99.99
select substr(l.name,1,30) name,
(misses/(gets+.001))*100 "miss_ratio",
(immediate_misses/(immediate_gets+.001))*100 "immediate_miss_ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
and (
(misses/(gets+.001))*100 > .2
or
(immediate_misses/(immediate_gets+.001))*100 > .2 )
order by l.name;
prompt
prompt
prompt **********************************************************
prompt Rollback Segment Section
prompt **********************************************************
prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
prompt if free list > 1% then increase FREELIST in init.ora
--column count format 999,999,999
select class, count
from v$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block')
group by class,count;
column "Tot # of Requests for Data" format 999,999,999
select sum(value) "Tot # of Requests for Data" from v$sysstat where
name in ('db block gets', 'consistent gets');
prompt
prompt =========================
prompt ROLLBACK SEGMENT CONTENTION
prompt =========================
prompt
prompt If any ratio is > .01 then more rollback segments are needed
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
prompt
prompt
set feedback on;
prompt **********************************************************
prompt Session Event Section
prompt **********************************************************
prompt if average-wait > 0 then contention exists
prompt
select substr(event,1,30) event,
total_waits, total_timeouts, average_wait
from v$session_event
where average_wait > 0 ;
--or total_timeouts > 0;
prompt
prompt
prompt **********************************************************
prompt Queue Section
prompt **********************************************************
prompt average wait for queues should be near zero ...
prompt
column "totalq" format 999,999,999
column "# queued" format 999,999,999
select paddr, type "Queue type", queued "# queued", wait, totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;
prompt here are waits within each tablesace
select
sysdate,
substr(event,1,30) event,
substr(tablespace_name,1,14) tablespace,
p2 block
from v$session_wait a, dba_data_files b
where
a.p1 = b.file_id
and
(
event = 'lock element waits'
or
event = 'lock element cleanup'
or
event = 'buffer busy waits'
or
event = 'DFS enqueue lock acquisition'
or
event = 'DFS enqueue lock handle'
or
event = 'db file sequential read'
);
prompt here are sessions counts waiting for block in these tablespaces
select
substr(a.tablespace_name,1,12) ts_name,
count(*)
from v$session b, dba_data_files a
where
b.row_wait_file# = a.file_id
and
row_wait_file# <> 0
and type='USER'
group by
substr(a.tablespace_name,1,12)
;
prompt
prompt
prompt **********************************************************
prompt Session Events Section
prompt **********************************************************
prompt These are the system-wide totals
set pages 999;
column c0 format 999;
column c1 heading 'event' format a35;
column c2 heading 'tot waits' format 9,999,999;
column c3 heading 'tot timeouts' format 9,999,999;
column c4 heading 'avg waits' format 9,999,999;
select distinct
substr(event,1,35) c1,
sum(total_waits) c2,
sum(total_timeouts) c3,
sum(average_wait) c4
from v$session_event
where
average_wait > 0
or
total_timeouts > 0
group by
substr(event,1,35)
order by
sum(total_timeouts) desc;
prompt
prompt
prompt **********************************************************
prompt Session summary by tablespace Section
prompt **********************************************************
prompt This is a summary of activity by tablespace
prompt high buffer busy waits may indicate the need for more freelists
column c0 heading 'tablespace' format a14;
column c1 heading 'event' format a25;
column c2 heading 'tot waits' format 999,999;
column c3 heading 'tot timeouts' format 999,999;
column c4 heading 'avg waits' format 999,999;
break on c0 skip 1;
select distinct
substr(b.tablespace_name,1,14) c0,
substr(a.event,1,25) c1,
sum(a.total_waits) c2,
sum(a.total_timeouts) c3,
sum(a.average_wait) c4
from v$session_event a, dba_data_files b, v$session c
where
a.sid = c.sid
and
b.file_id = c.ROW_WAIT_FILE#
and
(
average_wait > 0
or
total_timeouts > 10
)
and
total_waits > 1000
group by
substr(tablespace_name,1,14),
substr(event,1,25)
order by
substr(b.tablespace_name,1,14),
sum(total_waits) desc;
Now, let's execute this SQL on our system and see the results.
Simulation:
Command is "SQL>snap"
Output follows:
**********************************************************
Hit Ratio Section
**********************************************************
=========================
BUFFER HIT RATIO
=========================
(should be > 70, else increase db_block_buffers in init.ora)
logical_reads phys_reads phy_writes BUFFER HIT RATIO
--------------- ------------ ------------ ----------------
1,514,647,648 72,344,700 6,336,729 95
=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora)
Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
--------------- ----------------------- -------------------------
3,884,461 90,343 97
=========================
LIBRARY CACHE MISS RATIO
=========================
(If > 1 then increase the shared_pool_size in init.ora)
executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------ ---------------------------- ------------------------
2,589,057 7,546 .0029
=========================
Library Cache Section
=========================
hit ratio should be > 70, and pin ratio > 70 ...
NAMESPACE Hit ratio pin hit ratio reloads
--------------- ---------- ------------- ------------
SQL AREA 96 98 5,822
TABLE/PROCEDURE 87 90 1,722
BODY 99 99 0
TRIGGER 100 100 0
INDEX 0 0 1
CLUSTER 30 20 1
OBJECT 100 100 0
PIPE 99 99 0
=========================
REDO LOG BUFFER
=========================
(should be near 0, else increase size of LOG_BUFFER in init.ora)
redo log space requests 127,165
**********************************************************
Free memory should be > 1,000
**********************************************************
NAME BYTES
-------------------------- ------------
free memory 1,620,620
**********************************************************
SQL Summary Section
**********************************************************
Tot SQL run since startup SQL executing now
------------------------- -----------------
567,498 8
**********************************************************
Lock Section
**********************************************************
=========================
SYSTEM-WIDE LOCKS - all requests for locks or latches
=========================
no rows selected
=========================
DDL LOCKS - These are usually triggers or other DDL
=========================
no rows selected
=========================
DML LOCKS - These are table and row locks...
=========================
no rows selected
**********************************************************
Latch Section
**********************************************************
if miss_ratio or immediate_miss_ratio > 1 then latch
contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
NAME miss_ratio immediate_miss_ratio
------------------------------ ---------- --------------------
cache buffers chains .59 .01
cache buffers lru chain .17 .57
latch wait list .61 .00
parallel query alloc buffer 15.26 .00
parallel query stats 9.52 .00
process queue reference .03 79.83
query server freelists 1.83 .00
redo copy 98.61 .02
**********************************************************
Rollback Segment Section
**********************************************************
if any count below is > 1% of the total number of requests for data
then more rollback segments are needed
if free list > 1% then increase FREELIST in init.ora
CLASS COUNT
------------------ ----------
free list 0
system undo block 0
system undo header 0
undo block 24
undo header 20969
Tot # of Requests for Data
--------------------------
############
=========================
ROLLBACK SEGMENT CONTENTION
=========================
If any ratio is > .01 then more rollback segments are needed
NAME WAITS GETS Ratio
------------------------------ ---------- ---------- ---------
SYSTEM 0 1683 .00000
R02 11 23928 .00046
R03 9 29812 .00030
R04 11 34371 .00032
R05 6 728563 .00001
R06 8 237557 .00003
R07 9 63747 .00014
R08 8 52912 .00015
R09 9 35060 .00026
R10 10 178362 .00006
**********************************************************
Session Event Section
**********************************************************
if average-wait > 0 then contention exists
EVENT TOTAL_WAITS TOTAL_TIMEOUTS AVERAGE_WAIT
------------------------------ ------------ -------------- ------------
Null event 1,464,983 1 2.15970902
Null event 500,435 81,418 56.8211756
Null event 450,943 87,818 63.173665
Null event 428,568 89,966 66.5919644
Null event 436,297 89,355 65.3663972
latch free 7,429 7,429 .407726477
latch free 1,948 1,948 .484599589
latch free 4 4 .5
latch free 19 18 .315789474
latch free 25 25 .56
latch free 5 5 .4
latch free 104 104 .269230769
latch free 2 2 .5
latch free 47 47 .255319149
latch free 14 14 .428571429
latch free 244 243 .278688525
latch free 34 34 .529411765
latch free 52 52 .269230769
latch free 236,721 236,721 .403876293
latch free 292 282 .20890411
latch free 85 41 .635294118
latch free 32 32 .40625
pmon timer 117,843 102,420 264.268315
rdbms ipc reply 540 0 20.4592593
rdbms ipc reply 448 419 191.535714
rdbms ipc reply 3 0 41.6666667
rdbms ipc reply 19 1 38.7894737
rdbms ipc reply 2 0 35.5
rdbms ipc message 65,319 50,400 232.629097
rdbms ipc message 252,523 92,714 113.381114
rdbms ipc message 102,411 101,871 299.088399
rdbms ipc message 176 176 177002.386
enqueue 633 573 287.704581
enqueue 153 139 281.27451
enqueue 682 625 283.334311
control file sequential read 1,919 0 .003126628
control file sequential read 617,058 0 .563528874
control file sequential read 15,030 0 .283965403
control file sequential read 415,091 0 .020113662
control file parallel write 5,486 0 4.36766314
control file parallel write 50,908 0 4.2708808
free buffer waits 84 47 61.7380952
free buffer waits 304 198 73.8157895
free buffer waits 2 1 84.5
free buffer waits 16 15 98.0625
free buffer waits 83 63 84.6987952
free buffer waits 18 16 98.0555556
write complete waits 6 0 9.83333333
buffer busy waits 19,176 17,895 94.1271381
buffer busy waits 5 0 2
buffer busy waits 224 0 1.11160714
buffer busy waits 1,077 0 1.08635097
buffer busy waits 345 0 1.17101449
buffer busy waits 229 0 1.05240175
buffer busy waits 18 0 1.38888889
log file sequential read 553 0 3.23146474
log file single write 1,093 0 1.84903934
log file parallel write 151,432 0 14.9760619
log file switch (checkpoint in 248 177 98.2016129
log file switch completion 1 0 69
log file sync 39,827 81 2.10708816
log file sync 4 0 41.5
log file sync 28 0 2.5
log file sync 107 10 20.4205607
log file sync 29 0 9.34482759
log file sync 14 0 13.2857143
log file sync 12 0 13.5833333
log file sync 12 0 20.5833333
log file sync 27 0 2.37037037
log file sync 3 0 2.66666667
log file sync 1 0 4
log file sync 44 0 13.75
log file sync 2 0 2
db file sequential read 2 0 6.5
db file sequential read 566 0 .033568905
db file sequential read 283 0 .035335689
db file sequential read 77 0 3.4025974
db file sequential read 40,469 0 3.78173417
db file sequential read 4,114 0 1.30797278
db file sequential read 447,276 0 .978404833
db file sequential read 1 0 4
db file sequential read 16,317 0 .337500766
db file sequential read 77 0 1.23376623
db file sequential read 5,470 0 .822669104
db file sequential read 2,444 0 .26309329
db file sequential read 2,268 0 .196208113
db file sequential read 1 0 5
db file sequential read 161,640 0 .43022148
db file sequential read 4,615 0 .967063922
db file sequential read 436 0 .350917431
db file sequential read 26,176 0 .361437958
db file sequential read 326 0 .260736196
db file sequential read 199 0 .542713568
db file sequential read 96 0 .104166667
db file sequential read 5,299 0 .125495376
db file sequential read 28 0 1.10714286
db file sequential read 5,229 0 .625741059
db file scattered read 1,255 0 2.29243028
db file scattered read 2,219 0 2.02929247
db file scattered read 127,114 0 .910961814
db file scattered read 1,750 0 1.20971429
db file scattered read 3,355 0 .892101341
db file scattered read 25,016 0 .419931244
db file scattered read 1 0 6
db file scattered read 102,547 0 .738207846
db file scattered read 28,464 0 1.85353429
db file scattered read 262,139 0 1.97534896
db file scattered read 10,518 0 1.02918806
db file scattered read 50,532 0 1.24980211
db file scattered read 12,556 0 .774848678
db file scattered read 129,959 0 .648912349
db file scattered read 5,994 0 1.12395729
db file scattered read 303 0 3.74257426
db file single write 283 0 1.98939929
db file single write 40,469 0 3.45790605
db file parallel write 251,531 1,772 46.7045811
smon timer 979 959 29624.1195
row cache lock 386 381 299.904145
SQL*Net message to client 2,152 0 .001394052
SQL*Net message to client 5,786 0 .001901141
SQL*Net message to client 2,956 0 .00202977
SQL*Net message to client 20 0 .05
SQL*Net message to client 255 0 .007843137
SQL*Net message to client 69,821 0 .002091061
SQL*Net message to client 1,825 0 .004383562
SQL*Net message to client 1,850 0 .036756757
SQL*Net message to client 774 0 .03875969
SQL*Net more data to client 2 0 1
SQL*Net message from client 34 0 .235294118
SQL*Net message from client 36 0 .472222222
SQL*Net message from client 254 0 65.1299213
SQL*Net message from client 1,785 0 31.0414566
SQL*Net message from client 20 0 104
SQL*Net message from client 5,785 0 73.0914434
SQL*Net message from client 1,849 0 1007.57058
SQL*Net message from client 1,824 0 185.270833
SQL*Net message from client 78 0 4.24358974
SQL*Net message from client 139 0 6.92805755
SQL*Net message from client 19 0 3.84210526
SQL*Net message from client 2,955 0 98.0365482
SQL*Net message from client 136 0 536.088235
SQL*Net message from client 2,151 0 52.8335658
SQL*Net message from client 773 0 2390.9599
SQL*Net message from client 69,821 0 1.50756936
SQL*Net message from client 38 0 .184210526
SQL*Net message from client 13 0 .153846154
SQL*Net more data from client 3 0 .333333333
pipe get 35,075 33,652 422.997434
148 rows selected.
**********************************************************
Queue Section
**********************************************************
average wait for queues should be near zero ...
no rows selected
here are waits within each tablesace
SYSDATE EVENT TABLESPACE BLOCK
--------- ------------------------------ -------------- ----------
25-MAY-99 db file sequential read BASE2 4938
1 row selected.
here are sessions counts waiting for block in these tablespaces
no rows selected
**********************************************************
Session Events Section
**********************************************************
These are the system-wide totals
event tot waits tot timeouts avg waits
----------------------------------- ---------- ------------ ----------
Null event 3,281,301 348,558 254
latch free 247,058 247,002 7
rdbms ipc message 420,430 245,162 177,647
pmon timer 117,844 102,421 264
pipe get 35,076 33,653 423
buffer busy waits 21,074 17,895 102
db file parallel write 251,536 1,772 47
enqueue 1,468 1,337 852
smon timer 979 959 29,624
rdbms ipc reply 1,012 420 328
row cache lock 386 381 300
free buffer waits 507 340 501
log file switch (checkpoint incompl 248 177 98
log file sync 40,110 91 148
SQL*Net message from client 87,833 0 4,561
SQL*Net message to client 85,542 0 0
log file parallel write 151,432 0 15
db file scattered read 763,807 0 27
write complete waits 6 0 10
log file switch completion 1 0 69
log file single write 1,093 0 2
log file sequential read 553 0 3
db file single write 40,752 0 5
db file sequential read 723,449 0 33
control file sequential read 1,049,450 0 1
control file parallel write 56,394 0 9
SQL*Net more data from client 3 0 0
SQL*Net more data to client 2 0 1
28 rows selected.
**********************************************************
Session summary by tablespace Section
**********************************************************
This is a summary of activity by tablespace
high buffer busy waits may indicate the need for more freelists
no rows selected
