Oracle – OPSTAT Maintenance

The other day a clients production environment was having some really bad performance issues and it was causing some jobs to fail with deadlock or wait timeouts.  This seemed highly odd as the wait timeouts where on dedicated temporary tables which actually makes no sense.  After a deeper dive the system was trying to do statistics updates on the table and it was trying to purge from the OPSTAT tables.

When reviewing the OPSTAT tables which are tables associated with Oracle’s Optimizer Statistics, it turned out that there was 330 million entries in the wri$opstat_histhead_history table. Approximately 5 millions rows for 31 days, but you may say that isn’t 330 million and you’d be correct. There is a background MMON process that runs every 24 hours that which must run in 5 minutes or the task is not executed.  Basically once the table got to a specific size the maintenance job couldn’t keep up with the volume.

This was caused when the client wanted stats to be ran nightly on the entire main schema which contains 72,000 records and indexes.  So the first thing was to change the statistics job to weekly. Next the retention policy was changed to 7 days:

exec dbms_stats.alter_stats_history_retention(7);

Now to clean the necessary statistics:

exec dbms_stats.purge_stats(sysdate-7);

Yes, this is a terribly thing to run and it took 7 hours on the environment, but no outages where necessary and the environment continued to function without issues.

Here is a great script to see the size of the AWR being used (Thanks to Oracle DBA – A Lifelong learning experience):

set linesize 120

set pagesize 100

col ash form a30

col retention form a30

col snap form a30

COLUMN "Item" FORMAT A25

COLUMN "Space Used (GB)" FORMAT 999.99

COLUMN "Schema" FORMAT A25

COLUMN "Move Procedure" FORMAT A40

SELECT  occupant_name "Item",

space_usage_kbytes/1048576 "Space Used (GB)",

schema_name "Schema",

move_procedure "Move Procedure"

FROM v$sysaux_occupants

WHERE occupant_name = 'SM/AWR'

ORDER BY 1

/