애플리케이션 설계와 애플리케이션 튜닝은 가장 큰 성능 이점을 제공할 것입니다. 이러한 작업을 실행하는 구문이 적절하게 작성되지 않았을 경우, 데이터가 선택된 방법과 선택된 데이터의 양은 애플리케이션의 성능에 심각한 영향을 미칩니다.

데이터베이스 관리자 작업
보통 애플리케이션 개발자가 애플리케이션 개발과 SQL 문의 작성을 담당하기 때문에, 데이터베이스 관리자(DBA)는 애플리케이션 튜닝에 직접 관여하지 않을 수도 있습니다. 그러나, DBA는 잘못 작성된 SQL 문이 데이터베이스 환경에 미치는 영향에 대해서 알고 있을 필요가 있으며, 애플리케이션 튜닝 작업을 돕고 비효율적인 SQL 문을 즉시 식별할 수 있어야 합니다.

Oracle8에서는, 다음 두 가지의 옵티마이저 모드를 선택할 수 있습니다:

규칙 기반 최적화
이 모드에서, 서버 프로세스는 질의를 조사하여 데이터로의 액세스 경로를 선택합니다. 이 옵티마이저는 액세스 경로 서열에 대한 완벽한 세트의 규칙을 갖고 있습니다.

경험있는 오라클 개발자는 종종 이들 규칙을 매우 잘 이해하고 있기 때문에 SQL을 튜닝할 수 있습니다.

규칙 기반 옵티마이저는 문장의 구문을 사용하여 사용될 실행 계획을 정합니다.

원가 기반 최적화
이 모드에서, 옵티마이저는 각 문장을 검사하여 데이터로의 모든 가능한 액세스 경로를 확인합니다. 그런 다음, 각 액세스 경로의 자원 비용을 계산하여 가장 비용이 적게 드는 경로를 선택합니다. 원가 계산은 주로 논리적 읽기의 수를 기초로 합니다.

원가 기반 옵티마이저는 통계 중심으로, SQL 문과 관련된 객체에 대해 생성된 통계를 사용하여 가장 효과적인 실행 계획을 정합니다. SQL 문 내의 어떠한 객체라도 객체에 대해 생성된 통계가 있다면, 원가 기반 옵티마이저가 사용될 것입니다.

오라클 사는 특히 새로운 애플리케이션이 병렬 질의를 사용할 경우, 새로운 애플리케이션에 대해 이 옵티마이저 모드를 사용할 것을 권장합니다.

주: 규칙 기반 옵티마이저에 의해 사용되는 서열시스템에 관한 추가 정보에 대해서는 Oracle8 Concepts, Release 8.0 manual 부분을 참조하십시오.


옵티마이저 모드는 다음에서 설정될 수 있습니다:

  • 인스턴스 레벨, OPTIMIZER_MODE 파라미터 사용
  • 세션 레벨, OPTIMIZER_GOAL 설정
  • 문장 레벨, 힌트 사용

인스턴스를 재시작해야 하기 때문에, DBA가 OPTIMIZER_MODE를 인스턴스 레벨에서 설정합니다. 일반적으로 애플리케이션 개발자는 SQL 문에서 힌트를 사용하는 것은 물론OPTIMIZER_MODE를 세션 레벨에서 설정할 능력을 갖게 될 것입니다.

OPTIMIZER_MODE 파라미터
디폴트 값은 CHOOSE입니다. 이것은 테이블에 대해 통계를 사용할 수 있을 경우, 옵티마이저가 원가 기반 모드를 사용한다는 것을 의미합니다. 그렇지 않으면, 옵티마이저는 규칙 기반 최적화를 사용합니다.

다른 가능한 값은 RULE, FIRST_ROWS, ALL_ROWS 입니다. 마지막 두개는 원가 기반 최적화를 사용하는 다른 방법들을 나타냅니다. FIRST_ROWS는 즉각적인 응답시간을 최소화하고(아마도 전체 응답시간을 희생하고서), ALL_ROWS는 총 응답시간을 최소화합니다.

OPTIMIZER_GOAL 옵션
개발자는 ALTER SESSION 명령을 사용하여 이 옵션을 설정할 수 있습니다.
   ALTER SESSION SET OPTIMEZER_GOAL = value
가능한 값은 OPTIMIZER_MODE 파라미터에 대한 값과 같습니다.

옵티마이저 힌트
다음과 같이, 문장에 힌트를 코딩할 수 있습니다.
   SELECT /*+ FIRST_ROWS */  * FROM scott.emp;
가능한 옵티마이저 힌트는 RULE, FIRST_ROWS, ALL_ROWS입니다.

주의: 모든 가능한 힌트의 목록에 대해서는 Oracle8 Tuning, Release 8.0 튜닝 부분을 참조하십시오.

스타 질의는 “스타” 스키마로 알려진 것을 중심으로 종종 데이터 웨어하우스 애플리케이션에서 사용됩니다. 사실(fact) 테이블과 룩업(lookup) 테이블이 적절하게 구조화되었을 경우, 원가 기반 옵티마이저는 스타 질의를 인식하여 스타 질의 실행 경로를 설정합니다.

스타 질의 특성

이 예에서, 사실 테이블은 모든 룩업 테이블의 기본키(Primary key)로 이루어진 연결(concatenated) 키를 포함합니다.

다음 질의는 스타 질의 스키마를 이용합니다:

  SELECT sum(dollars)
  FROM fact_table, time_table, product_table, market_table
  WHERE market_table.stat = 'New York' AND
  product_table.brand = 'Mybrand' AND
  time_table.year = '1998' AND
  time_table.month = 'March' AND
  product_table.key1 = fact_table.key1 AND
  market_table.key2 = fact_table.key2 AND
  time_table.key3 = fact_table.key3;

관련 파라미터들
STAR_TRANSFORMATION_ENABLED 파라미터는 원가 기반 질의 변형이 스타 질의에 적용되어야 하는지 여부를 지정합니다. 디폴트 값은 TRUE입니다. 이 파라미터는 ALTER SESSION 명령을 사용하여 동적으로 설정될 수 있습니다.

해시 결합은 한 테이블이 다른 테이블보다 현저하게 큰 경우 두개의 테이블을 조인하기 위한 효과적인 메커니즘입니다. 예를 들어, 수천개의 행을 포함하는 직원 테이블은 행 수가 적은 부서 테이블과 조인됩니다.

해시가 구축되고 나면, 가능하다면 메모리에 저장되어, 수행되어야 하는 테이블 스캔의 수를 감소시켜 조인 작업을 훨씬 더 효과적으로 만듭니다.

 

위의 예에서:

  1. 해시 결합은 EMP 테이블과 DEPT 테이블 간에 수행됩니다. 그 결과 각 테이블을 전체 테이블 스캔합니다.
  2. DEPT 테이블이 더 작기 때문에, DEPT 테이블을 기본으로 하여 해시 테이블이 구축된 다음 메모리에 놓입니다.
  3. 그런 다음, 두 테이블 중에서 더 큰 테이블(EMP 테이블)이 해시 테이블을 시험하는데 사용됩니다.

관련 파라미터
해시 결합에 중요한 3개의 파라미터들은 다음과 같습니다:

SQL 문이나 PL/SQL 모듈의 성능을 평가하기 위하여 많은 진단 툴들을 이용할 수 있습니다. 각 툴은 개발자나 DBA에게 다양한 정보를 제공합니다.

추적을 사용하지 않고 SQL *Plus에서 EXPLAIN 문을 사용할 수 있습니다.

지원되는 utlxplan.sql 스크립트를 사용하여 PLAN_TABLE이라는 테이블을 생성할 필요가 있습니다. 가장 많은 목적에 유용한 열은 OPERATION, OPTIONS, OJBECT_NAME입니다.

질의에 대한 계획을 설명하기 위해서는, 다음 구문을 사용하십시오:

   EXPLAIN PLAN FOR
   SELECT ...

그런 다음, PLAN_TABLE을 질의하여 실행 계획을 검사하십시오.

PLAN_TABLE은 그 순간 문장을 실행시키기 위하여 선택할 경우 문장이 어떻게 실행되는가를 나타냅니다. 문장을 실행시키기 전에 변경(예를 들어, 인덱스 생성)할 경우 실제 실행은 다르게 수행된다는 것을 명심하십시오.

또한, 계획 설명에서 statement_id를 사용하지 않는다면, 다른 실행 계획을 생성하기 전에 PLAN_TABLE을 제거하기를 원할 것입니다.

plan_table에서 실행 계획을 조회하기 위해서는 다음 질의를 실행시키십시오:

   select id, operation, options, object_name, position from
   plan_table;

이 예제에서, PLAN_TABLE로부터 오직 3개의 열만 선택되는데, 이들은 다음과 같습니다:

EXPLAIN PLAN 출력에서 부모(parent) 프로세스를 결정하기 위하여, PLAN_TABLE의 ID와 PARENT_ID를 모두 선택하십시오.

실행 계획의 해석
실행 계획의 각 단계는 데이터베이스로부터 행을 불러오거나 하나 이상의 다른 단계로부터의 입력으로서 열을 받아들입니다. 실행 계획은 아래에서 위로 상향식으로 읽혀집니다.

실행 계획의 비용은 실행 계획을 사용하는 문장을 실행하는데 필요한 예상 경과 시간에 비례합니다.

실행 계획은 질의에 대해 인덱스를 생성하고 사용하는데 있어 이점이 될 것이 무엇인지 결정하는데 도움이 될 수 있습니다. 현명한 튜닝으로 현저한 성능 향상을 이룰 수 있습니다. SQL Trace나 Autotrace를 사용하여 잠재적으로 향상시킬 수 있는 영역을 확인하십시오.

주의: PLAN_TABLE의 열에 대한 추가 정보에 대해서는 Oracle8 tuning, Release 8.0 을 참조하십시오.

여러 단계 중 일부 순서는 SQL 문 성능을 적절하게 진단하는데 필수입니다.

  1. SQL 문 성능을 진단하는데 있어 첫번째 단계는 적합한 초기화  파라미터를 보장하는 것입니다. 이들 파라미터는 인스턴스 레벨에서 설정될 수 있습니다. 또는, 일부 파라미터의 설정은 시스템 레벨과 세션 레벨에서 지원됩니다.
  2. SQL 추적은 인스턴스 레벨이나 세션 레벨에서 시동되어야 합니다. 일반적으로, 세션 레벨에서 시동될 경우, 전체적인 성능 관점에서 이루어지는 것이 더 낫습니다.
  3. 진단하고자 하는 애플리케이션이나 SQL 문을 실행시키십시오.
  4. SQL 추적을 종료하십시오. 이것은 운영체제 레벨에서 추적 파일을 적절하게 종료시키는 데에  필요합니다 (그렇지 않을 경우, TKPROF 유틸리티가 유효한 출력정보를 제공하지 않을 것입니다.)
  5. TKPROF를 사용하여 추적 세션동안 생성된 추적 파일을 포맷하십시오. 출력 파일이 포맷되지 않으면, 결과를 해석하는데 매우 어려울 것입니다.
  6. TKPROF로부터 출력된 정보를 사용하여 SQL 문의 성능을 진단하십시오.

init.ora 파일에서 2개의 파라미터는 SQL 추적 기능으로부터 출력 파일의 크기와 목적지를 제어합니다.

   max_dump_file_size = n
   (default 500, measured in bytes if K or M is specified, otherwise
   the number represents operation system blocks)

SQL 추적기능의 출력파일의 크기가 정의된 값에 초과되었을 때, 추적 정보는 더 이상 추적 파일에 기록될 수 없습니다. 다음과 같은 메시지가 파일의 마지막에 나타납니다:
   *** Trace file full ***
   user_dump_dest = directory

타이밍 정보를 얻기 위하여 제3의 파라미터를 설정해야 합니다:

   timed_statistics = TRUE

타이밍 통계는 100분의 1초로 표현됩니다. 이것은 100분의 1초 또는 그 이하의 시간이 걸리는 작업은 정확한 시간이 계산되지 않을 수도 있다는 것을 의미합니다. (특히, 빠르게 실행되는 간단한 질의에 대해서) Oracle8에서, TIMED_STATISTICS 파라미터는 ALTER SESSION 명령을 사용하여 세션 레벨에서 동적으로 설정될 수 있습니다.

SQL Trace는 인스턴스 레벨이나 세션 레벨에서 다른 방법들을 사용하여 활성화 또는 비활성화로 될 수 있습니다.

인스턴스 레벨
SQL_TRACE 파라미터를 인스턴스 레벨에서 설정하는 것은 추적을 활성화하는 한 방법입니다. 그러나, 그러기 위해서는 인스턴스를 종료한 다음 추적이 더 이상 필요하지 않을 때 재시작해야 합니다. 이것은 인스턴스에 대한 모든 세션이 추적되기 때문에, 성능면에서 현저한 타격을 줍니다.

세션 레벨
세션 레벨 추적은 특정 세션을 추적할 수 있기 때문에 전체적인 성능면에서 받는 타격을 줄입니다. SQL 문을 활성 또는 비활성으로 하는 데에는 다음의 3가지 방법이 있습니다:

TKPROF를 사용하여 추적 파일을 읽을 수 있는 출력형태로 포맷하십시오.

Tkprof tracefile outputfile [sort=option] [print=n] [explain=username/password] [insert=filename] [sys=NO] [record=filename] [table=schema.tablename]

추적 파일은 USER_DUMP_DEST 파라미터에 의해 지정된 디렉토리에서 생성되며, 출력 결과는 출력파일명에 의하여 지정된 디렉토리에 위치합니다.

TKPROF 옵션

 옵션

 설명

 TRACEFILE

 추적 출력 파일명

 OUTPUTFILE

 포맷될 파일명

 SORT=option

 문장을 정렬하는 순서

 PRINT=n

 처음 n개의 문장 프린트

 EXPLAIN=user/password

 지정된 사용자명으로 EXPLAIN PLAN 실행

 INSERT=filename

 INSERT 문 생성

 SYS=NO

 사용자 sys로서 실행되는 순환적(recursive) SQL 문 무시

 AGGREGATE=[Y|N]

 AGGREGATE=NO로 설정한 경우, TKPROF는 동일한 SQL 텍스트의 복수 사용자 수를 총합하지 않음

 RECORD=filename

 추적 파일에서 발견된 문장 기록

 TABLE=schema.tablename

 실행 계획을 특정 테이블에 배치 (디폴트PLAN_TABLE 대신)

 
모든 이용가능한 옵션과 출력결과의 목록을 얻기 위하여 운영체제에서 tkprof를입력할 수 있습니다.

주의: 정렬 옵션은 다음과 같습니다.

 정렬 옵션

 설명

 Prscnt, execnt, fchcnt

 구문 분석, 실행, 패치가 호출된 횟수

 Prscpu, execpu, fchcpu

 구문 분석, 실행, 패치의 CPU 사용 시간

 Prsela, exela, fchela

 구문 분석, 실행, 패치의 총소요시간

 Prsdsk, exedsk, fchdsk 

 구문 분석, 실행, 패치 동안 디스크 읽기 횟수

 Prsqry, exeqry, fchqry

 구문 분석, 실행, 패치 동안 consistent 읽기 버퍼의 개수

 Prscu, execu, fchcu 

 구문 분석, 실행, 패치 동안 current 읽기 버퍼의 개수

 Prsmis, exemis

 구문 분석, 실행 동안 라이브러리캐시 miss 횟수

 Exerow, fchrow 

 실행, 패치 동안 처리된 행의 수

 Userid

 커서를 구문 분석한 userid

 

 통계

 의미

 Count   

 문장의 구문이 분석되거나 실행되고 페치 호출의 수가 문장에 대해 실행되는 회수

 CPU

 간 단계에 대한 처리 시간, 초로 표시 (문장이 공유 풀에서 발견되면,  구문분석 단계에 대한 시간은 0입니다.)

 Elapsed 

 경과 시간, 초로 표시 (다른 프로세스가 경과 시간에 영향을 미치기 때문에, 이 통계는 일반적으로 도움이 되지 않습니다. )

 Disk 

 데이터베이스 파일에서 읽혀진 물리적 데이터 블록 (이 통계는 데이터가 버퍼링되면, 꽤 낮을 수도 있습니다.)

 Query 

 일관된 읽기를 위하여 검색된 논리적 버퍼 (일반적으로 SELECT 문에 대해)

 Current 

 현재 모드에서 검색된 논리적 버퍼 (일반적으로 DML 문에 대해)

 Rows   

 다른 문장에 의하여 처리되는 열 (SELECT문의 경우,  이 통계는 페치 단계에 대해 표시되고, DML 문의 경우 실행 단계에 대해 표시됩니다)


 Query와 Current의 합계는 액세스되는 논리적 버퍼의 총 합계입니다.
 
call    count    cpu    elapsed   disk   query   current  rows
----    -----    ----   -------   ----   -----   -------  ----
Parse       2    0.11      0.22     00       0         2     0   
Execute     4    0.00      0.00     00       0         4     0
Fetch     428    0.00      0.06    600     806       808  6400
----    -----    ----   -------   ----   -----   -------  ----
total     434    0.11      0.28    600     806       814  6400

문장의 성능 검사

TKPROF로부터의 다른 출력 결과

AUTOTRACE는 SQL Trace 대신 사용될 수 있습니다. AUTOTRACE를 사용하여 얻는 이점은 추적 파일을 포맷할 필요가 없으며, AUTOTRACE가 SQL 문에 대한 실행계획을 자동으로 표시한다는 것입니다.

그러나, AUTOTRACE는 설명계획이 문장의 구문만 분석하는 곳에서 문장의 구문을 분석하고 실행합니다.

AUTOTRACE 사용을 위한 단계는 다음과 같습니다:

  1. utlxplan.sql 스크립트를 사용하여 PLAN_TABLE 생성.
  2. plustrce.sql 스크립트를 실행시켜 사용자에게 추적역할 부여.  이것은  V$ 뷰에 대한 선택 권한을 부여합니다.
  3. 만약 사용자가 DBA role을 가지고 있지 않다면 plustrce role을 부여합니다.
  4. Trace를 On으로 설정:
    - AUTOTRACE ON을 설정하면, 결과 세트, 설명 계획, 목록 통계를 산출합니다.
    - AUTOTRACE TRACEONLY를 설정하면, 설명 계획과 통계를 나타냅니다.

비효율적인 SQL 문의 증상
SQL문은 여러 가지 이유로 인하여 잘못 실행됩니다. 가장 일반적인 이유 중 일부는 다음과 같습니다.

옵티마이저가 인덱스를 사용할 수 없다
옵티마이저가 비록 더 효율적이지만, 인덱스를 사용할 수 없는 상황이 많이 있습니다. SQL 문을 검사하여 인덱스를 사용하도록 재작성하여야 합니다.

문장에 트리구조(Tree Walk)가 포함되어 있다
트리구조는 CONNECT BY절을 사용하여 계층형식으로 저장된 테이터를 검색합니다. 트리구조를 사용하여야 할 경우, START WITH와 CONNECT BY 절에 있는 열에 인덱스를 설정하십시오.

문장에 그룹 함수가 포함되어 있다  
다음과 같은 2가지 문제가 있을 수 있습니다:

문장에 DISTINCT 키워드가 포함되어 있다
DISTINCT를 사용할 때, 데이터는 정렬됩니다. 문장이 많은 행을 선택할 경우, 서버는 임시 세그먼트를 생성하여 개별 정렬 실행을 처리할 수도 있습니다. 이것은 I/O 및 처리 오버헤드를 야기시키기 때문에, 이것을 피해야 합니다.

문장이 복합 뷰를 기본으로 하고 있다.
뷰를 사용할 때, 뷰를 질의와 통합하여 처리되는 행의 수를 제한하고자 할 것입니다. 또한 뷰의 일부가 되는 열을 검색하기 위하여 질의가 테이블을 불필요하게 액세스하지 않도록 보장해야 합니다. 그렇지 않으면, 테이블을 직접 질의하여 열을 검색할 것입니다.

대체 질의
원하는 결과를 제공하는 SQL 문은 여러 개가 있을 수 있다는 사실을 명심하십시오. 각 문장은 다른 액세스 경로를 사용하여 다르게 수행될 수 있습니다. DBA와 개발자 모두 다른 질의들을 사용하여 동일한 결과를 산출할 수 있는 방법에 익숙해야 합니다. 

애플리케이션 튜닝에는 또한 패키지, 프로시저, 트리거를 이용하는 방법이 포함되어 있습니다. 각 유형의 PL/SQL 객체가 오라클 내에 컴파일된 형태로 저장되어 있다는 사실 때문에 사용방법을 튜닝할 필요가 없는 것은 아닙니다.

객체는 구문이 분석된 다음, 공유 풀에 캐쉬되어 구문분석된 버전이 재사용될 수 있습니다. 그러나, 객체가 공유 풀에서 오래되어 밀려나갈 경우, 실행될 때 다시 구문 분석을 해야 되기 때문에, 성능에 타격을 주게 됩니다.

객체를 공유 풀에 고정할 수 있는데, 이것은 오래되어도 삭제되지  않도록 보장하기 때문에, 자원 비용이 많이 드는 구문분석 작업을 최소화할 수 있습니다.

애플리케이션 개발자나 DBA는 그러한 PL/SQL 프로시저의 사용을 추적하여 공유되는 오라클 구조를 효과적으로 사용할 수 있도록 보장할 수 있습니다.

 

동적인 성능 뷰
동적인 성능 뷰를 질의하여 현재 세션이나 인스턴스에 관한 정보를 얻을 수 있습니다.

데이터 딕셔너리 뷰
많은 데이터 딕셔너리 뷰는 객체 및 통계에 관한 정보를 얻기 위하여 질의될 수 있습니다. 이들 뷰로는 USER_*, ALL_*, DBA_* 뷰가 있습니다. 예로, USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEXES, ALL_INDEXES, DBA_INDEXES 등 입니다.

참조

 문맥

 참조

초기화 파라미터

HASH_AREA_SIZE
HASH_JOIN_ENABLED
HASH_MULTIBLOCK_IO_COUNT
STAR_TRANSFORMATION_ENABLED
USER_DUMP_DEST
TIMED_STATISTICS
SQL_TRACE
OPTIMIZER_MODE

 동적인 성능 뷰

V$MYSTAT
V$PARAMETER
V$SYSTEM_ PARAMETER

 데이터 딕셔너리 뷰

다양한 USER_*, ALL_*, DBA_* 뷰들

 명령어

ALTER SYSTEM
ALTER SESSION
EXPLAIN PLAN

 프로시저

SYS.DBMS_SESSION.SET_SQL_TRACE
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

X 정답:C


X 정답:A


X 정답:C


X 정답:B


X 정답:AF


X 정답:C