清理SYSAUX（其二）

【是由于升级到19C之后index扩大导致，重建index】
1.
--检查表空间使用情况
column used format 999,999,999 heading 'USED(MB)'
column free format 999,999,999 heading 'FREE(MB)'
column total format 999,999,999 heading 'TOTAL(MB)'
column usedpct format 999 heading 'USED(%)'
break on report
compute sum label 'TOTAL(MB)' of used free total on report
select a.tablespace_name,
round(b.total) - round(nvl(c.free,0)) used,
round(nvl(c.free,0)) free,
round(b.total) total,
(round(b.total) - round(nvl(c.free,0)))/round(b.total)100 usedpct
from dba_tablespaces a,
(select tablespace_name, sum(bytes)/(10241024) total
from dba_data_files
group by tablespace_name) b,
(select tablespace_name, sum(bytes)/(10241024) free
from dba_free_space
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name (+)
union all
select tablespace_name||' (TEMPFILE)' tablespace_name,
round(sum(bytes)/(10241024)) used,
round(sum(bytes)/(10241024)) - round(sum(bytes)/(10241024)) free,
round(sum(bytes)/(10241024)) total,
round(sum(bytes)/(10241024))/round(sum(bytes)/(10241024))100 usedpct
from dba_temp_files
group by tablespace_name||' (TEMPFILE)'
having count(*) > 0
order by 1;

--具体内容
set lines 120
col occupant_name format a30
select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;

col segment_name format a30
col owner format a10
col tablespace_name format a10
col segment_type format a15
select segment_name,owner,tablespace_name,bytes/1024/1024 ""SIZE(MB)"",segment_type from dba_segments where tablespace_name='SYSAUX' order by bytes desc;

--job auto advisor 运行状态
col task_name format a35
select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;

col TASK_NAME format a25
col parameter_name format a35
col parameter_value format a20
set lines 120
select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';

SELECT EXECUTION_NAME, EXECUTION_END, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK'
ORDER BY 2;

select * from (select segment_name,PARTITION_NAME, 
sum(bytes) / 1024 / 1024 total_mb, 
tablespace_name 
from dba_segments 
where tablespace_name in = 'SYSAUX'
group by segment_name, tablespace_name 
order by 3 desc) 
where rownum <= 20;

SELECT occupant_name ""Item"", 
 space_usage_kbytes / 1048576 ""Space Used (GB)"", 
 schema_name ""Schema"", 
 move_procedure ""Move Procedure"" 
 FROM v$sysaux_occupants 
 ORDER BY space_usage_kbytes desc;

---是由于升级到19C之后index扩大导致，重建index

==处理办法=

1. Measure the size of the indexes below pre rebuild:
   set lines 1000

select Ai.index_name, sum(u.bytes)/1024/1024 ""SIZE"" from dba_segments u, all_indexes ai
where u.SEGMENT_NAME = ai.index_name
and ai.index_name in ('I_WRI$_OPTSTAT_H_ST','I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST')
group by ai.index_name
order by 2
/
2) Collect and save metadata for indexes:

set long 1000
set pagesize 0
select DBMS_METADATA.GET_DDL('INDEX','I_WRI$_OPTSTAT_H_ST') from DUAL;
select DBMS_METADATA.GET_DDL('INDEX','I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST') from DUAL;
select DBMS_METADATA.GET_DDL('INDEX','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST') from DUAL;
select DBMS_METADATA.GET_DDL('INDEX','I_WRI$_OPTSTAT_HH_ST') from DUAL;

CREATE INDEX ""SYS"".""I_WRI$_OPTSTAT_H_ST"" ON ""SYS"".""WRI$_OPTSTAT_HISTGRM_HISTORY"" (SYS_EXTRACT_UTC(""SAVTIME""))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE ""SYSAUX"";

CREATE INDEX ""SYS"".""I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST"" ON ""SYS"".""WRI$_OPTSTAT_HISTGRM_HISTORY"" (""OBJ#"", ""INTCOL#"", SYS_EXTRACT_UTC(""SAVTIME""), ""COLNAME"")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE ""SYSAUX"";

CREATE UNIQUE INDEX ""SYS"".""I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST"" ON ""SYS"".""WRI$_OPTSTAT_HISTHEAD_HISTORY"" (""OBJ#"", ""INTCOL#"", SYS_EXTRACT_UTC(""SAVTIME""), ""COLNAME"")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE ""SYSAUX"";

CREATE INDEX ""SYS"".""I_WRI$_OPTSTAT_HH_ST"" ON ""SYS"".""WRI$_OPTSTAT_HISTHEAD_HISTORY"" (SYS_EXTRACT_UTC(""SAVTIME""))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE ""SYSAUX"";

3)Drop indexes:

drop index SYS.I_WRI$_OPTSTAT_H_ST;
drop index SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST;
drop index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST;
drop index SYS.I_WRI$_OPTSTAT_HH_ST;

4. Create these indices using Metadata collected as per step 2

5. Gather statistics for two tables:
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','WRI$_OPTSTAT_HISTGRM_HISTORY');
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','WRI$_OPTSTAT_HISTHEAD_HISTORY');

6. Measure the size of the indexes below post rebuild:

select Ai.index_name, sum(u.bytes)/1024/1024 ""SIZE"" from dba_segments u, all_indexes ai
where u.SEGMENT_NAME = ai.index_name
and ai.index_name in ('I_WRI$_OPTSTAT_H_ST','I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST')
group by ai.index_name
order by 2
/