태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'dbms_stats'에 해당되는 글 5건

  1. 2010.10.12 RAW 값 변환하기
  2. 2009.02.12 Partition Statistics를 손쉽게 Copy하기 (7)
  3. 2008.01.24 Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback
  4. 2008.01.02 (Oracle 10g) dbms_stats.gather_schema_stats 수행에 시간이 너무 많이 걸려요... (1)
  5. 2007.12.05 [Oracle is Mad] dbms_stats.auto_invalidate의 작동 방식 - Oracle 10g에서의 통계 정보 수집과 Cursor Invalidation 문제 (4)

RAW 값 변환하기

오라클/프로그래밍 2010.10.12 16:26
딕셔너리를 검색하다 보면 RAW 형태로 제공되는 값들이 간혹 있습니다. 가령 USER_TAB_COLS 뷰에서 컬럼의 최소값과 최대값을 조회해보면 다음과 같이 의미를 알 수 없는 RAW 값으로 보여집니다.
SQL> col column_name format a10
SQL> col data_type format a10
SQL> select
  2  	column_name,
  3  	data_type,
  4  	low_value,
  5  	high_value
  6  from
  7  	user_tab_cols
  8  where
  9  	table_name = 'T1'
 10  ;

COLUMN_NAM DATA_TYPE  LOW_VALUE            HIGH_VALUE
---------- ---------- -------------------- --------------------
C1         NUMBER     C102                 C302
C2         VARCHAR2   6D616E79             6F6E65
이 값들은 UTL_RAW 패키지를 사용하면 변환할 수 있습니다.
SQL> select utl_raw.cast_to_number('C102') from dual;

UTL_RAW.CAST_TO_NUMBER('C102')
------------------------------
                             1

SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;

UTL_RAW.CAST_TO_VARCHAR2('6D616E79')
--------------------------------------------------------------------------------
many
다음과 같이 사용할 수 있겠네요.
SQL> select
  2  	column_name,
  3  	data_type,
  4  	decode(data_type,
  5  		'NUMBER', utl_raw.cast_to_number(low_value)||'',
  6  		'VARCHAR2', utl_raw.cast_to_varchar2(low_value), low_value||'') as low_value,
  7  	decode(data_type,
  8  		'NUMBER', utl_raw.cast_to_number(high_value)||'',
  9  		'VARCHAR2', utl_raw.cast_to_varchar2(high_value), high_value||'') as high_value
 10  from
 11  	user_tab_cols
 12  where
 13  	table_name = 'T1'
 14  ;

COLUMN_NAM DATA_TYPE  LOW_VALUE            HIGH_VALUE
---------- ---------- -------------------- --------------------
C1         NUMBER     1                    10000
C2         VARCHAR2   many                 one
DBMS_STATS 패키지도 동일한 역할을 하는 함수들을 제공합니다. 하지만 인자를 지정하는 방법이 조금 다릅니다. 사용의 편의성을 위해서 다음과 같이 사용자 정의 함수를 만듭니다.
SQL> -- dbms_stats (conversion)
SQL> create or replace function convert_me(p_value in raw, p_type in varchar2)
  2  return varchar2
  3  is
  4     v_number		number;
  5     v_varchar2   varchar2(4000);
  6  begin
  7     if (p_type = 'NUMBER') then
  8        dbms_stats.convert_raw_value(p_value, v_number);
  9        return v_number|| '';
 10     elsif (p_type = 'VARCHAR2') then
 11        dbms_stats.convert_raw_value(p_value,v_varchar2);
 12        return v_varchar2;
 13     /* other data types */
 14     else
 15        return p_value || '';
 16     end if;
 17  end;
 18  /

Function created.

SQL> 
SQL> select convert_me('C102', 'NUMBER') from dual;

CONVERT_ME('C102','NUMBER')
--------------------------------------------------------------------------------
1

SQL> select convert_me('6D616E79', 'VARCHAR2') from dual;

CONVERT_ME('6D616E79','VARCHAR2')
--------------------------------------------------------------------------------
many
그러면 다음과 같이 SQL 문장내에서 사용할 수 있습니다.
SQL> select
  2  	column_name,
  3  	data_type,
  4  	convert_me(low_value, data_type) as low_value,
  5  	convert_me(high_value, data_type) as high_value
  6  from
  7  	user_tab_cols
  8  where
  9  	table_name = 'T1'
 10  ;

COLUMN_NAM DATA_TYPE  LOW_VALUE            HIGH_VALUE
---------- ---------- -------------------- --------------------
C1         NUMBER     1                    10000
C2         VARCHAR2   many                 one
위와 같은 일을 하지 않아도 되게끔 오라클이 알아서 변환해주면 좋을텐데요. 하여간 간혹 위와 같은 변환 작업이 필요한 경우가 있습니다. 그때 잘 활용하면 좋겠습니다.
저작자 표시
신고

'오라클 > 프로그래밍' 카테고리의 다른 글

RAW 값 변환하기  (0) 2010.10.12
Trackback 0 : Comment 0

Write a comment


Partition Statistics를 손쉽게 Copy하기

오라클 2009.02.12 17:12
통계 정보 관리와 관련해서 가장 많이 접하는 질문 중 하나는 다음과 같다.
  • 우리 회사에서는 주기적으로 Partition(대부분 Range Partition)을 추가하고 Data를 로드한다.
  • Data양이 워낙 많기 때문에 통계 정보를 수집하는 것이 대단히 부담스럽다.
  • 통계 정보를 수집하지 않고, 새로 만들어진 Partition에 대해서만 통계 정보를 수동으로 만들어 주는 방법은 없을까?


이런 상황을 많이 접한 분들이라면 아마 나름대로의 해결책을 가지고 있을 것이다.

Oracle 10.2.0.4부터는 dbms_stats.copy_table_stats를 이용하면 이 고민이 매우 손쉽게 해결된다. 이 방법을 사용할 수 없으면 dbms_stats.set_xxx_stats 프로시저들을 이용해서 수동으로 통계 정보를 지정하는 방법도 사용 가는하다. 하지만 역시 새로운 버전에서 제공되는 편리한 기능을 사용할 수 있다면 최선이다.

아래 영문 블로그에서 이들 방법의 자세한 사용법을 확인할 수 있다.

How to copy partition stats? - We got easier way!



신고
Trackback 0 : Comments 7
  1. DONi 2009.11.23 15:14 신고 Modify/Delete Reply

    안녕하세요...

    통계 복사후에 날짜 속성 컬럼값의 통계값에 LOW_VALUE, HIGH_VALUE에 대해 추가작업이

    필요하지 않을까요?

    DBMS_STATS.SET_COLUMN_STATS procedure 관련된 추가보정 작업이 필요할 것 같습니다.

    • 욱짜 2009.11.23 15:31 신고 Modify/Delete

      dbms_stats.copy_table_stats는 low_value, high_value까지 자동으로 설정합니다(그렇지 않다면 너무 쓰기 불편할 겁니다)

      단, low_value와 high_value값을 설정하지 않은 버그도 보고되고 있으므로 반드시 확인 후 사용할 필요는 있습니다.

  2. DONi 2009.11.23 16:58 신고 Modify/Delete Reply

    음..제가 작업한 후에 확인 해보았습니다만,(version 10.2.0.4)

    EXEC DBMS_STATS.COPY_TABLE_STATS(OWNNAME => 'owner', TABNAME => 'partitioned_table', SRCPARTNAME => 'P200902', DSTPARTNAME => 'P200904');

    SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
    UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) AS LOW_VALUE,
    UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) AS HIGH_VALUE
    --,DENSITY, NUM_NULLS, SAMPLE_SIZE, LAST_ANALYZED
    FROM DBA_PART_COL_STATISTICS
    WHERE OWNER = 'owner' AND TABLE_NAME = 'partitioned_table' AND PARTITION_NAME = 'P200903';

    SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
    UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) AS LOW_VALUE,
    UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) AS HIGH_VALUE
    --,DENSITY, NUM_NULLS, SAMPLE_SIZE, LAST_ANALYZED
    FROM DBA_PART_COL_STATISTICS
    WHERE OWNER = 'owner' AND TABLE_NAME = 'partitioned_table' AND PARTITION_NAME = 'P200904';

    파티션 P200904의 파티션 기준 컬럼을 포함한 다른 날짜 컬럼 역시 P200903의 값과 동일합니다.

    자동으로 HIGH, LOW_VALUE를 보정하는 것은 11g 에도 없는 것 같습니다만,,,

  3. DONi 2009.11.23 17:00 신고 Modify/Delete Reply

    script에 오류가 있습니다...
    다음의 스크립트가 맞습니다. 죄송...(3월에서 4월로 복사...)


    EXEC DBMS_STATS.COPY_TABLE_STATS(OWNNAME => 'owner', TABNAME => 'partitioned_table', SRCPARTNAME => 'P200903', DSTPARTNAME => 'P200904');

    SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
    UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) AS LOW_VALUE,
    UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) AS HIGH_VALUE
    --,DENSITY, NUM_NULLS, SAMPLE_SIZE, LAST_ANALYZED
    FROM DBA_PART_COL_STATISTICS
    WHERE OWNER = 'owner' AND TABLE_NAME = 'partitioned_table' AND PARTITION_NAME = 'P200903';

    SELECT TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT,
    UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) AS LOW_VALUE,
    UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) AS HIGH_VALUE
    --,DENSITY, NUM_NULLS, SAMPLE_SIZE, LAST_ANALYZED
    FROM DBA_PART_COL_STATISTICS
    WHERE OWNER = 'owner' AND TABLE_NAME = 'partitioned_table' AND PARTITION_NAME = 'P200904';

    • 욱짜 2009.11.23 21:11 신고 Modify/Delete

      Oracle Manual에는 다음과 같이 기술되어 있습니다.
      http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#BABDDBGH

      <quote>
      It sets the high bound partitioning value as the maximum value of the first partitioning column and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for a range partitioned table. It finds the maximum and minimum from the list of values for the list partitioned table. It also sets the normalized maximum and minimum values. If the destination partition is the first partition then minimum values are equal to maximum values. If the statistics for source are not available then nothing is copied.
      </quote>

      10.2.0.4에 High Value, Low Value가 변경되지 않은 버그가 있습니다. 메타링크 문서 8318020를 참조하세요.

  4. DONi 2009.11.25 14:02 신고 Modify/Delete Reply

    Oracle 11g에서는 파티션 기준 컬럼은 자동적으로 보정이 되는군요(x86 11.1.0.7.0)

    그외의 컬럼은 물론 소스파티션의 값으로 복사됩니다.

    10g에서는 어쩔 수 없이 수작업으로 보정하는 수 밖엔 없네요...

    다음의 파티션 기준 컬럼에 대한 예제 스크립트입니다.

    DECLARE
    V_SREC DBMS_STATS.STATREC;
    V_ArrVals DBMS_STATS.CHARARRAY;
    BEGIN

    V_SREC.epc := 2;
    V_SREC.eavs := null;
    V_SREC.bkvals := DBMS_STATS.NUMARRAY(2.60592219238428E35, 2.60592219238428E35);
    V_ArrVals := DBMS_STATS.CHARARRAY('20090401', '20090430');

    DBMS_STATS.PREPARE_COLUMN_VALUES(V_SREC, V_ArrVals);

    DBMS_STATS.SET_COLUMN_STATS
    (
    OWNNAME=>'owner명',
    TABNAME=>'테이블명',
    COLNAME=>'SALEDATE',
    PARTNAME=>'P200904',
    DISTCNT=>30,
    DENSITY=>0.0357142857142857,
    NULLCNT=>0,
    AVGCLEN=>9,
    SREC=>V_SREC
    );
    END;
    /

    • 욱짜 2009.11.25 14:54 신고 Modify/Delete

      이런 기본적인 기능에서조차 버그가 있다는 것이 안타까울 뿐이죠. 해당 버그(버그번호는 8719831이네요)에 대한 패치를 설치하는 것이 가장 좋을 거 같습니다. 자세한 내용은 아래 블로그 포스트를 참조하세요.

      http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html

Write a comment


Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback

오라클 2008.01.24 20:10
Wolfgang Bretiling이라는 스위스를 기반으로 활동하는 Consultant가 있다. 이 사람의 전문 분야는 Oracle + PeopleSoft의 튜닝이다.

PeopleSoft과 같은 ERP 솔루션들은 Oracle과 무관하게, 아니 Oracle을 무시해서 설계된 Application으로 유명하다. 모든 DBMS에서 일관되게 동작 가능한 Application에 의한 필요악이라고나 할까... 이런 3rd Party Application의 튜닝에서 가장 큰 애로 사항이 쿼리를 직접 수정하는 것이 불가능하다는 것이다. 때문에 간혹 Oracle 관점에서의 튜닝이 불가능한 것으로 간주하기도 한다.

이 Wolfgang이라는 사람은 이런 상황에 착안해서 자신만의 쿼리 튜닝 기법을 개발했다. 하지만 쿼리 수정이 전혀 불가능한 상태에서 어떻게 (비교적) 자유롭게 쿼리를 튜닝할 수 있단 말인가?
놀랍게도 이 사람은 많은 시스템에서의 경험과 Oracle Optimizer에 대한 심도 깊은 지식을 기반으로 이것을 가능하게 하는 단순하면서도 심오한 방법을 체계화했다.

그 이름이 바로 TCF - Tuning by Cardinality Feedback이다. 풀어쓰면 "실행 예상 계획과 실제 실행 계획의 차이(Cardinality Feeback)에 의한 쿼리 튜닝 기법"이다. TCF는 다음과 같은 가정에 근거한다.

  • Oracle의 CBO는 Cardinality만 정확하면 나름대로 최적의 실행 계획을 생성한다.
  • Oracle의 통계 해석에서 오는 몇 가지 오류로 인해 Cardinality 계산이 비현실적인 경우가 있다.
  • 따라서, Oracle이 Cardinality를 잘 계산할 수 있도록 힌트를 주면 많은 경우 정상적인 실행 계획을 만들 것이다.


이런 가정 하에서 DBMS_STATS.SET_XXX 류의 메소드를 이용해서 Oracle이 최적의 Cardinality를 계산할 수 있도록 통계 값을 보정시켜주는 것이 이 TCF의 핵심이다. Oracle이 필요로 하는 Cardinality를 추론하게 해주는 것이 Explain Plan(실행 예상 계획)과 Execution Plan(실제 실행 계획)을 비교하고 해석하는 능력이다.

마침 Oracle 10g부터는 gather_plan_statistics 힌트나 statistics_level = all 과 함께 dbms_xplan.display_cursor 함수를 사용하면 TCF 적용에 필요한 데이터(실행 예상 로우 건수와 실제 실행 로우 건수)를 손쉽게 구할 수 있으니 이 Wolfgang의 TCF의 뛰어난 점을 Oracle이 수용한 것처럼 생각될 정도이다.

아래 URL에서 TCF 사용에 필요한 모든 이론적 도구를 얻을 수 있다.
http://www.centrexcc.com/papers.html

쿼리를 직접 수정할 수 없는 3rd party Application 튜닝에 최적의 도구를 하나 확보하게 된 셈이다.

PS) 이 TCF는 실제로 많은 엔지니어들이 자신도 인식하지 못하고 사용하고 있는 방법이다. 이런 것을 체계화하고 이론적 무장을 갖추는 능력이 부족한 우리 현실이 아쉬울 뿐이다.
신고
Trackbacks 7 : Comment 0

Write a comment


(Oracle 10g) dbms_stats.gather_schema_stats 수행에 시간이 너무 많이 걸려요...

오라클 2008.01.02 22:41
dbms_stats.gather_schema_stats 프로시저를 이용해서 통계 정보를 수집하는 경우 수행 시간이 너무 많이 걸린다는 불만이 종종 있다. 이런 현상은 대부분 다음과 같은 경우에 발생한다.

- 특정 테이블들의 크기가 매우 크다.
- 일부 큰 테이블들은 Partitioning이 되어 있다.

즉 큰 테이블들이 있고 더구나 Partitioning까지 되어 있으니(Global/Partiton Statistics모두 수집해야 하므로) 그만큼 통계 정보 수집에 시간이 걸리기 마련이다. 하지만 Oracle 10g에서는 estimate_percent 파라미터와 method_opt 파라미터의 값이 Oracle에 의해 적절하게 판단되어 통계 정보 생성 시간을 최적화한다. 그렇다면 왜 필요 이상 많은 시간이 걸리는 경우가 발생하는가?

이것은 이 두 파라미터에 다음과 같은 결함이 있기 때문이다.

- estimate_percent 파라미터의 값이 AUTO이다. 즉, Oracle이 테이블의 크기에 따라 적절한 샘플 크기를 선택한다는 의미이다. 대부분의 경우 Oracle은 테이블의 크기에 따라 적절한 값을 찾지만, 간혹 아주 큰 테이블에 대해 100%의 값을 사용하는 경우가 있다.
- method_opt 파라미터의 기본값이 FOR ALL COLUMNS SIZE AUTO이다. 즉 모든 컬럼에 대해 통계정보를 수집하면 히스토그램의 생성 여부는 Oracle이 판단한다. 대부분의 경우 Oracle은 적절한 히스토그램 버킷 크기를 할당하지만, 간혹 불필요하게(가령 Unique Key에 대해) 히스토그램을 생성하는 경우가 있다.

위와 같은 상황이 발생하면 예상보다 지나치게 많은 시간이 걸리게 되는 셈이다.

이런 상황을 해소할 수 있는 방법이 있을까? 가령 다음과 같이 하면...?

dbms_stats.gather_schema_stats(estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

물론 위의 방법으로 수행 시간 문제는 해결하겠지만, 좋은 방법은 될 수 없다. 만일 크기가 작은 테이블들이 있다면 샘플 크기가 작으면 왜곡된 통계 정보가 수집될 수 있다. 만일 특정 테이블에 대해서는 모든 컬럼(ALL COLUMNS)에 대해 통계 정보를 수집해야 한다면? 또는 특정 테이블의 특정 컬럼에 대해서는 히스토그램 크기를 주고 싶다면?

그렇다고 테이블 레벨로 일일이 dbms_stats.gather_table_stats를 호출하는 것은 너무나 지겨운 일이다.

그래서 보통 다음과 같은 트릭을 종종 사용한다.(편의상 정확한 문법은 생략)

-- 특정 테이블 통계 정보 변경을 막음
dbms_stats.lock_table_stats(user, 'TABLE1');

-- 스키마 레벨로 통계 정보 수집
dbms_stats.gather_schema_stats(user);

-- 통계 정보 활성화 후 이 테이블만 다시 통계 정보 수집
dbms_stats.unlock_table_stats(user, 'TABLE1');
dbms_stats.gather_table_stats(user,'TABLE', estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

위와 같이 하면 Schema 레벨로 대부분의 테이블에 대해 통계 정보를 수집한 후 특정 문제가 되는 테이블에 대해서만 별도로 통계 정보를 수집할 수 있다.

지저분한 트릭같지만 매우 유용한 방법이다.

Oracle 11g에서는 "Preference"라는 새로운 개념을 통해 위와 같은 불편을 일시에 해소하고 있다. Oracle 11g에서라면 다음과 같이 훨씬 직관적이고 깔끔한 방법을 쓸 수 있다.

-- 아래와 같이 한번만 설정
dbms_stats.set_table_prefs(user,'TABLE1', 'ESTIMATE_PERCENT', '10');
dbms_stats.set_table_prefs(user,'TABLE1','METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- Schema 레벨에서 통계 정보 수집
dbms_stats.gather_schema_stats(user);

즉, 특정 테이블별로 Preference(선호도)를 저장할 수 있어서 10g에서와 같은 부자연스러운 트릭은 불필요한 것이다. 11g에서 가장 마음에 드는 New Feature중 하나이다.
신고
Trackback 0 : Comment 1
  1. 김민기 2011.10.22 17:50 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


[Oracle is Mad] dbms_stats.auto_invalidate의 작동 방식 - Oracle 10g에서의 통계 정보 수집과 Cursor Invalidation 문제

오라클 2007.12.05 18:02
dbms_stats를 이용해 통계 정보를 수집한 후 갑자기 hard parse가 몰리면서 library cache pin 경합으로 인해 시스템 성능 저하 현상이 발생하는 경우가 많다.

다음과 같은 상황에서 이런 현상이 발생한다.
- 통계 정보 수집으로 통계 정보가 변경된다.
- 통계 정보가 변경되면 관련된 SQL Cursor들이 invalidate된다.
- SQL Cursor가 invalidate되면 다음 번 Access때 hard parse가 발생한다.
- hard parse시에는 LCO에 대해 library cache pin을 exclusive모드로 획득한다.
- hard parse가 진행 중인 LCO를 실행하려는 다른 session들은 library cache pin 이벤트를 대기한다.

즉, 통계 정보 갱신에 의한 Cursor invalidation으로 인해 hard parse storm이 발생하고 이로 인해 성능 문제가 발생한다.

9i부터는 통계 정보 수집시 no_invalidate 옵션으로 Cursor invalidation을 제어할수 있다.
- no_invalidate == false: 통계 정보 갱신 후 관련된 SQL Cursor들을 즉시 invalidate한다. 9i에서의 기본값이다.
- no_invalidate == true: 통계 정보 갱신 후 관련된 SQL Cursor들을 invalidate하지 않는다. SQL Cursor들이 Shared Pool에서 age-out 된 후 reaload될 때 갱신된 통계 정보가 사용된다.

위의 내용을 보면 알겠지만, no_invalidate 옵션은 참으로 애매모호한 옵션이다. false로 지정하면 hard parse storm이 발생해서 문제가 생길 것이고, true로 지정하면 갱신된 통계 정보가 반영되지 않으므로 곤란하다.

이런 고민을 해결하기 위해 Oracle 10g에서 dbms_stats.auto_invalidate(실제 값은 Null) 옵션이 추가되었다.
- no_invalidate == dbms_stats.auto_invalidate: 통계 정보 갱신 후, 통계 정보 반영을 일정 시간 지연해서 적용한다. 이 시간 값은 _optimizer_invalidation_period 파라미터로 결정된다. 기본값이 18000(초)로 5시간이다. 즉 통계 정보 갱신 후 5시간이 지난 시점에(5시간에 걸쳐) Cursor가 Access되면 그 때 통계 정보를 반영한다. 10g에서는 auto_invalidate가 기본값이다.

따라서 10g에서는 통계 정보를 수집해도 즉각 반영되지 않고, 자연스럽게 hard parse storm도 피할 수 있다. 대신 일정 시간 이후 반영이 되므로 일정 목적을 달성할 수 있다. 이런에  현상 때문에 Oracle 10g를 처음 운영하는 환경에서 오해들이 있었다. "왜 통계 정보를 수집했는데 반영이 안돼? 버그 아냐"... 다행히 버그가 아니고 원래 그렇게 설계가 된 것이다.

만일, auto_invalidate를 사용하면서 특정 SQL Cursor는 즉시 invalidation하고 싶다면 어떻게 해야할까?
가장 간단한 방법은 alter system flush shared_pool을 이용해서 모든 Cursor를 invalidation시키는 것이다.
더 좋은 방법은 alter session set "_optimizer_invalidation_period" = 1; 과 같은 값을 적용해서 가능한 빨리 invalidation을 시키는 것이다. 물론 이것은 Auto Invalidation의 원래 취지에는 어긋난다.

통계 정보 수집시 SQL Cursor의 invalidation 문제는 지금까지 문서화가 잘 안되어 있어서 많은 오해들이 있었다. 이 글이 이런 오해를 푸는데 도움이 되었으면 한다.

PS)
auto_invalidate 옵션으로 통계 정보 갱신 후 일정 시간이 지나 SQL Cursor를 새로 생성할 시점이 오면 Oracle은 어떻게 통계 정보를 반영할까?
기존의 SQL Cursor를 invalidate시키는 것이 아니라 새로운 Child Cursor를 생성한다. 실제 로 테스트를 해보면 새로운 Child Cursor가 생기고 v$sql_shared_cursor.roll_invalid_mismatch가 발생한 것으로 관찰된다.

참조)
http://wiki.ex-em.com/index.php/OPTIMIZER_INVALIDATION_PERIOD
신고
Trackback 0 : Comments 4
  1. 재혁재우 2008.11.21 17:40 신고 Modify/Delete Reply

    설명을 읽었는데 그래도 이해를 못해서 질문드립니다. --;

    전제 : 만약 하나의 테이블(T1)이 있다고 가정하고 현재의 실행계획를 A라 하고
    통계정보를 갱신한 후의 실행계획을 B라고 할때... (단, 무조건 실행계획은 바뀐다고 가정한다)

    1. T1 테이블에 대해 no_invalidate == true 으로 통계정보 수집한 후,
    T1을 사용하는 SQL Cursor를 요청하면 그 Cursor가 Shared Pool에서 age-out 되지 않으므로
    만약 지속적인 Cursor 사용이 요청되면 영원히 갱신된 통계 정보는 사용되지 못해 실행계획 A만 사용하는 건가요?

    2. T1에 대해 no_invalidate == dbms_stats.auto_invalidate 으로 통계정보 수집하면
    _optimizer_invalidation_period 기간안에 해당 Cursor가 무조건 invalidate 되나요?

    3. 2번 질문에 이어서
    _optimizer_invalidation_period 기간안에 invalidate되지 못한 cursor 사용요청이 오면 실행계획 A가 사용되나요?
    _optimizer_invalidation_period 기간안에 invalidate된 cursor 사용요청이 오면 실행계획 B가 사용되나요?
    _optimizer_invalidation_period 기간이후에는 invalidate 되어 있기 때문에 실행계획 B가 사용되나요?


    이상입니다.

  2. 욱짜 2008.11.21 23:03 신고 Modify/Delete Reply

    Auto Invalidation의 의미를 이렇게 이해하시면 좋겠습니다.

    "Invalidation을 하기는 하되 한꺼번에 하지 않고 적당히 나누어서 Query가 실행될 때 하겠다"

    이 관점으로 생각하면 모든 것이 명확해집니다.

    1번: 언젠가는(나누어서 하니까) Invalidation이 될 것이기 때문에 실행 계획은 그 시점에 바뀌게 됩니다.

    2번: 주어진 기간에 Query가 수행될 때(정확하게 언제인지는 알 수 없지만) Invalidation이 이루어집니다. 만일 주어진 시간안에 수행이 되지 않으면? 나중에 수행될 때 Invalidation이 될 것이기 때문에 문제가 안됩니다.

    3번: Yes, Yes, Yes 입니다.

    주의할 것은 Invallidation을 시켜주는 Process가 따로 있는 것이 아니라 Query를 수행하는 Process가 (내부적인) 알고리즘에 따라 적당하게 Invalidation을 시킨다는 것입니다. 이말은 Query가 수행이 안되면 Invalidation이 발생하지 않는다는 것입니다. 수행되지 않은 Query는 Invalidation이 이루어질 필요도 없으니까 전혀 문제가 안됩니다.

  3. kpt0507 2009.09.18 16:54 신고 Modify/Delete Reply

    안녕하세요 욱짜님 한 가지 궁금한 것이 있어서 질문 드립니다.

    현재 시스템에 sql_id='9kdgr0v1kjadc' 에 두 개의 child_cursor 가 있습니다.

    하나는 child_number=0 하나는 child_number=1 입니다. ( 당연하겠지요 ^^;)


    select child_number,roll_invalid_mismatch from v$sql_shared_cursor where sql_id = '9kdgr0v1kjadc'; 의

    결과는
    child_number | roll_invalid_mismatch
    0 Y
    1 N

    입니다.

    당연히 모든 session 이 child_number=1 인 child_cursor를 share 할 것이라 생각했는데

    select * from table(dbmx_xplan.display_cursor('9kdgr0v1kjadc')); 를 해서 현재

    사용되는 실행계획을 보면

    child_number=0 인 child_cursor의 실행계획을 보여줍니다.

    질문)
    child_number 가 max인 child_cursor가 실제 share 되는 cursor가 맞는지 예외는 없는지요?

    • 욱짜 2009.09.18 17:27 신고 Modify/Delete

      반갑습니다.

      번거로우시겠지만, 위의 질문을 다른 분들도 공유할 수 있도록 Ask Exem에 올려주시겠습니까?

      http://ask.ex-em.com

      Email 주소만 정확하게 적어주시면 게시판을 통해 질문/답변/의견 교환이 가능합니다. Email 주소는 외부에 노출되지 않으므로 걱정하지 않으셔도 됩니다.

      감사합니다.

Write a comment

티스토리 툴바