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

정미나 2019. 5. 20. 17:20

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를 저장