Oracle Database Tuning

Database analysis
Statement execute dbms_utility. Analyze_database (' COMPUTE ', Null,null, ');

Discovery Table Link Line
Statement Select table_name,chain_cnt from User_tables ORDER BY chain_cnt Desc
Select table_name,chain_cnt from All_tables where owner= ' RMS ' ORDER by chain_cnt Desc
Calculate cache Hit Rate
Statement SELECT Name,value from V$sysstat where NAME in (' physical reads ', ' db block gets ', ' consistent gets ');

Hit Rate 1-physical reads/(db block gets+consistent gets)
Recommendation up to 0.90 or above considered to be good
Calculation Library Cache Hit ratio
Statement SELECT SUM (pins-reloads)/sum (pins) from V$librarycache
Recommended less than 0.99 need to try to improve it, you can increase the size of the shared pool to improve it, if there is a lot of free space to improve our program
View currently unused shared pool select * from V$sgastat where name= ' free memory '

Calculate dictionary Cache Hit ratio
Statement select SUM (gets-getmisses-usage-fixed)/sum (gets) from V$rowcache
The proposal reached 0.85 or above is considered acceptable

Check rollback segment Contention
Statement select sum (gets), sum (Waits), sum (Waits)/sum (gets) from V$rollstat
Recommendation greater than 1% requires processing, you can reduce contention by more rollback segments

Check rollback segment Contraction
Statement Select Name,shrinks from V$rollstat,v$rollname where V$rollstat.usn=v$rollname.usn

Redo Log Cache
Select Name,value from v$sysstat where name = ' Redo Buffer allocation retries '