오라클 19c SYSAUX 테이블스페이스 SM/ADVISOR 용량증가 시 대처방안

2021. 12. 17. 13:59 내맘대로긍정

프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.6 (64bit)

DB 환경 : Oracle Database 19.11.0.0

에러 : 오라클 19c SYSAUX 테이블스페이스 SM/ADVISOR 용량증가 시 대처방안

SYSAUX tablespace 용량이 SM/ADVISOR으로 인해 증가한 경우 대처방안

*모든 작업을 sysdba에서 함

alert log 확인

1
2
3
4
5
6
7
8

$ vi /app/oracle/diag/rdbms/orcl19/ORCL19/trace/alert_ORCL19.log 
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_641"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

alert log에 위 메세지가 발생함

sysaux 테이블스페이스 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

SQL> 
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
    select a.tablespace_name col0,
      b.bytes/1024/1024 col1,
      a.bytes/1024/1024 col3,
      b.max/1024/1024 col5,
      (b.bytes - a.bytes)/1024/1024 col2,
      (a.bytes/ b.bytes) * 100 col4
    from (
        select tablespace_name,
          sum(bytes) bytes
        from dba_free_space
        group by tablespace_name) a,
      (
        select tablespace_name,
          sum(bytes) bytes,
          sum(maxbytes) max
        from dba_data_files
        where tablespace_name = 'SYSAUX'
        group by tablespace_name) b
    where b.tablespace_name=a.tablespace_name)
order by col4 asc;
 
TS Name         Size(MB)      Free(MB)        Used(MB)       Max(MB)      % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX            21,200.00     215.62         20984.38     32,767.98        1.12

현재 sysaux 테이블스페이스가 20gb 사용중임

sysaux 테이블스페이스 내 세부 사용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

SQL>
col occupant_name for a20
col schema_name for a20
select occupant_name, schema_name, round(space_usage_kbytes/1024) MB 
from v$sysaux_occupants 
where space_usage_kbytes >= 1
order by 3 desc;
 
OCCUPANT_NAME         SCHEMA_NAME          MB
-------------------- -------------------- ----------
SM/ADVISOR         SYS            20163
SM/AWR             SYS             355
SM/OPTSTAT         SYS             293
XDB             XDB              63
SM/OTHER         SYS              35
SQL_MANAGEMENT_BASE  SYS              33
LOGMNR             SYSTEM              11
JOB_SCHEDULER         SYS               7
WM             WMSYS               7
PL/SCOPE         SYS               3
SMON_SCN_TIME         SYS               3
AO             SYS               2
STREAMS          SYS               2
LOGSTDBY         SYSTEM               2
AUTO_TASK         SYS               1
EM_MONITORING_USER   DBSNMP               1
 
16 rows selected.

SM/ADVISOR가 많은 용량을 차지하고있음

SM/ADVISOR 세부 용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL>
col segment_name format a30
col owner format a10
col tablespace_name format a10
col segment_type format a15
select * from 
(select segment_name,owner,tablespace_name,bytes/1024/1024 "size(mb)", segment_type 
from dba_segments 
where tablespace_name='SYSAUX' 
order by bytes desc) 
where rownum<=10;
 
SEGMENT_NAME                   OWNER      TABLESPACE  SIZE(MB)   SEGMENT_TYPE
------------------------------ ---------- ----------  ---------- ---------------
WRI$_ADV_OBJECTS               SYS        SYSAUX      14500       TABLE
WRI$_ADV_OBJECTS_IDX_01        SYS        SYSAUX      2700       INDEX
WRI$_ADV_OBJECTS_IDX_02        SYS        SYSAUX      2700       INDEX

위 세그먼트들의 용량이 많음을 확인가능함

SM/ADVISOR 란?

SM/ADVISOR는 advisor frameworks 테이블을 참조함

SQL Tuning Advisor, SQL Access Advisor, ADDM과 같은 advisor가 이 인프라를 사용함

이러한 advisor는 AWR의 데이터를 사용하여 advise를 작성하지만 일단 생성되면 자체 테이블에 저장되고 SM/ADVISOR로 분류됨

원문

This refers to the advisor frameworks tables. Advisors like SQL Tuning Advisor, SQL Access Advisor, ADDM use this infrastructure. These advisors use the data in the AWR to build their advise, but once created this will be stored in their own tables, and that is classed under this heading.

해결 방법 : AUTO_STATS_ADVISOR_TASK 삭제 후 재생성 또는 보존기한 단축

방법1_1. AUTO_STATS_ADVISOR_TASK 데이터 삭제

기존 AUTO_STATS_ADVISOR_TAS 확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name 
from sys.wri$_adv_tasks 
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
 
NAME                     CTIME          HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
AUTO_STATS_ADVISOR_TASK          10-DEC-21    CMD     SYS
INDIVIDUAL_STATS_ADVISOR_TASK         11-JAN-21    CMD     SYS

AUTO_STATS_ADVISOR_TASK 가 존재함

AUTO_STATS_ADVISOR_TASK 데이터 삭제

1
2
3
4
5
6
7
8
9
10

SQL>
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
PL/SQL procedure successfully completed.

위 명령 실행 시 WRI$_ADV_OBJECTS 테이블의 데이터가 지워짐

용량에 따라 몇분정도 소요됨

AUTO_STATS_ADVISOR_TASK 를 사용하지 않으려면

아래나오는 EXEC DBMS_STATS.INIT_PACKAGE(); 을 실행하지 않으면됨

TASK 확인

1
2
3
4
5
6
7
8
9
10
11

SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name 
from sys.wri$_adv_tasks 
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
 
NAME                     CTIME          HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
INDIVIDUAL_STATS_ADVISOR_TASK         11-JAN-21    CMD     SYS

데이터 삭제 작업 후 확인시 AUTO_STATS_ADVISOR_TASK 가 없음

WRI$_ADV_OBJECTS 테이블 재생성 및 인덱스 rebuild

1
2
3
4
5
6

SQL>
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD; 

TASK 재확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name 
from sys.wri$_adv_tasks 
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
 
NAME                     CTIME          HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
AUTO_STATS_ADVISOR_TASK          17-DEC-21    CMD     SYS
INDIVIDUAL_STATS_ADVISOR_TASK         11-JAN-21    CMD     SYS

AUTO_STATS_ADVISOR_TASK 가 다시 생성됨

작업 후 sysaux tablespace 용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

SQL> 
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
    select a.tablespace_name col0,
      b.bytes/1024/1024 col1,
      a.bytes/1024/1024 col3,
      b.max/1024/1024 col5,
      (b.bytes - a.bytes)/1024/1024 col2,
      (a.bytes/ b.bytes) * 100 col4
    from (
        select tablespace_name,
          sum(bytes) bytes
        from dba_free_space
        group by tablespace_name) a,
      (
        select tablespace_name,
          sum(bytes) bytes,
          sum(maxbytes) max
        from dba_data_files
        where tablespace_name = 'SYSAUX'
        group by tablespace_name) b
    where b.tablespace_name=a.tablespace_name)
order by col4 asc;
 
TS Name         Size(MB)      Free(MB)        Used(MB)       Max(MB)      % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX            21,200.00     19215.62         1984.38     32,767.98        90.63

sysaux 사용량이 줄어듬

이 방법의 경우 삭제 후 한달이 지나면 다시 이전용량까지 sysaux가 증가할 수도 있음

(마이그레이션 등 일시적으로 통계정보를 대량 import 한 경우 제외)

방법1_2. AUTO_STATS_ADVISOR_TASK 데이터를 삭제하는 방법(수동 백업 삭제)

WRI$_ADV_OBJECTS 테이블에 AUTO_STATS_ADVISOR_TASK에 데이터가 너무 많으면 엄청난 UNDO가 필요함

이경우 과도한 redo/undo 생성을 방지하면서 데이터를 제거하기 위해 아래 방법을 사용할 수 있음

아래 작업을 수행 시 downtime 은 필요 없음, 그러나 피크 시간이 아닌 시간에 이 작업을 수행해야함

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

Auto Stats Advisor 태스크에 대한 WRI$_ADV_OBJECTS의 row수 확인
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
 
WRI$_ADV_OBJECTS에서 CTAS를 사용하여 row를 AUTO_STATS_ADVISOR_TASK와 분리
SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
 
CTAS로 만들어진 백업용 WRI$_ADV_OBJECTS_NEW 테이블 count 확인
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;
 
WRI$_ADV_OBJECTS 테이블 truncate
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;
 
백업용 WRI$_ADV_OBJECTS_NEW 테이블 데이터를 이용해서 WRI$_ADV_OBJECTS 데이터를 복원
SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;
SQL> COMMIT;
 
*19c 이상의 db에선 WRI$_ADV_OBJECTS 테이블에 SQL_ID_VC 컬럼이 추가되어 아래 구문을 사용해야함
SQL> INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,
"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18",
"ATTR19","ATTR20","OTHER" ,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" ) 
SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,
"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,
"SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;
SQL> COMMIT;
 
인덱스, PK rebuild
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
 
위 명령이 정상적으로 수행된 후 백업 테이블 삭제
SQL> DROP TABLE WRI$_ADV_OBJECTS_NEW PURGE;

이 방법의 경우 삭제 후 한달이 지나면 다시 이전용량까지 sysaux가 증가할 수도 있음

(마이그레이션 등 일시적으로 통계정보를 대량 import 한 경우 제외)

방법2. AUTO_STATS_ADVISOR_TASK 보존기한 단축

기존 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col value for a10
select name,value 
from wri$_adv_parameters 
where task_id in 
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS') 
and name = 'EXECUTION_DAYS_TO_EXPIRE';
 
NAME                     VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE         30

기본값으로 30일로 설정되어있음

1일로 변경(원하는 날짜로 변경)

1
2
3

SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 1);
 
PL/SQL procedure successfully completed.

1일이 지난 데이터는 만료된 것으로 표시됨

자동 제거 작업은 보존기한이 초과한(만료된) 데이터를 제거함

변경 후 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col value for a10
select name,value 
from wri$_adv_parameters 
where task_id in 
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS') 
and name = 'EXECUTION_DAYS_TO_EXPIRE';
 
NAME                     VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE         1

정상적으로 변경됨

수동으로 만료된 데이터 제거

1
2
3

SQL> exec PRVT_ADVISOR.DELETE_EXPIRED_TASKS;
 
PL/SQL procedure successfully completed.

용량에 따라 몇분정도 소요됨

AUTO_STATS_ADVISOR_TASK 데이터 삭제

1
2
3
4
5
6
7
8
9
10

SQL>
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
 
PL/SQL procedure successfully completed.

위 명령 실행 시 WRI$_ADV_OBJECTS 테이블의 데이터가 지워짐

용량에 따라 몇분정도 소요됨

AUTO_STATS_ADVISOR_TASK 를 사용하지 않으려면

아래나오는 EXEC DBMS_STATS.INIT_PACKAGE(); 을 실행하지 않으면됨

TASK 확인

1
2
3
4
5
6
7
8
9
10
11

SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name 
from sys.wri$_adv_tasks 
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
 
NAME                     CTIME          HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
INDIVIDUAL_STATS_ADVISOR_TASK         11-JAN-21    CMD     SYS

데이터 삭제 작업 후 확인시 AUTO_STATS_ADVISOR_TASK 가 없음

WRI$_ADV_OBJECTS 테이블 재생성 및 인덱스 rebuild

1
2
3
4
5
6

SQL>
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD; 

TASK 재확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col how_created for a10
col owner_name for a10
select name, ctime, how_created, owner_name 
from sys.wri$_adv_tasks 
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
 
NAME                     CTIME          HOW_CREATE OWNER_NAME
---------------------------------------- ------------ ---------- ----------
AUTO_STATS_ADVISOR_TASK          17-DEC-21    CMD     SYS
INDIVIDUAL_STATS_ADVISOR_TASK         11-JAN-21    CMD     SYS

AUTO_STATS_ADVISOR_TASK 가 다시 생성됨

테이블 재생성 후 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col value for a10
select name,value 
from wri$_adv_parameters 
where task_id in 
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS') 
and name = 'EXECUTION_DAYS_TO_EXPIRE';
 
NAME                     VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE         30

재생성 했기 때문에 30일로 원상복구됨

다시 1일로 변경(원하는 날짜로 변경)

1
2
3

SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 1);
 
PL/SQL procedure successfully completed.

1일이 지난 데이터는 만료된 것으로 표시됨

자동 제거 작업은 보존기한이 초과한(만료된) 데이터를 제거함

변경 후 AUTO_STATS_ADVISOR_TASK 의 보존기한 확인

1
2
3
4
5
6
7
8
9
10
11
12

SQL>
col name for a40
col value for a10
select name,value 
from wri$_adv_parameters 
where task_id in 
(select id from wri$_adv_tasks where name = 'AUTO_STATS_ADVISOR_TASK' and owner_name = 'SYS') 
and name = 'EXECUTION_DAYS_TO_EXPIRE';
 
NAME                     VALUE
---------------------------------------- ----------
EXECUTION_DAYS_TO_EXPIRE         1

정상적으로 변경됨

작업 후 sysaux tablespace 용량 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

SQL> 
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
    select a.tablespace_name col0,
      b.bytes/1024/1024 col1,
      a.bytes/1024/1024 col3,
      b.max/1024/1024 col5,
      (b.bytes - a.bytes)/1024/1024 col2,
      (a.bytes/ b.bytes) * 100 col4
    from (
        select tablespace_name,
          sum(bytes) bytes
        from dba_free_space
        group by tablespace_name) a,
      (
        select tablespace_name,
          sum(bytes) bytes,
          sum(maxbytes) max
        from dba_data_files
        where tablespace_name = 'SYSAUX'
        group by tablespace_name) b
    where b.tablespace_name=a.tablespace_name)
order by col4 asc;
 
TS Name         Size(MB)      Free(MB)        Used(MB)       Max(MB)      % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX            21,200.00     19215.62         1984.38     32,767.98        90.63

sysaux 사용량이 줄어듬

방법3. AUTO_STATS_ADVISOR_TASK disable

12.2 이전에는 AUTO_STATS_ADVISOR_TASK 를 비활성화 하는 방법이 없었지만

12.2 부터는 26749785 패치를 적용하면 비활성화가 가능해짐(버전별 OPTIMIZER 패치가 나옴)

패치가 적용되지 않은 상태에서 disable 시도

1
2
3
4
5
6
7
8
9

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
BEGIN dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); END;
 
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at "SYS.DBMS_STATS", line 52489
ORA-06512: at "SYS.DBMS_STATS", line 52737
ORA-06512: at line 1

안됨

패치 적용(현재 db 버전이 19.11 이어서 19.11용 패치파일 다운로드 후 적용)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

$ cd /home/oracle/media
$ unzip p26749785_1911000DBRU_Generic.zip 
$ cd 26749785
$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
 
 
Oracle Home       : /app/oracle/product/19.0.0
Central Inventory : /shcsw/oracle/app/oraInventory
   from           : /app/oracle/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.24
OUI version       : 12.2.0.7.0
Log file location : /app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-27_09-52-47AM_1.log
 
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   26749785  
 
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '26749785' to OH '/app/oracle/product/19.0.0'
 
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patch 26749785 successfully applied.
Log file location: /app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-27_09-52-47AM_1.log
 
OPatch succeeded.
 
$ cd $ORACLE_HOME/OPatch
$ datapatch
SQL Patching tool version 19.11.0.0.0 Production on Mon Dec 27 10:35:36 2021
Copyright (c) 2012, 2021, Oracle.  All rights reserved.
 
Log file for this invocation: /app/oracle/cfgtoollogs/sqlpatch/sqlpatch_28146_2021_12_27_10_35_36/sqlpatch_invocation.log
 
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done
 
Current state of interim SQL patches:
Interim patch 26749785 (PERF_DIAG  NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK):
  Binary registry: Installed
  SQL registry: Not installed
 
Current state of release update SQL patches:
  Binary registry:
    19.11.0.0.0 Release_Update 210413004009: Installed
  SQL registry:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 11-JAN-21 06.36.28.702431 PM
 
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 32545013 (Database Release Update : 19.11.0.0.210420 (32545013)):
    Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.11.0.0.0 Release_Update 210413004009
  The following interim patches will be applied:
    26749785 (PERF_DIAG  NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK)
 
Installing patches...
Patch installation complete.  Total patches installed: 3
 
Validating logfiles...done
Patch 26749785 apply: SUCCESS
  logfile: /app/oracle/cfgtoollogs/sqlpatch/26749785/24119974/26749785_apply_ORCL19_2021Dec27_10_38_02.log (no errors)
 
Please refer to MOS Note 1609718.1 and/or the invocation log
/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_28146_2021_12_27_10_35_36/sqlpatch_invocation.log
for information on how to resolve the above errors.
 
SQL Patching tool complete on Mon Dec 27 10:38:27 2021

정상적으로 패치됨

패치 적용 후 AUTO_STATS_ADVISOR_TASK 값 확인

1
2
3
4
5

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
 
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------
TRUE

TRUE임(enable 상태)

패치 적용 후 disable

1
2
3

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
 
PL/SQL procedure successfully completed.

disable 후 AUTO_STATS_ADVISOR_TASK 값 확인

1
2
3
4
5

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
 
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
-------------------------------------------------------------------------
FALSE

FALSE가 됨(disable 상태)

원인 : AUTO_STATS_ADVISOR_TASK로 인한 SYSAUX 용량증가

Oracle 19c 에서 Statistics Advisor Task 실행이 더 많아졌고 그로인해 SYSAUX 테이블스페이스에서 더 많은 공간을 차지 하게됨

보존기한을 줄이거나 수동으로 AUTO_STATS_ADVISOR_TASK 를 제거해주면됨

기존 dba_advisor_objects 확인

1
2
3
4
5
6
7
8
9
10
11
12
13

SQL> 
select task_name, count(*) cnt 
from dba_advisor_objects 
group by task_name 
order by cnt desc;
 
TASK_NAME                           CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK             76318521
SYS_AUTO_SPM_EVOLVE_TASK            21
SYS_AUTO_SQL_TUNING_TASK            39
.
.

AUTO_STATS_ADVISOR_TASK의 cnt가 많음

이로 인해 sysaux 용량을 많이 차지하게됨

위 작업 후 dba_advisor_objects 확인

1
2
3
4
5
6
7
8
9
10
11

SQL> 
select task_name, count(*) cnt 
from dba_advisor_objects 
group by task_name 
order by cnt desc;
 
TASK_NAME                           CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK             3
.
.

AUTO_STATS_ADVISOR_TASK의 cnt가 줄어듬

참조 :

2305512.1, 329984.1, 2730556.1, 2692726.1, 2544788.1, 27983174.8, 2686022.1, bug 27983174
https://community.oracle.com/mosc/discussion/3807325/ora-12012-error-on-auto-execute-of-job-sys-ora-at-os-opt-sy-42249
https://community.oracle.com/tech/apps-infra/discussion/4031378/ora-20001-statistics-advisor-invalid-task-name-for-the-current-user
https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/sql-access-advisor.html#GUID-561EC9B4-0930-4915-B5E1-17F2C5ACD261
https://dataonair.or.kr/db-tech-reference/d-guide/dbms-1/?mod=document&uid=101814
http://power.iotn.co.kr/zboard.php?tab_num=1&id=9_1_bbs&page=31&sn1=&divpage=1&sn=off&ss=on&sc=off&select_arrange=hit&desc=asc&no=2551

https://h391106.tistory.com/270

https://www.codetd.com/ko/article/6286857

https://positivemh.tistory.com/392

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in tablespace SYSAUX

OS환경 : Oracle Linux6.8(64bit) DB 환경 : Oracle Database 11.2.0.4 에러 : ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in tablespace..

positivemh.tistory.com

저작자표시

'ORACLE > Trouble Shooting' 카테고리의 다른 글

오라클 19c RAC GRID_HOME relink 시 발생하는 메세지 (0)	2021.12.30
AHF-00014: AHF Data Location /app/oracle/oracle.ahf/data is not owned by root in directory hierarchy (0)	2021.12.25
ORA-28040: No matching authentication protocol (0)	2021.12.09
WARNING: inbound connection timed out (ORA-3136) (0)	2021.11.24
ORA-03135: connection lost contact (0)	2021.11.23

내맘대로긍정

박영민 OracleDBA@kakao.com

Category

Menu

Comment

Link