# 요약
  # analyze

# no_invalidate
_optimizer_invalidation_period

■ System 통계
from sys.aux_stats$
# noworkload
# workload

■ 자동 통계 수집
from dba_autotask_client
from dba_scheduler_wingroup_members
from dba_scheduler_windows
from dba_autotask_window_clients
from dba_autotask_job_history
from dba_tab_modifications

 

 


 


# 테이블 통계
analyze table employees compute statistics for table;


# 컬럼 통계
analyze table employees compute statistics for all columns;
analyze table employees compute statistics for all columns size 25;
analyze table employees compute statistics for all columns job_id size 20, department_id size 10;


# 인덱스 통계
analyze index emp_emp_id_pk compute statistics;
exec dbms_stats.gather_index_stats('HR','EMP_EMP_ID_PK')


# EMP 테이블 통계 수집하면서 관련있는 인덱스 통계수집도 함께 수행
exec dbms_stats.gather_table_stats('HR', 'EMP', method_opt => 'FOR COLUMNS SIZE 20 JOB_ID', cascade => true)


# no_invalidate : 라이브러리 캐시에 캐싱된 커서(LCO)를 무효화 할지 결정

exec dbms_stats.gather_table_stats('HR','EMP',method_opt=>'FOR COLUMNS SIZE 20 JOB_ID', cascade => true, no_invalidate => true)

- no_invalidate => 
    - true  : 기본값, 연관된 커서(LCO)를 무효화 하지 않겠다. (10g)
    - false : 연관된 커서(LCO)를 무효화 한다. (9i)
    - dbms_stats.auto_invalidate : 정해진 시간동안 조금씩 무효화 한다.(10g)
                                _optimizer_invalidation_period = 18000(초)


# 히든 파라미터 정보
SYS@ora11g> select a.ksppinm name, b.ksppstvl value
            from x$ksppi a, x$ksppsv b
            where a.indx = b.indx
            and lower(a.ksppinm) in ('_optimizer_invalidation_period');

NAME                            VALUE
------------------------------- ----------
_optimizer_invalidation_period  18000

 

 




■ System 통계
- 9i부터 하드웨어 및 애플리케이션 특성에 맞는 시스템 통계를 수집하고 활용
- I/O, CPU 성능 같은 하드웨어 특성
    - CPU 속도
    - 평균적인 Singleblock I/O 속도, Multiblock I/O 속도
    - 평균적인 Multiblock I/O 개수
    - I/O 서브시스템의 최대 처리량(throughput)
    - 병렬 slave의 평균적인 처리량(throughput)

# 시스템 통계 정보

SYS@ora11g> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- -----------------
SYSSTATS_INFO                  STATUS                                    COMPLETED                                                                          SYSSTATS_INFO                  DSTART                                    08-25-2013 05:42
SYSSTATS_INFO                  DSTOP                                     08-25-2013 05:42
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     2734.11765
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

# noworkload 시스템 통계(10g)
- 명시적으로 시스템 통계 수집을 하지 않더라도 CPU 비용 모델을 사용할 수 있도록하기 위해서 오라클이 내부적으로 시스템 통계 설정
    CPUSPEEDNW : CPU 속도(백만/초)
    IOSEEKTIM  : 데이터를 읽으려고 디스크 헤드를 옮기는데 걸리는 시간, 보편적(5~15ms)
    IOTFRSPEED : OS 프로세스 I/O 서브 시스템으로부터 데이터를 읽는 속도(byte/ms)

# workload 시스템 통계(9i)
- 실제 애플리케이션에서 발생하는 부하를 측정한 값
    SREADTIM : 평균적 single block I/O 속도(ms:1/1000초)
    MREADTIM : 평균적 multi block I/O 속도(ms:1/1000초)
    CPUSPEED : 단일 CPU가 초당 수행할 수 있는 오퍼레이션 수(백만/초)
    MBRC     : multi block I/O 발생 시 평균적으로 읽은 블록 수
    MAXTHR   : I/O 서브시스템의 초당 최대 처리량(byte/초)
    SLAVETHR : 병렬 slave의 평균적인 초당 처리량(byte/초)

 

 

 

# NOWORKLOAD 시스템 통계 수집

SYS@ora11g> execute dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD')

PL/SQL procedure successfully completed.


SYS@ora11g> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    02-23-2024 14:28
SYSSTATS_INFO                  DSTOP                                     02-23-2024 14:28
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                           3562
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

 

 

# Workload 시스템 통계 수집

SYS@ora11g> execute dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL', interval => 1)

PL/SQL procedure successfully completed.

> interval : 분단위 설정
> SQL문 많이 사용할 때 하면 좋음(낮업무)


SYS@ora11g> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------
SYSSTATS_INFO                  STATUS                                    AUTOGATHERING
SYSSTATS_INFO                  DSTART                                    02-23-2024 14:31
SYSSTATS_INFO                  DSTOP                                     02-23-2024 14:32
SYSSTATS_INFO                  FLAGS                                   0
SYSSTATS_MAIN                  CPUSPEEDNW                           3562
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR
SYSSTATS_TEMP                  SBLKRDS                             94036
SYSSTATS_TEMP                  SBLKRDTIM                      276503.878
SYSSTATS_TEMP                  MBLKRDS                              9180
SYSSTATS_TEMP                  MBLKRDTIM                       42704.805
SYSSTATS_TEMP                  CPUCYCLES                         2509920
SYSSTATS_TEMP                  CPUTIM                             704612
SYSSTATS_TEMP                  JOB                                   581
SYSSTATS_TEMP                  CACHE_JOB                             582
SYSSTATS_TEMP                  MBRTOTAL                           155168

 


SYS@ora11g> execute dbms_stats.gather_system_stats(gathering_mode => 'start')
SYS@ora11g> execute dbms_stats.gather_system_stats(gathering_mode => 'stop')

 


# 테스트 데이터베이스(개발DB) 시스템 통계 정보를 운영 시스템 통계 정보로 반영

begin
    dbms_stats.gather_system_stats('SREADTIM', 1.2);
    dbms_stats.gather_system_stats('MREADTIM', 1.3);
    dbms_stats.gather_system_stats('MBRC', 16);
    dbms_stats.gather_system_stats('CPUSPEED', 700);
    dbms_stats.gather_system_stats('MAXTHR', 40580544);
    dbms_stats.gather_system_stats('SLAVETHR', 32224);
end;
/






■ 자동 통계 수집
- 10g

# 자동 작업 기능 목록

SYS@ora11g> select client_name, status, consumer_group, window_group 
            from dba_autotask_client;
            
CLIENT_NAME                         STATUS        CONSUMER_GROUP                 WINDOW_GROUP
----------------------------------- ------------- ------------------------------ ---------------
auto optimizer stats collection     ENABLED       ORA$AUTOTASK_STATS_GROUP       ORA$AT_WGRP_OS
auto space advisor                  ENABLED       ORA$AUTOTASK_SPACE_GROUP       ORA$AT_WGRP_SA
sql tuning advisor                  ENABLED       ORA$AUTOTASK_SQL_GROUP         ORA$AT_WGRP_SQ

- auto optimizer stats collection : 한번도 수집하지 안은 테이블(last_analyze = null),
                                    마지막 데더링 이후 10%이상 변경된 테이블
- sql tuning advisor : 유료, 문장 튜닝을 오라클이 해주겠다.
- auto space advisor : 유료, 재구성(리오브) 대상 테이블을 찾아주겠다.
                        Row Migration 발생한 테이블, HWM 위치대비 미사용 block 많은 테이블

> 유료인데 ENABLED ? > 사용안하면 disable 해놓기

 

 

# ORA$AT_WGRP_OS : 어느 요일에 어떤 작업을 하는지 정보

SYS@ora11g> select * 
            from dba_scheduler_wingroup_members 
            where window_group_name = 'ORA$AT_WGRP_OS';
            
WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
ORA$AT_WGRP_OS                 MONDAY_WINDOW
ORA$AT_WGRP_OS                 TUESDAY_WINDOW
ORA$AT_WGRP_OS                 WEDNESDAY_WINDOW
ORA$AT_WGRP_OS                 THURSDAY_WINDOW
ORA$AT_WGRP_OS                 FRIDAY_WINDOW
ORA$AT_WGRP_OS                 SATURDAY_WINDOW
ORA$AT_WGRP_OS                 SUNDAY_WINDOW



# 작업에 대한 시간정보

SYS@ora11g> select window_name, resource_plan, repeat_interval, duration 
            from dba_scheduler_windows;
            
WINDOW_NAME                    RESOURCE_PLAN                  REPEAT_INTERVAL                                         DURATION
------------------------------ ------------------------------ ------------------------------------------------------- --------------
MONDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00
TUESDAY_WINDOW                 DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00
WEDNESDAY_WINDOW               DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00
THURSDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00
FRIDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00
SATURDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00
SUNDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00
WEEKNIGHT_WINDOW                                              freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute +000 08:00:00
                                                              =0; bysecond=0
WEEKEND_WINDOW                                                freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0     +002 00:00:00

 


# 작업 시 수행여부, 다음 작업일

SYS@ora11g> select window_name, window_next_time, autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor
            from dba_autotask_window_clients;

WINDOW_NAME          WINDOW_NEXT_TIME                         AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ---------------------------------------- --------------- --------------- --------------- ----------------
MONDAY_WINDOW        26-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED
TUESDAY_WINDOW       27-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED
WEDNESDAY_WINDOW     28-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED
THURSDAY_WINDOW      22-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED
FRIDAY_WINDOW        23-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED
SATURDAY_WINDOW      24-FEB-24 06.00.00.000000 AM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED
SUNDAY_WINDOW        25-FEB-24 06.00.00.000000 AM EST5EDT     ENABLED         ENABLED         ENABLED         ENABLED




# 작업정보 수정

execute dbms_scheduler.set_attribute('FRIDAY_WINDOW', 'repeat_interval', 'freq=daily;byday=FRI;byhour=05;byminute=0; bysecond=0');

execute dbms_scheduler.set_attribute(name => 'MONDAY_WINDOW', attribute => 'duration', value => numtodsinterval(5,'hour'));

> numtodsinterval : day, hour, minute, second



# 작업 히스토리

SYS@ora11g> select client_name, job_status, job_start_time, job_duration 
            from dba_autotask_job_history;
            
CLIENT_NAME                         JOB_STATUS  JOB_START_TIME                        JOB_DURATION
----------------------------------- ----------- ------------------------------------- --------------
auto optimizer stats collection     SUCCEEDED   22-FEB-24 10.00.02.234326 PM EST5EDT  +000 00:00:10
sql tuning advisor                  SUCCEEDED   22-FEB-24 10.00.02.130719 PM EST5EDT  +000 00:00:01
auto space advisor                  SUCCEEDED   22-FEB-24 10.00.02.127326 PM EST5EDT  +000 00:00:02
sql tuning advisor                  SUCCEEDED   21-FEB-24 10.00.02.256360 PM EST5EDT  +000 00:00:02
auto space advisor                  SUCCEEDED   21-FEB-24 10.00.02.183351 PM EST5EDT  +000 00:00:04
auto optimizer stats collection     SUCCEEDED   21-FEB-24 10.00.02.167742 PM EST5EDT  +000 00:00:20
sql tuning advisor                  SUCCEEDED   20-FEB-24 10.00.02.180355 PM EST5EDT  +000 00:00:03
auto space advisor                  SUCCEEDED   20-FEB-24 10.00.02.169809 PM EST5EDT  +000 00:00:04
auto optimizer stats collection     SUCCEEDED   20-FEB-24 10.00.02.155618 PM EST5EDT  +000 00:00:11
...

 


> 자동 통계는 일반적으로 disable 상태로 두거나 특정요일만 사용

 


# 자동 통계 수집 비활성화

SYS@ora11g> begin
                dbms_auto_task_admin.disable(
                        client_name => 'auto optimizer stats collection',
                        operation => null,
                        window_name => null);
                end;
                /

PL/SQL procedure successfully completed.


SYS@ora11g> select client_name, status, consumer_group, window_group 
            from dba_autotask_client;

CLIENT_NAME                         STATUS        CONSUMER_GROUP                 WINDOW_GROUP
----------------------------------- ------------- ------------------------------ ---------------
auto optimizer stats collection     DISABLED      ORA$AUTOTASK_STATS_GROUP       ORA$AT_WGRP_OS
auto space advisor                  ENABLED       ORA$AUTOTASK_SPACE_GROUP       ORA$AT_WGRP_SA
sql tuning advisor                  ENABLED       ORA$AUTOTASK_SQL_GROUP         ORA$AT_WGRP_SQ


SYS@ora11g> select window_name, window_next_time, autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor
            from dba_autotask_window_clients;

WINDOW_NAME          WINDOW_NEXT_TIME                         AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ---------------------------------------- --------------- --------------- --------------- ----------------
MONDAY_WINDOW        26-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED
TUESDAY_WINDOW       27-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED
WEDNESDAY_WINDOW     28-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED
THURSDAY_WINDOW      22-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED
FRIDAY_WINDOW        23-FEB-24 10.00.00.000000 PM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED
SATURDAY_WINDOW      24-FEB-24 06.00.00.000000 AM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED
SUNDAY_WINDOW        25-FEB-24 06.00.00.000000 AM EST5EDT     ENABLED         DISABLED        ENABLED         ENABLED

 

 

 

# 자동 통계 수집 활성화

begin
    dbms_auto_task_admin.inable(
            client_name => 'auto optimizer stats collection',
            operation => null,
            window_name => null);
end;
/

 


# statistics_level : 통계 수집 레벨

SYS@ora11g> show parameter statistics_level

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
statistics_level                     string  TYPICAL 


> statistics_level = [ basic | typical(기본값) | all ]
    basic   : 통계수집 안하겠다.
    typical : 일부 통계 수집을 하겠다.
    all     : 가능한 모든 통계 수집을 하겠다.

> mmon이 통계수집을 하는 프로세스
> system level, session level

 

 

# block 샘플링 개수

SYS@ora11g> show parameter optimizer_dynamic_sampling

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
optimizer_dynamic_sampling           integer 2


> value : 1 ~ 10



# 예시

SYS@ora11g> create table hr.emp_new nologging as select employee_id, last_name, salary 
            from hr.employees;
            
Table created.


SYS@ora11g> select num_rows, blocks, avg_row_len, last_analyzed 
            from dba_tables 
            where table_name = 'EMP_NEW';

  NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALY
---------- ---------- ----------- ----------


SYS@ora11g> explain plan for select * from hr.emp_new where employee_id = 100;

Explained.


SYS@ora11g> select plan_table_output from table(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_NEW |     1 |    40 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEE_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)

 


# 통계 수집

SYS@ora11g> exec dbms_stats.gather_table_stats(ownname => 'hr', tabname => 'emp_new', degree => 2)

PL/SQL procedure successfully completed.

- degree => 2 : 프로세서 2개 사용, 병렬작업, CPU 2배수까지 가능, 대용량에선 꼭 사용하자.


SYS@ora11g> select num_rows, blocks, avg_row_len, last_analyzed 
            from dba_tables 
            where table_name = 'EMP_NEW';

  NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALY
---------- ---------- ----------- ----------
       107          4          14 2024-02-23



# 수정정보 : 모니터링

SYS@ora11g> select * from dba_tab_modifications where table_name = 'EMP_NEW';

no rows selected


SYS@ora11g> insert into hr.emp_new (employee_id, last_name, salary) values (301, 'itwill', 1000);

1 row created.

SYS@ora11g> insert into hr.emp_new (employee_id, last_name, salary) values (302, 'itwill', 2000);

1 row created.

SYS@ora11g> commit;

Commit complete.


SYS@ora11g> select * from dba_tab_modifications where table_name = 'EMP_NEW';

no rows selected

> 왜 안나옴? > smon 일을 안한다?


# flush : smon에 요청
SYS@ora11g> exec dbms_stats.flush_database_monitoring_info

PL/SQL procedure successfully completed.


SYS@ora11g> select * from dba_tab_modifications where table_name = 'EMP_NEW';

TABLE_OWNER                    TABLE_NAME           PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP  TRU DROP_SEGMENTS
------------------------------ -------------------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- --- -------------
HR                             EMP_NEW                                                                                     2          0          0 2024-02-23 NO              0


> INSERTS : 2개

 

 

SYS@ora11g> update hr.emp_new set salary = salary * 1.1 where salary > 10000;

14 rows updated.

SYS@ora11g> delete from hr.emp_new where employee_id in (100,101,102,103,104);

5 rows deleted.

SYS@ora11g> commit;

Commit complete.


SYS@ora11g> exec dbms_stats.flush_database_monitoring_info

PL/SQL procedure successfully completed.


SYS@ora11g> select * from dba_tab_modifications where table_name = 'EMP_NEW';

TABLE_OWNER                    TABLE_NAME           PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP  TRUNCATED DROP_SEGMENTS
------------------------------ -------------------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- --------- -------------
HR                             EMP_NEW                                                                                     2         14          5 2024-02-23 NO                    0

> update : 14개
> delete : 5개


# 통계정보 갱신 필요 여부
SYS@ora11g> select num_rows, last_analyzed, stale_stats 
            from dba_tab_statistics 
            where table_name = 'EMP_NEW';

  NUM_ROWS LAST_ANALYZED STALE_STATS
---------- ------------- -----------
       104 23-FEB-24     NO

> stale_stats = yes : 마지막 통계 수집 이후 10% 이상의 변화율이 관측된 테이블.

 

 


# 통계수집을 다시하면 초기화 된다.

SYS@ora11g> exec dbms_stats.gather_table_stats(ownname => 'hr', tabname => 'emp_new', degree => 2)

PL/SQL procedure successfully completed.


SYS@ora11g> select * from dba_tab_modifications where table_name = 'EMP_NEW';

no rows selected


SYS@ora11g> select num_rows, last_analyzed, stale_stats 
            from dba_tab_statistics 
            where table_name = 'EMP_NEW';

  NUM_ROWS LAST_ANALY STALE_STATS
---------- ---------- -----------
       104 2024-02-23 NO




# stale 상태로 빠지는 변화율의 값을 바꾸는 기능

SYS@ora11g> exec dbms_stats.set_table_prefs('hr','emp_new','stale_percent','40')

PL/SQL procedure successfully completed.

> 자동통계수집 시 emp_new 테이블은 10% -> 40% 기준 적용



# 통계수집을 못하게 lock 설정

# Lock 설정
SYS@ora11g> exec dbms_stats.lock_table_stats('hr','emp_new')

PL/SQL procedure successfully completed.


SYS@ora11g> select stattype_locked 
            from dba_tab_statistics 
            where owner = 'HR' and table_name = 'EMP_NEW';

STATTYPE_LOCKED
---------------
ALL

> all : lock 걸려있다.
> 수집하면 에러난다.
> 실행계획이 바뀌면 안되는 경우 사용


# 통계 수집 시도
SYS@ora11g> exec dbms_stats.gather_table_stats(ownname => 'hr', tabname => 'emp_new', degree => 2)

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332


# lock 해제
SYS@ora11g> exec dbms_stats.unlock_table_stats('hr','emp_new')

PL/SQL procedure successfully completed.

SYS@ora11g> select stattype_locked 
            from dba_tab_statistics 
            where owner = 'HR' and table_name = 'EMP_NEW';

STATTYPE_LOCKED
---------------