[튜닝]
1. instance tuning
2. DB tuning
– partition tuning
– query tuning
– index 설계
가장 cost가 적게 드는 튜닝 방법이 인덱스 설계다.
[인덱스]
조회 성능을 높이기 위한 객체. 대신 DML성능은 index split 때문에 엄청 느려진다.
사용자가 조회하고자 하는 데이터의 위치를 찾아 해당 위치에 있는 데이터만 빠르게 접근 가능.
Oracle optimizer가 plan을 만드는 가장 중요한 판단 기준 (통계 정보, algorithm…)
** index split : index가 있을 때 dml을 했을 때 인덱스 구조가 변경돼서 속도가 느려지는 것.
ex) emp 테이블에서 7788 사번에 해당하는 직원의 정보를 찾는 경우
인덱스가 없을 때는 전체 테이블을 조회.
인덱스가 있을 때는 인덱스를 이용해서 7788이 저장된 위치(rowid)만 찾아 해당 블럭만 조회.
create table scott.emp_test
as
select * from scott.emp;
desc scott.emp;
desc scott.emp_test;
** CTAS로는 PK 세팅이 옮겨지지 않는다.alter session set statistics_level = all;
-- full scan)
select *
from scott.emp_test
where empno=7788;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- index unique scan)
select *
from scott.emp
where empno = 7788;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- index 생성)
create index scott.idx_emp_test_empno on scott.emp_test(empno);
select *
from scott.emp
where empno=7788;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced')); 
index range scan 발생 -> unique index가 아니므로
-- unique index로 재생성)
drop index scott.idx_emp_test_empno;
create unique index scott.idx_emp_test_empno on scott.emp_test(empno);
select *
from scott.emp
where empno = 7788;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
index unique scan 발생 -> unique index를 생성했으므로
** index scan을 방해하는 조건
1. not between을 제외한 모든 부정형 조건 (!, not)
not between은 결국 A보다 작거나 B보다 큰 것을 의미하기 때문에 인덱스 스캔에 방해되지 않음.
2. 데이터 타입 오류
3. like 사용 시 맨 앞을 %나 _로 주는 경우 full scan을 함.
** student 테이블에서 남자만 출력하는 것을 인덱스로 빠르게 하기.
create index scott.idx_student_jumin on scott.student(substr(jumin,7,1));
select *
from scott.student
where substr(jumin,7,1) = '1'; -- 만약 그냥 1이라고 하면 내부적으로 filter(to_number())가 발생하면서 full scan을 하게 됨. 그래서 데이터타입을 일치 시켜야 됨
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
[인덱스 구조]
B TREE 인덱스
트리구조로 만든 인덱스.
몇 개의 블럭만 읽으면 된다는 것이 뚜렷하기 때문에 자주 사용함.
bitmap 인덱스
0, 1로 구분하게 만든 인덱스.
잘 사용하지 않음.
사용하더라도 범주가 정해져있는 경우에만 사용함.
만약 DML이 발생할 시 성능이 btree보다 훨씬 떨어진다. 그래서 잘 사용하지 않음.
[인덱스 종류]
unique 속성에 따라
1. unique index
컬럼 값이 unique한 경우만 생성 가능.
PK 생성 시 자동으로 생성됨.
문법 : create unique index 인덱스명 on 테이블명(컬럼1, 컬럼2, …);
index unique scan을 유도한다.
2. non unique index
보다 일반적인 인덱스.
index range scan을 유도. 절대 unique scan을 할 수 없다.
인덱스를 구성하는 컬럼 수에 따라
1. single index (단일 인덱스)
인덱스를 구성하는 컬럼이 단 한 개인 경우.
2. composite index (결합 인덱스)
인덱스를 구성하는 컬럼이 둘 이상인 경우.
인덱스의 구성 순서가 매우 중요!
– data set에서 특정 조건을 만족하는 row의 개수가 적을수록 selectivity가 높다. = cardinality가 낮다.
– cardinality : 어떤 rowset에 속하는 row의 개수.
– 선택도(selectivity)가 높은 컬럼이 선두 컬럼으로 배치되어야 한다.
– 조건절에는 반드시 선두 컬럼이 명시되어야 한다. 안 그러면 index range scan이 불가함.
-> 선택도가 높은 쪽을 선두 컬럼으로 배치해야지 첫 조건을 거쳤을 때 더 많은 값들이 필터링돼서 더 적은 양의 데이터를 가지고 진행할 수 있게 되기 때문이다.
예제) 결합 인덱스의 스캔 방식
-- 인덱스 생성)
일자를 선두 컬럼으로 하는 게 더 selectivity가 높기 때문에 일자, 업종 순으로 인덱스 만들거임.
create index scott.idx_deli_일자_업종 on scott.delivery(일자, 업종);
select bytes/1024/1024
from dba_segments
where segment_name = 'IDX_DELI_일자_업종'; -- 5MB
-- *로 조회해서 실제 데이터까지 내려가서 스캔한 경우)
select *
from scott.delivery
where 업종 = '음식점-족발/보쌈전문'
and 일자 = '20180201';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
-- 인덱스에 지정된 컬럼만 조회해서 실제 데이터까지 내려가지 않게 스캔한 경우)
select 업종, 일자
from scott.delivery
where 업종 = '음식점-족발/보쌈전문'
and 일자 = '20180201';
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));
다른 인덱스 스캔 유도 방법
인덱스끼리 어떤 게 좋은지 비교하는 상황일 때 등에서
1. 사용할 인덱스를 hint로 지정 – 개별 쿼리에만 적용
select /*+ index(d IDX_DELI_일자_업종_통화건수) */ 업종, 일자, 통화건수
from scott.delivery d
where 업종 = '음식점-족발/보쌈전문'
and 일자 = '20180201';2. 인덱스를 비활성화(invisible) 시키기 – 보다 광범위하게 적용
optimizer에 의해 해당 인덱스 활용을 못하도록 막는 조치
alter index scott.IDX_DELI_업종_일자_통화건수 invisible;[인덱스 관리]
1. index 조회
select owner, index_name, index_type, table_name, uniqueness, tablespace_name, status, degree, visibility
from dba_indexes
where 1=1
and owner = 'SCOTT'
and table_name = 'EMP'
and index_name = 'PK_EMP';
-- status : unusable 여부
-- degree : parallel 적용
-- visibility : visible 여부elect owner, index_name, index_type, table_name, uniqueness, tablespace_name, status, degree, visibility
from dba_indexes
where 1=1
and owner = 'SCOTT'
and table_name = 'EMP'
and index_name = 'PK_EMP';
-- status : unusable 여부 - optimizer는 볼 수 있는데 index 자체가 update가 중단된 느낌. 대용량 DML시에 무조건 해야된다.
-- degree : parallel 적용. 인덱스 생성 시 병렬 생성하는 경우 2 이상의 값을 가짐.
-- visibility : visible 여부 - optimizer가 못 보게 하는 느낌. 내부적으로 존재하므로 index split도 발생한다.2. index 병렬 생성
create index scott.idx_deli_일자_시간대 on scott.delivery(일자, 시간대) parallel 4;
select owner, index_name, index_type, table_name, uniqueness, tablespace_name, status, degree, visibility
from dba_indexes
where 1=1
and owner = 'SCOTT'
and index_name = 'IDX_DELI_일자_시간대';
-- 이대로 냅두면 select 날릴 때마다 4코어씩 먹기 때문에 cpu가 풀차게 된다.
-- 그래서 아래 쿼리로 noparallel해야됨.
alter index scott.idx_deli_일자_시간대 noparallel;3. index unusable
dml 발생 시 index 업데이트 후 DML 작업이 완료되는 구조 -> 대용량 dml 수행 시 성능 저하 발생!
데이터 변경이 발생하더라도 index의 변경을 중단하는 옵션이 unusable.
unusable의 반대로 만드는 쿼리는 rebuild. 조회 시엔 valid로 조회됨.
alter index scott.idx_deli_일자_시간대 unusable;
select owner, index_name, index_type, table_name, uniqueness, tablespace_name, status, degree, visibility
from dba_indexes
where 1=1
and owner = 'SCOTT'
and index_name = 'IDX_DELI_일자_시간대';
alter index scott.idx_deli_일자_시간대 rebuild;** 쌤 경험담 – 이관할 때 truncate를 치면 unsable한 index가 알아서 되살아난다(valid). 11g 때 얘기. 지금은 어떤지 모름.
4. index invisible
invisible : optimizer가 해당 index를 사용하는 실행계획을 만들지 못하도록 하는 설정.
visible : 다시 index를 사용하도록 하는 설정
alter index scott.idx_deli_일자_시간대 invisible;
select owner, index_name, index_type, table_name, uniqueness, tablespace_name, status, degree, visibility
from dba_indexes
where 1=1
and owner = 'SCOTT'
and index_name = 'IDX_DELI_일자_시간대';
alter index scott.idx_deli_일자_시간대 visible;