우리는 여러 사이트에 BPM 컨설팅을 진행 하면서, 기업내에 존재하는 레거시 시스템과 수많은 연계 작업을 하게 된다.또는 BPM 제품 또한 DBMS의 직결적인 연관이 있음에도 불구하고, DB 퍼포먼스에 대해서는 소흘히 하는 경우가 많다.

누군가는 BPM Software Engineer가, 왜 우리와 상관없는 Data 분야까지 신경 써야만 하는가? 라고 의구심을 품을수 있을지 모른다.그에 대한 해답은, 우리가 만드는 것에 대한 본질적인 의미를 제대로 이해하지 못하기 때문이 아닌가라고 생각 된다.

BPM은 EAI를 구현하면서, 기업의 모든 리소스를 프로세스 기반하에 Management 할 수 있는 경영학이다.그렇다면 기업의 모든 프로세스 활동에서 산출되는 자료(Data)는 경영에서 어떤 의미를 갖는가? 바로 기업이 관리하는 자산이 될 것이다.다르게 얘기하여, BPM을 통한 모든 업무 프로세스가 퍼포먼스의 문제를 내재하고 있다면? BPM을 도입 하지 않은것 보다 오히려 더 안좋을 수 있을 것이다.

또한, DB 퍼포먼스는 제품의 질을 떨어뜨리는 직관적인 지표가 될수 있다는 것은 자명한 사실이다.일례를 들자면, SI프로젝트 이후 구현된 시스템에 대한 스트레스 테스트를 통한 성능 지표 산출 작업일 수 있겟다

그러므로 Software Acchitect 들이 기본적으로 알아야만 하는, RDBMS의 대용량 데이터베이스 솔루션에 대하여 이해 하도록 하자.

RDBMS 의 옵티마이져란, 우리가 일반적으로 작성하는 SQL 문의 문법적 오류를 확인하고, 가장 빠른 데이터 억세스 경로를 작성 및 채택하여, 실제 데이터를 엑세스 하는 엔진이라 할 수 있다.

그렇다면, 옵티마이저는 항상 최적의 경로를 만들어 내는가? 그것은 아니다.때로는 옵티마이저가 좋아하는, 당근을 줌으로써 최적의 성능을 낼수 있도록 도와주어야 한다.

옵티마이저 이해는 아래의 그림을 통하여 이해를 돕도록 하겠다.


요즘 상용되는 RDBMS 벤더의 특징에 따라 옵티마이저를 Rule Based, Cost Based로 분류 한다. 오라클은 두가지 옵티마이저 를 모두 사용하며 상황에 따라 옵션설정 또는 오라클 힌트로 선택하여 사용 할 수도 있다.

Rule Based 는 말그대로 실행 계획을 작성함에 있어, 아래의 룰을 기반하여 실행 계획을 작성하게 되는 것이다.

1. Rowid에 의한 한 행

2. 클러스터 조인에 의한 한 행

3. Unique나 Primary Key를 사용한 해시 클러스터 키에 의한 한 행

4. Unique나 Primary Key에 의한 한 행문장의 Where 절이 = 조건 절에서 컬럼들의 Unique나 Primary Key를 사용할 경우

5. 클러스터 조인

6. 해시 클러스터 키

7. 인덱스 된 클러스터 키

8. 복합 컬럼 인덱스(Composite Index) Where 절에 결함 인덱스 컬럼들의 = 조건 절이 AND 연산자에 의해 연결되어 있는 경우

9. 단인 컬럼 인덱스(Single Index)

10. 인덱스 된 컬럼에 대한 유계 영역 검색(Bounded Range Search on Indexed Columms) =, >=,  <=, Between, Like

11. 인덱스 된 컬럼에 대한 무계 영역 검색(Unbounded Range Search on Indexed Columns)

12. 소트-머지 조인(Sort Merge Join)

13. 인덱스 된 컬럼의 최대 또는 최소(MAX or MIN of Indexed Columns)

14. 인덱스 된 컬럼의 Order by on Indexed Columns

15. Full Table Scan



아래의 예를 들어보도록 하자.emptable에 unique Primary Key를 empcode 로 해논 상태에서 아래의 쿼리는 어떤 실행 계획을 추출할까?select * from emptable where rowid='fefe43r39f3' and empcode='wanheecho'

unique Primary Key 의 순위는 4위에 해당 하며, Rowid에 의한 한 행은 1위 이므로 당연히 아래와 같은 실행 계획을 만든것을 확인할수 있다.


Cose Based 는 오라클이 내부적으로 관리하는 통계 데이터를 이용하여, 최적의 실행 경로를 만들게 된다.하나의 SQL에 대한 여러 개의 execution plan 가운데 가장 cost가 적은 execution plan을 선택 한다.

 - 목적은 대상 Row를 처리하는데 필요한 자원 사용을 최소화
   --> 궁극적으로 데이터를 빨리 처리하는데 목적
 - 비용산정 요소로는 각종 통계정보, SQL형태, hint, optimizer mode, 연산자, index, cluster,
    DBMS 버전, CPU 용량, 메모리 용량, Disk I/O, LINK N/W 비용 등 매우 다양하다.
 - 비용산정 요소 중에서 Data Dictionary 내의 테이블/클러스터 /인덱스에 대한 통계와 데이터
   분포를 중요시 한다.
 - CBO의 성능을 최적의 상태로 유지시키기 위해서는  ANALYZE OBJECT 작업을 정기적으로
    해주는 것이 가장 중요하다.


ANALYZE {TABLE/INDEX/CLUSTER} OBJECT_NAME


옵티마이저 방식은 이정도로 마치고 하고, 실제 우리가 어떻게 적용해야 하는가를 살펴보도록 하자.

부분 범위 처리.RBO 에서 우선순위를 보면 알겠지만, 옵티마이저가 가장 마지막 순서로 적용할수 있는 Access 방식이 Full Scan 이다.Full Scan은 테이블에 모든 정보를 처음부터 끝까지 읽어 내리는 것이다.반면 인덱스를 적용하여, 내가 필요한 범위만 Access 방식을 Range Scan 방식이라 한다. 한마디로 Index를 활용하면 RangeScan이 가능하다는 것이다.

그렇다면, 우리는 얼마나 인덱스를 잘 활용하고 있을까?

질문.


emptable 과 dept 테이블은 1:m 관계를 형성 하고 있다.
emptable 에는 당연히 FK 로 deptno를 가지고 있다.
dept 는 deptno라는 PK를 가지고 있고, emptable 또한 empcode를 pk로 가지고 있다.

select *
from emptable, dept
where dept.deptcode=emptable.deptcode

의 쿼리를 수행하였을때 Drive Table 과 실행계획은 어떻게 될까?


dept 테이블이 drive 하여 emptable 의 deptcode 를 엑세스 하게 될 경우, emptable.deptcode에는 인덱스가 없으므로 dept Row*emptable Row 만큼 풀스캔을 반복할 것이다. 이것이 루프 쿼리가 되는 것이다.

반면 emptable이 드라이브 된다면, dept.deptcode를 unique row scan 하여 인덱스를 활용할수 가 있다.옵티마이저는, 항상 인덱스가 없는 테이블을 드라이브 테이블로 활용하게 된다. 기억하자.


위와 같이 인덱스를 활용하여 데이터를 엑세스 하는 방식을 부분 범위 처리 라고 한다.그렇다면, 이런 부분 범위 처리를 위하여, 간과 하고 넘어가는 실수는 없는지 살펴 보도록 하자.


select *
from emptable, deptcode
where RTRIM(dept.deptcode)=emptable.deptcode

또는 RTRIM 외에 CONCAT, NVL이나 다른 데이터 변형 함수를 사용하였을 경우는 어떻게 될까?
인덱스를 탈수 없게 된다. 그러므로 우리가 예상한 데이터 엑세스 경로에, 인덱스 컬럼을 변형하지 말도록 하자.


 아래는 위 예제의 플랜이다



만약, 인덱스가 있는데도 불구하고 LIKE 조건을 사용하는 경우 인덱스를 탈수 없는 경우가 있다.

select *
from emptable
where emptable.name like '%name%'

name 컬럼에 인덱스가 있다고 가정하고 '%name%' 라고 명시 하였을 경우 인덱스는 탈까? 정답은 타지 않는다.
그렇다면 'name%' 이것은? 인덱스를 사용할수 있다.
like 조건시 가능한한 인덱스를 활용할수 있는 방법을 사용하도록 해야 한다.


마지막으로, 본인이 튜닝한 쿼리 구문을 분석해 보고자 한다.

인덱스


bpm_worklist TASKID -> UNIQUE PRIMARY KEY
bpm_procinst INSTID -> UNIQUE PRIMARY KEY
bpm_roleMapping  ROLLMAPPINGID -> UNIQUE PRIMARY KEY
bpm_roleMapping  ROLENAME -> INDEX

튜닝전 쿼리 실행 시간 15초


select wl.TITLE,
       wl.TASKID,
       inst.NAME,
       inst.INITRSNM,
       inst.INITEP
from bpm_procinst inst,
     bpm_worklist wl
where
         ( wl.endpoint='grayspec' or (select count(1)
                                      from bpm_roleMapping rm
                                      where rm.instId=wl.instId
                                            and (rm.roleName=wl.roleName or rm.roleName=wl.refRoleName)
                                            and rm.endpoint='grayspec') > 0
                                      )
         and (wl.status = 'NEW' or wl.status = 'CONFIRMED' or wl.status = 'DRAFT')
       
         and inst.isdeleted=0
         and inst.instid = wl.instid
order by wl.startdate desc;


위 플랜상에서 속도 저하의 원인은 무엇일까, 플랜상의 INLIST ITERATOR 를 확인 할 수 있을 것이다.이 구문은 반복 구문으로 엑세스 한다는 뜻이다. 그렇다면, bpm_worklist ROW 수만큼 SELECT COUNT 절이 계속 반복 되게 된다.데이터 량이 많거나, 위 구문의 쿼리를 동시에 접속하게 되면, 서버는 바로 다운되게 된다.


튜닝된 쿼리 사용시 0.03초

select wl.TITLE,
       wl.TASKID,
       inst.NAME,
       inst.INITRSNM,
       inst.INITEP
from bpm_procinst inst,
     bpm_worklist wl,
     bpm_roleMapping rm
where  inst.instid = wl.instid
       and rm.instId=wl.instId
       and rm.roleName in (wl.roleName,wl.refRoleName)
       and 'jjy' in (rm.endpoint,wl.endpoint)
       and wl.status in ('NEW' , 'CONFIRMED' , 'DRAFT')  
       and inst.isdeleted=0
order by wl.startdate desc;



위 플랜에서 확인할 수 있는 사항은 풀스캔이 2개의 테이블에 적용 된것과 HASH JOIN이 사용되었다는 것이다. 그런데 오히려 속도가 빨라진 이유는?풀스캔은 멀티블락IO를 사용하여 한번에 여러개에 블락을 처리하게 된다. 또한 HASH JOIN은 어느 한쪽이 드라이브 하여 반복적으로 다른 한쪽을 ACCESS 하는 것이 아니라 먼저 ACCESS 된 테이블을 매트릭스에 넣어두고 다음 테이블을 순서와 상관없이 멀티로 읽어 들여 지정된 매트릭스와 연관되어 있는 값들만 연결 시켜주는 조인이다.

그렇다면 인덱스를 사용하는 경우와, 풀스캔을 사용해야 하는가?

분포도와 손익분기점

더보기


분포도에 가 15% 이내에 대하여 인덱스를 적용한다.


출처 : http://www.uengine.org:8088/wiki/index.php/%EB%8C%80%EB%AC%B8
신고
Posted by jeonguk