• Professional IT Service
  • Disaster Recovery | Backupkonzepte
  • IT für Ihr Business

ORAYAPS - Oracle Performance Script

/*
oraYAPS - Yet another Performance Script Version .01.
 
This script pulls important oracle performance variables. This is a working project and is version .01
Please send enhancement suggestions to This email address is being protected from spambots. You need JavaScript enabled to view it.
 
This email address is being protected from spambots. You need JavaScript enabled to view it.
http://www.integer.org
 
copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted provided this notice and the above comments are preserved.
*/
 
 
 
prompt
prompt Rollback Segment Statistics
prompt
 
col name FOR a7
col xacts FOR 9990 head "Actv|Trans"
col InitExt FOR 990.00 head "Init|Ext|(Mb)"
col NextExt FOR 990.00 head "Next|Ext|(Mb)"
col MinExt FOR 99 head "Min|Ext"
col MaxExt FOR 999 head "Max|Ext"
col optsize FOR 9990.00 head "Optimal|Size|(Mb)"
col rssize FOR 9990.00 head "Curr|Size|(Mb)"
col hwmsize FOR 9990.00 head "High|Water|Mark|(Mb)"
col wraps FOR 999 head "W|R|A|P|S"
col extends FOR 990 head "E|X|T|E|N|D|S"
col shrinks FOR 990 head "S|H|R|I|N|K|S"
col aveshrink FOR 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits FOR 99990 head "Header|Waits"
col writes FOR 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg FOR 9990 head "AVG|Writes|Per|HedGet|(bytes)"
SET LINES 132 pages 40 feed off
break ON report
compute sum of gets ON report
compute sum of waits ON report
compute avg of aveshrink ON report
compute avg of wpg ON report
 
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
/
 
 
prompt
prompt More Rollback Segment Statistics
prompt
 
COLUMN "Rollback Segment" format a16
COLUMN "Size (Kb)" format 9,999,999
COLUMN "Gets" format 999,999,990
COLUMN "Waits" format 9,999,990
COLUMN "% Waits" format 90.00
COLUMN "# Shrinks" format 999,990
COLUMN "# Extends" format 999,990
 
SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks "# Shrinks", rs.Extends "# Extends"
FROM sys.v_$RollName rn, sys.v_$RollStat rs
WHERE rn.usn = rs.usn;
 
/
 
prompt
prompt Yet some More Rollback Segment Statistics
prompt
 
col RBS format a5 trunc
col SID format 9990
col USER format a10 trunc
col COMMAND format a78 trunc
col STATUS format a6 trunc
 
SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io;
/
 
 
Prompt
Prompt Cache hit ratio
prompt
 
SELECT 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value / (cur.value + con.value)))*100,2) "% 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';
 
/
 
Prompt
Prompt Another Buffer Cache hit ratio Calculation
prompt
 
COLUMN "logical_reads" format 99,999,999,999 
COLUMN "phys_reads" format 999,999,999 
COLUMN "phy_writes" format 999,999,999 
SELECT a.value + b.value "logical_reads", 
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / 
(a.value+b.value)) 
"BUFFER HIT RATIO" 
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE 
a.statistic# = 38 
AND 
b.statistic# = 39 
AND 
c.statistic# = 40;
 
/
 
prompt
prompt DATA Dictionary Hit Ratio should be over 90 percent
prompt
 
SELECT sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. Cache Misses",
round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",
round(sum(getmisses)*100/sum(gets)) "% MISSED"
FROM v$rowcache;
 
/
 
prompt
prompt Library Cache Miss Ratio
prompt
 
SELECT sum(pins) "executions",
sum(reloads) "Cache Misses",
round((1-(sum(reloads)/sum(pins)))*100) "LIBRARY CACHE HIT RATIO",
round(sum(reloads)*100/sum(pins)) "% Missed" 
FROM v$librarycache;
 
/
 
prompt
prompt More Library Cache stats
prompt
 
 
SELECT namespace,
trunc(gethitratio*100) "Hit Ratio",
trunc(pinhitratio*100) "Pin Hit Ratio",
reloads "Reloads"
FROM v$librarycache;
/
 
prompt
prompt Another Library Cache Calculation, total reloads should be AS close TO 0 AS possible.
prompt
 
COLUMN libcache format 99.99 heading 'Percentage' jus cen
SELECT sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
FROM v$librarycache;
 
/
 
prompt
prompt Redo Log Buffer should be AS close TO 0 AS possible
prompt
 
SELECT substr(name,1,30),value 
FROM v$sysstat WHERE name ='redo log space requests';
/
 
prompt
prompt Redo Log Contention, ALL ratios less than 1
prompt
 
SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention FOR Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
 
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
 
/
 
 
prompt
prompt Disk Vs. Memory Sorts. Try TO keep the disk/memory ratio TO less than .10 BY increasing the sort_area_size 
prompt
 
 
SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990
 
SELECT name, value FROM v$sysstat 
WHERE name IN ('sorts (memory)', 'sorts (disk)');
 
/
 
prompt
prompt Initialization Parameters
prompt
 
SELECT substr(name,1,35) "Parameter" ,substr(value,1,35) "Value" FROM v$parameter ORDER BY name ASC;
 
/