Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sppurge (Statspack Purge) does not seem to free up space

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

From: <premmehrotra_at_hotmail.com>
Date: 14 Feb 2005 18:58:41 -0800
Message-ID: <1108436321.145493.9370@o13g2000cwo.googlegroups.com>

cdos_jtommaney_at_comcast.net wrote:
> 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

JT:
I am 9.2.0.5. I looked in sppurge.sql under $ORACLE_HOME/rdbms/admin, it does not have
See hardcode declaration in sppurge.sql for:
> i_extended_purge => false

anywhere. Received on Mon Feb 14 2005 - 20:58:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US