object lock of WRI$_OPTSTAT_HISTHEAD_HISTORY
The SYSAUX and SYSTEM tablespaces have been continually growing due to
the databases where a high amount of import/exports and RMAN are taking place.
SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/
Item Space Used (GB) Schema Move Procedure
SM/OPTSTAT 12.45 SYS
To resolve this I set the stats retention period to 5 days.
SQL> exec dbms_stats.alter_stats_history_retention(5);
To find out the oldest available stats you can issue the following:
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
28-SEP-13 00.00.00.000000000 +04:00
To find out a list of how many stats are gathered for each day between the retention the current date and the oldest stats history issue the following:
SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by trunc(SAVTIME) order by 1;
TRUNC(SAV COUNT(1)
--------- ----------
28-SEP-13 2920140
29-SEP-13 843683
30-SEP-13 519834
01-OCT-13 958836
02-OCT-13 3158052
03-OCT-13 287
04-OCT-13 1253952
05-OCT-13 732361
06-OCT-13 507186
07-OCT-13 189416
08-OCT-13 2619
09-OCT-13 1491
10-OCT-13 287
11-OCT-13 126324
12-OCT-13 139556
13-OCT-13 181068
14-OCT-13 4832
15-OCT-13 258027
16-OCT-13 1152
17-OCT-13 287
18-OCT-13 27839
21 rows selected.
What has happened here is that the job run by MMON every 24hrs has checked the retention period and tried to run a purge of all stats older than the retention period. As the job has not compeleted within 5 minutes because of the high number of stats collected on each day, the job has given up and rolled back. Therefore the stats are not being purged.
As each day continues the SYSAUX table is continuing to fill up because the job fails each night and cannot purge old stats.
To resolve this we have to issue a manual purge to clear down the old statistics. This can be UNDO tablespace extensive so it’s best to keep an eye on the amount of UNDO being generated. I suggest starting with the oldest and working fowards.
To manually purge the stats issue the following:
SQL> exec dbms_stats.purge_stats(to_date('28-SEP-13','DD-MON-YY'));PL/SQL procedure successfully completed.
OR
exec DBMS_STATS.PURGE_STATS(SYSDATE-5);
Purge stats older than 5 days
(best to do this in stages if there is
a lot of data (sysdate-30,sydate-25 etc)
Then I tried rebuilding the stats indexes and tables as they would now be fragmented.
If you are only running standard edition then you can only rebuild indexes offline. Online index rebuild is a feature of Enterprise Edition.
SELECT
sum(bytes/1024/1024) Mb,
segment_name,
segment_type
FROM
dba_segments
WHERE
tablespace_name = 'SYSAUX'
AND
segment_type in ('INDEX','TABLE')
GROUP BY
segment_name,
segment_type
ORDER BY Mb;
MB SEGMENT_NAME SEGMENT_TYPE
-- --------------------------------------- ----------------
2 WRH$_SQLTEXT TABLE
2 WRH$_ENQUEUE_STAT_PK INDEX
2 WRI$_ADV_PARAMETERS TABLE
2 WRH$_SEG_STAT_OBJ_PK INDEX
3 WRI$_ADV_PARAMETERS_PK INDEX
3 WRH$_SQL_PLAN_PK INDEX
3 WRH$_SEG_STAT_OBJ TABLE
3 WRH$_ENQUEUE_STAT TABLE
3 WRH$_SYSMETRIC_SUMMARY_INDEX INDEX
4 WRH$_SQL_BIND_METADATA_PK INDEX
4 WRH$_SQL_BIND_METADATA TABLE
6 WRH$_SYSMETRIC_SUMMARY TABLE
7 WRH$_SQL_PLAN TABLE
8 WRI$_OPTSTAT_TAB_HISTORY TABLE
8 I_WRI$_OPTSTAT_TAB_ST INDEX
9 I_WRI$_OPTSTAT_H_ST INDEX
9 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
12 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
12 I_WRI$_OPTSTAT_IND_ST INDEX
12 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
14 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
20 WRI$_OPTSTAT_IND_HISTORY TABLE
360 I_WRI$_OPTSTAT_HH_ST INDEX
376 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
488 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
To reduce these tables and indexes you can issue the following:
Note that you cannot enable row movement and shrink the tables as the indexes are function based
alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
alter table WRI$_OPTSTAT_IND_HISTORY shrink space;
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
Run the rebuild table commands – note that this does cause any gather_stats jobs to fail
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move tablespace sysaux;
Script to generate rebuild statements
select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
Once completed it is best to check that the indexes (indices) are usable
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
Show how big the tables are and rebuild after stats have been purged
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
Show how big the indexes are ready for a rebuild after stats have been purged
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
No comments:
Post a Comment