"오라클 통계정보 이동"의 두 판 사이의 차이
DB CAFE
(→DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신(쿼리 생성)) |
(→테이블,인덱스포함 통계정보 갱신) |
||
(같은 사용자의 중간 판 57개는 보이지 않습니다) | |||
6번째 줄: | 6번째 줄: | ||
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', method_opt => 'for all indexed columns',cascade => true); | EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', method_opt => 'for all indexed columns',cascade => true); | ||
</source> | </source> | ||
+ | |||
+ | {{:오라클 테이블 통계정보 생성}} | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
BEGIN | BEGIN | ||
15번째 줄: | 18번째 줄: | ||
END; | END; | ||
</source> | </source> | ||
− | { | + | {| class="wikitable" |
− | | | + | |- |
− | + | ! Parameter !! Description | |
− | # | + | |- |
− | # | + | | Ownname || 분석할 테이블 소유자 |
− | # | + | |- |
− | # | + | | tabname || 테이블 이름 |
− | + | |- | |
− | + | | partname || 파티션 이름, 지정 하지 않으면 NULL 값 | |
− | + | |- | |
− | } | + | | Estimate_percent || 분석할 Row의 Percentage, NULL 이면 Compute(Row 전체) |
+ | 유효값은 1/1000000 ~ 100 | ||
+ | 디폴트로, DBMS_STATS.AUTO_SAMPLE_SIZE 에 의해서 최적의 값을 결정 | ||
+ | |- | ||
+ | | Block_sample || random block sampling or random row sampling 결정 | ||
+ | random block sampling 이 좀더 효과적이다. | ||
+ | 데이터의 블록 별 분포도가 안좋을 시에는 부적절한 정보 생성 | ||
+ | 디폴트 값이 False로, random row sampling 을 수행한다. | ||
+ | |- | ||
+ | | Method_opt || Histogram 생성시 사용하는 옵션 | ||
+ | FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ] | ||
+ | FOR COLUMN [ size clause ] column | attribute [size clause] | ||
+ | [, column|attribute [ size clause ]…] | ||
+ | * Size_clause := SIZE { integer | REPEAT | AUTO | SKEWONLY } | ||
+ | |||
+ | n Integer : Histogram Bucket 수, Max 는 1,254 | ||
+ | |||
+ | n REPEAT : 이미 Histogram 이 있는 칼럼에 대해서만 생성 | ||
+ | |||
+ | n AUTO : 데이터 분산도와 칼럼 부하 정도에 따라서 생성 결정 | ||
+ | |||
+ | n SKEWONLY : 데이터 분산도에 따라서 생성 결정 | ||
+ | |||
+ | 디폴트 값은 FOR ALL COLUMNS SIZE AUTO | ||
+ | * Histogram 의 생성여부를 Oracle 이 알아서 판단하게 된다. | ||
+ | 이 경우 | ||
+ | EX) method_opt => FOR ALL COLUMNS SIZE 1 | ||
+ | * 모든 컬럼에 대해서 Histogram bucket 의 수를 1로 한다. | ||
+ | * 즉, 컬럼 내에 존재하는 여러 가지 값들의 cardinality 는 모두 동일한 값으로 간주한다. ( histogram 을 사용하지 않는다.) | ||
+ | * 이것은 where 조건에 들어오는 특정 컬럼에 대한 값의 변화에 따라서 PLAN 이 변경될 가능성을 없애고자 함이다. | ||
+ | * FOR ALL COLUMNS SIZE 1 로 설정하여 Histogram 정보가 생성되지 않도록 조치 한다. | ||
+ | |- | ||
+ | |- degree || 병렬처리 정도 | ||
+ | # 디폴트 값은 NULL 이고, CREATE TABLE, ALTER TABLE 시 설정된 DEGREE 값에 의해 정해진다. | ||
+ | # AUTO_DEGREE 값은 병렬처리 정도를 자동으로 결정한다. | ||
+ | # 이것은 1 or DEFAULT_DEGREE [ Object Size 와 CPU Count 에 의해 결정 ] | ||
+ | |- | ||
+ | | granularity || Parition table 에 대한 분석시 사용 | ||
+ | # ‘ALL’ – Global, Partition, Subpartition 통계정보 수집 – Parition Table 대상 | ||
+ | # ‘AUTO’ – 디폴트 값으로 ,Partition Type 에 따라서 결정 – 일반 Table 대상 | ||
+ | # ‘DEFAULT’ – Global, Partition 통계정보 수집, Old Version 과 호환을 위해 사용 | ||
+ | # ‘GLOBAL’ - Global 통계정보 수집 | ||
+ | # ‘GLOBAL AND PARTITION’ – SubPartition 에 대한 통계정보는 수집되지 않는다. | ||
+ | # ‘PARTITION’ – Partition 통계정보 수집 | ||
+ | # ‘SUBPARTITION’ – SubPartition 통계정보 수집 | ||
+ | |- | ||
+ | | cascade || 대상 테이블의 인덱스에 대한 통계수집 여부 | ||
+ | 인덱스 통계정보는 병렬처리가 불가능하다. | ||
+ | TRUE – 대상 테이블에 관련된 index 에 대해서 통계정보 수집 | ||
+ | |- | ||
+ | | stattab || 통계수집을 통한 기존 통계정보 Update 전에, | ||
+ | 기존에 존재하는 통계정보를 저장할 User Stat Table 을 지정 | ||
+ | |- | ||
+ | | statid || Stattab 와 연관된 구분자 값 | ||
+ | |- | ||
+ | | statown || Stattab 에 지정한 User Stat Table 의 소유자가 다를 경우 지정 | ||
+ | |- | ||
+ | | no_invalidate || | ||
+ | * 의존적인 Cursor를 Invalidate 할지 , 안할지 결정 | ||
+ | *: True – 관련된 Cursor 를 invalidate 하지 않는다. | ||
+ | *: False – 관련된 Cursor 를 Invalidate 한다. | ||
+ | Default 로 DBMS_STATS.AUTO_INVALIDATE 값이고, | ||
+ | 의미는 DBMS 가 의존적 Cursor 를 언제 invalidate 할지 자동으로 결정 | ||
+ | 이때 작용하는 Parameter는 _OPTIMIZER_INVALIDATION_PERIOD 이고, | ||
+ | Default 롤 18000 초(5시간) 이다. | ||
+ | 즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에 해당 Cursor가 실행될 때 invalidation이 발생한다. | ||
+ | 이것을 Auto Invalidation이라고 부른다. | ||
+ | 일정 시간에 걸쳐 랜덤하게 Cursor를 Invalidation함으로써 특정 시점에 | ||
+ | Hard Parse가 한꺼번에 몰리는 현상을 피할 수 있다. | ||
+ | |||
+ | _OPTIMIZER_INVALIDATION_PERIOD 파라미터로 시간 조절 가능 | ||
+ | |- | ||
+ | | force || Lock 걸린 Table 에 대해서도 강제로 통계정보 생성 | ||
+ | |} | ||
+ | |||
{{틀:고지 상자 | {{틀:고지 상자 | ||
|내용= 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다. | |내용= 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다. | ||
}} | }} | ||
− | === 테이블 | + | === 테이블 DBMS_STATS.GATHER_TABLE_STATS === |
− | + | ||
− | + | * 예시 : '''EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8);''' | |
− | + | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * 예시 : | ||
− | |||
− | EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8); | ||
− | |||
<source lang=sql> | <source lang=sql> | ||
EXEC DBMS_STATS.GATHER_TABLE_STATS( | EXEC DBMS_STATS.GATHER_TABLE_STATS( | ||
87번째 줄: | 146번째 줄: | ||
</source> | </source> | ||
− | === 스키마 === | + | === 스키마 DBMS_STATS.GATHER_SCHEMA_STATS === |
<source lang=sql> | <source lang=sql> | ||
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( | EXEC DBMS_STATS.GATHER_SCHEMA_STATS( | ||
95번째 줄: | 154번째 줄: | ||
</source> | </source> | ||
− | === 인덱스 === | + | === 인덱스 DBMS_STATS.GATHER_INDEX_STATS === |
<source lang=sql> | <source lang=sql> | ||
EXEC DBMS_STATS.GATHER_INDEX_STATS( | EXEC DBMS_STATS.GATHER_INDEX_STATS( | ||
110번째 줄: | 169번째 줄: | ||
</source> | </source> | ||
− | === 데이터베이스 === | + | === 데이터베이스 DBMS_STATS.GATHER_DATABASE_ STATS === |
<source lang=sql> | <source lang=sql> | ||
DBMS_STATS.GATHER_DATABASE_ STATS | DBMS_STATS.GATHER_DATABASE_ STATS | ||
</source> | </source> | ||
− | === 시스템통계정보 === | + | === 시스템통계정보 DBMS_STATS.GATHER_SYSTEM_STATS === |
<source lang=sql> | <source lang=sql> | ||
DBMS_STATS.GATHER_SYSTEM_STATS | DBMS_STATS.GATHER_SYSTEM_STATS | ||
</source> | </source> | ||
+ | |||
=== 보다 상세한 통계정보 === | === 보다 상세한 통계정보 === | ||
<source lang=sql> | <source lang=sql> | ||
124번째 줄: | 184번째 줄: | ||
</source> | </source> | ||
− | == | + | == 테이블/인덱스 통계정보를 옮기는 방법. == |
− | + | * DBMS_STATS.GATHER_TABLE_STATS 패키지 이용 | |
− | = | + | {{틀:고지 상자 |
− | + | |내용=:1) 통계정보를 임시로 저장할 Table생성. | |
− | + | :2) 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관. | |
+ | :3) 통계정보 백업 테이블 통계 수집 | ||
+ | :4) 임시 table을 export, 그리고 target db로 import. | ||
+ | :5) 임시 Table로 부터 통계치를 대상 테이블에 입력. | ||
+ | :6) 통계정보 테이블 삭제 | ||
+ | }} | ||
+ | 보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우. | ||
+ | ---- | ||
+ | * '''통계정보 추출''' | ||
+ | ** 테이블 통계 백업 dbms_stats.export_table_stats | ||
+ | ** 스키마 통계 백업 dbms_stats.export_schema_stats | ||
+ | ---- | ||
+ | === 통계정보를 임시로 저장할 Table생성. === | ||
<source lang=sql> | <source lang=sql> | ||
EXEC DBMS_STATS.CREATE_STAT_TABLE ( | EXEC DBMS_STATS.CREATE_STAT_TABLE ( | ||
135번째 줄: | 207번째 줄: | ||
,'테이블스페이스명-생략가능' | ,'테이블스페이스명-생략가능' | ||
); | ); | ||
− | </source> | + | </source> |
− | |||
− | |||
+ | === 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관. === | ||
=====테이블 정보 EXPORT===== | =====테이블 정보 EXPORT===== | ||
* dbms_stats.export_table_stats ('table_owner', 'table name', null, 'stat table','stat_id',true,'stat table owner'); | * dbms_stats.export_table_stats ('table_owner', 'table name', null, 'stat table','stat_id',true,'stat table owner'); | ||
<source lang=sql> | <source lang=sql> | ||
EXEC DBMS_STATS.EXPORT_TABLE_STATS( | EXEC DBMS_STATS.EXPORT_TABLE_STATS( | ||
− | ' | + | 'SCOTT' -- 테이블 오너 |
− | , ' | + | , 'TB_EMP' -- 테이블명 |
, NULL -- 파티션명 | , NULL -- 파티션명 | ||
, 'TB_MGR_STATS' -- 통계테이블명 | , 'TB_MGR_STATS' -- 통계테이블명 | ||
, 'TB_DICT_20161101' -- statid 옵션 식별자(버전) | , 'TB_DICT_20161101' -- statid 옵션 식별자(버전) | ||
, TRUE -- cascade 인덱스포함하여 | , TRUE -- cascade 인덱스포함하여 | ||
− | , 'CYKIM' -- | + | , 'CYKIM' -- 통계테이블 소유자 |
) ; | ) ; | ||
</source> | </source> | ||
158번째 줄: | 229번째 줄: | ||
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( | EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( | ||
'SCOTT' -- schema name | 'SCOTT' -- schema name | ||
− | , 'TB_MGR_STATS' -- stat table | + | ,'TB_MGR_STATS' -- stat table |
− | , ' | + | ,'CYKIM_SCHEMA_20160101' -- stat_id(추후 복구용으로 사용할 ID) |
− | , 'CYKIM' -- stat table owner | + | ,'CYKIM' -- stat table owner |
); | ); | ||
</source> | </source> | ||
− | === 통계정보 | + | === 통계정보 백업 테이블 통계 수집 === |
+ | <source lang=sql> | ||
+ | EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE); | ||
+ | </source> | ||
+ | === 임시 table을 export, 그리고 target db로 import. === | ||
+ | <source lang=sql> | ||
+ | -- 만약 동일 DB라면 이 과정은 생략 가능합니다. | ||
+ | $exp scott/tiger tables=STATS file=expstat.dmp | ||
+ | $imp scott/tiger file=expstat.dmp full=y log=implog.txt | ||
+ | |||
+ | </source> | ||
+ | |||
+ | === 임시 Table로 부터 통계치를 원하는 table에 넣는다. === | ||
==== 테이블 통계정보 입력 ==== | ==== 테이블 통계정보 입력 ==== | ||
− | |||
<source lang=sql> | <source lang=sql> | ||
EXEC DBMS_STATS.IMPORT_TABLE_STATS( | EXEC DBMS_STATS.IMPORT_TABLE_STATS( | ||
− | + | 'SCOTT' -- 테이블 오너 | |
− | + | , 'TB_EMP' -- 테이블명 | |
− | + | , NULL -- 파티션명 | |
− | + | , 'TB_MGR_STATS' -- 통계테이블명 | |
+ | , 'TB_DICT_20161101' -- statid 옵션 식별자(버전) | ||
+ | , TRUE -- cascade 인덱스포함하여 | ||
+ | , 'CYKIM' -- 통계테이블 소유자 | ||
); | ); | ||
+ | </source> | ||
− | |||
==== 스키마 정보 입력 ==== | ==== 스키마 정보 입력 ==== | ||
<source lang=sql> | <source lang=sql> | ||
+ | DBMS_STATS.IMPORT_TABLE_STATS( | ||
+ | ownname, tabname, partname | ||
+ | , stattab, statid, cascade, statown); | ||
+ | |||
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS ( | EXEC DBMS_STATS.IMPORT_SCHEMA_STATS ( | ||
'CYKIM' -- schema name | 'CYKIM' -- schema name | ||
185번째 줄: | 274번째 줄: | ||
, 'CYKIM' -- stat table owner | , 'CYKIM' -- stat table owner | ||
); | ); | ||
− | </source> | + | </source> |
− | + | == 통계정보 테이블 삭제 시 == | |
<source lang=sql> | <source lang=sql> | ||
− | EXEC DBMS_STATS. | + | EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB'); |
− | |||
− | , ' | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ); | ||
</source> | </source> | ||
− | == | + | == ORA-38029: object statistics are locked 에러 발생시 == |
− | + | <source lang=sql> | |
− | + | exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명'); | |
+ | </source> | ||
− | === | + | == 테이블/인덱스/컬럼 통계정보조회== |
+ | === 테이블 통계정보 조회 === | ||
+ | <source lang=sql>p | ||
+ | SELECT OWNER | ||
+ | , TABLE_NAME | ||
+ | , BLOCKS -- 해당 데이터가 저장되어 있는 블록 수. | ||
+ | , NUM_ROWS -- 데이터 행 수. | ||
+ | , AVG_ROW_LEN -- 하나의 행의 평균 길이. | ||
+ | , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' ) | ||
+ | FROM USER_TABLES | ||
+ | -- [DBA_TABLES] | ||
+ | -- [WHERE TABLE_NAME = '테이블명'] | ||
+ | </source> | ||
+ | * DBA_TAB_STATISTICS | ||
<source lang=sql> | <source lang=sql> | ||
− | + | SELECT * | |
− | + | FROM DBA_TAB_STATISTICS | |
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> | ||
+ | === 컬럼 통계정보 조회 === | ||
+ | <source lang=sql> | ||
+ | SELECT TABLE_NAME | ||
+ | , COLUMN_NAME -- 컬럼명 | ||
+ | , LOW_VALUE -- 해당 컬럼에 저장되어 있는 최소값. | ||
+ | , HIGH_VALUE -- 해당 컬럼에 저장되어 있는 최대값. | ||
+ | , NUM_DISTINCT -- 유일한 값의 수. (히스토그램 기준) | ||
+ | , NUM_BUCKETS -- 히스토그램 | ||
+ | -- , HISTOGRAM | ||
+ | FROM DBA_TAB_COLUMNS | ||
+ | [WHERE TABLE_NAME = '테이블명'] | ||
+ | </source> | ||
+ | * DBA_TAB_COL_STATISTICS | ||
+ | <source lang=sql> | ||
+ | SELECT * | ||
+ | FROM DBA_TAB_COL_STATISTICS | ||
+ | </source> | ||
− | == | + | === 인덱스 통계정보 조회 === |
<source lang=sql> | <source lang=sql> | ||
− | SELECT | + | SELECT INDEX_NAME |
− | , | + | , BLEVEL -- 인덱스의 깊미(Depth) |
− | FROM | + | , LEAF_BLOCKS -- 리프 블록의 수. |
− | + | , DISTINCT_KEYS -- 인덱스 컬럼의 유일한 값의 수. | |
+ | , CLUSTERING_FACTOR -- 조건을 만족하는 데이터를 검색할 때 인덱스 키 값이 각 블록에 얼마나 잘 분산 저장되어 있는지를 나타내는 정도. | ||
+ | , NUM_ROWS -- 전체 행수. | ||
+ | , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' ) | ||
+ | FROM USER_INDEXES | ||
+ | -- [DBA_INDEXES] | ||
</source> | </source> | ||
− | + | * DBA_IND_STATISTICS | |
<source lang=sql> | <source lang=sql> | ||
− | SELECT | + | SELECT * |
− | FROM | + | FROM DBA_IND_STATISTICS |
− | |||
</source> | </source> | ||
251번째 줄: | 362번째 줄: | ||
</source> | </source> | ||
− | == DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성 == | + | ==== DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성 ==== |
<source lang=sql> | <source lang=sql> | ||
SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' | SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' |
2024년 4월 17일 (수) 12:09 기준 최신판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 통계정보 수집/추출/입력[편집]
1.1 통계정보수집[편집]
1.1.1 테이블,인덱스포함 통계정보 갱신[편집]
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', method_opt => 'for all indexed columns',cascade => true);
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT get_param('OPTIONS'));
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ROWNER',
TABNAME => 'TB_XXXX',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 120');
END;
- 디폴트 값은 NULL 이고, CREATE TABLE, ALTER TABLE 시 설정된 DEGREE 값에 의해 정해진다.
- AUTO_DEGREE 값은 병렬처리 정도를 자동으로 결정한다.
- 이것은 1 or DEFAULT_DEGREE [ Object Size 와 CPU Count 에 의해 결정 ]
Parameter | Description |
---|---|
Ownname | 분석할 테이블 소유자 |
tabname | 테이블 이름 |
partname | 파티션 이름, 지정 하지 않으면 NULL 값 |
Estimate_percent | 분석할 Row의 Percentage, NULL 이면 Compute(Row 전체)
유효값은 1/1000000 ~ 100 디폴트로, DBMS_STATS.AUTO_SAMPLE_SIZE 에 의해서 최적의 값을 결정 |
Block_sample | random block sampling or random row sampling 결정
random block sampling 이 좀더 효과적이다. 데이터의 블록 별 분포도가 안좋을 시에는 부적절한 정보 생성 디폴트 값이 False로, random row sampling 을 수행한다. |
Method_opt | Histogram 생성시 사용하는 옵션
FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ] FOR COLUMN [ size clause ] column | attribute [size clause] [, column|attribute [ size clause ]…]
n Integer : Histogram Bucket 수, Max 는 1,254 n REPEAT : 이미 Histogram 이 있는 칼럼에 대해서만 생성 n AUTO : 데이터 분산도와 칼럼 부하 정도에 따라서 생성 결정 n SKEWONLY : 데이터 분산도에 따라서 생성 결정 디폴트 값은 FOR ALL COLUMNS SIZE AUTO
이 경우 EX) method_opt => FOR ALL COLUMNS SIZE 1
|
granularity | Parition table 에 대한 분석시 사용
|
cascade | 대상 테이블의 인덱스에 대한 통계수집 여부
인덱스 통계정보는 병렬처리가 불가능하다. TRUE – 대상 테이블에 관련된 index 에 대해서 통계정보 수집 |
stattab | 통계수집을 통한 기존 통계정보 Update 전에,
기존에 존재하는 통계정보를 저장할 User Stat Table 을 지정 |
statid | Stattab 와 연관된 구분자 값 |
statown | Stattab 에 지정한 User Stat Table 의 소유자가 다를 경우 지정 |
no_invalidate |
Default 로 DBMS_STATS.AUTO_INVALIDATE 값이고, 의미는 DBMS 가 의존적 Cursor 를 언제 invalidate 할지 자동으로 결정 이때 작용하는 Parameter는 _OPTIMIZER_INVALIDATION_PERIOD 이고, Default 롤 18000 초(5시간) 이다. 즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에 해당 Cursor가 실행될 때 invalidation이 발생한다. 이것을 Auto Invalidation이라고 부른다. 일정 시간에 걸쳐 랜덤하게 Cursor를 Invalidation함으로써 특정 시점에 Hard Parse가 한꺼번에 몰리는 현상을 피할 수 있다. _OPTIMIZER_INVALIDATION_PERIOD 파라미터로 시간 조절 가능 |
force | Lock 걸린 Table 에 대해서도 강제로 통계정보 생성 |
android 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다.
1.1.2 테이블 DBMS_STATS.GATHER_TABLE_STATS[편집]
- 예시 : EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname=>'CYKIM'
, stattab=>'TB_MGR_STATS'
, tabname=>'TB_DICT'
, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 74'
, cascade=>TRUE);
보다자세히
exec DBMS_STATS.GATHER_TABLE_STATS (
'CYKIM'
,'TB_DICT5'
, estimate_percent => 100
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, degree=> 10
, granularity => 'ALL'
, cascade => TRUE
, no_invalidate => FALSE);
1.1.3 파티션테이블[편집]
exec DBMS_STATS.GATHER_TABLE_STATS (
'CYKIM'
, 'TB_DICT5'
, partname => 'P201102' -- 파티션명
, estimate_percent => 5
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, degree=> 10
, granularity => 'ALL'
, cascade => TRUE
, no_invalidate => FALSE);
1.1.4 스키마 DBMS_STATS.GATHER_SCHEMA_STATS[편집]
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'CYKIM'
, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
, cascade=>TRUE);
1.1.5 인덱스 DBMS_STATS.GATHER_INDEX_STATS[편집]
EXEC DBMS_STATS.GATHER_INDEX_STATS(
'CYKIM'
,'IX_DICT_01');
1.1.6 파티션인덱스[편집]
EXEC DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'CYKIM'
, indname => 'IX_DICT_01'
, partname => 'P20110222');
1.1.7 데이터베이스 DBMS_STATS.GATHER_DATABASE_ STATS[편집]
DBMS_STATS.GATHER_DATABASE_ STATS
1.1.8 시스템통계정보 DBMS_STATS.GATHER_SYSTEM_STATS[편집]
DBMS_STATS.GATHER_SYSTEM_STATS
1.1.9 보다 상세한 통계정보[편집]
DBMS_STATS.GENERATE_STATS
1.2 테이블/인덱스 통계정보를 옮기는 방법.[편집]
- DBMS_STATS.GATHER_TABLE_STATS 패키지 이용
android :1) 통계정보를 임시로 저장할 Table생성.
- 2) 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.
- 3) 통계정보 백업 테이블 통계 수집
- 4) 임시 table을 export, 그리고 target db로 import.
- 5) 임시 Table로 부터 통계치를 대상 테이블에 입력.
- 6) 통계정보 테이블 삭제
보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우.
- 통계정보 추출
- 테이블 통계 백업 dbms_stats.export_table_stats
- 스키마 통계 백업 dbms_stats.export_schema_stats
1.2.1 통계정보를 임시로 저장할 Table생성.[편집]
EXEC DBMS_STATS.CREATE_STAT_TABLE (
'스키마명'
,'TB_MGR_STATS'
,'테이블스페이스명-생략가능'
);
1.2.2 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.[편집]
1.2.2.1 테이블 정보 EXPORT[편집]
- dbms_stats.export_table_stats ('table_owner', 'table name', null, 'stat table','stat_id',true,'stat table owner');
EXEC DBMS_STATS.EXPORT_TABLE_STATS(
'SCOTT' -- 테이블 오너
, 'TB_EMP' -- 테이블명
, NULL -- 파티션명
, 'TB_MGR_STATS' -- 통계테이블명
, 'TB_DICT_20161101' -- statid 옵션 식별자(버전)
, TRUE -- cascade 인덱스포함하여
, 'CYKIM' -- 통계테이블 소유자
) ;
1.2.2.2 스키마 정보 EXPORT[편집]
- dbms_stats.export_schema_stats ('schema name', 'stat table','stat_id','stat table owner');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (
'SCOTT' -- schema name
,'TB_MGR_STATS' -- stat table
,'CYKIM_SCHEMA_20160101' -- stat_id(추후 복구용으로 사용할 ID)
,'CYKIM' -- stat table owner
);
1.2.3 통계정보 백업 테이블 통계 수집[편집]
EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);
1.2.4 임시 table을 export, 그리고 target db로 import.[편집]
-- 만약 동일 DB라면 이 과정은 생략 가능합니다.
$exp scott/tiger tables=STATS file=expstat.dmp
$imp scott/tiger file=expstat.dmp full=y log=implog.txt
1.2.5 임시 Table로 부터 통계치를 원하는 table에 넣는다.[편집]
1.2.5.1 테이블 통계정보 입력[편집]
EXEC DBMS_STATS.IMPORT_TABLE_STATS(
'SCOTT' -- 테이블 오너
, 'TB_EMP' -- 테이블명
, NULL -- 파티션명
, 'TB_MGR_STATS' -- 통계테이블명
, 'TB_DICT_20161101' -- statid 옵션 식별자(버전)
, TRUE -- cascade 인덱스포함하여
, 'CYKIM' -- 통계테이블 소유자
);
1.2.5.2 스키마 정보 입력[편집]
DBMS_STATS.IMPORT_TABLE_STATS(
ownname, tabname, partname
, stattab, statid, cascade, statown);
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (
'CYKIM' -- schema name
, 'TB_MGR_STATS' -- stat table
, 'CYKIM_TB_MGR_STATS' -- stat_id
, 'CYKIM' -- stat table owner
);
1.3 통계정보 테이블 삭제 시[편집]
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');
1.4 ORA-38029: object statistics are locked 에러 발생시[편집]
exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명');
1.5 테이블/인덱스/컬럼 통계정보조회[편집]
1.5.1 테이블 통계정보 조회[편집]
p
SELECT OWNER
, TABLE_NAME
, BLOCKS -- 해당 데이터가 저장되어 있는 블록 수.
, NUM_ROWS -- 데이터 행 수.
, AVG_ROW_LEN -- 하나의 행의 평균 길이.
, TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_TABLES
-- [DBA_TABLES]
-- [WHERE TABLE_NAME = '테이블명']
- DBA_TAB_STATISTICS
SELECT *
FROM DBA_TAB_STATISTICS
1.5.2 컬럼 통계정보 조회[편집]
SELECT TABLE_NAME
, COLUMN_NAME -- 컬럼명
, LOW_VALUE -- 해당 컬럼에 저장되어 있는 최소값.
, HIGH_VALUE -- 해당 컬럼에 저장되어 있는 최대값.
, NUM_DISTINCT -- 유일한 값의 수. (히스토그램 기준)
, NUM_BUCKETS -- 히스토그램
-- , HISTOGRAM
FROM DBA_TAB_COLUMNS
[WHERE TABLE_NAME = '테이블명']
- DBA_TAB_COL_STATISTICS
SELECT *
FROM DBA_TAB_COL_STATISTICS
1.5.3 인덱스 통계정보 조회[편집]
SELECT INDEX_NAME
, BLEVEL -- 인덱스의 깊미(Depth)
, LEAF_BLOCKS -- 리프 블록의 수.
, DISTINCT_KEYS -- 인덱스 컬럼의 유일한 값의 수.
, CLUSTERING_FACTOR -- 조건을 만족하는 데이터를 검색할 때 인덱스 키 값이 각 블록에 얼마나 잘 분산 저장되어 있는지를 나타내는 정도.
, NUM_ROWS -- 전체 행수.
, TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_INDEXES
-- [DBA_INDEXES]
- DBA_IND_STATISTICS
SELECT *
FROM DBA_IND_STATISTICS
1.6 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신[편집]
ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;
1.6.1 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)[편집]
SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;'
FROM DBA_TABLES
WHERE OWNER = '스키마명';
1.6.2 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신[편집]
ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS
FROM INDEX_STATS;
1.6.2.1 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성[편집]
SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;'
FROM DBA_INDEXES WHERE OWNER = '스키마명';