Ⅰ. Index
인덱스는 테이블이나 클러스터에서 쓰여지는 선택적인 객체로써, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조입니다. 인덱스는 일련의 엔트리 목록으로 구성되어 있으며, 이러한 엔트리는 테이블의 테이타 행에 사용되는 각각의 키값과 ROWID값을 가집니다. 따라서 특정한 값에 대해 ROWID값을 가지고 접근 하기 때문에 접근속도가 상당히 빠릅니다. 인덱스는 여러 가지 방법으로 구현될 수 있으나 오라클에서는 B*Tree기반의 인덱스를 사용합니다. 왜 이렇게 인덱스에 관해서 장황하게 설명하냐구요? 앞으로 나오는 거의 대부분의 튜닝 과정속에서 index가 중요하게 사용되기 때문입죠. 아울러 어떤이는 “가장 최적의 인덱스를 어떻게 구성할 것인가는 수 많은 애플리케이션을 잘 작성하는 것보다 훨씬중요하다.”라고 하더군요. 인덱스의 ROWID는 16진수 문자열로 표현된 테이블 데이터의 실제 메모리의 물리적인 주소를 저장하고 있는 데이터 타입을 말합니다. 아래는 인덱스를 생성하는 형식입니다.
CREATE [UNIQUE] INDEX index
ON table (
column [ASC | DESC], ....
)
[CLUSTER cluster]
[INITRANS int]
[MAXTRANS int]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PCTFREE int]
[NOSORT]
여기서 column은 인덱스를 생성할 컬럼명을 뜻합니다. 인덱스 생성시 B*Tree의 키 값이 되겠죠,
따라서 SQL문에서 특정 column을 찾고자 할 때 이 키를 가지고 B*Tree 찾게 되므로 중요한 값입니다. 물론 index적용여부 또한 이 키가 결정 할 수도 있으니 키 선정에 많은 신경쓸 필요가 있겠군요. 뒤에서 인덱스 키를 선택하는 방법과 이를 이용한 쿼리 성능 향상 방법에 대해 다룹니다.
따라서 index가 생성되어 있는 경우 이 index를 이용한 검색은 물리주소를 직접 엑세스하기 때문에 일반검색에 비해 훨씬 빠른 속도를 냅니다. 그러니 우리에 목적은 얼마나 잘 이 index를 이용해 검색하느냐가 되겠지요. 그럼 바로 SQL문에서 index를 타지 못하는 경우를 살펴보겠습니다. 여기서 인덱스를 탄다고 하는 말은 인덱스를 이용한 검색이 실행됨을 뜻합니다. 다만 넓은 범위 처리시에는 테이블로의 랜덤 액세스가 증가하여 인덱스를 타는 경우 성능이 저하되기도 합니다. 그러한 경우는 clustering 적용을 검토해 봐야할듯 합니다. 그럼 인덱스를 사용할때 좋은 성능을 내는데도 불구하고 인덱스를 사용하지 못하게 되는 경우를 살펴보겠습니다.
◦ 인덱스 사용이 안되는 경우
1) index column이 변형되어 인덱스를 타지 못하는 경우
[예] select * from dept where substr(dname, 1, 3)='abc';
여기에서 인덱스로 사용 되어야 할 dname column이 함수로 인해 변형되어, index를 이루는 값을 찾지 못하여 full scan이 발생하는 경우입니다. 여기서 full scan이라함은 테이블의 처음부터 끝까지 순차적인 탐색이 일어나는 것을 뜻합니다. 정확히는 full rage scan이 맞으나 full scan이란 용어도 많이 쓰이므로 구분 없이 쓰겠습니다.
2) index column으로 사용될 column의 값이 부정형인경우 index타지 못한다.
[예] select * from tmp where job<>'sales';
이 경우 역시 B*Tree에서 찾고자 하는 값이 아예 없으므로 인덱스를 타지 못하겠죠.
3)찾을 키가 NULL 혹은 NOT NULL의 경우 index를 타지못한다.
[예] select * from emp where ename is NOT NULL;
여기서 NULL인 경우 index는 만들어 지지만 key column엔 첨가되지 못하므로 index를 타지 못합니다, NOT NULL인 경우는 B*Tree에서 찾고자하는 키가 없으므로 역시 index를 타지못합니다.
4)Optimizer의 취사선택으로 index scan이 발생되지 않는 경우 (‘7890‘만 인덱스로 생성된 경우)
[예] select * from emp where job like 'ab%' and empno='7890';
이 경우는 SQL문이 해석되고 실행되는 과정에서 optimizer가 비용(여기선 탐색시간 혹은 응답시간 등)이 적게드는 키 값을 선택하여 그것을 기본으로 하여 다른 키 값을 찾기 때문에 (이것을 ‘경로를 선택한다’라고 합니다.) 위의 예에선 ‘ab%’에 해당하는 column이 테이블내에서 특정한 부분에 집중되어 있는 경우 optimizer가 이 column을 선택 할 경우 index를 타지 못하게 됩니다.
일단 이러한 경우만 피해간다고 하면 index사용시 상당한 효과를 볼 수 있겠습니다. insert, update, delete시 인덱스를 재구성해야하기 때문이죠 index도 서버에겐 하나의 overhead입니다. 그것을 어떤 상황에 맞게 구성하고 사용하는 것이 중요하겠죠.
그러면 위와 같은 쿼리문이 꼭 필요한 경우에 인덱스를 적용시키고자 할 경우엔 어떻게 해야 될까요? “인덱스 타도록 강제로 지정해 주면 되지”하시면 정답입니다. 그럼 각각의 경우 어떻게 해결 할지 차례대로 살펴보겠습니다.
◦인덱스 미사용 해결
1) index column에 외부적(external)인 변형이 이루어진 경우
◦select * from dept where substr(dname, 1, 3)='abc';
→select * from dept where dname like "abc%";
◦select * from emp where sal*12=1200000;
→select * from emp where sal=1200000/12;
◦select * from emp where TO_CHAR(HIREDATE,'YYMMDD')='940101';
→select * from emp HIREDATE=TO_DATE('940101','YYMMDD');
◦select * from emp where job like 'ab%' and empno='7890';
→??? 앞서 말씀 드렸듯이 이경우는 데이터의 전체적인 분포를 알아야만 optimizer가 어떤 것을 선택 할지 예상이 가능하므로 테이블에 대한 지식이 우선해야 겠습니다.
◦select * from emp where empno between 100 and 200 and NVL(job, 'x')='clerk';
→select * from emp where empno between 100 and 200 and job='clerk';
◦select * from emp where deptno || nob='10salesman';
→select * from emp where deptno='10' and job='salesman';
[예외] 어디에나 그렇듯이 예외도 있습니다. 여기서 예외란 인덱스를 타지 않도록 만드는 경우가 인덱스를 타는 경우보다 빠른 SQL문을 말합니다. 이러한 방법을 사용하실 때엔 테이블 전체적인 데이터 분포도나 많이 사용되는 쿼리 종류라든지 혹은 자주 검색되는 데이터의 종류 등의 것에 대한 지식이 요구됩니다. 한마디로 테이블을 꿰고 있어야 된다는 말씀. 이러한 경우 index 적용을 피하게 하는 방법을 ‘의도적인 suppressing’ 이라합니다.
[예]
◦select * from emp where job='manager';
→select * from emp where RTRIM(job)='manager';
여기서 job이 index로 구성 되있는 경우이고, 사람들이 입력시 왼쪽이나 오른쪽에 공백을 많이 입력하는 경우라면 굳이 index를 타는 것이 full scan보다 빠를 순 없습니다. 따라서 이러한 경우라면 full scan이 빠르겠죠.
2) index column의 내부적(internal)인 변형시
앞서 살펴보았던 외부적인 변형들의 경우 대개 탐색을 위한 ‘키’값의 변형을 줄이는 방법이 많았던 반면 내부적인 변형은 바로 전에 보았던 suppressing이 주류를 이룹니다. 그럼 예를 보도록 하죠.
테이블의 구조가 다음과 같다고 하고 아래의 예를 보시기 바랍니다.
[테이블]
create table samplet(
chr char(10),
num nubmer(12,3),
var varchar2(20),
dat date);
◦select * from samplet where chr=10;
→select * from samplet where TO_NUMBER(cha)=10;
◦select * from samplet where num like '9410%';
→select * from samplet where TO_CHAR(num) like '9410%';
◦select * from samplet where dat='01-JAN-94';
→select * from samplet where dat=TO_DATE('01-JAN-94');
왜 설명 안하냐구요? 한번에 몽땅하겠습니다. suppressing의 경우 몇가지 기준이 있습니다. 대개의 경우 아래의 룰을 이용하시면 되겠습니다.
변경 전 형식
변경 후 형식
number=character
TO_CHAR(number)=character
character=number
TO_NUMBER(character)=number
date=character
date=TO_DATE(character)
3) NOT Operator의 경우
◦select 'Not fount!' into :col1 from emp where empno<>'1234';
→select 'OK' into:col1 from emp where NOT EXISTS (
select * from emp where empno='1234')
여기에선 서브쿼리를 이용하여 키를 포함 하는 튜플들을 제외한 값들을 가져오게끔 변형한 예입니다. 이는 검색에 사용되는 키가 not operator인 경우 변형 할 수 있는 한가지 방법이 되겠습니다.
◦select * from emp where ename like '김%' and job <> 'sales';
→(1)select * from emp where ename like '김%‘
MINUS
select * from emp b where b.job ='sales';
→(2)select * from emp a where a.ename like ‘김%’ and NOT EXISTS(
select * from emp b where a.ename=b.ename and b.job='sales');
(1)번은 앞에서와 같은 변형방법으로 이것의 검색 횟수는 (자료가 n튜플 이라면) 2n번이 되겠습니다. 그에 비해 (2)번은 둘다 full scan이 일어나서 검색 횟수는 n2이 되겠죠.
4) NULL, NOT NULL의 경우
◦select * from emp where ename si not null;
→select * from emp where ename > '';
ename이 문자형일 경우에 사용하는 예입니다.
◦select * from emp where comm is not null;
→select * from emp where comm > 0;
comm이 number형일 경우 사용하는 예입니다.
◦select * from emp where comm is null;
→이건 full scan이 필요하겠죠. 아니면 null 값만을 가지고 index를 생성할수도 있으나 그만큼의 null이 있다면 그 외의 경우에서 빼는 방식이 더 빠르겠죠.
많은 사람들이 NULL을 정확하게 이해하지 못하고 사용하기를 꺼려합니다. 물론 그렇지 않은 분들도 많으시겠지만.... ^^;; 일단 하나만 기억 하고 넘어가야 할 것은 NULL이 탐색의 키로 이용될 경우 무조건 full scan이 발생하므로 이용시 많은 주의가 필요합니다. 그럼 NULL대해서 알아보죠.
NULL은 테이블내에서 공백으로 표시 됩니다. 특징으로는 다음과 같죠
∘ 어떤 값보다 크지도 않고 작지도 않다.
∘ 그러므로 어떤 값과 비교될 수 없다.
∘ 즉, NULL과의 연산결과는 NULL이 된다.
대부분의 사람들이 세 번째를 놓치게 되는 경우가 많이 있습니다.
즉 a,b중 하나가 NULL이면 avg(a+b)와 avg(a)+avg(b)는 모두 결과가 NULL이 됩니다.
그러나 sum(a)의 경우는 NULL값을 참여시키지 않기 때문에 정확한 값이 나오죠.
그러면 이러한 NULL을 어떻게 이용 할 것인가?
일단 조건이 있습니다. 특정한 data type이 테이블 내에서 많은 경우 아울러 full scan이 꼭 필요한 경우 이 data type을 NULL로 지정합니다. NULL은 공백으로 표시 되기 때문에 기억장치에서 공간을 차지 하지 않습니다. 따라서 NULL로 지정한 만큼 공간을 절약할 수 있겠죠. “그게 얼마나 된다고 굳이 NULL로 지정합니까?” 이렇게 물으신다면 만약 테이블 내에서 남여를 구분하는 column이 있다고 가정하면 남과 여중 한 값을 NULL로 한다면 일단 50%는 아끼는 셈이 되죠. 그양이 비록 작을 지라도. 따라서 이 경우에도 손익을 생각하셔서 적용하시면 됩니다.
또 다음과 같은 경우에 NULL을 사용합니다.
∘ 미확정 값을 표현하고자 할 때
∘ 특정 값이 지나치게 많고 나머지 값만 주로 인덱스로 액세스 하고자 할때(위의 경우와 인덱스적용하여 공간과 탐색시간을 줄여주는 방법입니다.)
∘ 결합인덱스의 구성컬럼이 된다면 NOT NULL로
∘ 입력 조건값으로 자주 사용되면 NOT NULL로
마지막의 두가지 경우는 앞으로 나올 예정이므로 따로 설명하지 않겠습니다.
5) Optimizer의 취사선택으로 index scan이 발생되지 않는 경우를 앞서 살펴 보았습니다. 그러면 어떻게 해야 Optimizer가 제대로 index를 적용하도록 할까요? 이것을 알기위해서 먼저 Optimizer의 역할에 대해서 알아 보겠습니다.
Optimizer의 목표는 select, update, insert, delete문을 최소한의 프로세싱 시간과 최단 시간의 I/O를 사용하여 실행 시키도록 하는 것이다. 이를 위해 실행계획을 세우고 문장을 실행하기 전에 가장 효과적인 계획을 선택하는 일을 한다. 이를 위해 Optimizer는 실행계획중 규칙기반 접근법(rule-based approach) 또는 비용기반 접근법(cost-based approach)중 하나를 선택한다. 그러나 Optimizer역시 사람이 만든것이라 완벽하게 처리할 수 없다 따라서 만물의 영장인 사람이 정확한 정보를 주어 신속하게 처리하도록 도움을 주어야만 한다. 그럼 앞에서 말한 두가지 접근법에 대해서 알아 보도록 하겠습니다.
①Rule-Based방식
여러개의 가능한 경로를 찾아서 이미 정해져 있는 Rank를 기준으로 서로의 비용을 비교하고, 이를 토대로 가장 효율적인 것을 선택한다.
옵션
액세스경로(Access Path)
1
ROWID에 의한 단일 행 접근
2
클러스터 조인(Cluster Join)에 의한 단일 행 접근
3
Unique key 또는 Primary key를 사용하는 Hash Clushter key에 의한 단일행 접근
4
Unique key 또는 Primary key에 의한 단일행 접근
5
Cluster Join
6
Hash Cluster key
7
Indexed Cluster key
8
복합키(Composite key)
9
단일 컬럼 인덱스(single-column indexes)
10
인덱스 컬럼에서의 바운드 범위 조회
11
인덱스 컬럼에서의 언바운드 범위 조회
12
sort-merge join
13
인덱스 컬럼의 MAX 또는 MIN값
14
인덱스 컬럼에서의 order by 사용
15
full-table scan
②Cost-Based방식
가장 효율적인 실행계획을 선택하기 위해서 데이커베이스의 통계자료를 사용한다. 오라클 RDBMS사용시 ANALYZE명령어를 사용하면 테이블, 클러스터, 인덱스 등의 통계자료들이 수집, 저장 된다. Cost-Based방식은 이렇게 모인 데이터를 사용한다.
③ANALYZE명령어
Cost-Based방식에 사용할 통계 데이터를 모아준다. 또한, 이 명령어는 다음과 같은 목적으로도 사용된다.
Function
설 명
통계데이터 수집
Table, Cluster, Index등에 관련된 자료들을 모아 Cost-Based Optimization에 사용한다.
데이터 무결성 확인
Table, Cluster, Index등의 무결성 확인 작업을 한다.
Chained-row통계
Table, Cluster에 있는 데이터 체인행(Chained row)에 대한 통계 자료 수집
그럼 Optimizer가 어떻게 작동하는지 몇가지 예를 들어 보겠습니다.
∘select * from emp where ename like 'ab%' and empno='7890';
→둘다 index일 때 값이 더욱 확실한 empno index만 사용합니다.
∘select * from emp where ename like 'ab%' and job like 'sa%';
→ename or job index중 하나만 사용, 혹은 full scan(이 경우엔 index merge라고 함)
∘select * from emp where empno > '10';
→이와 같은 경우는 특정한 값을 찾는 것이 아니라 테이블 전체를 탐색해야 하므로 full scan선택이 비용이 덜듭니다. 따라서 full scan 선택.
∘select /* INDEX(emp job_IDX)*/ from emp where ename like 'ab%' and job like 'sa%';
→만약 데이터의 분포도나 탐색빈도수가 job index가 월등히 많이 사용된다면 프로그래머가 강제적으로 우선순위를 높여줄수 있는데 이것이 바로 HINT라고 합니다. 형식은 위와 같이 쉽운 편이지만 테이블에 들어가는 데이터들에 대한 지식이 우선되야 하겠지요.
<Hints에 관하여...
Hints를 통해서 optimizer에게 알려줄수 있는 정보는 다음과 같습니다.
-SQL연산을 위한 Cost-based접근 방식의 목표
-index보다 더 효과적인 Scan방식
-Join순서
-병렬연산 순서
문법
/*+comment */ →‘+’ 다음에 나오는 내용이 Hints라는 것을 Optimizer에게 알려준다.
이정도만 이해하고 넘어가도 될 것 같습니다.
▸Index의 활용 및 적용기준
“index를 언제 사용 할 것이냐?” 문제가 되겠지요. 절대적인 기준은 아니지만 대개의 경우 적용할 만한 적용 기준은 다음과 같습니다.
-6블록 이상의 테이블에 적용(6블록 이하는 연결고리만)
-컬럼의 분포도가 10-15%이내인 경우 적용
-분포도가 범위내이더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 컴토할 것
-분포도가 범위 이상이더라도 부분범위 처리를 목적으로 하는 경우에는 적용
-인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점 10-15%이내)
-질의에서 선택된 column의 값이 동일한 Rows들은 그 Table에 할당된 data block에 균일하게 분산되어 있을 때
-Table의 row는 질의되는 column에 대하여 불규칙적으로 분포
-Table에 할당된 각 data block은 최소한 10개의 row를 포함할 때 적용
-Table은 상당히 작은 수의 column을 가질 때
-Table에 대한 대부분의 질의들은 비교적 단순한 WHERE절일 때
-Cache hit ratio는 낮고 operating system cache는 없을 때
<분포도 = 1/컬럼값의 종류 * 100 = 컬럼값의 평균 로우수/테이블의 총 로우수 * 100
대개 앞의 것을 많이 이용합니다. 예를 들어 5가지 데이터가 들어있는 컬럼의 분포도는 20%이다.
<부분범위 처리란?
먼저 전체범위 처리에 대해서 알아보면 SQL문에서 group by문이나 order by 문의 경우 테이블의 처음부터 끝까지 전제적으로 정렬이 이루어 져야 한다. 이러한 경우를 전제 범위 처리라 한다. 이와는 반대의 경우를 부분범위 처리라하고 튜닝시 되도록 전제범위처리를 피하는 것이 바람직하다.
<손익분기점 read하고자 하는 컬럼스/access해야할 컬럼수 * 100
손익분기점이 높으면 hit ratio가 낮음을 의미하고, 손익분기점이 낮으면 hit ratio가 높다.
지금까진 single index만을 살펴 보았습니다. 그러면 결합인덱스에 대해서 알아 보겠습니다.
결합 인덱스는 말그대로 2개이상(오라클에선 16개까지 가능)의 컬럼을 가지고 index를 생성하는 경우를 말합니니다.
▸결합인덱스의 장점
-좋은 분포도 : 나쁜 분포도를 가진 column을 결합한 결합 인덱스가 더 좋은 분포도를 가질 수 있다.
-저장 공간의 효율성 : 한 질의에 의해 선택된 모든 칼럼이 결합인덱스에 있을 경우, table을 access하지 않고 결합 인덱스 만으로 원하는 값을 가져올 수 있다. 그러나 그만큼의 디스크나 메모리에 부하가 많이 걸릴 것이다.
[예] select empno from emp where empno='123';
이 경우는 특별히 테이블을 access하지 않고 index만으로 해당값을 출력할수 있다. 따라서 검색속도가 비약적으로 빨라지는 효과를 볼 수 있습니다.
▸결합 인덱스의 column선택을 위한 지침
-각 column의 분포도 보다 결합 인덱스에서 결합된 분포도가 더 좋을 경우
-여러 질의에서 하나이상의 칼럼값을 가진 칼럼의 동일한 집합을 질의할 경우, 이들 모든 칼럼을 포함하는 결합 인덱스 생성을 고려한다.
▸결합 인덱스 구성시 column순서 배치를 위한 지침
-WHERE절에 사용된 칼럼을 선행부분으로 만들기 위한 결합 인덱스를 생성
-칼럼의 일부가 WHERE절에서 자주 사용될 경우 =>자주 select되는 column을 선행부분올 만들어서 이 column만으로 인덱스를 사용할 수 있도록 한다.
-모든 칼럼이 WHERE절에서 동일하게 사용되면 =>질의 성능을 개선하기 위하여 CREATE INDEX statement에서 분포도가 좋은 순서대로 배열
-모든 칼럼이 WHERE절에서 동일하게 자주 사용되지만 데이터가 한 column에 대해 물리적으로 정렬되어 있으면 =>그 column을 결합 인덱스의 첫번째로 구성
-‘=’을 사용할 땐 선택범위가 좁은 것을 앞에 놓는다.
<in을 이용한 access효율 향상. (in은 ‘=’의 의미를 가지고 있다.)
예) select * from tab1 where col1='a' and col2 between '111' and '112';
→select * from tab1 where col1='a' and col2 in('111', '112');
여기서 col2의 값이 적을수록 후자의 속도가 빨라진다. 물론 보통의 경우에도 후자가 빠르다.
일반적으로 between, like문이 들어가는 SQL문은 in문으로 대체해 준다.
[주의] 만약 index가 (a+b+c)로 이루어져 있을 경우 where조건에서 a, a+b, a+c, a+b+c가 나오면 index적용이 가능 하지만, b, b+c가 나오면 index적용이 불가능하다. -> B*Tree에서 검색이 불가능 하기 때문에.. 따라서 결합인덱스의 첫 번째 컬럼이 꼭 나와야 index를 적용할 수가 있다. 아울러 대개의 결합인덱스는 최고 5개까지가 적당함. 그럼 간략히 정리를 해보겠습니다.
<인덱스 선정 절차
-해당 테이블의 액세스 유형조사
-대상 컬럼의 선정 및 분포도 분석
-반복 수행되는 액세스 경로의 해결
-클러스트링 검토
-인덱스 컬럼의 조합 및 순서의 결정
-시험생성 및 테스트
-수정이 필요한 애플리케이션 조사 및 수정
-일괄 적용
<액세스 유형의 조사(설계단계)
-반복 수행되는 액세스의 형태를 찾는다.
-분포도가 아주 양호한 컬럼의 찾아 액세스 유형을 찾는다.
-자주 넓은 범위의 조건이 주여되는 경우를 찾는다.
-자주 조건절에 사용되는 컬럼들의 액세스 유형을 찾는다.
-자주 결합되어 사용되는 경우를 찾는다.
-sort의 유형을 조사한다.
-통계자료 추출을 위한 액세스 유형을 조사한다.
<index의 활용(선정기준)
-분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상
-자주 조합되어 사용되는 경우는 결합인덱스 생성
-각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담.
-가능한 수정이 빈번하지 않는 컬럼
-기본키 및 외부키(조인의 연결고리가 되는 컬럼)
-반복수행(loop)되는 조건은 가장 빠른 수행속도를 내게 할 것
-실제 조사된 액세스 종류를 토대로 선정 및 검증
<index의 활용(고려사항)
-새로 추가된 인덱스는 기족 액세스 경로에 영향을 미칠 수 있음
-지나치게 많은 인덱스는 오버헤드 발생
-넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
-Optimizer를 위한 통계데이타를 주기적으로 갱신
-인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
-분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음.
-인덱스 사용원칙을 준수애야 인덱스가 사용됨
-조인(join)시에 인덱스가 사용여부에 주의
-데이터 변경시 자주 리빌드해주는 것이 좋다.
Ⅱ. Cluster
cluster는 테이블에 데이터를 저장하는 방식입니다. 데이터를 저장하는데 있어서 클러스터를 사용할 것인지의 여부는 SQL문장과는 아무런 관계가 없으면, 단지 테이블을 저장하는 방법에 적용됩니다. cluster는 다수 개의 테이블을 하나의 오라클 블록에 저장하는 메커니즘이며, cluster index를 꼭 필요로 합니다. 테이블을 clustering하는 순서는 다음과 같습니다.
①cluster를 생성한다.
②cluster index를 생성한다.
③cluster table들을 생성한다.
그럼 cluster생성 문법을 알아 보겠습니다.
CREATE CLUSTER cluster(
column data_type, ...
)
[PCTUSED int]
[PCTFREE int]
[INITRANS int]
[MAXTRANS int]
[SIZE int [K or M]]
[TABLESPACE tablespace]
[STORAGE storage_clause]
여기서 주의 해야 할 것은 SIZE로 이것은 하나의 클러스터 키와 그 데이터를 함께 저장하는 데 필요한 스페이스의 크기를 byte단위로 지정한다. 즉 SIZE는 하나의 데이터 블록에 같이 저장될 쉬 있는 클러스터 키의 총수를 결정 짓는 옵션이다.
[예]
①create cluster cluster_t1_t2(
idnum number(3)
)
size 400
tablespace data1
storage (initial 30k);
②create index ind_cluster_t1_t2(
on cluster cluster_t1_t2
tablespace idx1;
③create table t1(
name varchar2(10),
hire_date date,
idnum number(3)
)
cluster cluster_t1_t2 (idnum);
create table t2(
name varchar2(10),
idnum number(3)
)
cluster cluster_t1_t2 (idnum);
이렇게 지정하면 테이블 t1과 t2의 데이터는 같은 데이터 블록에 저장됩니다. 그럼 이제 본론으로 들어가겠습니다.
이해를 쉽게하기 위해 앞에서 보았던 index와 cluster index의 차이 점을 짚어보면 일반 index는 rowid를 가지고 있는 반면 cluster index는 block의 header를 가지고 있습니다. 따라서 보통의 경우엔 일반 index가 빠른 속도를 냅니다. 그러나 중복 데이터를 많이 가지고 있는 컬럼이 있는 테이블의 경우 그 성능이 훨씬 높게 됩니다. 물론 I/O이용이 적으면서 이러한 효과를 볼 수 있다니 얼마나 좋습니까. 따라서 앞에서 다루었던 index와 마찬가지로 cluster 역시 해당 테이블에 대한 정확한 이해를 바탕으로 이루어 져야 합니다. 그럼 정리해 보겠습니다.
<cluster의 특징
-지정된 컬럼값의 순서대로 로우를 저장시키는 방법
-하나 혹은 그 이상의 테이블을 같은 클러스터내 저장 가능
-엑세스 기법이 아니라 액세스 효율향상을 위한 물리적 저장기법
-검색 효율을 높여주나 입력, 수정, 삭제시는 부하증가
-분포도가 넓을 수록 오히려 유리(인덱스의 단점을 해결 5~7배)
-분포도가 넓은 테이블의 클러스터링은 오히려 저장공간 절약
마지막 것은 좀 이해가 안되는 부분이요. 이유인 즉슨 오라클 내부에서 동한건에 대해 나머지 값들은 저장이 안된다고 합니다. 따라서 그만큼의 저장 공간이 절약되겠죠.
<cluster의 활용(선정기준)
-6블록 이상의 테이블
-다량의 범위를 자주 액세스 해야 하는 경우
-인덱스를 사용한 처리가 부담되는 넓은 분포도
-여러 개의 테이블이 빈번한 조인을 일으킬 때
-반복 컬럼이 정규화 작업에 의한 어떨수 없이 분할된 경우
-UNION, DISTICT. ORDER BY, GROUP BY가 빈번한 컬럼이면 고려해 볼 것
-수정이 자주 발생하지 않는 커럼
-처리 범위가 넓어 문제가 발생되는 경우 단일 테이블 클러스터링
-조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링
<cluster의 활용(고려사항)
-데이터 처리(입력, 수정, 삭제)에 오버헤드 발생 주의
-인덱스로도 충분한 범위는 클러스터링 효과가 없음
-클러스터 키는 수정이 빈번하지 않을 것
-각종 액세스형태에 대해 인덱스와 적절한 역할 분담
-클러스터링은 기존의 인덱스의 수를 감소시킴(인덱스 재구성)
-클러스터 SIZE 인자가 중요
-클러스터 키별 로우 수의 편차가 심하지 않을 것
-클러스터에 데이터 입력시 로우가 적은 테이블부터 실시할 것
-클러스터링된 테이블 조인시 로우 수의 역순으로 from절에 기술할 것
-클러스터 키를 첫 번째로 하는 인덱스는 생성하지 말것(optimizer에 의해 선택 문제 발생)
<index와 cluster의 비교
Ⅲ. VIEW
보통 뷰를 이용는 경우는 보안이나 복잡한 쿼리를 피하기 위해 사용 하는 경우가 많이 있습니다. 그럼 과연 튜닝에는 어떻게 쓰일수 있을까요? 일반적으로 튜닝시에는 다음의 두가지 목적을 위해 사용합니다.
<수행속도 향상을 위한 뷰
-수행 속도 향상을 위해 미리 튜닝한 select문을 뷰로변환 시켜 사용
-M:1조인의 연결회수를 감소 시키기 위해 먼저 group by된 뷰를 만들고 그 뷰와 조인을 일으키게 하기 위한 뷰
-특정 client tool의 문제 해결을 위한 뷰
-수행 속도에 심한 영향을 주는 넓은 범위 처리나 특정 컬럼의 조건검색을 막아 악성검색을 방지하기 위한 뷰
-특정한 절차로 수행시키기 위해 뷰의 select list에 suppressing, hint등을 사용한 뷰
설명이 필요 없으리라 생각됩니다.
<SQL 기능 향상을 위한 뷰
-불규칙적인 sort가 필요한 경우
-group by결과를 다시 가공하고자 하는 경우(group by는 어쩔수 없이 full scan이 발생 합니다. 따라서 그에따른 비용도 많이 듭니다. 따라서 미리 group by결과를 뷰로 가지고 있으면 그만큼의 비용을 절감 할 수 있겠지요)
-서로 다른 테이블의 group by결과를 같은 줄에 맞추려는 경우
-ROWNUM을 이용한 특별한 처리
-각 row에 있는 값들(예:일자)간의 가공처리(예:기간산정)
-각각의 소계와 그 내역을 하나의 SQL로 처리
-복잡한 outer join의 해결을 위한 뷰(outer join은 만족하는 레코드뿐만 아니라 만족하지 않는 레코드들도 출력을 해줍니다. 따라서 만족하지 않는 값으로 찾고자 할 경우 full scan이 발생하여 전제 SQL문의 속도를 늦춰 줍니다. 이러한 경우 뷰를 이용하여 만족하지 않는 레코드를 따로 처리해 놓으면 훨씬 빠른 속도를 얻을수 있습니다.)
-테이블은 row를 가지지 않으나 뷰는 row를 가지도록 한 뷰
-SQL*From의 execute_query를 활용하기 위한 뷰
-기타 SQL의 기능 확장을 위해 원하는 임의의 집합이 필요한 경우
-코드성 테이블을 미리 조인한 후 fact table과 다시 조인한다.(코드성테이블이란 master file과 같은 의미로 삽입이나 삭제가 거의 되지 않고 참조위주의 테이블을 말합니다. 예로 이자율테이블이나 할인율 테이블 같은 것이 되겠죠, 그리고 fact table은 사용자가 입력한 테이터 정보를 모두 가지고 있는 테이블입니다. 보통의 테이블들이 해당 되겠죠. 아울러 덩치도 아주 큽니다.)
-조인시 데이터 범위가 작은 테이블을 먼저 조인한 다음 점점 큰 순서로 조인하는 것이 훨씬 빠른 속도를 냅니다.
Ⅳ. 부분범위 처리(Partial Range Scan)
그럼 일단 그림을 먼저 보겠습니다
<부분범위 처리 방법
-조건을 만족하느 전제 집합이 아닌 일부분만 access
-data량이 많아도 performance에 지장이 없고, 오히려 향상
-index나 cluster를 적절히 활용한 sort의 대체(order by사용을 대체)
-max처리
-table은 access하지 않고 index만 사용하도록 유도
-exists의 활용
-rownum의 활용 (rownum- 1,2차 가공시 나오는 건수)
-query를 이원화 하여 일부분씩 scan하도록 유도
-stored function을 이용
[예]
여기서 첫 번째 문장에서는 index가 ymd만으로 이루어졌고, 두 번재 문장에서는 index가 ymd+item으로 이루어 졌다고 가정합니다.
∘select * from product where ymd='951023' and item like 'ab%' order by ymd, item;
→select * from product where ymd='951023' and item like 'ab%';
sort를대체하는 예
∘select orddate, custno from orddate between '940101' and '941130' order by orddate desc;
→select --+index_desc(a orddate) orddate, custno from order1t a where orddate between '940101' and '941130';
∘select orddate, custno from order1t where orddept like '7%' order by orddate desc;
→select --+index_desc(a orddate) orddate, custno from order1t a where orddept like '7%' and orddate <='991231';
<index생성시 아무 옵션도 주지않을 경우 default로 ASC옵션이 적용되어 생성된다.
따라서 DESC로 출력을 원할 경우 다음 두방법중 하나를 이용하면 된다.
1. 뒤에서부터 검색하거나
2. index create시 desc로 생성시켜줘야 속도 향상을 얻을 수 있다.
max처리하는 예 (전자는 index(dept), 후자는 index(dept+seq)로 index생성 되어 있음)
∘select max(seq)+1 from product where dept='12300';
→select /*+index_desc(a index1)*/ seq+1 from product a where dept='12300' and rownum=1;
rownum이용 예
∘select count(*) into:cnt from item_tab where dept='101' and seq>100 ......
→select 1 into:cnt from item_tabl where dept='101' and seq>100 and rownum=1...
1:M join의 부분범위 유도 예
∘select x.cust_no, x.addr, x.name, ....... from cust x, reqt y where x.cust_no=y.cust_no and x.cust_stat in ('a', 'c', 'f') and y.un_pay >0 group by x.cust_no having sum(y.un_pay) between :val1 and :val2;
→select cust_no, addr, un_pay, .... from (select cust_no, addr, unpay_sum(cust_no) as un_pay, ......... from cust where cust_stat in ('a', 'c', 'f')) where un_pay between :val1 and :val2;
Ⅴ. 조인 메커니즘의 이해
조인의 경우 사용히 매우 신중을 기해야 하는 것중의 하나입니다. 특히 조인 컬럼이용되는 컬럼의 선정시 특히 많은 주위를 해야 optimizer의 실행 순서 선택시 우리가 원하는 수행능력을 보장 받을 수 있습니다. 그럼 조인시 수행속도에 영향을 끼치는 것들에 대해서 한가지씩 알아 보겠습니다.
<driving의 영향
<Join순서의 영향
<index의 영향
양쪽에 index
한쪽에 index
①
tab1
range scan
③
tab1
range scan
tab2
index scan
tab2
full scan
②
tab1
range scan
④
tab1
full scan
tab2
index scan
tab2
range scan
①,②의 경우는 일량의 변화가 없다.
③의 경우 n*m번 비교가 일어난다.(최악의 경우) 이런 경우 ④과 같이 변형하면 조금이나마 성능 향상에 도움이 된다.
<Join시 유의사항
-join되는 key column의 data type을갖게 한다.
-join되는 key column을 index로 만들어 준다.(절대 변형되지 않도록한다.-변형시 full scan발생)
-두 table간에 join이 맞지 않을 경우(한쪽에만 index가 있는 경우) driving table을 full scan table로 설정하는 것이 최악의 경우를 방지 할 수 있다.
-두 index에 대한 분포도가 같은 경우 from절의 오른쪽에 있는 table을 driving으로 삼는다.
-check조건이 되는 테이블의 범위는 넓을 수록 좋다.(사용자에게 테이블의 내용을 빨리 display 할 때 보다 빨리 전송단위 씩 보내줄수 있기 때문에)
<join과 loop query의 속도 비교.
-loop query가 빠른 경우 → 부분범위 처리가 발생 할 경우
-부분범위 처리가 join과 loop query 둘다 발생하면 (전체가 부분범위 처리인 경우) → join이 빠르다.
-전체 범위 처리가 발생한 이유가 모든 table에 있으면 → join이 빠르다.
-연결되는 table중 하나를 부분범위로 바꿀수 있다면 → loop query가 빠르다.
<Loop query?
말 그대로 query를 loop로 돌려 특정 값을 다음 테이블내에서 찾는 방법입니다. 보통의 SQL문에서 사용하는 경우는 거의 없고 batch 혹은 pl_SQL 사용시 많이 이용됩니다.
Ⅵ. Trace
하하 이젠 분석도구라 불리는 놈들을 보겠습니다. 어디가 잘못 됐는지 알아야 고칠거 아닙니까? 따라서 가장 중요하다고 생각합니다. 그런데 어렵더군요. 저 역시 교육 받을 때 이 부분에서 거의 헤맸습니다. ^^;; 여러분은 그러시지 않기를 빕니다.
<SQL Trace?
SQL문을 튜닝하기 위해서는 현제의 SQL문이 어떻게 수행되는 가를 정확하게 파악해야 할 필요가 있습니다. 그럼 간단하게 Trace의 사용 목적에 대해서 알아보죠.
< Trace 파일은 시스템을 튜닝하는데 필요한 아주 유요한 정보(cpu Time, 총수행시간, I/O횟수 등등 아래 자세히 설명)를 제공한다.
< SQL문의 실행통계를 Session별로 모아서 Trace 파일을 만든다.
- SQL Parsing, Execute, Fetch를 수행한 횟수
- CPU Time, Elapsed Time(총 경과시간)
- Disk(물리적), Memory(논리적) I/O 수행한 횟수
- 추출된 Row의 수
- 라이브러리 캐쉬 miss 수
- Parse Count
그럼 trace를 한번 만들어 보자구요. 물론 슆지는 않습니다. 또한 Trace를 생성하는 동안 시스템 전체적인 수행성능은 20%~30% 정도 감소합니다. 따라서 운영중인 DB의 경우에는 되도록 사용을 금하기소 되도록 개발용 장비에서 실행하기기 바랍니다.
먼저 SQL_TRACE를 생성하려면 다음과 같은 파라미터들을 INIT.ORA에 지정해야합니다.
- CREATE INDEX문에서 NOSORT Option의 사용
- TIMED_STATISTICS=TRUE → 시간 통계를 모을 수 있게 한다.
- SQL_TRACE=TRUE → Session을 종료하는 모든 사용자들의 Trace를 수행한다.
- USER_DUMP_DEST=directory path → SQL_TRACE가 Trace 파일을 저장하는 디렉토리를 지정한다. default는 시스템 덤프(dump) 디레토리이다.(예; oracle_home/rdbms/log)
-MAX_DUMP_SIZE=number → Trace 파일의 물리적인 크기를 바이트 단위로 지정할 수 있게 한다.
[주의] SQL_TRACE는 공간이 부족하면, 완전한 출력이 되지 않습니다. 따라서 디스크 공간을 확보하신후 실행 시키싶시오, 아울러 주기적으로 필요 없는 파일들은 삭제해 주어야 합니다.
[예]
timed_statistics = true # if you want timed statistics
user_dump_dest = /oracle8/app/oracle/product/8.0.3/rdbms/log
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
-SQL*Plus에서는 다음과 같이 세션을 초기화시켜야 합니다.
SQL>alter session set sql_trace=true;
-optimizer goal의 변경은 다음과 같습니다.
SQL>alter session set optimizer_goal=rule;(optimizer goal을 정의)
-RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION Produce사용
SQL>execute RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(11,6,TRUE)
여기서 11은 sid, 6은 Serial number(둘다 v$session에서 확인), TRUE는 Trace enable입니다.
이렇게 설정해 두면 실행되는 질의문에 대해서 트레이스 파일이 생성된다. 생성되는 위치는 initSID.ora에서 정의한 user_dump_dest 디렉토리에 *.trc형태로 생성됩니다.
그럼 이제 Trace 파일을 보기만 하면 됩니다. 그러나 생성된 트레이스 파일은(*.trc) 바로 볼 수 없습니다. 시험삼아 한번 vi에디터로 열고 보셔도 물론 됩니다. 그러나 수많은 문자들 가운데서 우리가 원하는 정보를 찾기란 너무 어렵습니다. 그래서 tkprof라는 유틸리티를 사용하여 생성된 트레이스 파일을 분석이 가능한 형식으로 전환해줘야 합니다. 물론 기본적으로 오라클에서 제공하는 유틸리티입니다.
이것은 이미 생성된 트레이스 파일이나 트레이스 파일을 생성하고 있는 중에도 tkprof를 수행시킬 수 있습니다. 트레이스 파일은 SQL문에 대한 실행계획뿐만 아니라 실행시간, 다양한 옵션을 이용하여 분석하기 쉬운 형태 등의 정보를 보여줍니다.
그럼 tkprof란 놈은 어떻게 실행 시킬까요?
Usage: tkprof tracefile outputfile [explain=user/passwd] [table=schema.tablename]
[print=integer] [insert=filename] [sys=yes/no] [sort=option]
tracefile : 생성된 트레이스 파일명
outputfile : tkprof가 출력하는 텍스트 파일명(디폴트로 확장자가 .prf임)
explain=user/passwd : 해당 트레이스 파일이 수행된 세션의 사용자 및 패스워드
table=schema.tablename : 실행계획(execution plan)을 저장할 TKPROF 임시 테이블의 이름
print=integer : 트레이스 파일별로 출력시킬 SQL문의 수
aggregate=yes|no
insert=filename : List SQL statements and data inside INSERT statements.
sys=yes/no : TKPROF does not list SQL statements run as user SYS.
record=filename : Record non-recursive statements found in the trace file.
sort=option : Set of zero or more of the following sort options:
< sort option의 종류 >
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
여기서 SQL문을 실행하는데 걸린 CPU시간을 보여주는 EXECPU가 가장 실용적이다. 만약 init.ora 파라미터를 TIMED_STATISTICS=FALSE로 지정했을 때는 수행 중에 액세스된 블록 수를 보여주는 EXEQRY가 가장 실용적이다.
[예] tkprof ccdb_ora_1124.trc 1124.txt explain=scott/tiger
그럼 이제 우리가 눈으로 보기에 꽤 괜찮은 파일이 만들어 집니다.
- tkprof는 정형화된 리스트(출력파일)를 생성합니다..
- 생성된 파일에는 다음과 같은 내용들을 포함하고 있습니다.
call
count
cpu
elapsed
disk
query
current
rows
Parse
1
0.01
0.01
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
1
0.00
0.02
2
3
0
1
total
3
0.01
0.03
2
3
0
1
보기 좋게 표로 만들었습니다만 실제론 이렇게 출력되진 않습니다. 그럼 하나 하나 살펴 보지요.
- parse
·SQL문이 파싱되는 단계에 대한 통계이다. 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함된다.
·단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PRO*SQL에서 보존커서(Hold cursor)를 지정한 경우에는 한번만 파싱된다.
- execute
·SQL문의 실행 단계에 대한 통계이다. UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타난다.
· 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 fetch에는 아주 적은 값이 나타난다.
- fetch
·SQL문이 실해되면서 페치된 통계이다.
· 부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우(AGGREGATE, 전체집계, Count 등)는 주로 여기에 많은 값들이 나타나고 execute에는 아주 적은 값이 나타난다.
- count
·SQL문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수이다.
- cpu
·pares, execute, fetch가 실제로 사용한 CPU 시간이다.(1/100초 단위)
- elapsed
·작업의 시작에서 종료시까지 실제 소요된 총 시간이다.
- disk
·디스크에서 읽혀진 데이타 블록의 수
- query
·메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경 되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수이다.
·SELECT문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT 시에는 소량만 발생한다.
- current
·현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을 액섹스한 블록 수이다.
· 주로 UPDATE, INSERT, DELETE 작업시 많이 발생한다. SELECT 문에서는 거의 없으나 아주 적은 양인 경우가 대부분이다.
- rows
·SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수이다.
·서브쿼리에 의해서 추출된 로우는 제외된다.
·만약 SUM, AVG, MAX, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없다.
☞분석결과의 예
- execute, fetch의 횟수가 동일하다는 것은 SQL 수행시마다 기본키에 의해 한건씩만 처리되고 있다는 것을 의미한다. 만약 pares가 1인데 execurte와 fetch가 100이라면 루프가 100번 수행되면서(어프리케인션은 한번만 수행되고 SQL은 루프 내에서 반복수행되었다. 왜냐하면, 어플리케이션이 여러번 실행되었다면 비록 SQL이 실제 파싱하지 않고 Shared SQL Area에서 찾아 왔다고 해도 parse의 횟수는 증가되기 때문이다) 보관커서 상태의 SQL이 한 건씩을 추출한 상태이다. 이 경우의 SQL문은 'SELECT ... INTO ...'형식으로 사용되었을 것이다.
- parse가 1이고 execute가 1이며, fetch가 100이라면 SQL은 단 한번 수행되었고(루프 내에서 수행되지 않았음) 페치만 연속해서 100번을 수행한 것이다. 이 경우의 SQL문은 대개 'DECLARE CURSOR'로 선언한 SQL이 'FETCH ... INTO ...'에 의해 SQLCODE가 '1403'(Date Not Found)일 때까지 수행되었거나 부분범위 처리에 의해 일정 양만큼만 수행하고 멈추었을 때이다.
- parse : execute : fetch의 비율은 공통 작업이 여러번 수행되면 그 배수로 나타난다. 예를 들면 parse : execute : fetch가 10 : 10 : 1000인 경우는 1 : 1 : 100인 작업이 10번 수행되었다는 것을 의미한다.
- fetch가 10인데 rows가 100이라면 운반단위가 10인 다중처리(Array Processing)를 사용하여 한번 페치마다 10건의 로우가 추출되었음을 의미한다.
- 트레이스의 중간부분에 'Misses im library cache during parse : 1'이라는 문장이 있다. 이것은 공유 SQL 영역에서 파상된 결과를 찾지 못하여 실제 파싱작업을 하게 되었다는 것을 의미한다.
- 최종적으로 추출된 로우의 수는 적으나 많은 CPU 시간이 소요되었다면 이것은 분명히 적절한 액세스 경로로 수해되지 않았음을 의미한다.
- CPU 시간과 ELAPSED 시간의 차이는 적을수록 좋다. 만약 CPU시간에 비해 ELAPSED 시간이 훨씬 많다면, 그 원인은 다음 중 하나일 가능성이 높다. 즉 주변의 다른 세션에서 많은 부하를 발생시켜 시스템 전체에 부하가 많이 걸려있는 경우나 혹은 어플리케이션의 문제이거나 다량의 데이타 처리에 따른 I/O 병목현상이 발생한 경우
- disk, query, current의 숫자는 적을수록 좋다. 이 숫자들이 커다는 것은 메모리 공유영역의 적중률(Hit Ratio)이 낮다는 것을 의미한다.
- Overall totals For All Statements에서 적중률 계산은 다음과 같다.
(Execute 'disk' + Fetch 'Disk')/(Execute 'query' + Execute 'current' + Fetch 'query' + Fetch 'Current') * 100
이 값이 10%이상이라면 메모리 캐쉬에서 데이타를 찾는 비율(적중률)이 너무 낮은 것이다.
- 다음은 아주 빠른 응답이 요구되는 온라인 프로세싱 시스템의 경우에서만 적용되는 규칙들이다.
모든 Execute 'CPU'가 1초보다 적어야 한다.
Parse 'CPU' 시간이 Parse당 0.01초보다 적어야 한다.
작은 테이블(200로우 이하)에서만 전체 테이블 스캔이 일어나게 한다.
sysdate만 찾아오거나, 오직 연산만 하거나, 'SELECT ... INTO ...'로 값을 복사하는 경우를 위해서 DUAL 테이블들을 불필요하게 사용하는 것은 모두 없앤다.
동시에 작업되는 SQL들은 가능한 PL/SQL을 사용한다.
조인시에 옵티마이져가 적절한 드라이빙 테이블을 선택하는지를 확인하거나, 여러개의 조건들 중에서 주(드라이빙)가 되는 조건들과 부(체크)가 되는 조건들을 확인한다. 또한 적적한 인덱스가 사용될 수 있는지를 확인하여 주조건의 처리범위가 넓지 않도록 항상 유의한다.
Ⅶ. Analyze
이 명령어는 Cost-Based방식에 사용할 통계 데이터를 모아 줍니다.
Function
설 명
통계데이터 수집
table, Cluster, index 등에 관련된 자료들을 모아 Cost-Based Optimizer에 사용한다.
데이터 무결성확인
table, cluster, index등의 무결성 작업을 한다.
Chained-row통계
table, cluster에 있는 데이터 체인행(Chained row)에 대한 통계 자료 수집
다음으로 문법을 보죠. 문법은 아래와 같습니다.
ANALYZE object-name operation STATISTICS
-object → TABLE, INDEX, CLUSTER 중에서 해당되는 오브젝트 종류
-name → 오브젝트의 이름
-operation
· COMPUTE : 각각의 값들을 정확하게 계산 따라서 옵션은 가장 정확한 통계를 얻을 수가 있지만 가장 처리속도가 느린 단점이 있습니다.
·ESTIMATE : 자료사전의 값과 데이타 견본을 갖고 검사해서 통계를 예상 이 방법은 덜 정확한 정보를 주지만 훨씬 처리속도가 빠릅니다.
·DELETE : 테이블의 모든 통계정보를 삭제
Analyyze와 관련된 데이터 사전 뷰를 정리 했습니다.
USER_INDEXS, ALL_INDEXS, DBA_INDEXS
USER_TABLES, ALL_TABLES, DBA_TABLES
USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS
출처 : http://skyforce.egloos.com/1844944
