쿼리 성능 측정시 체크 리스트 정리

쿼리 성능을 측정하고자 할때 기본적으로 아래의 리스트를 체크해보도록 한다.

 

인덱스 스캔 관련 체크리스트

1. 인덱스 사용이 가능한가?

 - where 연락처 is null 처럼 null 조건만 있는 경우, 인덱스 사용이 불가능하다.

- 쿼리에서 참조하는 컬럼들은 인덱스 안에 저장되어 있어야 한다.

 

2. 인덱스 범위 스캔이 가능한가?

- 부정형 비교의 경우 인덱스 사용이 불가능하다.

    where 직업 <> '학생'

    where 직업 is not null

- 인덱스 컬럼을 가공한 경우 인덱스 사용이 불가능하다.

- 묵시적 형 변환을 한 경우 > 숫자형과 문자형이 비교될 때 숫자형이 우선시 되기 때문에 컬럼 타입과 맞춰주어야 한다.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26739954

2. 인덱스 기본 원리 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!

 

만약 바인드 변수 처리를 

select * from t1

where 1=1

and (  :v1 is null or ( :v1 is not null and t2 = :v2)) ;

라고 작성하면 인덱스를 탈 수 없어 성능저하가 발생한다. 그러므로 case 문을 활용하여 null 값을 처리해주어야 한다.

 

select * 

from t1 

where 1=1

and case when ( :v2 is null or (:v2 is not null and c1 =:v1)) then 1 end = 1;

 

이런식으로 case 문으로 처리해주어야 한다. 

 

인덱스 컬럼이 not null 이라면 nvl을 제거해주고, 아니면 함수 기반의 인덱스 (FBI) 를 생성한다 

 -> create index 주문x_01 on 주문( nvl(주문수량, 0));

 

 

3. 인덱스 스캔이 풀스캔보다 효율적인가?

 : 인덱스 스캔은 rowid에 의해서 데이터를 랜덤하고 single Block Read 전략을 가지는 액세스이고,

   테이블 풀스캔은 sequential 한 방식으로 multi Block Read 전략을 가지고 데이터에 엑세스 한다.

 만약 읽어야 할 데이터가 sequential하게 모여있지 않아 여러 블록으로 분산될 경우, Single Block 단위로 여러번 I/O가 발생되므로 성능의 저하를 가져온다. 이러한 경우 IOT, 클러스터 테이블, 파티셔닝 등을 통해 해결해야 한다.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26739960 

4. 테이블 Random 액세스 부하 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지

 

 

4. pk 인덱스로 조인하는가?

pk 인덱스로 NL 조인을 할 경우, driving table 에는 하나의 row 만 선택되게 된다. 하지만 inner table에 엑세스 될 때는 Random 엑세스 부하가 만만치 않다. 특히 Inner table 쪽 필터 조건에 의해 버려지는 레코드가 많다면 그 비효율은 매우 심각할 수 있다.

 

select /*+ ordered use_nl(d)*/

from emp e, dept d

where d.deptno = e.deptno

 and d.loc = 'NEWYORK'

 

인 경우에 loc 필터 조건에 의해 버려지는 레코드가 많아 비효율적이다. 하지만 PK에 인덱스를 추가하는 것은 데이터 모델에 영항을 주므로 함부로 실행할 수 없고 DEPTNO(PK) + LOC(Filter 조건) 으로 인덱스를 생성하는 것이 좋다.

 

http://wiki.gurubee.net/pages/viewpage.action?pageId=26740107 ㅇ에 

5. 테이블 Random 액세스 최소화 튜닝 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구

 

 

5. nl 조인의 inner table의 사이즈가 작고 반복적으로 lookup 하는가? 넓은 범위를 주로 검색하는가?

    데이터 입력과 조회 패턴이 서로 다른 테이블인가? (ex. 거래일자를 기준으로 특정 상품을 조회, 영업사원별 실적 조회 등 )

-> IOT 테이블을 사용한다. IOT 테이블은 모든 행 데이터를 리프 블록에 저장하고 있어 random access가 아니라 sequential 한 방식으로 데이터 접근이 가능하다. pk 컬럼 기준으로 선행 컬럼이 등치 조건이 아닌 경우 레코드들이 서로 흩어져 있어 많은 스캔을 유발하지만, Random Access보다는 낫다.

 

넓은 범위를 검색할 때 생성하는 또 하나의 테이블, 클러스터 테이블이 있는데 이는 클러스터 키값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용한다. 따라서 한 클러스터 키값을 찾으면 그 안에서 sequential access 가 가능해진다.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26740111 

6. IOT, 클러스터 테이블 활용 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지

https://bae9086.tistory.com/88

CH1. 인덱스 원리와 활용 - 06. IOT,클러스터 테이블 활용

 

 

6. 인덱스 컬럼의 distinct value 개수가 적은가 ?

그렇다면 비트맵 인덱스의 사용을 고려해보자. 단 random access의 측면에서는 B-tree와 유사하기 때문에 여러 비트맵 인덱스를 사용해야 할 때 큰 효과를 발휘한다.

 

 

7. 인덱스 선행조건 체크리스트

인덱스 선행 조건은 등치(=) 조건이 아니면 비효율이 발생한다. 

인덱스 선행 컬럼이 조건에 누락되었는가?

인덱스 선행조건이 between, 부등호, like 같은 범위 검색 조건이 사용되었는가?

 

8. 선행 조건이 범위 검색이라면, Between을 in-list로 변환 가능한가?

만약 선행조건 컬럼이 범위로 값을 탐색한다면 비효율이 발생한다. 시스템에서 인덱스 구성을 바꾸기는 어려운 경우가 있으므로 between 을 in-list 조건으로 바꿔준다.

 

9. 선행조건이 누락되었거나 between, like, 부등호 같은 범위 검색인가?

Index Skip Scan을 사용하면 between 이고, 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져있을 경우 효과적이다.

 

10. 범위 조건이 여러개인가 ?

범위 조건이 여러개인 경우 첫번째 범위 조건에 의해 스캔 범위가 대부분 결정되며, 그 다음을 따르는 조건부터는 스캔 범위를 줄이는데 크게 기여하지 못하므로 성능상 불리할 수 있다. 이런 경우라면 범위를 나눠서 union all 하는것이 더 빠를 수 있다.

 

11. like 를 Between으로 바꿀 수 있는가?

Between을 사용하면 적어도 손해는 안본다. 특히 like의 경우 범위가 202201, 202202일 때 202201% 이런식으로 조건을 주면 ( 인덱스에서 실제 찾아지는 값으로 주면) 스캔 범위를 줄일 수 있지만 2022% 이런식으로 주면 데이터를 모두 스캔해버린다.

 

12. 항상 두개의 부등호를 함께 사용하여 데이터를 검색해야 하는가?

조회일자 >= 시작일자 and 조회일자 <= 종료일자 와 같이 두개의 부등호를 같이 사용해야 하는 것을 선분이력이라고 하는데,

이는 두번째 부등호 조건이 스캔 범위를 줄이는데 전혀 도움을 주지 못한다.

• 선분이력처럼 between 검색 조건이 사용될 때는 어느 시점을 주로 조회하느냐에 따라 인덱스 전략을 달리 가져가라.
• 최근 데이터를 주로 조회할 경우는 (종료일 + 시작일)순으로 구성
• 과거 데이터를 주로 조회한다면 (시작일 + 종료일) 순으로 구성
• (시작일 + 종료일) 일 때는 index_desc 힌트와 rownum <= 1 조건을 추가
• (종료일 + 시작일) 일 때는 rownum <= 1 조건만 추가
• 중간지점을 조회할 때도 위의 방식 사용.
• 미래 시점 데이터를 미리 입력하는 경우가 없다면, 현재 시점 데이터를 조회할 때는 (종료일 = '99991231') 조건을 사용하는 것이 효과적.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26740302 

7. 인덱스 스캔 효율 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!

 

13. 인덱스 구성시 고려 사항

선택도가 높은 컬렆을 앞쪽에 두는 것이 유리한 경우 , 뒤에 두는 것이 유리한 경우

 

14. 정렬이 필요한 경우

인덱스를 구성하고 있는 컬럼들로 정렬이 필요할 경우 order by 절에 인덱스 구성 컬럼을 누락없이 기술함으로써 sorting operation을 생략할 수 있다.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26740390 

8. 인덱스 설계 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!

 

 

JOIN 관련 체크리스트

15. 테이블 조인시 조인 컬럼에 대한 인덱스를 각각의 테이블이 가지고 있는가?

적절한 인덱스가 없다면 소트머지 조인이나 해시 조인이 실행되므로 NL 조인이 유리한 경우 인덱스에 조인 컬럼을 추가해주어야 한다.

 

16. 조인식 조건이 등치가 아니라면?

소트 머지를 사용하는 것이 좋다. 소트부하가 심하지 않다면.

그 외에도 First(Outer) table이 이미 정렬되어있거나, 소트 연산을 대체할 인덱스가 있을 때는 해시 조인보다 소트 머지가 매우 유용하다.

 

17. NL 조인에서의 Driving table 선택 방법

 ( 1 ) 필터 조건 없이 Join이 되는 경우 -> 건수가 작은 테이블로 Driving

 ( 2 ) 필터 조건이 있는 경우 -> 건수가 작은 테이블로 Driving하고 Inner table에 조인값 + 필터값을 가지는 인덱스를 추가한다.

 

/*+ LEADING(A) USE_NL(B) INDEX(B B_INDEX)*/ 이런식으로 힌트를 주면 된다.

 

 

 

http://wiki.gurubee.net/pages/viewpage.action?pageId=26740492 

4. 조인 순서의 중요성 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고

 

18.스칼라 서브쿼리를 이용한 조인 중 여러 건을 조회하고 싶을 때는?

substr이나 오브젝트 Type을 사용하면 된다.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26740581 

6. 스칼라 서브쿼리를 이용한 조인 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루

 

19. 서브 쿼리를 사용하였는가?

 

메인쿼리 : 서브쿼리 = M : 1의 관계인지 확인하자.

Yes -> JOIN이 더 효과적일 수 있다 ( or unnest )

No -> 메인쿼리 : 서브쿼리가 1 : M 이라면 unnest 사용 시 M 개의 집합이 만들어지므로 비효율적이다. 따라서 이때는 no_unnest 를 사용하는 것이 좋다.

 

메인 쿼리 : 서브쿼리 = M : 1의 관계라면, 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화를 함. 이를 서브쿼리 Unnesting이라고 한다. no_unnest 힌트를 사용한다면 필터 방슥으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리되고 메인쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행할 것이다. 반면 unnest 방식으로 수행된다면 메인 쿼리와 서브 쿼리를 조인하여 수행.

unnest 방식으로 변환할 수 있는지 체크해보자.

http://wiki.gurubee.net/pages/viewpage.action?pageId=26741721 

2. 서브쿼리 Unnesting - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!

 

20. 서브쿼리를 뷰 머징(조인)으로 해결 할 수 있는가?

- 조건절, 조인문 정도만 단순구조이거나 group by, distinct가 포함된 쿼리는 Merge가 가능하다.

- 집합연산자 ( union, union all, intersect, minus ), connect by, rownum pseudo , group by 없이 전체 집계, 분석함수 등을 사용하면 Merge는 불가능하다.

 

21. 쿼리에 OR 조건이 사용되었는가?

select * from emp

where job='CLERK' or depno=20;

이러한 쿼리는 

select * from emp where job='CLERK'

union all 

select * from emp where depno=20

으로 변환되어 사용하는것이 인덱스를 사용하기 때문에 더 효율적이다.

 

아래의 힌트를 사용하면 자동으로 중복값은 제외하고 인덱스 검색을 수행해준다.

select /*+ use_concat */* from emp

where job='CLERK' or depno=20;

 

use_concat은 같은 컬럼에 대한 or 조건, in 절, union all 에도 적용할 수 있다.

http://wiki.gurubee.net/display/STUDY/7.+OR-Expansion

7. OR-Expansion - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!

 

 

22. 범위 검색을 하거나 바인드 변수를 사용할 때

: 컬럼의 타입과 맞는지 확인 꼭 해야 한다. 날짜의 경우 to_date, 바인드 변수의 경우 to_char