Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: sppurge (Statspack Purge) does not seem to free up space
The SQL statements and plan information are not purged automatically
with sppurge, as they can be associated with more then one snap_id.
See hardcode declaration in sppurge.sql for:
i_extended_purge => false
There are three statements within sppurge.sql that you can run to clean
up the statements, or copy off sppurge and declare i_extended_purge
=> true.
This will not immediately lower the table allocations (see hwm notes ), however it should allow stabilizing growth.
SYSTEM_at_JTDB(153)>exec statspack.snap(i_snap_level=>6);
PL/SQL procedure successfully completed.
SYSTEM_at_JTDB(153)>select s.snap_id from perfstat.stats$snapshot s order by snap_id;
SNAP_ID
1
SYSTEM_at_JTDB(153)>select /*BAD_SQL_01*/ name, substr(ltrim(text),1,5)
text_substr, count(*)
2 from dba_source
3 group by name, substr(ltrim(text),1,5)
4 having count(*) between 100 and 105;
NAME TEXT_COUNT(*)
-------------------------------------------------- ----- ---------------- HTP end i101
DBMS_LDAP_UTL * R104
10 rows selected.
SYSTEM_at_JTDB(153)>select /*BAD_SQL_02*/ table_name,
substr(column_name,1,10) col_name, count(*)
2 from dba_tab_columns
3 group by table_name, substr(column_name,1,10)
4 having count(*) between 9 and 10;
TABLE_NAME COL_NAME COUNT(*) ------------------------------ ---------- ---------------- EXU10MVL MVL_OLDEST 10 ... SDO_INDEX_METADATA_TABLE SDO_RTREE_ 9
11 rows selected.
SYSTEM_at_JTDB(153)>exec statspack.snap(i_snap_level=>6);
PL/SQL procedure successfully completed.
SYSTEM_at_JTDB(153)>select sql_text from perfstat.STATS$SQLTEXT WHERE upper(SQL_TEXT) LIKE '%BAD_SQL%';
SQL_TEXT
2 rows selected.
SYSTEM_at_JTDB(153)>execute statspack.purge( i_begin_snap => 1 , i_end_snap => 2, i_snap_range => true, i_extended_purge => false, i_dbid => 2449909381, i_instance_number => 1);
PL/SQL procedure successfully completed.
SYSTEM_at_JTDB(153)>select sql_text from perfstat.STATS$SQLTEXT WHERE upper(SQL_TEXT) LIKE '%BAD_SQL%';
SQL_TEXT
2 rows selected.
SYSTEM_at_JTDB(153)> delete from stats$sqltext st1 2 where (st1.old_hash_value, st1.text_subset) in 3 (select /*+ index_ffs(st) */ 4 st.old_hash_value, st.text_subset 5 from stats$sqltext st 6 where (old_hash_value, text_subset) not in 7 (select /*+ hash_aj index_ffs(ss) */ 8 distinct old_hash_value, text_subset 9 from stats$sql_summary ss 10 ) 11 and st.piece = 0 12 );
305 rows deleted.
SYSTEM_at_JTDB(153)>
SYSTEM_at_JTDB(153)> delete from perfstat.stats$sql_plan sp1 2 where sp1.plan_hash_value in 3 (select /*+ index_ffs(sp) */ 4 sp.plan_hash_value 5 from stats$sql_plan sp 6 where plan_hash_value not in 7 (select /*+ hash_aj */ 8 distinct plan_hash_value 9 from stats$sql_plan_usage spu 10 ) 11 and sp.id = 0 12 );
420 rows deleted.
SYSTEM_at_JTDB(153)> delete /*+ index_ffs(sso) */ 2 from stats$seg_stat_obj sso 3 where (dbid, dataobj#, obj#) not in 4 (select /*+ hash_aj full(ss) */ 5 dbid, dataobj#, obj# 6 from stats$seg_stat ss 7 );
0 rows deleted.
SYSTEM_at_JTDB(153)>select sql_text from perfstat.STATS$SQLTEXT WHERE upper(SQL_TEXT) LIKE '%BAD_SQL%';
no rows selected
Good luck -
JT
Received on Mon Feb 14 2005 - 17:40:09 CST
![]() |
![]() |