Re: sppurge (Statspack Purge) does not seem to free up space

Ahh, sorry, had 10g install I was playing with and gave a 10g answer to a 9i questions..

Search for this in 9.2 sppurge, the following 2 statements are commented. This has the same effect as in 10g, though.

    /* Delete any dangling SQLtext */
    /*
    Rem The following statement deletes any dangling SQL statements which

    Rem are no longer referred to by ANY snapshots. This statment has been

    Rem commented out as it can be very resource intensive.

You can try this to see if this may actually be the source of your growth:
select /*+ index_ffs(st) */ count(*)
 from perfstat.stats$sqltext st

	 where (hash_value, text_subset) not in


	 (select /*+ hash_aj full(ss) */ hash_value, text_subset

	 from perfstat.stats$sql_summary ss );

I did have some issues running against larger number of snapshots in some smaller installs, this is a code snippet that will delete a smaller number of 'dangling snapshot' sql statements. The logic will 'tend' towards 30 days of snapshot, but won't try to blow away 30 days worth if some time has gone since it was run. Std disclaimer here: test before using, no responsibilty.

cur_snap number;

• altered cursor to delete at most 1/2 day of snaps
• built loop to allow commits between snaps_ids CURSOR cu_old_snap_ids IS select snap_id from perfstat.stats$snapshot where snap_time+30 < (select sysdate from dual) and snap_time < (select min(snap_time) + .5 from perfstat.stats$snapshot ) order by snap_id asc;

begin

OPEN cu_old_snap_ids;
LOOP

	FETCH cu_old_snap_ids INTO cur_snap;
	EXIT WHEN cu_old_snap_ids%NOTFOUND;

	delete from perfstat.stats$snapshot
		 where snap_id = cur_snap;

	/*  Delete any dangling SQLtext  */
	delete /*+ index_ffs(st) */
	 from perfstat.stats$sqltext st
	 where (hash_value, text_subset) not in


	 (select /*+ hash_aj full(ss) */ hash_value, text_subset

	 from perfstat.stats$sql_summary ss where snap_id = cur_snap);

	commit;

END LOOP;
CLOSE cu_old_snap_ids;

Take care -
JT Received on Mon Feb 14 2005 - 22:52:21 CST