Statistics Advisor가 활성화되어 SYSAUX 사용량 증가 (19c)

• 현상
➢ AUTO_STATS_ADVISOR_TASK가 자동 수행되어 SYSAUX 공간 사용률 증가 가능성 있음
➢ Statistics Advisor : Optimizer Statistics의 수집에 대한 가이드를 분석 리포팅해주는 Tool
➢ 12.2.0.1 이후 Statistics Advisor가 자동으로 활성화되어 SYSAUX 사용률 증가 및 CPU 사용률 증가
➢ 수동으로도 수행 가능하므로, 자동으로 수행되지 않도록 할 수 있음

• 해결 방안
1. Patch 적용 : patch 26749785 : PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR
AUTO_STATS_ADVISOR_TASK

2. Disable 방안 : SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE’);

3. 확인 방법 : 패치를 적용해야 확인 가능함
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK’)
--------------------------------------------------------------------------------
FALSE
➢ 참고 : <Note:2686022.1> How to disable Optimizer Statistics Advisor from 12.2 Onwards.
➢ https://mikedietrichde.com/2017/08/22/oracle-optimizer-statistics-advisor-in-oracle-database-12-2-0-1/