Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to purge PERFSTAT.STATS$SQLTEXT table, after deleting snapshots

931315Apr 19 2012 — edited Apr 20 2012
I had an alarm on the free space of the PERFSTAT tablespace. In order to free up some space I tried to delete some old StatsPack snapshots, with the following query:

DELETE from perfstat.stats$snapshot where snap_time < sysdate - 10 ;
COMMIT;

After running it the space usage on the tablespace was not significantly reduced. I checked again and I saw that the table PERFSTAT.STATS$SQLTEXT was very big, almost 8 Gb, but all the other tables were a lot smaller. I read somewhere that the sppurge.sql procedure, that comes with Oracle, could be used to purge the statspack data, but that it comes with the lines related to PERFSTAT.STATS$SQLTEXT commented, because it is a big query and it can take a lot of undo space. I tried to run the followiung query, which I found in the forum, by Don Burleson, but it failed, because of running out of undo:

DELETE /*+ index_ffs(st)*/
FROM perfstat.stats$sqltext st
WHERE (hash_value, text_subset) NOT IN (
SELECT /*+ hash_aj full(ss) no_expand*/ hash_value, text_subset
FROM perfstat.stats$sql_summary ss
WHERE snap_id NOT IN (SELECT DISTINCT snap_id FROM perfstat.stats$snapshot)
);
COMMIT;

Is there any way to know if the PERFSTAT.STATS$SQLTEXT table has records that could be purged, and an easier way, that don't use as much undo, to purge it?

My oracle version is:
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
----------------------------------------------------------------

I. Neva
Oracle DBA
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 18 2012
Added on Apr 19 2012
5 comments
8,246 views