http://jhroom.co.kr/22639
![[레벨:7]](http://jhroom.co.kr/modules/point/icons/default/7.gif "포인트:5532point (83%), 레벨:7/30"){kind=icon}
JH어댑티브 커서와 SQL 실행 계획 관리
바인드 변수를 이용하여 각 상황에 맞는 적절한 실행 계획을 선택하고, 새로운 실행 계획의 효과를 미리 확인해 볼 수 있습니다.
 | Oracle Database 11g 다운로드 |
아마도 여러분들 중 대부분이 바인드 변수를 이용한 성능 개선 방법에 대해 잘 알고 계실 것입니다. 아직 바인드 변수에 대해 잘 모르고 계신 분들을 위해, 가능한 한 간단하게 그 개념을 설명해 보도록 하겠습니다. (톰 카이트의 asktom.oracle.com사이트도 함께 추천 드립니다. 바인드 변수를 이용한 SQL 구문 성능 개선이 중요한 이유가 무엇인지, 그리고 바인드 변수를 여러 가지 언어에서 활용하는 방법이 무엇인지 확인하실 수 있습니다.)
CUSTOMER 테이블에 포함된 STATE_CODE라는 컬럼에 고객이 거주하는 주 정보가 약자로 저장되어 있다고(예: CT, NY) 가정해 봅시다. 3회 이상 상품을 주문했고 코네티컷('CT') 주에 거주하는 고객을 확인하려면 아래와 같은 쿼리를 실행해야 할 것입니다:
select count(1) from customers where state_code = 'CT' and times_purchased > 3;이 쿼리를 실행하면, 오라클은 파싱(parsing)이라 불리는 작업을 수행하고 방금 실행된 SQL 구문을 위한 실행 계획(execution plan)을 생성합니다. 파싱이 완료된 쿼리는 바로 실행이 가능합니다. 파싱은 소프트웨어의 코드 컴파일과 유사한 개념입니다. C++로 작성된 코드를 운영 체제에서 바로 실행할 수 없으며, 컴파일 과정을 통해 실행 파일로 변환하는 것과 같은 이치입니다. 파싱 역시 SQL 구문을 실행 가능한 형태로 바꾸어 줍니다.
이제 다른 사용자가 아래와 같은 구문을 실행했다고 가정해 봅시다:
select count(1) from customers where state_code = 'NY' and times_purchased > 3;위의 구문은 먼저 실행한 쿼리와 매우 유사하며 검색되는 state_code가 'CT'가 아닌 'NY'라는 차이만 있을 뿐입니다. 파싱 결과로 생성되는 코드는 동일하며 'CT', 'NY'와 같은 문자열 값은 런타임에 입력됩니다. 하지만 이런 식으로 쿼리를 작성하는 경우, 오라클은 두 가지 쿼리를 서로 다른 것으로 간주하고 2번째 쿼리에 대해 별도의 파싱을 수행합니다.
이번에는 쿼리가 아래와 같은 형태로 작성된 경우를 고려해 봅시다:
select count(1) from customers where state_code = <StateCode> and times_purchased > 3;첫 번째 쿼리에서는
위에서
where state_code = :state_code코드에 바인드 변수가 적용되지 않았고 그 대신 state_code='CT'와 같은 구문이 여러 군데에서 사용되고 있다면, 초기화 매개변수를 아래와 같이 설정하여 모든 문자열을 강제로 바인드 변수로 변환하도록 설정할 수 있습니다.
cursor_sharing = force위와 같이 매개변수를 설정하면 state_code = 'CT'가 state_code = ":SYS_0001"로 변환됩니다. 여기서 SYS_0001은 시스템에 의해 자동 생성된 변수 이름을 의미합니다.
바인드 변수 활용과 관련한 문제
바인드 변수가 이처럼 효과적이라면 왜 항상 바인드 변수를 사용하지 않는 것일까요? "cursor_sharing"이라는 만병통치약을 이용하면 잘못 작성된 모든 코드를 공유 가능한 구문으로 변환할 수 있을 것 같지 않습니까? (그 이유, 특히 바인드-피킹(bind-peeking)과 같은 개념을 잘 알고 있으시다면 "어댑티브 커서" 섹션으로 바로 넘어가셔도 좋습니다.)
이번에는 STATE_CODE 컬럼에 인덱스가 적용되어 있는 경우를 고려해 봅시다. 이 컬럼에 입력되어 있는 값의 분포가 아래와 같습니다:
select state_code, count(1) from customers group by state_code; ST COUNT(1) -- ---------- NY 994901 CT 5099위에서 볼 수 있듯, 데이터는 매우 고르지 않은 분포를 갖습니다. 약 5%의 로우만이 'CT' 값을 가지며 나머지는 모두 'NY' 값을 가집니다. (뉴욕 주와 코네티컷 주의 인구를 고려하면 그리 놀랄 일도 아닙니다.) 이제 앞에서 예로 든 쿼리를 실행했을 때 어떤 실행 계획이 생성되는지 확인해 봅시다:
SQL> set autot traceonly explain
SQL> select * from customers where state_code = 'NY' and times_purchased > 3
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895K| 26M| 1532 (9)| 00:00:19 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 895K| 26M| 1532 (9)| 00:00:19 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TIMES_PURCHASED">3 AND "STATE_CODE"='NY')
이 쿼리는 풀 테이블 스캔(full table scan)을 사용하고 있습니다. 이는 전체 로우 중 95%가 반환되고 있음을 고려할 때 올바른 선택이라 할 수 있습니다. 인덱스 스캔을 사용하면 매우 많은 비용이 들 것입니다. 이제 'CT'를 조건으로 같은 쿼리를 다시 실행해 봅시다:
SQL> c/NY/CT
1* select * from customers where state_code = 'CT' and times_purchased > 3
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4876992
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4589 | 138K| 56 (2)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4589 | 138K| 56 (2)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_CUST_STATE | 5099 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TIMES_PURCHASED">3)
2 - access("STATE_CODE"='CT')
이번에는 인덱스가 사용되었습니다. 이것도 올바른 결과입니다. 'CT'는 전체 로우의 5%만을 차지하며 따라서 인덱스 스캔이 그 효과를 발휘할 수 있습니다.이제 바인드 변수를 사용했을 때 달라지는 것이 무엇인지 알아 봅시다. Oracle Database 10g 환경에서 쿼리의 실행 계획을 확인한 결과가 아래와 같습니다.
SQL> var state_code varchar2(2)
SQL> exec :state_code := 'CT'
PL/SQL procedure successfully completed.
SQL> select max(times_purchased) from customers where state_code = :state_code
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1511 (8)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 500K| 2929K| 1511 (8)| 00:00:19 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATE_CODE"=:STATE_CODE)
옵티마이저는 CUSTOMER 테이블에 대해 Full Table Scan을 선택했습니다. 전체 레코드의 5%에 불과한 'CT'를 검색하는 쿼리이므로 인덱스를 사용하는 것이 옳지 않을까요? 옵티마이저가 인덱스 스캔 대신 풀 테이블 스캔을 선택한 이유는 무엇일까요?바인드 피킹(bind peeking)이라는 현상에서 그 이유를 찾을 수 있습니다. 앞에서 'NY'로 바인드 변수 값을 설정하고 쿼리를 실행했을 때, 옵티마이저는 하드 파싱(hard parsing)을 수행합니다. 이 과정에서 옵티마이저는 바인드 변수에 어떤 값이 할당되었는지 미리 엿보는(peeking) 작업을 하고, 여기서 'NY'라는 값을 확인합니다. 'NY'가 전체 로우의 95%를 차지하므로, 옵티마이저는 풀 테이블 스캔을 선택합니다. 또 이와 별도로, 옵티마이저는 쿼리의 실행 계획을 동결(freeze)시킵니다. 이제 'CT'를 검색 조건으로 하는 동일한 쿼리를 실행하면, 옵티마이저는 실행 계획을 재계산하지 않고 앞에서 사용한 실행 계획을 그대로 사용합니다. 쿼리에서 'CT'라는 값 대신 바인드 변수를 사용했다면 옵티마이저가 올바른 실행 계획을 사용했을 것입니다.
지금까지 설명한 것처럼, 바인드 변수는 대부분의 상황에서 그 효과를 발휘하지만 값의 선택성(selectivity)이 고르지 못한 경우에는 오히려 역효과를 냅니다. 앞에서처럼 'CT', 'NY' 값이 각각 5%, 95%를 차지하는 경우가 좋은 예입니다. 데이터가 고르게 분포되어 모든 값의 선택성이 거의 같은 경우라면 항상 같은 실행 계획이 사용될 것입니다. 따라서 SQL 구문을 작성하면서 바인드 변수 대신 문자열을 사용해야 하는 경우가 언제인지 분별할 수 있는 판단력이 필요합니다.
어댑티브 커서
상황에 따라 바인드 변수 적용 여부를 판단할 만한 스킬이 부족하거나, 구문을 재작성할 시간이 부족한 경우라면 어떻게 해야 할까요? 오라클이 뭔가 유용한 대안을 제공해 주지 않을까요?
네, 그렇습니다. Oracle Database 11g에서는 커서에 전에 없던 스마트한 기능이 새로 추가되었습니다. 쿼리가 실행될 때마다 캐시에 저장된 실행 계획을 무작정 실행하는 대신, 이제는 바인드 변수의 값이 변경되었을 때 실행 계획을 재작성해야 하는지의 여부를 판단하는 과정이 추가된 것입니다. 커서에 바인드 변수가 포함되었다면, 데이터베이스는 변수에 어떤 유형의 값이 전달되는지, 그리고 실행 계획이 재작성 되어야 하는지를 한동안 모니터합니다. 실행 계획의 재작성이 필요하다면, 이 커서는 "Bind-Sensitive"로 마킹됩니다.
아래 쿼리는 "Bind-Sensitive" 커서가 적용될 수 있는 최적의 후보입니다. 따라서 바인드 변수의 값을 기준으로 적절한 실행 계획이 적용될 것입니다. 별도로 설정해 주어야 하는 것은 아무 것도 없으며, 모든 것은 자동으로 실행됩니다.
딕셔너리 뷰 V$SQL을 조회해 보면 두 개의 컬럼, IS_BIND_SENSITIVE와 IS_BIND_AWARE가 추가된 것을 확인할 수 있습니다. 두 컬럼의 용도가 무엇인지 알아 봅시다
select is_bind_sensitive, is_bind_aware, sql_id, child_number from v$sql where sql_text = 'select count(1) from customers where state_code = :state_code and times_purchased > 3' I I SQL_ID CHILD_NUMBER - - ------------- ------------ Y Y 7cv5271zx2ttg 0 Y N 7cv5271zx2ttg 1위에서 컬럼의 의미를 파악해 보겠습니다. 오라클은 커서의 실행 과정을 일정 기간 감시하면서 값이 어떻게 달라지는지를 확인합니다. 다른 값에 의해 실행 계획이 달라질 수 있는 경우, 커서는 "Bind-Sensitive"로 마킹되고 IS_BIND_SENSITIVE 컬럼의 값이 "Y"로 변경됩니다. 몇 차례 실행이 반복된 뒤, 커서와 값에 대한 더 많은 정보를 얻은 데이터베이스는 바인드 변수의 값에 따라 실행 계획이 변경되어야 하는지의 여부를 결정합니다. 실행 계획이 변경되어야 한다면 커서는 "Bind-Aware"로 마킹되고 IS_BIND_AWARE 컬럼의 값이 "Y"로 변경됩니다. 다시 요약하자면 다음과 같습니다. Bind-Sensitive 커서는 실행 계획이 변경될 수 있는 후보로 선정된 커서를 의미하며, Bind-Aware 커서는 실행 계획이 변경되도록 설정된 커서를 의미합니다.
새로 추가된 V$SQL_CS_HISTOGRAM 뷰는 SQL 구문이 몇 차례 실행되었는지에 대한 정보를 표시합니다. 아래에서 확인할 수 있듯, 정보는 각각의 자식 커서(child cursor)에 대해 3개의 버킷(bucket)으로 분류되어 표시됩니다.
select * from v$sql_cs_histogram where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 0 0 45C8218C 2144429871 7cv5271zx2ttg 5 1 2 45C8218C 2144429871 7cv5271zx2ttg 5 2 0 45C8218C 2144429871 7cv5271zx2ttg 4 0 8 ... and so on ... 45C8218C 2144429871 7cv5271zx2ttg 0 2 0어댑티브 커서 공유 기능을 이용하여 바인드 변수의 값을 기준으로 실행 계획을 선택하려면, 데이터베이스가 이에 관련된 정보를 어딘가에 저장하고 있어야 할 것입니다. 역시 새로 추가된 V$SQL_CS_SELECTIVITY 뷰는 바인드 변수에 전달되는 값들의 선택성(selectivity)에 대한 정보를 보여 줍니다.
select * from v$sql_cs_selectivity where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBE PREDICATE R LOW HIGH -------- ---------- ------------- ----------- ----------- - -------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 =STATE_CODE 0 0.895410 1.094391 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 0 0.004589 0.005609 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 1 0.002295 0.002804 45C8218C 2144429871 7cv5271zx2ttg 3 =STATE_CODE 0 0.002295 0.002804 45C8218C 2144429871 7cv5271zx2ttg 0 =STATE_CODE 0 0.004589 0.005609새로운 뷰를 통해 매우 많은 정보를 얻을 수 있습니다. PREDICATE 컬럼은 사용자에 의해 적용된 다양한 조건절(WHERE 절)을 보여 줍니다. LOW, HIGH 값은 전달된 최소, 최대값을 의미합니다.
새로 추가된 세 번째 뷰, V$SQL_CS_STATISTICS는 Bind-Aware 또는 Bind-Sensitive로 마킹된 커서의 활동 내역을 보여줍니다.
select child_number,
bind_set_hash_value,
peeked,
executions,
rows_processed,
buffer_gets,
cpu_time
from v$sql_cs_statistics
where sql_id = '7cv5271zx2ttg';
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
------------ ------------------- - ---------- -------------- ----------- ----------
1 22981142 Y 1 9592 3219 0
0 22981142 Y 1 9592 3281 0
이 뷰를 통해 실행 과정에서 데이터베이스에 기록된 성능 통계를 확인할 수 있습니다. EXECUTIONS 컬럼은 서로 다른 바인드 변수의 값에 대해 쿼리가 얼마나 자주 수행되었는지에 대한 정보를 보여줍니다. ("P"로 표시되는) PEEKED 컬럼은 옵티마이저가 적절한 실행 계획을 얻기 위해 바인드 변수에 대한 엿보기(peek)를 수행했음을 의미합니다.이 세 가지 뷰를 통해 어댑티브 커서 기능의 동작 원리를 이해하는데 필요한 추가적인 정보를 확인할 수 있습니다. 앞에서 설명한 것처럼 어댑티브 커서는 자동적으로 활성화되고 사용됩니다.
SQL 실행 계획 관리
다음과 같은 상황을 얼마나 자주 경험해 보셨습니까? 어떤 쿼리에 최적의 실행 계획이 저장되었지만 갑자기 무슨 이유에서인가 실행 계획이 삭제되어 버리는 경우가 있습니다. 이러한 현상은 누군가가 테이블에 대한 분석을 실행하였거나, 옵티마이저에 영향을 미치는 매개변수(예: star_transformation)가 변경되었거나, 또는 그 밖의 여러 가지 이유로 인해 발생할 수 있습니다. 당황한 관리자는 데이터베이스 내부의 변경 작업을 일체 금지해 버리고, 데이터베이스 통계 수집, 매개변수 변경 등이 이루어질 수 없도록 조치합니다.
말로는 쉽지만 실제로 실행하는 것은 무척 어렵습니다. 데이터 패턴이 변경되는 경우에는 어떤 문제가 발생할까요? "어댑티브 커서" 섹션에서 설명한 예를 다시 살펴 봅시다. CUSTOMERS 테이블의 STATE_CODE가 대부분 "NY"로 업데이트되었다고 합시다. 따라서 쿼리가 아래와 같은 조건으로 실행되는 경우,
where state_code = 'CT'인덱스 스캔이 아닌 풀 테이블 스캔이 발생합니다. 조건절이 아래와 같다면,
where state_code = 'CT'적은 수의 로우만을 검색하면 되므로 인덱스가 사용될 것입니다. 그러던 중 갑자기 패턴이 변경되어 코네티컷(state_code='CT')의 고객 수가 갑자기 증가해서 CT의 비율이 70%로 뛴다면 어떻게 될까요? 이러한 경우라면 CT를 조건으로 하는 쿼리는 풀 테이블 스캔을 사용해야 합니다. 하지만 관리자가 옵티마이저의 통계 수집을 중단시킨 상태이므로, 옵티마이저는 패턴에 발생한 변화를 인지하지 못하고 계속적으로 비효율적인 인덱스 스캔을 고집할 것입니다. 어떻게 해야 할까요?
통계 수집, 데이터베이스 매개변수 변경과 같은 하부 요인이 발생하였을 때 오라클 데이터베이스가 실행 계획을 자동으로 재평가하고, 다른 실행 계획이 최적인 것으로 확인된 경우에만 실행 계획을 변경하도록 하면 어떨까요? 정말 멋지지 않을까요? Oracle Database 11g가 바로 이러한 기능을 제공합니다. 지금부터 그 원리를 확인해 봅시다.
SQL Plan Baselining
Oracle Database 11g에서는 하부 요인의 변경으로 인해 실행 계획의 업데이트가 발생하는 경우, 업데이트된 정보는 즉각적으로 반영되지 않습니다. 다시 말해, 오라클 데이터베이스는 업데이트된 실행 계획을 평가하고 기존의 실행 계획에 비해 나은 성능을 제공하는 경우에만 이 실행 계획을 적용합니다. 또 각각의 쿼리를 위한 실행 계획의 히스토리를 확인하고 서로 다른 실행 계획을 비교할 수 있는 툴과 인터페이스가 제공됩니다제일 먼저, 오라클 데이터베이스는 두 번 이상 반복 실행되는 "repeatable" 구문의 존재를 확인합니다. 오라클 데이터베이스는 확인된 "repeatable" 구문의 실행 계획을 캡처하고 SQL Plan Baseline으로 저장합니다. SQL Plan Baseline은 SQL Management Base(SMB)라는 이름의 논리적인 컨스트럭트로 데이터베이스에 저장됩니다. 어떤 이유로든 새로운 실행 계획이 계산되면, 이 실행 계획은 SMB에도 함께 저장됩니다. 결국 SMB는 쿼리에 대한 모든 실행 계획과 쿼리가 생성된 이유 등에 대한 정보를 포함하게 됩니다.
실행 계획은 SMB에 자동으로 저장되지는 않습니다. (그렇다면 SMB에 모둔 쿼리의 실행 계획이 저장되면서, 그 용량이 엄청나게 커지게 될 것입니다.) 사용자가 얼마나 많은 쿼리가 SMB에 저장되는지 설정해 주어야 합니다. 이를 위한 방법은 두 가지가 있습니다. 첫 번째가 모든 "repeatable" 구문을 SMB에 자동으로 저장하는 것이고, 두 번째는 베이스라인 처리되어야 하는 쿼리를 수작업으로 로드하는 방법입니다.
먼저 간단한 예를 살펴 보겠습니다. 데이터베이스 매개변수 optimizer_capture_sql_plan_baselines의 값을 FALSE(디폴트)에서 TRUE로 바꾸어 주면 SQL Plan Management 기능이 모든 "repeatable" 쿼리에 대한 SQL Plan Baseline을 캡처하게 됩니다. 이 매개변수는 다이내믹 매개변수입니다.
SQL> alter system optimizer_capture_sql_plan_baselines = true;실행된 모든 "repeatable" 구문의 실행 계획은 SMB에 SQL Plan Baseline으로 저장됩니다. SQL Plan Baseline은 DBA_SQL_PLAN_BASELINES라는 이름의 뷰에 저장됩니다. 이 뷰는 Enterprise Manager에서도 확인할 수 있습니다. 베이스라인 처리된 실행 계획을 확인하려면 EM에서 "Server" 탭을 클릭합니다(아래 화면 참조):
새로운 실행 계획이 생성되는 경우, 기존의 계획은 SMB의 히스토리 정보로 보존됩니다. 하지만 계속 이렇게 하다가는 SMB가 히스토리 정보로 가득 차게 될 것입니다 Plan Retention (Weeks)의 텍스트 박스를 통해 실행 계획의 히스토리 정보를 최대 몇 주 동안 보존할 것인지 설정할 수 있습니다. 예제 화면은 53 주로 설정되어 있습니다. 이는 SQL Plan Baseline이 53주 동안 사용되지 않은 경우 자동으로 삭제됨을 의미합니다
스크린의 중간 부분에 위치한 검색 상자를 이용하면 SQL 구문을 검색할 수 있습니다. 이곳에 문자열을 입력하고 Go를 누르면 위 그림과 같이 관련된 SQL 구문과 그 실행 계획을 확인할 수 있습니다. 베이스라인으로 저장된 각 실행 계획에는 많은 양의 상태 정보가 포함되어 있습니다. 어떤 정보가 있는지 알아 봅시다:
- Enabled - 베이스라인 처리된 실행 계획이 활성화된 것으로 간주합니다.
- Accepted - 베이스라인 처리된 실행 계획이 특정 쿼리에 대해 적용 가능한 것으로 간주됩니다.
- Fixed - 실행 계획이 FIXED로 마킹된 경우, 옵티마이저는 최적의 실행 계획을 결정할 때 "fixed"로 설정된 계획들만을 고려합니다. 한 예로 특정 쿼리에 대해 5개의 실행 계획이 베이스라인 처리되었고 그 중 3개가 "fixed"로 설정되었다면, 옵티마이저는 이 3가지 중에서만 최적의 실행 계획을 고르게 됩니다.
- Auto-Purge - 실행 계획이 자동으로 삭제됩니다.
SQL> desc DBA_SQL_PLAN_BASELINES Name Null? Type ----------------------------------------- -------- --------------- SIGNATURE NOT NULL NUMBER SQL_HANDLE NOT NULL VARCHAR2(30) SQL_TEXT NOT NULL CLOB PLAN_NAME NOT NULL VARCHAR2(30) CREATOR VARCHAR2(30) ORIGIN VARCHAR2(14) PARSING_SCHEMA_NAME VARCHAR2(30) DESCRIPTION VARCHAR2(500) VERSION VARCHAR2(64) CREATED NOT NULL TIMESTAMP(6) LAST_MODIFIED TIMESTAMP(6) LAST_EXECUTED TIMESTAMP(6) LAST_VERIFIED TIMESTAMP(6) ENABLED VARCHAR2(3) ACCEPTED VARCHAR2(3) FIXED VARCHAR2(3) AUTOPURGE VARCHAR2(3) OPTIMIZER_COST NUMBER MODULE VARCHAR2(48) ACTION VARCHAR2(32) EXECUTIONS NUMBER ELAPSED_TIME NUMBER CPU_TIME NUMBER BUFFER_GETS NUMBER DISK_READS NUMBER DIRECT_WRITES NUMBER ROWS_PROCESSED NUMBER FETCHES NUMBER END_OF_FETCH_COUNT NUMBER실행 계획의 이름을 클릭하면 실행 계획의 상세 정보가 표시됩니다. 출력 예가 아래와 같습니다:
Return 을 눌러 다시 실행 계획의 목록으로 돌아갑니다. 이제 "Accepted"로 처리되지 않은 실행 계획을 선택하고 Evolve를 눌러 최적의 실행 계획을 재검토할 필요가 있는지 확인합니다. 아래와 같은 화면이 표시됩니다.
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e
SYS_SQL_PLAN_b5429522e53beeec
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_b5429522e53beeec
-----------------------------------
It is already an accepted plan.
Plan: SYS_SQL_PLAN_b5429522ee05ab0e
-----------------------------------
Plan was verified: Time used 3.9 seconds.
Failed performance criterion: Compound improvement ratio <= 1.4.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 3396 440 7.72
CPU Time(ms): 1990 408 4.88
Buffer Gets: 7048 5140 1.37
Disk Reads: 4732 53 89.28
Direct Writes: 0 0
Fetches: 4732 25 189.28
Executions: 1 1
서로 다른 실행 계획들을 비교한, 매우 유용한 리포트가 생성되었습니다. 특정 실행 계획이 더 나은 성능을 제공하는 것으로 확인된 경우, 옵티마이저는 새로운 실행 계획을 사용할 것입니다. 새로운 실행 계획이 충분한 성능 개선을 보여주지 못한다면 "Accept" 처리되지 않고 사용되지도 않습니다. SQL Performance Management를 이용하면 서로 다른 실행 계획의 차이를 확인하고 실질적으로 더 나은 성능을 제공하는 실행 계획을 사용할 수 있습니다.DBMS_SPM 패키지를 실행하여 실행 계획의 "Accepted" 상태를 변경하는 것도 가능합니다:
declare
ctr binary_integer;
begin
ctr := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_e0b19f65b5429522',
plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e',
attribute_name => 'ACCEPTED',
attribute_value => 'NO'
);
end;
SQL Plan Baseline을 비활성화하여 베이스라인이 옵티마이저에 의해 사용되지 않도록 할 수도 있습니다. 나중에 이 실행 계획이 사용되도록 다시 활성화하는 것도 가능합니다. 베이스라인을 비활성화하는 방법은 아래와 같습니다.declare
ctr binary_integer;
begin
ctr := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_e0b19f65b5429522',
plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e',
attribute_name => 'ENABLED',
attribute_value => 'NO'
);
end;
특정 SQL 구문의 실행 계획이 베이스라인에 의해 "fixed"로 설정된 경우 실행 계획에서 이를 분명하게 확인할 수 있습니다. 실행 계획의 마지막 부분에 실행 계획이 베이스라인에 의해 "fixed" 처리되었음을 표시하는 라인이 표시됩니다.Stored Outlines와의 비교
Stored Outlines를 알고 계신 분들이라면, 이 기능이 SQL Plan Management와 어떤 차이를 갖는지 궁금하게 생각하실 것입니다. 이 두 가지는 특정 실행 계획을 쿼리에 적용할 수 있다는 공통점을 갖습니다. 하지만 SQL Plan Management에서는 베이스라인에 대한 평가 작업을 통해 더 나은 실행 계획을 적용하고 활성화할 수 있다는 점에서 차이가 있습니다. Stored Outlines은 고정된 설정을 가지며, 사용자가 다른 프로파일을 이용해서 기존의 설정을 비활성화하거나 대체하지 않는 한 변경되지 않습니다. 또 SQL Plan Management에서는 히스토리 정보를 통해 실행 계획이 일정 기간 동안 어떻게 변화하였는지 확인할 수 있다는 점에서도 차이가 있습니다.
특정 쿼리에 스토어드 아웃라인이 설정되어 있는데 베이스라인에서 더 나은 실행 계획을 찾았다면 어떻게 될까요? 두 기능 사이에 충돌이 발생하지는 않을까요? 그렇지는 않습니다. 쿼리가 스토어드 아웃라인을 통해 파싱 되는 경우, 아웃라인에 의해 강제 설정된 실행 계획은 SQL Plan Baseline으로 캡처 됩니다. 옵티마이저가 해당 구문에 대해 다른 실행 계획을 확인한 경우, 새로운 실행 계획은 SMB에 저장되지만 "Accept" 처리되지는 않습니다. 이 경우 사용자가 직접 evolve 프로세스를 실행하여 새로운 실행 계획이 기존의 SQL Plan Baseline에 비해 더 나은 성능을 제공한다는 사실을 증명해야 합니다.
Stored Profiles와의 비교
프로파일은 실행 계획이 아니며, 실행 계획의 일부로 저장되는 메타데이터일 뿐입니다. 프로파일에서는 실행 계획이 조건절을 기준으로 변경될 수 있습니다. 이에 반해, SQL Plan Baselines에서는 조건절의 값과 무관하게 동일한 실행 계획이 적용됩니다.
활용 사례
그렇다면 어떤 상황에서 이 기능을 사용할 수 있을까요? 업그레이드 또는 매개변수 변경 작업을 수행하는 경우를 가장 대표적인 예로 들 수 있을 것입니다. 여러 개의 구문에 대한 베이스라인을 설정하기 위한 방편으로 SQL Tuning Set을 이용하여 STS로부터 SPM에 구문들을 로드할 수 있습니다. 이를 위해 Oracle Database 10g에서 STS를 생성하여 내보내기를 실행하고, Oracle Database 11g에서 가져오기를 수행한 다음 DBMS_SPM.UNPACK_STGTAB_BASELINE 패키지를 실행하여 실행 계획을 SQL Plan Baselines로 저장합니다. 옵티마이저는 이후 더 나은 실행 계획이 발견되는 경우 이를 SMB에 추가하고 비교할 수 있도록 지원합니다.
'wif LiNoUz > Oracle,Sql' 카테고리의 다른 글
| 롤백 세그먼트 에러 관련 (0) | 2014.05.31 |
|---|---|
| 튜닝 방법론 (0) | 2014.05.30 |
| sql ftp handling and perfmon command (0) | 2014.04.29 |
| 오라클에서 is_number, is_date 함수 사용하기 (0) | 2014.04.21 |
| 오라클 캐릭터셋 짜증 (1) | 2013.05.15 |