- 중첩 루프 조인에서는 먼저 엑세스 되는 테이블과 뒤에 엑세스되는 테이블을 아래와 같이 구분한다.

* DRIVING 테이블 : 먼저 엑세스하는 테이블
* INNER 테이블 : 뒤에 엑세스하는 테이블


중첩 루프 조인 특징
* DRIVING 테이블 - 조건을 만족하는 데이터에 대해 한 번만 엑세스하며 조인 조건을 상수로 제공받지 못한다.
* INNER 테이블 - DRIVING 테이블에서 추출되는 데이터 건수만큼 반복 수행하며 조인 조건을 상수로 제공받는다.

 

* 인덱스 생성 (결합인덱스)
- 점조건 + 선분조건 으로 생성한다. 



 
신고
Posted by jeonguk

SQL>

SELECT column1, column2
FROM TABLE_NAME SAMPLE(10);



실행계획>

SELECT STATEMENT
    TABLE ACCESS (SAMPLE) OF 'TABLE_NAME'




- 해당 테이블에서 표본 집합을 추출하는 것이다.
- 위에서 SAMPLE(10)은 TABLE_NAME테이블에서 10%에 해당하는 데이터를 추출한다는 뜻이다.
- TABLE_NAME 테이블을 구성하는 각 데이터 블록에서 10%의 데이터가 추출된다.
- 개발 시스템에 데이터를 저장할 때 주로 사용될 수 있을 것이다. (실제 운영서버에서 마이그레이션을 할때...)




테이블 엑세스 방법을 크게 두 가지로 나눌 수 있으며, BY INDEX ROWID 실행 계획과 FULL 실행 계획이 주로 사용된다.
 
신고
Posted by jeonguk

- SQL을 작성하는 개발자가 직접 ROWID를 제공하거나, SQL에서 ROWID를 조인 컬럼으로 이용할때
- BY USER ROWID 실행 계획은 BY ROWID 실행 계획과 동일하게 만들어진다.


SQL>

SELECT column1, column2
FROM TABLE_NAME
CONNECT BY PRIOR column2 = column3
START WITH column2 = '11111';




실행계획>

SELECT STATEMENT
    CONNECT BY (WITHOUT FILTERING)
        NESTED LOOPS
            INDEX (UNIQUE SCAN) OF 'column2_IDX'
            TABLE ACCESS (BY USER ROWID) OF 'TABLE_NAME' 
    NESTED LOOPS
        BUFFER (SORT)
            CONNECT BY PUMP
        TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_NAME'
            INDEX (RANGE SCAN) OF 'column3'   



- CONNECT BY 절을 사용하면 BY USER ROWID 실행 계획이 생성될 수 있다.
- 위 SQL에서 인덱스를 통해 column2 값인 '11111'에 대한 하나의 ROWID가 추출된다. 추출된 ROWID가 CONNECT BY절로 다시 한번 제공되기 때문에 인덱스를 통해 제공된 ROWID가 아니라 이미 엑세스된 ROWID가 제공된 것이므로 BY USER ROWID 실행 계획이 생성된다.


SQL>

SELECT column1, column2
FROM TABLE_NAME
WHERE ROWID = 'AAABBBCCCAA1'



실행계획>

SELECT STATEMENT
    TABLE ACCESS (BY USER ROWID) OF 'TABLE_NAME'



- WHERE절에 ROWID값을 명시적으로 설정하면 BY USER ROWID 실행 계획이 생성된다.
- 인덱스에 엑세스하여 하나의 ROWID를 제공받은 것과 같은 실행 계획이다.



 
신고
Posted by jeonguk

- 인덱스를 이용하지 않고 테이블에 바로 엑세스
- 테이블 전체 엑세스
- 인덱스를 이용하지 않고 테이블에 엑세스 하면 ROWID를 이용하지 못한다.
- 테이블을 처음부터 끝까지 모두 엑세스한다.

실행계획>

SELECT STATEMENT
    TABLE ACCESS (FULL) OF 'TABLE_NAME'  









 
신고
Posted by jeonguk

실행 계획 : SQL이 수행되는 절차

SQL을 작성한 후에는 바로 어플리케이션에 적용하기 전에 실행 계획을 확인하여 문제점을 파악하는 것이 좋다.

SQL을 통해 결과를 추출하는 과


- 테이블 관련 실행계획
- 인덱스 관련 실행 계획
- 정렬 관련 실행 계획
- 연산자 관련 실행 계획
- 데이터 연결 관련 실행 계획
- 파티션 관련 실행 계획
- 기타 실행 계획



테이블 엑세스 방법


- BY INDEX ROWID - 인덱스 스캔을 통한 테이블 엑세스
- FULL SCAN - 테이블 전체 스캔
- BY USER ROWID - 정의된 ROWID에 의한 테이블 엑세스
- SAMPLE - 표본 데이터 추출


1. 가장 일반 적인 BY INDEX ROWID 실행 계획

SQL> SELECT COLUNM_1,  COLUNM_2
         FROM TABLE_NAME
         WHERE  COLUNM_3 BETWEEN '20070301' AND '20070331'



실행계획>

SELECT STATEMENT
    TABLE ACCESS (BY INDEX ROWID) OF ' TABLE_NAME '
        INDEX (RANGE SCAN) OF ' COLUNM_3_IDX'



- TABLE_NAME 테이블의 COLUMN_3 컬럼에 인덱스가 존재한다면 해당 인덱스에 엑세스하는 순간 인덱스로부터 ROWID가 제공됨.
- 인덱스는 인덱스를 구성하는 컬럼과 ROWID로 이루어진다.
- WHERE 조건인 COLUMN_3 컬럼의 값을 만족하는 데이터에 엑세스하면 해당 인덱스 값들 옆에는 ROWID가 있으며, 이 ROWID를 통해 테이블에 엑세스한다.
- 인덱스로부터 제공받은 ROWID를 이용하여 TABLE_NAME 테이블에 엑세스 한다.

- ROWID는 주소와 같은 역할을 한다. (유일한 하나의 값)





 
신고
Posted by jeonguk

우리는 여러 사이트에 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

 

Nested Loop Join과 Sort Merge Join

Nested Loop Join

Nested Loop Join이란 먼저 어떤 테이블의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다.

1. 특징

1) 순차적으로 처리된다. 선행테이블(Driving table)의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐만 아니라 테이블간의 연결도 순차적이다.

2) 먼저 액세스되는 테이블(Driving Table)의 처리범위에 의해 처리량이 결정된다.

3) 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스된다. 즉, 자신에게 주어진 상수값에 의해 스스로 범위를 줄이는 것이 아니라 값을 받아서 처리범위가 정해진다.

4) 주로 랜덤 액세스 방식으로 처리된다. 선행 테이블의 인덱스 액세스는 첫번째 로우만 랜덤 액세스이고 나머지는 스캔이며 연결작업은 모두 랜덤 액세스이다.

5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라질 수 있다.

6) 연결고리가 되는 인덱스에 의해 연결작업이 수행되므로 연결고리 상태가 매우 중요하다. 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 발생된다.

7) 연결작업 수행 후 마지막으로 check되는 조건은 부분범위처리를 하는 경우에는 조건의 범위가 넓을수록, 아예 없다면 오히려 빨라진다.

2. 사용기준

1) 부분범위처리를 하는 경우에 주로 유리해진다.

2) 조인되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리해진다.

3) 주로 처리량이 적은 경우(많더라도 부분범위처리가 가능한 경우)에 유리해진다. 그것은 처리방식이 주로 랜덤 액세스방식이므로 많은 양의 랜덤 액세스가 발생한다면 수행속도가 당연히 나빠지기 때문이다.

4) 가능한 한 연결고리 이상 상태를 만들지 않도록 주의해야 한다.

5) 순차적으로 처리되기 때문에 어떤 테이블이 먼저 액세스되느냐에 따라 수행속도에 많은 영향을 미치므로 최적의 액세스 순서가 되도록 적절한 조치가 요구된다.

6) 부분범위처리를 하는 경우에는 운반단위 크기가 수행속도에 많은 영향을 미칠 수 있다. 운반단위가 적을 수록 빨리 운반단위를 채울 수 있으나, 폐치(Fetch) 횟수에서는 불리해지는 이중성을 가지고 있다.

7) 선행 테입ㄹ의 처리 범위가 많거나 연결 테이블의 랜덤 액세스의 양이 아주 많다면 Sort Merge 조인보다 불리해지는 경우가 많다.

Sort Merge Join

Sort Merge Join이란 양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식을 말한다. 이 방식은 경우에 따라 Nested Loop Join보다 훨씬 빨라지는 경우도 많이 있으며 랜덤 액세스가 줄어들어 시스템의 부하를 감소시키지만 일반적으로 Nested Loop Join 보다는 사용되는 빈도가 적은 편이다.

이 방식의 가장 큰 특징은 상대방에게 아무런 값도 받지 않고 자신이 가지고 있는 조건만으로 처리범위가 정해지며, 랜덤 액세스를 줄일 수는 있으나 항상 전체범위처리를 한다는 것이다.

1. 특징

1) 동시적으로 처리된다. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다.

2) 각 테이블은 다른 테이블에서 어떠한 상수값도 제공받지 않는다. 즉, 자신에게 주어진 상수값에 의해서만 범위를 줄인다.

3) 결코 부분범위처리를 할 수가 없으며, 항상 전체범위처리를 한다.

4) 주로 스캔방식으로 처리된다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 액세스이고 머지작업은 스캔방식이다.

5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않는다.

6) 조인의 방향과는 전혀 무관하다.

7) 스스로 자신의 처리범위를 줄이기 위해 사용되는 인덱스는 대개 가장 유리한 한가지만 사용되어진다. 그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 작업대상을 줄여 주기 때문에 중요한 의미를 가진다.

2. 사용기준

1) 전체범위처리를 하는 경우에 주로 유리해진다.

2) 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태인 경우 주로 유리해 질 수 있다. 상수값을 받아 처리(Nested Loop Join)한 범위의 크기와 처리범위를 줄여 처리(Sort Merge Join)한 범위의 크기를 대비해보아 상수값을 받아 줄여진 범위가 약 30% 이상이라면 Sort Merge Join이 일반적으로 유리해진다. 그러나 부분범위처리가 되는 경우라면 전혀 달라질 수 있다. 이런 경우는 처리할 전체범위를 비교하지 말고 첫번째 운반단위에 도달하기 위해 액세스하는 범위애 대해서 판단해야 한다.

3) 주로 처리량이 많은 경우 (항상 전체범위처리를 해야 하는 경우)에 유리해진다. 그것은 처리방식이 주로 스캔방식이므로 많은 양의 랜덤 액세스를 줄일 수가 있기 때문이다.

4) 연결고리 이상 상태에 영향을 받지 않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하게 사용할 수 있다.

5) 스스로 자신의 처리범위를 어떻게 줄일 수 있느냐가 수행속도에 많은 영향을 미치므로 보다 효율적으로 액세스할 수 잇는 인덱스 구성이 중요한다.

6) 전체범위처리를 하므로 운반단위의 크기가 수행속도에 영향을 미치지 않는다. 가능한 운반단위를 크게 하는 것이 페치(Fetch) 횟수를 줄여준다. 물론 지나치게 큰 운반단위는 시스템에 나쁜 영향을 미친다.

7) 처리할 데이터량이 적은 온라인 애플리케이션에서는 Nested Loop Join이 유리한 경우가 많으므로 함부로 Sort Merge Join을 사용하지 말아야 한다.

8) 옵티마이저 목표(Goal)가 "ALL_ROWS"인 경우는 자주 Sort Merge Join으로 실행계획이 수립되므로 부분범위처리를 하고자 한다면 이 옵티마이져 목표가 어떻게 지정되어 있는지에 주의하여야 한다.

================================================

"대용량 데이터베이스" 책에서 발췌

신고
Posted by jeonguk