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

/

Oracle TableSpace Maintenance

Normally, I don’t get to worried about the size of TableSpaces, as they grow and shrink all the time. However, recently while setting up some automation scripts I was having problems with multiple databases being on the same server due to space issues.  So I decided it was time to take a review of the table spaces and see if there was some that I could easily do some quick maintenance to so that I could free up some space.

As always I found some guide examples out there using good old google.  The main item that I found that I could easily and quickly adjust was the undo tablespace.  Since  these scripts were for refreshing the environment and these environments are used by a very small subset of people, have a 32gb undo tablespace seemed a wee-bit excessive. So after the database clone is done I execute the following commands:

CREATE UNDO TABLESPACE undotbs2 DATAFILE ‘/u01/oracle/mynewdatabase/datafiles/undotbs02.dbf’ SIZE 2G AUTOEXTEND ON NEXT 250M;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

After a stop and restart of the database 30 gigabytes of space are instantly reclaimed. So for 3 databases that is almost 100gb of dead space that is now free!

I also found a excellent bit of SQL for Oracle to show usage of the tablespace from Oracle-Base:

SELECT tablespace_name, size_mb, free_mb, max_size_mb, max_free_mb, TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (SELECT a.tablespace_name, b.size_mb, a.free_mb, b.max_size_mb,a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name, TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) ORDER BY tablespace_name;


For Temporary Tablespaces:

Maintenance on Temporary Tablespaces is a little easier than working with regular table spaces, you can use the following to easily shrink a temporary space:

ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

or:

ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/oracle/mynewdatabase/datafiles/temp01.dbf’ KEEP 40M;

If you don’t specify a KEEP option it while shrink to the smallest possible size it can.

For Regular Tablespaces

I am simply going to refer to guide I was using from Oracle-Base.  There are many ways to do maintenance on these tablespaces, and since I was looking for quick and simple ideas, the temporary and undo tablespace shrink was by far the easiest to do, but it is always a good strategy to do tablespace maintenance.