How to Purge WRH$_SQL_PLAN Table in AWR Repository, Occupying Large Space in ...

郑全

In this Document
Goal
Solution
References

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Goal
How to Purge WRH$_SQL_PLAN table in AWR repository when it is occupying large space in SYSAUX tablespace.
Solution
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.

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.