[본문스크랩] 실행계획의 Argument를 꼭 확인하자.

 

실행계획에 대해서 몇마디 적어보려고 합니다.

 

저희 팀에도 개발자가 몇 명 있습니다.

쿼리분석기로 쿼리를 작성하여, 이 쿼리가 어떻게 실행되는지 보기 위해 실행계획을 확인하는데, 중요한 부분을 놓치곤 합니다.

 

다음과 같은 쿼리가 있습니다.

select * from CE_ACCT where MANDT = '100' and PAOBJNR = '0000000607'

 

이 쿼리의 실행계획은,

 

이 테이블은 클러스터드 인덱스 한개만 있으므로 위 그림으로 보면 데이터 액세스 방식이 'clustered index seek'임을 알수 있습니다.

물론 마우스 포인터를  가져다 대면 'clustered index seek'라고 나오겠지요.

 

그리고는 '인덱스 잘 사용하고 있군'하고 넘어가는 경우가 종종 있습니다.

 

하지만, 사실 이 그림만 봐서는 방식은 인덱스 조회인데, 제대로 인덱스를 사용하고 있는지 알 수가 없습니다. 

좀더 자세한 내용을 알기 위해서는 꼭 실행 계획의 'Argument'를 확인하셔야 합니다.

 

인덱스 조회 노드에 마우스 포인터를 가져가 보면,

위 그림에서 '인수'라고 쓰인 부분이 바로 'Argument'입니다.

set statistics profile on 옵션을 준 후에 text plan을 보면, 결과창에 'Argument' 칼럼이 있습니다. 바로 이 값을 확인해 봐야 합니다.

 

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1]), 

WHERE:([CE_ACCT].[PAOBJNR]=[@2]) ORDERED FORWARD

Argument에는 크게 두가지 부분으로 나뉩니다.

SEEK 와 WHERE

SEEK 부분이 바로 쿼리 조건 중 인덱스 조회를 할 때 사용되어지는 칼럼 입니다.

WHERE 부분은 그렇게 인덱스를 통해 걸러진 결과집합을 가지고 필터로 걸러 내는 역할을 하는 칼럼입니다.

 

다시 예제 쿼리로 돌아가서,

우선 조회되는 테이블의 인덱스는 MANDT, AKTBO, PAOBJNR로 구성된 클러스터드 인덱스 입니다.

그리고 이 인덱스의 선택도를 보면

dbcc show_statistics (CE_ACCT,cx)

앞 두 칼럼 MANDT, AKTBO의 선택도는 낮고 제일 뒤에 있는 칼럼인 PAOBJNR 만 선택도가 굉장히 좋은 것을 알수 있습니다.

 

where 절의 조건 칼럼은 MANDT, PAOBJNR이고 이 두 칼럼 모두가 인덱스에 포함되어 있는데, 하나는 인덱스 조회용으로, 또 하나는 필터 용으로 사용되었을까요? 다시한번 엄밀히 말하면 PAOBJNR 칼럼의 조건은 인덱스에서 사용되지 않은 것입니다. 이 PAOBJNR칼럼의 선택도가 좋음에도 불구하고 왜 사용되지 못했을까요?

 

이유는, 인덱스의 칼럼 순서와 인덱스 구조 때문입니다.

인덱스 구조에 대한 설명은 여기서 하지 않고, 결론만 말씀 드리면, 인덱스 생성시 칼럼의 순서대로, 인덱스 칼럼이 사용될 수 있습니다.

다시 말하면, 위와 같은 순서의 인덱스에서는 PAOBJNR 칼럼이 인덱스 조회시 사용되려면, AKTBO 칼럼에 대한 조건이 where 절에 들어가 있어야 한다는 얘기지요.

 

그러므로 이 쿼리의 성능을 최적화 하기 위해서는 두가지 방법이 있습니다.

1. 쿼리에 AKTBO 조건이 포함되도록 한다.

2. 인덱스 칼럼의 순서를 변경한다. MANDT, AKTBO, PAOBJNR --> MANDT, PAOBJNR, AKTBO

물론 인덱스 칼럼의 순서를 변경하는 일은 극히 신중을 기해야 합니다.

 

그래서 AKTBO 칼럼에 대한 조건을 주고 쿼리를 실행해 보면

select * from CE_ACCT where MANDT = '100' and PAOBJNR = '0000000607' and AKTBO = 'X'

 

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1] AND [CE_ACCT].[AKTBO]=[@3] AND [CE_ACCT].[PAOBJNR]=[@2]) ORDERED FORWARD

실행계획의 Argument 부분에 SEEK 만 있고, WHERE 부분은 없어진 것을 알 수 있습니다.

그러면 두 쿼리의 비용과 I/O를 비교해 보겠습니다.

(1개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0.

(1개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

 

AKTBO 조건이 유무에 따라서 비용이 98.93% 대 1.07% 그리고 I/O 차이는 395 대 3으로

엄청난 성능 향상을 가져왔습니다.

 

 

위 쿼리를 가지고 재미있는 실험을 하나 해보겠습니다.

아래 두 쿼리의 I/O를 비교하면 어떤차이가 있을까요? (쿼리1의 결과는 1행만을 반환 합니다. 그리고 쿼리2는 10000행을 반환 하고요.)

 

--쿼리1

select * from CE_ACCT where MANDT = '100' and PAOBJNR = '0000000607'

 

--쿼리2

select * from CE_ACCT where MANDT = '100'

 

결과는,

--쿼리1

(1개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0.

 

--쿼리2

(10000개 행 적용됨)

Table 'CE_ACCT'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0.

 

놀랍게도, I/O는 같습니다.

이유는 두 쿼리의 실행계획의 Argument를 보면 이해할 수 있습니다.

--쿼리1

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1]),  WHERE:([CE_ACCT].[PAOBJNR]=[@2]) ORDERED FORWARD

--쿼리2

OBJECT:([PRD].[dbo].[CE_ACCT].[cx]), SEEK:([CE_ACCT].[MANDT]=[@1]) ORDERED FORWARD

쿼리1의 실행계획은 위에서 이미 설명 드렸습니다.

두 쿼리다 인덱스 조회 방식을 사용하고 있고 SEEK 부분이 같습니다. 물론 쿼리1은 WHERE 부분에 아주 선택도가 좋은 칼럼 PAOBJNR의 조건이 있기 때문에 결과는 1행만 반환하는것이고요.

결과가 1행만 반환했다고 해서 실제 디스크에서 읽는 I/O의 양이 꼭 적은 것은 아닙니다. 위 실험에서 보듯이...

그러므로 위 두 쿼리는 MANDT = '100' 조건만으로 인덱스를 사용하여 디스크(혹은 메모리)에서 읽은 다음, 첫번째 쿼리만 PAOBJNR 칼럼으로 필터링하여 결과를 보여주는 것입니다. 그러므로 동일한 I/O가 발생되는 것이지요.

 

그러면 이제 마무리를 지어볼까 합니다.

1. 먼저, 실행계획을 볼 때에는 꼭 'Argument'를 확인하라고 말씀드렸습니다. 추가로, graphical plan 보다 text plan을 추천합니다.

가끔 쿼리가 긴 경우, graphical plan 의 Argument의 뒷부분이 짤리기도 합니다. 또한 text plan은 보다 더 많은 유용한 정보를 보여 줍니다.

2. 인덱스 조회 방식일 때, Argument 에는 SEEK와 WHERE의 두부분으로 나뉜다고 말씀 드렸습니다. SEEK 부분에 있는 칼럼이 인덱스를 제대로 사용하는 칼럼이고, WHERE 부분의 칼럼은 다 읽어 놓은 결과에서 여러분 화면으로 데이터를 보내서 보여줄 때 필터 역할만 합니다.

3. 인덱스 칼럼의 순서대로 인덱스를 사용할 수 있습니다. 그러므로, 인덱스 칼럼의 순서와 맨 앞에 오는 칼럼을 결정하는 일은 너무나 중요합니다.

 

나름대로 중요하다고 생각되는 내용을 두서없이 적어 봤습니다. 이 글을 읽고 조금이나마 도움이 되셨기를 바랍니다.

 

 

잘보겠습니당 ^_^