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

From: <cdos_jtommaney_at_comcast.net>
Date: 14 Feb 2005 15:40:09 -0800
Message-ID: <1108424409.697917.39030@f14g2000cwb.googlegroups.com>

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 i

101
...

DBMS_LDAP_UTL                                      *   R

104

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

---

select /*BAD_SQL_02*/ table_name, substr(column_name,1,10) col_n select /*BAD_SQL_01*/ name, substr(ltrim(text),1,5) text_substr,

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

---

select /*BAD_SQL_02*/ table_name, substr(column_name,1,10) col_n select /*BAD_SQL_01*/ name, substr(ltrim(text),1,5) text_substr,

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