by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

System Statistics




  • 정의
    1. System H/W의 I/O, CPU의 특성을 분석하여 optimizer가 CPU costing을 계산할 때 사용하는 정보
    2. optimizer가 SQL문에 대한 실행 계획을 수립할 때 이 정보를 기반으로 계산

  • 특징
    구 분 상세설명
    수행 주기 ① 초기 : 1회
    ② 메모리, CPU, IO등의 시스템 자원의 변경 발생 할 경우
    작업 방법 【 방법1】
    ① 통계치 종류에 따른 통계정보 생성
    ② 시스템 통계 생성 및 보관을 위한 통계 테이블 생성
      exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'mystats');
    ③ 시스템 통계 생성 작업(이 때 시스템에 load를 일정량 줌)
      exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL', interval => 10, stattab => 'mystats',' statid => 'DAY', statown => 'SYSTEM');
    ④ 생성된 정보를 시스템에 import하여 통계 정보 적용
      dbms_stats.import_system_stats(stattab => 'mystats', statid => 'DAY');
    【 방법1】
    ① gathering하면서 바로 시스템에 적용하는 방법
      exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL', interval => 10);
    작업 확인 select * from sys.aux_stats$;
    고려 사항 ① RAC에서 node간 시스템 사양이 다를 경우 : 시스템 전체에서 대표성의 성격을 가지는 node(대표 node)에서 수행
      ⓐ 시스템 통계를 node별로 나누어서 수행하면 관리가 되지 않음
      ⓑ 각 node별로 통계치 생성 후 비교해서 대표 node 결정
    ② 주간 OLTP Job과 야간 BATCH Job의 작업이 구분되는 경우 : time window에 따른 통계치를 import하여 사용


  • 필요성 : CBO는 통계 정보를 바탕으로 비용을 계산하여 실행계획을 세우는데, 비용에는 시스템 통계도 포함되어 계산되므로 중요하다. 시스템 통계 기능은 시스템의 실제 수행 상태에 대한 "사실" 정보를 제공뿐만 아니라, 쿼리 실행 시간 예측치가 실측치(실제 측정치)에 더욱 가깝게 해준다.
  • 테스트
    SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
    
PNAME 시스템 통계 정보 수집 전
PVAL1
시스템 통계 정보 수집 후
PVAL1
CPUSPEED   916
CPUSPEEDNW 587.115789 587.115789
IOSEEKTIM 10 10
IOTFRSPEED 4096 4096
MAXTHR   2163712
MBRC   16
MREADTIM   1.936
SLAVETHR    
SREADTIM   9.42

‡ sreadtim: 단일 블록 읽기 요청에 소요되는 평균 시간(1000분의 1초)
‡ mreadtim: 다중 블록 읽기 요청에 소요되는 평균 시간(1000분의 1초)
‡ MBRC: 다중 블록 읽기의 평균 블록 수
‡ aux_stats$(SYS 소유) : 시스템 통계를 수집한 후, select 하면 비용계산에 필요한 수치 확인 가능
       (기존 실행 계획을 무효화하기 위해서는 shared_pool flush 시켜야 함)

SQL> execute dbms_stats.gather_system_stats('Start');

-- 평상시의 workload 상태

SQL> execute dbms_stats.gather_system_stats('Stop');
SQL> select  t1.object_name, t2.object_name 
     from big_table t1, big_table t2 
     where t1.object_id= t2.object_id and t1.owner='WMSYS';

Before> 예측치와 실측치 차이가 있음

-----------------------------------------------------------------------------------------------------------
|Id|       Operation              |       Name         | Rows  | Bytes | TempSpc | Cost (%CPU) |   Time   |
-----------------------------------------------------------------------------------------------------------
|0 |SELECT STATEMENT              |                    | 2057K | 133M  |         |  8094 (2)   | 00:01:38 | 
|*1| HASH JOIN                    |                    | 2057K | 133M  | 4000K   |  8094 (2)   | 00:01:38 | 
|2 |  TABLE ACCESS BY INDEX ROWID |BIG_TABLE           | 83473 |       | 3016K   |  2527 (1)   | 00:00:31 | 
|*3|   INDEX RANGE SCAN           |BIG_TABLE_OWNER_IDX | 83473 |       |         |  199 (2)    | 00:00:03 | 
|4 |    TABLE ACCESS FULL         |BIG_TABLE           |       | 1001K |   29M   |  3289 (2)   | 00:00:40 | 
-----------------------------------------------------------------------------------------------------------

After> 예측치와 실측치 차이가 없음 (00:00:36.89)

1. 예측치
-----------------------------------------------------------------------------------------------------------
|Id|       Operation              |       Name         | Rows  | Bytes | TempSpc | Cost (%CPU) |   Time   |
-----------------------------------------------------------------------------------------------------------
|0 |SELECT STATEMENT              |                    | 1738K | 112M  |         |  10881(1)   | 00:01:10 | 
|*1| HASH JOIN                    |                    | 1738K | 112M  | 3408K   |  10881(1)   | 00:01:10 | 
|2 |  TABLE ACCESS BY INDEX ROWID |BIG_TABLE           | 71220 | 2573K |         |   2185(1)   | 00:00:15 | 
|*3|   INDEX RANGE SCAN           |BIG_TABLE_OWNER_IDX | 71220 |       |         |   165 (2)   | 00:00:02 | 
|4 |  TABLE ACCESS FULL           |BIG_TABLE           | 997K  | 29M   |         |  5238 (1)   | 00:00:34 | 
-----------------------------------------------------------------------------------------------------------

2. 실측치 
-----------------------------------------------------------------------------------------------------------
|Id|       Operation              |       Name         | Rows  | Bytes | TempSpc | Cost (%CPU) |   Time   |
-----------------------------------------------------------------------------------------------------------
|0 |SELECT STATEMENT              |                    | 1738K | 112M  |         |  10881(1)   | 00:01:10 | 
|*1| HASH JOIN                    |                    | 1738K | 112M  | 3408K   |  10881(1)   | 00:01:10 | 
|2 |  TABLE ACCESS BY INDEX ROWID |BIG_TABLE           | 71220 | 2573K |         |   2185(1)   | 00:00:15 | 
|*3|   INDEX RANGE SCAN           |BIG_TABLE_OWNER_IDX | 71220 |       |         |   165 (2)   | 00:00:02 | 
|4 |  TABLE ACCESS FULL           |BIG_TABLE           | 997K  | 29M   |         |  5238 (1)   | 00:00:34 | 
-----------------------------------------------------------------------------------------------------------

문서에 대하여

  • 최초작성자 : 박혜은
  • 최초작성일 : 2009년 11월 19일
  • 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.