이펙티브 오라클 (2009년)
CBO 최적화하기 0 0 42,856

by 구루비스터디 System Statistics [2018.05.26]


  1. 정의
  2. 특징
  3. 필요성
  4. 테스트


정의

  • 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
CPUSPEED916
CPUSPEEDNW587.115789587.115789
IOSEEKTIM1010
IOTFRSPEED40964096
MAXTHR2163712
MBRC16
MREADTIM1.936
SLAVETHR
SREADTIM9.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년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3482

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입