SQL 튜닝의 시작 (2013년)
SQL Performance Analyzer 0 0 53,884

by 구루비스터디 SPA SQL Performance Analyzer [2018.07.14]


  1. SPA ( SQL Performance Analyzer )
  2. 환경
  3. SPA 프로세스
  4. SPA 테스트 환경
  5. CREATE TABLE
  6. PACKAGE
  7. PACKAGE BODY


SPA ( SQL Performance Analyzer )

  • 환경의 변환에 따른 SQL의 변경 플랜에 대한 영향도 분석
  • 11.2.0.1


환경

  • 데이터 베이스 : 버전 업그레이드, 패치, 파라미터
  • 인덱스 : 변경, 추가
  • 통계 정보 : 최신화
  • OS : CPU, Memory


SPA 프로세스

1. SQL SET 생성 및 등록
2. SQL EXECUTE ( BEFORE )
3. 환경 변환
4. SQL EXECUTE ( AFTER )
5. 비교 분석


SPA 테스트 환경


CREATE TABLE



CREATE TABLE SYS.TEMP_SPA_LOG_JHLEE
(
  SPA_GRP           NUMBER,
  TASK_NAME         VARCHAR2(1000 BYTE),
  BEFORE_EXEC_NAME  VARCHAR2(1000 BYTE),
  AFTER_EXEC_NAME   VARCHAR2(1000 BYTE),
  RN                NUMBER,
  TABLE_NAME        VARCHAR2(1000 BYTE),
  BYTES             NUMBER,
  STATUS            VARCHAR2(1000 BYTE),
  START_DT          DATE,
  END_DT            DATE,
  STAT_GET_DDL      VARCHAR2(2000 BYTE),
  REPORT            CLOB
)
TABLESPACE LCTM_DAT
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
LOB (REPORT) STORE AS 
      ( TABLESPACE  SYSTEM 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        RETENTION
        NOCACHE
        INDEX       (
          TABLESPACE SYSTEM
          STORAGE    (
                      INITIAL          64K
                      NEXT             1
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      FREELISTS        1
                      FREELIST GROUPS  1
                      BUFFER_POOL      DEFAULT
                     ))
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL
MONITORING;




PACKAGE



CREATE OR REPLACE PACKAGE SYS.PKG_SPA AS
/******************************************************************************
   NAME:       PKG_SPA
   PURPOSE: 

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2013-05-24      hyun       1. Created this package.
******************************************************************************/
  
  PROCEDURE CREATE_BEFORE_TASK( IN_TASK_NAME IN VARCHAR2, IN_TABLE_LIKE_NAME IN VARCHAR2);
  
  PROCEDURE CREATE_AFTER_REPORT( IN_TASK_NAME IN VARCHAR2, IN_BEFORE_EXEC_NAME IN VARCHAR2, IN_TABLE_LIKE_NAME IN VARCHAR2  );
  

--  PROCEDURE CREATE_USER( IN_USERNAME IN VARCHAR2 
--                                         , IN_ROLENAME IN VARCHAR2
--                                         , IN_USERNO IN NUMBER);
--                                         
--   PROCEDURE DROP_OBJECT( IN_OBJECT IN VARCHAR2, IN_USER IN VARCHAR DEFAULT 'TEMP');    

END PKG_SPA;
/



PACKAGE BODY



CREATE OR REPLACE PACKAGE BODY SYS.PKG_SPA
AS
                                     
    PROCEDURE  CREATE_BEFORE_TASK( IN_TASK_NAME IN VARCHAR2 , IN_TABLE_LIKE_NAME IN VARCHAR2 ) 
    IS
    /******************************************************************************
       NAME: CREATE_ROLE
       PURPOSE: 
       
        EXEC PKG_SPA.CREATE_BEFORE_TASK( '2013051706' , 'OP')
        
        sqlplus "/as sysdba"<<EOF
        set serveroutput on
        EXEC PKG_SPA.CREATE_BEFORE_TASK( '2013061011' , 'OP')
        exit;
        
        삭제
         EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK( '2013052009' )

        EXEC DBMS_SQLTUNE.DROP_SQLSET( '2013052009' )
    
        

        
       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       0.0       2013-05-24      LeeJH       1. Start   
       1.0       2013-05-28      LeeJH       1. Created this procedure.
    ******************************************************************************/  

    START_SNAP_ID NUMBER;
    END_SNAP_ID NUMBER;
    
    CHECK_SQLSET NUMBER;
    
    V_TASK_NAME VARCHAR2(100);
    
    V_EXEC_NAME VARCHAR2(100);
    
    V_BEGIN_INTERVAL_TIME VARCHAR2(100);
    
    BASELINE_SNAP   DBMS_SQLTUNE.SQLSET_CURSOR;
    
    tname varchar2(30); 

    BEGIN
    
            -- 생성하는 TASK NAME이 없으면 디폴트 설정
        IF IN_TASK_NAME IS NULL THEN
        
            SELECT TO_CHAR( TRUNC( SYSDATE, 'IW' ) - 3 + 1 / 1440 * 60 * 9 , 'YYYYMMDDHH24')
              INTO V_BEGIN_INTERVAL_TIME
              FROM DUAL;
              
        ELSE
        
             -- 존재하는 SQLSET 인지 확인 있으면 에러발생
--            SELECT SQL_ID AS CHECK_SQLSET
--              INTO CHECK_SQLSET
--              FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET ( IN_TASK_NAME ));

        
             V_BEGIN_INTERVAL_TIME := IN_TASK_NAME;
             
        
        END IF; 
        
        V_TASK_NAME := V_BEGIN_INTERVAL_TIME;
        
        V_EXEC_NAME := V_BEGIN_INTERVAL_TIME||'_'||TO_CHAR( LPAD( TRUNC( DBMS_RANDOM.VALUE( 1, 9999999999 ) ),10, '0') );
       
/*
       SELECT COUNT (*)
         INTO CHECK_SQLSET
         FROM TABLE (DBMS_SQLTUNE.select_sqlset (in_task_name) )
        WHERE ROWNUM <= 1;
        
    
        -- 존재하는 SQL 셋이라면 삭제후 생성
        IF CHECK_SQLSET = 1  THEN
        
            /*+
            select name,created,last_modified,statement_count,description from DBA_SQLSET;

            select description, created, owner from DBA_SQLSET_REFERENCES where sqlset_name = '2013061011';

            select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = '2013061011'

            EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => '2013061011') */  /* 
            
            CHECK_SQLSET := 0;
            
            SELECT COUNT (*)
              INTO CHECK_SQLSET
              FROM dba_advisor_tasks
             WHERE task_name = in_task_name
               AND ROWNUM <= 1;
            
            IF CHECK_SQLSET = 1 THEN
            
                DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => in_task_name );
            
            END IF;
        
            DBMS_SQLTUNE.DROP_SQLSET( IN_TASK_NAME );
            
        
        END IF;
        
        */
        
        DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'CREATE_SQLSET START');
        
        DBMS_SQLTUNE.CREATE_SQLSET( SQLSET_NAME => V_TASK_NAME, DESCRIPTION => 'I/O intensive workload');
        -- SELECT *   FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( '2013052009_BE' ) ) 
        -- exec DBMS_SQLTUNE.DROP_SQLSET( '2013052009_BE' )
        
        SELECT MIN( SNAP_ID ) AS START_SNAP_ID, MAX( SNAP_ID ) AS END_SNAP_ID
          INTO START_SNAP_ID, END_SNAP_ID
          FROM DBA_HIST_SNAPSHOT
         WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE( V_BEGIN_INTERVAL_TIME, 'YYYYMMDDHH24' ) AND TO_DATE( V_BEGIN_INTERVAL_TIME, 'YYYYMMDDHH24' ) + 1 / 1440 * 60 * 1  -- 1 시간 +
           AND INSTANCE_NUMBER = 1
         ORDER BY 1;
         
         
         DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'CREATE_SQLSET END');
          OPEN BASELINE_SNAP  FOR
        SELECT VALUE(p)
          FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( START_SNAP_ID,END_SNAP_ID,NULL,NULL, NULL,NULL,NULL,1,NULL,'ALL')) p
         WHERE PARSING_SCHEMA_NAME in ('EC_APP','EC_ADM')
--           AND COMMAND_TYPE=3 -- 20130808 주석처리 
           AND SQL_TEXT NOT LIKE '%searchEntrCostBondConcommonSignsMgmt%'; -- 넘 오래걸려서 배제함.
           
         
         DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'LOAD_SQLSET START');
           
         DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> V_TASK_NAME, POPULATE_CURSOR=>BASELINE_SNAP );
          -- SELECT sql_text FROM   dba_sqlset_statements WHERE  sqlset_name = 'spa_test_sqlset';
         
         DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'CREATE_ANALYSIS_TASK START');
          
          -- 20130808 주석 처리 
         DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER ( DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => V_TASK_NAME,TASK_NAME => V_TASK_NAME ) ,'DISABLE_MULTI_EXEC','TRUE'); --, ORDER_BY  => 'BUFFER_GETS'

--         tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => V_TASK_NAME, TASK_NAME => V_TASK_NAME );
        
         DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'EXECUTE_ANALYSIS_TASK START');
         
         DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( TASK_NAME => V_TASK_NAME, EXECUTION_TYPE => 'TEST EXECUTE', EXECUTION_NAME => V_EXEC_NAME );
         --DBMS_SQLPA.DROP_ANALYSIS_TASK
         
         
         DBMS_APPLICATION_INFO.SET_MODULE('CREATE_AFTER_REPORT', 'START');
         
         CREATE_AFTER_REPORT( IN_TASK_NAME   => V_TASK_NAME, IN_BEFORE_EXEC_NAME => V_EXEC_NAME, IN_TABLE_LIKE_NAME => IN_TABLE_LIKE_NAME  );
         
         DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
         
         
         
        /*
        
        EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK( '2013052009' )

        EXEC DBMS_SQLTUNE.DROP_SQLSET( '2013052009' )
    
        
         --task select
         select TASK_NAME,
       ADVISOR_NAME,
       created
  from DBA_ADVISOR_TASKS
 where task_name LIKE '%2013052009%'

        
         SELECT EXECUTION_NAME
             , STATUS
             , EXECUTION_END
          FROM DBA_ADVISOR_EXECUTIONS
         WHERE EXECUTION_NAME LIKE 'BEFORE_%'  --
        ORDER BY EXECUTION_END DESC
        
        */

   END CREATE_BEFORE_TASK;
   
                                     
    PROCEDURE  CREATE_AFTER_REPORT( IN_TASK_NAME IN VARCHAR2, IN_BEFORE_EXEC_NAME IN VARCHAR2,IN_TABLE_LIKE_NAME IN VARCHAR2  ) 
    IS
    /******************************************************************************
       NAME: CREATE_ROLE
       PURPOSE: 
       
      
        
       EXEC
       
        EXEC PKG_SPA.CREATE_BEFORE_TASK( '2013051512' )
        
       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        2013-05-24      LeeJH       1. Created this procedure.   
    ******************************************************************************/  
    
    V_AFTER_EXEC_NAME VARCHAR2( 1000 );
    
    INTO_MAX_RN NUMBER;
    
    
    
    BEGIN
    
--        진행 로그
    INSERT INTO TEMP_SPA_LOG_JHLEE ( BYTES, SPA_GRP, TASK_NAME, BEFORE_EXEC_NAME, START_DT, RN, TABLE_NAME, STATUS, STAT_GET_DDL )
    SELECT BYTES, NVL( (SELECT MAX( SPA_GRP ) + 1 FROM TEMP_SPA_LOG_JHLEE ), 1 ) AS SPA_GRP,  IN_TASK_NAME AS TASK_NAME, IN_BEFORE_EXEC_NAME AS BEFORE_EXEC_NAME, SYSDATE START_DT, ROWNUM RN, SEGMENT_NAME,'EXEC' AS STATUS, STAT_GET_DDL
      FROM (SELECT SEGMENT_NAME,SUM(C.BYTES) BYTES, 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || C.OWNER || ''', TABNAME => ''' || C.SEGMENT_NAME || ''', CASCADE => TRUE, ESTIMATE_PERCENT =>''' ||
                       CASE
                --         WHEN SUM(C.BYTES)/(1024*1024) <= 10 THEN 100
                --         WHEN SUM(C.BYTES)/(1024*1024) < 50 THEN 80
                --         WHEN SUM(C.BYTES)/(1024*1024) < 100 THEN 40
                         WHEN SUM(C.BYTES)/(1024*1024) < 500 THEN 100
                         WHEN SUM(C.BYTES)/(1024*1024) < 1000 THEN 50
                         WHEN SUM(C.BYTES)/(1024*1024) < 10000 THEN 25
                         WHEN SUM(C.BYTES)/(1024*1024) < 20000 THEN 5
                         ELSE 1
                       END || ''', DEGREE => ''' ||
                       CASE
                         WHEN SUM(C.BYTES)/(1024*1024) <= 10 THEN 1
                         WHEN SUM(C.BYTES)/(1024*1024) < 50 THEN 2
                         WHEN SUM(C.BYTES)/(1024*1024) < 100 THEN 4
                         WHEN SUM(C.BYTES)/(1024*1024) < 500 THEN 4
                         WHEN SUM(C.BYTES)/(1024*1024) < 1000 THEN 6
                         WHEN SUM(C.BYTES)/(1024*1024) < 10000 THEN 8
                         ELSE 12
                       END || ''',  METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'',NO_INVALIDATE=>false); END;' STAT_GET_DDL
                FROM   DBA_TABLES A,
                       DBA_OBJECTS B,
                       DBA_SEGMENTS C
                WHERE  A.TABLE_NAME=B.OBJECT_NAME
                AND B.OBJECT_NAME=C.SEGMENT_NAME
                AND B.OBJECT_TYPE='TABLE'
                AND A.OWNER IN ('USER')
                AND NOT EXISTS (SELECT 1   FROM DBA_PART_TABLES PT WHERE PT.TABLE_NAME=A.TABLE_NAME)
                AND TABLE_NAME NOT LIKE 'LN%'
                AND TABLE_NAME NOT LIKE '%TMP%'
                AND TABLE_NAME NOT LIKE '%_BACKUP'
                AND TABLE_NAME NOT LIKE '%_OLD%'
                AND TABLE_NAME NOT LIKE '%TEMP%'
                AND TABLE_NAME NOT LIKE 'STATS%'
                AND TABLE_NAME NOT LIKE 'INO%'
                AND TABLE_NAME NOT LIKE '%CLOB%'
                AND TABLE_NAME NOT LIKE '%KHM%'
                AND TABLE_NAME NOT LIKE '%TABLE%'
                AND TABLE_NAME NOT LIKE '%BAK'
                AND TABLE_NAME 'LTCUST_LOG'
                AND TABLE_NAME LIKE UPPER( IN_TABLE_LIKE_NAME )||'%'
                AND LAST_ANALYZED < TRUNC( SYSDATE, 'YYYY' )
            --        AND B.CREATED >SYSDATE -90
--                AND C.BYTES > 10737418240
              GROUP BY C.OWNER, C.SEGMENT_NAME
              ORDER BY C.OWNER, BYTES, C.SEGMENT_NAME
              );
--                WHERE ROWNUM <= 2;
            
        COMMIT;

--    
        --나중에 간단 셀렉트로 변경.
        FOR RLIST in  ( SELECT *
                          FROM TEMP_SPA_LOG_JHLEE
                         WHERE TASK_NAME = IN_TASK_NAME 
                           AND SPA_GRP = (SELECT MAX( SPA_GRP ) FROM TEMP_SPA_LOG_JHLEE )
                         ORDER BY BYTES, TABLE_NAME                             
--                            WHERE ROWNUM <= 2
        ) LOOP
        
            UPDATE TEMP_SPA_LOG_JHLEE
               SET STATUS = 'EXECUTE_TABLE_STATS'
                 , START_DT = SYSDATE
             WHERE SPA_GRP = RLIST.SPA_GRP
               AND TASK_NAME = RLIST.TASK_NAME
               AND TABLE_NAME = RLIST.TABLE_NAME;  
                              
            COMMIT;
                          
            EXECUTE IMMEDIATE RLIST.STAT_GET_DDL;
                          
            UPDATE TEMP_SPA_LOG_JHLEE
               SET STATUS = 'COMPLETE_TABLE_STATS'
                 , END_DT = SYSDATE
             WHERE SPA_GRP = RLIST.SPA_GRP
               AND TASK_NAME = RLIST.TASK_NAME
               AND TABLE_NAME = RLIST.TABLE_NAME;  
                              
            COMMIT;
          
            IF RLIST.BYTES >= 10737418240 THEN
              
                V_AFTER_EXEC_NAME := IN_TASK_NAME||'_'||TO_CHAR( LPAD( TRUNC( DBMS_RANDOM.VALUE( 1, 9999999999 ) ),10, '0') );
                
                DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name  => IN_TASK_NAME, execution_type => 'TEST EXECUTE',  EXECUTION_NAME => V_AFTER_EXEC_NAME );
                
                UPDATE TEMP_SPA_LOG_JHLEE
                   SET STATUS = 'COMPLETE_AFTER_TASK'
                     , AFTER_EXEC_NAME = V_AFTER_EXEC_NAME
                     , END_DT = SYSDATE
                WHERE SPA_GRP = RLIST.SPA_GRP
                  AND TASK_NAME = RLIST.TASK_NAME
                  AND TABLE_NAME = RLIST.TABLE_NAME;  
                
                COMMIT;
                
                -- SPA 비교 분석
                DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(   task_name => IN_TASK_NAME
                                                        , execution_type   => 'compare performance'
                                                        , execution_params => dbms_advisor.arglist(   'execution_name1'
                                                                                                       , IN_BEFORE_EXEC_NAME
                                                                                                       , 'execution_name2'
                                                                                                       , V_AFTER_EXEC_NAME 
                                                                                                       , 'comparison_metric'
                                                                                                       , 'buffer_gets'
                                                                                                      ) );
                                                                                                       
               UPDATE TEMP_SPA_LOG_JHLEE
                  SET STATUS = 'COMPLETE_FINAL_TASK'
                    , END_DT = SYSDATE
    --                , AFTER_EXEC_NAME = V_AFTER_EXEC_NAME
    --                , START_DT = SYSDATE
                WHERE SPA_GRP = RLIST.SPA_GRP
                  AND TASK_NAME = RLIST.TASK_NAME
                  AND TABLE_NAME = RLIST.TABLE_NAME;                                                                                        
                                                          
                COMMIT;                                                
                                                    
               UPDATE TEMP_SPA_LOG_JHLEE
                  SET REPORT = ( SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK( IN_TASK_NAME, 'HTML', 'ALL', 'ALL') FROM DUAL )
                WHERE 1 = 1 --SPA_GRP = RLIST.SPA_GRP
                  AND TASK_NAME = RLIST.TASK_NAME
                  AND TABLE_NAME = RLIST.TABLE_NAME;  
                
                 
                UPDATE TEMP_SPA_LOG_JHLEE
                  SET STATUS = 'COMPLETE_REPORT'   
                    , END_DT = SYSDATE              
    --                , REPORT = ( SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK( IN_TASK_NAME||'_FINAL_'||RLIST.RN, 'HTML', 'ALL') FROM DUAL )
                WHERE SPA_GRP = RLIST.SPA_GRP
                  AND TASK_NAME = RLIST.TASK_NAME
                  AND TABLE_NAME = RLIST.TABLE_NAME;    
                  
               
               COMMIT;
            
            END IF;
       
            
        END LOOP;
    
    END CREATE_AFTER_REPORT;
      
END PKG_SPA;
/
{CODE}


 
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3838

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입