오라클 Table/Index Analyze 통계 확인 및 실행방법
출처 : http://jangpd007.tistory.com/248
DBMS_STATS.GATHER_TABLE_STATS
참고 : http://blog.naver.com/PostView.nhn?blogId=tyboss&logNo=70153024309
http://egloos.zum.com/bosoa/v/1402860
http://m.blog.daum.net/ryaniya/277
ANALYZE 란?
ANALYZE는 인덱스, 테이블, 클러스터의 통계정보를 생성 한다.
ANALYZE가 생성한 통계정보들은 비용기준(Cost-based)의 옵티마이저가 가장 효율적인 실행계획을 수립하기 위해 최소비용을 계산할 때 사용 된다.
각 오브젝트의 구조를 확인하는 것과 체인(Chain) 생성 여부를 확인할 수 있으므로 시스템의 저장공간 관리를 도와준다.
ANALYZE는 다음과 같은 통계정보를 생성하여 데이터 사전에 저장 한다.
- - 테이블 : 총 로우의수, 총 블럭의 수, 비어있는 블럭에 쓰여질 수 있는 빈 공간의 평군, 체인이 발생된 로우의 수, 로우의 평균 길이
- - 인덱스 : 인덱스의 깊이(Depth), Leaf block의 개수, Distinct Key의 수, Leaf Blocks/Key의 평균, Data blocks/key의 평균, Clustering Factor, 가장 큰 key 값, 가장 작은 key 값
- - 컬럼 : Distinct한 값의 수, 히스토그램 정보
- - 클러스터 : Cluster Key당 길이의 평균
문법
object-clause : TABLE, INDEX, CLUSTER중에서 해당하는 오브젝트를 기술하고 처리할 오브젝트 명을 기술 한다.
operation : operation 옵션에는 다음 3가지중 한가지 기능을 선택할 수 있다.
- - COMPUTE : 각각의 값들을 정확하게 계산 한다. 가장 정확한 통계를 얻을 수 있지만 처리 속도가 가장 느리다.
(테이블에 저장되어 있는 모든 행을 대상으로 통계정보를 수집한다.) - - ESTIMATE : 자료사전의 값과 데이터 견본을 가지고 검사해서 통계를 예상 한다. COMPUTE보다 덜 정확 하지만 처리속도가 훨씬 빠르다
(오라클 서버의 자동화 알고리즘에 의해 데이터를 추출하여 통계정보를 수집한다.) - - DELETE : 테이블의 모든 통계 정보를 삭제 한다
(수집되어 있는 통계정보를 삭제한다.)
정보수집
주기적인 ANALYZE 작업을 수행 시켜 주어야 한다.
테이블을 재생성 하거나, 새로 클러스터링을 한 경우, 인덱스를 추가하거나 재생성한 경우, 다량의 데이터를 SQL이나 배치 애플리케이션을 통해 작업한 경우 ANALYZE를 수행 시켜 주는 것이 좋다.
사용자는 USER_TABLES, USER_COLUMNS, USER_INDEXS, USER_CLUSTER 등의 자료사전 뷰를 통해 정보를 확인할 수 있다
테이블을 ANALYZE 시킨다면 거기에 따르는 인덱스들도 같이 실시하는 것이 좋다.
오 라클에서는 20,000건 이하의 행수를 가진 데이터에 대해서는 COMPUTE STATISTICS절의 사용을 권장하며 20,000건 이상되는 경우에는 ESTIMATE STATISTICS절의 사용을 권장하고 있다. 또한, 통계정보의 분석은비 일과시간에 수행하는게 원칙이며 일과 시간에 수행해야 하는 경우라면 ESTIMATE STATISTICS절의 사용을 권장한다.
테이블 정보수집 예제
SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;
새로운 정보를 구하기 전에 기존 정보를 삭제
SQL> ANALYZE TABLE emp DELETE STATISTICS;
특정 column에 대한 data 분포 수집
SQL> ANALYZE TABLE emp COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
통계 정보의 확인 예제
SQL> SELECT NUM_ROWS
, BLOCKS
, EMPTY_BLOCKS
, AVG_SPACE
, CHAIN_CNT
, AVG_ROW_LEN
, SAMPLE_SIZE
, LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'CMS_CATEGORY';
SQL> SELECT NUM_DISTINCT
, DENSITY
, LOW_VALUE
, HIGH_VALUE
, LAST_ANALYZED
, COLUMN_NAME
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'CMS_CATEGORY';
1-1. 테이블 통계정보
SELECT TABLE_NAME
, BLOCKS -- 해당 데이터가 저장되어 있는 블록 수.
, NUM_ROWS -- 데이터 행 수.
, AVG_ROW_LEN -- 하나의 행의 평균 길이.
, TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_TABLES
[WHERE TABLE_NAME = '테이블명']
1-2. 테이블 통계정보
SELECT TABLE_NAME
, COLUMN_NAME -- 컬럼명
, LOW_VALUE -- 해당 컬럼에 저장되어 있는 최소값.
, HIGH_VALUE -- 해당 컬럼에 저장되어 있는 최대값.
, NUM_DISTINCT -- 유일한 값의 수. (히스토그램 기준)
FROM USER_TAB_COLUMNS
[WHERE TABLE_NAME = '테이블명']
2. 인덱스 통계정보SELECT INDEX_NAME
, BLEVEL -- 인덱스의 깊미(Depth)
, LEAF_BLOCKS -- 리프 블록의 수.
, DISTINCT_KEYS -- 인덱스 컬럼의 유일한 값의 수.
, CLUSTERING_FACTOR -- 조건을 만족하는 데이터를 검색할 때 인덱스 키 값이 각 블록에 얼마나 잘 분산 저장되어 있는지를 나타내는 정도.
, NUM_ROWS -- 전체 행수.
, TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_INDEXESex) SELECT TABLE_NAME
, NUM_ROWS
, TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_TABLES;TABLE_NAME NUM_ROWS TO_CHAR(
------------------------------ ---------- --------
ABS_TYPE 38 20040101ANNIVERS 183 20040101
APPRFLDRHISTORY 570 20040101
APPRFOLDER 16885 20040101
APPRFOLDER_ERR 3670 20040101
APPRFORM 359 20040101
.
.
.
USR_INFO_ADMIN 0 20040101
VAR_DEPT_INFO 0 20040101
VIEW_TYPE 0 20040101
WASTEBOX 0 20040101
ZIP_CODE 44195 20040101252 rows selected.※ 참고 : desc user_tables 에서 보통 num_rows 로도 확인 가능[특정 Table만 Analyze 하는 방법]ANALYZE TABLE DOCUMENT COMPUTE STATISTICSex) DOCUMENT Table 만 AnalyzeANALYZE INDEX XPKDOCBOX COMPUTE STATISTICSex) XPKDOCBOX Index 만 Analyze[전체 Table Analyze 하는 간단한 방법]1. vi analyze_all.sql
SELECT 'analyze table || table_name || estimate statistics;' FROM USER_TABLES2. @analyze_all.sql3. set heading off
set echo off
set feedback off
set pagesize 300 (line 이 300 미만일 경우)
spool analyze_table.sql
/
spool off4. vi analyze_table.sql
필요없는 Line 제거 및 정리5. @analyze_table.sql
[전체 Index Analyze 하는 간단한 방법]1. vi analyze_all.sql
SELECT 'analyze index || index_name || estimate statistics;' FROM USER_INDEXES2. @analyze_all.sql3. set heading off
set echo off
set feedback off
set pagesize 300 (line 이 300 미만일 경우)
spool analyze_index.sql
/
spool off4. vi analyze_index.sql
필요없는 Line 제거 및 정리5. @analyze_index.sql
===========================================================================================================================
===========================================================================================================================
출처 : http://blog.naver.com/vxxv122?Redirect=Log&logNo=130128144052
정의
→ 비용기반 옵티마이저에서 통계정보를 모아주기 위한 튜닝 도구.
테이블과 인덱스, 클러스터, 컬럼에 대한 통계정보를 수집.
권한
→ Analyze Any 시스템 권한
통계 수집
1 . 테이블
→ Analyze Table 테이블명 Compute Statistics;
* Row수, 사용된 Block수, 사용안된 Block수, 사용가능한 평균공간, 변경된 Row수, 컬럼당 distinct 값수
컬럼당 두번째로 가장 작은 값, 컬럼당 두번째로 가장 큰 값
(질문) 왜 두번째로 가장 작은값과 큰값을 ?
2 . 인덱스
→ Analyze Index 인덱스명 Estimate Statistics;
* 인덱스 레벨, 레벨 Block수, distinct Key수, Key당 Leaf와 Data Block수 평균, Clustering Factor, 최소 Key 값
최대 Key값
3 . 클러스터
→ Analyze Cluster 클러스터명 Delete Statistics;
* Cluster Key당 길이의 평균
4 . 컬럼
→ Analyze Table 테이블명 Compute Statistics For Table;
→ Analyze Table 테이블명 Compute Statistics For Columns 컬럼명 Size 75;
→ Analyze Table 테이블명 Compute Statistics For All Indexed Columns Size 75;
* 디볼트 버켓 수는 75개
Distinct 한 값의 수, 히스토그램 정보
유효성 검사
→ Analyze Table 테이블명 Validate Structure [Cascade];
* 검사하려는 테이블과 관련된 모든 테이블을 검사하려면 Cascade 옵션 사용.
옵션
1 . Compute
→ 테이블에 저장되어 있는 모든 행을 대상으로 통계정보 수집
2 . Estimate
→ 오라클 서버의 자동화 알고리즘에 의해 데이터를 추출하여 통계정보를 수집
3 . Delete
→ 수집되어 있는 통계정보를 삭제
* 20,000건 이하의 Row는 Compute 권장, 이상은 Estimate 권장.
확인
1 . User_Tables
2 . User_Tab_Columns
3 . User_Indexes, Index_Stats, Index_histogram
4 . User_Cluster
5 . DBA_Analyze_Objects
===========================================================================================================================
===========================================================================================================================
출처 : http://www.oracleclub.com/article/23928
1. 개요
- TABLE, COLUMN, 그리고 INDEX 에 대한 통계 정보를 수집 하게 하는 PROCEDURE
2. SYNTAX
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')),
FORCE BOOLEAN DEFAULT FALSE);
3. PARAMETER 설명
- DBMS_STATS.SET_PARAM 에 의해서 디폴트 파라미터 설정 변경이 가능하다.
- 수동 통계정보 생성 시에 저정을 하지 않았을 때 적용되는 DEFAULT 값에 영향을 미치는 값
=> CASCADE
=> DEGREE
=> ESTIMATE_PERCENT
=> METHOD_OPT
=> NO_INVALIDATE
=> GRANULARITY
- 자동 통계정보(GATHER_STATS_JOB) 시에만 영향을 미친다.
=> AUTOSTATS_TARGET [ AUTO : ORACLE이 자동으로 대상 OBJECT 결정,
ALL : 대상 시스템의 모든 OBJECTS
ORACLE : SYS/SYSTEM OBJECT 만 ]
1) DEFAULT 값 확인
SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL ;
DBMS_STATS.GET_PARAM('METHOD_OPT')
----------------------------------
FOR ALL COLUMNS SIZE AUTO
2) DEFAULT 값 변경
EXECUTE DBMS_STATS.SET_PARAM('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1') ;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
3) 변경된 DEFAULT 값 확인
SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL ;
DBMS_STATS.GET_PARAM('METHOD_OPT')
----------------------------------
FOR ALL COLUMNS SIZE 1
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 생성시 사용하는 옵션 |
| 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_VALIDATE | - 의존적인 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가 한꺼번에 몰리는 현상을 피할 수 있다. |
| FORCE | - Lock 걸린 Table 에 대해서도 강제로 통계정보 생성 |
4. Test
1) Mission
CASCADE => TRUE
E 인덱스에 대한 통계정보도수집하라.
CASCADE => FALSE
E 인덱스에 대한 통계정보도수집하라.
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1'
E 칼럼(HIGH AND LOW COLUMN VALUE)에 대한 통계정보도 수집하라.
METHOD_OPT =>'FOR COLUMNS'
E컬럼에 대한통계정보를 수집하지 마라
2) Test
2-1) 일반 테이블
SHOW USER
USER IS"SYS"
① SCOTT의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고, 테이블, 칼럼(HIGHAND LOW COLUMN VALUE),
연관 인덱스의 통계정보를 생성한다.( COMPUTE STATISTICS )
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
② SCOTT의 BIG_TABLE의 15% ROW를 가지고, 테이블, 칼럼, 연관인덱스의 통계정보를 생성한다.( SAMPLE 15 PERCENT )
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => TRUE,
ESTIMATE_PERCENT => 15);
③ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블의 통계정보를 수집하라.
인덱스와 칼럼에 대한 통계정보는 제외
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => FALSE,
METHOD_OPT => 'FOR COLUMNS');
④ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블과인덱스에 대한 통계정보를 수집하라.
칼럼에 대한 통계정보는 제외
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS');
⑤ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블과 컬럼(NO HISTOGRAM),
그리고 인덱스에 대한 통계정보를 수집하라.
잠시 후에 인덱스 칼럼들의 HISTOGRAM 통계정보를 수집하라.
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => TRUE);
잠시 후에..
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL INDEXED COLUMNSSIZE 1');
⑥ SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고, 테이블과 인덱스칼럼(ONLY HIGH AND LOW )에
대한 통계정보를 수집하라. 인덱스에 대한 통계정보는수집하지 마라.
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BIG_TABLE',
CASCADE => FALSE,
METHOD_OPT => 'FOR ALL INDEXED COLUMNSSIZE 1');
2-2) PARTITIONTABLE 의 경우
① 추가적으로 GRANULARITY 정보를 'ALL', 'AUTO', 'PARITION', 'GLOBAL AND PARTITION', 'GLOBAL', 'SUBPARTITION'을
통해서 통계수집 대상 TABLE SEGMENT를 선정 가능하다.
② 참고
- LOCK VS DBMS_STATS.GATHER_TABLE_STATS
: DML 이 LOCK 이 발생 하여도 GATHER_TABLE_STATS 는 정상적으로 진행된다.
BEGIN
FOR I IN 1001 .. 5000 LOOP
INSERT INTO CHECK_LOCK VALUES ( I , I , 'LOCK');
END LOOP ;
END ;
/
- @CHECK_USER_LOCK.SQL
ENTER VALUE FOR USER_NAME: SCOTT10
OLD 46: AND B.USERNAME =UPPER('&USER_NAME')
NEW 46: AND B.USERNAME =UPPER('SCOTT10')
USERNAME SID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---------- ---- --------------- ----------- ---------- -------- --------
SCOTT10 151 DML ROW-X (SX) NONE 51782 0
SCOTT10 151 TRANSACTION EXCLUSIVE NONE 131077 307
- EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCOTT10',TABNAME => 'CHECK_LOCK');
==> DML LOCK 과는 무관하게 진행 된다.
========================================================================
ANALYZE 와 DBMS_STATS 의 수집정보 차이
참고 : http://jedah.tistory.com/entry/ANALYZE-%EC%99%80-DBMSSTATS-%EC%9D%98-%EC%88%98%EC%A7%91%EC%A0%95%EB%B3%B4-%EC%B0%A8%EC%9D%B4
=================================================================================
index rebuild 작업
출처 : http://aozjffl.tistory.com/285
질문 :
index에 관해서 공부를 하다가 궁금한 점이 있어 이렇게 질문을 등록합니다.
테이블에 데이터가 자주 들락날락해서 delete된 데이터의 index가 그대로 남아있어, 필요이상으로 많은 공간을 차지할 때 한다는 것을 알고 있습니다.
그런데, 이렇게 필요이상으로 index가 공간을 많이 차지한다는 것을 어떻게 알 수 있을까요?
답변1 :
아래에 있는 두 방법의 결과로 판단합니다.
첫번째 방법 :
우선 분석하고자 하는 인덱스에 대한 통계정보를 생성합니다.
-- 수백만건 이상의 row를 지닌 테이블에 대한 인덱스인 경우 compute statistics 대신에 estimate 옵션을 사용할 것.
아래 쿼리 결과의 blevel값이 4가 넘으면 index rebuild를 하는 것이 좋습니다.
이 blevel(Branch level)이 의미하는 것은 오라클이 index access를 할 때 몇 단계를 거쳐서 블럭의 위치를 찾아가는 가와 관계가 있습니다.
from user_indexes
where index_name = '인덱스명';
두번째 방법 :
아래의 쿼리를 이용하여 index_stats테이블에 추가적인 인덱스 정보를 생성합니다.
아래 쿼리 결과의 pct_deleted가 20%이상으로 나타나면 rebuild 대상입니다.
그리고 distinctiveness는 인덱스가 만들어진 컬럼의 값이 얼마나 자주 반복되는지를 보여주는 값입니다.
예를 들면, 만일 1만건의 row와 9000건의 서로 다른 값을 가진 테이블이 있을 때 distinctiveness값은 다음과 같이 계산됩니다.
(10000-9000)*100/10000 = 10 => 컬럼의 값이 잘 분산되어 있음
또 다른 예를 들면, 1만건의 row가 있지만 2가지 값으로만 중복되어 있다면 distintiveness값은
(10000-2)*100/10000 = 99.98 => rebuild 대상이 아니라 bitmap index로 만들 대상(99%이상이면 bitmap index 대상)
(lf_rows-distinct_keys)*100/decode(lf_rows,0,1,lf_rows) distinctiveness
from index_stats
where name = '&index_name';
답변2 :
답변2도 답변 1과 같은 내용이지만 스크립트를 사용함으로써, 한번에 여러개의 rebuild 대상 index를 찾을 수 있다는 장점이 있다.
결과 정보를 휘발성으로 저장하는데, 그 결과를 통해서 rebuild 대상을 체크할 수 있음.
주의할 점은 아래의 쿼리 실행시 Lock이 발생하므로, 업무시간 이외에 할 것을 강추함.
rebuild_indx.sql
REM
REM rebuild_indx.sql
REM
REM Copyright (c) Oracle Software, 1998 - 2000
REM
REM Author : Jurgen Schelfhout
REM
REM The sample program in this article is provided for educational
REM purposes only and is NOT supported by Oracle Support Services.
REM It has been tested internally, however, and works as documented.
REM We do not guarantee that it will work for you, so be sure to test
REM it in your environment before relying on it.
REM
REM This script will analyze all the indexes for a given schema
REM or for a subset of schema's. After this the dynamic view
REM index_stats is consulted to see if an index is a good
REM candidate for a rebuild or for a bitmap index.
REM
REM Database Version : 7.3.X and above.
REM
REM =============================================================
prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt - deleted entries represent 20% or more of the current entries
prompt - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt - when distinctiveness is more than 99%
prompt
spool &spoolfile
set serveroutput on
set verify off
declare
c_name INTEGER;
ignore INTEGER;
height index_stats.height%TYPE := 0;
lf_rows index_stats.lf_rows%TYPE := 0;
del_lf_rows index_stats.del_lf_rows%TYPE := 0;
distinct_keys index_stats.distinct_keys%TYPE := 0;
cursor c_indx is
select owner, table_name, index_name
from dba_indexes
where owner like upper('&schema')
and owner not in ('SYS','SYSTEM');
begin
dbms_output.enable (1000000);
dbms_output.put_line ('Owner Index Name % Deleted Entries Blevel Distinctiveness');
dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');
c_name := DBMS_SQL.OPEN_CURSOR;
for r_indx in c_indx loop
DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
ignore := DBMS_SQL.EXECUTE(c_name);
select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
into height, lf_rows, del_lf_rows, distinct_keys
from index_stats;
--
-- Index is considered as candidate for rebuild when :
-- - when deleted entries represent 20% or more of the current entries
-- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%
--
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
end if;
end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
spool off
set verify on
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
샘플 결과
Sample Output
-------------
Rebuild the index when:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Possible candidate for bitmap index:
- when distinctiveness is more than 99%
Owner Index Name % Deleted Entries Blevel Distinctivenes
--------------- ------------------------- ----------------- ------ -------------
SMS PLATMAP_I1 31.504 1 32.927
SMS PLATMAP_I2 27.682 1 29.399
SMS PLATMAP_I3 31.237 1 31.237
SMS PRODMAP_I4 8.765 5 99.9
SMS SB_CR_BOM_ITEMS_I1 34.496 2 97.356
SMS SB_CR_OS_VERSIONS_I1 51.942 1 68.063
SMS SB_CR_RELEASES_I1 34.584 1 18.426
SMS TAR_HEAD_I2 21.728 5 22.344
PL/SQL procedure successfully completed.
참조 : http://cafe.naver.com/prodba/16759
http://oracleclub.com/article/46676