Automating the removal of statspack snapshots

In case you find yourself in one of those shops with the good old statspack!!
Every child likes new toys but don’t throw your dummy out yet,statspack is still good.
It does eat up space if you set it to run regularly and you don’t want to make your already miserable life difficult by manually deleting snapshots.
You can be proactive by setting up a procedure to do the cleaning job for you.
Try this:

##I want to delete all snapshots that are older than 14 days in a RAC cluster for a particuler instance.
##Remember in RAC each instance has it’s own snapshot

create or replace procedure perfstat.purge_snaps as
max_snap number;
min_snap number;
database_id number;
inst_no number;
snap_number number;
ret number ;

ret := 0;
select instance_number into inst_no from v$instance;
select count(*) into ret from stats$snapshot where instance_number=inst_no and snap_time 0 then

select min(s.snap_id) , max(s.snap_id),max(di.dbid) into min_snap,max_snap,database_id
from stats$snapshot s , stats$database_instance di
where s.dbid= di.dbid
and s.instance_number= di.instance_number
and s.instance_number=inst_no
and di.startup_time = s.startup_time
and s.snap_time min_snap, i_end_snap =>max_snap, i_snap_range => true, i_extended_purge => false, i_dbid=> database_id , i_instance_number=> inst_no);

dbms_output.Put_line(‘The number of snapshots deleted are ‘||to_char(snap_number));

end if;
end; /


Who has got the locks?

**Those times as a DBA when you want to track and hunt down who or what is causing high enq locks?
Sweet mum,try this.Note that if you run this on a RAC database the information returned will be relevant only to the instance.

SELECT vh.sid locking_sid, vs.status status, vs.program program_holding, vw.sid waiter_sid, vsw.program program_waiting FROM v$lock vh, v$lock vw, v$session vs, v$session vsw WHERE (vh.id1, vh.id2) IN (SELECT id1, id2 FROM v$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM v$lock WHERE lmode = 0) AND vh.id1 = vw.id1 AND vh.id2 = vw.id2 AND vh.request = 0 AND vw.lmode = 0 AND vh.sid = vs.sid AND vw.sid = vsw.sid;

who is trashing my temp segments?

–When an Oracle db is running out of temporary tablespace and you need to find out which SQL is using the temp tablespace.

**First, check the temp space usage:

select y.Total_MB, y.Total_MB – round(x.used_blocks*8/1024) Current_Free_MB, round(used_blocks*8/1024) Current_Used_MB,round (max_used_blocks*8/1024) Max_used_MB from v$sort_segment x,(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) y;
** Then,let’s try and fish out who is using the temp segments

col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000

SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||’/’||u.sqlhash hash_value, u.segtype, u.contents, u.blocks FROM v$session s, v$tempseg_usage u WHERE s.saddr=u.session_addr order by u.blocks desc;

–As you may see,segment types differ. Most of the time, SORT is the one we need to check.
–If you are lucky enough, or the client only has 1 SQL cursor, you will get the SQL hash value in the above result.
–NB: the value of x can be got from the sid of the session with the highest sorted blocks from the previous previous query while y is the schema’s name

–However, the tempspace can be used by any open cursor in that session. The current SQL is not necessarily the culprit. In that case, we can check it from v$sql using the query below:

col hash_value for 999999999999
select hash_value, sorts, rows_processed/executions from v$sql where hash_value in (select hash_value from v$open_cursor where sid=x) and sorts > 0 and PARSING_SCHEMA_NAME=’y’ order by rows_processed/executions desc;