Co zabírá nejvíce místa v SYSAUX tablespace?
SQL> set linesize 120
SQL> set pagesize 100
SQL>
SQL> COLUMN "Item" FORMAT A25
SQL> COLUMN "Space Used (GB)" FORMAT 999.99
SQL> COLUMN "Schema" FORMAT A25
SQL> COLUMN "Move Procedure" FORMAT A40
SQL>
SQL> 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
/ 2 3 4 5 6 7
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO .00 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK .00 SYS
EM .14 SYSMAN emd_maintenance.move_em_tblspc
EM_MONITORING_USER .00 DBSNMP
EXPRESSION_FILTER .00 EXFSYS
JOB_SCHEDULER .02 SYS
LOGMNR .01 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY .00 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .00 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS .00 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA .00 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .00 SYS
SDO .00 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .09 SYS
SM/AWR .81 SYS
SM/OPTSTAT 14.19 SYS
SM/OTHER .01 SYS
SMON_SCN_TIME .01 SYS
SQL_MANAGEMENT_BASE .00 SYS
STATSPACK .00 PERFSTAT
STREAMS .00 SYS
TEXT .00 CTXSYS DRI_MOVE_CTXSYS
TSM .00 TSMSYS
ULTRASEARCH .00 WKSYS MOVE_WK
ULTRASEARCH_DEMO_USER .00 WK_TEST MOVE_WK
WM .00 WMSYS DBMS_WM.move_proc
XDB .08 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD .00 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .00 SYS DBMS_XSOQ.OlapiMoveProc
31 rows selected.
Kolik dní zpětně se statistiky uchovávají?
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
Kolik bylo posbíráno statistik během každého dne?
SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by trunc(SAVTIME) order by 1;
TRUNC(SAV COUNT(1)
--------- ----------
21-AUG-15 25568
22-AUG-15 18817
23-AUG-15 13563
24-AUG-15 38773
25-AUG-15 29265
26-AUG-15 27653
27-AUG-15 35601
28-AUG-15 36604
29-AUG-15 29257
30-AUG-15 32559
31-AUG-15 27855
01-SEP-15 31816
02-SEP-15 27703
03-SEP-15 28951
04-SEP-15 27747
05-SEP-15 29419
06-SEP-15 37304
07-SEP-15 35372
08-SEP-15 27428
09-SEP-15 27847
10-SEP-15 30838
11-SEP-15 28461
12-SEP-15 29252
13-SEP-15 34365
14-SEP-15 29039
15-SEP-15 34989
16-SEP-15 27345
17-SEP-15 27878
18-SEP-15 35078
19-SEP-15 29436
20-SEP-15 32341
21-SEP-15 27853
22-SEP-15 28672
33 rows selected.
Nejstarší snímek
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
22-AUG-15 12.04.38.739326000 AM +02:00
Největší tabulky
SQL> col Mb form 9,999,999
SQL> col SEGMENT_NAME form a40
SQL> col SEGMENT_TYPE form a6
SQL> set lines 120
SQL> select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
2 where tablespace_name = 'SYSAUX'
3 and segment_name like 'WRI$_OPTSTAT%'
4 and segment_type='TABLE'
5 group by segment_name,segment_type order by 1 asc
6 ;
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_SYNOPSIS_PARTGRP TABLE
0 WRI$_OPTSTAT_SYNOPSIS_HEAD$ TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
2 WRI$_OPTSTAT_OPR TABLE
4 WRI$_OPTSTAT_TAB_HISTORY TABLE
8 WRI$_OPTSTAT_IND_HISTORY TABLE
96 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
4,230 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
8 rows selected.
Největší indexy
SQL> col Mb form 9,999,999
SQL> col SEGMENT_NAME form a40
SQL> col SEGMENT_TYPE form a6
SQL> set lines 120
SQL> select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
2 where tablespace_name = 'SYSAUX'
3 and segment_name like '%OPT%'
4 and segment_type='INDEX'
5 group by segment_name,segment_type order by 1 asc
6 /
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 MGMT_DB_OPTIONS_ECM_PK INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
0 WRH$_PLAN_OPTION_NAME_PK INDEX
0 I_WRI$_OPTSTAT_SYNOPHEAD INDEX
0 I_WRI$_OPTSTAT_SYNOPPARTGRP INDEX
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
4 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
4 I_WRI$_OPTSTAT_TAB_ST INDEX
7 I_WRI$_OPTSTAT_IND_ST INDEX
7 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
31 I_WRI$_OPTSTAT_HH_ST INDEX
62 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
3,265 I_WRI$_OPTSTAT_H_ST INDEX
6,806 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
15 rows selected.
Změna retence na 14 dní
exec dbms_stats.alter_stats_history_retention(14);
Smazání statistit starších jako 14 dní
exec DBMS_STATS.PURGE_STATS(SYSDATE-14);
Následně kontrola volného místa, reorganizace tabulek a rebuild indexů
SQL> select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE' 2 ;
'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACESYSAUX;'
------------------------------------------------------------------------------------------------------------------------
alter table WRH$_OPTIMIZER_ENV move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$ move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP move tablespace SYSAUX;
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace SYSAUX;
alter table MGMT_DB_OPTIONS_ECM move tablespace SYSAUX;
11 rows selected.
SQL> 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' 2 ;
'ALTERINDEX'||SEGMENT_NAME||'REBUILDONLINEPARALLEL(DEGREE14);'
------------------------------------------------------------------------------------------------------------------------
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPHEAD rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPPARTGRP rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online parallel (degree 14);
alter index WRH$_OPTIMIZER_ENV_PK rebuild online parallel (degree 14);
alter index WRH$_PLAN_OPTION_NAME_PK rebuild online parallel (degree 14);
alter index MGMT_DB_OPTIONS_ECM_PK rebuild online parallel (degree 14);
15 rows selected.
Suggestions if your SYSAUX Tablespace grows rapidly or too large [Document 1292724.1]
General Guidelines for SYSAUX Space Issues [Document 552880.1]
SYSAUX Grows Because Optimizer Stats History is Not Purged [Document 1055547.1]
Space issue in Sysaux tablespace due to Unexpected AWR size [Document 1218413.1]
Space Management In Sysaux Tablespace with AWR in Use [Document 287679.1]
SYSAUX Tablespace Grows Heavily Due To AWR [Document 852028.1]
Komentáře
