[SQL] DBMS_STATS.LOCK_TABLE_STATS

DBMS_STATS.LOCK_TABLE_STATS :  테이블의 통계정보 잠그는데 사용, 테이블의 통계 정보가 변경되지 않도록 보호할 수 있음

 

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1');​

drop table t1;

CREATE TABLE t1 (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

INSERT INTO t1 VALUES (1, 'a');
INSERT INTO t1 VALUES (2, 'b');

 

통계정보 잠그기

SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname => USER, tabname => 't1'); 

PL/SQL procedure successfully completed.

 

통계정보 수집 X

force=>TRUE 사용하면 잠긴테이블도 수집가능 (default false)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1'); 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

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1', force=>FALSE);
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1', force=>FALSE); 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

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1', force=>TRUE);

PL/SQL procedure successfully completed.

 

통계정보 잠금 해제

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => USER, tabname => 't1'); 

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1', force=>FALSE);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 't1', force=>TRUE);

PL/SQL procedure successfully completed.