Oracle Tips and Techs: Performance Tuning

Performance Tuning is a vast area in Oracle Database Administration. Tuning is divided into 3 parts as Oracle database is considered. 1. Performance Planning 2. Instance Tuning 3. SQL Tuning

In this post let's see about some Instance tuning basics which might help many DBAs who are new to performance tuning tasks.

1. Tuning the cache hit ratio

Select sum(getmisses) / sum(gets) "Miss ratio"

From v$rowcache;

If ratio < 15% --> DB fine

If ratio > 15% --> increase shared_pool_size

2. Tuning the library cache

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');

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. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora parameter OPEN_CURSORS may also need to increased

3. Tuning the log buffer

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

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';

If the ratio of "redo log space requests" to "redo entries" is less than 5000:1 (i.e. less than 5000 in the above), then increase the size of the redo log buffer until the space request ratio stops falling.

Alternately, if memory is not a constraint then try to reduce the number of times that a process had to wait for the log cache to be flushed:

Select name, value from v$sysstat
Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M.

If you want to know how long processes had to wait as well as the number of times then try the following script instead:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Be ware that because of the time granularity, 'redo log space requests' may be greater than zero whilst 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Unless you have a busy server having 'redo log space wait time' of (or near) zero may indicate an acceptable level of tuning.

4. Tuning the buffer cache hit ratio

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';

If the cache-hit ratio goes below 90% then:

For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.

5. Tuning sorts

Select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');

If a large number of sorts require I/O to disk, increase the initialisation parameter SORT_AREA_SIZE. As a guide less than 1% of the sorts being to disk is optimum.

If more than 1% of sorts are to disk then increase SORT_AREA_SIZE and then restart Oracle.

6. Tuning rollback segments

To identify contention for rollback segments first find out the number of times that processes had to wait for the rollback segment header and blocks. The V$WAITSTAT view contains this information:

select class, count from v$waitstat
where class in ('system undo header', 'system undo block', 'undo header', 'undo block');

The number of waits for any class should be compared with the number of logical reads over the same period of time. This information can be found in V$SYSSTAT:

select sum(value) from v$sysstat
where name in ('db block gets', 'consistent gets');

If the number of waits for any class of waits is greater than 1% of the total number of logical reads then add more rollback segments.

The following query gives the percentage of times that a request for data resulted in a wait for a rollback segment:

select round(sum(waits)/sum(gets),2) from v$rollstat;

If the percentage is greater than 1% then create more rollback segments.

Rollback segments should be isolated as much as possible by placing them in their own tablespace, preferably on a separate disk from other active tablespaces. The OPTIMAL parameter is used to cause rollback segments to shrink back to an optimal size after they have dynamically extended. The V$ROLLSTAT table can help in determining proper sizing of rollback segments:

Select segment_name, shrinks, aveshrink, aveactive "Avg.Active"
from v$rollstat v, dba_rollback_segs d
where v.usn = d.segment_id;

The following table shows how to interpret these results:

Cumulative number of shrinks	Average size of shrink	Recommendation
Low	Low	If the value for “Avg.Active” is close to OPTIMAL, the settings are correct. If not, then OPTIMAL is too large. (Note: Be aware that it is sometimes better to have a larger optimal value - depending on the nature of the applications running, reducing it towards “Avg.Active” may cause some applications to start experiencing ORA-01555.)
Low	High	Excellent – few, large shrinks.
High	Low	Too many shrinks – `OPTIMAL` is too small.
High	High	Increase `OPTIMAL` until the number of shrinks is lower.