태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Partition과 Global 통계 자동 수집의 기준 - Stale 여부

오라클 2008.07.25 13:32

Oracle 10g에서 자동 통계 수집을 잘 알고 있을 것이다.

  • 매일 밤(10시)에 돈다
  • GATHER AUTO 옵션을 준 것과 같이 돈다.
  • 이 경우, 10% 이상 변경된 Table에 대해 모든 옵션을 Auto로 설정한 다음 돈다.

이와 관련해 어제(2008년 7월 24일) [Oracle is Mad(R)] 세미나 때 이런 질문이 있었다.
  • Local Index가 통계 정보가 변경이 안되는 문제가 있다.
  • Table의 특정 Partition이 변경되었는데도 불구하고 Index에 대한 통계 정보가 수집되지 않는다.
  • 왜 그런가?

만일 Data의 변경이 많고 통계 정보가 수집될 조건이 만족되었는데도 통계 정보가 수집되지 않았다면 그건 Oracle의 버그일 것이다.

그래서 간단한 테스트를 통해 Partition된 Table과 Index가 어떤 기준으로 통계 정보가 재수집되는지 확인해 보기로 했다. 아래에 간단한 테스트 케이스가 있다.
-- 필요한 Object를 생성한다.
drop table t1 purge;

create table t1(c1 int, c2 int)
partition by list(c1) (
partition p1 values (1),
partition p2 values (2),
partition p3 values (3),
partition p4 values (4)
)
;

insert into t1
select mod(level,4)+1, level
from dual
connect by level <= 80000
;

create index t1_n1 on t1(c1)
local
;

exec dbms_stats.gather_table_stats(user, 't1');
-- 다음과 같이 통계 정보 재수집을 확인한다.
select table_name, last_analyzed
from user_tables
where table_name = 'T1';

select table_name, partition_name, last_analyzed
from user_tab_partitions
where table_name = 'T1';

select index_name, last_analyzed
from user_indexes
where index_name = 'T1_N1';

select index_name, partition_name, last_analyzed
from user_ind_partitions
where index_name = 'T1_N1';
다음과 같이 통계 수집을 하면 Oracle의 자동 통계 수집과 동일한 효과가 있다.
exec dbms_stats.gather_schema_stats(user, options=>'GATHER AUTO');

1. Case1: Table 전체의 10% 이상이 변경된 경우
-- when modify > 10% of table data
update t1
set c2 = c2 + 1
where c1 = 4
;

-- 다음과 같이 해당 Partition과 Table/Index가 모두 재수집된다.
TABLE_NAME           LAST_ANALYZED                                              
-------------------- -------------------                                        
T1                  2008/07/25 11:58:04      <-- Collected

TABLE_NAME           PARTITION_NAME       LAST_ANALYZED                         
-------------------- -------------------- -------------------                   
T1                   P1                   2008/07/25 11:57:53                   
T1                   P2                   2008/07/25 11:57:54                   
T1                   P3                   2008/07/25 11:57:54                   
T1                   P4                   2008/07/25 11:58:07     <-- Collected             

INDEX_NAME           LAST_ANALYZED                                              
-------------------- -------------------                                        
T1_N1                2008/07/25 11:58:06                                        

INDEX_NAME           PARTITION_NAME       LAST_ANALYZED                         
-------------------- -------------------- -------------------                   
T1_N1                P1                   2008/07/25 11:57:59                   
T1_N1                P2                   2008/07/25 11:57:59                   
T1_N1                P3                   2008/07/25 11:57:59                   
T1_N1                P4                   2008/07/25 11:58:08    
               

2. Case2: 특정 Partition은 10% 이상 변경되지만 Table 전체는 10% 미만으로 변경된 경우
-- when 10% of partition < modified data < 10% of table
update t1
set c2 = c2 + 1
where c1 = 4
      and rownum <= 5000
;

-- Partition은 수집되지만 Table/Index 전체 레벨은 수집되지 않는다
TABLE_NAME           LAST_ANALYZED                                              
-------------------- -------------------                                        
T1                   2008/07/25 11:58:04    <-- Not Collected   

TABLE_NAME           PARTITION_NAME       LAST_ANALYZED                         
-------------------- -------------------- -------------------                   
T1                   P1                   2008/07/25 11:57:53                   
T1                   P2                   2008/07/25 11:57:54                   
T1                   P3                   2008/07/25 11:57:54                   
T1                   P4                   2008/07/25 11:58:12     <-- Collected              


INDEX_NAME           LAST_ANALYZED                                              
-------------------- -------------------                                        
T1_N1                2008/07/25 11:58:06       <-- Not Collected                          

INDEX_NAME           PARTITION_NAME       LAST_ANALYZED                         
-------------------- -------------------- -------------------                   
T1_N1                P1                   2008/07/25 11:57:59                   
T1_N1                P2                   2008/07/25 11:57:59                   
T1_N1                P3                   2008/07/25 11:57:59                   
T1_N1                P4                   2008/07/25 11:58:12        <-- Collected           


3. Case2: 특정 Partition이 10% 미만으로 변경된 경우
-- when modified data < 10% of partition 
update t1
set c2 = c2 + 1
where c1 = 4
      and rownum <= 1900
;

-- Partition이든 Table/Index 전체 레벨이든 수집되지 않는다
TABLE_NAME           LAST_ANALYZED                                              
-------------------- -------------------                                        
T1                   2008/07/25 11:58:04       <-- Not Collected                                

TABLE_NAME           PARTITION_NAME       LAST_ANALYZED                         
-------------------- -------------------- -------------------                   
T1                   P1                   2008/07/25 11:57:53                   
T1                   P2                   2008/07/25 11:57:54                   
T1                   P3                   2008/07/25 11:57:54                   
T1                   P4                   2008/07/25 11:58:12   <-- Not Collected                 

INDEX_NAME           LAST_ANALYZED                                              
-------------------- -------------------                                        
T1_N1                2008/07/25 11:58:06      <-- Not Collected                              


INDEX_NAME           PARTITION_NAME       LAST_ANALYZED                         
-------------------- -------------------- -------------------                   
T1_N1                P1                   2008/07/25 11:57:59                   
T1_N1                P2                   2008/07/25 11:57:59                   
T1_N1                P3                   2008/07/25 11:57:59                   
T1_N1                P4                   2008/07/25 11:58:12     <-- Not Collected               
위의 테스트 결과를 보면 거의 우리가 예상한 것과 동일한 방식으로 동작하는 것을 알 수 있다. 주의할 것은 특정 Partition이 크게 바뀌어도 그 범위가 테이블 전체 레벨에서 10% 이하라면 Table/Index 레벨의 통계 정보는 수집되지 않는다는 것이다.

이런 동작 방식이 마음에 들지 않으면 문제가 된다고 생각되는 Segment에 한해서만 수동으로 개별 수집할 필요가 있겠다.

PS) 위의 테스트는 10.2.0.1에서 수행되었다. 다른 버전에서는 다른 결과가 나올 수도 있다.

Trackbacks 2 : Comments 5
  1. DB사랑ㅋ 2008.07.25 20:25 신고 Modify/Delete Reply

    그럼 Global Level(dba_tab_col_statistics)에서 수집되는데,
    Local level( dba_part_col_statistics)에서는 수집되지 않는 현상이 발생될때가 있을까요?

  2. 욱짜 2008.07.27 06:17 신고 Modify/Delete Reply

    Granularity를 'Global'로 준 경우에는 가능합니다.
    혹시 특정 Partition이 10% 이상 변했는데도 Partition 레벨의 통계 정보가 수집되지 않는 현상이 발생했나요?

  3. DB사랑ㅋ 2008.07.29 12:25 신고 Modify/Delete Reply

    착각해서 질문이 잘못되었습니다.

    통계정보는 gather_stats_job에 의해서 자동 갱신됩니다.
    global : dba_tables, dba_tab_col_statistics
    partition table: dba_tab_partitions, dba_part_col_statistics
    partition index :dba_ind_partitions
    위의 테이블에 대해서 통계정보가 갱신됨(last_analyzed)을 확인하였으나, dba_indexes에서는 총 8개중 특정 index를 제외한 7개가 통계정보가 갱신되지 않음을 확인하였습니다.

    위의 상황으로 유추해볼수 있는 내용이 있을까요?

  4. 욱짜 2008.07.29 15:11 신고 Modify/Delete Reply

    당연히 되어야 할 작업이 이루어지지 않으면 오라클의 버그이죠. 말씀하신 현상은 실제로 Oracle의 버그인거 같습니다.
    <Metalink doc# 6679567>을 보세요. 비슷한 내용이 있습니다. 본문의 테스트 스크립트에서 dbms_stats.gather_schema_stats 호출을 dbms_stats.gather_database_stats_job_proc 호출(단, 이 호출은 sys로 해야 함)로 대신하면 Index 레벨의 통계 정보는 수집되지 않습니다. 두 호출이 Parameter만 적절히 세팅하면 똑같을거라는 제 가정이 완전히 잘못되었던 거죠.

    이 현상은 Partition Pruning이 정확하게 이루어지는 상황에서는 크게 문제가 안되겠지만, Globa 레벨의 통계 정보가 큰 의미를 지닌다고 하면 문제가 될 소지가 있을 거 같습니다.

  5. DB사랑ㅋ 2008.07.29 16:34 신고 Modify/Delete Reply

    버그라는 확신이 없었는데,
    좋은 자료 감사합니다. 패치를 적용하던가, 또다른 방안을 마련해야겠습니다.^^

    이렇게 큰 이슈를 이제야 알다니,, 헐..

Write a comment

티스토리 툴바