Database Performance Tuning Scripts

OraYAPS – Yet another Oracle Performance Tuning script

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…

Display and release DBMS_LOCK locks

set serveroutput on size 50000 col name format a30 set veri off feed off pagesize 50000 cle scr prompt Please enter the user’s login id: select * from sys.dbms_lock_allocated where upper(name) like upper(‘%&userid.%’); prompt Please enter lockid to release: declare rc integer; begin rc := dbms_lock.release(‘&lockid’); if rc = 0 then dbms_output.put_line(‘Success.’); elsif rc =…

Another lock monitor script

set trimspool on ttitle off set linesize 155 set pagesize 60 column osuser heading ‘OS|Username’ format a7 truncate column process heading ‘OS|Process’ format a7 truncate column machine heading ‘OS|Machine’ format a10 truncate column program heading ‘OS|Program’ format a25 truncate column object heading ‘Database|Object’ format a25 truncate column lock_type heading ‘Lock|Type’ format a4 truncate column mode_held…

Display database locks and latches (with tables names, etc)

set pagesize 23 set pause on set pause ‘Hit any key…’ col sid format 999999 col serial# format 999999 col username format a12 trunc col process format a8 trunc col terminal format a12 trunc col type format a12 trunc col lmode format a4 trunc col lrequest format a4 trunc col object format a73 trunc select…

Lookup database details for a given Unix process id

set serveroutput on size 50000 set echo off feed off veri off accept 1 prompt ‘Enter Unix process id: ‘ DECLARE v_sid number; s sys.v_$session%ROWTYPE; p sys.v_$process%ROWTYPE; BEGIN begin select sid into v_sid from sys.v_$process p, sys.v_$session s where p.addr = s.paddr and (p.spid = &&1 or s.process = ‘&&1’); exception when no_data_found then dbms_output.put_line(‘Unable…

Display database sessions using rollback segments

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…

Rollback segment statistics

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 Prompt Prompt Rollback Segment Statistics… 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”,…