1. 객체 튜닝

테이블, 인덱스, 세그먼트에 관련한 사항이 대상이다.

  • 객체는 성능을 고려하여 설계되어야 한다.

  • 저장 장치를 이루는 블록, 확장 영역, 세그먼트에 관련된 사항을 튜닝한다.

  • 인덱스는 삭제, 갱신으로 스큐 현상이 심한 경우는 재구성 작업이 필요하다.

  • IO 병목이 발생하지 않게 물리적인 배치를 실시한다.

2. 인스턴스 튜닝

DBMS 인스턴스는 메모리 부분과 프로세스가 튜닝 대상이다. 이는 DBMS 종속적인 요소가 많으므로 DBMS별 확인이 필요하다.

  • 메모리

  • 메모리는 Buffer 캐시, library 캐시 등의 히트율(HIT ratio)에 의해서 평가하여 조정한다.

  • sort area, hash area는 스와핑(swapping) 발생 여부에 따라 사이즈를 결정한다. 특정한 대형 작업은 작업을 실시하는 세션에서 조정하여 작업을 실시한다.

  • 프로세스

  • 대부분의 DBMS가 다중 프로세스 시스템이고 필요에 따라서 추가적인 프로세스 가동이 가능하다.

  • Latch 경합

  • 트랜잭션 처리를 위한 경합이 발생한다.

  • 객체 생성이나 변경 등으로 경합이 발생할 수 있다.

3. 환경 튜닝

환경 튜닝은 하드웨어나 운영체제 관점에서의 튜닝이다. CPU, 메모리, 디스크 I/O, 네트워크가 환경 튜닝 대상이다. 환경 튜닝은 기본값으로 설정되어 있는 경우를 제외하고 많은 성능의 향상을 기 대하기 어렵다. 하드웨어 성능이나 구성에 따라 환경 설정된 상태에서 운영하기 때문이다.

예외적으로 고가용성을 위한 시스템 구성, RAID의 구성, 버전에 따른 패치 적용이 정상적이지 않을 경우에 성능에 결정적인 영향을 줄 수 있으므로 확인이 필요하다.

  • CPU

  • 튜닝 대상이라기보다는 성능을 평가하기 위한 기준으로 CPU 사용율(Utilization)을 평가한다.

  • SAR(System Activity Report)로 모니터링 했을 때 CPU 사용이 %usr > %sys > %wio 순으로 되는 것이 바람직하다.

  • %idle이 일반적으로 20~30%을 유지하는 것이 바람직하며, 0%인 상태가 지속적으로 유지되면 증설을 고려한다.

  • Sun - ps, HP - top 또는 glance, IBM - monitor 등의 프로세스별 모니텅링이 가능하다.

이와같은 도구는 문제 프로세스를 OS 차원에서 확인할 수 있다.

  • 메모리 튜닝

  • Paging(page-in, page-out)과 프로세스 단위의 Paging 현상인 Swapping 발생 상태를 확인한다.

  • DBMS를 포함한 사용자 사용 메모리 크기가 전체 크기의 40 ~ 60%를 유지하는 것이 바람직하다.

  • I/O 튜닝

  • 데이터베이스 병목은 I/O에 의해서 발생한다.

  • 물리적인 디스크와 디스크 채널을 분산하므로 성능을 개선할 수 있다.

  • 읽기/쓰기 작업에 따른 분산이 필요하다

  • Cook Device보다는 Raw Device가 IO 성능에 유리하다.

  • 네트워크 튜닝

장 요약

  • 제1절 성능 개선 방법론
    • 성능 개선 작업은 분석, 이행, 평가순으로 진행한다.
    • 성능 개선을 하기 위해서는 트레이스, 실행 계획, 서버 상태를 확인할 지원 도구가 필요하다.
    • 성능 개선 목표는 처리 능력, 처리 시간, 응답 시간, 로드 시간 등이 있다..
  • 제2절 조인(Join)
    • 대표적인 조인에는 Nested-loop 조인, Sort-merge 조인, Hash 조인이 있다.
    • Nested-loop 조인은 드라이빙 조건의 범위가 좁으면 항상 성능이 양호하다.
    • Nested-loop 조인은 드리이빙 조건의 범위가 넓더라도 확인 조건의 검색 범위가 넓으면 성능이 양호하다.
    • Sort-merge 조인은 Inner 집합의 조인 속성에 인덱스가 없을 때 Simple Nested-Loop 조인보다 성능이 우수하다.
    • Sort-merge 조인은 정렬에 대한 부하가 많이 발생하므로 대용량 처리 시 수행 속도가 저하될 수 있다.
    • 해쉬 조인은 대용량의 데이터 조인 시에 Sort-merge 조인이나 Nested-Loop 조인보다 더 좋은 성능을 나타낸다. 특히, 작은 집합과 대용량 데이터의 조인 시에 아주 좋은 성능을 나타낸다.
  • 제3절 애플리케이션 성능 개선
    • 온라인 프로그램은 응답 시간 기준으로, 배치 프로그램은 차리 시간 기준으로 최적화되어야 한다.
    • 온라인 프로그램은 실행 계획을 최적화하기 위해 인덱스를 이용한 액세스 경로 개선, SQL 재작성으로 실행 계획 개선, 힌트를 이용한 실행 계획 변경 등의 방법을 사용한다.
  • 제4절 서버 성능 개선
    • 서버 성능 개선은 인스턴스, 데이터베이스, 네트워크 부분으로 구성된다.
    • 인스턴스는 메모리, 프로세스를 대상으로 최적화한다.
    • 데이터베이스는 데이터 저장을 위한 블록(Blocks), 익스텐트(Extents), 세그먼트(Segments), 데이터 파일을 대상으로 성능 개선을 실시한다.

연습문제

문제 1. 다음과 같은 인덱스 조건에서 아래의 SQL이 수행된다고 가정할 때 다음 중 예상 플랜에 대한 설명으로 틀린 것은?(INDEX : 주문일자 + 제품)

select *
  from ORDERITEM
 where 주문일자 BETWEEN '20060501' and '20060510'
   and 제품 IN (select 제품 from PRODUCT where 제품명 like 'SM%)
   and 금액 > 1000000;
  • ① 서버 쿼리는 메인 쿼리의 항목을 가지지 않았으므로 먼저 수행하여 ‘제품’ 코드를 제공할 수도 있다.
  • ② 위 SQL은 서버 쿼리가 먼저 수행되는 것보다 체크 조건으로 사용하는 것이 좋다.
  • ③ 서버 쿼리가 먼저 수행되면 옵티마이저는 공급된 제품 수만큼의 ‘주문일자’ 범위를 반복 검색한다.
  • ④ 서버 쿼리가 먼저 수행되는 경우에는 /*+ USE CONCAT */ 힌트를 사용하여 제공되는 제품 수만큼 실행 계획이 분리될 수 있도록 해야 한다.

    서버 쿼리가 먼저 수행되더라도 서브쿼리에서 제공되는 결과 건수를 알수가 없으므로 /*+ USE CONCAT */ 힌트를 사용하여 실행계획을 분리할 수는 없다.

문제 2. 다음은 TKPROF를 수행하여 집계된 OVERALL TOTALS을 보고 분석한 내용이다.다음 중 분석 내용으로 틀린 것은?의

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
Parse 334703 240.06 472.64 1141 4548 2801 0
Execute 1225282 1413.52 3885.84 114650 12349154 4415610 403674
Fetch 1678364 19460.79 40252.73 9442664 368882755 379063 4091994
total 3238349 21114.37 44611.21 9558455 381236457 4797474 4495688
  • ① CPU Time과 ELAPSE 타임의 편차가 심각한 것으로 보아 I/O에 대한 병목이나 시스템 가에 대한 오버헤드가 존재하고 있는 것을 알 수 있다.
  • ② 1 Row당 평균 Disk I/O는 2.1 Block이 발생하고 있는 것으로 보아 대체적으로 옵티마이징 전략에 많은 문제가 있는 것으로 판단된다.
  • ③ BLOCK I/O의 비효율을 분석해 본 결과 불필요한 DB Block I/O가 많이 나타나고 있어데이터베이스 버퍼 캐시의 사이즈를 증가시켜야 한다.
  • ④ Parse, Execute, Fetch의 비율로 보아 대부분의 SQL이 Hold Cursor 없이 루프 내에서 반복적으로 수행되었다.

    BLOCK I/O의 비효율을 분석할 때 메모리 사용을을 많이 분석하는데 97% 이상으로 나타난 것으로 보아 불필요한 DB Block I/O는 없는 것으로 판단된다.

문제 3. 다음 중 Nested Loop 조인의 원리에 대한 설명으로 틀린 것은 ?

  • ① 조인 후 CHECK 되는 조건은 처리 범위가 넓을수록 수행 속도가 저하된다.
  • ② 자신에게 주어진 상수 값에 의해 스스로 처리 범위를 줄이는 것이 아니라 선행 집합에서 처리된 결과에 따라 처리 범위가 결정된다.
  • ③ 먼저 어떤 테이블의 처리 범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다.
  • ④ 조인 속성의 인덱스 유무와 액세스 방향에 따라 수행 속도에 많은 차이가 발생된다.

    Nested Loop 조인에서 조인 후 CHECK 되는 조건의 처리 범위가 넓을수록 수행 속도는 향상된다.

문제 4. 다음 중 절차적인 처리 방식의 비효율에 대한 설명으로 틀린 것은?

  • ① 수행되는 SQL이 LOOP 내에서 반복적으로 수행되는 구조이므로 DBMS Call이 과도하게 발생하게 된다.
  • ② 업무 규칙 변경 시 프로그램 구조 수정이 불가피하다.
  • ③ 단위 SQL이 반복적으로 수행되는 구조이므로 랜덤 I/O 발생이 감소한다.
  • ④ DBMS는 SQL 단위로 최적화를 수행하므로 개별적인 SQL은 개선이 가능하지만 전체의 최적화는 불가능하다.

    단위 SQL이 반복적으로 수행되는 구조의 경우 랜덤 I/O 발생이 증가한다.

문제 5. CPU Utilization은 시스템의 성능을 평가하기 위한 기준으로 많이 활용된다. 다음 중 CPU Utilization에 대한 설명으로 틀린 것은?

  • ① 주기별 %idle 비율은 분석의 주요 관점으로 Kidle 비율이 낮게 나타날 수 있는 원인은 단순히 cpu의 용량 부족 이외에도 %wio(waiting for block I/O)와 관련이 있을 수 있다.
  • ② %idle은 일반적으로 20~30%을 유지하는 것이 바람직하다.
  • ③ %usr는 사용자 모드의 CPU 사용 비율이고, %sys는 시스템 모드의 CPU 사용 비율을 의미한다.
  • ④ %wio는 CPU가 I/O를 발생시키기 위해 사용한 CPU 사용률을 의미한다.

    %wio는 process의 block I/O Waiting으로 인한 idle 비율을 의미한다.

업데이트: