다른 명령
통계정보 수집/추출/입력
통계정보수집
테이블,인덱스포함 통계정보 갱신
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', method_opt => 'for all indexed columns',cascade => true);
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 에 대해서도 강제로 통계정보 생성 |
테이블 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);
파티션테이블
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);
스키마 DBMS_STATS.GATHER_SCHEMA_STATS
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname => 'CYKIM' , method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO' , cascade=>TRUE);
인덱스 DBMS_STATS.GATHER_INDEX_STATS
EXEC DBMS_STATS.GATHER_INDEX_STATS( 'CYKIM' ,'IX_DICT_01');
파티션인덱스
EXEC DBMS_STATS.GATHER_INDEX_STATS( ownname => 'CYKIM' , indname => 'IX_DICT_01' , partname => 'P20110222');
데이터베이스 DBMS_STATS.GATHER_DATABASE_ STATS
DBMS_STATS.GATHER_DATABASE_ STATS
시스템통계정보 DBMS_STATS.GATHER_SYSTEM_STATS
DBMS_STATS.GATHER_SYSTEM_STATS
보다 상세한 통계정보
DBMS_STATS.GENERATE_STATS
테이블/인덱스 통계정보를 옮기는 방법.
- DBMS_STATS.GATHER_TABLE_STATS 패키지 이용
틀:고지 상자 보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우.
- 통계정보 추출
- 테이블 통계 백업 dbms_stats.export_table_stats
- 스키마 통계 백업 dbms_stats.export_schema_stats
통계정보를 임시로 저장할 Table생성.
EXEC DBMS_STATS.CREATE_STAT_TABLE ( '스키마명' ,'TB_MGR_STATS' ,'테이블스페이스명-생략가능' );
통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.
테이블 정보 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' -- 통계테이블 소유자 ) ;
스키마 정보 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 );
통계정보 백업 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);
임시 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
임시 Table로 부터 통계치를 원하는 table에 넣는다.
테이블 통계정보 입력
EXEC DBMS_STATS.IMPORT_TABLE_STATS( 'SCOTT' -- 테이블 오너 , 'TB_EMP' -- 테이블명 , NULL -- 파티션명 , 'TB_MGR_STATS' -- 통계테이블명 , 'TB_DICT_20161101' -- statid 옵션 식별자(버전) , TRUE -- cascade 인덱스포함하여 , 'CYKIM' -- 통계테이블 소유자 );
스키마 정보 입력
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 );
통계정보 테이블 삭제 시
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');
ORA-38029: object statistics are locked 에러 발생시
exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명');
테이블/인덱스/컬럼 통계정보조회
테이블 통계정보 조회
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
컬럼 통계정보 조회
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
인덱스 통계정보 조회
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
DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신
ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;
DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)
SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;' FROM DBA_TABLES WHERE OWNER = '스키마명';
DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신
ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS FROM INDEX_STATS;
DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성
SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' FROM DBA_INDEXES WHERE OWNER = '스키마명';