기본 DML 튜닝

DML 성능에 영향을 미치는 요소

• 인덱스
  ↳ 테이블은 Freelist를 통해 입력할 블록을 할당받지만, 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 입력할 블록을 찾아야 한다. 인덱스에 입력하는 과정이 더 복잡하므로 DML  성능에 미치는 영향도 더 크다. 핵심 트랜잭션 테이블에서 인덱스 하나라도 줄이면 TPS(Transaction Per Second)는 그만큼 향상된다.

• 무결성 제약
  ↳ 개체 무결성 , 참조 무결성 , 도메인 무결성 , 사용자 정의 무결성

• 조건절 

• 서브 쿼리 

•  Redo 로깅
  ↳ Redo 로그는 트랜잭션 데이터가 어떤 이유에세건 유실됐을 때, 트랜잭션을 재현함으로써 이전상태로 복구한다.
      DML을 수행할 때마다 Redo 로그를 생성해야 하므로 Redo 로깅은 DML 성능에 영향을 미친다.

• Undo 로깅
  ↳ Undo는 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅한다.
     DML을 수행할 때마다 Undo 로그를 생성해야 하므로 Undo 로깅은 DML 성능에 영향을 미친다.

•  Lock
  ↳ Lock을 필요 이상을 자주, 길게 사용하거나 레벨을 높일수록 DML 성능은 느려진다. 

• 커밋
  ↳ DML을 끝내려면 커밋까지 완료해야 하므로 서로 밀접한 관련이 있다. 특히 DML이 Lock에 의해 블로킹된 경우, 커밋은 DML 성능과 직결된다.
• ↳ 커밋 내부 메커니즘
  1) DB 버퍼캐시 : DB에 접속한 사용자를 대신해 모든 일을 처리하는 서버 프로세스는 버퍼캐시를 통해 데이터를 읽고 쓴다. 버퍼캐시에서 변경된 블록을 모아 주기적으로 데이터파일에 일괄 기록하는 작업은 DBWR프로세스가 맡는다.
  
  2) Redo 로그버퍼 : 버퍼캐시는 휘발성이므로 DBWR 프로세스와 Dirty블록들을 데이터파일에 반영할 때까지 불안한 상태라고 생각할 수 있다. 하지만, Redo로그를 이용해 언제든 복구할 수 있어서 안심해도 된다.
  redo 로그도 파일이기에 redo 로그 파일에 기록하기  전에 먼저 로그버퍼에 기록한다. 그리고 나중에 LGWR 프로세스가 Redo 로그 파일에 일괄(Batch) 기록한다.
  
  3) 트랜잭션 데이터 저장과정 : 한 트랜잭션이 데이터를 변경하고 커밋하는 과정, 그리고 변경된 블록을 데이터파일에 기록하는 과정은 아래 그림과 같다.
  
  4) 커밋 = 저장 버튼 : 커밋은 서버 프로세스가 그때까지 했던 작업을 디스크에 기록하라는 명령어인 셈이다. 오랜동안 커밋하지 않은 채 데이터를 계속 갱신하면 Undo 공간이 부족해져 시스템 자애 상황을 유발할 수 있다. 루프를 돌면서 건건이 커밋한다며, 프로그램 자체 성능이 매우 느려진다. 불필요한 커밋이 없도록 구현해야 한다.

트랜재션 데이터 저장 과정

데이터베이스  call과 성능

SQL은 아래 세 단계로 나누어 실행된다.

 - Parse Call : SQL 파싱과 최적화를 수행하는 단계다. SQL과 실행계획을 라이브러리 캐시에서 찾으면 최적화 단계

 - Execute Call : SQL을 실행하는 단계다. DML은 이 단계에서 모든 과정이 끝나지만, SELECT문은 Fetch 단계를 거친다.

 - Fetch Call : 데이터를 읽어서 사용자에게 결과집합을 전송하는 과정으로 SELECT문에서만 나타난다. 전송할 데이터가 많을 때는 Fetch Call이 여러 번 발생한다.

 

 

Call이 어디서 발생하느냐에 따라 user call과 recursive call로 나눌 수 있다.

user call은 네트워크를 경유해 DBMS 외부로부터 인입되는 Call이다. 그림에 사용자는 맨 왼쪽 클라이언트 단에 위치한다. 하지만, DBMS 입장에서 사용되는 WAS이다. 3-Tier 아키텍처에서 user call은 WAS서버에서 발생하는 call이다.

recursive call은 DBMS 내부에서 발생하는 Call이다. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, PL/SQL로 작성한 사용자 정의 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생하는 Call이 여기에 해당한다.

→ user call이든 recursive call이든, SQL을 실행할 때마다 Parse, Execute, Fetch Call 단계를 거친다. 데이터베이스 Call이 많으면 성능은 느릴 수 밖에 없다.

ㅇ

 

 

 

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝