...: ORACLE WITH문 성능지연 개선사례

1. 성능지연현상 설명

최근 1년간 기존의 시스템 환경을 유지보수 하는 일을 하고 있습니다. 여러 SQL처리 문제점 중 WITH문이기에 발생하는 문제점도 종종 있는데 이번에 쓸 내용은 오라클이 TEMP SPACE를 사용하면서 원래는 인덱스에 의해 적절히 처리되거나, 필터나 함수에 의해 처리 돼야 할 부분까지 뻥튀기 되는 현상입니다.

솔직히 저도 이런 현상을 보는 것이 처음이고 이런 형태의 SQL을 사용하는 경우도 처음 보는 일입니다. 현상에 대한 구현도 간단하고, 처리도 간단한 편입니다. 그리고 시연도 간단합니다.

참고로 ORACLE 10g 10.2.0.4.0버전입니다.

기본적인 처리 방법으로는 이러한 connect by 를 불필요하게 2회 이상 반복하는 것 자체를 없애는 것이 답이지만, 경우에 따라 connect by 된 데이터에 데이터 일부를 추가해서 보여주는 경우가 발생하기에 어쩔 수 없이 쓰는 경우가 있습니다. 그런데 그렇더라도 union all 만 한번 하면 되는 것인데... 왜 저렇게 짰는지 제 지능으로는 도무지 이해가 가지 않습니다.

2. FULL SCAN 발생 SQL 및 실행계획

가) SQL

WITH TBL1 AS (
SELECT * FROM (
SELECT NULL AS COL1, 2 AS COL2, 3 AS COL3 FROM DUAL
UNION ALL
SELECT 2 AS COL1, 2 AS COL2, 2 AS COL3 FROM DUAL
) START WITH COL1 IS NULL
CONNECT BY PRIOR COL1 = COL2
)
SELECT * FROM TBL1 CONNECT BY COL2 = COL3

나) 실행계획

- SELECT STATEMENT ALL_ROWS (cost : 6 bytes : 18 Cardinality : 2)
20 - TEMP TABLE TRANSFORMATION
15 - LOAD AS SELECT
14 - CONNECT BY WITH FILTRING
6 - FILTER
5 - (*) COUNT
4 VIEW (Cost 4 Byte 18 Cardinality 2)
3 - UNION ALL
1 FAST DUAL (Cost 2 Cardinality 1)
2 FAST DUAL (Cost 2 Cardinality 1)
13 - HASH JOIN
7 - CONNECT BY PUMP
12 - (*) COUNT
11 VIEW (Cost 4 Byte 18 Cardinality 2)
10 - UNION ALL
8 FAST DUAL (Cost 2 Cardinality 1)
9 FAST DUAL (Cost 2 Cardinality 1)
20 - TEMP TABLE TRANSFORMATION
18 - (*) COUNT
17 VIEW (Cost 4 Byte 18 Cardinality 2)
16 TABLE ACCESS FULL TABLE(TEMP)SYS.SYS_TEMP_5DE.......
(Cost 4 Byte 18 Cardinality 2)

작성된 SQL만으로 봐서는 ()안에 첫 with문을 넣고 with를 제외한 것과 with문에 넣은 것에 전혀 차이가 없어 보이지만, 동작하는 부분에서는 제법 차이가 납니다. 원인은 뭐... 옵티마이저 문제라고 해야 할까요? ;

지금이야 단 2건의 데이터가 있는 SQL을 작성했기에 성능에 영향을 주지 않겠지만, 데이터가 만 건 단위가 넘어가면서 부터 성능상의 문제가 발생하기 시작합니다.

운영 중인 시스템에선 실제로 약 4만건,20만건이 있는 테이블간의 레벨링을 수행하고 있었는데 실행 계획상 처리 비용은 (cost : 23,844 bytes : 206,185,688 Cardinality : 45,236) 이 발생하여 동시접속자가 500명만 되더라도 해당 SQL이 공유 메모리풀과 TEMPSPACE를 모두 차지하여 EXTENSION이 발생했고 해당 TEMPSPACE의 확장이 완료되기 전까지 약 30여분간 모든 서비스의 지연이 발생하곤 했습니다. 또한 시스템에서는 MR LOCKING. 즉 media recovery가 발생했습니다. 미디어 리커버리는 디스크의 이상 상황에 발생한다고 하는데... 확장영역을 충분히 얻지 못하거나 확장이 정상적으로 처리되지 않은 경우에도 발생하는 것 같습니다.

비용은 그렇다 쳐도 bytes 약 200MB 500명이면 약100GB의 메모리를 사용해야 합니다. 하나의 데이터만 제공하는 시스템이 아니기에 shared pool의 점유, 그리고 tempspace 대부분이 점유되면 모든 서비스의 지연이 일어납니다. TEMPTABLESPACE 가 작게 설정되어있다면 확장이 일어나게 되며, 확장이 완료될 때 까지 확장에 의한 지연이 발생 하기도 합니다. 게다가 지연이 발생하는 상황속에서 접속하는 추가 사용자 까지 합산한다면 용량은 점점 더 커지겠죠.

이러한 특성은 WITH문의 문제점으로 보입니다. 현재 사용하는 버전인 10G에서는 WITH문 괄호()안에 아무리 제약조건을 잘 걸어 두어도 with를 CONNECTION BY 조인하거나, 혹은 절차 처리를 하기 위해 다수의 with테이블이 상호 참조 관계가 발생 할 경우 with내에 선언된 테이블을 FULL SCAN 해서 시스템에 부하를 주는 SQL로 변질됩니다. 답은 빠른 응답이 필요한 프로그램의 경우 with문을 사용하지 않는 것이 좋은 것 같습니다.

3. 문제해결

문제 해결 방법은 매우 간단했습니다. temp스페이스를 더 이상 이용하지 않게 만들면 되니, with절에 들어있는 SQL을 ()안에 넣어주면 될 일입니다.

SELECT * FROM (
SELECT * FROM (
SELECT NULL AS COL1, 2 AS COL2, 3 AS COL3 FROM DUAL
UNION ALL
SELECT 2 AS COL1, 2 AS COL2, 2 AS COL3 FROM DUAL
) START WITH COL1 IS NULL
CONNECT BY PRIOR COL1 = COL2
)
CONNECT BY COL2 = COL3

나) 실행계획

- SELECT STATEMENT ALL_ROWS (cost : 4 bytes : 18 Cardinality : 2)
17 - CONNECT BY WITHOUT FILTERING
16 - (*) COUNT
15 - VIEW....
14 - CONNECT BY WITHOUT FILTERING

6 - FILTER

5 - (*) COUNT
4 VIEW (Cost 4 Byte 18 Cardinality 2)
3 - UNION ALL
1 FAST DUAL (Cost 2 Cardinality 1)
2 FAST DUAL (Cost 2 Cardinality 1)
13 - HASH JOIN
7 - CONNECT BY PUMP
12 - (*) COUNT
11 VIEW (Cost 4 Byte 18 Cardinality 2)
10 - UNION ALL
8 FAST DUAL (Cost 2 Cardinality 1)
9 FAST DUAL (Cost 2 Cardinality 1)

고작 2건의 가상데이터에서도 table full scan 이 사라지며 비용이 2가량 감소합니다. 실제 운영 시스템에서는 (cost : 48 bytes : 18,232 Cardinality : 4)로 사용 메모리양이 1/10000 가량 줄어들게 되었습니다. full scan때문에 불가능했던 index 전략도 정상적으로 처리되었기 때문이죠. 또한 위와 아래의 plan을 비교해보면 table full scan이 굳이 필요한가 싶기도 합니다. 뭐... 옵티마이저님 하시는 일을 일개 인간인 제가 어찌 알겠습니까...

이상입니다. 읽어주셔서 감사합니다.

분류

2019년 12월 9일 월요일