DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
데이터베이스의 변경 작업이 실제 SQL 구문에 미치는 영향을 정확하게 비교, 분석하는 방법을 배워 보십시오. 본 시리즈의 이전 연재에서 실제 워크로드를 캡처하고 재생하는 Database Replay 툴에 대해 소개한 바 있습니다. Database Replay는 오라클이 새로이 제공하는 Real Application Testing 옵션의 일부분입니다. Real Application Testing 옵션은 데이터베이스에 "실제"로 발생하는 워크로드에 중점을 두어 개발된 기능입니다. Database Replay를 이용하면 캡처된 워크로드 전체를 데이터베이스에서 실행할 수 있습니다. 하지만 사용자가 원하는 것이 이것이 전부가 아닐 수 있습니다. 한 예로, 데이터베이스의 변경 작업으로 인해 SQL 실행 계획과 성능에 어떤 영향이 발생할지 예측해야 하는 경우를 생각해 볼 수 있습니다. Database Replay는 실제로 캡처된 정보에 대해서만 그 초점을 맞추고 있습니다. 따라서 실제 운영 환경에서는 적용되지 않은, 일부 SQL 구문의 매개변수 변경 작업으로 인한 영향을 파악하는 데에는 유용하지 않습니다. Real Application Testing 옵션의 또 다른 구성 요소인 SQL Performance Analyzer가 이에 필요한 기능을 제공합니다. SPA를 이용하면 초기화 매개변수 변경, 옵티마이저 통계 리프레시, 데이베이스 업그레이드와 같은 다양한 변경 작업이 특정 SQL 구문 또는 전체 SQL 워크로드에 미치는 영향을 평가하고, 비교 리포트를 통해 그 차이를 분석할 수 있습니다. 이번 연재에서, SQL Performance Analyzer의 활용 방법에 대해 소개하고자 합니다. 바로 테스트를 시작해 보겠습니다. 먼저 해결하고자 하는 문제를 정의해 봅시다. 아주 전형적인 문제입니다. 오라클이 실행 계획에서 인덱스를 사용하지 않고 있으며, 관리자는 그 이유를 알고 싶어 합니다. 이 질문에 대답하기 위해, 오라클 구루 팀 고먼(Tim Gorman)이 작성한 고전적인 논문("Searching for Intelligent Life in Oracle's CBO")을 인용하고자 합니다. (이 논문은 웹에서 쉽게 검색하실 수 있습니다.) 팀은 먼저, optimizer_index_cost_adj 매개변수를 디폴트 값(100)보다 작은 수치로 변경할 것을 권고하고 있습니다. 논문에서는 변경할 수치를 결정하기 위한 계산식도 함께 제공되고 있습니다. 이 공식을 기준으로, 필자는 적절한 매개변수 값을 10으로 계산하였습니다. 하지만 한 가지 까다로운 문제가 있습니다. 이 변경 작업이 전체 SQL 구문에 어떤 영향을 미칠까요 Oracle Database 11g 이전 버전에서는 모든 SQL 구문을 캡처하고, 트레이스를 걸고, 실행 계획을 일일이 확인해야 했습니다. 이 작업은 많은 시간을 필요로 할 뿐 아니라 오류의 가능성도 높습니다. 하지만 새로운 버전에서는 SQL Performance Analyzer를 이용함으로써 아주 쉽고 간단하게 소기의 목적을 달성할 수 있습니다. 먼저, 아래 쿼리를 데이터베이스에서 실행해 봅시다. 이 쿼리가 애플리케이션에 의해 실행되었다고 가정해 봅시다. SQL에 커멘트를 달아 두면, 나중에 검색하기에 용이할 것입니다. Oracle Enterprise Manager는 SPA 툴을 가장 효과적으로 활용할 수 있는 환경을 제공라인 옵션을 이용할 수도 있지만, Enterprise Manager가 제공하는 관리 편의성을 따라갈 수는 없습니다.) 아래 작업 단계를 실행합니다. 1. Enterprise Manager Database Control을 시작하고 Performance 탭을 클릭합니다. 페이지 하단으로 스크롤하면 아래와 같은 하이퍼링크를 확인할 수 있습니다. 2. Search Sessions를 클릭하면 아래와 같은 화면이 표시됩니다: 3. 커서 캐시로부터 실행한 SQL 구문의 패턴을 검색합니다. SQL 구문에 포함시켜 놓은 커멘트(CONTROL_QUERYn, n=11, 12)를 검색 기준으로 활용합니다. 앞에서 실행된 모든 SQL 구문이 표시될 것입니다. 위의 화면에서 Save to a new SQL Tuning Set이 라는 이름의 라디오 버튼을 확인할 수 있습니다. 이 버튼을 누르고 SQL Tuning Set의 이름으로 CONTROL1을 입력합니다. (참고: 예제의 효과를 높이기 위해 여러 개의 구문을 선택하도록 합니다.) SQL 구문에 커멘트를 달 필요는 없습니다. "SQL Tuning Set"을 생성하고 관련된 모든 구문을 여기에 넣기만 하면 됩니다. 4. SQL Tuning Sets를 클릭하면 SQL Tuning Sets 페이지가 표시됩니다. CONTROL1이라 확인하고 이곳에 SQL 구문을 추가하거나 삭제할 수 있습니다. STS 페이지 화면의 스크린샷이 아래와 같습니다: 5. Performance 페이지에서 SQL Performance Analyzer를 누릅니다. 아래와 같이 메인 SPA 페이지가 표시됩니다. 6. 화면에서 확인할 수 있듯, 아직 아무런 SPA 작업도 정의되지 않은 상태입니다. 새로운 SPA 작업을 정의해 봅시다. 본 예제에서는 optimizer_index_cost_adj 매개변수의 변경으로 인한 영향을 평가해야 하므로, Parameter Change를 클릭합니다. 아래와 같이 SPA Task Definition 페이지가 표시됩니다. 7. 이 페이지에서 SPA 작업에 필요한 정보를 입력해야 합니다. 작업의 이름으로 STS1을 입력합니다. 8. 다음으로 SQL Tuning Set의 이름을 입력합니다. 플래시라이트 아이콘을 클릭하고 CONTROL1이라는 이름의 STS를 선택합니다. 9. Parameter Change 섹션에서는 변경하고자 하는 매개변수를 입력합니다. 플래시라이트 아이콘을 클릭하고 optimizer_index_cost_adj매개변수를 선택합니다. 현재 값(current value)이 이미 입력되어 있을 것입니다. 변경하고자 하는 값(target value)을 Changed Value에 입력합니다. 10. 다음으로, 변경 사항을 비교하는 방법(경과 시간 기준, CPU 타임 기준 등)을 정의할 차례입니다. 예제에서는 Buffer Gets를 선택하기로 합니다. 11. 마지막으로 SPA 작업의 실행 스케줄을 설정합니다. Immediate 라디오 버튼을 눌러, 작업이 바로 실행되도록 합니다. 12. 필요한 정보를 모두 입력하였다면 Submit을 클릭합니다. 생성된 작업을 별도로 모니터링할 수도 있지만, 아래 화면에서 볼 수 있듯 이 페이지 안에서 SPA의 상태를 직접 모니터하는 것도 가능합니다. 13. Refresh를 클릭하여 작업의 현재 상태를 확인합니다. Last Run Status 컬럼 아래의 아이콘을 주목하시기 바랍니다. 이 아이콘에 대한 설명이 다음과 같습니다: 14. 그림의 아이콘은 작업이 현재 실행 중임을 의미합니다. Refresh를 계속 클릭합니다. SPA 작업의 실행이 완료되면 아이콘이 체크 마크(Completed)로 변경됩니다. 15. 결과를 비교하기 위해 SPA 작업 이름 (STS1)를 클릭합니다. 아래와 같은 화면이 표시됩니다. 16. Comparison Report 컬럼 아래의 안경 아이콘을 주목하시기 바랍니다. 이 아이콘을 이용하여 매개변수 변경 이전/이후의 SQL 실행 결과를 비교할 수 있습니다. 아이콘을 클릭합니다. 17. 이제 가장 흥미로운 부분이 소개됩니다. 아래와 같은 화면이 표시될 것입니다. 이 화면에서 우리가 기대했던 결과를 확인할 수 있습니다. 화면의 왼쪽 상단에서 변경 실행 이전/이후 두 차례의 SQL 실행 결과를 비교하고 있습니다. 성능은 전반적으로 60%나 개선되었음을 확인할 수 있습니다! 이것으로 모든 것이 분명하게 확인되었습니다. 화면의 오른쪽에서는 실행 계획이 변경된 SQL 구문의 수가 표시됩니다. 대부분의 실행 계획이 변경되었지만, 일부 구분은 여전히 변경되지 않은 실행 계획을 사용하고 있음을 확인할 수 있습니다. 화면의 하단에는 이번 작업에서 분석된 SQL 구문들의 SQL ID가 표시되고 있습니다. SQL ID 앞에 위치한 작은 화살표 기호는 이 SQL 구문의 성능이 개선되었는지 또는 저하되었는지 알려 주고 있습니다. 또 SQL ID 뒤에 표시된 숫자는 그 영향을 퍼센티지로 표시합니다. 이 숫자를 통해 변경 작업이 각각의 SQL 구문에 미친 영향을 정확하게 파악할 수 있습니다. 필요하다면, SQL ID를 클릭하여 각각의 SQL 구문에 관련된 상세 정보를 확인할 수 있습니다. 가장 많은 영향이 발생한 첫 번째 SQL 구문을 클릭하 이 화면을 통해 SQL 실행 결과에 대한 상세한 통계가 표시되고 있습니다. 또 스크린의. 이것으로, 인덱스를 적용함으로써 사용되는 버퍼의 수가 줄어 들었음을 확인하였습니다. 하지만 모든 것이 좋게만 변한 것일까요 또 다른 SQL 구문을 고려해 봅시다. 이 경우에는 성능 향상 효과가 0.48%에 불과합니다. 그 이유가 무엇일까요 SQL ID를 클릭하면 아래와 같은 화면이 표시됩니다. 이곳에서 변경된 것이 무엇인지 확인할 수 있습니다. 경과 시간(elapsed time)은 0.504초에서 1.022초로 증가했습니다. CPU 시간이 그만큼 증가했기 때문입니다. 그 이유가 무엇일까요 promo_id 컬럼 값이 분포된 패턴을 조회해 본 결과가 아래와 같습니다. promo_id 999는 887,837건 입력되었으며 이는 전체의 97%에 해당합니다. 따라서 인덱스 스캔을 사용하면 오히려 쿼리 성능이 저하될 수 밖에 없는 것입니다. 이러한 경우라면 풀 테이블 스캔이 더 유리할 것입니다. 이처럼, 전체적인 성능이 개선되었음에도 불구하고 일부 SQL 구문의 성능은 오히려 저하되었음을 확인할 수 있습니다. 매개변수의 변경 작업을 검토하면서, 먼저 성능이 개선/저하 되는 SQL 구문들이 갖는 중요도를 미리 고려해야 할 것입니다. 지금까지 데이터베이스 매개변수에 대한 중요 변경 작업의 영향을 분석해 보았습니다. SPA를 이용하면 단순한 추측에 의존하여 잠재적인 성능 영향을 예상할 필요가 없습니다. 그 대신, 실제로 애플리케이션이 사용하는 SQL 구문을 적용해 봄으로써 객관적인 비교 결과를 얻을 수 있습니다. 이제 또 다른 시나리오를 고려해 봅시다. 매개변수를 변경한 이후 성능이 개선되기는커녕 오히려 더 안 좋아졌습니다. 결과 화면이 아래와 같습니다. 모든 SQL 구문이 이전보다 저하된 성능을 보여 주고 있습니다. 문제를 해결하기 위해 (앞의 연재에서 설명한) SQL Plan Management를 이용해 보겠습니다. SPM은 베이스라인을 설정하고 이를 옵티마이저에서 활용하도록 함으로써 가장 효과적인 실행 계획을 선택하고 안정적인 실행 계획 관리 환경을 구현할 수 있게 합니다. 베이스라인 처리된 실행 계획은 비활성화되거나 또는 새로운 베이스라인 실행 계획에 의해 대체될 수 있습니다. SQL 성능 저하 문제를 해결하기 위한 또 다른 방법으로, SQL Tuning Advisor를 활용하여 SQL 튜닝을 위한 권고 사항을 제공받을 수 있습니다. SPA는 데이터베이스 버전 업그레이드, 데이터베이스 패치 셋 적용, 데이터베이스 매개변수 변경, 옵티마이저 매개변수 변경과 같은 다양한 시나리오에서 매우 유용하게 활용됩니다. 한 예로, 옵티마이저 매개변수를 10.2 버전에서 11.1 버전으로 변경하는 경우를 생각해 봅시다. 관리자는 당연히 이러한 변경 작업이 SQL 구문에 미치는 영향을 미리 확인하고 싶어할 것입니다. 이러한 목적을 위한 최적의 도구가 바로 SPA입니다. 위에서 예로 든 작업 과정의 5 번째 단계에서 Database Parameter Changes를 선택하는 대신 Optimizer Changes를 선택해 주기만 하면 됩니다. 그 결과로 표시되는 화면이 아래와 같습니다. 이 화면에서 적절한 소스/타겟 옵티마이저 버전을 선택한 뒤 이후 단계를 실행해 주기만 하면 됩니다. 어떤 상황에서 SPA가 가장 유용하게 활용될 수 있을까요 SPA는 데이터베이스에서 변경 작업을 수행하는 경우라면 어떠한 상황에서든 그 유용성을 발휘합니다. 개별 SQL 구문의 실행 결과를 확인할 수 없는 Database Replay와 달리, SPA를 이용하면 특정 SQL 구문 또는 전체 애플리케이션 SQL 워크로드의 실행 결과를 비교하고 분석할 수 있습니다. 결과적으로 관리자는 변경 이전/이후의 장점과 단점을 비교하고 애플리케이션의 성능을 보장할 수 있는 최적의 대안을 얻어낼 수 있을 것입니다. 이상적인 대안이란 존재하지 않습니다. 모든 변경 작업은 그 나름의 장단점을 가지고 있으며 그렇기 때문에 결정을 내리기가 그토록 어려운 것입니다. SPA는 관리자로 하여금 정확한 측정 결과를 바탕으로 보다 쉽게 결정을 내릴 수 있게 해 주BLE>SQL Performance
SQL Performance Analyzer
간략한 개요
예제 시나리오
select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;
select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;
select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;
select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;
select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2;
select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3;
select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4;
select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5;
select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9;
사용 방법
활용 사례
결론