Database Tuning
The following few queries are runned on the pcil database for assessment of the database
performance.
Catch Hit Ratio
SQL> Select sum(getmisses) / sum(gets) "Miss ratio"
From v$rowcache;
Miss ratio
----------
.001634153
The miss ratio should be less than 15%. In our db it is 0.01 %
very much sufficient. Therefore Tuning is not required
Library Cache
SQL> Select sum(pinhits) / sum(pins) "Hit Ratio",
sum(reloads) / sum(pins) "Reload percent"
From v$librarycache
Where namespace in
('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');
Hit Ratio Reload percent
---------- --------------
.869162485 .0253795
The hit ratio should be at least 85% (i.e. 0.85). The reload percent should be very
low, 2% (i.e. 0.02) or less.
In our db, it is 86% and 2%, it is sufficient, therefore Tuning is not required.
Log Buffer
SQL> Select Round(e.value/s.value,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests' and e.name = 'redo entries';
Redo Log Ratio
--------------
47772.5102
SQL> Select name, value from v$sysstat
Where name = 'redo log space requests';
NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 49
SQL> Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');
NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 49
redo log space wait time 65
Tuning is not required.
Buffer Cache hit ratio
SQL> select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets 8222836
consistent gets 2303412747
physical reads 60119927
SQL> select 100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from v$sysstat v1, v$sysstat v2, v$sysstat v3 where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads';
Cache Hit Ratio [%]
-------------------
97.3987933
The ratio should be greater than 90%. In our DB it is 97% – therefore tuning is not
required.
Tuning Sorts
SQL> Select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 2571266
sorts (disk) 0
Less than 1% of the sorts being to disk is optimum. In our DB, it is 0%, therefore
tuning is not required.
Tuning Roll Back Segments
SQL> select class, count from v$waitstat
where class in ('system undo header', 'system undo block', 'undo header', 'undo
block');
CLASS COUNT
------------------ ----------
system undo header 0
system undo block 0
undo header 74
undo block 2
SQL> select sum(value) from v$sysstat
where name in ('db block gets', 'consistent gets');
SUM(VALUE)
----------
2336618410
SQL> select round(sum(waits)/sum(gets),2) from v$rollstat;
ROUND(SUM(WAITS)/SUM(GETS),2)
-----------------------------
0
SQL> Select segment_name, shrinks, aveshrink, aveactive "Avg.Active"
from v$rollstat v, dba_rollback_segs d where v.usn = d.segment_id; 2 3
SEGMENT_NAME SHRINKS AVESHRINK Avg.Active
------------------------------ ---------- ---------- ----------
SYSTEM 0 0 5734
_SYSSMU1$ 4 4980736 2800056
_SYSSMU2$ 4 1048576 389870
_SYSSMU3$ 5 4194304 2712101
_SYSSMU4$ 7 10785353 89114178
_SYSSMU5$ 6 4718592 6447132
_SYSSMU6$ 8 10616832 126408491
_SYSSMU7$ 3 2097152 697635
_SYSSMU8$ 2 1048576 381349
_SYSSMU9$ 4 1310720 450043
_SYSSMU10$ 11 8102632 12131760
SEGMENT_NAME SHRINKS AVESHRINK Avg.Active
------------------------------ ---------- ---------- ----------
_SYSSMU11$ 2 1048576 330487
_SYSSMU12$ 2 1572864 402773
_SYSSMU13$ 2 1048576 342764
_SYSSMU14$ 4 6291456 3513439
_SYSSMU15$ 7 730258 404257
16 rows selected.
The ratio should be less than 1% , in our db it is 0% . therefore, tuning is not
required.
Identifying free list contention
SQL> select round( (sum(decode(w.class,'free list',count,0))
/ (sum(decode(name,'db block gets', value, 0))
+ sum(decode(name,'consistent gets', value, 0)))) * 100,2)
from v$waitstat w, v$sysstat;
ROUND((SUM(DECODE(W.CLASS,'FREELIST',COUNT,0))/
(SUM(DECODE(NAME,'DBBLOCKGETS',VA
--------------------------------------------------------------------------------
0
This ratio should be less than 1%. In our DB, it is 0%. Therefore Tuning is not
recommended.
Identify significant reparsing of SQL
SQL> select executions, t.sql_text
from v$sqlarea a, v$sqltext t
where parse_calls >1 and parse_calls = executions and a.address=t.address and
executions > 10000
order by executions desc;
EXECUTIONS SQL_TEXT
---------- ----------------------------------------------------------------
53033 DATE_FORMAT'
53033 SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE
PARAMETER ='NLS_
53016 Select TARGETTIME From TimeConfiguration
26380 TERRITORY='AMERICA'
26380 ALTER SESSION SET TIME_ZONE='+5:30'
NLS_LANGUAGE='AMERICAN' NLS_
6 rows selected.
This is sufficient. Tuning is not recommended, as there is no large no. of sql statements
are selected from the above query.
