Oracle TABLE / SQL DML 조사

가. DML 발생시킨 SQL 조사  

-- 출처 이병관 책임님 

SELECT SQ.INSTANCE_NUMBER,
       SN.BEGIN_INTERVAL_TIME,
       MAX(END_INTERVAL_TIME) MAX_END_TIME,
       SQ.SQL_ID ,
       SQ.MODULE,
       SQ.PLAN_HASH_VALUE,
       SQ.PARSING_SCHEMA_NAME ,
       (SELECT SUBSTR(T.SQL_TEXT, 1, 50)
          from DBA_HIST_SQLTEXT T
         where T.SQL_ID = SQ.SQL_ID) AS SQL_INFO ,
       SUM(EXECUTIONS_DELTA) ,
       ROUND( SUM(ELAPSED_TIME_DELTA)/1000000 ) "ALL_ELPASED_TIME(S)" ,
       ROUND(( SUM(ELAPSED_TIME_DELTA) / SUM(EXECUTIONS_DELTA) )/1000000) "AVG_ELPASED_TIME(S)" ,
       ROUND( SUM(BUFFER_GETS_DELTA)) "ALL_LOGICAL_IO" ,
       ROUND(( SUM(BUFFER_GETS_DELTA) / SUM(EXECUTIONS_DELTA) )) "AVG_LOGICAL_IO" ,
       ROUND( SUM(DISK_READS_DELTA)) "ALL_PHYSICAL_IO" ,
       ROUND(( SUM(DISK_READS_DELTA) / SUM(EXECUTIONS_DELTA) )) "AVG_PHYSICAL_IO" ,
       ROUND( SUM(ROWS_PROCESSED_DELTA)) "ALL_ROWS_PROCESSED" ,
       ROUND(( SUM(ROWS_PROCESSED_DELTA) / SUM(EXECUTIONS_DELTA) )) "AVG_ROWS_PROCESSED" ,
       ROUND( SUM(APWAIT_DELTA)/1000) "ALL_APWAIT(ms)" ,
       ROUND(( SUM(APWAIT_DELTA) / SUM(EXECUTIONS_DELTA) )/1000) "AVG_APWAIT(ms)" ,
       ROUND( SUM(IOWAIT_DELTA)/1000) "ALL_IOWAIT(ms)" ,
       ROUND(( SUM(IOWAIT_DELTA) / SUM(EXECUTIONS_DELTA) )/1000) "AVG_IOWAIT(ms)" ,
       ROUND( SUM(CLWAIT_DELTA)/1000) "ALL_CLWAIT(ms)" ,
       ROUND(( SUM(CLWAIT_DELTA) / SUM(EXECUTIONS_DELTA) )/1000) "AVG_CLWAIT(ms)" ,
       ROUND( SUM(CCWAIT_DELTA)/1000) "ALL_CCWAIT(ms)" ,
       ROUND(( SUM(CCWAIT_DELTA) / SUM(EXECUTIONS_DELTA) )/1000) "AVG_CCWAIT(ms)" ,
       ROUND( SUM(DIRECT_WRITES_DELTA)/1000) "ALL_DIRECT_WRITE(ms)" ,
       ROUND(( SUM(DIRECT_WRITES_DELTA)/SUM(EXECUTIONS_DELTA) )/1000) "AVG_DIRECT_WRITE(ms)"
  FROM DBA_HIST_SQLSTAT SQ,
       DBA_HIST_SNAPSHOT SN,
       DBA_HIST_SQLTEXT ST
 WHERE SQ.SNAP_ID = SN.SNAP_ID
   AND SQ.DBID = SN.DBID
   AND SQ.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
   AND SQ.SQL_ID = ST.SQL_ID
   AND SN.BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2018/02/09 18:00:00', 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2018/02/09 19:00:00', 'YYYY/MM/DD HH24:MI:SS')
   AND SQ.EXECUTIONS_DELTA > 0
   AND ST.COMMAND_TYPE <> 3
   AND SQ.PARSING_SCHEMA_NAME NOT IN ( 'SYS','SYSTEM' )
 GROUP BY SQ.INSTANCE_NUMBER,
       SN.BEGIN_INTERVAL_TIME,
       SQ.SQL_ID,
       SQ.MODULE,
       SQ.PLAN_HASH_VALUE,
       SQ.PARSING_SCHEMA_NAME
 ORDER BY 16 DESC 

나.  TABLE 별 DML 발생량 조사 

: 통계정보 갱신을 위해서, Oracle 내부적으로 저장하는 테이블 변경 정보를 활용 

   Flush 하여 메모리 정보를 dba_tab_modificationS 에 반영 후, 조회 하면 된다.

-. 테이블 변경 정도 체크 하기

-. 10% 이상 변경 발생시만 Logging 된다.

-. 전제 조건 : MONITORING=YES / STATISTICS_LEVEL is set to TYPICAL 

-. 아래 질의 결과가 없다면, 강제로 모니터링 정보를 Flush 하면 된다고 한다.

-. execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

-. SYS 로만 수행 가능하다.

-- Memory -> dba_tab_modification  반영 
execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-- DBA_TAB_MODIFICATIONS 조회 
SELECT TABLE_OWNER, TABLE_NAME , INSERTS+UPDATES+DELETES AS ALL_DML_ROWS
      , INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED
FROM   DBA_TAB_MODIFICATIONS 
ORDER BY ( INSERTS + UPDATES + DELETES ) DESC ;