DBMS_XPLAN 패키지를 이용한 SQL 실행계획 검토 및 작성 가이드

DBMS_XPLAN 패키지를 이용한 SQL 실행계획 검토 및 작성 가이드 성능향상을 위한 튜닝포인트는 하드웨어 자원, DB 파라미터, OPTIMIZER_MODE, 통계정보, 애플리케이션 튜닝 등 매우 다양하다. 이중 개발자가 담당할 수 있는 영역이 바로 애플리케이션 튜닝으로 대부분 이것을 성능관리 포인트로 여기고 있다. 필자가 속한 사이트에서도 SQL을 작성하는 응용 담당자 선에서 1차 튜닝 검토가 진행되며, 추가 검토가 필요한 건에 대해서만 성능관리 파트에서 튜닝을 담당하고 있다. 이번 시간에는 DBMS_XPLAN 패키지를 이용한 SQL 실행계획 검토와 성능을 고려한 SQL 작성 가이드에 대해 이야기하자. DBMS_XPLAN은 오라클 9.2 버전부터 제공되는 패키지로 Plan_Table에 저장된 실행계획을 쉽게 출력해 볼 수 있다. 오라 클 10g 버전에서는 보다 다양한 기능을 제공하게 됐는데, 가장 최근에 수행한 커서 외 특정 커서에 대한 실행계획(Explain Plan) 정보 확인이나 AWR로부터의 데이터 추출 등을 할 수 있 게 됐다. 또한 몇 가지 다이내믹 뷰(Dynamic View)에 대한 권한설정 만 해주면 다양한 정보를 사용자가 원하는 포맷으로 출력할 수 있어 좀더 쉽게 활용할 수 있다는 장점이 있다. 그래서 현재 많이 사용되는 10046 TRACE나 tkprof 분석 방법과 함께 실행계획 분석에 매우 유용한 패키지다. DBMS_XPLAN.DISPLAY를 이용한 사전 실행계획 확인 모든 쿼리 검토의 기본은 실행계획 확인이다. 단순하게 쿼리를 수행해서 결과값이 빨리 나온다고 성능이 좋은 쿼리라고 생각하 면 절대 안 된다. 운영 DB와 달리 조건에 맞는 데이터가 없거나 테스트하는 테이블에 데이터가 몇 건 없어도 결과값은 빨리 나올 수 있는 법이기 때문이다. 사전 실행계획은 SQL 문을 직접 실행하지 않고 옵티마이저가 SQL 문 실행에 필요한 계획을 생성하게 되는데, 이 계획을 Plan_Table에 저장한 후 DBMS_XPLAN.DISPLAY 함수를 사 용해 확인할 수 있다. 사용법 우선‘SELECT * FROM TABLE(DBMS_XPLAN. DISPLAY);’로 SQL의 사전 실행계획을 생성하고 원하는 포맷 으로 출력한다. Plan_Table 생성 예제 ‘SQL>@?/rdbms/admin/utlxplan.sql’로 실행하면 된다. 오 라클 10g 버전 이후부터는 설치 시 자동으로 plan_table과 public synonym이 생성된다(<그림 1> 참조). 실행계획을 읽는 순서 실행계획은 위에서 아래 방향으로, 안에서 밖으로 읽는다. 조 인은 쌍(Pair)로, 조인 순서와 방법은 각 오퍼레이션대로 읽으면 된다. <그림 2>의 경우 3 → 5 → 4 → 2 → 1 순서로 해석한다. 이렇게 만들어진 사전 실행계획은 FULL TABLE SCAN, INDEX FULL SCAN, CARTESIAN MERGE JOIN, JOIN 방법이나 순서가 제대로 됐는지 기본적으로 확인하는 게 1차 검 토 항목이다. 만일 1차 검토를 거쳐 사전 실행계획에 특이사항이 없다는 것 을 확인한 다음 SQL을 수행했을 때 성능이 제대로 안 나오거나 DB 부하가 발생하게 되면 실제 실행계획(Runtime Plan)을 확 인해 정확한 튜닝 포인트를 찾는 것이 중요하다. 그럼 사전 실행계획과 실제 실행계획이 달라지는 이유는 무엇 일까? 우선 DB에서 BIND PEEKING 기능을 사용하게 되는 경 우 사전 실행계획 생성 시 BIND 변수의 영향을 받지 않고 생성 되지만, 실제 실행계획에서는 PEEKING된 BIND 변수를 참고 해 실행계획을 세우므로 사전 실행계획과 다를 수 있다. 또한 사전 실행계획의 경우 최신 통계정보를 이용해 실행계획 을 생성하기 때문에 통계정보가 갱신됐을 때 실행계획이 달라지 며, 실행계획을 생성하는 환경과 실제 수행되는 DB의 OPTIMIZER_MODE 환경 등이 다를 경우에도 달라질 수 있으 므로 SQL 수행 시 성능저하가 발생되면 실제 실행계획을 반드 시 확인해야 한다. DBMS_XPLAN.DISPLAY_CURSOR를 이용한 실제 실행계획 확인 수행된 SQL의 실제 실행계획을 확인하려면 DBMS_ XPLAN.DISPLAY_CURSOR 함수를 이용한다. 그리고 TRACE 파일을 생성했을 때와 동일하게 예측 ROW 수가 아닌 실제 ROW 수를 확인하고자 할 경우 STATISTICS_LEVEL =ALL이나 _ROWSOURCE_EXECUTION_STATISTICS =TRUE, 오라클 10g 버전이라면 GATHER_PLAN_STATIS TICS HINT를 사용하면 된다. 더불어 DBMS_XPLAN 패키지 사용 시에는 V$SESSION, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL, V$SQL 에 대한 SELECT 권한을 가지고 있어야 제공되는 모든 정보를 확인할 수 있다. 실제 수행 데이터를 확인하기 위한 포맷 항목은 다음과 같다. - ALLSTATS : 실제 액세스한 ROW 수와 수행시간의 수행 횟수에 따른 누적값 - ALLSTATS LAST : 실제 액세스한 ROW 수와 수행시간의 가장 최근 에 수행된 값 - ADVANCED ALLSTATS LAST : 모든 포맷 정보 다음 예제로 수행한 화면이 <그림 3>이다. /*+ gather_plan_statistics */ 힌트 부여 DBMS_XPLAN.DISPLAY_CURSOR의 각 항목은 <표 2>와 같은 의미를 가진다. 사전 실행계획 또는 실제 실행계획의 검토방법 출력된 실행계획을 확인할 때 다음 요소를 기준으로 검토해, SQL의 일부가 최적화되지 않았을 경우 SQL 수정이나 인덱스 구성 등을 고려하는 게 좋다. INDEX 처리 주요 조인 조건으로 사용된 컬럼에 인덱스가 있는지 확인해 필 요 시 인덱스를 생성한다. 이때 연산자와 인덱스의 상태를 비교 해 처리 범위를 가장 많이 줄여주는 조건이 반영될 수 있도록 한 다. 또한 현재의 인덱스가 최소 범위를 액세스할 수 있도록 구성 돼 있는지 그리고 적절한 인덱스를 사용하는지 확인한다. TABLE FULL SCAN 계획상에 TABLE FULL SCAN이 있을 경우 WHERE 절의 조건으로 인덱스를 타지 않는 이유를 확인해 본다. 물론 실행계 획에 TABLE FULL SCAN이 있더라도 HASH JOIN 등 적절 한 조인방식으로 수행한다면 항상 비효율적인 것은 아니다. 그러 나 파티션 테이블의 경우 모든 파티션을 다 읽는 경우엔 파티션 키가 조회조건으로 사용 될 수 있는지 검토해야 한다(partiton pruning). CARTESIAN MERGE JOIN 비의도적인 Cartesian Product가 생기는지 확인해 조인 조건이 빠지지 않았는지와 INDEX SCAN을 할 수 있는지도 확인한다. 좀더 정확한 실제 실행계획을 생성하고자 할 경우 몇 가지 사 항을 염두에 둬야 한다. 우선 BLIND 변수가 사용된 SQL의 경 우 실제 실행계획 생성 시에도 해당 변수를 이용해 실행계획을 생성해야 상수로 들어오는 조건값에 의해 실행계획이 변경되는 것을 막을 수 있다. 또 실제 실행계획은 가능하면 결과 건수가 나 올 수 있는 조건값을 주고 생성해야 한다. 결과 건수 없이 실제 실행계획을 작성한 경우 반영 후 성능저하 가능성을 사전에 확인 할 수 없으므로 문제가 발생할 가능성이 높다. 성능을 고려한 SQL 작성 가이드 SQL을 작성한 후 성능 튜닝하는 것도 중요하지만, SQL 작성 시에 다음 내용들을 염두에 두고 SQL을 작성한다면 성능 관리 에 대한 영향력을 그만큼 줄일 수 있다. INDEX 변형(INDEX SCAN 방해) <표 3>은 INDEX SCAN을 방해하는 몇 가지 예와 SQL 수정 법이다. 예를 들어 WHERE 조건절을 <표 3>과 같이 생성할 경 우 INDEX SCAN을 방해하게 되므로 해당 인덱스를 사용할 수 없게 된다. OR 사용 제한 WHERE 절에서 단순하게 사용된 OR는 최적화 실행계획에서 분리한 다음 이들을 결합(Concatenation)하는 실행계획이 수립 돼야 하지만, 복잡하게 사용된 OR는 거의 FULL TABLE SCAN을 하게 된다. 따라서 OR를 사용해 FULL TABLE SCAN이 되는 경우 UNION ALL을 사용해 SQL 구문을 분리하 고 이를 통해 인덱스 사용을 유도한다. 또 복잡한 OR를 사용하 는 SQL 구문은 DBA와 상의해 이와 같은 패턴이 반복적으로 나 올 경우 모델 수정이나 별도 다른 방안을 마련해야 한다. SQL에 대한 정확한 이해 NOT IN과 NOT EXISTS의 정확한 차이점을 알고 작성해야 한다. 또 OUTER JOIN은 OUTER JOIN 하는 테이블의 조건 절에는 모두 OUTER JOIN 연산자(+)를 표시해야 하며, OUTER JOIN에 대해 정확하게 이해한 다음 작성해야 한다. 기타 주의할 점해 그 외 주의할 점들은 다음과 같다. - SELECT 절에 과다하게 함수를 사용하지 않도록 한다. - SELECT 절의 DECODE 단계는 가능한 2레벨을 초과하지 않도록 한다. - DISTINCT는 항상 Sort 연산을 수행하므로, 처리 결과값이 항상 유니크 한 경우 DISTINCT 키워드를 사용하지 않는다. - 여러 테이블을 조인할 경우 테이블의 ALIAS 명을 중복해서 사용하지 말고, 각기 구분이 되도록 사용해야 쿼리 분석이 용이하다. HINT를 사 용하면 명확하게 컨트롤 할 수 있다. - 그렇다고 무조건적인 HINT를 사용하지는 말자. INDEX 구성이 완료되 고 튜닝 시점에 DBA와 협의해 HINT를 사용한다.