toad 에서 explain plan 보기

출처 : http://blog.daum.net/maihikki/9078992

방 1)

Sql Plus 에서 아래 코드 붙여넣기 하신후 실행하시고

@C:\oracle\ora92\rdbms\admin\utlxplan.sql

toad-view-option-oracle-general 에서 가운데쯤 보시면 Explan Plan Table Name이라는 항목이 있습니다. 거기에 Plan_Table 이라고 기재하신후 사용하면 됩니다.

 

 

방 2)

토드에서 explain plan을 볼려면 아래의 스크립트를 실행 시킵니다.
C:\Program Files\Quest Software\TOAD\temps\toadprep.sql

 

toadprep.sql을 열어보면 toad유저를 생성할 때..
테이블스페이스를 지정하는데 데이타베이스에 존재하는 테이블 스페이스에 맞게 수정해야 합니다.

 

=============== 아래 부분은 제 오라클에 맞게 수정한 부분입니다. ===================

CREATE USER TOAD IDENTIFIED BY TOAD
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
QUOTA 0K ON SYSTEM;

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

 

toadprep.sql스크립트가 에러없이 수행이 되면 오라클에 toad라는 유저가 생성되고...

toad_plan_table, toad_plan_sql 테이블이 생성이 됩니다.

또한 시퀀스, 시노님, 권한부여, 함수가 에러없이 생성이 되면 설치가 다 끝난겁니다.

 

실행계획을 보는 방법은 우선 SQL을 실행할 유저로 토드를 접속합니다..

 

그리고 나서 sql 을 실행하고 나면 토드 아래에  explain plan과 autotrace를 보면 됩니다.

 

아래의 그림은 제 피시에서 실행한 예 입니다..아래의 explain plan이 보이지 ？을 경우에는 토드 메뉴에서 view->explain plan을 선택하거나 아래 그림 맨 오른쪽 세번째 있는 엠브런스차 아이콘을 클릭하면 됩니다..

 

실행 계획을 보는 방법은 Operation 컬럼에 나온 내용과 아래의 표를 참고해서  보시면 됩니다.

그리고 Explain Plan 오른쪽에   Auto Trace를 보면 Trace정보가 나옵니다..

 

AutoTrace관련 몇 가지를 설명하면 아래와 같습니다.

db block gets : current gets에 대한 논리적인 IO횟수(in memory)

consistent gets : read-consistent gets에 대한 논리적인 IO횟수(in memory)

physical reads : Disk에서 읽은 블럭수

redo size : (DML)문에 의해 생성된 redo의 양

sorts(memory) : memory에서 수행된 sort횟수

sorts(disk) : Temporary 영역에서 sort된 횟수

 

이미 아시겠지만 db block gets와 consistent gets는 Logical Read를 나타냅니다.
두개를 더한 값과 Pysical Reads를 비교해서 Hit Ratio를 구하죠.

우선 consistent gets는 consistent mode에서 db block read를 수행한 숫자입니다.

TKPROF 레포트에서 query에 해당하는 값입니다.

 

consistent라는 말은 read consistency와 관련이 있는데 즉 읽기 일관성이 보장되는 상황에서 읽는 숫자라는 것입니다. 데이터를 수정하지 않고 단지 읽기만 하기때문에 lock이 발생하지 않습니다.

db block gets는 CURRENT mode에 있는 block의 데이터를 읽은 숫자입니다. TKPROF 레포트에서 current에 해당하는 값이구요. current mode에서는 곧 수정될 Segment header나 block을 얻을 때 일어납니다. INSERT, UPDATE, DELETE에서 데이터 쿼리부분이 아니라 수정될 값을 읽을 때 나타나는 숫자입니다. select문일 경우에는 Full table scan일 경우 Segment header를 읽을 때 나타납니다.

위의 내용은 Performance Tuning 문서에 있는 내용을 대충 정리한 것입니다. 그래서 아마도 위의 내용은 대부분 보셧을 내용이겠지만, 정확한 차이점을 이해하려면 더 많은 것들을 알고 이해해야합니다.

여기서 Consistent mode와 current mode가 무엇인지를 아는 것이 중요합니다.

우선 Consistency를 알기 위해서 오라클이 제공하는 Mulitversion concurrency control에 대해 알아야 합니다. 사용자가 쿼리를 날렸을때 그 순간의 이미지를 가지고 데이터를 가져오게 됩니다. 쿼리를 날린 후에 다른 세션에서(자기 자신의 세션에서도 마찬기지입니다.) 원하는 데이터가 수정이 되어도 처음 쿼리의 처리를 시작한 순간의 내용을 오라클은 제공합니다. 이런 처리를 위해 쿼리가 실행되는 순간 SCN(System change number)가 결정의 되고 각 DB Block의 scn과 비교하여 더 큰 scn을 가지고 있는 경우 즉 변경이 일어난 경우에는 rollback segment에서 과거의(자신의 scn보다 낮은 scn을 가지고 있는) 이미지의 block을 가지고 오게 됩니다.

consistent gets가 consistent mode에서 block의 데이터를 읽은 숫자라는 것은 이와 같이 읽기 일관성이 보장되는 상황에서 읽은 block의 숫자라는 것입니다.

DML문장이 수행되는 경우에 처리되는 부분을 두 부분으로 나눌 수 있는데, 하나는 수정할 데이터를 찾기 위해 읽는 부분이고, 다른 하나는 실제 데이터를 수정하기 위해 데이터를 읽는 부분입니다. 데이터를 찾기위해 읽는 부분은 INSERT문의 sub query부분과 delete문과 update문의 where 조건절에 해당하는 부분이나 sub query에 해당하는 부분입니다.

DML문에서는 데이터를 찾기 위한 부분이 consistent gets에 나타나고 수정하기 위한 부분이 db block gets에 나타납니다.

update t set value = value 5 where value > 10;
예를 들어 위와 같은 문장을 보면 처음 위의 문장이 실행 되는 순간의 이미지를 이용해서 조건에 맞는 즉 value가 10보다 큰 row들을 찾습니다. 그리고 각각의 row를 실제로 update하는 value = value 5를 실행할때는 current mode에서 수행이 됩니다. 조건에 해당하는 row를 이미 읽었지만 수정하기 위해서 다시 또 읽게 됩니다. 이 때는 실제 데이터를 수정해야 하기 때문에 지금 바로 현재의 데이터여야 합니다. consistent mode에서 읽은 데이터는 과거의 이미지의 데이터일 수 있지만 수정시에는 가장 최근의 버젼을 수정해야 하기 때문입니다. 즉 current mode라는 것은 과거의 시점이 아닌 바로 지금의 data를 읽는 것을 말합니다.

update문을 수행시에 consistent gets와 db block gets의 숫자를 비교해 보면 db block gets의 숫자가 더 큰 경우를 보게 되는데 이것은 consistent gets는 즉 수정될 로우를 찾을 경우는 block단위로 io가 일어나서 읽은 block의 수를 나타내게 되지만, db block gets는 즉 수정될 로우를 찾을 때는 각 로우마다 current mode에서 데이터를 읽기 때문에 같은 block도 row의 수만큼 읽게 됩니다. trace를 보시면 db block gets의 수는 실제 수정될 row의 수와 거의 같은 것을 확인하실 수 있습니다.

consistent gets는 physical reads를 포함하고 있기 때문에 실제로 쿼리를 튜닝할 때 중요한 것은 Logical IO를 줄이는 것입니다. 이것에 관한 내용도 설명하자면 매우 길어지므로 여기서는 logical reads의 한 경우인 db block gets의 관한 예를 하나만 들겠습니다.

다음의 두 문장은 똑 같은 읽을 수행합니다. 읽은 범위는 똑 같습니다. 즉 consistent reads의 수는 동일합니다. 그러나 db block gets의 숫자는 두배의 차이가 있기때문에 속도의 차이가 있습니다. 직접 샘플을 만들어 확인해 보시기 바랍니다.

UPDATE ta A
SET A.QTY = (
SELECT SUM(B.QTY) A.QTY
FROM tb B
WHERE B.ID = A.ID
);

UPDATE ta A
SET A.QTY = A.QTY (
SELECT SUM(B.QTY)
FROM tb B
WHERE B.ID = A.ID
);


질문의 내용에는 벗어나지만 성능을 향상시키기 위해서는 LIO를 줄여야합니다. 모든 LIO는 latch를 발생시키기 때문에 가능한 적은 LIO를 수행하도록 하여야 좋은 성능과 확장성을 보장할 수 있습니다.