Oracle10g sysaux space increase and space recovery

Author: skate
Time: 2012/02/22
 
Oracle10g sysaux space increase and space recovery
 
In Oracle10, The tablespace SYSAUX is introduced, and oracle stores statistics here. This is also to better optimize the system tablespace. We can use view V $ SYSAUX_OCCUPANTS to view the table space, which data is stored in SYSAUX in oracle.
SELECT occupant_name, space_usage_kbytes from v $ SYSAUX_OCCUPANTS;
 
Statistics of SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER in oracle are stored in SYSAUX. Here we will focus on SM/OPTSTAT.
SM/OPTSTAT: used to store optimization statistics of earlier versions. In oracle10g, oracle selects an incorrect execution plan when we manually or automatically update statistics. Oracle10g indicates that the statistical information of the old version can be restored. This statistical information is saved for 31 days by default.
 
Query the storage time of the Current SM/OPTSTAT statistics
SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
31
 
Save the statistical information of SM/OPTSTAT for 10 days.
SQL> exec dbms_stats.alter_stats_history_retention (10 );
 
PL/SQL procedure successfully completed
 
SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
10
 
SQL>
Delete the statistics generated 16 days ago
SQL> exec dbms_stats.purge_stats (sysdate-16 );
PL/SQL procedure successfully completed
 
SQL>
 
View the time when the current valid statistics are generated.
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
12-2-12 07.15.49.000000000 PM + 08:00
Delete the Statistical Data seven days ago.
SQL> exec dbms_stats.purge_stats (sysdate-7 );
 
PL/SQL procedure successfully completed
 
At this time, we found that the effective statistical information time has changed.
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
14-2-12 07.15.57.000000000 PM + 08:00
 
SQL>
At this time, although the data is deleted, the space has not been recycled. How can we recycle the space?
 
The space is not released because "purge_stats" deletes data using the delete method. Although the data is gone, HWM has not been downgraded yet. Check which tables OPTSTAT uses and then reduce its high level.
SQL> SELECT s. object_name from dba_objects s where s. object_name like '% OPTSTAT %' and s. object_type = 'table'
2;
 
OBJECT_NAME
--------------------------------------------------------------------------------
WRI $ _ OPTSTAT_TAB_HISTORY
WRI $ _ OPTSTAT_IND_HISTORY
WRI $ _ OPTSTAT_HISTHEAD_HISTORY
WRI $ _ OPTSTAT_HISTGRM_HISTORY
WRI $ _ OPTSTAT_AUX_HISTORY
WRI $ _ OPTSTAT_OPR
OPTSTAT_HIST_CONTROL $
 
7 rows selected
 
SQL>
 
Use the following SQL statement to determine which table is large and move the table.
SQL> select a. table_name, a. num_rows from dba_tables a where a. tablespace_name = 'sysaux 'and a. table_name like' % OPTSTAT %'
2;
 
TABLE_NAME NUM_ROWS
----------------------------------------
WRI $ _ optstat_ops 151
WRI $ _ OPTSTAT_AUX_HISTORY 0
WRI $ _ OPTSTAT_HISTGRM_HISTORY 139933
WRI $ _ OPTSTAT_HISTHEAD_HISTORY 14406
WRI $ _ OPTSTAT_IND_HISTORY 1196
WRI $ _ OPTSTAT_TAB_HISTORY 1323
 
6 rows selected
 
SQL>
Use the following statement to find the index of the relevant table. Because the index is removed from the table, it will become invalid and you need to re-create the index.
SQL> select I. index_name, I. table_name, I. status, I. table_owner
2 from dba_indexes I, dba_objects s where I. table_name = s. object_name and s. object_name like '% OPTSTAT %' and s. object_type = 'table'
3;
 
INDEX_NAME TABLE_NAME STATUS TABLE_OWNER
--------------------------------------------------------------------------------------------------
I _WRI $ _ OPTSTAT_TAB_OBJ # _ ST WRI $ _ OPTSTAT_TAB_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_TAB_ST WRI $ _ OPTSTAT_TAB_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_IND_OBJ # _ ST WRI $ _ OPTSTAT_IND_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_IND_ST WRI $ _ OPTSTAT_IND_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_HH_OBJ_ICOL_ST WRI $ _ OPTSTAT_HISTHEAD_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_HH_ST WRI $ _ OPTSTAT_HISTHEAD_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_H_OBJ # _ ICOL # _ ST WRI $ _ OPTSTAT_HISTGRM_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_H_ST WRI $ _ OPTSTAT_HISTGRM_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_AUX_ST WRI $ _ OPTSTAT_AUX_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_OPR_STIME WRI $ _ OPTSTAT_OPR VALID SYS
 
10 rows selected
 
SQL>
 
Reduce HWM
SQL> alter table WRI $ _ OPTSTAT_TAB_HISTORY move;
SQL> alter table WRI $ _ OPTSTAT_OPR move;
SQL> alter table WRI $ _ OPTSTAT_IND_HISTORY move;
SQL> alter table WRI $ _ OPTSTAT_HISTHEAD_HISTORY move;
SQL> alter table WRI $ _ OPTSTAT_HISTGRM_HISTORY move;
SQL> alter table WRI $ _ OPTSTAT_AUX_HISTORY move;
SQL> alter table OPTSTAT_HIST_CONTROL $ move;
 
Re-Indexing
Alter index I _WRI $ _ OPTSTAT_TAB_OBJ # _ ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_TAB_ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_IND_OBJ # _ ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_IND_ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_HH_ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_H_OBJ # _ ICOL # _ ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_H_ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_AUX_ST rebuild online;
Alter index I _WRI $ _ OPTSTAT_OPR_STIME rebuild online;
 
If the index compilation fails, create indexe is required.
Use the following statements to generate DDL statements:
SQL> set long 4000
SQL> select dbms_metadata.get_ddl ('index', 'I _ WRI $ _ OPTSTAT_IND_OBJ # _ st', 'sys') from dual;
SQL> select dbms_metadata.get_ddl ('index', 'I _ WRI $ _ OPTSTAT_TAB_ST', 'sys ') from dual;
 
How to restore statistics
 
Use the following statement to find the time point of the statistics
Select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;
 
Statistics can be restored Based on Time points as needed
Execute DBMS_STATS.RESTORE_TABLE_STATS ('owner', 'table', date)
Execute DBMS_STATS.RESTORE_DATABASE_STATS (date)
Execute DBMS_STATS.RESTORE_DICTIONARY_STATS (date)
Execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS (date)
Execute DBMS_STATS.RESTORE_SCHEMA_STATS ('owner', date)
Execute DBMS_STATS.RESTORE_SYSTEM_STATS (date)
 
For example:
SQL> execute dbms_stats.restore_table_stats ('skate', 'bk _ admin', sysdate-1 );
 
PL/SQL procedure successfully completed
 
SQL>
 
Reference: [ID 329984.1], [ID 452011.1], [ID 454678.1]
 
 
 
--------- End --------