출처 : [한국 Oracle Technology Network]의 기술 지원 게시판
========================================================================================
No. 22383 : DBMS_STATS.GATHER_SYSTEM_STATS를 이용한 통계정보 수집 가이드
========================================================================================
PURPOSE
-------
이 자료는 DBMS_STATS.GATHER_SYSTEM_STATS 함수를 사용하여 system table에 대한
통계 정보 수집 시 stats table과 AUX_STATS$ table에 나타나는 정보에 대한 분석 및
통계 정보 생성 시 필요한 INTERVAL에 대한 내용과 통계정보 수집 상태를 나타내는
NOWORKLOAD, BADSTATS, AUTOGATHERING 상태에 대한 설명입니다.
Explanation
-----------
다음과 같은 문서를 참고하여 test 후, 확인하였습니다.
<Note:153761.1> Gathering System Statistics Returns only 'NOWORKLOAD'.
<Note:149560.1> Collect and Display System Statistics (CPU and IO) for CBO usage.
DBMS_STATS.GATHER_SYSTEM_STATS를 사용하여 20분 동안의 통계정보를 생성하는
간단한 절차를 test를 통해 알아보기로 한다. 통계정보 수집 시간이 최소 권장 사항인 60분
이상이 되지 않아도 오라클에서 통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 있으면
PVAL2가 badstats로 나오지 않고 autogathering으로 정상으로 나오는 것을 알 수 있다.
Step
-----
1. sqlplus "/as sysdba"
2. EXECUTE DBMS_STATS.CREATE_STAT_TABLE( ownname => 'SYSTEM',
stattab => 'mystats');
3. ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;
4. EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( interval => 20, stattab => 'mystats',
statid => 'DAY', statown => 'SYSTEM');
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 20);
< 참고 > statid 로 DAY 와 NIGHT 이 있으므로, DBMS_STATS.GATHER_SYSTEM_STATS
함수의 파라미터로 선택하여 사용 가능함.
5. select * from system.mystats;
select count(*) from system.mystats;
위의 select 수행 결과 no rows selected로 나오면 아래와 같이 수행한다.
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START',
stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');
또한 START 를 MANUAL하게 해주지 않으면 다음과 같이 NOWORKLOAD로 나타난다.
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
PNAME PVAL2
--------- --------------------------------------------------------------------
STATUS NOWORKLOAD
6. DBMS_STATS.GATHER_SYSTEM_STATS 함수에서 START 를 해주면 아래와 같이
AUTOGATHERING 상태로 바뀐다.
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
PNAME PVAL2
-------- ---------------------------------------------------------------------
STATUS AUTOGATHERING
7. 아직 20분이 경과되지 않으면 아래와 같이 진행 상황을 확인할 수 있다.
SQL> alter session set nls_date_format='mm-dd-YYYY HH24:MI';
SQL> select c1, to_date(c2), to_date(c3) from system.mystats;
C1 TO_DATE(C2) TO_DATE(C3)
--------------------------------------------------------------
MANUALGATHERING 12-21-2004 02:46 12-21-2004 02:46
8. MANUAL하게 STOP을 하려면 아래와 같이 수행한다.
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP',
stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');
<주의> 만약, DBMS_STATS.GATHER_SYSTEM_STATS 함수를 이용하여 STOP을
실행한 이후에 아래 9번과 같은 명령을 수행 시 PVAL2가 badstats 로 나온다면 이것은
interval 이 충분하지 않아 그 기간 동안에 작업 WORKLOAD가 부족하기 때문입니다.
따라서, interval 동안 SINGLE BLOCK I/O와 multiblock I/O에 대한 workload가 오라클에서
통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 없으면 정보가 부족하여 발생하는
현상으로 보입니다. 즉, 지속적인 i/o에 대한 통계치를 제대로 수집하지 못해서 발생하는
것입니다. 시스템에 대한 통계 정보 수집 시 interval은 최소한 60분, default로 120분입니다.
9. 20분이 지나면 아래와 같은 spool 결과를 볼 수 있고, sys.aux_stats$ table에서
PVAL2 가 AUTOGATHERING에서 COMPLETED 로 변경됨을 알 수 있다.
SQL> select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$
where pname ='STATUS';
SNAME PNAME PVAL1 PVAL2
--------------------------------------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
10. 통계정보를 보관하기 위해 생성한 'mystats' 라는 stats table을 drop하려면
이와 같이 DBMS_STATS.DROP_STAT_TABLE procedure를 수행한다.
EXECUTE DBMS_STATS.DROP_STAT_TABLE( ownname => 'SYSTEM', stattab =>'mystats');
< 권장 사항 >
dbms_stats.gather_system_stats package 사용 시 이 두 개의 파라미터를 Tuning 후
parameter file에 설정하면 보다 나은 CBO의 효과를 얻을 수 있다.
~ optimizer_index_caching (range of value : 0 to 100)
~ optimizer_index_cost_adj (range : 1 to 10000)
< 참고 >
이와 같은 시스템 통계정보 생성 작업을 dbms_job.submit 프로시져를 이용하여 수행할 수도 있다.
이렇게 job으로 등록했을 경우에는 통계정보 생성 중에 dba_jobs 와 dba_jobs_running 뷰를
조인하여 확인해 보면 WHAT 컬럼에 아래와 같은 job scheduling이 걸리는 것을 볼 수 있다.
sql>dbms_stats.gather_system_stats(gathering_mode => 'AUTO_STOP', statown => 'SYS');
Example
----------
위의 작업에 대한 spool on 수행 결과입니다.
SQL> select * from sys.aux_stats$;
SQL> SELECT count(*) from system.mystats;
SQL> select job, what from user_jobs;
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
SQL> select * from sys.aux_stats$;
SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM');
BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM'); END;
*
ERROR at line 1:
ORA-20003: Unable to import system statistics
ORA-06512: at "SYS.DBMS_STATS", line 5437
ORA-06512: at line 1
SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP',
stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');
SQL> select c1, to_date(c2), to_date(c3) from system.mystats;
C1 TO_DATE(C2) TO_DATE(C3)
------------------------------ ---------------- ----------------
COMPLETED 12-21-2004 02:46 12-21-2004 03:10
SQL> select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$
where pname ='STATUS';
SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START',
stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
SQL> select count(*) from system.mystats;
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM');
BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM'); END;
*
ERROR at line 1:
ORA-20003: Unable to import system statistics
ORA-06512: at "SYS.DBMS_STATS", line 5437
ORA-06512: at line 1
SQL> spool off
위의 DBMS_STATS.IMPORT_SYSTEM_STATS 수행 시 ORA-20003 error에 대해서는
이 문서 <Note:154601.1>을 참조하시기 바랍니다. User table로부터의 system 통계 정보를
Data Dictionary로 transfer할 때 ORA-20003 에러에 대한 해결방법입니다.
위 과정 전체를 JOB으로 등록하여 DBMS_JOB.SUBMIT으로 수행하는 방법은 아래
참고 노트에 나와 있습니다.
Reference Documents
-------------------
<Note:153761.1>
<Note:149560.1>
