To purge WRH$_SQL_PLAN table (old awr reports)

From the diag0 trace logs, WRH$_SQL_PLAN table may cause the database hangs. Please purge the table after taking a backup of any good sql plans (if any).

1> Please keep a backup of good sql plans so that you can restore it after purging WRH$_SQL_PLAN table

2> Please use one of the option to Purge the WRH$_SQL_PLAN table (Doc ID 1478615.1).

OPTION 1
========

Try executing the dbms_workload_repository.purge_sql_details() as per below:

First count the number in WRH$_SQL_PLAN table.

SQL> select count(*) from sys.wrh$_sql_plan;

SQL>SELECT dbid FROM v$database;

SQL> exec dbms_workload_repository.purge_sql_details(1000, &dbid);               --------------------------- Enter the dbid from above query.

SQL> select count(*) from sys.wrh$_sql_plan;


See if this deletes any rows from the WRH$_SQL_PLAN table. It is needed that you shrink the table and check the table size in the end to reclaim the space.

OPTION 2
========


Another option is to remove the old wrh$_sql_plan data by running simple "delete from wrh$_sql_plan where snap_id between x and y" manually (delete little by little). After deletion of old records from wrh$_sql_plan table, the table needs to be shrinked.  Then check the table size in the end.

Below SQLs will help in identifying the range of snapshots that can deleted from WRH$_SQL_PLAN.

SQL> select snap_id ,timestamp from  wrh$_sql_plan where snap_id=(select min(snap_id) from  wrh$_sql_plan);

The above query gives the oldest available data in table WRH$_SQL_PLAN.

This data could be older than the last available snapshot in the AWR repository.

SQL> select dbid,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where SNAP_ID=&snap_id;The above query gives the oldest available AWR data from dba_hist_snapshot view.

Example for the delete process:

SQL> select min(TIMESTAMP) from wrh$_sql_plan;
MIN(TIMESTAMP)
------------------
06-JUN-08

1 row selected.

SQL> select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot;
MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
31-JUL-12 12.00.24.903 AM

SQL> select count(*) from sys.wrh$_sql_plan;
COUNT(*)
----------
27147

SQL> delete from wrh$_sql_plan where trunc(TIMESTAMP) < (select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot);
7449 rows deleted.

SQL> commit;
Commit complete.

SQL> select min(TIMESTAMP) from wrh$_sql_plan;
MIN(TIMESTAMP)
------------------
01-AUG-12

1 row selected.

SQL> select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot;
MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
31-JUL-12 12.00.24.903 AM

1 row selected.

Delete the older snap_ids from the table to reclaim space in the table. Then shrink the table after delete to reclaim the space.

Also, set the purging policy as per the Database usage.