Thanks again, Tom. That's exactly what I need!
Susan Yuan, December 03, 2002 - 12:45 pm UTC
minor improvement
Roman Podshivalov, December 11, 2002 - 1:43 pm UTC
It would be more clean to add nvl function to
min and max like :
select nvl(min(s.snap_id), 0) min_snap_id, nvl(max(s.snap_id), 0) max_snap_id
from perfstat.stats$snapshot s, perfstat.stats$database_instance di....etc
If you have nothing to delete this wont generate any errors.
PS: adding schema name will allow to run this script
from any account with appropriate privs.
purging stats$sqltext
Fan Zhang, July 20, 2003 - 6:46 pm UTC
Tom,
In sppurge.sql, purging stats$sqltext is commented out because of performance issue. What'd be the better solution to purge the table?
July 20, 2003 - 9:15 pm UTC
undelete it, if you want to remove it.
purging stats$sqltext
Fan Zhang, July 20, 2003 - 10:31 pm UTC
Tom,
I think I didn't ask the question clearly. The SQL 'delete from stats$sqltext ...' will take a lot resource. Do you think a PL/SQL with frequent commit will be faster and taking less resource? Thanks,
July 21, 2003 - 8:12 am UTC
plsql with frequent commit will achieve this:
a) it will generate more redo
b) it will generate more undo
c) it will take longer
committing frequently runs slower, takes longer
A reader, October 06, 2003 - 11:59 am UTC
Sagi
Sagi, November 11, 2004 - 1:28 am UTC
Tom,
How do you purge the statspack for removing say data older than 2 weeks.
I am aware that with 817 you can use sppurge. But I am looking for solution for 816.
Regards,
November 11, 2004 - 10:20 am UTC
you can use the 817 statspack scripts in 816.
meaning, you have to rebuild the statspack stuff (statsdrp.sql from the 816 stuff and then spcreate in 816 using the 817 scripts)
the way back machine shows that in 816, there wasn't any utility to purge old statistics.
Sagi, November 14, 2004 - 9:54 pm UTC
Thank you once again.
You are simply superb.
Regards,
Sagi
Purging from Statspack
vikas, June 05, 2006 - 7:17 am UTC
This discussion pointed out to me how to purge, as well as gave me some customizable features that I could add.
Perfstat tablespace space recliamable?
Sabir, August 31, 2007 - 3:35 pm UTC
Tom,
We have got alert for the perfstat tablespace reached 90%.
I did run the sppurge.sql to purge 4 months data's to reclaim the those space.
After running sppurge.sql the space remains same, but those data's have deleted and committed.
Any tips that i should take to recliam those space?
Thanks in Advance.
September 05, 2007 - 9:31 am UTC
the space is reclaimed already. A delete will never "free" space back to the tablespace - but the table you deleted from - it has plenty of space.
I run my tablespaces at 100% utilization (using autoextend). Having a tablespace at 90% is no big deal, especially if you use autoextend.
but you have already reclaimed the space - the tables statspack inserts into have lots of free space now.
sppurge
sabir, September 06, 2007 - 11:35 pm UTC
But still we are getting the alert for 90% even after running sppurge.sql and deleted 4 months data and committed.
Just to avoid the alert and when checking the free space also it highlights the same.
Even i tried coalescing the tablespace...!
Please suggest....
September 11, 2007 - 8:38 am UTC
so, the tables themselves have free space - but the allocated space in the tablespace will not change
coalescing the tablespace will NEVER increase the the amount of free space - you misunderstand the goal of that command (do not run things you don't know what they do!). that is used on dictionary managed tablespaces to coalesce two or more adjacent FREE extents into one - it will NOT create new free ones.
so what, this thing is at 90%, so what???? change the threshold, ignore it, add more space - any of those would work.
As for a means of deallocating that space . . .
KAbbott, January 25, 2011 - 10:54 am UTC
I find myself revisiting STATSPACK as a free alternative to AWR reports on some of my clients, and I have automated monitoring, sometimes out of my control, that triggers alerts when any tablespace begins to fill, so anything less than 5% free space is something that sometimes requires me to take remedial action to reclaim space as a quick fix.
If it's the PERFSTAT tables/indexes that need to be reduced, I just rebuild the indexes after running sppurge.sql. I reclaimed over 30% in the PERFSTAT tablespace on a 9i instance just now in doing so by running the output from this query.
select 'alter index '||owner||'.'||segment_name||' rebuild online;'
from dba_segments
where segment_name in (select index_name from dba_indexes where owner = 'PERFSTAT');
As I recall, you will have to omit the "online" clause of the command if you don't have Enterprise Edition, since that's an EE-only feature, but it should still work, I think.
Hope this helps.