'Oracle 11g'에 해당되는 글 1건

  1. 2009.08.25 기준선 및 개선된 계획
ORACLE/11G2009. 8. 25. 08:00

기준선 및 개선된 계획

반응형


 

실행 계획을 최적화하기 위해 Oracle Database 11 g 의 SQL 계획 관리 기능 사용

 

제대로 작동하던 데이터베이스 쿼리의 성능이 갑자기 떨어지는 상황이 발생한 적이 있습니까? 여러분의 십중팔구는 실행 계획의 변경사항에서 그 원인을 찾으려 했을 것입니다. 더 자세한 분석에서는 이러한 성능 변화가 해당 쿼리에서 참조한 테이블과 인덱스에 대해 새로 수집된 옵티마이저 통계 때문인 것으로 밝혀졌을 수도 있을 것입니다.

 

이런 상황에 당황하여 통계 수집을 중단하기로 성급하게 결정한 적이 있으십니까? 그러한 조치는 실행 계획을 해당 쿼리에 대해 거의 동일하게 유지시키지만 다른 것들은 더욱 악화시킬 수 있습니다. 오래된 통계를 통해 생성된 차선의 실행 계획은 다른 쿼리의 성능 또는 다른 술어(WHERE 절)를 가진 동일한 쿼리의 성능을 악화시킵니다.

 

다음에 어떤 조치를 취하든 여기에는 일부 위험이 따르기 마련입니다. 따라서 어떻게 하면 위험을 완화하고, 옵티마이저 통계가 정기적으로 수집되고, 모든 SQL문을 크게 변경하지 않고도(힌트 추가 등) 제대로 수행할 수 있는 정상적인 환경을 유지하면서, 생성된 SQL문에 대한 실행 계획을 최적으로 유지할 수 있겠습니까? 여러분 중에는 저장된 개요를 사용하여 계획을 유지하려 하는 분도 있을 것입니다. 하지만 그러한 행동은 옵티마이저가 도움이 될만한 실행 계획을 생성하는 것을 방해할 뿐입니다.

 

Oracle Database 11g에서는 새로운 SQL 계획 관리 기능을 사용하여 실행 계획이 계속해서 어떻게 변하는지 확인하고, 실행 계획을 사용하기 전에 먼저 데이터베이스에서 실행하여 계획을 확인하며, 통제된 방식으로 계획이 더 나아지도록 점차 개선할 수 있습니다.

 

SQL 계획 관리

 

SQL 계획 관리를 사용하는 경우, 옵티마이저는 특별 저장소인 SQL 관리 베이스에 생성된 실행 계획을 저장합니다. 특정 SQL문에 대해 저장된 모든 계획은 해당 SQL문의 계획 내역의 일부가 됩니다.

 

내역의 일부 계획은 "수락됨(Accepted)"으로 표시될 수 있습니다. SQL문이 재분석될 경우 옵티마이저는 내역 중 수락된 계획만을 고려합니다. 해당 SQL문에 대해 수락된 계획 세트를 SQL 계획 기준선 (SQL Plan Baseline) 또는 줄여서 기준선 (Baseline)이라고 합니다.

 

그러나 옵티마이저는 계속해서 더 나은 계획을 생성하기 위해 노력합니다. 옵티마이저가 새로운 계획을 생성한 경우, 계획 내역에 해당 계획을 추가하지만, 이 계획이 기준선의 수락된 모든 계획보다 더 나은 경우가 아니라면 SQL을 재분석할 때 고려하지 않습니다. 따라서 SQL 계획 관리를 사용하면 SQL문이 갑자기 효과가 떨어지는 계획을 가져와서 성능이 떨어지는 결과를 초래하는 현상이 발생하지 않습니다.

SQL 계획 관리를 사용하면 SQL문의 계획 내역에서 사용 가능한 모든 계획을 검사하고, 상대적인 효율성을 비교할 수 있습니다. 또한 특정 계획이 수락 상태로 되도록 하기도 하며 심지어 계획을 영구(고정) 계획이 되게 할 수도 있습니다.

 

이 문서에서는 SQL문의 최적 성능을 보장하기 위해 명령 행에서 Oracle Enterprise Manager 및 SQL을 사용하여 기준선의 캡처, 선택 및 개선을 포함한 SQL 계획 기준선의 관리 방법에 대해 알아봅니다.

 

캡처

 

SQL 계획 관리의 캡처 기능은 SQL문에서 사용한 다양한 옵티마이저 계획을 캡처합니다. 기본적으로 캡처 기능은 비활성화되어 있습니다. 즉, SQL 계획 관리에서 분석 또는 재분석중인 SQL문의 내역을 캡처하지 않습니다.

 

한 세션에서 파생된 몇몇 SQL문 예제에 대한 기준선을 캡처해 보겠습니다. 여기서는 Oracle Database 11 g 과 함께 제공된 샘플 스키마(SH)와 SALES 테이블을 사용할 것입니다.

 

먼저 세션에서 기준선 캡처 기능을 활성화합니다.

 

alter session <br /> set optimizer_capture_sql_plan_baselines = true;<br />

 

이제 이 세션에서 실행된 모든 SQL문이 해당 최적화 계획과 함께 SQL 관리 베이스에 캡처됩니다. SQL문의 계획은 변경될 때마다 계획 내역에 저장됩니다. 이를 확인하려면 목록 1에 표시된 스크립트를 실행해 보십시오. 이 스크립트는 완전히 동일한 SQL을 다른 환경에서 실행합니다. 먼저, SQL이 모든 기본값으로 실행됩니다(암시적 기본값 optimizer_mode = all_rows 포함). 다음 실행 시, optimizer_mode 매개 변수 값은 first_rows로 설정되어 있습니다. SQL의 세 번째 실행 전에 테이블 및 인덱스에 대한 새로운 통계를 수집합니다.

 

코드 목록 1: SQL 계획 기준선 캡처

alter session set optimizer_capture_sql_plan_baselines = true;<br /> -- First execution. Default Environment<br /> select * /* ARUP */ from sales<br /> where quantity_sold &gt; 1 order by cust_id;<br /> -- Change the optimizer mode<br /> alter session set optimizer_mode = first_rows;<br /> -- Second execution. Opt Mode changed<br /> select * /* ARUP */ from sales<br /> where quantity_sold &gt; 1 order by cust_id;<br /> -- Gather stats now<br /> begin<br /> dbms_stats.gather_table_stats (<br /> ownname =&gt; 'SH',<br /> tabname =&gt; 'SALES',<br /> cascade =&gt; TRUE,<br /> no_invalidate =&gt; FALSE,<br /> method_opt =&gt; 'FOR ALL INDEXED COLUMNS SIZE AUTO',<br /> granularity =&gt; 'GLOBAL AND PARTITION',<br /> estimate_percent =&gt; 10,<br /> degree =&gt; 4<br /> );<br /> end;<br /> /<br /> -- Third execution. After stats<br /> select * /* ARUP */ from sales<br /> where quantity_sold &gt; 1 order by cust_id;<br />

 

목록 1의 SQL 실행마다 계획이 변경될 경우, 해당 SQL문의 계획 내역에 다른 계획이 캡처됩니다. (/* ARUP */ 코멘트는 공유 풀의 특정 SQL문을 쉽게 식별합니다.)

계획 내역을 확인하는 가장 쉬운 방법은 Oracle Enterprise Manager를 사용하는 것입니다. Database 기본 페이지에서 Server 탭을 선택한 다음 SQL Plan Control을 클릭합니다. 페이지에서 SQL Plan Baseline 탭을 선택합니다. 그림 1처럼 해당 페이지에서 ARUP 이름이 포함된 SQL문을 검색합니다. 그러면 화면의 하단부에 SQL문에 대한 계획 내역이 표시됩니다.

 

그림 1: SQL 계획 내역

SQL 계획 이름(예: SYS_SQL_PLAN_27a47aa154bc8843)을 클릭하면 계획 내역에 저장된 계획의 세부 정보를 확인할 수 있습니다. 화면의 중요한 열은 다음과 같습니다.

  • Enabled는 계획의 활성 여부를 나타냅니다.
  • Accepted는 옵티마이저에서 계획을 고려하는지 여부를 나타냅니다. 하나 이상의 계획이 수락된 경우 옵티마이저는 최적의 계획을 선택합니다.
  • Fixed는 계획이 해당 SQL문에 영구적으로 사용될 것인지 여부를 나타냅니다. 하나 이상의 계획이 고정된 경우 옵티마이저는 최적의 계획을 선택합니다.
  • Auto Purge는 계획이 사용되지 않는 경우, 일정 기간 후에 계획 내역에서 해당 계획이 자동으로 삭제되는지 여부를 나타냅니다. 자동 삭제가 활성화된 경우, 지정한 기간 후에 계획 내역에서 사용되지 않는 계획이 자동으로 삭제됩니다. 사용되지 않는 계획이 삭제되도록 지정한 기간은 그림 1의 Plan Retention(Weeks) 레이블 옆에 표시되어 있습니다. 이 경우 53주로 설정되어 있지만 Configure 버튼을 클릭하여 변경할 수 있습니다.

또한 Settings 섹션에서 적절한 링크를 통해 이 Oracle Enterprise Manager 화면에서 SQL 계획 기준선의 캡처 및 사용을 활성화할 수도 있습니다.

 

커서 캐시 또는 SQL 튜닝 세트에서 SQL 계획 기준선에 계획을 로드할 수도 있다는 것을 참고하십시오. 계획을 SQL 계획 기준선에 수동으로 로드한 경우, 이 로드된 계획은 수락된 계획으로 추가됩니다. 자세한 내용은 Oracle Database Performance Tuning Guide의 15장 "Using SQL Plan Management"를 참조하십시오.

 

기준선 사용

 

SQL 계획 기준선을 캡처한 경우, 옵티마이저를 활성화하여 해당 기준선을 사용할 수 있습니다.

 

alter session set <br /> optimizer_use_sql_plan_baselines = true;<br />

 

기준선 사용이 활성화된 경우, 옵티마이저가 SQL문을 재분석할 때 해당 SQL문의 기준선에 저장된 계획을 검사하고 최적의 계획을 선택합니다. 이것이 바로 기준선의 가장 중요한 이점입니다. 또한 옵티마이저는 SQL문을 계속 재분석하며(기준선의 존재가 이를 방해하지 않음), SQL의 계획 내역에 새로 생성된 계획이 없는 경우, 이 계획을 추가하지만 “Accepted” 상태로 추가하지는 않습니다. 따라서 새로 생성된 계획이 더 나쁜 경우에도 그 계획은 사용되지 않기 때문에 SQL 성능은 영향을 받지 않습니다. 그러나 데이터 배포 및 애플리케이션 논리에 관한 개인적인 지식에 따라 새 계획이 더 낫다고 결정하는 경우도 있습니다. 예를 들어, 테이블이 실제로 비어 있을 때 계획이 캡처됐다고 가정해 봅시다. 이 경우 옵티마이저는 인덱스 검색을 선택합니다. 그러나 여러분은 나중에 SQL문을 호출하기 전에 애플리케이션이 테이블을 채운다는 것과 전체 테이블 검색이 계획에는 결국에는 더 좋다는 것을 알고 있습니다. 이런 상황에서는 새 계획을 검사하여 더 나은 경우 이를 수락할 수 있습니다. 그리고 옵티마이저는 그 후에 이 계획을 고려하게 됩니다. 이 경우 좋은 점은 항상 좋은 계획이 사용되고, 옵티마이저가 더 나은 계획을 생성한 경우에는 비교하여 사용할 수 있다는 것입니다.

SQL문의 기준선의 계획을 사용하지 않고자 하는 경우에는 SQL문을 호출하기 전에 세션에서 다음과 같은 문을 사용하여 기준선 사용을 비활성화할 수 있습니다.

 

alter session set <br /> optimizer_use_sql_plan_baselines = false;<br />

 

목록 2에서는 동일한 쿼리를 두 번 실행합니다. 먼저 기준선을 활성화하여 실행한 다음, 기준선을 비활성화하고 실행합니다. 여기서 기준선을 비활성화한 후 계획이 어떻게 변경되는지 확인할 수 있습니다. 맨 처음 옵티마이저는 SALES_TIME_BIX 인덱스에서 BITMAP INDEX FULL SCAN을 선택했습니다. 기준선을 비활성화한 후에는 계획이 SALES 테이블의 TABLE ACCESS FULL로 바뀌었습니다. 이는 이 계획이 옵티마이저 통계와 옵티마이저에 지금 당장 영향을 미치는 다른 변수를 기준으로 최적의 계획으로 여겨지기 때문입니다. 먼저 기준선을 활성화한 경우에서는 옵티마이저가 기준선에 저장된 수락된 계획 세트에서 최적의 계획을 선택했습니다.

코드 목록 2: SQL 계획 기준선 사용

SQL&gt; explain plan for select * /* ARUP */ from sales<br /> 2 where quantity_sold &gt; 1 order by cust_id;<br /> Explained.<br /> SQL&gt; select * from table(dbms_xplan.display(null, null, 'basic'));<br /> PLAN_TABLE_OUTPUT<br /> ---------------------------<br /> Plan hash value: 143117509<br /> --------------------------------------------------------------<br /> | Id | Operation | Name |<br /> --------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | |<br /> | 1 | SORT ORDER BY | |<br /> | 2 | PARTITION RANGE ALL | |<br /> | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |<br /> | 4 | BITMAP CONVERSION TO ROWIDS | |<br /> | 5 | BITMAP INDEX FULL SCAN | SALES_TIME_BIX |<br /> --------------------------------------------------------------<br /> -- Now disable baselines and look at the latest plan<br /> SQL&gt; alter session set optimizer_use_sql_plan_baselines = false;<br /> Session altered.<br /> SQL&gt; explain plan for select * /* ARUP */ from sales<br /> 2 where quantity_sold &gt; 1 order by cust_id;<br /> Explained.<br /> SQL&gt; select * from table(dbms_xplan.display(null, null, 'basic'));<br /> PLAN_TABLE_OUTPUT<br /> ----------------------------<br /> Plan hash value: 3803407550<br /> --------------------------------------<br /> | Id | Operation | Name |<br /> --------------------------------------<br /> | 0 | SELECT STATEMENT | |<br /> | 1 | SORT ORDER BY | |<br /> | 2 | PARTITION RANGE ALL | |<br /> | 3 | TABLE ACCESS FULL | SALES |<br /> --------------------------------------<br />

 

관리 및 개선

 

특정 SQL문에 대한 기준선을 만든 후, 그림 1에 표시된 Oracle Enterprise Manager 화면에서 연관된 계획 이름을 클릭하여 (Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab) 기준선을 검사하고 계획의 세부 정보를 확인할 수 있습니다. 특정 계획이 좋아지지 않을 경우, Disable 버튼을 클릭하여 계획을 완전히 비활성화할 수 있습니다. 나중에 생각이 바뀔 경우에는 다시 Enable 버튼을 클릭할 수 있습니다.. Drop 버튼을 사용하면 SQL 관리 베이스에서 계획이 완전히 삭제됩니다. 계획이 사용되지 않고 보관 기간이 경과하면 해당 계획은 자동으로 삭제된다는 점을 참고 하십시오.

 

현재 기준선의 계획이 최적이 아니고 계획 내역의 다른 계획이 더 낫다고 생각되는 경우, 진화 함수(Evolve Function)를 사용하여 계획의 성능을 비교해볼 수 있습니다(Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline 탭 또는 명령 행에서 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 함수 사용). 진화 함수를 사용하려면 그림 1의 Oracle Enterprise Manager 화면에서 비교할 계획을 선택한 다음 Evolve 버튼을 클릭합니다. 그러면 옵티마이저가 선택한 최고의 계획과 여러분이 선택한 계획 간의 비교가 수행됩니다. 이 기능으로 목록 3에서 보여지는 것과 같은 보고서가 생성됩니다. 목록 상단의 다음 행을 주목하십시오:

 

코드 목록 3: 기준선 개선 보고서

-----------------------------------------------------<br /> Evolve SQL Plan Baseline Report<br /> -----------------------------------------------------<br /> Inputs:<br /> ----<br /> PLAN_LIST = SYS_SQL_PLAN_27a47aa15003759b<br /> TIME_LIMIT = DBMS_SPM.AUTO_LIMIT<br /> VERIFY = YES<br /> COMMIT = YES<br /> Plan: SYS_SQL_PLAN_27a47aa15003759b<br /> ----------------------<br /> Plan was verified: Time used 41.06 seconds.<br /> Failed performance criterion: Compound improvement ratio &lt; .36<br /> Baseline Plan Test Plan Improv. Ratio<br /> -------------- --------- ------------- <br /> Execution Status: COMPLETE COMPLETE<br /> Rows Processed: 0 0<br /> Elapsed Time(ms): 5036 1033 4.88<br /> CPU Time(ms): 254 700 .36<br /> Buffer Gets: 1728 43945 .04<br /> Disk Reads: 254 22 11.55<br /> Direct Writes: 0 0<br /> Fetches: 49 22 2.23<br /> Executions: 1 1<br /> --------------------------------------------------------------------<br /> Report Summary<br /> --------------------------------------------------------------------<br /> Number of SQL plan baselines verified: 1.<br /> Number of SQL plan baselines evolved: 0.<br /> Failed performance criterion: <br /> Compound improvement ratio &lt; .36. <br />

 

이 행에서는 새로 고려된 계획이 원래 계획보다 성능이 떨어지는 것으로 표시됩니다. 따라서 옵티마이저의 최적 계획 선택을 위한 대체 항목으로서 거부되었습니다. 비교로 인해 개선 요인이 1보다 큰 것으로 드러나면 SQL 계획 관리에서는 해당 계획을 옵티마이저가 고려할 후보로 수락했을 것입니다.

진화 함수를 통한 결정이 정확하지 않다고 생각되어 옵티마이저가 한 특정 계획을 사용하도록 하고 싶은 경우에는 어떻게 하시겠습니까? 이는 계획이 기준선에 고정되도록 함으로써 할 수 있습니다. 목록 4처럼 dbms_spm 패키지에서 alter_sql_plan_baseline 함수를 실행하여 계획이 고정되게 할 수 있습니다.

 

코드 목록 4: 계획 기준선 고정

declare<br /> l_plans pls_integer;<br /> begin<br /> l_plans := dbms_spm.alter_sql_plan_baseline (<br /> sql_handle =&gt; 'SYS_SQL_f6b17b4c27a47aa1',<br /> plan_name =&gt; 'SYS_SQL_PLAN_27a47aa15003759b',<br /> attribute_name =&gt; 'fixed',<br /> attribute_value =&gt; 'YES'<br /> );<br /> end;<br /> -- Now examine the plan:<br /> SQL&gt; explain plan for select * /* ARUP */ from sales<br /> 2 where quantity_sold &gt; 1 order by cust_id;<br /> Explained.<br /> SQL&gt; select * from table(dbms_xplan.display(null, null, 'basic'));<br /> Plan hash value: 143117509<br /> --------------------------------------------------------------<br /> | Id | Operation | Name |<br /> --------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | |<br /> | 1 | SORT ORDER BY | |<br /> | 2 | PARTITION RANGE ALL | |<br /> | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |<br /> | 4 | BITMAP CONVERSION TO ROWIDS | |<br /> | 5 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX |<br /> --------------------------------------------------------------<br />

출력 결과로부터 새로운 계획에서 이전 계획에서 사용되었던 (그리고 목록 2에서 보여진) SALES_TIME_BIX 인덱스 대신 SALES_PROMO_BIX 인덱스를 사용한 것을 알 수 있습니다. 이제 새 계획이 고정됩니다.

그렇다면 고정된 계획을 어디서 사용할 수 있을까요? SQL문의 계획이 최적이 아니며 (예를 들어, 계획이 SALES_TIME_BIX 인덱스를 사용할 경우 더 효율적일 수 있는데 SALES_PROMO_BIX 인덱스를 사용하는 계획) 코드를 변경하여 힌트를 배치할 수 없는 상황을 가정해 보십시오. 이 경우 다음 단계를 수행할 수 있습니다.

 

1. 다른 세션에서 optimizer_mode 매개 변수를 목록 1에서 보여진 것처럼 원하는 계획을 생성하는 값으로 변경합니다.

2. SQL문을 실행하고, 목록 1처럼 기준선을 캡처한 다음, 세션의 연결을 끊습니다.

3. 목록 4에서 보여진 것처럼 SALES_TIME_BIX 인덱스를 사용하여 계획을 고정으로 표시합니다. SQL 핸들과 계획 이름을 여러분의 케이스에 해당하는 이름으로 바꾸는 것을 기억하십시오.

 

계획이 고정으로 표시되고 나면 SQL문은 옵티마이저가 생성한 계획이 아닌 해당 계획만을 사용하게 됩니다. 하나 이상의 고정된 계획이 존재하는 경우, 옵티마이저는 최적의 계획을 선택합니다.

 

동일한 방법을 사용하여 데이터베이스 업그레이드 중 SQL문의 안정적인 실행 경로를 보장할 수도 있습니다. 먼저 시스템 매개 변수 optimizer_capture_sql_plan_baselines를 true로 설정하여 데이터베이스에서 모든 SQL문에 대한 기준선을 수집한 다음, 중요한 SQL문마다 하나의 계획만 고정으로 표시되도록 합니다. 그런 다음 점진적으로 계획을 "고정 해제"하고 진화 함수를 사용하여 다른 최적의 계획이 있는지 확인합니다. 옵티마이저가 나중에 생성한 계획이 더 나쁠 경우에는 항상 이전의 고정된 계획으로 되돌릴 수 있습니다.

 

결론

 

저장된 개요 또한 계획을 안정적으로 만들지만 융통성이 없게 합니다. 옵티마이저는 SQL문에 대한 개요가 있는지 확인하고 새로운 계획 생성을 중지합니다. 한편 기준선은 옵티마이저가 새로운 계획을 생성하는 것을 절대 중단하지 않습니다.

 

SQL 계획 관리 기능을 사용하면 SQL문에 대한 검증된 계획이나 잘 알려진 계획을 기준선의 형식으로 저장할 수 있습니다. 그러면 갑작스런 성능 저하를 진단할 때 매우 유용합니다. 기준선(및 해당 계획)이 저장소에 저장되기 때문에 기준선 및 계획을 비교하여 가장 효율적으로 사용할 수 있도록 결정할 수 있습니다.

 

필자소개

 

Arup Nanda ( arup@proligence.com ) 는 성능 조정에서 보안 및 재해 복구에 이르기까지 모든 데이터베이스 관리 측면을 관리하는 Oracle DBA로서 14년 이상 근무해 왔습니다. 그리고 2003년 Oracle Magazine에 의해 올해의 DBA로 선정된 바 있습니다

 

출처 : 한국 오라클

제공 : DB포탈사이트 DBguide.net

반응형
저작자표시 비영리

'ORACLE > 11G' 카테고리의 다른 글

11g： 스케줄러 유지 관리 작업 또는 자동작업 (문서 ID 1534329.1)  (0) 2014.06.14
oracle 11g에 추가된 파티션  (0) 2013.08.20
ADR  (0) 2013.05.19
11g 데이타베이스 에러수집방법  (0) 2012.05.11
Oracle Advanced Compression  (0) 2009.04.02
Posted by [PineTree]

티스토리툴바