Performance Tuning Part 2

1) REDO LOG BUFFER

select substr(name,1,30) name, value from v$sysstat where name = 'redo log space requests';

2) Ideally, 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".

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

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

3) If there are any Wait events because of log switches, consider increasing the size of the redo log files.

select 'Current size of redo log files is ' || bytes || ' bytes' from v$log where rownum = 1;

select event, total_waits, time_waited, average_wait from v$system_event   where event like 'log file switch completion%';

4) SQL Summary Section

select sum(executions) "Tot SQL run since startup",sum(users_executing) "SQL executing now"   from v$sqlarea;

5) Tables with Chain count greater than 10% of the number of rows:

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;

6) Lock Section
 SYSTEM-WIDE LOCKS - all requests for locks or latches

select substr(username,1,12) "User",substr(lock_type,1,30) "Lock Type",substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread') and a.session_id = b.sid;

7) DDL LOCKS - These are usually triggers or other DDL */---------------

select substr(username,1,12) "User", substr(owner,30) "Owner", substr(name,1,15) "Name", substr(a.type,1,20) "Type", substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;

8) DML LOCKS - These are table and row locks...

select substr(username,1,12) "User",substr(owner,1,8) "Owner", substr(name,1,20) "Name", substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;

9) Latch Section

if miss_ratio or immediate_miss_ratio > 1 then latch ----contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora

select substr(l.name,1,30) name,(misses/(gets+.001))*100 "miss_ratio", (immediate_misses/(immediate_gets+.001))*100 "immediate_miss_ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch# and ( (misses/(gets+.001))*100 > .2 or (immediate_misses/(immediate_gets+.001))*100 > .2 )
order by l.name;

10) Rollback Segment Section

--if any count below is > 1% of the total number of requests for data then more rollback segments are needed

select class, count, count/100
from v$waitstat
where class in ('free list','system undo header','system undo block','undo header','undo block')
group by class,count;

11) "Tot # of Requests for Data"

select sum(value) "Tot # of Requests for Data" from v$sysstat where name in ('db block gets', 'consistent gets');

12) CONTENTION

--Description:    This script will display the waits on the instance since the instance has been created. Good place to look for system bottle necks

select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event
order by 4;

13) ROLLBACK SEGMENT CONTENTION

------Description: Display the contention in rollback segments
------Code:column "Ratio" format 99.99999
--------If any ratio is > .01 then more rollback segments are needed -----------------------

select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;

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

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

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.

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

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

----Rollback segment waits for transaction slots -- any waits indicates need for more segments.

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

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

--------------Rollback contention -- should be zero for all rbs's.

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

14) ACTIVE TRANSATION ROLL BACK SEGMENT

--Description:    This script will display the active user and the rollback segment being used in the database

--Code:

SELECT r.name rr,nvl(s.username,'no transaction') us,s.osuser os,s.terminal te

FROM v$lock l,v$session s,v$rollname r

WHERE l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = 'TX' AND l.lmode = 6

ORDER BY r.name ;

15) ROLL BACK SEGMENT WRAPPED

--Description:    This script reports how many hours it has been since the rollback segments wrapped

--Code:

select n.name,round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) / (s.writes/s.rssize),1) "Hours"

from v$instance i1,v$instance i2,v$rollname n,v$rollstat s

where i1.key = 'STARTUP TIME - JULIAN' and i2.key = 'STARTUP TIME - SECONDS' and n.usn = s.usn and s.status = 'ONLINE';

16) ROLL BACK STATISTICS

--Description:    Gives lots of usefull easy to read info on how your RBS are performing. Needs 132 char display.

--Code:

select name,XACTS,initial_extent/1048576 InitExt,next_extent/1048576 NextExt,min_extents MinExt,max_extents MaxExt,optsize/1048576 optsize,

RSSIZE/1048576 rssize,HWMSIZE/1048576 hwmsize,wraps,extends,shrinks,aveshrink/1048576 aveshrink,gets,waits,writes/1024 writes,writes/gets wpg

from v$rollstat,v$rollname,dba_rollback_segs

where v$rollstat.usn=v$rollname.usn

and dba_rollback_segs.segment_id=v$rollname.usn

order by name;

17) Session Event Section

----------if average_wait > 0 then contention exists

select substr(event,1,30) event, total_waits, total_timeouts, average_wait from v$session_event where average_wait > 0 ;

--or total_timeouts > 0;

18) Queue Section

--------average wait for queues should be near zero ...

select paddr, type "Queue type", queued "# queued", wait, totalq, decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;

19) Multi-threaded Server Section

----If the following number is > 1 then increase MTS_MAX_SERVERS parm in init.ora

select decode( totalq, 0, 'No Requests', wait/totalq || ' hundredths of seconds') "Avg wait per request queue"  from v$queue  where type = 'COMMON';

-----If the following number increases, consider adding dispatcher processes

select decode( sum(totalq), 0, 'No Responses', sum(wait)/sum(totalq) || ' hundredths of seconds') "Avg wait per response queue"

 from v$queue q, v$dispatcher d

 where q.type = 'DISPATCHER' and q.paddr = d.paddr;

20) DISPATCHER USAGE

----(If Time Busy > 50, then change MTS_MAX_DISPATCHERS in init.ora)

select name, status, idle, busy, (busy/(busy+idle))*100 "Time Busy" from v$dispatcher;

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

select count(*) "Shared Server Processes"  from v$shared_server  where status = 'QUIT';

21) high-water mark for the multi-threaded server

----select * from v$mts;

IO (file i/o should be evenly distributed across drives. )*/--------------------------------------------------------------------------

------Description:    Display IO by file

select substr(a.file#,1,2) "#", substr(a.name,1,90) "Name", a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

select substr(name,1,55) system_statistic, value from v$sysstat order by name;