清理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)/(1024*1024) total
      from dba_data_files
      group by tablespace_name) b,
     (select tablespace_name, sum(bytes)/(1024*1024) 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)/(1024*1024)) used,
round(sum(bytes)/(1024*1024)) - round(sum(bytes)/(1024*1024)) free,
round(sum(bytes)/(1024*1024)) total,
round(sum(bytes)/(1024*1024))/round(sum(bytes)/(1024*1024))*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
/