SQL Access Advisor

간략한 개요

데이터의 실제 활용 빈도, 활용 유형을 기준으로 파티셔닝, 인덱싱, MV(materialized view)를 생성하여 스키마 설계를 개선하는 방법을 배워 보십시오.

Oracle Database 10g가 제공하는 다양한 어드바이저(advisor)들을 이용하면 최선의 작업 방법에 대한 도움을 얻을 수 있습니다. 그 한 가지 예가 SQL Tuning Advisor입니다. SQL Tuning Advisor는 쿼리 튜닝에 대한 권고 사항을 제시하고, 보다 세부적인 최적화 프로세스를 적용할 수 있게 합니다.

하지만 다음과 같은 튜닝 시나리오를 고려해 봅시다. 어떤 쿼리를 실행할 때 인덱스를 사용하면 성능에 많은 도움이 될 것으로 보입니다. 하지만 이 쿼리는 단 한 번만 실행됩니다. 따라서 쿼리가 얻는 성능 효과가 크다 해도 인덱스를 생성하고 유지하는 비용이 이를 상쇄하고도 남을 것입니다. 이와 같은 방법으로 시나리오를 분석하기 위해서는, 먼저 쿼리가 얼마나 자주, 그리고 어떤 목적으로 사용되는지를 파악하고 있어야 합니다.

또 다른 어드바이저, SQL Access Advisor가 바로 이러한 분석 작업을 담당합니다. Oracle Database 10g에서 제공되어 온 인덱스, MV(materialized view) 분석 기능에 더하여, Oracle Database 11g SQL Access Advisor에는 테이블과 쿼리의 분석을 통해 적용 가능한 파티셔닝 전략을 도출하는 기능이 추가되었습니다. 이 기능은 최적의 스키마를 설계하는데 매우 큰 도움이 됩니다. Oracle Database 11g의 SQL Access Advisor는 액세스 구조의 생성, 유지 비용을 포함하는 전체 워크로드에 대한 분석 결과를 제시합니다.

이번 연재에서는 새로운 SQL Access Advisor를 이용하여 일반적인 문제를 해결하는 방법을 설명하고자 합니다. (참고: 본 데모에서는 하나의 구문에 대한 분석 과정을 예로 들어 설명하고 있습니다. 하지만, 오라클은 하나의 SQL 구문이 아닌 전체 워크로드에 대해 SQL Access Advisor를 활용할 것을 권고하고 있습니다.)

문제

전형적인 문제가 아래와 같습니다. 아래의 SQL 구문이 애플리케이션에 의해 실행되었습니다. 이 쿼리는 많은 리소스를 사용하며 매우 느리게 수행되는 것으로 보입니다.

SQL은 두 개의 테이블, RES와 TRANS에 접근하고 있습니다. 여기서 TRANS는 RES의 자식 테이블(child table)입니다. DBA는 이 쿼리의 성능을 개선하기 위한 방안을 고안해 내도록 요청 받습니다. DBA는 새로운 임무를 위한 최적의 툴이 SQL Access Advisor라고 판단합니다.

DBA는 커맨드 라인 또는 Oracle Enterprise Manager Database Control을 통해 어드바이저에 접근할 수 있습니다. 하지만 GUI를 이용하면 해결책을 시각화하고 간단한 마우스 클릭 작업만으로 여러 가지 작업을 쉽게 수행할 수 있다는 이점이 있습니다.

Enterprise Manager에서 SQL Access Advisor를 이용하여 쿼리의 성능 문제를 해결하기 위해, DBA는 다음과 같은 수순을 밟습니다.

1. 물론 제일 먼저 할 일은 Enterprise Manager를 실행하는 것입니다. Database Homepage에서 페이지 하단으로 스크롤하면 아래와 같은 여러 개의 하이퍼링크를 확인할 수 있습니다.

2. 이 메뉴에서 Advisor Central을 클릭하면 아래와 같은 화면이 표시됩니다. 여기에서는 스크린의 상단 부분만이 보여지고 있습니다.

3. SQL Advisors를 클릭하면 아래와 같은 화면이 표시됩니다:

4. 이 화면에서 SQL Access Advisor 세션의 스케줄과 옵션을 설정할 수 있습니다. 먼저 어드바이저를 통해 작업 대상 SQL 구문을 수집해야 합니다. 가장 간단한 방법은 Current, Recent SQL Activity를 통해 공유 풀(shared pool)에서 구문을 가져오는 것입니다. 이 방법을 사용하면 공유 풀에 캐시된 모든 SQL 구문을 가져와 분석할 수 있습니다.

하지만 경우에 따라 공유 풀의 모든 구문을 가져오는 대신 그 중 일부만을 분석 대상으로 포함시키고자 할 수 있습니다. 이를 위해 다른 화면에 "SQL Tuning Set"을 생성하고, 기존 화면에서 생성된 셋 네임(set name)을 참조해야 합니다.

또는 가상적인 시나리오를 기준으로 인공적으로 생성된 워크로드를 실행하기를 원할 수도 있습니다. 이러한 가상적인 SQL 구문은 아직 실행된 일이 없으므로 공유 풀에서 확인할 수 없습니다. 따라서 이 구문들을 직접 생성하여 별도의 테이블에 저장해 두어야 합니다. 세 번째 옵션(Create a Hypothetical Workload...)에서는 테이블 네임과 스키마 네임을 직접 입력해 주어야 합니다.

본 문서에서는 공유 풀로부터 SQL 구문을 가져오는 방법을 사용하기로 하겠습니다. 디폴트로 설정된 화면의 첫 번째 옵션을 선택합니다.

5. 하지만 공유 풀의 구문을 모두 가져 오는 대신, 중요한 몇 가지만을 선택해야 할 수도 있습니다. 한 예로, 사용자 SCOTT에 의해 실행된 SQL 구문을 분석해야 하는 경우를 들 수 있습니다. 이러한 경우라면 다른 사용자들이 실행한 SQL 구문을 분석 대상에서 제외해야 할 것입니다. 이를 위해 아래 화면의Filter Options앞에 위치한 "+" 기호를 클릭합니다.

6. 화면의 텍스트 상자에 SCOTT를 입력하고 Include only SQL... 라디오 버튼(디폴트)을 선택합니다. 마찬가지 방법으로, 일부 사용자들을 제외하도록 설정할 수도 있습니다. 예를 들어, SYS, SYSTEM, SYSMAN 사용자들을 제외한 모든 데이터베이스 작업 내역을 캡처하는 경우를 생각해 봅시다. 이 사용자들을 텍스트 상자에 입력하고 Exclude all SQL statements... 버튼을, Action, 또는 SQL 구문에 포함된 특정 문자열을 기준으로 필터링을 수행하여 사용자에게 관심 있는 구문만을 선택적으로 분석할 수도 있습니다. SQL 캐시에 저장된 구문의 일부만을 선택하면 분석 작업도 한층 빨라질 것입니다. 예제에서는 사용자 SCOTT에 의해 단 하나의 구문만이 실행되었다고 가정하고 있습니다. 필요한 경우 추가적인 필터링 조건을 입력하여 분석된 세트에 하나의 SQL 구문만이 포함되도록 설정할 수 있을 것입니다.

8. Next를 클릭합니다. 아래와 같은 화면이 표시됩니다(화면의 상단만이 보여지고 있습니다):

9. 이 화면에서 검색에 포함시킬 권고 유형을 설정할 수 있습니다. 본 예제에서는 인덱스, MV(materialized view), 파티셔닝에 관련한 권고 사항을 확인하고자 합니다. 따라서 해당 항목의 체크박스를 모두 체크합니다. Advisor Mode에서는 디폴트 모드 이외에도 높은 비용을 수반하는 SQL 구문만을 범위로 한정하는 Limited Mode를 사용할 수 있습니다. Limited Mode는 더 빠르고 효율적인 실행이 가능하다는 장점이 있습니다. 전체 SQL을 분석하려면 Comprehensive Mode를 사용해야 합니다. (본 예제에서는 단 하나의 SQL 구문만을 분석 대상으로 설정하였으므로 모드 선택이 의미를 갖지 않습니다.)

10. 스크린 하단에는 SQL 구문의 우선 순위 설정, 테이블스페이스 사용 여부 등의 고급 옵션이 제공됩니다. 이 부분은 디폴트 상태로 둡니다(자세한 내용은 뒷부분에 설명하겠습니다). Next를 클릭하면 스케줄링 화면이 표시됩니다. Run Immediately를 선택하고 Next를 클릭합니다.

11. Submit을 클릭하면 Scheduler 작업이 생성됩니다. 화면에 표시되는 작업 하이퍼링크를 클릭할 수 있습니다. 작업은 Running 상태로 표시될 것입니다.

12. Refresh를 반복적으로 누르면서 Last Run Status 컬럼의 값이 SUCCEEDED로 변할 때까지 기다립니다.

13. 이제 1단계에서처럼 Database Homepage로 돌아가 Advisor Central을 클릭합니다. 이제 아래 화면처럼 SQL Access Advisor 로우가 표시되는 것을 확인할 수 있습니다.

14. 화면을 통해 SQL Access Advisor 태스크가 완료 (COMPLETED) 되었음을 알 수 있습니다. 이제 View Result 버튼을 누릅니다. 결과 화면이 아래와 같습니다:

15. 화면이 모든 것을 말해 줍니다! SQL Access Advisor가 SQL 구문을 분석하고 쿼리 성능을 10배 이상 개선할 수 있는 몇 가지 해법을 발견했습니다. 자세한 권고 사항을 확인하려면 Recommendations 탭을 클릭합니다. 아래와 같은 화면이 표시될 것입니다.

16. 이 스크린에서는 좀 더 상위 레벨의 유용한 정보가 제공되고 있습니다. 예를 들어 Actions 컬럼에, ID = 1인 구문에 대해 2가지 권고 조치 사항이 제시되고 있음을 확인할 수 있습니다. Action Type은 색상이 입혀진 사각형을 통해 조치 사항의 유형을 표시하고 있습니다. 그 아래에 위치한 아이콘 가이드에서 조치 사항이 인덱스와 파티션에 관련된 내용임을 알 수 있습니다. 이 조치 사항을 수행함으로써 성능을 극적으로 향상시킬 수 있을 것입니다.

어떤 SQL 구문의 성능 개선이 가능한지 확인하려면 ID를 클릭합니다. 그러면 아래와 같은 화면이 표시될 것입니다. 물론 이 분석 작업은 단 하나의 구문만을 대상으로 하고 있으므로, 하나의 결과만이 표시되고 있습니다. 여러 개의 구문이 분석 대상으로 포함되었다면, 모든 결과를 한 눈에 확인할 수 있을 것입니다.

17. 위의 화면에서 Recommendation ID 컬럼을 주목하시기 바랍니다. 컬럼의 하이퍼링크를 클릭하면 아래와 같이 상세한 권고 사항이 표시됩니다.

18. 이 화면을 통해 문제 해결 방법을 매우 명확하게 확인할 수 있습니다. 2가지 권고 사항이 제시되고 있습니다. 그 하나는 파티셔닝된 테이블을 생성하는 것이고 두 번째는 인덱스를 사용하는 것입니다. 또, 어드바이저는 인덱스가 이미 존재함을 확인하고 인덱스를 유지할 것을 권고합니다.

Action 컬럼 아래의 PARTITION TABLE을 클릭하면 오라클이 파티셔닝된 테이블을 생성하기 위해 사용하는 실제 스크립트를 확인할 수 있습니다. 클릭하기 전에, 텍스트 상자에 테이블스페이스 네임을 입력해야 합니다. 이 작업을 통해 SQL Access Advisor가 스크립트 적용 과정에서 해당 테이블스페이스를 사용하도록 설정할 수 있습니다.

이 스크립트는 새로운 테이블을 생성한 뒤 리네임(rename) 작업을 통해 기존 테이블과 매치시키는 작업을 수행합니다.

19. 마지막 탭인 Details는 태스크에 관련된 몇 가지 흥미로운 정보를 제공하고 있습니다. 이 정보가 분석 과정에서 중요하지 않을 수도 있습니다. 하지만 어드바이저가 어떻게 제시된 결론에 도달했는지에 대한 단서가 제공되고 있으므로, 튜닝 스킬을 쌓는데 좋은 참고가 될 것입니다. 화면의 두 개의 영역으로 구분됩니다. 첫 번째 영역인 Workload and Task Options 화면이 아래와 같습니다.

20. 화면의 하단에는 태스크의 실행 로그가 표시되고 있습니다. 어드바이저가 모든 SQL 구문을 처리하지 못하는 경우도 있습니다. 일부 SQL 구문이 분석되지 않은 경우, 이곳의 Invalid SQL String: Statements discarded 카운트에 누적됩니다. 또 전체가 아닌 일부 SQL 구문만이 분석된 이유가 무엇인지 이곳에서 확인할 수 있습니다.

고급 옵션

위의 10 단계에서 고급 설정에 대해 간단히 언급한 바 있습니다. 이 옵션들에 대해 설명해 보기로 하겠습니다.

Advanced Options 왼쪽의 "+" 기호를 클릭하면 아래와 같은 화면이 표시됩니다:

이 화면에서 인덱스가 생성되는 테이블스페이스 네임, 또는 스키마 네임 등을 입력할 수 있습니다. 파티셔닝 관련 조언 항목으로 테이블스페이스와 파티션을 설정하는 것도 가능합니다.

이 화면에서 가장 중요한 항목은 바로 Consider access structures creation costs recommendations 체크박스입니다. 이 박스를 체크하면, SQL Access Advisor는 인덱스 생성의 비용을 고려 사항에 포함시킵니다. 예를 들어, 10개의 새로운 인덱스가 필요한 경우, SQL Access Advisor는 수반되는 비용을 고려하여 인덱스 생성을 추천하지 않을 수도 있습니다.

또 인덱스의 최대 크기를 화면에서 설정할 수도 있습니다.

SQL Tuning Advisor와의 차이점

서론 부분에서 SQL Access Advisor와 SQL Tuning Advisor의 차이점에 대해 간략하게 설명한 바 있습니다. 여기에서는 좀 더 자세히 짚어 보기로 하겠습니다. 간단한 데모를 통해 그 차이를 쉽게 이해하실 수 있을 것입니다.

SQL Advisors 화면에서 SQL Tuning Advisor를 선택하여 실행합니다. 실행이 완료된 후 표시되는 화면의 일부가 아래와 같습니다:

여기서 권고 사항을 확인하기 위해 View를 클릭하면 아래와 같은 화면이 표시됩니다:

권고 사항을 주의 깊게 살펴 보시기 바랍니다. TRANS 테이블의 RES_ID 컬럼에 인덱스를 생성하라는 조언이 제시되고 있습니다. 하지만 SQL Access Advisor에서는 이 권고 사항이 제공되지 않았으며, 그 대신 테이블 파티셔닝을 사용하도록 권고 되었습니다. 그 이유가 무엇일까요 사용된 액세스 패턴과 데이터를 기준으로, SQL Access Advisor는 컬럼에 인덱스를 생성하는 것보다 파티셔닝을 사용하는 것이 더 효율적이라고 판단한 것입니다. 따라서 SQL Tuning Advisor보다 더 "현실적"인 권고 사항을 제시한 것으로 볼 수 있습니다.

SQL Tuning Advisor가 제공하는 권고 사항은 다음과 같은 네 가지 목표를 기준으로 제시됩니다:

• 누락되거나 업데이트되지 않은 성능 통계를 포함하는 오브젝트의 통계 수집
• 데이터 오차(skew), 복잡한 조건문, 업데이트되지 않은 통계의 존재를 확인
• 성능 최적화를 위해 SQL을 재구성
• 새로운 인덱스 생성을 권고

이러한 권고 사항들은 전체 워크로드가 아닌 단일 구문에만 한정됩니다. 따라서 부하가 심한 비즈니스 크리티컬 쿼리에는 SQL Tuning Advisor를 가끔씩 적용해 줄 필요가 있습니다. SQL Tuning Advisor는 성능을 크게 개선해 줄 수 있는 인덱스만을 추천하는 반면, SQL Access Advisor는 좀 더 세부적인 튜닝을 가능하게 한다는 점을 참고하시기 바랍니다. 또 SQL Tuning Advisor는 파티셔닝에 관련한 조언을 제공하지 않는다는 차이도 있습니다.

활용 사례

SQL Access Advisor는 쿼리 또는 스키마의 튜닝에 유용하게 활용됩니다. 관리자는 SQL Access Advisor를 이용하여 효율적인 SQL 튜닝 계획을 수립할 수 있습니다.

1. 높은 비용을 발생시키는 SQL 구문을 검색하거나, 전체 워크로드를 진단합니다.
2. 의심스러운 구문을 SQL Tuning Set에 포함시킵니다.
3. SQL Tuning Advisor와 SQL Access Advisor를 동시에 활용하여 분석을 수행합니다.
4. 분석 결과와 권고 사항을 확인합니다.
5. 권고 사항을 SQL Performance Analyzer에 적용합니다(관련 연재 보기).
6. SQL Performance Analyzer를 통해 변경 이전/이후 결과를 확인하여 최적의 방안을 도출합니다.
7. 최적의 스키마 설계를 얻을 때까지 위의 작업을 반복합니다.
8. 최적의 스키마 설계를 얻었다면 SQL Plan Management 베이스라인을 이용하여 실행 계획을 적용합니다.

결론

데이터베이스 구조의 튜닝은 가장 까다로운 작업이면서도 가장 보상이 큰 작업이기도 합니다. 또 파티셔닝은 매우 효과적인 튜닝 도구이지만 파티셔닝 방법을 선택하는 것이 그리 쉽지는 않습니다. SQL Access Advisor는 이러한 작업을 수행하는데 필요한 기능을 효과적으로 지원하고 있습니다.