[1] SQL 수행구조

풀이

(1) 데이터베이스 아키텍처

p.14 ~ 16

백그라운드 프로세스 : 2번

• SMON
• PMON
• DMWn : Dirty 버퍼 > 디스크
• LGWR : 로그 버퍼 > 로그 파일
• ⭐️ 로그버퍼에 로그를 기록하는 것은 서버 프로세스(사용자 프로세스)

Redo 메커니즘 : 7번

✅ Log Force at commit ( 로그 버퍼 > 로그 파일 )

DML을 수행하는 사용자 프로세스가 로그 버퍼에 로그를 기록하고 데이터 블록을 변경한다.
이후 Log Writer(LGMR)가 주기적으로 로그 버퍼 엔트리를 Redo 로그 파일에 기록한다.
메모리상의 로그 버퍼는 언제든 유실될 가능성이 있다.
따라서 트랜젝션의 영속성을 보장하려면 최소한 커밋 시점에는 로그를 메모리가 아닌 데이터파일에 안전하게 기록해야 한다.

✅ Write Ahead Logging ( 로그 파일 > 데이터 파일 )

버퍼캐시 블록을 갱신하기 전에 먼저 Redo 엔트리를 로그 버퍼에 기록해야 하며, DBWR가 버퍼캐시의 Dirty 블록들을 데이터파일에 기록하기 전에 먼저 LGWR가 해당 Redo 엔트리를 모두 Redo 로그 파일에 기록했음이 보장되어야 한다.

(2) SQL 처리과정

p.17 ~ 19

백그라운드 프로세스 : 2번

(3) 데이터베이스 I/O 매커니즘

p.19 ~ 22

22번 : 버퍼캐시 히트율

• 읽은 총 블록수 : Query + current
  • 안에 물리적 I/O도 포함

[2] SQL 분석 도구

(1) 예상 실행계획

p.38 ~ 39

1번 : 오라클에서 예상 실행계획을 출력

• explain plan for문을 사용 : 실행계획이 PLAN_TABLE에 저장된다.
• dbms_xplan.display 함수를 사용 : PLAN_TABLE에 저장된 정보를 읽어 분석하기 쉬운 형태로 포매팅

2번 : sql server 에서 예상 실행계획을 출력

• set showplan_text on 문을 사용

4번 : 오라클 Autotrace

✅ 오라클 AutoTrace에서 확인할 수 있는 정보

• 예상 실행계획
• 실제 디스크에서 읽은 블록 수
• 실제 기록한 Redo 크기
  
• ⭐️ 오라클 AutoTrace에서 실제 사용한 CPU Time은 알 수 없다.

5번 : 오라클 AutoTrace 옵션

✅ AutoTrace에서 사용할 수 있는 옵션

[키워드 다섯가지를 조합하는 것이다.]

• 공통 문법 : set autotrace
• 선택 문법1(결과출력 여부,필수)
  • 결과 출력 : on
  • 결과 미출력 : traceonly
• 선택 문법2(실행계획/실행통계,선택)
  • 실행계획 : explain
  • 실행통계 : statistics

[결과 출력 O (당연히 SQL 실행)]

1. set autotrace on
   • SQL을 실행
   • 결과집합 있음
   • 예상 실행계획 및 실행통계를 출력
2. set autotrace on explain
   • SQL을 실행
   • 결과집합 있음
   • 예상 실행계획을 출력
3. set autotrace on statistics
   • SQL을 실행하고
   • 결과집합 있음
   • 실행통계를 출력

[결과 출력 X]

1. set autotrace traceonly
   • SQL을 실행하고
   • 결과는 출력하지 않고
   • 예상 실행계획과 실행통계 출력
2. set autotrace traceonly explain
   • ⭐️ SQL을 실제로 실행하지 않고
   • 예상 실행계획만 출력
3. set autotrace traceonly statistics
   • SQL을 실행하고
   • 결과는 출력하지 않고
   • 실행통계만 출력

(2) SQL 트레이스

p.39 ~ 42

6번 : 오라클에서 SQL 트레이스를 확인

alter session set sql_trace='true'

8번 : 오라클 기본 Trace에서 확인할 수 있는 정보

✅ 오라클 기본 Trace에서 확인할 수 있는 정보

• 예상 실행계획
• 실제 디스크에서 읽은 블록 수
• 실제 사용한 CPU Time
• 하드파싱 횟수
• ⭐️ 실제 기록한 Redo 크기는 알 수 없다.

⭐️autotrace에서는 실제 사용한 CPU Time을 알 수 없고
오라클의 기본 SQL 트레이스에서는 실제 기록한 Redo 크기를 알 수 없다.⭐️

10번 : 오라클 SGA 메모리에 기록한 SQL 트레이스 정보를 출력하기

SQL> select /*+ gather_plan_statistics */ count(*) from big_table;
SQL> select from table( dbms_xplan.display_cursor(null, null, 'allstats last'));
SQL> select * from table(dbms_xplan.display_cursor( [sql_id] , [child_number], 'allstats last'));

1. SQL에 gather_plan_statistics 힌트를 지정하면, SQL 트레이스 정보를 서버 파일이 아닌 SGA 메모리에 기록한다.
2. SGA 메모리에 저장된 트레이스 정보를 dbms_xplan.display_cursor 함수를 이용하면 분석하기 쉬운 형태로 포매팅해 준다.
   • 첫 번째와 두 번째 인자에는 SQL 커서의 ID와 CHILD_NUMBER를 입력해야 한다.
   • 첫 번째와 두 번째 인자에 null, null을 입력하면 바로 직전에 수행한 커서 ID와 CHILD_NUMBER를 내부에서 자동 선택해 준다.

13번 : DBMS_XPLAN.DISPLAY_CURSOR 함수를 통해 추출한 SQL 트레이스 정보

✅ _DBMS_XPLAN.DISPLAY_CURSOR 함수를 통해 추출한 SQL 트레이스 정보

• Starts : 각 오퍼레이션 단계별 실행 횟수
• E-Rows : 옵티마이저가 예상한 Rows
• A-Rows : 각 오퍼레이션 단계에서 읽거나 갱신한 로우 수
  • SQL 트레이스 항목에서는 rows
• A-Times : 각 오퍼레이션 단계별 소요시간
  • SQL 트레이스 항목에서는 times
• Buffers : 캐시에서 읽은 버퍼 블록 수
  • SQL 트레이스 항목에서는 query(=cr) , current
• Reads : 디스크에서 읽은 블록수
  • SQL 트레이스 항목에서는 pr

14번 : sql server 에서 sql trace를 확인

• set statistics profile on 문을 사용

(3) 응답 시간 분석

p.42 ~ 44

16번 : 대기 이벤트(Wait Event)

• 래치를 획득하는 과정에 경합이 발생하면 대기 이벤트가 나타나지만, 경합없이 바로 읽으면 대기 이벤트가 나타나지 않는다.
• 즉 , 래치를 획득할 때마다 대기 이벤트가 나타나는 것은 아니다.

17번 : SQL 하드 파싱과 가장 관련이 깊은 대기 이벤트

1. Shared Pool에서 특정 오브젝트 정보 또는 SOL 커서를 위한 Free Chunk 할당받으려할 때 shared pool 래치를 할당받아야 한다.
2. Latch: shared pool 대기 이벤트는 shared pool 래치를 할당받는 과정에 발생하는 경합과 관련 있으며, 하드 파싱을 동시에 심하게 일으킬 때 주로 나타난다.
3. library cache lock , library cache pin 대기이벤트는 주로 SOL 수행 도중 DDL을 수행할 때 나타난다.
4. free buffer waits 대기 이벤트는 서버 프로세스가 버퍼 캐시에서 Free Buffer를 찾지못해 DBWR에게 공간을 확보해 달라고 신호를 보낸 후 대기할 때 나타난다.
5. log file sync 대기 이벤트는 커밋 명령을 전송받은 서버 프로세스가 LGWR에게 로그 버퍼를 로그 파일에 기록해 달라고 신호를 보낸 후 대기할 때 나타난다.
6. Latch: shared pool 대기 이벤트는 shared pool 래치를 할당받는 과정에 발생하는 경합과 관련 있으며, 하드 파싱을 동시에 심하게 일으킬 때 주로 나타난다.

응답 시간 분석

• 대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아
  문제를 해결하는 방법
• '대기 이벤트(Wait Event) 기반' 이라고도 한다.

  ✅ Response Time

  Service Time + Wait Time
  = CPU Time + Queue Time

오라클 AWR

• Automatic Workload Repository
• peak 시간대 또는 장애 발생 시점을 전후 가능한 한 짧은 스냅샷 구간을 선택해야 한다.
• 뷰를 조회하지 않고 DMA(Direct Memory Access) 방식으로 SGA 공유 예모리를 직접 액세스해서 성능 정보를 수집하기 때문에 좀 더 빠르게 정보를 수집할 수 있다.
  (statspack은 아님)
• 전통적으로 사용하던 Ratio 기반 성능 분석 방법론에 응답 시간 분석 방법론을 더해 Statspack을 개발하였고 이를 확장 및 업그레이드해서 만든 것이 AWR이다.
• AWR 보고서에 출력되는 항목들은 'dbahist'로 시작하는 각종 뷰를 이용해 사용자가 직접 조회할 수도 있다.(저장은 아님)
• dba_hist_active_sess_history를 통해서는 AMR로 옮겨진 오래된 과거의 세션 히스토리 정보를 확인할 수 있다.

22번 : SQL 파싱과 관련된 인스턴스 효율성 항목들

1. Soft Parse : 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL 을수행한 비율이다. 구하는 공식은 아래와 같다.
   (전체 Parse Call 횟수 - 하드파싱 횟수) / (전체 Parse Call 횟수)* 100
2. Execute to Parse : Parse Call 없이 곧바로 SQL을 수행한 비율, 즉 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율이다.
3. Parse CPU to Parse Elapsed : 파싱 총 소요 시간 중 CPU time이 차지한 비율이다. 파싱에 소요된 시간 중 실제 일을 수행한 시간 비율을 말하며, 이 값이 낮다면 파싱 도중 대기가 많이 발생했음을 의미한다.

23번 : 세션 레벨 실시간 모니터링 기능(ASH)