|
/*
-- 필요 권한
GRANT ANALYZE ANY TO [패키지소유계정];
GRANT SELECT ANY DICTIONARY TO [패키지소유계정];
GRANT SELECT ANY TABLE TO [패키지소유계정];
GRANT CREATE JOB TO [패키지소유계정];
-- 통계백업 테이블 생성
BEGIN
DBMS_STATS.CREATE_STAT_TABLE(USER, 'TB_STAT_EXP');
END;
/
*/
CREATE OR REPLACE PACKAGE PKG_STAT
/* -------------------------------------------------------------------------------------
* 용도 : 통계정보 수집
* 1.테이블 단위 통계수집
* 2.기타 : 통계수집 로그 기능, 통계정보 백업 기능, 통계 스케줄링 기능 보완 필요
---------------------------------------------------------------------------------------- */
IS
-- 테이블 통계 수집
PROCEDURE SET_TAB (PV_OWNER IN VARCHAR2
,PV_TABLE_NAME IN VARCHAR2);
-- 전체 통계 수집
PROCEDURE SET_ALL ;
-- 전체 통계 백업
PROCEDURE EXP_ALL ;
END;
/
-- 패키지 바디
CREATE OR REPLACE PACKAGE BODY PKG_STAT
IS
--******************************************************************************
-- 테이블 통계 수집
PROCEDURE SET_TAB (PV_OWNER IN VARCHAR2
,PV_TABLE_NAME IN VARCHAR2)
IS
/*
----------------------------------------------------------------------------
-- 용도 : 테이블 단위로 통계를 수집하는 프로시저
-- 특징 : 테이블의 ROW를 체크하여 0건이면 통계를 수집하지 않음
----------------------------------------------------------------------------
*/
-- 변수
V_OWNER VARCHAR2(30) ;
V_TABLE_NAME VARCHAR2(30) ;
-- 상수 : 옵션 고정
V_METHOD_OPT VARCHAR2(100) := 'FOR ALL COLUMNS SIZE 1'; -- 컬럼 히스토그램을 생성하지 않음
V_GRANULARITY VARCHAR2(100) := 'GLOBAL'; -- 테이블 통계정보만 생성, (서브)파티션 통계정보는 생성하지 않음
V_CASCADE BOOLEAN := TRUE; -- 인덱스 통계정보도 함께 생성
V_NO_INVALIDATE BOOLEAN := TRUE; -- 현재 커서를 유지함(커서를 무효화하지 않음)
-- 변수 : 테이블 사이즈에 따라 변경
V_BASE_SIZE NUMBER := 1024; -- 100%를 수집하는 기준크기 => 1Gb
V_TABLE_SIZE NUMBER ;
V_SAMPLE_PERCENT NUMBER ;
V_DOP NUMBER ;
V_WORKAREA_SIZE NUMBER ;
-- 기타
V_ROW_COUNT NUMBER ;
V_RUN_STR VARCHAR2(200);
BEGIN
-----------------------------------------------------------------------------
-- ARGUMENT 할당
-----------------------------------------------------------------------------
V_OWNER := PV_OWNER ;
V_TABLE_NAME := PV_TABLE_NAME;
IF V_OWNER IS NULL OR V_TABLE_NAME IS NULL THEN
RETURN;
END IF;
-----------------------------------------------------------------------------
-- ROW가 존재하는지 체크하여, 건수가 없으면 통계수집을 수행하지 않음
-----------------------------------------------------------------------------
V_RUN_STR := 'SELECT /*+ INDEX(A) */ COUNT(*) FROM ' || V_OWNER || '.' || V_TABLE_NAME || ' A WHERE ROWNUM <= 1';
EXECUTE IMMEDIATE V_RUN_STR INTO V_ROW_COUNT;
IF V_ROW_COUNT = 0 THEN
-- 기존 통계정보가 0건이면, 통계를 삭제한 후 종료
SELECT MAX(A.NUM_ROWS)
INTO V_ROW_COUNT
FROM DBA_TABLES A
WHERE A.OWNER = V_OWNER
AND A.TABLE_NAME = V_TABLE_NAME
AND A.NUM_ROWS = 0
;
IF V_ROW_COUNT = 0 THEN
DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => V_OWNER
,TABNAME => V_TABLE_NAME
,NO_INVALIDATE => V_NO_INVALIDATE
);
END IF;
RETURN;
END IF;
-----------------------------------------------------------------------------
-- 세그먼트 사이즈를 체크하여 샘플비율, DOP를 구함
-----------------------------------------------------------------------------
SELECT A.SAMPLE_PERCENT
,A.DOP
,CASE WHEN A.DOP >= 2
THEN LEAST(ROUND(A.PGA_TERGET/A.DOP/2), 2147483647)
ELSE LEAST(ROUND(A.PGA_TERGET/2 ), 2147483647)
END WORKAREA_SIZE
INTO V_SAMPLE_PERCENT
,V_DOP
,V_WORKAREA_SIZE
FROM (
SELECT CASE WHEN A.TABLE_SIZE <= A.BASE_SIZE
THEN CASE WHEN V.VERSION <= 10
THEN 100 --> 10g 이하
ELSE 0 --> 11g 이상
END
ELSE CASE WHEN V.VERSION <= 10
THEN GREATEST(ROUND((A.BASE_SIZE/A.TABLE_SIZE)*100, 1), 0.1)
ELSE GREATEST(ROUND((A.BASE_SIZE/A.TABLE_SIZE)*10 , 1), 0.1)
END
END SAMPLE_PERCENT --> Sample Rate
,CASE WHEN E.EDITION = 'EE'
THEN CASE
WHEN A.TABLE_SIZE <= A.BASE_SIZE*POWER(10,-1) THEN 1
WHEN A.TABLE_SIZE <= A.BASE_SIZE*POWER(10, 0) THEN 2
WHEN A.TABLE_SIZE <= A.BASE_SIZE*POWER(10, 1) THEN 4
WHEN A.TABLE_SIZE > A.BASE_SIZE*POWER(10, 1) THEN 8
END
ELSE NULL
END DOP --> Degree Of Parallelism
,P.PGA_TERGET
FROM (
SELECT ROUND(SUM(A.BYTES)/1024/1024)
TABLE_SIZE
,V_BASE_SIZE BASE_SIZE
FROM DBA_SEGMENTS A
WHERE A.OWNER = V_OWNER
AND A.SEGMENT_NAME = V_TABLE_NAME
AND A.SEGMENT_TYPE LIKE 'TABLE%'
) A
,(
-- VERSION
SELECT TO_NUMBER(SUBSTR(S.VERSION, 1, INSTR(S.VERSION, '.') - 1))
VERSION
FROM V$INSTANCE S
) V
,(
-- EDITION
SELECT NVL(MAX('EE'), 'SE') EDITION
FROM V$VERSION S
WHERE S.BANNER LIKE 'Oracle Database%Enterprise Edition%'
) E
,(
-- PGA target
SELECT MAX(S.VALUE) PGA_TERGET
FROM V$PGASTAT S
WHERE S.NAME = 'aggregate PGA target parameter'
) P
) A
;
-----------------------------------------------------------------------------
-- WORKAREA(PGA) 조정
-----------------------------------------------------------------------------
V_RUN_STR := 'ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL';
EXECUTE IMMEDIATE V_RUN_STR;
V_RUN_STR := 'ALTER SESSION SET SORT_AREA_SIZE = ' || TO_CHAR(V_WORKAREA_SIZE);
EXECUTE IMMEDIATE V_RUN_STR;
V_RUN_STR := 'ALTER SESSION SET HASH_AREA_SIZE = ' || TO_CHAR(V_WORKAREA_SIZE);
EXECUTE IMMEDIATE V_RUN_STR;
V_RUN_STR := 'ALTER SESSION SET SORT_AREA_RETAINED_SIZE = ' || TO_CHAR(V_WORKAREA_SIZE);
EXECUTE IMMEDIATE V_RUN_STR;
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- 통계수집
-----------------------------------------------------------------------------
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => V_OWNER
,TABNAME => V_TABLE_NAME
,ESTIMATE_PERCENT => V_SAMPLE_PERCENT
,METHOD_OPT => V_METHOD_OPT
,GRANULARITY => V_GRANULARITY
,DEGREE => V_DOP
,CASCADE => V_CASCADE
,NO_INVALIDATE => V_NO_INVALIDATE
);
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- WORKAREA(PGA) 원복
-----------------------------------------------------------------------------
V_RUN_STR := 'ALTER SESSION SET WORKAREA_SIZE_POLICY = AUTO';
EXECUTE IMMEDIATE V_RUN_STR;
-----------------------------------------------------------------------------
-- EXCEPTION 발생시 처리
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTRB(SQLERRM, 1, 200));
END;
--******************************************************************************
-- 전체 통계 수집
PROCEDURE SET_ALL
IS
BEGIN
-- 테이블 변경 정보 반영
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-- 테이블별 통계 수집
FOR X IN (
SELECT T.OWNER
,T.TABLE_NAME
,T.NUM_ROWS
,T.LAST_ANALYZED LAST_ANALYZE_TIME
,M.TIMESTAMP LAST_MODIFY_TIME
,M.INSERTS
,M.UPDATES
,M.DELETES
FROM DBA_USERS U -- USER
,DBA_TABLES T -- TABLE
,SYS.DBA_TAB_MODIFICATIONS M -- TABLE MODIFY
,DBA_TAB_STATISTICS L -- TABLE STAT LOCK
WHERE U.DEFAULT_TABLESPACE NOT IN ('SYSTEM', 'SYSAUX') --> 오라클 디폴트 계정 제외
AND (U.DEFAULT_TABLESPACE <> 'USERS' OR U.LOCK_DATE IS NULL) --> 오라클 디폴트 계정 제외
AND T.OWNER = U.USERNAME
AND M.TABLE_OWNER = T.OWNER
AND M.TABLE_NAME = T.TABLE_NAME
AND M.PARTITION_NAME IS NULL --> TABLE만 체크
AND (M.INSERTS > 0 OR M.UPDATES > 0 OR M.DELETES > 0) --> 데이터변경이 발생한 테이블만
AND L.OWNER = T.OWNER
AND L.TABLE_NAME = T.TABLE_NAME
AND L.PARTITION_NAME IS NULL --> TABLE만 체크
AND L.STATTYPE_LOCKED IS NULL --> TABLE 통계가 LOCK되지 않은 테이블만 체크
ORDER BY T.OWNER
,T.TABLE_NAME
)
LOOP
SET_TAB(X.OWNER, X.TABLE_NAME);
END LOOP;
-- 통계 백업
EXP_ALL;
END;
--******************************************************************************
-- 전체 통계 백업
PROCEDURE EXP_ALL
IS
BEGIN
-- 통계 백업
FOR X IN (
SELECT U.USERNAME
FROM DBA_USERS U
WHERE U.DEFAULT_TABLESPACE NOT IN ('SYSTEM', 'SYSAUX') --> 오라클 디폴트 계정 제외
AND (U.DEFAULT_TABLESPACE <> 'USERS' OR U.LOCK_DATE IS NULL) --> 오라클 디폴트 계정 제외
ORDER BY U.USERNAME
)
LOOP
DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => X.USERNAME
,STATID => 'SID_' || TO_CHAR(SYSDATE, 'YYYYMMDD')
,STATOWN => USER
,STATTAB => 'TB_STAT_EXP'
);
END LOOP;
-- 6개월 이상된 통계 정보 삭제
DELETE FROM TB_STAT_EXP A
WHERE A.STATID <= 'SID_' || TO_CHAR(ADD_MONTHS(SYSDATE, -6), 'YYYYMMDD');
COMMIT;
END;
--******************************************************************************
END;
/
-- 전체 테이블 통계생성
BEGIN
PKG_STAT.SET_ALL;
END;
/
-- 특정 테이블 통계생성
BEGIN
PKG_STAT.SET_TAB('SCOTT', 'EMP');
END;
/
-- 스케줄러 등록
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'STAT_JOB_WEEKLY'
,JOB_TYPE => 'PLSQL_BLOCK'
,JOB_ACTION => 'PKG_JOB.SET_ALL;'
,START_DATE => SYSDATE
,ENABLED => TRUE
,AUTO_DROP => FALSE
,REPEAT_INTERVAL => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=22'
);
END;
/
-- 스케줄러 제거
BEGIN
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'STAT_JOB_WEEKLY');
END;
/
-- 백업된 통계정보 조회
SELECT CASE WHEN A.TYPE = 'I'
THEN B.TABLE_OWNER
ELSE A.C5
END TABLE_OWNER
,CASE WHEN A.TYPE = 'I'
THEN B.TABLE_NAME
ELSE A.C1
END TABLE_NAME
-- ,A.C2 PARTITION_NAME
-- ,A.C3 SUBPARTITION_NAME
,CASE A.TYPE
WHEN 'T' THEN 'Table'
WHEN 'I' THEN 'Index'
WHEN 'C' THEN 'Column'
END OBJECT_TYPE
,CASE WHEN A.TYPE = 'I'
THEN A.C5
END INDEX_OWNER
,CASE WHEN A.TYPE = 'I'
THEN A.C1
END INDEX_NAME
,A.C4 COLUMN_NAME
,CASE A.TYPE
WHEN 'T' THEN A.N1
WHEN 'I' THEN A.N1
END NUM_ROWS
,CASE A.TYPE
WHEN 'T' THEN A.N2
WHEN 'I' THEN A.N2
END BLOCKS
,CASE A.TYPE
WHEN 'T' THEN A.N3
END AVG_ROW_LEN
,CASE A.TYPE
WHEN 'T' THEN A.N4
WHEN 'I' THEN A.N8
END SAMPLE_SIZE
,CASE A.TYPE
WHEN 'I' THEN A.N3
END DISTINCT_KEYS
,CASE A.TYPE
WHEN 'I' THEN A.N7
END BTREE_LEVEL
,CASE A.TYPE
WHEN 'I' THEN A.N6
END CLUSTERING_FACTOR
,CASE A.TYPE
WHEN 'C' THEN A.N1
END NUM_DISTINCT
,CASE A.TYPE
WHEN 'C' THEN A.N5
END NUM_NULLS
,CASE A.TYPE
WHEN 'C' THEN A.N1
END AVG_COL_LEN
,A.D1 LAST_ANALYZED
FROM TB_STAT_EXP A
LEFT OUTER JOIN DBA_INDEXES B
ON (A.TYPE = 'I'
AND B.OWNER = A.C5
AND B.INDEX_NAME = A.C1)
WHERE A.STATID = 'SID_20151015'
-- AND A.C1 IN ('EMP3')
-- AND A.TYPE IN ('C')
ORDER BY TABLE_OWNER
,TABLE_NAME
,OBJECT_TYPE DESC
,INDEX_OWNER
,INDEX_NAME
,COLUMN_NAME
;