#통계정보란
# 히스토그램 수집
아래 키워드 누락시. Default 수집하게 되어있음,
method_opt=>'FOR ALL COLUMNS SIZE 1' -- 수집 안함
* FOR ALL COLUMNS SIZE 1 = 칼럼에 대한 통계정보도 수집
<-> FOR COLUMNS = 칼럼에 대한 통계정보 수집하지 말아라
#파티션 통계정보 재생성
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SPP',TABNAME=>'ORD_DTL',PARTNAME=>'P201710', GRANULARITY=>'PARTITION', DEGREE => '4' ,CASCADE => TRUE)
#파티션 통계정보 카피
EXEC dbms_stats.copy_table_stats('PBJ', 'TEST_P' , srcpartname => 'P_17', dstpartname => 'P_18', FORCE => TRUE );
#파티션 통계정보 카피 ( PR_201603를 PR_201612 로)
EXEC dbms_stats.copy_table_stats('HPC_USR', 'TF_PC_PT_ARST_RETN_HST' , srcpartname => 'PR_201603', dstpartname => 'PR_201612', FORCE => TRUE );
1. 통계정보
- optimazer 가 실행계획을 수립시 참조하는 정보
=> optimazer의 기능을 최적화 하기 위함
- optimazer 가 최적화 되기 위해서는 통계정보를 보고 판단
- Analyze 란 명령어를 사용하여
table 및 index, cluster 의 통계정보를 생성할 수 있다.
- 저장 공간 : data dictionary(자료 사전)
** analyze , dbms_stat 차이
=> analyze 는 Serial Statistics Gathering 기능만 있음
dbms_stats 는 Parallel Gatehring 기능이 있음
=> DBMS_STATS는 EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, GLOBAL_STATS
analyze
. Serial Statistics Gathering
. partition 통계정보는 부정확
. 전체 cluster에 대해서는 통계정보를 수집
. empty_blocks, avg_space, chain_cnt 등도 수집 가능
. 사용자가 지정한 table에 통계정보 수집이 불가
. export/import 기능이 불가능
dbms_stats
. parallel gathering
. partition 통계정보는 정확(사용 권장)
. cluster에 대해서는 통계정보를 수집하지 않음
. CBO와 관련된 통계정보만을 수집
즉 empty_blocks, avg_space, chain_cnt등은 수집 안함
. 사용자가 지정한 table에 통계정보 수집이 가능
. export/import 기능이 가능
이 기능을 이용하여 운영 DB의 통계정보를 개발장비의 통계정보로 복사할 수 있으므로 개발장비의
플랜을 운영장비와 같게 만들 수 있음
2. Analyze
2.1 Analyze 사용법 및 옵션 설명
ANALYZE 오브젝트명 실행옵션 STATISTICS
[VALIDATE STRUCTURE[CASCADE]]
[LIST CHAINED ROWS[INTO tables]]
* 오브젝트 명 (object_type) : TABLE, INDEX, CLUSTER
* 실행 옵션
- COMPUTE : 값을 정확히 계산함, 처리속도 늦음
- ESTIMATE : 자료사전의 값, 데이터 견본을 가지고 통계를 예상함, COMPUTE 정확성 떨어지고, 처리속도 빠름
- DELETE : 테이블의 모든 통계정보를 삭제
* VALIDATE STRUCTURE 절
- 모든 인덱스 블록을 검사하여 블록 손상을 조사.
- INDEX 에 대한 정보를 가지고 있는 INDEX_STATS 데이터 사전에 기록함
** Table Analyze 추출 SQL
select 'analyze table' || table_name || ' estimate statistics;' from user_tables
** 전체 index Analyze 추출 sql
select 'analyze '||object_type||' ps_mom.'||object_name||' compute statistics; ' from dba_objects
2.2 저장 목록
- 테이블
: 총 로우수, 총 블럭 수, 빈 공간의 평균(block), 체인이 발생된 로우 수, 로우의 평균 길이
- 인덱스
: 인덱스 깊이(Depth), Leaf block 개수, Distinct Key 수, Leaf Blocks/Key 평균, Data blocks/Key 평균
Clustering Factor, 가장 큰 Key 값, 가장 작은 Key 값
- 컬럼
: Distinct 한 값의 수, 히스토그램 정보
- 클러스터
: Cluster Key 당 길이의 평균
3. DBMS_STAT
- 오라클은 테이블이나 인덱스와 같은 데이터베이스 오브젝트에 대한 통계 정보 생성을 용이하게 하기 위해
DBMS_STATS 패키지를 제공 한다. 이 패키지를 이용하면 테이블이나 인덱스의 모든 데이터를 근간으로
통계정보를 생성 할 수 있으며, Sample 데이터를 기반으로 하여 통계 정보를 평가 하는 것이 가능하다.
- 세가지 목적
1) 통계 정보의 설정 혹은 생성
2) 통계 정보의 이전
3) 옵티마이저 통계 정보 생성
- 거의 모든 DBMS_STATS 프로시저에는 statown, stattab, statid 라는 파라미터가 존재하여 생성스킬
통계 정보들을 데이터 딕셔너리의 외부에 존재하는 지정한 테이블에 저장할 수 있도록 되어 있다.
외부 테이블에 존재하는 데이터들은 옵티마이저의 판단에는 영향을 미치지 않게 된다.
=> 다중의 통계 정보들을 저장하여 시스템 통계에 사용 할 수 있다.
- 한 특정 객체에 대한 통계 정보를 DBMS_STATS의 프로시저나 함수를 사용하여 변경, 혹은 삭제 하였을 시,
해당 객체를 참조하는 모든 커서들은 Invalid 상태로 변환된다.
3.1 DBMS_STAT procedure 종류
- 키워드를 포함하는 procedure들은 현 트랙잭선에 commit 을 실행한 후, 해당 작업을 실행하고 다시 commit 실행함
1) Create
2) Delete
3) Drop
4) Export
5) Import
6) Gather
3.1.1 Delete 관련 procedure
- Delete 프로시저들은 특정 객체를 대상으로 한 기본 통계 정보 및 유저설정 통계정보를 삭제
1) DELETE_COLUMN_STATS
2) DELETE_INDEX_STATS
3) DELETE_SYSTEM_STATS
4) DELETE_TABLE_STATS
5) DELETE_SCHEMA_STATS
6) DELETE_DATABASE_STATS
ex) exec dbms_stats.delete_table_stats(owname=>'hr', tablename=>'employees'):
3.1.2 EXPORT, IMPORT 관련 procedure
- IMPORT 프로시저들은 stattab 파라미터를 사용하여 지정된 테이블로부터 유저설정 통계정보를 읽어
데이터 딕셔너리 내부에 저장 할 수 있다.
=> SET_*_STATS 와 GET_*_STATS의 사용을 통해 데이터베이스 간의 유저설정 통계정보를 이동 할 수 있다.
=> CREATE_STAT_TABLE 프로시저를 사용하여 생성
CREATE_STAT_TABLE
- 데이터 딕셔너리 외부에 존재하는 통계 정보용 통계 테이블 생성
DROP_STAT_TABLE
- 통계 테이블 삭제
EXPORT_COLUMN_STATS
- 특정 컬럼의 통계정보 추출, stattab에 지정한 테이블에 저장
EXPORT_INDEX_STATS
- 특정 인덱스의 통계정보 추출, stattab에 지정한 테이블에 저장
EXPORT_SYSTEM_STATS
- 특정 시스템의 통계정보 추출, stattab에 지정한 테이블에 저장
EXPORT_TABLE_STATS
- 특정 테이블의 통계정보 추출, stattab에 지정한 테이블에 저장
EXPORT_SCHEMA_STATS
- 특정 스키마의 통계정보 추출, stattab에 지정한 테이블에 저장
EXPORT_DATABASE_STATS
- DB 내의 모든 객체 통계 정보 추출, statown.stattab 파라미터에 지정된 스키마의 테이블에 저장
IMPORT_COLUMN_STATS
- stattab에서 특정 컬럼의 통계정보 추출, 데이터 딕셔너리에 저장
IMPORT_INDEX_STATS
- stattab에서 특정 인덱스의 통계정보 추출, 데이터 딕셔너리에 저장
IMPORT_TABLE_STATS
- stattab에서 특정 테이블의 통계정보 추출, 데이터 딕셔너리에 저장
IMPORT_SYSTEM_STATS
- stattab에서 시스템 통계정보 추출, 데이터 딕셔너리에 저장
IMPORT_SCHEMA_STATS
- stattab에서 ownname에 지정된 스키마 내의 모든 객체의 통계정보 추출, 데이터 딕셔너리에 저장
IMPORT_DATABASE_STATS
- stattab에서 DB내의 모든 객체에 대해 통계정보 추출, 데이터 딕셔너리에 저장
ex)
-- 통계정보를 저장 할 table 생성
exec dbms_stats.create_stat_table
(owname => 'SCOTT',
stattab =>'my_stats_20160203',
tblspace => 'PBJ_DAT')
- 생략시 default tablespace 에 생성됨
- 통계정보 table 백업 => 만들어진 'my_stats_20160203' 테이블에 정보가 들어감'
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'MY_STATS_20160203');
- 통계정보 table 삭제
exec dbms_stats.drop_stat_table
( owname=>'scott',
stattab =>'my_stats_20160203')
- 통계정보 복구
SCOTT@ORCL > exec DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB => 'MY_STATS_20160203');
PL/SQL procedure successfully completed
3.1.3 Gather 관련 procedure
GATHER 프로시저들을 사용, 유저설정 통계정보를 특정 컬럼 레벨이나 도메인 인덱스 레벨로 생성 할 수 있다.
GATHER_INDEX_STATS
- 인덱스 통계 정보 생성
GATHER_TABLE_STATS
- 테이블과 테이블 내 컬럼 (및 인덱스) 통계 정보 생성
GATHER_SCHEMA_STATS
- 해당 스키마 내 모든 객체들의 통계 정보 생성
GATHER_DATABASE_STATS
- DB 내의 모든 객체들의 통계 정보 생성
GATHER_SYSTEM_STATS
- 시스템의 통계 정보 생성
GENERATE_STATS
- 특정 객체의 기존의 통계 정보들에서 더 상세한 통계 정보 생성. B-TREE 인덱스와 BITMAP 인덱스를 대상으로 사용 가능.
- 프로시저 인자 값 설명
Ownname => 스키마 이름
Indname => 인덱스 이름
Partname => 파티션 이름
Tabname => 테이블 이름
Statown => 통계 테이블이 속한 스키마의 이름
Stattab => 특정 통계 테이블의 이름
Statid => stattab 내의 통계 정보의 ID 설정
no_validate => TRUE로 설정할 시 해당 객체를 참조하는 커서의 재 파싱 실행.
gather_sys => ‘SYS’ 유저의 객체에 대해 통계 정보 생성
interval => 지정 분 동안의 DB 실시간 SGA 사용량의 의거한 통계 정보 생성. Gathering_mode=’INTERVAL’ 일 때에만 사용 가능.
Degree => 병렬 처리 개수 설정. NULL 시 해당 객체의 생성시에 명시된 DEGREE 값이 적용. DBMS_STATS.DEFAULT_DEGREE 를 사용하여 INIT 파라미터 값 사용 가능.
Cascade => 테이블 대상 시에 해당 테이블의 모든 인덱스에 대해서도 통계 정보 생성. 이 때 인덱스 통계 정보는 병렬 사용 불가능.
gather_temp => global temporary table에 통계 정보 저장. 해당 테이블은 “on commit ---serve rows” 옵션을 사용해야만 함. 이 테이블에 저장된 통계 정보는 모든 세션들에 대해 공유 가능
tblspace => 통계 테이블이 생성될 테이블스페이스의 이름. 지정하지 않을 시에는 실행 유저의 Default 테이블스페이스 사용.
4. 자동 통계정보 수집 기능(Automatic Statistics)
gather_stats_job에 의해 통계정보들이 수집이 됩니다.
gather_stats_job은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가 변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행합니다.
- AWR(Automactic Workload Repository)
AWR의 정보는 default로 7
- CBO(Cost-Based Optimizer)
Optimizer historical 통계정보는 default로 31일간
statistics_level = {all | typical | basic}
typical => 전반적인 DB 성능과 관련 통계정보와 DB 자가 관리 기능관련 정보를 수집합니다. (기본값 설정)
ex. AWR(Automatic Workload Repository) , ADDM(Automatic Database Diagnostic Monitor),
ASSM(Automatic SGA Memory Management), CBO 통계정보 등등
all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집 합니다.
basic => 수집을 하지 않습니다.
=> 변경 방법
1) Database level 적용
alter system set statistics_level='ALL'
2) session level 적용
alter session set statistics_level='ALL'
# 자동 통계정보 수집 시 스케줄 상세 확인
select window_name, repeat_interval, duration
from dba_scheduler_windows
where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');
# AUTOTASK DISABLE 확인
select * from dba_autotask_client;
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME=>'AUTO OPTIMIZER STATS COLLECTION', OPERATION=> NULL, WINDOW_NAME=> NULL);
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME=>'SQL TUNING ADVISOR', OPERATION=> NULL, WINDOW_NAME=> NULL);
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME=>'AUTO SPACE ADVISOR', OPERATION=> NULL, WINDOW_NAME=> NULL);
EXEC DBMS_SCHEDULER.DISABLE('SUNDAY_WINDOW');
EXEC DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
EXEC DBMS_SCHEDULER.DISABLE('TUESDAY_WINDOW');
EXEC DBMS_SCHEDULER.DISABLE('WEDNESDAY_WINDOW');
EXEC DBMS_SCHEDULER.DISABLE('THURSDAY_WINDOW');
EXEC DBMS_SCHEDULER.DISABLE('FRIDAY_WINDOW');
EXEC DBMS_SCHEDULER.DISABLE('SATURDAY_WINDOW');
# 통계이력 확인 (30일동안 수행된것)
select * from DBA_AUTOTASK_SCHEDULE order by start_time desc;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by window_start_time desc;
# 자동 통계정보 수집 확인 방법
SELECT owner,
job_name,
job_type,
enabled
FROM dba_scheduler_jobs
WHERE job_name='BSLN_MAINTAIN_STATS_JOB';
exec DBMS_SCHEDULER.DISABLE('BSLN_MAINTAIN_STATS_JOB');
exec DBMS_SCHEDULER.ENABLE('BSLN_MAINTAIN_STATS_JOB');
***
index 가 걸려있는 테이블을 통계정보 생성을 할때.
index 상태가 unusable 상태이면 통계정보 생성아 되지 않음.
=> index 상태 체크 후 -> unusable index rebuild 필요.
-------------------------------------------------------------------------------------------
SCOTT계정의 DEPT 테이블 기준으로 통계정보를 EXPORT , IMPORT , DELETE .
1) 통계정보를 생성하기 위해 stattab 생성
exec dbms_stats.create_stat_table('SCOTT','STATS_20160210');
SCOTT@ORCL > select table_name, tablespace_name from user_tables where table_name='STATS_20160210';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STATS_20160210 USERS
2) 통계정보 생성
exec dbms_stats.gather_table_stats('scott','dept');
PL/SQL procedure successfully completed.
SCOTT@ORCL >
SCOTT@ORCL > select table_name, last_analyzed from user_tables where table_name ='DEPT';
TABLE_NAME LAST_ANALYZE
------------------------------ ------------
DEPT 10-FEB-16
3) 통계정보 export
exec dbms_stats.export_table_stats( ownname=>'SCOTT', tabname=>'DEPT',stattab=>'STATS_20160210',statid=>'DEPT',cascade=>TRUE );
PL/SQL procedure successfully completed.
select statid, c1, c4,c5 from stats_20160210;
STATID C1 C4 C5
------- ------- ------- -------
DEPT DEPT SCOTT
DEPT PK_DEPT SCOTT
DEPT DEPT DEPTNO SCOTT
DEPT DEPT DNAME SCOTT
DEPT DEPT LOC SCOTT
4) 통계정보 delete
exec dbms_stats.delete_table_stats( ownname => 'SCOTT', tabname =>'DEPT' );
PL/SQL procedure successfully completed
select table_name, last_analyzed from user_tables where table_name ='DEPT';
TABLE_NAME LAST_ANALYZE
------------------------------ ------------
DEPT
5) 통계정보 import
exec dbms_stats.import_table_stats( ownname=>'SCOTT', tabname=>'DEPT', stattab=>'STATS_20160210',statid=>'DEPT', cascade=>TRUE );
PL/SQL procedure successfully completed.
select table_name, last_analyzed from user_tables where table_name ='DEPT';
TABLE_NAME LAST_ANALYZE
------------------------------ ------------
DEPT 10-FEB-16
.
---------------------------------------------------------------
#통계정보 복원
exec DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'NHCON_FRONT', STATTAB => 'NHCON_FRONT_STATS')
exec DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'NHCON_PLATFORM', STATTAB => 'NHCON_PLATFORM_STATS')
---------------------------------------------------------------
test
1) scott.bonus 테이블과 모든 인덱스를 가지고 테이블, 칼럼, 연관 인덱스 통계정보 생성
exec dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'BONUS', cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1' );
* FOR ALL COLUMNS SIZE 1 = 칼럼에 대한 통계정보도 수집
<-> FOR COLUMNS = 칼럼에 대한 통계정보 수집하지 말아라
* cascade = TURE = 인덱스에 대한 통계정보도 수집
2) scott.dept 테이블의 15% row 를 가지고, 테이블 칼럼, 연관 인텍스의 통계정보 생성
exec dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'DEPT', cascade=>TRUE, estimate_percent=>15 );
3) scott.dept 테이블의 전체 테이블과 모든 인덱스를 가지고 테이블 통계정보 수집하라. 단 인덱스, 칼럼에 대한 통계정보는 제외
exec dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'DEPT', cascade=>FALSE, method_opt=>'FOR COLUMNS') ;
4) 10g 에서는 간혹 dbms_stats.gather_schema_stat 수행이 오래걸린다.
=> 11g 에서 Preference(선호도) 개념으로 이것을 해결
=> 문제 이유 : 특정 테이블이 너무 크거나, 일부 큰 테이블들은 파티션되어 있다.
=> 오라클은 스스로 적절한 판단으로 estimate_persent, method_opt 를 사용한다.
=> 그러나, estimate_percent 값이 AUTO 로 인해 , 매우큰 테이블의 샘플 크기를 100%로 사용한다.
=> method_opt 기본값은 FOR ALL COLUMNS SIZE AUTO 이므로 히스토그램 생성에서 Unique Key에대한 히스토그램까지 생성함
통계정보 설정을, estimate_persent => ROWS 10%, method_opt => FOR ALL INDEXED COLUMNS 으로 변경
exec dbms_stats.set_table_prefs(user,'DEPT', 'ESTIMATE_PERCENT','10');
exec dbms_stats.set_table_prefs(user,'DEPT', 'METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');
스키마 레벨로 통계정보 수집
exec dbms_stats.gather_schema_stats(user);
*10g 에서는 특정 테이블에 대하여
lock_table_stat -> gather_schema_stats(user) : 스키마 단 설정 -> unlock_table_stats -> 통계정보 수행
5) 통계정보 자동 백업일 확인
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
6) 통계정보 시점 복구
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where owner = 'SCOTT';
TABLE_NAME STATS_UPDATE_TIME
EMP 06-FEB-16 10.02.25.261568 AM +09:00
EMP 06-FEB-16 06.02.37.821887 PM +09:00
EMP 06-FEB-16 10.02.41.688304 PM +09:00
EMP 10-FEB-16 09.39.56.053559 AM +09:00
EMP 10-FEB-16 09.40.00.099617 AM +09:00
EMP 10-FEB-16 09.45.01.793673 AM +09:00
DEPT 10-FEB-16 09.48.54.423307 AM +09:00
EMP 07-FEB-16 10.03.05.976901 AM +09:00
EMP 04-FEB-16 10.00.10.162572 PM +09:00
A 03-FEB-16 04.44.36.237895 PM +09:00
EMP 05-FEB-16 10.00.09.598270 PM +09:00
B 03-FEB-16 04.44.36.254938 PM +09:00
exec dbms_stats.restore_table_stats('SCOTT','EMP','06-FEB-16 10.02.41.688304 PM +09:00');
6) 자동 통계수집 기능 확인
SYS@ORCL AS SYSDBA> SELECT owner,
job_name,
job_type,
enabled
FROM dba_scheduler_jobs
WHERE job_name='BSLN_MAINTAIN_STATS_JOB';
OWNER JOB_NAME JOB_TYPE ENABL
---------- ------------------------------ ---------------- -----
SYS BSLN_MAINTAIN_STATS_JOB TRUE
SYS@ORCL AS SYSDBA> SELECT client_name,
status
FROM dba_autotask_client
WHERE client_name='auto optimizer stats collection';
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
* 자동통계수집 기능 비활성화
exec DBMS_SCHEDULER.DISABLE('BSLN_MAINTAIN_STATS_JOB');
SYS@ORCL AS SYSDBA> SELECT owner,
2 job_name,
3 job_type,
4 enabled
5 FROM dba_scheduler_jobs
6 WHERE job_name='BSLN_MAINTAIN_STATS_JOB';
OWNER JOB_NAME JOB_TYPE ENABL
---------- ------------------------------ ---------------- -----
SYS BSLN_MAINTAIN_STATS_JOB FALSE
exec DBMS_SCHEDULER.ENABLE('BSLN_MAINTAIN_STATS_JOB');
11g 부터 통계정보 자동 갱신 스케줄러가 돈다.
스케줄러 명 : BSLN_MAINTAIN_STATS_SCHED
잡 명 : BSLN_MAINTAIN_STATS_JOB
select *
from dba_scheduler_schedules
WHERE SCHEDULE_NAME='BSLN_MAINTAIN_STATS_SCHED'
- 잡 돈거 확인
select * from dba_scheduler_job_run_details
WHERE JOB_NAME='BSLN_MAINTAIN_STATS_JOB'
ORDER BY ACTUAL_START_DATE DESC
- 스케줄러 변경 -> 언제 돌지 설정 가능
exec sys.dbms_scheduler.set_attribute( name => 'BSLN_MAINTAIN_STATS_SCHED', attribute =>'repeat_interval', value => 'FREQ=WEEKLY;byhour=1;');
- 잡 설정 확인 (상세 설정 가능)
SELECT *
FROM dba_scheduler_jobs
where job_name='BSLN_MAINTAIN_STATS_JOB'
exec sys.dbms_scheduler.set_attribute( name => 'BSLN_MAINTAIN_STATS_JOB', attribute => 'MAX_RUN_DURATION', value => TO_DSINTERVAL('+000 05:00:00'));
7) 파티션 테이블과 연관된 인덱스 및 통계정보 조회
select index_name, partitioned from dba_indexes where table_name = 'HASH_PT';
index_name partitioned
NO_IDX NO
NO_IDX NO
dba_part_key_columns
dba_subpart_key_columns
dba_indexes
dba_ind_partition
7) 파티션 추가후 통계정보가 생성되지 않았다면 통계정보 재생성 및 COPY 필요
------------------------------------------------------------------------------
CREATE TABLE salet
(salet_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL,
customer_name VARCHAR2(30),
price NUMBER)
PARTITION BY RANGE (sale_year, sale_month, sale_day)
(PARTITION sales_t1 VALUES LESS THAN (2016, 01, 01) TABLESPACE TBS_A,
PARTITION sales_t2 VALUES LESS THAN (2016, 02, 01) TABLESPACE TBS_B,
PARTITION sales_t3 VALUES LESS THAN (2016, 03, 01) TABLESPACE TBS_C,
PARTITION sales_t4 VALUES LESS THAN (2016, 04, 01) TABLESPACE TBS_D,
PARTITION sales_t5 VALUES LESS THAN (2016, 05, 01) TABLESPACE TBS_D,
PARTITION sales_t6 VALUES LESS THAN (2016, 06, 01) TABLESPACE TBS_D,
PARTITION sales_t7 VALUES LESS THAN (2016, 07, 01) TABLESPACE TBS_D,
PARTITION sales_t8 VALUES LESS THAN (2016, 08, 01) TABLESPACE TBS_D,
PARTITION sales_t9 VALUES LESS THAN (2016, 09, 01) TABLESPACE TBS_D );
CREATE INDEX SALET_NO_IDX ON SALET(SALET_NO)
TABLESPACE TBS_D
LOCAL
INSERT INTO salet
SELECT ROWNUM AS salet_no,
2016 AS sale_year,
'0'|| MOD(ROWNUM, 8)+1 AS sale_month,
MOD(ROWNUM,27)+1 AS sale_day,
DBMS_RANDOM.STRING('u', 5) AS CUSTOMER_NAME,
CEIL(DBMS_RANDOM.VALUE(1000,10000) )AS price
FROM dual CONNECT BY ROWNUM <= 25000;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','SALET');
CREATE INDEX IDX_DEPTNO_DEPTTEST ON DEPTTEST(DEPTNO)
TABLESPACE TBS_D
LOCAL
INSERT INTO DEPTTEST
SELECT ROWNUM AS DEPTNO,
DBMS_RANDOM.STRING('u', 5) AS CUSTOMER_NAME
FROM dual CONNECT BY ROWNUM <= 40000;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','DEPTTEST');
select *
from salet s , deptTEST D
where S.salet_no = D.DEPTNO
AND SALET_NO IN ( 1234, 14500 ,13000)
결과 : SALET 테이블에 25000건, DEPTTEST 테이블에 4만건 데이터 추가 -> 통계정보 실행 -> 조회
SELECT STATEMENT OPTIMIZE = CHOOSE
HASH JOIN (Cost=22 Card=3 Bytes=108)
PARTITION RANGE(ALL) (Cost=17 Card=3 Bytes=75)
| INLIST ITERATOR
| TABLE ACCESS ( BY LOCAL INDEX ROWID) OF'SALET'(TABLE)(Cost=17 Card=3 Bytes=75)
| INDEX(RANGE SCAN) OF 'SALET_NO_IDX'(INDEX)(Cost=16 Card=3)
|_ INLIST ITERATOR
TABLE ACCESS ( BY INDEX ROWID) OF'DEPTTEST'(TABLE)(Cost=5 Card=3 Bytes=33)
INDEX(RANGE SCAN) OF 'IDX_DEPTNO_DEPTTEST'(INDEX)(Cost=4 Card=3)
ALTER TABLE SALET
ADD PARTITION sales_T10 values less than (2016, 10, 01)
TABLESPACE TBS_A;
ALTER TABLE SALET
ADD PARTITION sales_T11 values less than (2016, 11, 01)
TABLESPACE TBS_B;
ALTER TABLE SALET
ADD PARTITION sales_T12 values less than (2016, 12, 01)
TABLESPACE TBS_C;
ALTER TABLE SALET
ADD PARTITION sales_T13 values less than (2017, 01, 01)
TABLESPACE TBS_D;
INSERT INTO saleT
SELECT ROWNUM+25000 AS saleT_no,
2016 AS sale_year,
'1'|| MOD(ROWNUM, 2) AS sale_month,
MOD(ROWNUM,27)+1 AS sale_day,
DBMS_RANDOM.STRING('u', 5) AS CUSTOMER_NAME,
CEIL(DBMS_RANDOM.VALUE(25000,40000))AS price
FROM dual CONNECT BY ROWNUM <= 15000;
select *
from salet s , deptTEST D
where S.salet_no = D.DEPTNO
AND SALET_NO IN ( 25500, 39000,30102)
결과 : SALET 테이블에 15000 테이터 추가후 -> 쿼리 질의
SELECT STATEMENT OPTIMIZE = CHOOSE
NESTED LOOPS
NESTED LOOPS(Cost=21 Card=2 Bytes=50)
| PARTITION RANGE(ALL) (Cost=17 Card=2 Bytes=50)
| | INLIST ITERATOR
| | TABLE ACCESS ( BY LOCAL INDEX ROWID) OF'SALET'(TABLE)(Cost=17 Card=2 Bytes=50)
| | INDEX(RANGE SCAN) OF 'SALET_NO_IDX'(INDEX)(Cost=16 Card=2)
| |______ INDEX(RANGE SCAN) OF 'IDX_DEPTNO_DEPTTEST'(INDEX)(Cost=1 Card=1)
|__ TABLE ACCESS ( BY INDEX ROWID) OF'DEPTTEST'(TABLE)(Cost=2 Card=1 Bytes=11)
즉. 통계정보가 실행이 되지 않았을 경우 옵티마이저 자신이 판단하기에 좋은 방향으로 실행됨을 알수 있음
-----------------------------------------------------------------------
dba_part_indexes TABLE = local partitions index 만을 확인됨.
select * from dba_part_indexes
where owner = 'SCOTT'
select * from user_indexes
where table_name ='SALET'
=> partitioned 값이 yes 면 local
no 면 grobal
global_stats
-no : 인덱스 파티션에 대한 통계가 추정됬을때
yes : 전체 인덱스를 분석하여 수집이 되었을때.
이전 실행계획 (a)
인덱스 추가 ->쿼리 실행 -> 실행계획 확인 (b)
통계정보 어제 일자로 대체(Replace)
같은 쿼리 실행 -> 실행계획을 바뀌는지 확인 ( 기존의 실행계획을 타는지. 이전의 실행계획을 타는지 )
DROP INDEX SALET_NO_IDX;
create index SALE_YEAR_idx on SALET(SALET_NO, SALE_MONTH);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','SALET');
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where owner = 'SCOTT' and table_name='SALET' ORDER BY STATS_UPDATE_TIME;
SYS@ORCL AS SYSDBA>exec dbms_stats.restore_table_stats('SCOTT','SALET','11-FEB-16 03.00.02.921112 PM +09:00');
PL/SQL procedure successfully completed.
쿼리 실행시. 이전의 통계정보를 불러오게됨.
실행계획이 달라진것을 확인.
exec dbms_stats.restore_table_stats('SCOTT','SALET','11-FEB-16 03.00.02.921112 PM +09:00');
12-FEB-16 05.52.29.920156 PM +09:00
://bosoa.egloos.com/4023967
1) 변경
- ENABLE
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
- DISABLE
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
2) 확인
DBA_AUTOTASK_WINDOW_CLIENTS OPTIMIZER_STATS 컬럼 값
set linesize 200
col WINDOW_NAME for a20
col window_next_time for a40
select * from DBA_AUTOTASK_WINDOW_CLIENTS OPTIMIZER_STATS;
** OPTIMIZE 항목이 ENABLED이면 활성화 된것임
------------------------------------------------------------------
-- 다른거
11g에서는 아래 2개를 확인하시어 모두 FALSE로 해야 함
select owner,job_name,job_type,enabled
from dba_scheduler_jobs
where job_name='BSLN_MAINTAIN_STATS_JOB';
col client_name for a40
select client_name, status
from Dba_Autotask_Client
where client_name='auto optimizer stats collection' ;
sqlplus “/ as sysdba”
exec DBMS_SCHEDULER.DISABLE('BSLN_MAINTAIN_STATS_JOB');
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
COMMIT;
'Oracle > 통계정보' 카테고리의 다른 글
| dbms_stats.lock_table_stats (0) | 2022.01.24 |
|---|---|
| Histogram 사용하다가 중지할 경우 발생되는 이슈, 장애처리 (0) | 2022.01.24 |