SYSAUX枯渇の調査

 

https://www.system-exe.co.jp/dbexpert11/
https://www.intellilink.co.jp/column/oracleletter/2014/063000.aspx
https://qiita.com/Western24/items/2e2764a34a3cfe7d80ce
https://qiita.com/ts_carp/items/1b1f3ec0b8ec55c56c67
https://qiita.com/danishi/items/98f457397989b5b7fc97

https://taliphakanozturken.wordpress.com/2024/03/26/how-to-clean-sysaux-tablespace-purging-data-from-wri_adv_objects/
https://www.pro-oracle.com/2023/05/oracle-database-122-sysaux-space-usage.html
https://manual.retail-soft.pro/spaces/UsMan/pages/73894565/Oracle19c+manually+cleans+up+large+objects+in+PDB+Sysaux+such+as+WRI+_adv_Objects+ORA-65040

 

-- 1. 枯渇の主な理由

1. 監査
2. AWR
3. アドバイザ
4. 統計情報

 

-- 2. 調査SQL

-- SYSAUX表領域の使用コンポーネントの確認
set  markup csv on quote on

select OCCUPANT_NAME, OCCUPANT_DESC 
from v$sysaux_occupants
order by space_usage_kbytes desc, occupant_name;

select occupant_name, schema_name, space_usage_kbytes/1024 space_usage_mb
from v$sysaux_occupants
order by space_usage_kbytes desc, occupant_name;

select *
from V$SYSAUX_OCCUPANTS
order by space_usage_kbytes desc, occupant_name;

 

-- SYSAUX表領域の使用スキーマ確認
select owner, sum(bytes)/1024/1024 as owner_size
from   dba_segments
where  tablespace_name = 'SYSAUX'
group  by owner
order  by sum(bytes) desc;

-- SYSAUXレポート作成 ※内部でpackage等を作成
@?/rdbms/admin/awrinfo.sql

-- 表領域空きサイズ確認

select
d.tablespace_name,
d.gbytes "total[GB]",
NVL(f.gbytes,0) "free[GB]",
d.gbytes - NVL(f.gbytes,0) "used[GB]",
(1 - (NVL(f.gbytes,0)/d.gbytes))*100 "used_percent"
from
(SELECT tablespace_name, (SUM(bytes)/(1024*1024*1024)) gbytes
FROM dba_data_files GROUP BY tablespace_name) d
left outer join
(SELECT tablespace_name, (SUM(bytes)/(1024*1024*1024)) gbytes
FROM dba_free_space GROUP BY tablespace_name) f
on d.tablespace_name=f.tablespace_name
where d.tablespace_name = 'SYSAUX'
;

-- 断片化確認

SELECT
COUNT(bytes) D,
tablespace_name
FROM
dba_free_space
where TABLESPACE_NAME = 'SYSAUX'
GROUP BY
tablespace_name
;

 

-- 3. 枯渇シミュレーション

-- 3.1 監査
show parameter audit
alter system set audit_trail=DB,EXTENDED scope=spfile;
shutdown immediate
startup

AUDIT ALL STATEMENTS BY test BY ACCESS WHENEVER SUCCESSFUL;

select count(*) from tab1;
大量実行

select count(*) from sys.aud$;
select count(*) from AUDSYS.AUD$UNIFIED;

-- 3.2 AWR

exec  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
大量実行

select min(snap_id), max(snap_id) ,count(snap_id)
from dba_hist_snapshot;

-- 3.3 アドバイザ

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  for i in 1..100000 loop
    v_tname := 'opt_adv_task__' || to_char(i);
    v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  end loop;
END;
/

 

SELECT count(*) FROM USER_ADVISOR_TASKS;

-- 3.4 統計情報

BEGIN
  for i in 1..1000 loop
    dbms_stats.gather_schema_stats('TEST');
  end loop;
END;
/

select count(*) from all_tab_stats_history
where OWNER = 'TEST';