Oracle Performance Tuning Tips

---------------------------------
--> Tuning Multi-Threaded Server
=================================

If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number of
MTS_SERVERS in the init&SID.ora file.

set heading off
select 'Current MTS_SERVERS number is ' || value from v$parameter where name = 'mts_servers';

set heading on
select * from v$mts;

-----------------------------
--> Tuning The Library Cache
==============================

Library cache get/hit ratio for SQL AREA should be in high 90's. If not, there
is room to improve the efficiency of your application.

select namespace, gets, gethits, gethitratio from v$librarycache;

---------------------------
--> Reloads-to-pins ratio
===========================

If reloads-to-pins ratio is greater than .01, increase SHARED_POOL_SIZE in the init&SID.ora file.

set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';

set heading on

select sum(pins) "Executions", sum(reloads) "LC Misses",
sum(reloads)/sum(pins) "Ratio" from v$librarycache;

---------------------------
--> Data Dictionary Cache
===========================

If ratio is greater than .15, consider increasing prompt SHARED_POOL_SIZE in the init&SID.ora file.

set heading off

select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';

set heading on

select sum(gets) "Total Gets", sum(getmisses) "Total Get Misses",
sum(getmisses)/sum(gets) "Ratio" from v$rowcache;

--------------------------------------------------------------------
--> Packages you might want to consider pinning into the shared pool
=====================================================================

column owner format a12
column name format a25
column type format a15
set feedback on

select owner, name, type, loads, executions, sharable_mem
from v$db_object_cache
where kept = 'NO'
and loads > 1 and executions > 50 and sharable_mem > 10000
and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc;

--------------------------------
--> Shared Pool Reserved space
================================

The goal is to have zero REQUEST_MISSES and REQUEST_FAILURES, so increase SHARED_POOL_RESERVED_SIZE in the init&SID.ora file.
If either of them are greater than 0.

set heading off

select 'Current SHARED_POOL_RESERVED_SIZE value is ' || value from v$parameter where name = 'shared_pool_reserved_size';

set heading on

select request_misses, request_failures, last_failure_size
from v$shared_pool_reserved;

--------------------------------------
--> Tuning the Data Dictionary Cache
======================================

If the gethitratio is greater than .15 -- increase the SHARED_POOL_SIZE parameter in the init&SID.ora file.

set heading off

select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';

set heading on

select sum(gets) "total_gets", sum(getmisses) "total_get_misses",
sum(getmisses)/sum(gets) * 100 "gethitratio"
from v$rowcache;

----------------------------------
--> Tuning The Data Buffer Cache
==================================

Goal is to have a Cache Hit Ratio greater than 90% -- if lower, increase value for DB_BLOCK_BUFFERS in the init&SID.ora file.

set heading off

select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';

set heading on

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

select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';

------------------------------------------------------------------

--> If the number of free buffers inspected is high or increasing, consider increasing the DB_BLOCK_BUFFERS parameter in the init&SID.ora file.

set heading off

select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';

set heading on

select name, value from v$sysstat where name = 'free buffer inspected';

---------------------------------------------------------------------------

--> A high or increasing number of waits indicates that the db writer cannot keep up writing dirty buffers. Consider increasing the number of writers using the DB_WRITER_PROCESSES parameter in the init&SID.ora file.

set heading off

select 'Current DB_WRITER_PROCESSES value is ' || value from v$parameter where name = 'db_writer_processes';

set heading on

select event, total_waits from v$system_event where event in
('free buffer waits', 'buffer busy waits');

--------------------------
--> LRU Hit percentage
==========================

If the LRU Hit percentage is less than 99%, consider adding more DB_WRITER_PROCESSES and increasing the DB_BLOCK_LRU_LATCHES parameter in the init&SID.ora file.

set heading off

select 'Current DB_WRITER_PROCESSES value is ' || v1.value || chr(10) ||
'Current DB_BLOCK_LRU_LATCHES value is ' || v2.value
from v$parameter v1,v$parameter v2
where v1.name = 'db_writer_processes' and v2.name = 'db_block_lru_latches';

set heading on

select name, 100 - (sleeps/gets * 100) "LRU Hit%" from v$latch
where name = 'cache buffers lru chain';

---------------------------------
--> Tuning The Redo Log Buffer
=================================

There should never be a wait for log buffer space. Increase LOG_BUFFER in the init&SID.ora file if the selection below doesn't show "no rows selected".

set heading off

select 'Current LOG_BUFFER size is ' || value from v$parameter where name = 'log_buffer';

set heading on
set feedback on

select sid, event, state from v$session_wait
where event = 'log buffer space';

-------------------------------------------------------------------
--> Tables with Chain count greater than 10% of the number of rows
====================================================================

set feedback on

select owner, table_name, num_rows, chain_cnt, chain_cnt/num_rows "Percent"
from dba_tables where (chain_cnt/num_rows) > .1 and num_rows > 0;

-------------------
--> Tuning Sorts
===================

The ratio of disk sorts to memory sorts should be less than 5%. Consider increasing the SORT_AREA_SIZE parameter in the init&SID.ora file. You might also consider setting up separate temp tablespaces for frequent users of disk sorts.

set heading off

select 'Current SORT_AREA_SIZE value is ' || value from v$parameter where name = 'sort_area_size';

set heading on

select disk.value "Disk", mem.value "Mem", (disk.value/mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';

-------------------------------
--> Tuning Rollback segments
===============================

If ratio of waits to gets is greater than 1%, you need more rbs segments.

set heading off

select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';

set heading on

select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets"
from v$rollstat;

----------------------------
--> Rollback segment waits
============================

Any waits indicates need for more segments.

set heading off

select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';

set heading on

select * from v$waitstat where class = 'undo header';

--------------------------------------------------
--> Rollback segment waits for transaction slots
==================================================

Any waits indicates need for more segments.

set heading off

select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';

set heading on
set feedback on

select * from v$system_event where event = 'undo segment tx slot';

--------------------------
--> Rollback contention
==========================

Should be zero for all rbs's.

column name format a10

select n.name,round (100 * s.waits/s.gets) "%contention"
from v$rollname n, v$rollstat s
where n.usn = s.usn;

---------------------------------------------------------------------------

Purpose: List locks on tables and other objects currently held by user sessions, including session information and commands to kill those sessions.

column object_name format a10
column username format a10
column owner format a10

select username,v$session.sid,serial#,owner,object_id,object_name,
object_type,v$lock.type
from dba_objects,v$lock,v$session where object_id = v$lock.id1
and v$lock.sid = v$session.sid and owner != 'SYS';

set heading off pagesize 0

!echo
!echo To kill locking sessions:

select 'alter system kill session ''' || v$session.sid || ',' ||
serial# || ''';'
from dba_objects,v$lock,v$session where object_id = v$lock.id1
and v$lock.sid = v$session.sid and owner != 'SYS';