원본 출처 http://www.oracleclub.com/article/23893
[ 출처 ]
1. 10g Optimizer개념 및 통계치 생성 방법,SQL Tuning 방법
http://www.dbguide.net/club/board/download.jsp?maskname=274&fileName=10g+Optimizer+to+public.pdf
2. 10g 자동화 통계정보 수집에 관하여... [2008/05/11 Update]
http://blog.naver.com/sungeunn/120051268815
3. 어떤 STATISTICS_LEVEL 을 사용할 것인가 ?
http://cafe.naver.com/prodba/9293
4. 메타링크
4.1 Two types of automatic statistics collected in 10g [ 559029.1 ]
4.2 How to Disable Automatic Statistics Collection in 10G ? [ 311836.1 ]
5. 10G References [ STATISTICS_LEVEL ]
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#REFRN10214
[ 결론 ]
1. System 통계정보는 따로 생성 하지 않는다.
어느 시간이 최적인지 모른다. DBA 가 알수 있으면 정기적으로 생성해서 활용한다.
2. 자동 통계정보 수집(CBO) 는 disable 하고, 수동으로 통계정보를 생성한다.
운영 중에 통계정보 생성으로 인해 업무의 성능 저하를 사전에 예방하자.
3. STATISTICS_LEVEL = TYPICAL 로 유지 하자
4. 자동 통계정보 수집 대상은 User Objects, Sys/System Object 가 대상이다.
[ 요약 ]
| 대상 |
설명 |
TATISTICS_LEVEL=BASIC |
STATISTICS_LEVEL=TYPICAL |
| System Statistics |
System 성능 ( CPU, DISK ) |
|
|
| Fixed Objects Statistics |
DBMS 성능 ( X$, V$) |
|
|
| Dictionary Statistics |
SYS/SYSTEM USER TABLE |
자동 수집되지 않는다. |
자동 수집된다. |
User Table Statistics
|
일반 유저 TABLE |
자동 수집되지 않는다 |
자동 수집된다. |
통계정보의 종류
: 크게 4가지로 구분할 수 있다.
[ 1.System Statistics ]
: 개요 - System Statistics 는 System Hardware 의 I/O, CPU 의 특성을 분석하여
Optimizer 가 CPU Costing 을 계산할 때 사용하는 정보로써,
이를 기반으로 Optimizer 가 임의의 SQL에 대한 실행 계획을 수립할 때
이를 기반으로 계산하게 된다.
수행주기 - 초기 1회
시스템 자원의 변경이 생겼을 경우 ( Memory, CPU, I/O 등 )
9i - 처음 소개된 개념이고, DBA가 수동으로 수집 하지 않으면 기본적으로
존재하지 않는 정보이다. 기본적으로 I/O Model로 비용산정
System Statistics 정보가 있으면 Optimizer 가 비용 산정을 CPU Model 로 하고,
System Statistics 정보가 없다면 Optimizer 가 비용 산정을 I/O Model 로 한다.
10G - System Statistics 정보를 수집 하지 않는다면 Noworkload System Statistics 가
사용된다. 10G 에서는 Optimizer 가 비용 산정을 CPU Model 로 한다. [ Default ]
[ CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 로 구성된다. ]
수동으로 System Statistics 수집 시에는 Workload System Statistics 라고 한다.
결론적으로 운영 시스템의 최적의 System Statistics 를 생성하여, 적절히
적용 하여 사용하는 것이 최선이나, 보통은 NoWorkload System Statistics 를
그대로 사용한다.
주의사항 : RAC 에서 NODE 가 서로 같은 시스템 사양을 같지 않을 경우에는
System Statistics 를 Node 별로 나누어 관리 되지 않으므로 전체 System 의
대표성을 가지는 Node 에서 수행을 한다.
특히 위와 같은 결정을 하기 위해서는, 각각의 Node 별로 통계치를 생성해
보고 비교해 본 후에 결정할 수 있다.
노드의 사양이 동일한 경우 가장 일반적인 Node 에서 수행한다.
실습
: 시스템 통계정보는 Optimizer 가 실행 계획 세움에 있어서 지대한 영향을
미치므로, 항상 기존의 시스템 통계자료를 백업 후 진행 하자.
OLTP 와 OLAP 성 통계정보를 생성하고, IMPORT 해보자
-- 1. 시스템 통계정보 확인
SELECT * FROM SYS.AUX_STATS$ ;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-13-2009 13:33
SYSSTATS_INFO DSTOP 02-13-2009 13:33
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1489
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
13 rows selected.
-- 2. 기존 통계정보 백업 받을 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- 기존 통계정보 백업
SQL> execute DBMS_STATS.EXPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS
PL/SQL procedure successfully completed.
-- 백업 받은 시스템 통계정보 데이타 확인
SQL> SELECT STATID, C1, C2, C3 FROM ORIGIN ;
-- C1 = COMPLETED --> 수집 완료
-- 3. 시스템 통계정보 수집
-- 사전에 JOB PROCESS 확인 ( 1보다 커야 한다.)
SQL> show parameters job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
SQL> alter system set job_queue_processes = 5 ;
System altered.
SQL> show parameters job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 5
-- 4. OLTP 성 시스템 통계정보 수집하기
-- OTLP용 시스템 통계정보 생성을 위한 통계정보 테이블 생성
-- OWNER, TABLE 이름, TABLESPACE 이름 순
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLTP’,’USERS’);
PL/SQL procedure successfully completed.
-- 지금 부터 INTERVAL 로 지정된 시간 동안 시스템 통계정보를 생성 하라..
-- 2분동안 시스템 통계정보를 수집 하라
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE
=>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLTP’, STATID =>
’OLTP’);
PL/SQL procedure successfully completed.
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID C1 C2 C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP AUTOGATHERING 02-24-2009 08:54 02-24-2009 08:56
OLTP
-- C1 = AUTOGATHERING --> 수집 중
-- C1 = COMPLETED --> 수집 완료 로 변경된다. [ 2분 경과 후 ]
-- 10분동안 시스템 통계정보를 수집 하라 [앞서 수행한 2분동안 통계정보는 Update 된다. ]
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE
=>’INTERVAL’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID =>
’OLTP’);
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID C1 C2 C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP AUTOGATHERING 02-24-2009 08:56 02-24-2009 08:58
OLTP
-- C1 = AUTOGATHERING --> 수집 중
-- 시스템 통계정보 수집 중지 하기 [ gathering_mode=>’STOP’ ]
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE
=>’STOP’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID =>
’OLTP’);
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
-- C1 = AUTOGATHERING --> 수집 중
-- C1 = COMPLETED --> 수집 완료 [ 강제로 중지 하여도 COMPLETED 로 나온다. ]
-- 5. OLAP 성 시스템 통계정보 수집하기
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLAP’,’USERS’);
PL/SQL procedure successfully completed.
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE
=>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLAP’, STATID =>
’OLAP’);
SQL> SELECT STATID, C1, C2, C3 FROM OLAP ;
STATID C1 C2 C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLAP COMPLETED 02-24-2009 09:04 02-24-2009 09:06
OLAP
-- C1 => AUTOGATHERING -- 수집 중
-- C1 => COMPLETED -- 수집 완료 로 변경된다. [ 2분 경과 후 ]
-- 6. 생성한 OLAP_STATS 시스템 통계정보 IMPORT 하기
-- 기존 통계정보 삭제
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- 기존 통계정보를 삭제 하면,
-- SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 값만 DELETE 시점으로 변경된다.
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-24-2009 09:08
SYSSTATS_INFO DSTOP 02-24-2009 09:08
-- OLTP 시스템 통계정보 IMPORT 하기
execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’OLTP’, STATID =>’OLTP’,STATOWN =>’SYS’);
-- Import 된 시스템 통계정보 확인
-- OLTP 시스템 통계정보를 수행한 시간으로 SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP
-- 값이 변경되어 진다.
SQL> select * from sys.aux_stats$ ;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-24-2009 08:56
SYSSTATS_INFO DSTOP 02-24-2009 08:58
-- 7. 다시 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
SQL> select * from sys.aux_stats$ ;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-13-2009 13:33
SYSSTATS_INFO DSTOP 02-13-2009 13:33
-- SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 로 ORIGIN 으로 변경됨
-- 8. 수동으로 시스템 통계정보 생성하기 [ 파라미터 개별 설정 ]
-- 기존 시스템 통계정보 삭제 하기
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- CPUSPEED 설정
-- CPUSPEED : Wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’CPUSPEED’, PVALUE=>400);
PL/SQL procedure successfully completed.
-- CPUSPEED 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’CPUSPEED’;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN CPUSPEED 400
-- SREADTIM 설정
-- SREADTIM : wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’SREADTIM’, PVALUE=>100);
PL/SQL procedure successfully completed.
-- SREADTIM 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’SREADTIM’;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN SREADTIM 100
-- 9. 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- 원복 결과 확인
SQL> select * from sys.aux_stats$ ;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-13-2009 13:33
SYSSTATS_INFO DSTOP 02-13-2009 13:33
[ 2.Fixed Objects Statistics ]
개요 - Dynamic Performance View와 같이 fixed view(x$ tables)에 대한 통계치는
Manual 한 Gathering 이 필요하다. 이 Fixed Objects Statistics 는
Database 의 Activity 를 기록하게 되므로 database 가 일반적인 운영 상태
일때 gathering 하여야 한다.
일반적으로 Fixed Object Statistics 통계치는 V$ view 를 조회하는
사용자 Query에 필요하다.
수행주기 - 초기 1회
추가적인 Application 이나 변경으로 동시 사용자 등의 변경 발생시
주의사항 - 자동화 대상이 아니다. [ 자동으로 Fixed Object의 통계정보가 생성되지 않는다. ]
RAC 에서는 아직 Fixed Objects Statistics 를 Instance 별로 구분하지
않기 때문에, 가장 부하가 많은 Node(Instance)에서 통계치를 조사한다.
실습
: Fixed Object 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
항상 기존의 Fixed Object 통계정보를 백업 후 진행 하자.
OLTP 와 OLAP 성 Fixed Object 통계정보를 생성하고, IMPORT 해보자
-- 1. 기존의 Fixed Object 통계정보 백업 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_ORIGIN’,’USERS’);
-- EXPORT_FIXED_OBJECTS_STATS 를 통한 백업 수행
SQL> execute DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’,STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- 2. 신규로 Fixed Object 통계정보 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_NEW’,’USERS’);
-- 신규 Fixed Object 통계정보 수집
SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’);
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 13578
ORA-06512: at "SYS.DBMS_STATS", line 13892
ORA-06512: at "SYS.DBMS_STATS", line 14420
ORA-06512: at line 1
SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’FIX_NEW’);
PL/SQL procedure successfully completed.
-- 3. 기존 Fixed Object 통계정보 확인 하기
SQL> select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
where table_name like ’X$%’ ;
TABLE_NAME TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA
X$KQFVI
X$KQFVT
X$KQFDT
-- 4. 신규 Fixed Object 통계정보 IMPORT
-- FIXED TABLE 의 통계정보 삭제
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_NEW’, STATID =>’FIX_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- 5. 신규 Fixed Object 통계정보 확인 하기
SQL> select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
where table_name like ’X$%’ ;
TABLE_NAME TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA 20090224 09:50:34 09:50:34
X$KQFVI 20090224 09:50:34 09:50:34
X$KQFVT 20090224 09:50:34 09:50:34
X$KQFDT 20090224 09:50:34 09:50:34
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.
6. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’, STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- 복원된 정보 확인 하기
SQL > select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
where table_name like ’X$%’ ;
[ 3.Dictionary Statistics ]
개요 - DBMS_STATS.GATHER_DICTIONARY_STATS 를 이용하여 Sys, System Schema 의
Object를 Gathering 한다. 이 procedure 는 또한 DRSYS 나 CTX user Schema의
Object 도 함께 Gathering 한다.
수행주기 - 초기 1회
Database Object(사용자 Table, PL/SQl, User생성) 의 변경이 있는 경우
실습
: Dictionary 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
항상 기존의 Dictionary 통계정보를 백업 후 진행 하자.
OLTP 와 OLAP 성 Dictionary 통계정보를 생성하고, IMPORT 해보자
-- 1. 기존의 Dictionary 통계정보 통계정보 백업 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- EXPORT_DICTIONARY_STATS 를 통한 백업 수행
SQL> execute DBMS_STATS.EXPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’,STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- 2. 신규로 Dictionary 통계정보 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_NEW’,’USERS’);
PL/SQL procedure successfully completed.
-- 신규 Dictionary 통계정보 수집
SQL> execute DBMS_STATS.GATHER_DICTIONARY_STATS(’SYS_NEW’);
PL/SQL procedure successfully completed.
3. 신규 Dictionary 통계정보 확인 하기
SQL> select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
where table_name like ’%$’
order by 2
....
X$LOGMNR_OBJ$ 20090224 10:07:20 10:07:20
X$LOGMNR_TABCOMPART$ 20090224 10:07:20 10:07:20
X$LOGMNR_USER$ 20090224 10:07:20 10:07:20
SUMDELTA$
SDO_TOPO_DATA$
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.
4. Dictionary 통계정보 IMPORT 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_NEW’, STATID =>’SYS_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- IMPORT 된 Dictionary 통계정보 확인 하기
SQL> select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
where table_name like ’%$’
order by 2;
SRS$ 20081229 22:00:05 22:00:05
X$LOGMNR_ATTRIBUTE$ 20090224 10:07:19 10:07:19
X$LOGMNR_COLTYPE$ 20090224 10:07:19 10:07:19
X$LOGMNR_IND$ 20090224 10:07:19 10:07:19
X$LOGMNR_COL$ 20090224 10:07:19 10:07:19
X$LOGMNR_DICT$ 20090224 10:07:19 10:07:19
==> 테스트 에서는 실제로 Dictionary 통계정보를 가진 Table Count 가 오히려 줄어들었다.
5. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’, STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.
-- 복원된 정보 확인 하기
SQL> select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
where table_name like ’%$’
order by 2;
[ 4.User Table Statistics ]
: 사용자의 Object 에 대한 통계치 수집은 10G 에서 기본적인 GATHER_STATS_JOB 을
이용한다. 이 JOB 은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가
변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행한다.
또한 분석하는 순서 역시 우선순위 순으로 수행한다.
만약 Object 에 많은 Data 가 Load 되거나 변경이 있는 경우에는 Manual 하게
수행한다.
- 10G 에서 말하는 자동 통계정보 수집 기능(Automatic Statistics) 의 대상은
1. AWR(Automactic Workload Repository)
- 분석, Self-Tuing 및 일반적인 Tuning 을 목적으로 하는 자료로
특정 시간 동안 데이타 베이스에서 발생한 여러 가지 상황 정보를 이른다.
Wait Events, Latces, Enqueues, Cpu Consumption, SGA Compoenent, PGA 등에
대한 자료 이다.
’STATISTICS_LEVEL’ 에 의해서 수집되는 자료의 LEVEL 이 결정된다.
AWR 정보는 디폴트로 7일간 보관한다. [ SYSAUX TABLESPACE 에 ]
2. CBO(Cost-Based Optimizer)
- Database 의 Object 즉, Application 및 Oracle Internal (Sys/System) 유저의
Table, Index 에 대한 통계정보 수집
이 수집된 정보는 Optimizer 가 수행계획 수립 시에 의해 사용된다.
GATHER_STATS_JOB 에 의해서 수집 된다. [DB 생성시 자동 생성됨]
Optimizer historical 통계정보는 디폴트로 31일간 보관한다.
STATISTICS_LEVEL=BASIC 이면 CBO 통계정보가 수집되지 않는다.
- Automatic Optimizer Statistics Collection
- Object level Statistics
[ SYSAUX TABLESPACE 에 ]
-- SYSAUX 사용 현황 파악 하기
SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;
1. SM/AWR
- AWR 정보 수집
- AWR 정보 수집 옵션 확인
SQL> SELECT DBID, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL ;
DBID RETENTION TOPNSQL
---------- ------------------------------ ----------
2466823093 +00007 00:00:00.0 DEFAULT
보존 변경은 dbms_workload_repository.modify_snapshot_settings 를 통해서 가능
-- AWR 정보 한달 보관주기로 변경 [ 60*24*31 = 44640 분 ]
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 44640 );
PL/SQL procedure successfully completed.
-- 변경되 AWR 정보 확인
SQL> SELECT DBID, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL ;
DBID RETENTION TOPNSQL
---------- ------------------------------ ----------
2466823093 +00031 00:00:00.0 DEFAULT
2. SM/ADVISOR
SQL Tuning Advisor, SQL Access Advisor, ADDM 이 사용하는 정보 저장소
3. SM/OPTSTAT
- 구버전(Old) Optimizer 통계정보 저장소
- SM/OPTSTAT 저장 기간 확인
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
==> Default 로 31일
-- 10일로 조절
SQL> exec dbms_stats.alter_stats_history_retention(10);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
10
-- 원복
SQL> exec dbms_stats.alter_stats_history_retention(31);
PL/SQL procedure successfully completed.
4. SM/OTHER
- Alert History 등의 저장소
-- STATISTICS_LEVEL PARAMETER
: Database 와 OS 의 통계정보의 수집 Level 을 제어 하는 파라미터
1.Typical
- Default, 일반적인 환경에 가장 적합
2. ALL
- typical + Timed OS Statistics + Plan Execution Statistics
3. BASIC
- 아래 기능을에 필요한 중요한 통계정보를 수집 할 수 없다.
- AWR
- ADDM
- All Server-Generated Alerts
- Automatic SGA Memory Management
- Automatic Optimizer Statistics Collection
- Object level Statistics
등...
실습 : 여기서 말하는 자동 통계수집 이란 CBO 에 대한 것을 이른다.
- 1. 자동 통계수집일정을 확인하고
- 2. 자동 통계수집을 Disable 해보자
- 3. 통계정보 백업 / 복구 하기
- 4. 특정 테이블 통계수집 중지 하기
-- 1.1 자동통계정보 수집 확인 하기
SQL > select job_name, job_type, program_name, schedule_name, job_class
from dba_scheduler_jobs
where job_name =’GATHER_STATS_JOB’;
JOB_NAME JOB_TYPE PROGRAM_NAME SCHEDULE_NAME JOB_CLASS
-------------------- ---------------- -------------------- ------------------------------ ------------------------------
GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS
-- 1.2 자동통계정보 수집 시 실행 되는 Program 확인
SQL> select program_Action from dba_scheduler_programs where program_name =’GATHER_STATS_PROG’;
PROGRAM_ACTION
--------------------------------------------------
dbms_stats.gather_database_stats_job_proc
-- 1.3 자동통계정보 수집 시 스케줄 확인
SQL> select * from dba_scheduler_wingroup_members where window_group_name =’MAINTENANCE_WINDOW_GROUP’;
WINDOW_GROUP_NAME WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW
-- 1.4 자동통계정보 수집 시 스케줄 상세 확인
SQL> select window_name, repeat_interval, duration
from dba_scheduler_windows
where window_name in (’WEEKNIGHT_WINDOW’,’WEEKEND_WINDOW’);
WINDOW_NAME REPEAT_INTERVAL DURATION
-------------------- -------------------------------------------------------------------------------- --------------------
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00
-- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행 된다.
-- 토요일 0시에 수행되어 이틀 동안 수행된다.
-- 2.1 자동통계정보 수집 중지
-- STATISTICS_LEVEL=BASIC 이면 자동통계정보 수집(CBO)
SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;
JOB_NAME STATE
-------------------- ---------------
GATHER_STATS_JOB SCHEDULED
SQL> exec dbms_scheduler.disable(’GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;
JOB_NAME STATE
-------------------- ---------------
GATHER_STATS_JOB DISABLED
-- 2.2 자동통계정보 수집 재설정
SQL> exec dbms_scheduler.enable(’GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;
JOB_NAME STATE
-------------------- ---------------
GATHER_STATS_JOB SCHEDULED
-- 3.1 통계정보 백업 / 복구 하기
-- 유저 테이블 통계정보 백업 받을 테이블 생성하기
SQL> execute dbms_stats.create_stat_table(’SYS’,’USER_STATS’,’USERS’);
PL/SQL procedure successfully completed.
-- SCOTT 유저 테이블 통계정보 백업 받기
SQL> execute dbms_stats.export_schema_stats(’SCOTT’,’USER_STATS’,’SCOTT’,’SYS’);
PL/SQL procedure successfully completed.
-- 백업된 SCOTT 유저의 통계정보 확인
SQL> select STATID, C1, C2, C4, D1 from USER_STATS ;
-- 3.2 신규로 유저 테이블 통계정보 생성
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,-
GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
CASCADE => TRUE );
-- 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;
-- 날짜에 주목하자
-- 3.3 유저 테이블 통계정보 원복하기
SQL> exec dbms_stats.delete_schema_stats(’SCOTT’);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.import_schema_stats(’SCOTT’,’USER_STATS’,’USER_STATS’,’SYS’);
PL/SQL procedure successfully completed.
-- 3.4 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;
-- 날짜에 주목하자
-- 4.1 특정 테이블 통계수집 중지 하기
-- 수동으로 통계정보 수집 하여 LAST_ANALYZED Update 하기
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,-
GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
CASCADE => TRUE );
PL/SQL procedure successfully completed.
-- 4.2 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;
-- 날짜에 주목하자
-- 특정 테이블 통계정보 수집 막기
SQL> execute dbms_stats.lock_table_stats(’SCOTT’,’T1’);
PL/SQL procedure successfully completed.
-- 특정 테이블 통계정보 수집 막음 확인
SQL> SELECT owner, table_name, stattype_locked
FROM dba_tab_statistics
WHERE OWNER=’SCOTT’
and stattype_locked is not null;
OWNER TABLE_NAME STATT
------------------------------ ------------------------------ -----
SCOTT T1 ALL
-- 확인을 위해서 수동으로 통계정보 수집 하여 LAST_ANALYZED Update 하기
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,-
> GRANULARITY =>’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
> CASCADE => TRUE );
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
from dba_tab_statistics
WHERE OWNER=’SCOTT’
and table_name in (’T1’,’EMP’,’DEPT’);
OWNER TABLE_NAME TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ ------------------------------ --------------------------
SCOTT DEPT 20090224 11:37:57 11:37:57
SCOTT EMP 20090224 11:37:57 11:37:57
SCOTT T1 20090224 11:32:53 11:32:53
==> Lock 되어진 T1 에 대해서는 테이블 통계정보가 생성되지 않았음을 확인
