기본 DML튜닝

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

• 인덱스
• 무결성 제약
• 조건절
• 서브쿼리
• Redo 로깅
• Undo 로깅
• Lock
• 커밋

1.1 인덱스와 DML성능

• 테이블에 레코드를 입력하면, 인덱스에도 입력해야한다.
• 테이블은 Freelist(테이블마다 관리하는 데이터 입력이가능한 블록목록)를 통해 입력할 블록 할당
• 인덱스는 정렬된 자료구조이므로 수직적 탐색을 통해 입력할 블록을 찾아야 한다.
• 인덱스에 입력하는 과정이 더 복잡하므로 DML성능에 미치는 영향도 더 크다
• DELETE도마찬가지, 테이블에서 레코드하나 삭제시 인덱스 레코드를 모두 찾아서 삭제해 줘야 한다.
• UPDATE할때는 변경된 컬럼을 참조하는 인덱스만 변경해주며된다.
• 테이블에서 1건 변경할때마다 인덱스에는 두개 오퍼레이션 발생, 인덱스는 정렬된 자료구조이기 때문
  예를 들어 'A'를 'K'로 변경하면 저장위치도 달라지므로 삭제 후 삽입하는 방식으로 처리
• 인덱스가 추가되고 데이터가 자주 수정될수록 성능은 떨어진다.

1.2 무결성 제약과 DML성능

• 무결성 규칙 : 데이터 베이스에 논리적으로 의미있는자료만 저장되게 하는 규칙
  • 개체 무결성(Entity Integrity)
  • 참조 무결성(Referential Integrity)
  • 도메인 무결성(Domain Integrity)
  • 사용자 정의 무결성(또는 업무제약 조건)
• 어플리케이션으로도 구현할 수 있지만, DBMS에서 PK, FK, Check, Not Null같은
  제약(Constraint)을 설정하면 더 완벽하게 데이터 무결성을 지켜낼 수 있다.
• PK, FK제약은 Check, Not Null 제약보다 성능에 더 큰 영향
• Check, Not Null은 제약조건 준수여부만 확인, PK, FK는 실제 데이터를 조회해봐야하기때문

1.3 조건절과 DML성능

• SELECT문과 실행계획이 다르지않으므로, 2장3장에서 학습한 인덱스 튜닝원리 그대로 적용가능

1.4 서브쿼리와 DML성능

• SELECT문과 실행계획동일, 조인 튜닝원리 적용, 특히 서브쿼리 조인과 밀접한 관련

1.5 Redo로깅과 DML성능

• 오라클은 데이터파일과 컨트롤파일에 가해지는 모든 변경사항을 Redo로그에 기록
• Redo로그는 트랜잭션 데이터가 어떤 이유에서건 유실됐을때, 트랜잭션을 재현함으로써 유실이전상태로 복구하는데 사용
• DML수행시마다 Redo로그를 생성해야하므로 Redo로깅은 DML성능에 영향
• INSERT작업에 대해REDO로깅생략기능을 제공하는 이유가 여기있다.

Redo 로그의 용도

• Data Recovery
  • 물리적 디스크깨지는등 Medial Fail발생시 db복구
  • 이때 온라인Redo로그를 백업해둔 Archived Redo로그시용, 'Media Recovery'라고도 한다
• Cache Recovery(Instance Recovery시 roll foward단계)
  • 모든 DBMS가 버퍼캐시 도입하는이유가 I/O성능향상인데, 버퍼캐시는 휘발성
  • 캐시에 저장된 변경사항이 디스크상 데이터블록에 아직기록되지않은 상태에서 정전등이 발생해 인스턴스가 비정상 종료되면, 그때까지의 작업내용을 모두 잃게된다는 뜻
  • 트랜잭션 데이터 유실을 대비하기 위함
• Fast commit
  • 변경된 메모리 버퍼블록을 디스크상의 데이터 블록에 반영하는 작업은 랜덤액세스방식이라 매우느림
  • 로그는 Append방식으로 기록하므로 상대적으로 빠름
  • 트랜잭션에 의한 변경사항을 우선 Append방식으로 빠르게 로그에 기록하고, 변경된 메모리 버퍼블록과 데이터파일 블록간 동기화는 적절한수단(DBWR, checkpoint)을 이용해 나중에 배치(batch)방식으로 일괄수행
  • 사용자 갱신내용이 메모리상 버퍼블록에만 기록한채 아직 디스크에 기록되징낳앗지만, Redo로그만 믿고 빠르게 커밋완료한다는 의미
  • 커밋정보까지 Redo로그파일에 안전하게 기록했다면, 인스턴스 Crash가 발생해도 언제든 복구할수 있음으로 오라클은 안심하고 커밋을 완료할수있다.

  1.6 Undo 로깅과 DML성능

• ROLLBACK > 9i부터 Undo용어사용
• Redo는 트랜잭션 재현, 과거를 현재상태로 돌리는데 사용
• Undo는 트랜잭션을 롤백함으로써 현재를 과거로 되돌리는데 사용
• Redo에는 트랜잭션을 재현하는데 필요한 정보를 로깅하고, Undo에는 변경된 블록을 이전상태로 되돌리는데 필요한 정보를 로깅
• DML수행시마다 Undo를 생성해야하므로 Undo로깅은 DML성능에 영항, BUT Undo를 안남길순없다 그런기능은 제공하지않는다.

  Undo의 용도

• 데이터 입력 수정 삭제시마다Undo세그먼트에 기록, Undo데이터를 기록한공간은 해당 트랜잭션이 커밋하는순간, 다른트랜잭션이 사용할 수 있는 상태로 바뀐다.
• 가장 오래전에 커밋한 Undo공간부터 재사용하므로 Undo데이터가 곧바로 사라지진않겠지만,
  언젠가 다른 트랜잭션 데이터로 덮어쓰이면서 사라질수밖에없다
• Undo에 기록한 데이터 3가지목적
  • Transaction Rollback : 트랜잭션에 의한 변경사항을 최종커밋하지않고 롤백하고자할때
  • Transaction Recovery(Instance Recovery시 rollback단계) : Instance Crash후 Redo를이용해 roll forward단계가 완료되면 최종 커밋되지않은 변경사항까지 모두 복구됨. 따라서 셧다운된 시점에 아직 커밋되지않았던 트랜잭션들을 모두 롤백해야하는데 이때 Undo데이터 사용
  • Read Consistency : 읽기 일관성을 위해 사용

  MVCC모델

• MVCC모델을 사용하는 오라클은 두가지모드로 데이터를 읽는다
• Current모드 : 디스크에서 캐시로 적재된 원본블록을 현재 상태그대로 읽는 방식
• Consistent모드 : 쿼리가 시작된 이후에 다른 트랜잭션에 의해 변경된 블록을 만나면, 원본 블록으로부터 복사본(CR Copy) 블록을 만들고, 거기에 Undo데이터를 적용함으로써 쿼리가'시작된 지점'으로 되돌려서 읽는 방식
  
  
• Consistent모드를 이해하라면 SCN에 대한 이해가 필요, 오라클은 시스템에서 마지막 커밋이 발생한 시점정보를 'SCN(System Commit Number)'라는 Global변수값으로 관리
• 이값은 기본적으로 각 트랜잭션이 커밋할때마다 1씩증가하지만, 오라클 백그랑운드 프로세서에 의해서도 조금씩 증가
• 또한 오라클은 각블록이 마지막으로 변경된 시점을 관리하기위해 블록 헤더에 SCN을 기록하는데 이를 '블록 SCN'
• 모든 쿼리는 Global변수인 SCN값을 먼저확인하고서 읽기작업시작, 이를 '쿼리 SCN'
• Consistent모드는 쿼리 SCN과 블록SCN을 비교함으로써 쿼리 수행 도중에 블록이 변경됐는지를 확인하면서 데이터를 읽는 방식
• 데이터를 읽다가 블록SCN이 쿼리 SCN보다 더큰 블록을 만나면 복사본 블록을 만들고 Undo데이터를 적용함으로써 쿼리가 시작된 시점으로 되돌려서 읽는다.
• Undo 데이터가 다른 트랜잭션에 의해 재사용됨으로써 쿼리시작 시점으로 되돌리는 작업을 실패할때
  'Snapshot too old(ORA-01555)'에러가 발생
• SELECT문은 항상 CONSISTENT모드로 데이터를 읽는다. 반면 수정삭제변경은 Consistent모드로 레코드를 찾고, Current모드로 데이터변경한다. Consistent모드로 DML문 시작된시점에 데이터블록찾고, 다시 Current모드로 원본 블록을 찾아서 갱신

1.7 LOCK과 DML성능

• Lock을 필요이상 자주, 길게 사용하거나 레벨을 높일수락 DML성능은 느려진다
• Lock을 너무 적게, 짧게 사용하거나 필요레벨 이하로 낮추면 데이터 품질이 나빠진다.
• 품질과 성능은 트레이드 오프관계여서 어렵다, 세심한 동시성 제어가 필요
• 동시성 제어(Concurrency Control) : 동시에 실행되는 트랜잭션 수를 최대화(고성능)하면서 입력/수정/삭제/검색시 데이터 무결성을 유지(고품질)하기 위해 노력하는것.

커밋과 DML성능

• DML이 Lock에 의해 블로킹된 경우, 커밋은 DML성능과 직결된다
• DML을 완료할 수 있게 Lock을 푸는 열쇠가 바로 커밋
• 모든 DBMS가 Fast Commit을 구현, 구현방식은 서로 다르지만, 갱신한 데이터가 아무리 많아도 커밋만큼은 빠르게 처리한다는 점은 같다.
• 커밋은 결코 가벼운작업이아니다 커밋의 내부 매커니즘
  1. DB 버퍼캐시
  - DB접속한 사용자를 대신해 모든 일을 처리하는 서버 프로세스는 버퍼캐시를 통해 데이터를 읽고 쓴다.
  - 버퍼캐시에 변경된 블록(Dirty블록)을 모아 주기적으로 데이터 파일에 일괄기록하는 작업은 DBWR(Database Writer)프로세스가 맡는다.
  2. Redo로그버퍼
  - 버퍼캐시는 휘발성, DBWR프로세스가 Dirty 블록들을 데이터파일에 반영할때까지 불안한상태
  - but 버퍼캐시에 가한변경을 Redo로그에도 기록해두었으므로 문제없음
  버퍼캐시데이터 유실되더라도 Redo로그 이용해 언제든 복구가능
  - but Redo로그도 파일, Append방식기록이라도 디스크 I/O는 느리다
  - Redo로깅 성능문제 해결을 위해 로그버퍼이용 (Redo로그파일에 기록하기전 로그버퍼에 먼저 기록)
  - 로그버퍼에 기록한 내용은 나중에 LGWR(Log Writer)프로세스가 Redo로그 파일에 일괄 기록
  3. 트랜잭션 데이터 저장과정
  
  1) DML문을 실행하면 Redo 로그버퍼에 변경사항을 기록한다.
  2) 버퍼블록에서 데이터를 변경(레코드 추가/수정/삭제)한다. 버퍼캐시에서 찾지못하는 블록은 데이터파일에서 읽는 작업부터 한다.
  3) 커밋한다.
  4) LGWR 프로세스가 Redo로그버퍼 내용을 로그파일에 일괄 저장한다.
  5) DBWR 프로세스가 변경된 버퍼블록들은 데이터파일에 일괄 저장한다.
  - Write Ahead Logging
  - 오라클은 데이터 변경전 항상 로그부터 기록
  - DBWR프로세스가 Dirty블록을 디스크 기록하기전에 LGWR프로세스가 Redo로그파일에 로그를 먼저기록
  - 메모리 버퍼캐시가 휘발성이어서 Redo로그를 남기는데, Redo로그마저 휘발성 로그버퍼에기록한다면 트랜잭션 데이터를 지킬수있느냐? (커밋한 트랜잭션의 영속성 보장문제)
  - DBWR과 LGWR프로세스는 주기적으로 Dirty 블록과 Redo로그버퍼를 파일에 기록, LGWR은 커밋 발생시에도 파일에 기록.
  - 적어도 커밋시점에는 Redo로그버퍼 내용을 로그파일에 기록한다 > Log Force at Commit

4. 커밋 = 저장버튼
   - 문서 작성시 워드프로세서는 사용자입력내용을 메모리에 기록, 저장부턴누르면 디스크파일에 저장
   - 데이터베이스 트랜잭션을 문서 작업에 비유하면, 커밋은 문서작업도중에 '저장' 버튼을 누르는것
   - 서버프로세스가 그때까지했던 작업을 디스크에 기록하라는 명령어
   - 트랜잭션을 필요이상 길게정의하는것도, 너무자주 커밋하는것도 문제, 오래 커밋하지 않은 데이터를 계속 갱신하면 Undo공간 부족으로 시스템장애가 발생할 수 도 있다

2. 데이터베이스 Call과 성능

데이터베이스 Call

• 트레이스 리포트에서 Call통계 부분만 발췌
  
• SQL 실행단계
  • Parse Call : SQL파싱과 최적화 수행단계, SQL과 실행계획을 라이브러리캐시에서 찾으면 생략가능
  • Execute Call : SQL실행단계, DML은 이단계에서 모든과정이 끝나고, SELECT 문은 Fetch단게 거침
  • Fetch Call : 데이터를 읽어 사용자에게 결과집합 전송, SELECT문에만있음, 전송할 데이터가 많으면 여러번 생
• User Call : 네트워크 경유해 DBMS외부로부터 인입되는 Call
  3-Tier 아키텍처에서 User Call은 WAS(또는 AP서버)에서 발생하는 Call
• Recursive Call : DBMS내부에서 발생하는 Call, SQL파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, PL/SQL로 작성한 사용자 정의 함수/프로시저/트리거에 내장된 SQL을 실행할 때 발생하는 Call
• User Call이든 Recursive Call이든 SQL실행할때마다 Parse, Execute, Fetch단계를 거친다
• 데이터베이스 Call이 많으면 성능은 느림, 특히 네트워크경유하는 User Call은 성능영향 매우크다

절차적 루프 처리

• 100만건의 테이블에서 PL/SQL프로그램에서 테이블을읽어 100만번 루프를돌아 건건이 Recursive Call 수행
• 위와조건은 같지만, 커밋을 루프안으로 옮겨서 건건마다 commit 실행
• 위와조건은 같지만, 커밋을 루프안으로 옮겨서 조건을 주어 10만건마다 commit수행
• 자바에서 루프를 돌면서 User Call수행
• 위 4가지 상황에서 어떤것이 가장 성능에 유리할지 고민

One SQL의 중요성

• 절차적루프처리의 100만건 테이블(source)을 아래와같이 One SQL로 insert 할경우 비교할수없을 만큼의 성능향상 효과가있다.

  insert into target
  select * from source;

• 업무 로직이 복잡하면 절차적으로처리해야겠지만 그렇지않으면 가급적 One SQL로 구현하도록 노력
• One SQL구현 구문 활용법
  • Insert Into Select
  • 수정가능 조인 뷰
  • Merge 문