Oracle Database Tuning Guide

1)Try find out where there is memory available or not in the machine.
2) within SGA you can increase individual component like log buffer,
shared pool or any other if Sufficient memory is available with SGA.
user
SQL> show parameter sga_max_size (for check total sga allocation)

2) you can increase or decrease component size with ALTER SYSTEM command
if you database is 9.0 or abobe because 8i is not possible because spfile is not there.
In 8i you have to shutdown the database and edit the parameter file then startup.

3) DB_BLOCK_BUFFER is the parameter for buffer cache size defination in 8i or below.
from oracle 9i or 10g or above it is DB_CACHE_SIZE.
To find out hit ratio of Database buffer cache

select (1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100 "Buffer cache hit ratio"
from v$buffer_pool_statistics


4) Rollback segment are the concept of oracle 8i.
From 9i using the Undo tablespace.
Undo segment tuning done by the orcale server itself you have to just monitor
that the undo tablespace are the proper size or not or you have to add datafiles.

4) Log_buffer parameter for redo log size upto 10g alteration of this parameter need
shutdown and then startup the database.
You have to find out the log buffer is currently proper sized or not

select *
from v$sysstat
where name like 'redo%'

from this query you have to find out "redo entries" and "redo buffer allocation retries"
vales

log ratio= redo buffer allocation retries/redo entries

This ratio if <1 or 0 then it is proper size. Other wise you have increase parameter value.

5) for sorting it is good if your 98% sorting going on in memory
you can fing out by using.

select *from v$sysstat
where name like 'sort%'

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
344 sorts (memory) 64 306941 2091983730
345 sorts (disk) 64 2 2533123502
346 sorts (rows) 64 28458010 3757672740

-- See only 2 time going to disk for sorting and (306941-2) so 306939 times in memory.
Because first sorting done in memory so in this case 2 times disk mean it after sorting memory
due to insufficient memory it gone into disk.
---------------------------------------------------------------------------------------

1) The following query identifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, hash_value, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2) The following query identifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, hash_value, sql_text FROM v$sqlarea WHERE buffer_gets > 100000