데이터 설계 단계
애플리케이션이 필요로 하는 데이터를 결정해야 합니다. 성능 목표를 최상으로 달성하기 위해서는 정보를 체계화할 필요가 있습니다.

데이터베이스의 물리적 구조
이것은 주로 다음과 관련이 있습니다:

전형적인 OLTP 애플리케이션

요구사항

특성
의사결정 지원 애플리케이션은 대용량의 정보를 이해하기 쉬운 보고서로 만듭니다.

이들 애플리케이션은 OLTP 시스템으로부터 데이터를 수집하며, 요약 내용을 사용하고, 검색하여 총합을 계산합니다. 이들 애플리케이션은 액세스 방법으로서 전체 테이블 스캔을 많이 사용합니다.

조직 내에서 의사결정자는 이 데이터를 사용하여 조직이 취해야 할 전략을 결정합니다.

예: 마케팅 툴은 어느 품목이 어디에서 가장 잘 판매되는가를 결정하기 위하여 인구통계적 데이터로부터 수집된 정보에 기초하여 소비자의 구매 패턴을 결정합니다.

요구사항

하이브리드 시스템
많은 애플리케이션들은 구성 방법과 오라클 옵션에 따라 달라집니다. 애플리케이션이 수행하는 활동의 유형을 정하고, 애플리케이션에 가장 적합한 기능을 정해야 합니다.

OLTP 데이터베이스에서 수집된 데이터를 질의만 수행할 DSS 데이터베이스로 복사함으로써 모순되는 목표들의 문제를 해결할 수 있습니다. 그러나, 이 예를 사용하기 위해서는 DSS 애플리케이션에 대한 정확성을 목표로 해야 합니다.

따라서, 가장 중요한 목표를 결정해야 합니다. 

인덱스, IOT 및 클러스터
이들은 필요한 경우 DBA가 생성하는 논리적인 데이터 구조입니다.

히스토그램
이들은 SQL 문의 실행을 튜닝하기 위하여 ANALYZE 명령에 의해 수집되고, 딕셔너리 뷰에 저장되며, Cost Based Optimizer에 의해 사용되는 통계입니다.

B - Tree 인덱스 생성 시기
B - Tree 인덱스는 테이블에서 적은 비율의 행을 선택하는 질의의 성능을 향상시킵니다. 일반적인 Guideline으로서, 테이블 행의 10 - 15% 보다 적게 질의되는 테이블에 인덱스를 생성해야 합니다. 이 값은 모든 데이터가 한 인덱스로부터 검색되거나 인덱스된 열이 다른 테이블들에 조인하기 위하여 사용될 수 있는 상황에서는 보다 높을 수 있습니다.

이 지침은  다음과 같은 가정에 의거한 것입니다:

인덱스 증가 방법
인덱스는 항상 균형이 유지되고 상향식으로 증가합니다.

키가 단조롭게 증가하는 상황에서는, 인덱스 레벨(BLEVEL)이 상승할 수 있습니다. 행이 추가됨에 따라, 새로운 엔트리들이 인덱스 오른쪽에 추가되기 때문입니다. 가장 오른쪽의 리프(leaf) 블록이 가득 차면, 오라클은 2개의 블록으로 분할하고 50%는 원래의 리프 블록에 놓고 50%는 새 리프 블록에 놓습니다. 새로운 엔트리/포인터는 가장 오른쪽 분기(branch) 블록에 추가됩니다.

이 패턴은 행이 삽입됨에 따라, 가장 오른쪽 분기 블록이 가득 차서 2개로 분할되어야 할 때까지 계속하여 반복됩니다.

이 마지막 패턴은 루트(root) 블록인 가장 상위의 블록이 분기 블록의 첫번째 레벨에 포인터를 더 이상 포함시킬 수 없을 때까지 계속하여 반복됩니다. 오라클은 루트 블록을 분할하지 않기 때문에, 새 레벨의 분기 블록을 추가합니다.

인덱스의 깊이가 깊어 질수록, 효율성이 감소됩니다.
B - Tree의 BLEVEL을 가능하면 4 이하로 낮게 유지하는 것이 중요합니다.

이에 관한 정보를 얻기 위하여, *_INDEXES 딕셔너리 뷰에 있는 통계자료를 수집하여 BLEVEL 열에 저장하는 ANALYZE 명령을 실행시키십시오.

  SQL> ANALYZE INDEX i_name COMPUTE STATISTICS;

B - Tree 인덱스 성능 감소 문제 해결 방법

예: 이 예는 EMP 테이블의 JOB 열과 관련되어 있습니다.

EMP 테이블

비트맵 인덱스

EMPNO 

JOB 

Job='CLERK’

Job=' MANAGER'

Job=' ANALYST'

8000

MANAGER

 0

 1

 0

8001

ANALYST

 0

 0

 1

8002

CLERK

 1

 0

 0

비트맵 인덱스 생성 시기

성능 고려사항

유지 시 고려사항
DSS 환경에서, 일반적으로 데이터는 대량의 삽입 및 갱신을 통하여 유지됩니다. 인덱스 유지는 각 DML 작업이 종료될 때까지 연기합니다. 예를 들어, 비트맵 인덱스, 비트맵 행 정보 및 rowid 정보가 있는 테이블에 1000 행을 삽입할 경우, 삽입된 행이 정렬 버퍼의 참조에 위치한 다음, 모든 1000개의 인덱스 엔트리가 배치로 갱신됩니다.

이것이 SORT_AREA_SIZE가 비트맵 인덱스에서 삽입 및 갱신으로 좋은 성능을 위해 적절하게 설정되어야 하는 이유입니다. 따라서, 세그먼트 내 하나 이상의 row가 변경되더라도, 각 비트맵 세그먼트는 DML 당 한번만 갱신 됩니다.

비트맵 인덱스에 대한 초기화 파라미터
다음 2개의 초기화 파라미터는 성능에 영향을 미칩니다:

정의
리버스 키 인덱스를 생성하면 복합 키의 경우 열 순서를 유지하면서 각 열 키 값의 바이트가 역으로 됩니다.

리버스 키 인덱스 생성 시기
증가하는 키(송장이나 직원 또는 주문 번호와 같은 시퀀스 번호)가 인덱스 BLEVEL을 반복하여 낮출 경우, 인덱스 엔트리를 더 균등하게 분산시켜 강제 블록 분할을 피할 수 있습니다.

단점
애플리케이션 문장이 범위를 지정할 때, explain plan이 전체 데이블 스캔을 생성합니다.

딕셔너리 뷰
다음의 질의를 사용하여 모든 리버스 키 인덱스의 이름을 나열합니다:

  SQL> select index_name, index_type
    2  from dba_indexes
    3  where index_name = ‘ORD_ORD_NO_IDX’;
  INDEX_NAME         INDEX_TYPE
  ------------------ ------------
  ORD_ORD_NO_IDX     NORMAL/REV

정의
Index-organized 테이블은 하나 이상의 열에 인덱스를 갖고 있는 일반 테이블과 같지만, 테이블과 B-Tree 인덱스에 대해 2개의 별도의 세그먼트를 유지하지 않고, 데이터베이스 시스템은 다음의 2가지 모두를 포함하는 하나의 B-Tree 인덱스를 유지합니다:

성능 이점

Index-organized 테이블 생성 시기
Index-organized 테이블은 기본 키(primary key)를 통하거나, 텍스트 검색에서 사용되는 역 인덱스를 사용하는 애플리케이션에서와 같이 기본 키(primary key)의 prefix인 키를 통해서 데이터에 빈번하게 액세스하기에 적합합니다. 이들 인덱스는 값과 모든 위치를 함께 보관합니다. 따라서, 각 워드는 하나의 엔트리를 갖고 있고, 엔트리는 워드가 발생하는 모든 위치를 기록합니다. 인덱스로 구성된 테이블은 역 인덱스를 모델링하는데 매우 효율적인 메커니즘입니다.

PCTTHRESHOLD, INCLUDING 절과 오버플로우 세그먼트

PCTTHRESHOLD 절
이 절은 인덱스로 구성된 테이블 행에 대한 인덱스 블록 내에 저장된 공간의 비율을 지정합니다. 행이 이 값을 기본으로 하여 계산된 크기를 초과할 경우, INCLUDING 절에 이름이 있는 열 다음의 모든 열은 오버플로우 세그먼트로 이동됩니다. OVERFLOW가 지정되어 있지 않다면, 한계값을 초과하는 행은 거부됩니다. PCTTHRESHOLD 디폴트는 50이고 0 - 50 의 값이어야 합니다.

INCLUDING 절
이 절은 인덱스로 구성된 테이블 행을 인덱스와 오버플로우 부분으로 분할하기 위한 열을 지정합니다. INCLUDING 키워드 다음의 모든 열은 오버플로우 데이터 세그먼트에 저장됩니다. 이것이 지정되어 있지 않고 행 크기가 PCTTHRESHOLD를 초과할 경우, 기본 키를 제외한 모든 열은 오버플로우 영역으로 이동될 것입니다. 열은 마지막 기본 키 열의 이름이나 어떠한 기본 키의 이름이라도 될 수 있습니다.

OVERFLOW 절과 세그먼트
이 절은 지정된 한계 값을 초과하는 인덱스로 구성된 테이블 데이터 행이 테이블스페이스, 저장공간 및 블록 이용 파라미터를 지정하는 세그먼트 속성에 의하여 정의된 데이터 세그먼트 내에 위치하도록 지정합니다.


정의
클러스터는 동일한 테이터 블록을 공유하는 하나 이상의 테이블 그룹으로서, 동일한 데이터 블록은 공통 열들을 공유하고 종종 조인 질의에서 함께 사용됩니다.

성능 이점

성능 고려사항
일반적으로 전체 테이블 스캔은 클러스터되지 않은 테이블에서보다는 클러스터된 테이블에서 속도가 더 느립니다.

인덱스 클러스터
인덱스 클러스터는 클러스터 인덱스라하는 인덱스를 사용하여 클러스터 내에 데이터를 유지합니다:

해시 클러스터
해시 클러스터는 함수를 사용하여 행의 위치를 계산합니다. 해시 함수는 클러스터 키를 사용하며, 사용자가 정의하거나 시스템에 의해 생성될 수 있습니다.
행이 해시 클러스터 내의 테이블에 삽입될 때:

해시 함수는 해시 테이블로부터 데이터를 검색하는 동안 행의 위치를 정하는 데 사용됩니다.
클러스터 키를 사용하는 동등 검색에 대해, 해시 클러스터는 인덱스 클러스터 보다 뛰어난 성능을 제공합니다:

클러스터를 사용해서는 안 되는 경우

해시 클러스터를 사용해서는 안 되는 경우

정의
균등하게 분산된 데이터에 대해, 원가 기반 옵티마이저는 특정 문장을 실행하는데 소요되는 원가를 정확하게 추측합니다. 그러나, 데이터가 균등하게 분산되어 있지 않은 경우, 옵티마이저는 질의의 선택성을 정확하게 추정할 수 없습니다. 이러한 경우, 히스토그램은 특정 열의 분배를 설명할 필요가 있습니다.

히스토그램을 사용해야 할 경우
WHERE 절에서 빈번하게 사용되는 열과 인덱스된 열에서, 그리고 데이터가 매우 비대칭적으로 분산되어 있는 경우.

히스토그램을 사용해서는 안 되는 경우

히스토그램 통계를 생성하는 방법

  SQL> ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal    
    2 SIZE 10;
  SQL> ANALYZE TABLE t1 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

SIZE 절은 버킷의 최대 수를 정합니다.

열에서 발생하는 동일하지 않은 값의 수가 상대적으로 적을 경우, 빈번하게 발생하는  값의 수 보다 큰 수로 버킷수를 설정하는 것이 유용합니다.

주의 : 관심 있는 열에 대해서만 통계정보를 수집하도록 하여 통계의 양을 감소시키십시오:예를들어 조인 조건에서 사용되는 열에 대하여 ANALYZE … FOR COLUMNS 또는 ANALYZE … FOR ALL INDEXED COLUMNS 같은 명령어를 사용하십시오.

ANALYZE … SIZE n 명령어는 모든 관련된 열에 대하여 히스토그램을 생성합니다. 이 문장과 FOR ALL INDEXED COLUMNS를 함께 사용하는 경우, 기본키와 유일키 제약조건을 가진 컬럼에 대한 불필요한 히스토그램이 생성됩니다.

딕셔너리 뷰


공간 할당

  • 동적인 공간 할당의 성능 로드 피하기: 공간을 테이블, 클러스터, 인덱스에 명백하게 할당
  • extent가 할당되는 속도를 알아내어 extent 생성 계획을 세울 수 있도록, 규칙적으로 증가 패턴 검사

인덱싱

  • 인덱싱은 OLTP 시스템에서 데이터 검색 기능에 매우 중요합니다. 인덱스된 테이블에서 DML 문장은 인덱스 유지를 필요로 하는데, 이 경우 현저한 성능 오버헤드가 발생합니다. 따라서, 인덱싱 전략은 애플리케이션의 실제 필요에 보다 면밀하게 맞추어져야 합니다.
  • 외부키(foregin key)에 인덱스를 설정할 경우, 부모(parent) 데이터를 잠그 (locking)지 않고도 자식(child) 데이터를 수정하는 데 도움이 됩니다.
  • DML 작업에 영향을 미치는 잠금(lock) 이슈 때문에, 비트맵 인덱싱 보다는 B-Tree 인덱싱이 선호됩니다: B-Tree 인덱스 엔트리가 잠겨있을 경우에는 하나의 행만이 잠겨지지만, 비트맵 인덱스 엔트리가 잠겨 있을 경우에는 전체 범위의 행이 잠겨집니다.
  • 리버스 키 인덱스는 시퀀스 행에 대해 자주 발생하는 B-Tree 블록 분할을 하지 않습니다. 또한, 리버스 키 인덱스를 사용할 경우, Oracle Parallel Server 애플리케이션이 더 빠르게 실행됩니다.
  • 앞 페이지에서 제시한 것처럼, 규칙적으로 인덱스를 재구축할 필요가 있습니다.

해시 클러스터링

  • 해시 클러스터를 사용할 경우 동등 질의에 액세스하는 속도를 높여야 합니다. 그러나, 다음과 같은 테이블에 대해 이러한 데이터 구조를 선택해서는 안됩니다:
    -  많이 삽입되는 테이블 (액세스될 필요가 있는 블록의 수와 공간의 사용 때문에)
    -  큰 열 값으로 많이 갱신되는 테이블(이전(migration) 가능성 때문에)  
  • 테이블이 증가할 경우, 해시 키에서 많은 충돌이 발생하게 되어 오버플로우 블록에 행을 저장해야 합니다. 이러한 상황을 피하기 위하여 HASHKEYS 값을 정확하게 산정하십시오. 특정 행 수에 대해 많은 수의 해시 키를 사용하면, 충돌이 감소할 것입니다.  


롤백 세그먼트 고려사항
트랜잭션은 짧아야 하는데, 이것은 롤백 세그먼트 구성에 중요합니다:

롤백 세그먼트의 정확한 수를 얻기 위하여, 트랜잭션 패턴을 알 필요가 있습니다. 예를 들어, 시스템의 다음 특성을 고려하십시오:

특정 시간에 8건 이상의 트랜잭션이 활동할 가능성은 매우 낮으므로, 8개의 롤백 세그먼트면 충분합니다.

롤백 세그먼트 extent 크기는 상대적으로 작을 수 있습니다 (이들 작은 트랜잭션이 시스템에서 유일한 트랜잭션이라면 100k).

작은 데이터베이스에 대해서는 적어도 10을, 더 큰 데이터베이스에 대해서는 20을 MINEXTENTS로 설정할 필요가 있습니다. 그 이유는 다음과 같습니다:

무결성 제약조건
애플리케이션 논리를 프로시저 코드에 보관하는 것과 선언적 제약조건을 사용하는 것 중에서 선택해야 하는 경우, 항상 제약조건이 프로세스보다 비용이 덜 든다는 사실을 명심하십시오. 참조 무결성과 CHECK 제약조건은 여기서 고려해야 할 주요 유형입니다.

공유 코드
그렇지 않다면, 코드가 패키지, 프로시저, 함수와 같은 저장 프로시저 객체에 의해 공유되고 있나를 확인할 필요가 있습니다.

바인드 변수
구문분석의 오버헤드를 최소로 유지하고자  할 경우, 애플리케이션 코드가 리터럴(literal) 대신 바인드 변수를 사용하도록 하십시오. 

DB_BLOCK_SIZE와 DB_FILE_MULTIBLOCK_READ_COUNT
일반적으로 DB_BLOCK_SIZE 파라미터를 시스템에서 지원되는 최대값으로 설정해야 합니다. 이것 때문에 큰 데이터베이스를 재생성하게 되더라도, 더 큰 블록 사이즈가 DSS 애플리케이션 특성의 읽기 집중 작업을 쉽게 하기 때문에 분명한 성과가 있습니다.

DB_FILE_MULTIBLOCK_READ_COUNT 파라미터의 설정에 특별히 주의하십시오. 이 파라미터는 전체 테이블 스캔 동안 단일 운영체제 읽기 호출에 의해서 읽혀지는 데이터베이스 블록 수를 결정합니다: 8k 블록 크기에는 8, 16k 블록 크기에는 4.

큰 값으로 설정하면 테이블 스캔 비용이 적게 들고 주로 인덱스로 테이블을 스캔합니다.

인덱싱
인덱스 유지의 공간 및 성능 오버헤드를 최소화할 수 있는 방법을 고려하십시오. 대부분의 질의가 전체 데이블 스캔을 사용하기 때문에, 다음의 작업을 할 수 있습니다:

클러스터링
클러스터를 재생성해야 할 가능성이 있는 경우를 제외하고는, 두 가지 유형의 클러스터 모두 고려되어야 합니다. 특히, 많은 데이터를 로드하는 동안 규칙적으로 증가하는 테이블은 제외하고 최상의 액세스 성능을 위해서는 해시 클러스터를 고려해야 합니다

구문분석 시간
SELECT 문을 구문분석하는데 소요되는 시간은 질의 실행에 소요되는 시간 중 일부에 불과합니다. 라이브러리 캐쉬를 튜닝할 경우, OLTP 보다는 DSS에 대한 이슈가 훨씬 적습니다.

실행 계획에서 최적의 액세스 경로가 최우선입니다: 적은 변동사항이 수분 또는 수시간의 비용을 초래할 수 있습니다. 따라서, 개발자는 반드시 다음을 지켜야 합니다:

애플리케이션 로직이 바인드 변수를 사용할 경우, 이러한 기능의 이점을 얻지 못합니다. 옵티마이저는 열 선택성에 관하여 총괄적으로 가정하지만, 리터럴(literal)이 있는 Cost Based Optimizer(원가 기반 옵티마이저)는 히스토그램을 사용합니다.

예: 다음 문장을 실행할 경우, 옵티마이저가 실행 계획을 결정할 때 :1의 값은 알지 못합니다.
    SQL> SELECT * FROM big_table
      2  WHERE number_column = :1;

옵티마이저는 이 단계에서 반환하는 행의 수를 정확하게 계산할 수 없습니다. 이것은 서버가 하위의(sub) 최적 실행 계획을 사용한다는 것을 의미합니다.

따라서, 인덱스로 액세스하는 OLTP 시스템에서 바인드 변수는 동일한 구문이 두 번 실행될 기회가 적기 때문에 바인드 변수를 사용하도록 강력하게 권장하였지만, 의사결정 지원 시스템에는 이상적이지 않습니다. 

OLTP와 DSS에 대해 동일한 데이터베이스 사용
큰 데이터베이스를 관리하는 많은 오라클 고객들은 종종 트랜잭션 처리와 의사결정 지원에 동일한 인스턴스를 사용하기를 원합니다. 두 가지 필요를 위해 데이터 저장 공간을 최적으로 튜닝하는 것은 불가능합니다. 따라서, 애플리케이션은 자원 사용을 위해 경합하게 될 것입니다.

오라클사는 OLTP와 DSS 작업에 대해 별도의 데이터베이스를 사용하여 필요할 때 데이터를 다운로드할 것을 강력히 권장합니다.

이슈

권장사항
그러나, 하나의 데이터베이스 대신 두개의 큰 데이터베이스를 유지하는 것은 분명히 재무 비용이 드는 일이기 때문에, 일부 조직은 다음과 같은 경우 여전히 하이브리드 시스템을 실행하기로 결정할 것입니다:

메모리 사용
다음의 파라미터는 주간에 더 높은 값을 갖게 될 것입니다(즉, OLTP에서):

병렬 질의

한가한 시간에, 이들 파라미터를 적절한 수로 재설정하여, DSS 질의 사용자들에게 야간 프로파일을 재지정할 수 있습니다.

롤백 세그먼트
공간의 여유가 있는 경우, 다음을 원할 것입니다:

공간의 여유가 없는 경우, 야간에 데이터베이스를 종료하기 전에, 주간 롤백 세그먼트를 야간 롤백 세그먼트로 대체하는 스크립트의 일정을 세우고, 데이터베이스를 시작할 때에는 반대로 하십시오. 

다중 쓰레드 서버
NET8 tnsnames 파일은 두 가지 범주의 별칭(alias)을 통합할 수 있습니다: 공유 서버 트랜잭션에 대한 별칭과 전용 서버 연결에 대한 별칭.

통계 수집 및 사용

ANALYZE 명령으로 객체에 대한 통계를 수집합니다. 비용 기준 최적기는 볼륨이나 선택성의 작은 변경에 민감하지 않지만, 자주 수정되는 테이블에 대한 새 통계를 주기적으로 수집하여 최적기가 정확한 최신 정보를 사용하도록 할 수 있습니다.

        ANALYZE {INDEX|TABLE|CLUSTER} object_name
        {COMPUTE|DELETE|ESTIMATE} STATISTICS
        [FOR ... [SIZE n]] [SAMPLE n {ROWS|PERCENT}]

ANALYZE 명령을 사용하여 새 통계를 수집하면 데이터 딕셔너리에 있는 기존 통계를 덮어쓰고 공유 풀에서 관련된 실행 계획을 비웁니다.

DBMS_STATS 패키지에는 인덱스, 테이블, 스키마 또는 데이터베이스를 분석할 수 있도록 하는 여러 프로시저가 포함되어 있습니다. 또한 이 패키지를 사용하면 어느 정도 병렬성을 가지고 대부분의 통계를 수집할 수 있습니다. 자세한 사항은 Oracle8i Supplied Packages Reference 설명서를 참조하십시오.

통계 정확도

통계를 수집할 때 두 가지 레벨의 정확도를 지정할 수 있습니다.

표본과 함께 사용하면 COMPUTE 옵션만큼 신뢰할 수 있습니다.

수집된 통계를 지울 때는 DELETE 옵션을 사용할 수 있습니다. 새 통계가 기존 통계를 덮어쓰므로 객체를 재분석하기 전에 반드시 DELETE 옵션을 사용할 필요는 없습니다.

FOR 절

FOR 절은 다음 옵션을 제공합니다.

SIZE 절

SIZE 절은 히스토그램의 최대 버킷 개수를 지정합니다. 기본값은 75이고 최대값은 254입니다. 히스토그램은 이 단원의 뒷부분에서 더 자세히 검토됩니다.

테이블 통계

테이블을 분석하면 다음과 같은 테이블 관련 정보를 얻게 됩니다.

모든 테이블 레벨 통계가 중요하지만 빈 블록 수가 왜 중요한지 명확히 설명되지 않았으므로 여기서 자세히 검토합니다.

블록과 빈 블록 수

각 테이블은 세그먼트 헤더 블록에 고수위를 가지고 있습니다. 고수위는 테이블에 사용된 마지막 블록을 가리킵니다. Oracle 서버에서 전체 테이블 스캔을 수행할 때 서버는 처음부터 고수위까지의 모든 블록을 읽습니다. 테이블에서 행이 삭제될 때는 고수위가 재설정되지 않는다는 데 유의하십시오. 이것은 Oracle 서버가 빈 블록을 읽는 데 시간을 낭비할 수 있다는 것을 의미합니다.

테이블 분석이 불가능하거나 바람직하지 못한 경우 DBMS_SPACE.UNUSED_SPACE 프로시저를 사용하여 고수위를 찾고 고수위 위에 있는 블록 수를 찾을 수 있습니다.

예제

다음 예제에서 약 5개의 빈 블록을 볼 수 있습니다. 빈 블록과 전체 블록 간의 비율을 모니터해야 합니다.

SQL> analyze table employees estimate statistics for table;

SQL> select num_rows,blocks,empty_blocks

2       ,      avg_space,avg_row_len, sample_size

3       from   dba_tables

4       where  table_name = 'EMPLOYEES' and owner = user;

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE

-------- ------ ------------ --------- ----------- -----------

   15132    434            5       225          48        1064

 

빈 블록에 문제가 있다고 판단될 때는 테이블을 재구성해야 합니다. SQL*Plus, SQL*Loader, 엑스포트 및 임포트 같은 여러 다른 기술을 사용하여 테이블을 재구성하면 테이블에 더 효율적으로 액세스할 수 있습니다.

클러스터링 요소

인덱스 클러스터링 요소는 비용 기준 최적기가 인덱스 스캔 비용 추정에 사용하는 중요한 인덱스 통계로 인덱스를 통해 모든 테이블 행을 검색하는 데 필요한 논리적 데이터 블록 방문 횟수를 나타냅니다. 인덱스 항목이 테이블 행 순서를 따르는 경우에는 각 블록에 대한 방문이 한 번만 이루어지므로 이 값이 데이터 블록 수에 근접합니다. 그러나 인덱스 항목이 무작위로 여러 다른 데이터 블록을 가리키는 경우에는 클러스터링 요소가 행 수에 근접할 수 있습니다.

예제

 

SQL> analyze index reg_pk compute statistics;

SQL> select blevel, leaf_blocks, distinct_keys, clustering_factor

  2  from   dba_indexes

  3  where  index_name = 'REG_PK' and owner = user;

BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR

------ ----------- ------------- -----------------

     2         682         56252             21349

열 통계 보기

가장 낮은 값과 가장 높은 값 둘 다 RAW(이진) 형식으로 저장된다는 점에 유의하십시오.

USER_TAB_COL_STATISTICS 뷰는 USER_TAB_COLUMNS 뷰에 표시되는 열의 관련 부분 집합을 제공합니다. 대신 DBA_TAB_COL_STATISTICS 뷰를 사용할 수도 있으나 이 뷰는 OWNER 열을 포함하지 않으므로 데이터베이스의 여러 다른 스키마에 같은 이름의 테이블이 여러 개 있을 때는 혼란스러운 결과를 얻게 됩니다.

NUM_BUCKETS 열은 일반 열 통계가 하나의 버킷이 있는 히스토그램으로 처리됨을 표시합니다. 히스토그램은 다음 페이지에서 검토됩니다.

예제

 

SQL> select column_name, num_distinct, low_value, high_value

  2  ,      num_nulls, num_buckets

  3  from   user_tab_col_statistics

  4  where  table_name = 'EMPLOYEES' and column_name = 'SALARY';

COLUMN_NAME NUM_DIST LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS

----------- -------- --------- ---------- --------- -----------

SALARY           496 C208      C30A62             0           1

히스토그램 사용

일반 열 통계를 사용할 때 각각의 열에 대한 최소값과 최대값이 저장됩니다. 비용 기준 최적기는 이 값을 사용해서 두 양극 값 사이에 데이터가 균등하게 분산되었다고 가정하여 술어 선택성을 계산합니다. 그러나 데이터가 비대칭인 경우 이 가정은 부분적인 최적화 계획이 될 수 밖에 없습니다. 히스토그램에서 열 값을 많은 버킷으로 분할하여 열 내에서의 데이터 분산에 대해 더 자세한 정보를 저장하는 것을 고려할 수 있습니다. 그러나 이렇게 하면 추가 데이터 딕셔너리 저장 영역이 필요합니다. 버킷의 높이는 균형을 이루며 이것은 각 버킷이 대략 같은 수의 값을 포함함을 의미입니다.

기본 버킷 수는 75이고 최대값은 254입니다.

예제

SQL> analyze table classes compute statistics for columns class_id;

SQL> select endpoint_number, endpoint_value

  2  from   user_histograms

  3  where  table_name = 'CLASSES' and column_name = 'CLASS_ID';

ENDPOINT  ENDPOINT

 _NUMBER    _VALUE

--------  --------

       0     50008

       1     55198

       2     56718

       3     63037

     ...       ...

      75    155801

DBMS_STATS를 사용하여 데이터베이스 간의 통계 복사

DBMS_STATS 패키지 프로시저를 사용해서 운용 중인 Oracle8i 데이터베이스의 통계를 테스트 데이터베이스로 복사하여 튜닝을 쉽게 할 수 있습니다. 예를 들어, 스키마 통계를 복사하려면 다음과 같이 합니다.

  1. 운용 중인 데이터베이스의 DBMS_STATS.CREATE_STAT_TABLE 프로시저를 사용하여 사용자 정의 통계 테이블을 생성합니다.
  2. 운용 중인 데이터베이스의 DBMS_STATS.EXPORT_SCHEMA_STATS 프로시저를 사용하여 데이터 딕셔너리의 통계를 1단계의 사용자 정의 통계 테이블로 복사합니다.
  3. 엑스포트/임포트 유틸리티를 사용하여 테스트 데이터베이스의 해당 사용자 정의 통계 테이블로 통계를 전송합니다.
  4. DBMS_STATS.IMPORT_SCHEMA_STATS 프로시저를 사용하여 테스트 데이터베이스의 데이터 딕셔너리로 통계를 임포트합니다.

객체를 분석하기 전에 DBMS_STATS 패키지를 사용하여 통계를 백업할 수도 있습니다. 백업은 다음과 같은 목적으로 사용할 수 있습니다.

통계 복사

데이터 딕셔너리에서 사용자 정의 통계 테이블로 테이블에 대한 통계를 복사하려면, CREATE_STAT_TABLE 프로시저를 사용하여 사용자 정의 테이블을 생성한 다음 EXPORT_TABLE_STATS 프로시저를 사용하여 통계를 복사합니다.

DBMS_STATS는 통계 복사를 위해 다음과 같은 프로시저를 포함합니다.

프로시저

설명

CREATE_STAT_TABLE

통계를 보유할 수 있는 사용자 정의 테이블을 생성합니다.

DROP_STAT_TABLE

사용자 정의 통계 테이블을 삭제합니다.

EXPORT_object_STATS

데이터 딕셔너리에서 사용자 정의 테이블로 통계를 엑스포트합니다.

IMPORT_ object_STATS

사용자 정의 테이블에서 데이터 딕셔너리로 통계를 임포트합니다.

object

COLUMN, INDEX, TABLE, SCHEMA가 될 수 있습니다.

계획 안정도

최적기에 항상 완전한 정보가 있는 것이 아니므로 가능한 최상의 실행 계획을 선택할 수 없는 경우가 종종 있습니다. 이런 경우 힌트나 다른 튜닝 기법을 사용하여 SQL 문을 재작성함으로써 최적기가 계획을 선택하는 데 도움을 주는 것이 좋습니다. 만족스러운 결과가 나온 경우 최적화에 영향을 주는 요소가 변경되었을지라도 같은 질의가 재컴파일될 때마다 같은 튜닝 계획이 생성되도록 하고 싶을 것입니다.

Oracle8i는 Oracle 릴리스, 데이터베이스의 변경 또는 일반적으로 실행 계획을 변경하도록 만드는 다른 요소를 포용하면서 실행 계획을 안정화하는 방법을 제공합니다. 또한 실행 계획을 생성하기 위해 최적기에서 사용하는 여러 힌트가 포함된 스토아드 아웃라인을 생성할 수 있습니다.

SQL 문 등가

계획 안정도는 질의에 정확히 일치하는 텍스트가 있는지를 확인하여 스토아드 아웃라인이 있는지 판단합니다. 이는 공유 풀의 실행 계획을 재사용할 수 있는지 여부를 판단할 때 사용하는 것과 동일한 일치 조건입니다.

스토아드 아웃라인은 최적기에서 안정적인 실행 계획을 얻는 데 사용하는 힌트에 부분적으로 의존합니다. 따라서 계획이 등가가 되는 정도는 계획에서 사용하는 힌트의 성능에 따라 달라집니다. 스토아드 아웃라인에 포함된 실행 단계에는 행 액세스 방식, 조인 순서, 조인 방식, 분산 액세스, 뷰/서브 쿼리 병합이 포함됩니다. 분산 액세스는 원격 노드의 실행 계획을 포함하지 않습니다.

계획 안정도

이 계획은 많은 유형의 데이터베이스와 인스턴스 변경을 거쳐 유지 관리됩니다. 따라서 대량 배포할 응용 프로그램을 개발하는 경우 스토아드 아웃라인을 사용하여 모든 고객이 같은 실행 계획을 액세스하도록 할 수 있습니다. 예를 들어, 인덱스 추가로 스키마가 변경된 경우 스토아드 아웃라인에서 새 인덱스 사용을 막을 수 있습니다.

스토아드 아웃라인 생성

Oracle에서 자동으로 아웃라인을 생성하거나 사용자가 특정 SQL 문에 대한 아웃라인을 생성할 수 있습니다. 아웃라인은 힌트에 의존하므로 비용 기준 최적기를 사용합니다.

범주

스토아드 아웃라인은 범주별로 그룹화할 수 있습니다. 같은 SQL 문에 여러 범주의 스토아드 아웃라인이 있을 수 있습니다. 예를 들면, OLTP 범주와 DSS 범주가 있을 수 있습니다. 범주 이름을 생략하면 아웃라인은 DEFAULT 범주에 포함됩니다.

CREATE_STORED_OUTLINES 매개변수

Oracle에서는 매개변수 CREATE_STORED_OUTLINES가 TRUE나 범주 이름으로 설정될 때 스토아드 아웃라인을 자동으로 생성합니다. TRUE로 설정하면 DEFAULT 범주가 사용됩니다. 이 매개변수가 활성화될 때 Oracle에서는 모든 실행된 SQL 문에 대한 아웃라인을 생성합니다. 이 매개변수를 FALSE로 설정하여 프로세스를 비활성화할 수 있습니다. 이 매개변수를 사용하면 Oracle에서 아웃라인 이름을 생성합니다.

CREATE OUTLINE 명령

CREATE OUTLINE 명령을 사용하여 특정 명령문에 대한 스토아드 아웃라인을 생성할 수도 있습니다. 이 접근 방법의 한 가지 장점은 스토아드 아웃라인의 이름을 지정할 수 있다는 것입니다.

스토아드 아웃라인 사용

USE_STORED_OUTLINES를 TRUE로 설정하면 DEFAULT 범주의 아웃라인이 사용됩니다.

USE_STORED_OUTLINES를 범주 이름으로 설정하면 해당 범주의 아웃라인이 사용됩니다. 해당 범주에는 일치하는 아웃라인이 없지만 DEFAULT 범주에 있으면 그 아웃라인이 사용됩니다.

명령문은 아웃라인의 명령문 텍스트와 일치해야 합니다. 명령문은 공유 풀에서 커서를 비교하는 방식으로 비교됩니다. 이것은 아웃라인의 힌트가 명령문의 텍스트에서 사용될 때와 일치해야 한다는 의미입니다. 바인드 변수 값은 일치하지 않아도 됩니다.

SQL 문의 실행 계획을 결정하기 위해 Oracle8i는 다음 논리를 사용합니다.

사용할 수 없는 객체를 사용하도록 아웃라인에서 지정하는 경우 명령문에서는 단순히 힌트를 사용하지 않습니다. 예를 들면, 명령문이 더 이상 존재하지 않는 인덱스를 참조하는 경우가 있습니다. 스토아드 아웃라인이 사용 중인지 확인하기 위해 명령문에 대한 explain plan을 USE_STORED_OUTLINES 집합과 함께 실행할 때와 USE_STORED_OUTLINES 집합 없이 실행할 때를 비교해 볼 필요가 있습니다.

스토아드 아웃라인 유지 관리

OUTLN_PKG 패키지의 프로시저를 사용하여 스토아드 아웃라인 및 그 범주를 관리합니다.

프로시저

설명

DROP_UNUSED

생성된 이후 사용된 적이 없는 아웃라인을 삭제합니다.

DROP_BY_CAT

지정한 범주 이름에 할당된 아웃라인을 삭제합니다.

UPDATE_BY_CAT

한 범주의 아웃라인을 다른 범주로 재할당합니다.

ALTER/DROP OUTLINE 명령을 사용하여 아웃라인을 관리할 수도 있습니다.

모든 아웃라인이 저장되어 있는 OUTLN 스키마를 엑스포트하여 계획을 엑스포트하고 임포트할 수 있습니다. OUTLN 스키마의 테이블에 아웃라인을 질의할 수 있습니다.

DBA_OUTLINES 및 DBA_OUTLINE_HINTS는 동일한 데이터 딕셔너리 뷰입니다.

참고: 데이터베이스와 함께 사용자 OUTLN이 자동으로 생성되므로 그 암호를 변경해야 합니다.

구체화된 뷰

구체화된 뷰에는 뷰 실행의 결과 행과 뷰 정의가 모두 저장됩니다. 뷰와 마찬가지로 구체화된 뷰도 질의를 기반으로 사용하지만 이 질의는 뷰가 생성될 때에 실행되어 그 결과가 테이블에 저장됩니다. 사용자가 다른 테이블과 동일한 저장 영역 매개변수를 가지고 테이블을 정의하여 선택한 테이블스페이스에 배치할 수 있습니다. 다른 테이블처럼 구체화된 뷰 테이블을 인덱스화하고 분할하여 이 테이블에 대해 실행되는 질의의 성능을 향상시킬 수 있습니다.

질의를 통해 얻은 구체화된 뷰의 데이터가 만족스러울 경우 서버에서는 기본 테이블이 아닌 뷰를 참조하도록 질의를 변형합니다. 구체화된 뷰를 사용하면 구체화된 뷰에 대한 질의를 재작성하여 재실행하는 것과 같은 비경제적인 작업(예: 조인 또는 합계)을 하지 않아도 됩니다.

구체화된 뷰 갱신

구체화된 뷰는 스냅샷과 같은 내부 처리 방법을 사용하며 여러 갱신 기법을 지원합니다.

구체화된 뷰의 완전한 갱신에는 CREATE 명령의 질의 정의를 재실행하여 기존 데이터를 잘라내는 작업과 디테일 테이블을 기반으로 하는 모든 데이터를 재삽입하는 작업이 포함됩니다.

빠른 갱신은 마지막 갱신 이후의 변경 내용에만 적용됩니다. 두 가지 유형의 빠른 갱신을 사용할 수 있습니다.

Force 갱신 유형으로 정의된 뷰는 가능할 경우 빠른 처리 방법으로 갱신됩니다. 그렇지 않으면 완전 갱신을 사용합니다. Force는 기본 갱신 유형입니다.

Never 옵션은 구체화된 뷰의 모든 갱신을 억제합니다.

갱신 모드

DBMS_MVIEW 패키지를 사용하여 수동 갱신을 수행할 수 있습니다. DBMS_MVIEW 패키지는 REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_MVIEWS 프로시저 등 많은 프로시저와 함수를 제공하여 구체화된 뷰를 관리합니다.

다음과 같은 방법으로 자동 갱신을 수행할 수 있습니다.

참고: 모든 구체화된 뷰에 대해 모든 갱신 유형과 갱신 모드를 사용할 수 있는 것은 아닙니다. Oracle8i Replication 설명서의 부록 C에 각각의 모드 및 모드를 사용할 수 있는 경우가 설명되어 있습니다.

구체화된 뷰의 수동 갱신

다음은 구체화된 뷰(MV)에 대해 가능한 갱신 시나리오 목록입니다.

패키지의 프로시저는 많은 매개변수를 사용하여 다음을 지정합니다.

갱신 작업 실행에는 서버 작업 대기열이 사용됩니다. 따라서 해당 초기화 매개변수인 JOB_QUEUE_PROCESSES와 JOB_QUEUE_INTERVAL을 설정하여 작업 대기열 갱신 프로세스를 사용하도록 해야 합니다.

질의 재작성

구체화된 뷰에 대한 액세스가 바탕이 되는 기본 테이블에 대한 액세스보다 훨씬 더 빠를 수 있으므로 최적기는 질의에서 허용하는 경우 뷰에 액세스하도록 질의를 재작성합니다. 질의 재작성 작업은 응용 프로그램에 투명합니다. 이런 점에서 질의를 재작성하는 것은 인덱스를 사용하는 것과 유사합니다.

사용자는 구체화된 뷰를 사용하기 위한 명시적 권한을 가지고 있지 않아도 됩니다.  기본 테이블에 대한 권한을 가진 사용자가 실행한 질의를 재작성하여 구체화된 뷰에 액세스할 수 있습니다.

구체화된 뷰는 활성화되거나 비활성화될 수 있으며 활성화된 구체화된 뷰를 질의 재작성에 사용할 수 있습니다.

질의 재작성

질의 재작성은 최적기에서 수행됩니다. 재작성은 응용 프로그램에 투명합니다.

재작성을 수행하는 기능은 QUERY_REWRITE_ENABLED 매개변수를 사용하여 인스턴스 레벨이나 세션 레벨 중 하나에서 활성화되어야 합니다.

질의 재작성을 위해 개별 구체화된 뷰를 활성화하거나 비활성화하려는 사용자는 GLOBAL QUERY REWRITE나 QUERY REWRITE 시스템 권한을 가지고 있어야 합니다. 두 권한 모두 사용자가 자신의 스키마에 있는 구체화된 뷰를 활성화할 수 있도록 합니다. GLOBAL 버전을 사용하면 사용자 소유의 구체화된 뷰를 모두 활성화할 수 있는 반면 단순 QUERY REWRITE 권한을 사용할 때는 뷰만이 아니라 기본 테이블도 사용자의 스키마에 있어야 합니다.

구체화된 뷰 생성: 구문

구체화된 뷰 생성 구문은 CREATE SNAPSHOT 명령과 유사하며 추가 옵션이 몇 가지 있습니다. 예제에서는 CREATE 명령이 실행될 때 구체화된 뷰가 채워지도록 BUILD IMMEDIATE 옵션을 선택합니다. 이것은 기본 동작이며 이것과는 달리 구조는 생성하지만 첫번째 갱신이 발생할 때까지 구체화된 뷰를 채우지 않는 BUILD DEFERRED 옵션을 선택할 수도 있습니다.

Oracle8i 이전에 생성된 테이블이 구체화된 뷰의 소스가 되도록 하려는 경우에는 ON PREBUILD TABLE 옵션을 사용합니다.

ENABLE/DISABLE QUERY REWRITE 절은 구체화된 뷰에 대해 질의 재작성이 자동으로 활성화되게 할지 여부를 결정합니다.

구체화된 뷰: 질의 재작성

질의에 대한 실행 계획(계획 테이블의 형식이 지정된 결과)은 질의가 두 개의 기본 테이블에서 실행되지 않고 단지 구체화된 뷰 테이블만을 스캔한다는 것을 보여 줍니다. 이 예제는 구체화된 뷰와 질의 재작성의 위력을 보여주는 것입니다. 구체화된 뷰로 구성된 테이블은 질의에서 명명된 기본 테이블과 이들 기본 테이블에 대한 모든 작업을 완전히 대신합니다.

질의 재작성 제어

다음 매개변수로 질의 재작성을 제어합니다.

OPTIMIZER_MODE: 비용 기준 최적화에서만 질의 재작성을 사용할 수 있습니다. 규칙 기반 최적화를 사용하면 질의 재작성이 발생하지 않습니다.

QUERY_REWRITE_ENABLED: 이 매개변수를 FALSE로 설정하면 비용 기준 최적기를 사용하면서도 질의 재작성 사용을 억제할 수 있습니다. 이 매개변수는 개별 세션뿐만 아니라 전체 인스턴스에 대해 동적으로 변경할 수 있습니다.

QUERY_REWRITE_INTEGRITY: 이 매개변수는 인스턴스와 개별 세션에 대해 동적으로 재설정될 수도 있습니다. 이 매개변수는 다음 값을 받아들입니다.

  • 기본값인 ENFORCED는 Oracle에서 일관성을 보장할 수 있는 경우에만 질의 재작성을 활성화합니다. 최신의 구체화된 뷰와 활성화되고 검증된 제약 조건만이 질의 재작성에 사용됩니다.
  • TRUSTED는 반드시 강제적인 것은 아니지만 선언된 관계를 기반으로 하는 질의 재작성을 허용합니다. 갱신된 구체화된 뷰와 RELY 플래그를 가진 제약 조건이 모두 질의 재작성에 사용됩니다.
  • STALE_TOLERATED를 사용하면 마지막 DML 작업 이후 갱신된 적이 없는 구체화된 뷰와 선언된 관계를 질의 재작성에 사용할 수 있습니다.

질의 재작성은 실행 계획 경로와 유사하게 처리됩니다. 따라서 질의 재작성과 관련된 객체 권한은 없습니다. 디테일 테이블에 대한 액세스를 가지고 있는 사용자는 요약 재작성에서 암시적으로 이익을 얻습니다.

새 힌트인 REWRITE를 사용하여 질의 재작성의 고려 대상이 될 구체화된 뷰를 제한할 수 있습니다. 또 다른 힌트인 NOREWRITE를 사용하여 질의 블록에 대해 재작성을 하지 않도록 할 수 있습니다.

차원

차원은 기존 데이터베이스 테이블의 열을 기반으로 하는 계층을 정의하는 Oracle8i 데이터 딕셔너리 구조입니다. 차원은 옵션이지만 다음과 같은 이유 때문에 사용할 것을 적극적으로 권장하고 있습니다.

  • 제약 조건을 사용하지 않고도 추가 재작성이 가능합니다. 성능상의 이유로 데이터 웨어하우스에서 제약 조건을 구현하는 것은 바람직하지 않을 수 있습니다.
  • 차원과 계층을 명시적으로 문서화하는 데 도움이 됩니다.
  • OLAP 툴에서 사용할 수 있습니다.

차원에 대한 자세한 사항은 Oracle8i Server Tuning 설명서를 참조하십시오.

질의 재작성 비활성화

이 예제에서 NOREWRITE 힌트는 최적기에서 구체화된 뷰를 사용하는 질의를 재작성하지 않도록 합니다. 이 점만 제외하면 명령문이 이전 예제와 동일합니다. 질의에서 파생된 실행 계획은 그룹을 얻으려는 정렬과 두 테이블 사이의 해시 조인을 포함하여 원래의 명령문이 실행되는 것을 보여줍니다.

REWRITE/NOREWRITE 힌트는 ENABLE QUERY REWRITE 절을 가진 CREATE 또는 ALTER MATERIALIZED VIEW 명령에서 설정된 구체화된 뷰 정의를 무효화합니다.

X 정답:A


X 정답:B


X 정답:B


X 정답:B


X 정답:A