차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

--- study:oracle:datadb:5week_1 [2010/06/02 16:58]
vleeky
+++ study:oracle:datadb:5week_1 [2010/06/02 23:58] (현재)
vleeky
@@ 줄 1: / 줄 1: @@
-**굵은 글**4.====== 1단계 헤드라인 ======
+====== 4.1.6 인덱스 선정절차 ======
-.6 인덱스 생성 절차
+=====가) 테이블의 엑세스 형태를 최대한 수집=====
+==== ▶ 개발 단계에서의 엑세스 형태 수집====
+===1) 반복 수행되는 엑세스 형태를 찾는다. ===
+- 반복 수행되는 엑세스는 자신의 수행속도에 반복횟수를 곱한 만큼의 부하를 가져오므로 수행속도에 미치는 영향이 아주 크다.
+- Nested Loop Join, Sub-Query, Fetch후 루프 내에서 반복되는 쿼리
+===2) 분포도가 아주 양호한 컬럼들을 발췌하여 엑세스 유형을 조사한다.===
+- 테이블에는 아주 양호한 분포도를 가진 컬럼이 있기 마련이다.
+- 인덱스 컬럼 순서를 정할 때등 여러 경우에서 크게 활용될수 있음
+===3) 자주 넒은 조건이 부여되는 경우를 찾는다.===
+- 인덱스는 넓은 범위를 처리하게 될 때 많은 부담을 주게 된다.
+- 전체 범위 처리 시, 넓은 범위 처리는 인덱스 사용 때 수행속도가 좋지 않을 수 있음.
+- 처리 범위의 최대 크기와 평균 예상 범위, 자주 사용되는 정렬의 순서, 처리 유형을 수집하면 유용한 자료가 될 것임
+===4) 조건에 자주 사용되는 주요 컬럼들을 추출하여 엑세스 유형을 조사한다.===
+- 빈번하게 사용된다는 것은 시스템 전반에 미치는 영향이 그 만큼 크기 때문에 아주 중요하다.
+===5) 자주 결합되어 사용되는 컬럼들의 조합 및 정렬되는 순서를 조사한다.===
+- 업무적인 측면에서 각 컬럼들간의 상호관계를 잘 파악해 보면 특정 컬럼들끼리 자주 조합하여 사용되는 경합형태를 찾을 수 있다.
+- 중심이 되는 컬럼을 찾아 이와 연관되는 컬럼들을 검토
+===6) 역순으로 정렬되어 있는 경우를 찾는다.===
+- End User는 조건의 범위는 넓게 부여하면서 최근의 데이터만 보려고 하는 경우가 많음. 빠른 응답까지 요구함
+===7) 통계 자료 추출을 위한 엑세스 유형을 조사한다.===
+- 통계자료를 추출하는 경우는 대개 범위가 넓다.
+- 넓은 범위 처리가 대부분 임. 클러스터나 잘 조합된 결합 인덱스 이용하는 것이 좋음.
+====▶  운영 단계에서의 엑세스 형태 수집====
+ 시점: Test 단계 또는 정상 가동 중인 시스템의 인덱스 교정 시점
+===1) 어플리케이션 소스 코드에서 SQL을 추출하여 분석용 테이블에 보관한다.===
+- 기능: 어플리케이션을 분석하여 SQL 문장을 Repository 에 저장. 손쉽게 SQL 문장을 가져올 수 있음.
+- 한계: 동적 SQL 과 같은 쿼리는 가져오는데 한계가 있으며, 수행 횟수와 같은 자세한 사항은 알 수 없음
+===2) SQL-Trace 파일을 파싱하여 SQL 문장 뿐만 아니라===
+===실행계획, 현재 적용되고 있는 인덱스 , 실행 횟수, 처리 범위등의 매우 상세한 정보 획득할 수 있다.===
+- 기능: 전체 커다란 trace 결과에 대해 하나의 상세한 레포트도 얻을 수 있으며 상용 SW에 따라 Advice 역할 까지 해주기도 함.
+- 한계: 시스템에 큰 부하를 줌. 그러므로 trace 기간은 제한 되어야 하며 그 기간내의 분석 밖에 행할수 없음
+===3) 공유 SQL 영역에서 직접 SQL을 찾아오는 방법은 보다 SQL 수집을 간편하게 한다.  ===
+- 기능: 서버에 부담을 덜 주므로 장기간 수집하는 것이 가능하다.
+- 단점: trace에 비해 정보가 빈약하다.
+어떤 어플리케이션에서 수행되었는지와 같은 장기간 수집을 하더라도 여전히 모든 SQL 구문 수집하는 것이 어렵다.
+ ====▶  수집된 SQL을 테이블 별로 출력하여 엑세스 형태 기록====
+- 유사한 형태의 SQL들이 정렬되도록 출력
+- SQL문장 별로 정렬하는 것 보다 조건절에 사용된 컬럼별로 정렬하는 것이 보다 유리
+=====나) 인덱스 대상 컬럼의 선정 및 분포도 조사=====
+====1) 인덱스 대상 컬럼 선정 및 분포도 분석====
+===- 인덱스 대상 컬럼 기준====
+- 엑세스 유형에 자주 등장하는 컬럼
+- 인덱스의 앞부분에 지정해야 할 컬럼
+- 기타 수행속도에 영향을 미칠 것으로 예상되는 컬럼
+{{:notepad:테이블조사서.jpg|}}
+====2) 현행 인덱스 존재 시, 조사한 엑세스 형태가 어떤 인덱스를 타는지 조사====
+===사용 인덱스 이외에 해당 연산 수행 시 예상되는 범위 또한 조사===
+=====다) 특수한 엑세스 형태에 대한 인덱스 선정=====
+===반복해서 엑세스 되는 형태(Critical Access Path)를 찾아내는 것이 우선으로 해야할일임===
+=====라) 클러스터링 검토=====
+===넓은 범위 처리를 자주하는 컬럼에 대해 클러스터링 검토===
+===클러스터링 = 슈퍼스타 영입===
+===인덱스 : 클러스터링 = 안타 : 홈런===
+==='단일테이블클러스터링'을 말함===
+-대량의 동일 컬럼값을 가진 로우들을 하나의 클러스터에 저장하여 클러스터링 팩터를 향상
+-오로지 검색의 속도향상을 위한 클러스터링이므로 대량의 트랜잭션처리되는 경우는 부하발생
+-SALE_DEPT + SALEDATE
+=====마) 결합 인덱스 구성 및 순서의 결정=====
+===앞서 설명한 결합 인덱스 순서 구성 원리에 따라 선정===
+  - 항상 사용하는가?
+  - 항상 ‘=‘로 사용하는가?
+  - 어느 것이 더 좋은 분포도를 가지는가?
+  - 자주 정렬되는 순서는 무엇인가?
+  - 부가적으로 추가시킬 컬럼은?
+=====바) 시험 생성 및 테스트=====
+=====사) 수정이 필요한 애플리케이션 조사 및 선정=====
+====== 4.2 클러스터링 형태의 결정 기준 ======
+{{:notepad:테이블1.jpg|}}
+===테이블 가정===
+- TAB3, TAB4는 대용량의 데이터를 가짐
+- TAB3 는 매우 빈번하기 COL3의 대량을 처리범위에서 TAB2와 TAB1을 조인
+- TAB4 는 COL4로 자주 넓은 범위를 처리
+====== 4.2.1 포괄적인 클러스터링 ======
+===두 테이블이 아닌 그 이상을 결합하는 형태===
+===지나치게 강한 결합은 독립성을 저하===
+===다른 집합과의 유연성 저하===
+- TAB1 + TAB2 + TAB3
+====== 4.2.2 부분적인 클러스터링 ======
+===결합도가 매우 강한 테이블만 클러스터링===
+===원래 하나의 테이블이 정규화로 분할된 테이블===
+===다른 집합과는 거의 조인하지 않는 경우===
+- TAB1 + TAB2(COL1)
+- CLUSTER(TAB3)
+====== 4.2.3 단일테이블 클러스터링 ======
+===주로 넓은 범위처리가 빈번하게 발생하는 경우===
+===넓은 범위 처리를 스캔방식으로 유도하려는 경우에 적용===
+====== 4.2.4 단위 클러스터의 크기 결정 ======
+{{:notepad:테이블2.jpg|}}
+====1. 블록당 유효 저장공간 크기 = (블록크기 – 블록헤더 크기) * (100-PCTFREE)/100====
+ - (8000-100)*0.9=7110
+====2. 로우의 평균 길이 산정====
+ - 평균로우 길이 100바이트로 가정
+====3. 클러스터키 별 로우 수를 구한다.====
+  SELECT AVG( CASE WHEN row_cnt > 71 THEN 71 ELSE row_cnt END)
+         average_row_count
+  FROM ( SELECT cluster_key_columns, count(*) row_cnt
+         FROM table_name
+         WHERE sampling_conditions....
+         GROUP BY  cluster_key_columns );
+====4. 단위 클러스터의 크기를 계산하고 결정한다.====
+- AVG_ROW_LEN * average_row_count
+- 7110/2000 =3.5
+- 블록에 3개까지 단위클러스터가 존재.
+==== *클러스터링 생성절차 예 ( SALES TABLE )*====
+===1. 클러스터 생성===
+       CREATE CLUSTER sales_cluster (sale_date varchar2(8))
+       STORAGE ( storage_clause……)
+       PCTFREE 10 PCTUSED 60
+       SIZE  2000;
+===2. 클러스터 인덱스 생성===
+      CREATE INDEX sales_cluster_idx
+      ON CLUSTER sales_cluster
+      PCTFREE 2
+      STORAGE (INITIAL 20K NEXT 10K);
+===3. 기존테이블 명칭 변경===
+      RENAME  sales TO  sales_copy ;
+===4. 클러스터내에 테이블 생성===
+     CREATE TABLE sales
+     ( saleno     varchar2(6) not null,
+       sale_dept  char(4),
+       sale_date  varchar2(8) not null,
+       …………………………………………………………… )
+     CLUSTER  sales_cluster  (sale_date) ;
+===5. 테이블 내에 데이터 저장===
+     INSERT INTO sales
+     SELECT * FROM sales_copy
+     WHERE sale_date >= '20010101';
+===6. 기존 테이블 삭제, 새로운 인덱스 생성===
+     DROP TABLE sales_copy  ;
+     CREATE INDEX  …………………………………………………;
+====== 4.2.5 클러스터 사용을 위한 조치 ======
+====- 클러스터 키 컬럼을 첫번째로 하는 결합 인덱스가 존재하면====
+====    경우에 따라 클러스터를 사용하지 않고 결합 인덱스를 사용하는 실행계획을 수립====
+====- 클러스터가 반드시 사용되어지기를 원한다면 액세스 경로를 고정시킨다.====
+     SELECT SUM(count(*))
+     FROM   sales
+     WHERE sale_date between ‘20051201’ and ‘20051225’
+     AND sael_dept = ‘11200
+====- 클러스터 사용이 유리할 경우 힌트 사용하여 실행계획 고정====
+    SELECT /*+ CLUSTER(sales) */
+           SUM(count(*))
+    FROM   sales
+    WHERE sale_date between ‘20051201’ and ‘20051225’
+    AND sael_dept = ‘11200’

adminschool.net wiki

사용자 도구

사이트 도구

차이

문서 도구