Oracle Tuning Tips

-->  Tuning Cache Hit Ratio
The cache miss ratio is the the number of cache misses compared to the total number of cache read attempts. This is calculated as follows:

Select sum(getmisses) / sum(gets) "Miss ratio"
From v$rowcache;

The miss ratio should be less than 15%. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE.

--> Tuning the Library Cache
Calculate the cache hit ratio for the library cache with the following query:

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.

--> 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. (Tip contributed by Gianni Quattrocchi.)

--> Tuning the Buffer Cache Hit Ratio
A logical read occurs whenever a user requests data from the database. It occurs whenever the data is in the buffer cache or whether the user process must read it from disk. If the data must be read from disk then a physical read occurs. Oracle keeps track of logical and physical reads in the V$SYSSTAT table.
Use the following SQL statement to determine the values required for the hit radio calculation:

select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');

The cache-hit ratio can be calculated as follows:

Hit ratio = 1 - (physical reads / (db block gets + consistent gets))

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.

I am grateful to Kamil Jakubovic for provided me with a single statement for calculating the 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'

--> Tuning Sorts
Issue the following SQL statement to determine the amount of sorting in memory and on disk:

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. I would suggest laving Oracle to run for a day or two before checking to see what difference the change has made.
--> Tuning Rollback Segements
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.

--> Identifying Missing Indexes:
There is no guaranteed way of finding missing indexes. The following is intended to help identify where beneficial indexes do not exist.
To find the top SQL statements that have caused most block buffer reads:

Select buffer_gets, sql_text
    from v$sqlarea
    where buffer_gets > 10000
    order by buffer_gets desc;

If this returns a large number of rows then increase the number of ‘buffer_gets’ required, if it returns no rows then decrease this threshold.
Typically, most of these will be select statements of some sort. Considering each in turn, identify what indexes would help with the query and then check that those indexes exist. Create them if necessary.
To find the most frequently executed SQL:

Select executions, buffer_gets, sql_text
    from v$sqlarea
    where executions > 10000
    order by executions desc;

If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then decrease the number of executions required.

--> Identify index fragmentation:
To obtain information about an index:

analyze index  validate structure;

This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.
An index should be considered for rebuilding under any of the following conditions:

• The percentage of deleted rows exceeds 30% of the total, i.e. if
  del_lf_rows / lf_rows > 0.3.
• If the ‘HEIGHT’ is greater than 4.
• If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

--> Identify FreeList Contention
To identify the percentage of requests that resulted in a wait for a free block run the following query:

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;

This should be less than 1%. To reduce contention for a table’s free list the table must be recreated with a larger value in the FREELISTSstorage parameter.
--> Identify Significant Reparsing of SQL
The shared-pool contains (amongst other things) previously parsed SQL, and this allows Oracle to avoid re-parsing SQL unnecessarily.
The following SQL identifies those SQL statements that have needed to be re-parsed numerous times:

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;

If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then perhaps decrease the number of executions required.
If there is SQL that is being repeatedly reparsed then consider increasing the value of SHARED_POOL_SIZE.

--> Reducing database Fragmentation
Excessively fragmented tables or indexes can adversely affect performance. Use the following SQL to identify those database objects that have over 10 extents allocated:

select * from dba_segments where extents > 10;

In general, if a table or index has more than 10 extents then rebuild it to fit into one extent.
A table can only be rebuilt by exporting and then importing it. The database will be unavailable for use by applications during this time. The steps to accomplish this are:

1. Export the table with COMPRESS=Y
2. Drop the table
3. Import the table.

An index can be rebuilt without preventing others from still using it. Firstly change the storage parameters to make the ‘next’ storage parameter larger (perhaps double it). The initial storage value cannot be changed. Then rebuild the index.

--> Rebuilding Indexes
Periodically, and typically after large deletes or inserts, it is worth rebuilding indexes. The SQL for this is:

Alter index  rebuild;

Alternatively, the following performs the same, but avoids writing to the redo logs and thus speeds up the index rebuild:

Alter index  rebuild unrecoverable;

Note: If performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:

Alter index  rebuild tablespace ;

Otherwise the index will be moved to the temporary tablespace.