오라클 19c 통계정보 lock 방법

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 19.27.0.0

 

방법 : 오라클 19c 통계정보 lock 방법

본문에서는 특정 테이블, 특정 유저(스키마), 특정 파티션에 통계정보 lock을 거는 방법을 설명함
lock이 걸려있을 경우 자동 통계잡 수행시 통계정보가 수집되지 않고, 수동으로 통계를 수집하려해도 에러가 발생함
참고 : 오라클 11g 자동통계정보수집 table lock , unlock ( https://positivemh.tistory.com/332 )

 

 

테스트
1. 특정 테이블 통계정보 lock
2. 특정 테이블 통계정보 unlock
3. 특정 유저들의 모든 테이블 통계정보 lock(테이블 레벨)
4. 특정 유저의 모든 테이블 통계정보 lock(스키마 레벨)
5. 특정 유저들의 모든 테이블 통계정보 lock(스키마 레벨)

 

 

테스트
1. 특정 테이블 통계정보 lock(DBMS_STATS.LOCK_TABLE_STATS 이용)

1 2 3

SQL> exec dbms_stats.lock_table_stats('IMSI', 'TBL_LOCK');   PL/SQL procedure successfully completed.

 

 

확인

1 2 3 4 5 6 7 8 9 10

SQL>  set lines 200 pages 1000 col table_name for a20 select table_name, stattype_locked  from dba_tab_statistics  where table_name = 'TBL_LOCK';   TABLE_NAME           STATT -------------------- ----- TBL_LOCK             ALL

통계정보 수집상태가 lock 상태이면 stattype_locked가 all로 표시됨

 

 

2. 특정 테이블 통계정보 unlock

1 2 3

SQL> exec dbms_stats.unlock_table_stats('IMSI', 'TBL_LOCK');   PL/SQL procedure successfully completed.

 

 

확인

1 2 3 4 5 6 7 8 9 10

SQL>  set lines 200 pages 1000 col table_name for a20 select table_name, stattype_locked  from dba_tab_statistics  where table_name = 'TBL_LOCK';   TABLE_NAME           STATT -------------------- ----- TBL_LOCK

unlock 됨

 

 

3. 특정 유저들의 모든 테이블의 통계정보 lock(테이블 레벨)
owner 명시 또는 created 날짜로 유저 선택

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

SQL> spool lock_stat.sql SET SERVEROUTPUT ON BEGIN   FOR t IN (     SELECT owner, table_name     FROM dba_tables     WHERE owner NOT IN ('SYS', 'SYSTEM', 'XDB')       --AND owner NOT LIKE 'APEX%'   --AND owner in (select owner from dba_users where created > to_date('2025/04/17 00:00:00', 'yyyy/mm/dd hh24:mi:ss')) ORDER BY 1   ) LOOP     DBMS_OUTPUT.PUT_LINE(       'EXEC DBMS_STATS.LOCK_TABLE_STATS(''' || t.owner || ''', ''' || t.table_name || ''');'     );   END LOOP; END; / spool off

 

 

이후 lock_stat.sql 실행

1 2 3 4 5 6 7

SQL> @lock_stat.sql PL/SQL procedure successfully completed.   PL/SQL procedure successfully completed.   PL/SQL procedure successfully completed. ...

 

 

lock 이후 신규 테이블을 생성하면 해당 테이블은 통계정보가 unlock로 존재하게됨

 

 

4. 특정 유저의 모든 테이블 통계정보 lock(스키마 레벨)

1 2 3

SQL> exec dbms_stats.lock_schema_stats('IMSI');   PL/SQL procedure successfully completed.

 

 

신규테이블 생성 후 통계정보 lock 확인

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

SQL>  conn imsi/imsi create table test1 (col1 number); set lines 200 pages 1000 col table_name for a20 select table_name, stattype_locked  from dba_tab_statistics  where owner = 'IMSI';   TABLE_NAME           STATT -------------------- ----- TEST1 TBL_LOCK             ALL

기존에 존재하던 테이블은 stattype_locked가 ALL로 변경되어 lock이 설정되었지만
dbms_stats.lock_schema_stats 수행 이후 생성된 테이블은 통계정보 lock이 설정되지않음

 

 

통계정보 수동 수집

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

SQL> exec dbms_stats.gather_table_stats('IMSI', 'TEST1');   PL/SQL procedure successfully completed.   SQL> exec dbms_stats.gather_table_stats('IMSI', 'TBL_LOCK'); BEGIN dbms_stats.gather_table_stats('IMSI', 'TBL_LOCK'); END;   * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 40751 ORA-06512: at "SYS.DBMS_STATS", line 40035 ORA-06512: at "SYS.DBMS_STATS", line 9393 ORA-06512: at "SYS.DBMS_STATS", line 10317 ORA-06512: at "SYS.DBMS_STATS", line 39324 ORA-06512: at "SYS.DBMS_STATS", line 40183 ORA-06512: at "SYS.DBMS_STATS", line 40732 ORA-06512: at line 1

dbms_stats.lock_schema_stats 수행 이전 생성된 테이블만 통계정보 lock이 설정됨

 

 

참고로 특정 테이블의 파티션만 lock을 걸려면 dbms_stats.lock_partition_stats 프로시저를 이용하면됨

1	SQL> exec dbms_stats.lock_partition_stats('IMSI', '테이블명', '파티션명');

 

 

5. 특정 유저들의 모든 테이블의 통계정보 lock(스키마 레벨)
username 명시 또는 created 날짜로 유저 선택

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

SQL> spool lock_stat.sql SET SERVEROUTPUT ON BEGIN   FOR t IN (     select username from dba_users  where username NOT IN ('SYS', 'SYSTEM', 'XDB')  and username NOT LIKE 'APEX%'  and created > to_date('2025/04/17 00:00:00', 'yyyy/mm/dd hh24:mi:ss') ORDER BY 1   ) LOOP     DBMS_OUTPUT.PUT_LINE(       'EXEC DBMS_STATS.LOCK_SCHEMA_STATS(''' || t.username || ''');'     );   END LOOP; END; / spool off

 

 

이후 lock_stat.sql 실행

1 2 3 4 5 6 7

SQL> @lock_stat.sql PL/SQL procedure successfully completed.   PL/SQL procedure successfully completed.   PL/SQL procedure successfully completed. ...

 

 

참조 : 

오라클 11g 자동통계정보수집 table lock , unlock ( https://positivemh.tistory.com/332 )
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html