[Oracle] 오라클 Data Dictionary, 데이터 딕셔너리

Data Dictionary

- 오라클 데이터베이스의 메모리 구조와 파일에 대한 구조 정보 저장
- 각 오브젝트들이 사용하고 있는 공간들의 정보 저장
- 제약 조건 정보 저장
- 사용자 관련 정보 저장
- Role, Privilege 관련 정보 저장
- 감사 및 보안 관련 정보 저장

v$sql
- 라이브러리 캐시에 캐싱돼 있는 각 Child 커서에 대한 수행통계를 보여준다.
- 쿼리가 수행을 마칠 때마다 갱신(수행시간이 긴 쿼리는 5초마다 갱신)

SELECT /* 라이브러리 캐시에 저장된 SQL 커서 자체에 대한 정보 */ 
          SQL_ID, CHILD_NUMBER, SQL_TEXT, SQL_FULLTEXT, PARSING_SCHEMA_NAME 
          /* SQL 커서에 의해 사용되는 메모리 사용량 */  
        , SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM 
          /* 하드파싱 및 무효화 발생횟수, Parse, Execute, Fetch Call 발생 횟수,  
             Execute 또는 Fetch Call 시점에 처리한 로우 건수 등 */ 
        , LOADS, INVALIDATIONS, PARSE_CALLS, EXECUTIONS, FETCHES, ROWS_PROCESSED 
          /* SQL을 수행하면서 사용된 CPU time, 소요시간 */ 
        , CPU_TIME, ELAPSED_TIME 
          /* SQL을 수행하면서 발생한 논리적 블록 읽기, 디스크 읽기, 소트 발생 횟수 */ 
        , BUFFER_GETS, DISK_READS, SORTS 
          /* SQL 수행 도중 대기 이벤트 때문에 지연이 발생한 시간 */ 
        , APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME 
          /* 커서가 라이브러리 캐시에 처음 적재된 시점, 마지막에 수행된 시점 */ 
        , FIRST_LOAD_TIME, LAST_ACTIVE_TIME 
 FROM V$SQL; 

※ 각 컬럼값들은 누적값을 나타내기 때문에 각각의 SQL 수행횟수로 나눠 평균값을 구해야 함  

계정별 통계

SELECT  PARSING_SCHEMA_NAME  
         , COUNT(*) SQL_CNT 
         , COUNT(DISTINCT SUBSTR(SQL_TEXT, 1, 100)) SQL_CNT2 
         , SUM(EXECUTIONS) EXECUTIONS 
         , ROUND(AVG(BUFFER_GETS/EXECUTIONS)) BUFFER_GETS 
         , ROUND(AVG(DISK_READS/EXECUTIONS)) DISK_READS 
         , ROUND(AVG(ROWS_PROCESSED/EXECUTIONS)) ROWS_PROCESSED 
         , ROUND(AVG(ELAPSED_TIME/EXECUTIONS/1000000),2) "ELAPSED_TIME(AVG)" 
         , COUNT(CASE WHEN ELAPSED_TIME/EXECUTIONS/1000000 >= 10 THEN 1 END) "BAD SQL" 
         , ROUND(MAX(ELAPSED_TIME/EXECUTIONS/1000000),2) "ELAPSED_TIME(MAX)" 
   FROM V$SQL 
 WHERE LAST_ACTIVE_TIME >= SYSDATE - 7 
    AND EXECUTIONS > 0 
 GROUP BY PARSING_SCHEMA_NAME; 

※ SQL_CNT가 SQL_CNT2보다 현저히 크면 바인드 변수 처리가 필요하다고 판단할 수 있음
   (불필요한 하드파싱 발생)

 

v$sqlarea 
- Parent 커서에 대한 수행통계, 대부분이 v$sql을 group by한 값

IO/CPU Top SQL 추출
(* 출처: EXEM 아카데미 강의 자료) 

SELECT  SQL_ID,  
           SCHEMA_NAME,  
           MODULE,  
           ELA_RATIO,  
           ELA_TOT,  
           CPU_RATIO,  
           CPU_TOT,  
           EXEC_RATIO,  
           EXEC_TOT,  
           LIO_RATIO,  
           LIO_TOT,  
           PIO_RATIO,  
           PIO_TOT,  
           ROWS_RATIO,  
           ROWS_TOT  
 FROM  (SELECT SQL_ID,  
                      PARSING_SCHEMA_NAME AS SCHEMA_NAME,  
                      NVL(SUBSTR(B.MODULE, 1, 15), '-') AS MODULE,  
                      ROUND(RATIO_TO_REPORT(SUM(B.ELAPSED_TIME_DELTA)) OVER() * 100, 1) AS ELA_RATIO,  
                      ROUND(SUM(B.ELAPSED_TIME_DELTA) / 1000000, 0) AS ELA_TOT,  
                      ROUND(RATIO_TO_REPORT(SUM(B.CPU_TIME_DELTA)) OVER() * 100, 1) AS CPU_RATIO,  
                      ROUND(SUM(B.CPU_TIME_DELTA) / 1000000, 0) AS CPU_TOT,  
                      ROUND(RATIO_TO_REPORT(SUM(B.EXECUTIONS_DELTA)) OVER() * 100, 1) AS EXEC_RATIO,  
                      SUM(B.EXECUTIONS_DELTA) AS EXEC_TOT,  
                      ROUND(RATIO_TO_REPORT(SUM(B.BUFFER_GETS_DELTA)) OVER() * 100, 1) AS LIO_RATIO,  
                      SUM(B.BUFFER_GETS_DELTA) AS LIO_TOT,  
                      ROUND(RATIO_TO_REPORT(SUM(B.DISK_READS_DELTA)) OVER() * 100, 1) AS PIO_RATIO,  
                      SUM(B.DISK_READS_DELTA) AS PIO_TOT,  
                    ROUND(RATIO_TO_REPORT(SUM(B.ROWS_PROCESSED_DELTA))  OVER() * 100, 1) AS ROWS_RATIO,  
                      SUM(B.ROWS_PROCESSED_DELTA) AS ROWS_TOT  
              FROM DBA_HIST_SNAPSHOT A,  
                      DBA_HIST_SQLSTAT B  
            WHERE A.INSTANCE_NUMBER = 1  
                AND A.BEGIN_INTERVAL_TIME >= TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'))  
                AND A.END_INTERVAL_TIME <= TO_DATE( TO_CHAR(SYSDATE, 'YYYY-MM-DD')) + 1  
                AND A.DBID = B.DBID  
                AND B.PARSING_SCHEMA_NAME NOT IN ( 'SYS', 'SYSTEM', 'SYSMAN' )  
                AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER  
                AND A.SNAP_ID = B.SNAP_ID  
            GROUP BY B.SQL_ID, B.PARSING_SCHEMA_NAME, B.MODULE  
            ORDER BY CPU_RATIO DESC)  
WHERE ROWNUM <= 100;  

* DBA_HIST_SNAPSHOT : 최근에 생성된 snapshot에 대한 정보를 제공
* DBA_HIST_SQLSTAT : V$SQL의 통계정보 HISTORY를 저장