Oracle Procedure 제작 - 통계정보 수집 자동화 프로시저

오라클이 버전업이 되어감에 따라 자동화와 관련된 기능들도 업그레이드되어 갑니다.

하지만, 특정 버전들에서는 자동화 기능이 정상 동작하지 않는 현상도 때때로 발생됩니다.

 

오늘은 Oracle 10g부터 나온 autotask 기능이 제대로 동작하지 않을 때,

프로시저를 통해 통계정보 수집을 자동화할 수 있는 방법을 공유하려고 합니다.

 

해당 프로시저를 사용함에 앞서, 2가지 사항을 확인 및 조치가 필요합니다.

 

A. 다음의 SQL로 통계정보 수집을 하려는 테이블에 통계정보 LOCK이 걸려 있는가 확인

SELECT OWNER, TABLE_NAME,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE OWNER='계정명' AND TABLE_NAME='테이블명';

 

만약, 통계정보 LOCK이 걸려 있다면 해당 테이블의 통계정보 갱신을 위해 LOCK 해제가 필요합니다.

EXEC DBMS_STATS.UNLOCK_TABLE_STATS('계정명','테이블명');

 

B. 다음의 SQL로 통계정보 수집 대상인 테이블이 한 번이라도 수집된 적이 있는지 확인

SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='계정명' AND TABLE_NAME='테이블명';

만약, 한 번도 통계정보 수집이 되지 않았다면 통계정보 수집이 필요합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'계정명',tabname=>'테이블명', cascade=>TRUE, estimate_percent => 10);

 

자.. 이제 확인 작업이 끝났으니, 프로시저 수행을 위한 작업을 해볼까요?

 

 

1. system 계정에 권한 부여 (프로시저 수행 계정이 system 계정일 경우)

GRANT SELECT ON SYS.DBA_TABLES TO SYSTEM; GRANT SELECT ON SYS.DBA_TAB_MODIFICATIONS TO SYSTEM; GRANT ANALYZE ANY TO SYSTEM;

system 계정은 오라클 superuser이지만, 위의 권한 부여를 하지 않으면 프로시저 수행 시 에러가 발생됩니다.

 

 

 

2. 통계정보 수집 자동화 프로시저 생성

system 계정으로 로그인하여 아래 프로시저를 생성합니다.

create or replace PROCEDURE        SU_STAT_GATHER_V1 AS           v_Output                    VARCHAR2(32767);     v_owner                            varchar2(30);           v_tname         varchar2(30);     v_script        varchar2(10000) ;     start_time               date;     end_time                date;     v_SQLERRM      varchar2(10000) ;   BEGIN                DBMS_OUTPUT.PUT_LINE('=== Work Start : '|| to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));              for i in  (select table_owner, table_name from ( select a.*  , nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates from (    select     (select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name     and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows   , (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name     and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed   , (inserts + updates + deletes) tot_updates   , DBA_TAB_MODIFICATIONS.*   from sys.DBA_TAB_MODIFICATIONS   WHERE TABLE_OWNER LIKE 'SCOTT%'   AND TABLE_NAME NOT LIKE '%TIGER%' ) a ) b where perc_updates > 10 )            loop                      v_owner := i.table_owner;                      v_tname := i.table_name;                                   --v_script := 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||v_owner||''',tabname=>'''||v_tname||''', cascade=>TRUE,estimate_percent=>10, novalidate=>true)';             v_script := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||v_owner||''',tabname=>'''||v_tname||''',cascade=>TRUE,estimate_percent=>10,no_invalidate=>true); END;';             DBMS_OUTPUT.PUT_LINE(v_script);                                        EXECUTE IMMEDIATE v_script;              end loop;               DBMS_OUTPUT.PUT_LINE('=== Work End: '|| to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));   EXCEPTION WHEN OTHERS THEN   v_SQLERRM := SQLERRM;   DBMS_OUTPUT.PUT_LINE('An error was ORA-'||SQLCODE||' and Error:'||v_SQLERRM);    DBMS_OUTPUT.PUT_LINE('=== Work Failed!! Please Check the Error and You have to retry!! ===');     --  RAISE; END;

   색으로 마킹된 SQL이 이 프로시저의 핵심입니다.

WHERE절 뒤에 조건을 주어 통계정보 수집 자동화 수집 범위를 조절할 수 있습니다.

   색으로 마킹된 SQL은 마지막으로 통계정보가 수집된 이후의 DML(insert, delete, update) 변경량이 10% 이상 넘어가는 대상 테이블을 조회하는 SQL입니다.

 

 

 

3. 쉘 작성 및 수행

sqlplus -s /nolog << TOY conn system/***** set serveroutput on size 1000000 set feedback off exec system.SU_STAT_GATHER_V1; TOY exit

생성한 프로시저는 oracle db scheduler로 걸 수도 있으나, 여기서는 shell programming을 통한 방법을 공유합니다.

 

수행한 이후에는 아래의 SQL로 통계정보 갱신이 된 테이블 리스트들을 확인할 수 있습니다.

SELECT OWNER, TABLE_NAME,TO_CHAR(LAST_ANALYZED,'YYYYMMDD') AS "최근 통계정보 갱신일자" FROM DBA_TAB_STATISTICS WHERE TO_CHAR(LAST_ANALYZED,'YYYYMMDD') >= SYSDATE;