행위

오라클 플랜 수집 프로그램

DB CAFE

1 오라클 플랜수집 / 성능 진단 프로그램 개발

1.1 SQL 수집 프로그램 테이블

1.1.1 스냅샷 테이블 TB_TUNE_SNAP

CREATE TABLE RTIS_DBA.TB_TUNE_SNAP
(
  SNAP_ID        NUMBER                         DEFAULT 1                     NOT NULL,
  SNAP_START_DT  VARCHAR2(16 BYTE),
  SNAP_END_DT    VARCHAR2(16 BYTE),
  CREATED        DATE                           DEFAULT SYSDATE               NOT NULL,
  UPDATED        DATE                           DEFAULT SYSDATE               NOT NULL
)
;
CREATE UNIQUE INDEX RTIS_DBA.PK_TUNE_SNAP ON RTIS_DBA.TB_TUNE_SNAP
(SNAP_ID)
;
ALTER TABLE RTIS_DBA.TB_TUNE_SNAP ADD (
  CONSTRAINT PK_TUNE_SNAP
  PRIMARY KEY
  (SNAP_ID)
  USING INDEX RTIS_DBA.PK_TUNE_SNAP
  ENABLE VALIDATE);

1.1.2 SQL 테이블 TB_TUNE_SQL

CREATE TABLE RTIS_DBA.TB_TUNE_SQL
(
  SQL_ID              VARCHAR2(13 BYTE),
  SID                 NUMBER,
  HASH_VALUE          NUMBER,
  SNAP_ID             NUMBER,
  PLAN_HASH_VALUE     NUMBER,
  SERIAL#             NUMBER,
  BIND_DATA           RAW(2000),
  ADDRESS             RAW(8),
  MACHINE             VARCHAR2(64 BYTE),
  OSUSER              VARCHAR2(30 BYTE),
  ELAPSED_TIME        NUMBER,
  EXECUTIONS          NUMBER,
  END_OF_FETCH_COUNT  NUMBER,
  LAST_ACTIVE_TIME    DATE,
  CREATED             DATE,
  UPDATED             DATE
);

1.1.3 SQL 쿼리 테이블 TB_TUNE_SQL_TEXT

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_TEXT
(
  SQL_ID        VARCHAR2(13 BYTE),
  SQL_TEXT      VARCHAR2(1000 BYTE),
  SQL_FULLTEXT  CLOB
)
CREATE INDEX RTIS_DBA.IX_TUNE_SQL_TEXT_CTX ON RTIS_DBA.TB_TUNE_SQL_TEXT
(SQL_FULLTEXT)
INDEXTYPE IS CTXSYS.CONTEXT;

1.1.4 SQL 통계 테이블 TB_TUNE_SQL_STATS

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_STATS
(
  SNAP_ID                    NUMBER,
  SQL_ID                     VARCHAR2(13 BYTE),
  LAST_ACTIVE_TIME           DATE,
  LAST_ACTIVE_CHILD_ADDRESS  RAW(8),
  PLAN_HASH_VALUE            NUMBER,
  PARSE_CALLS                NUMBER,
  DISK_READS                 NUMBER,
  DIRECT_WRITES              NUMBER,
  BUFFER_GETS                NUMBER,
  ROWS_PROCESSED             NUMBER,
  SERIALIZABLE_ABORTS        NUMBER,
  FETCHES                    NUMBER,
  EXECUTIONS                 NUMBER,
  END_OF_FETCH_COUNT         NUMBER,
  LOADS                      NUMBER,
  VERSION_COUNT              NUMBER,
  INVALIDATIONS              NUMBER,
  CPU_TIME                   NUMBER,
  ELAPSED_TIME               NUMBER,
  AVG_HARD_PARSE_TIME        NUMBER,
  APPLICATION_WAIT_TIME      NUMBER,
  CONCURRENCY_WAIT_TIME      NUMBER,
  USER_IO_WAIT_TIME          NUMBER,
  PLSQL_EXEC_TIME            NUMBER,
  JAVA_EXEC_TIME             NUMBER,
  SORTS                      NUMBER,
  SHARABLE_MEM               NUMBER,
  TOTAL_SHARABLE_MEM         NUMBER,
  TYPECHECK_MEM              NUMBER,
  IO_INTERCONNECT_BYTES      NUMBER,
  PHYSICAL_READ_REQUESTS     NUMBER,
  PHYSICAL_READ_BYTES        NUMBER,
  PHYSICAL_WRITE_REQUESTS    NUMBER,
  PHYSICAL_WRITE_BYTES       NUMBER,
  CREATED                    DATE               DEFAULT SYSDATE               NOT NULL,
  UPDATED                    DATE               DEFAULT SYSDATE               NOT NULL
)

1.1.5 바인드 변수 테이블 TB_TUNE_SQL_BIND_VAL

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_BIND_VAL
(
  SNAP_ID          NUMBER,
  ADDRESS          RAW(8),
  HASH_VALUE       NUMBER,
  SQL_ID           VARCHAR2(13 BYTE),
  CHILD_ADDRESS    RAW(8),
  CHILD_NUMBER     NUMBER,
  NAME             VARCHAR2(120 BYTE),
  POSITION         NUMBER,
  DATATYPE_STRING  VARCHAR2(60 BYTE),
  WAS_CAPTURED     VARCHAR2(3 BYTE),
  LAST_CAPTURED    DATE,
  VALUE_STRING     VARCHAR2(4000 BYTE),
  VALUE_ANYDATA    SYS.ANYDATA,
  CREATED          DATE,
  UPDATED          DATE
)

1.1.6 실행계획 테이블 TB_TUNE_SQL_PLAN

CREATE TABLE RTIS_DBA.TB_TUNE_SQL_PLAN
(
  SNAP_ID            NUMBER,
  ADDRESS            RAW(8),
  HASH_VALUE         NUMBER,
  SQL_ID             VARCHAR2(13 BYTE),
  PLAN_HASH_VALUE    NUMBER,
  CHILD_ADDRESS      RAW(8),
  CHILD_NUMBER       NUMBER,
  TIMESTAMP          DATE,
  OPERATION          VARCHAR2(120 BYTE),
  OPTIONS            VARCHAR2(120 BYTE),
  OBJECT_NODE        VARCHAR2(160 BYTE),
  OBJECT#            NUMBER,
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(100 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_TYPE        VARCHAR2(80 BYTE),
  OPTIMIZER          VARCHAR2(80 BYTE),
  ID                 NUMBER,
  PARENT_ID          NUMBER,
  DEPTH              NUMBER,
  POSITION           NUMBER,
  SEARCH_COLUMNS     NUMBER,
  COST               NUMBER,
  CARDINALITY        NUMBER,
  BYTES              NUMBER,
  OTHER_TAG          VARCHAR2(140 BYTE),
  PARTITION_START    VARCHAR2(256 BYTE),
  PARTITION_STOP     VARCHAR2(256 BYTE),
  PARTITION_ID       NUMBER,
  OTHER              VARCHAR2(4000 BYTE),
  DISTRIBUTION       VARCHAR2(80 BYTE),
  CPU_COST           NUMBER,
  IO_COST            NUMBER,
  TEMP_SPACE         NUMBER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               NUMBER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  CREATED            DATE,
  UPDATED            DATE
)

1.2 스케쥴 등록

BEGIN
  SYS.DBMS_SCHEDULER.DROP_JOB
    (job_name  => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
      ,start_date      => TO_TIMESTAMP_TZ('2019/04/23 13:30:00.000000 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN RTIS_DBA.sp_dbm_sql_gather(); END;'
      ,comments        => 'SQL GATEHRING FOR 5MIN  EVERY DAY'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'RESTART_ON_RECOVERY'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'RESTART_ON_FAILURE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
     ,attribute => 'STORE_OUTPUT'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/

1.2.1 통계정보 수집 프로시져

CREATE OR REPLACE PROCEDURE RTIS_DBA.sp_dbm_sql_gather
/* 
   0. SNAPSHOT ID 생성 
   1. TB_TUNE_SNAP          -- SNAPSHOT 시작 시간 
   2. TB_TUNE_SQL_STATS     -- SQL_STATS UPSERT  처리  
   3. TB_TUNE_SQL_TEXT      -- SQL_TEXT 
   4. TB_TUNE_SQL_BIND_VAL  -- SQL 바인드 변수 추가   
   5. TB_TUNE_SQL           -- SQL(SQLAREA) 추가        
   6. TB_TUNE_SQL_PLAN      -- SQL PLAN 
   7. TB_TUNE_SNAP          -- SNAPSHOT 종료 시간   
*/

    IS 
-- DECLARE
--변수,상수 선언
    v_msg       long;
    v_snap_id   number default 0;
    v_snap_end_dt date;
BEGIN
    
    /* 0.SNAPSHOT번호 생성 */
    SELECT SNAP_ID+1                                 
         , to_date(SNAP_END_DT,'YYYYMMDD HH24MISS')
      INTO v_snap_id,v_snap_end_dt         
      FROM (
            SELECT /*+ INDEX_DESC(A PK_TUNE_SNAP) */ SNAP_ID SNAP_ID 
                 , SNAP_END_DT 
              FROM RTIS_DBA.TB_TUNE_SNAP
             UNION ALL 
            SELECT 0,'' FROM DUAL
            
             ORDER BY SNAP_ID DESC                       
            )                 
      WHERE ROWNUM <= 1;

     
    /* 1. SNAPSHOT ID 생성 및 SNAPSHOT 시작 시간  */
    INSERT INTO RTIS_DBA.TB_TUNE_SNAP (SNAP_ID,SNAP_START_DT)
         VALUES (v_snap_id,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))
    ;
    
    /* 2. TB_TUNE_SQL_STATS  UPSERT  처리 */ 
    MERGE  /*+  PARALLEL(T,1) APPEND */  INTO TB_TUNE_SQL_STATS T
         USING (
        SELECT A.SQL_ID, LAST_ACTIVE_TIME, LAST_ACTIVE_CHILD_ADDRESS
             , PLAN_HASH_VALUE, PARSE_CALLS, DISK_READS, DIRECT_WRITES, BUFFER_GETS
             , ROWS_PROCESSED, SERIALIZABLE_ABORTS, FETCHES, EXECUTIONS, END_OF_FETCH_COUNT
             , LOADS, VERSION_COUNT, INVALIDATIONS, CPU_TIME
             , ELAPSED_TIME, AVG_HARD_PARSE_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME
             , USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, SORTS, SHARABLE_MEM, TOTAL_SHARABLE_MEM
             , TYPECHECK_MEM, IO_INTERCONNECT_BYTES, PHYSICAL_READ_REQUESTS, PHYSICAL_READ_BYTES
             , PHYSICAL_WRITE_REQUESTS, PHYSICAL_WRITE_BYTES
             , B.INST_ID,B.OSUSER,B.MACHINE
          FROM GV$SQLSTATS A 
         INNER JOIN GV$SESSION B
                 ON B.SQL_ID = A.SQL_ID
                AND B.INST_ID = A.INST_ID  
                AND B.CON_ID = A.CON_ID
                AND B.OSUSER = 'jeus'           
         WHERE LAST_ACTIVE_TIME > v_snap_end_dt
              ) S
           ON (T.SQL_ID = S.SQL_ID 
              )
      WHEN MATCHED THEN
        -- SQL_ID가 같으면 업데이트 
       UPDATE SET T.LAST_ACTIVE_TIME = S.LAST_ACTIVE_TIME , T.LAST_ACTIVE_CHILD_ADDRESS = S.LAST_ACTIVE_CHILD_ADDRESS , T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE , T.PARSE_CALLS = S.PARSE_CALLS , T.DISK_READS = S.DISK_READS , T.DIRECT_WRITES = S.DIRECT_WRITES , T.BUFFER_GETS = S.BUFFER_GETS , T.ROWS_PROCESSED = S.ROWS_PROCESSED , T.SERIALIZABLE_ABORTS = S.SERIALIZABLE_ABORTS , T.FETCHES = S.FETCHES , T.EXECUTIONS = S.EXECUTIONS , T.END_OF_FETCH_COUNT = S.END_OF_FETCH_COUNT , T.LOADS = S.LOADS , T.VERSION_COUNT = S.VERSION_COUNT , T.INVALIDATIONS = S.INVALIDATIONS , T.CPU_TIME = S.CPU_TIME , T.ELAPSED_TIME = S.ELAPSED_TIME , T.APPLICATION_WAIT_TIME = S.APPLICATION_WAIT_TIME , T.CONCURRENCY_WAIT_TIME = S.CONCURRENCY_WAIT_TIME , T.USER_IO_WAIT_TIME = S.USER_IO_WAIT_TIME , T.PLSQL_EXEC_TIME = S.PLSQL_EXEC_TIME , T.JAVA_EXEC_TIME = S.JAVA_EXEC_TIME , T.SORTS = S.SORTS , T.INST_ID = S.INST_ID ,T.OSUSER = S.OSUSER,T.MACHINE =  S.MACHINE
        WHEN NOT MATCHED THEN 
        -- SQL_ID가 없으면 인서트  
        INSERT (SNAP_ID,SQL_ID ,LAST_ACTIVE_TIME ,LAST_ACTIVE_CHILD_ADDRESS ,PLAN_HASH_VALUE ,PARSE_CALLS ,DISK_READS ,DIRECT_WRITES ,BUFFER_GETS ,ROWS_PROCESSED ,SERIALIZABLE_ABORTS ,FETCHES ,EXECUTIONS ,END_OF_FETCH_COUNT ,LOADS ,VERSION_COUNT ,INVALIDATIONS  ,CPU_TIME ,ELAPSED_TIME ,APPLICATION_WAIT_TIME ,CONCURRENCY_WAIT_TIME  ,USER_IO_WAIT_TIME ,PLSQL_EXEC_TIME ,JAVA_EXEC_TIME ,SORTS,INST_ID ,OSUSER,MACHINE)
        VALUES (v_snap_id,S.SQL_ID ,S.LAST_ACTIVE_TIME ,S.LAST_ACTIVE_CHILD_ADDRESS ,S.PLAN_HASH_VALUE ,S.PARSE_CALLS ,S.DISK_READS ,S.DIRECT_WRITES ,S.BUFFER_GETS ,S.ROWS_PROCESSED ,S.SERIALIZABLE_ABORTS ,S.FETCHES ,S.EXECUTIONS ,S.END_OF_FETCH_COUNT ,S.LOADS ,S.VERSION_COUNT ,S.INVALIDATIONS  ,S.CPU_TIME ,S.ELAPSED_TIME ,S.APPLICATION_WAIT_TIME ,S.CONCURRENCY_WAIT_TIME  ,S.USER_IO_WAIT_TIME ,S.PLSQL_EXEC_TIME ,S.JAVA_EXEC_TIME ,S.SORTS,S.INST_ID,S.OSUSER,S.MACHINE)
        ;     
    -- COMMIT;
    
    /* 3. SQL_TEXT INSERT  => TB_TUNE_SQL_TEXT */
    INSERT /*+ APPEND */ INTO TB_TUNE_SQL_TEXT 
           (SQL_ID,SQL_TEXT,SQL_FULLTEXT)
    SELECT A.SQL_ID,A.SQL_TEXT,A.SQL_FULLTEXT
      FROM GV$SQLSTATS A 
     INNER JOIN GV$SESSION B
             ON B.SQL_ID = A.SQL_ID
            AND B.INST_ID = A.INST_ID  
            AND B.CON_ID = A.CON_ID
            AND B.OSUSER = 'jeus'      
     WHERE LAST_ACTIVE_TIME > v_snap_end_dt
       AND NOT EXISTS (SELECT 1 FROM TB_TUNE_SQL_TEXT X
                        WHERE X.SQL_ID = A.SQL_ID)
    ;
        
    /* 4.SQL 바인드 변수 추가 */   
    INSERT /*+ APPEND */ INTO TB_TUNE_SQL_BIND_VAL
           ( SNAP_ID,ADDRESS, HASH_VALUE, SQL_ID, CHILD_ADDRESS
           , CHILD_NUMBER, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED
           , LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA )
    SELECT v_snap_id, A.ADDRESS, A.HASH_VALUE, A.SQL_ID, CHILD_ADDRESS
         , CHILD_NUMBER, NAME, POSITION, DATATYPE_STRING, WAS_CAPTURED
         , LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA     
      FROM GV$SQL_BIND_CAPTURE A
     INNER JOIN GV$SQLAREA   B
             ON B.HASH_VALUE = A.HASH_VALUE
            AND B.ADDRESS    = A.ADDRESS
            AND B.INST_ID    = A.INST_ID
     WHERE B.LAST_ACTIVE_TIME > v_snap_end_dt
       AND EXISTS (SELECT 1 FROM GV$SESSION X
               WHERE X.SQL_ID   = A.SQL_ID
                 AND X.INST_ID  = A.INST_ID            
                 AND X.OSUSER   = 'jeus'
             )    
    ;
    
    
    /* 5.SQL 추가 */
    INSERT /*+ APPEND */ INTO TB_TUNE_SQL
         ( SQL_ID, SID, HASH_VALUE,SNAP_ID, PLAN_HASH_VALUE
         , SERIAL#, BIND_DATA, ADDRESS, MACHINE, OSUSER  
         , ELAPSED_TIME, EXECUTIONS, END_OF_FETCH_COUNT, LAST_ACTIVE_TIME        
         ) 
    SELECT C.SQL_ID, B.SID, C.HASH_VALUE, v_snap_id, C.PLAN_HASH_VALUE
         , B.SERIAL#, C.BIND_DATA, C.ADDRESS, B.MACHINE, B.OSUSER  
         , C.ELAPSED_TIME, C.EXECUTIONS, C.END_OF_FETCH_COUNT, C.LAST_ACTIVE_TIME
      FROM GV$SESSION B
         , GV$SQLAREA C     
--             , v$sql c
     WHERE B.SQL_HASH_VALUE = C.HASH_VALUE
       AND B.INST_ID = C.INST_ID 
       AND B.SQL_ID = C.SQL_ID
       AND C.LAST_ACTIVE_TIME > v_snap_end_dt              
;
    
    
    /* 6.SQL PLAN */
    MERGE /*+  PARALLEL(T,1) APPEND */ INTO TB_TUNE_SQL_PLAN T
         USING (
                SELECT A.ADDRESS, A.HASH_VALUE, A.SQL_ID, A.PLAN_HASH_VALUE, CHILD_ADDRESS
                     , CHILD_NUMBER, TIMESTAMP, OPERATION, OPTIONS, OBJECT_NODE
                     , OBJECT#, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_TYPE
                     , OPTIMIZER, ID, PARENT_ID, DEPTH, POSITION
                     , SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TAG
                     , PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION
                     , CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES
                     , PROJECTION, TIME, QBLOCK_NAME
                  FROM GV$SQL_PLAN A
                     , GV$SQLAREA B
                 WHERE A.ADDRESS = B.ADDRESS
                   AND A.HASH_VALUE = B.HASH_VALUE
                   AND A.SQL_ID = B.SQL_ID
                   AND A.PLAN_HASH_VALUE = B.PLAN_HASH_VALUE
                   AND A.INST_ID = B.INST_ID                                        
                   AND B.LAST_ACTIVE_TIME > v_snap_end_dt            
              ) S
           ON (T.SQL_ID          = S.SQL_ID AND  
               T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE
              )
--      WHEN MATCHED THEN
        -- SQL_ID가 같으면 업데이트 
        --UPDATE SET T.LAST_ACTIVE_TIME = S.LAST_ACTIVE_TIME , T.LAST_ACTIVE_CHILD_ADDRESS = S.LAST_ACTIVE_CHILD_ADDRESS , T.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE , T.PARSE_CALLS = S.PARSE_CALLS , T.DISK_READS = S.DISK_READS , T.DIRECT_WRITES = S.DIRECT_WRITES , T.BUFFER_GETS = S.BUFFER_GETS , T.ROWS_PROCESSED = S.ROWS_PROCESSED , T.SERIALIZABLE_ABORTS = S.SERIALIZABLE_ABORTS , T.FETCHES = S.FETCHES , T.EXECUTIONS = S.EXECUTIONS , T.END_OF_FETCH_COUNT = S.END_OF_FETCH_COUNT , T.LOADS = S.LOADS , T.VERSION_COUNT = S.VERSION_COUNT , T.INVALIDATIONS = S.INVALIDATIONS , T.CPU_TIME = S.CPU_TIME , T.ELAPSED_TIME = S.ELAPSED_TIME , T.APPLICATION_WAIT_TIME = S.APPLICATION_WAIT_TIME , T.CONCURRENCY_WAIT_TIME = S.CONCURRENCY_WAIT_TIME , T.USER_IO_WAIT_TIME = S.USER_IO_WAIT_TIME , T.PLSQL_EXEC_TIME = S.PLSQL_EXEC_TIME , T.JAVA_EXEC_TIME = S.JAVA_EXEC_TIME , T.SORTS = S.SORTS 
        WHEN NOT MATCHED THEN 
        -- SQL_ID가 없으면 인서트  
        INSERT (SNAP_ID,ADDRESS,HASH_VALUE,SQL_ID,PLAN_HASH_VALUE
               ,CHILD_ADDRESS,CHILD_NUMBER,TIMESTAMP,OPERATION,OPTIONS
               ,OBJECT_NODE,OBJECT#,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS
               ,OBJECT_TYPE,OPTIMIZER,ID,PARENT_ID,DEPTH
               ,POSITION,SEARCH_COLUMNS,COST,CARDINALITY,BYTES
               ,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,OTHER
               ,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES
               ,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME         
        )
        VALUES (v_snap_id,S.ADDRESS,S.HASH_VALUE,S.SQL_ID,S.PLAN_HASH_VALUE
               ,S.CHILD_ADDRESS,S.CHILD_NUMBER,S.TIMESTAMP,S.OPERATION,S.OPTIONS
               ,S.OBJECT_NODE,S.OBJECT#,S.OBJECT_OWNER,S.OBJECT_NAME,S.OBJECT_ALIAS
               ,S.OBJECT_TYPE,S.OPTIMIZER,S.ID,S.PARENT_ID,S.DEPTH
               ,S.POSITION,S.SEARCH_COLUMNS,S.COST,S.CARDINALITY,S.BYTES
               ,S.OTHER_TAG,S.PARTITION_START,S.PARTITION_STOP,S.PARTITION_ID,S.OTHER
               ,S.DISTRIBUTION,S.CPU_COST,S.IO_COST,S.TEMP_SPACE,S.ACCESS_PREDICATES
               ,S.FILTER_PREDICATES,S.PROJECTION,S.TIME,S.QBLOCK_NAME )
        
        ;
/*                 
    INSERT INTO TB_TUNE_SQL_PLAN          
         ( SNAP_ID, ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE
         , CHILD_ADDRESS, CHILD_NUMBER, TIMESTAMP, OPERATION, OPTIONS
         , OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS
         , OBJECT_TYPE, OPTIMIZER, ID, PARENT_ID, DEPTH
         , POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES
         , OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER
         , DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES
         , FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME             
         )  
    SELECT v_snap_id, A.ADDRESS, A.HASH_VALUE, A.SQL_ID, A.PLAN_HASH_VALUE
         , CHILD_ADDRESS, CHILD_NUMBER, TIMESTAMP, OPERATION, OPTIONS
         , OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS
         , OBJECT_TYPE, OPTIMIZER, ID, PARENT_ID, DEPTH
         , POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES
         , OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER
         , DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES
         , FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME        
      FROM V$SQL_PLAN A
         , V$SQLAREA  B
     WHERE A.ADDRESS    = B.ADDRESS
       AND A.HASH_VALUE = B.HASH_VALUE
       AND A.SQL_ID     = B.SQL_ID
       AND A.PLAN_HASH_VALUE = B.PLAN_HASH_VALUE     
       AND B.LAST_ACTIVE_TIME > v_snap_end_dt 
      ;
*/    
   /* 7. SNAPSHOT 종료 시간 */    
    UPDATE TB_TUNE_SNAP 
       SET SNAP_END_DT = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
     WHERE SNAP_ID = v_snap_id
    ;
 
    
    COMMIT;
           
  
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
        v_msg := 'ERROR : [ NO_DATA_FOUND ] '; 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
--에러처리
END;
/

1.3 수집결과 플랜 확인

-- SQL PLAN VIEW   
select a.sql_id, a.child_number child,
       a.id
     , a.parent_id pid
      ----------------------------------------------------------------
     ,  lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
	    || decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
        || decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name 
                                         || ' ('|| a.object_type ||')')
        ----------------------------------------------------------------(
        || decode(a.cost||a.cardinality||a.bytes,null,null,' (')
        || decode(a.cost, null, null, ' Cost='||a.cost)
        || decode(a.cardinality, null, null, ' Card='||a.cardinality)
        || decode(a.bytes, null, null, ' Bytes='||a.bytes)
        || decode(a.cost||a.cardinality||a.bytes,null,null,' )')
        ----------------------------------------------------------------)
        || decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
       ---------------------------------------------------------------
     , a.object_type         "Type"
       ----------------------------------------------------------------
     , a.cardinality         "E-Rows"
     , a.bytes               "E-Bytes"
     , a.cost
     , a.time                "E-Time"
       ----------------------------------------------------------------
     , a.partition_start     "Pstart"
     , a.partition_stop      "Pstop"
     , a.partition_id        "Pid"
       ----------------------------------------------------------------
     , a.qblock_name            "QB"
     , a.access_predicates
     , a.filter_predicates
       ----------------------------------------------------------------
  --from v$sql_plan a
  from TB_TUNE_SQL_PLAN A --v$sql_plan a
 where 1 = 1
   AND A.SNAP_ID = (SELECT MAX(SNAP_ID) FROM TB_TUNE_SQL_PLAN  B WHERE B.SQL_ID = A.SQL_ID)
   and a.sql_id = 'fwyjdtd7dudvr' --and plan_hash_value = ''
   and a.child_number = 0
 order by a.sql_id, a.id, a.child_number;