꿈꾸는 개발자, DBA 커뮤니티 구루비

정의
특징
필요성
테스트

정의

System H/W의 I/O, CPU의 특성을 분석하여 optimizer가 CPU costing을 계산할 때 사용하는 정보
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 | 
-----------------------------------------------------------------------------------------------------------