1. 7. 시스템통계
   1. 7.1 dbms_stats 패키지
   2. 7.2 이용 가능한 시스템 통계에는 어떠한 것들이 있는가?
   3. 7.3 시스템 통계 수집하기
   4. 7.3.1 noworkload 통계
   5. 7.3.2 workload 통계
   6. 7.3.3 noworkload 통계 vs workload 통계
   7. 7.4 시스템 통계 복원하기
   8. 7.5 백업 테이블을 사용한 작업
   9. 7.6 관리 오퍼레이션의 로깅
   10. 7.7 쿼리 옵티마이저에 미치는 영향

7. 시스템통계

7.1 dbms_stats 패키지

• 시스템 통계를 관리하는 데 필요한 프로시저 집합을 의미한다.
• 패키지 내의 대부분의 프로시저는 데이터 딕셔너리를 변경한다.
• 아래 2가지 이유로 사용자가 생성한 외부테이블에 통계데이터를 저장할 수도 있다.
  • 옵티마이저가 사용하지 못하게 하고, 시스템 통계만 수집하기 위한 목적
  • 2개의 데이터 베이스 사이에 시스템 통계를 이동시키기 위한 목적
• 패키지 실행권한은 PUBLIC에 부여되어 있으나, 통계데이터는 백업테이블에만 저장가능하다.
• 딕셔너리 저장은 gather_system_statistics 롤이 있어야 한다.

7.2 이용 가능한 시스템 통계에는 어떠한 것들이 있는가?

• 시스템 통계수집 방식
  • noworkload 통계 : 오라클에서 미리 정의한 방식으로 DISK I/O 서브시스템의 성능을 시뮬레이션 한다.
  • workload 통계 :실제 애플리케이션이 수행되는 상용환경에서 성능을 측정한다.

• 통계 딕셔너리
  • SYSSTATS_INFO : 통계가 언제 수집되었는지 정보조회가 가능하다.
  • SYSSTATS_MAIN : 시스템 통계 자체가 저장된 딕셔너리이다.
  • SYSSTATS_TEMP : workload 통계가 수집되는 동안 통계 계산값을 임시저장한다.

SQL> select pname, pval1, pval2 from sys.aux_stats$ where sname = 'SYSSTATS_INFO';

PNAME				    PVAL1 PVAL2
------------------------------ ---------- ------------------------------
DSTART					  07-07-2014 06:53
DSTOP					  07-07-2014 06:53
FLAGS					1
STATUS					  COMPLETED

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW			3308.9701
IOSEEKTIM			       10
IOTFRSPEED			     4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

7.3 시스템 통계 수집하기

7.3.1 noworkload 통계

• 언제든지 사용할 수 있는 통계로, 수동삭제 하여도, 오라클 재기동시 자동으로 다시 수집된다.

-- 일반시스템
exec dbms_stats.gather_system_stats(gathering_mode => 'noworkload');
-- 엑사데이터 (MBRC라는 통계항목이 추가로 수집된다.)
exec dbms_stats.gather_system_stats(gathering_mode => 'exadata');

7.3.2 workload 통계

• 관리자가 명시적으로 수집할 때만 사용가능하다.
• 워크로드의 변동폭이 심할 경우 여러날에 걸쳐 계산된 통계의 평균는 오류가 있을 수 있으므로 10분 이내의 짧은 시간 동안 통계를 수집하는 것이 좋다.

-- 수집 시작시점과 종료시점을 직접 지정한다.
exec dbms_stats.gather_system_stats(gathering_mode => 'start');
exec dbms_stats.gather_system_stats(gathering_mode => 'stop');
-- 프로시저 실행순간부터 5분 뒤까지만 수집한다.
exec dbms_stats.gather_system_stats(gathering_mode => 'interval', interval => 5);

-- 수동으로 통계정보를 설정할 수 있다.(수동으로 통계를 설정할 경우 이전 통계 데이터를 삭제해야 새로 지정한 통계데이터와 병합되지 않는다.)
BEGIN
  dbms_stats.delete_system_stats;
  dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 772);
  dbms_stats.set_system_stats(pname => 'SREADTIM', pvalue => 5.5);
  dbms_stats.set_system_stats(pname => 'MREADTIM', pvalue => 19.4);
  dbms_stats.set_system_stats(pname => 'MBRC',      pvalue => 53);
  dbms_stats.set_system_stats(pname => 'MAXTHR',   pvalue => 1136136192);
  dbms_stats.set_system_stats(pname => 'SLAVETHR', pvalue => 16870400);
END;

7.3.3 noworkload 통계 vs workload 통계

• 단순함이 가장 중요하다면 noworkload,
• 병렬 오퍼레이션의 비용을 제어하고자 한다면 workload를 선택한다.(필자는 workload 추천)

7.4 시스템 통계 복원하기

• wri$_optstat_aux_history : 보존기간(기본: 31일) 내에 발생한 통계정보의 이력이 저장된다. 과거 통계로 복원하고자 할 때 사용된다.
• 통계를 복원할 경우 현재 통계 데이터를 삭제해야, 복원한 통계와 현재 통계데이터가 병합되지 않는다.)

BEGIN
  -- 통계를 삭제하고 지정한 날짜의 통계로 복원한다.
  dbms_stats.delete_system_stats();
  dbms_stats.restore_system_stats(to_date(:now,'YYYYMMDDHH24MISS'));
END;

7.5 백업 테이블을 사용한 작업

  -- system 유저가 소유한 mystats 테이블에 통계를 저장한다.
  dbms_stats.gather_system_stats(gathering_mode => 'noworkload', 
                                 statown => 'system', 
                                 stattab => 'MYSTATS');

  -- 통계용 테이블을 생성한다.
  dbms_stats.create_stat_table(owname => user,
                              stattab => 'MYSTATS',
			      tblspace => 'user');

  -- 통계용 테이블을 삭제한다.
  dbms_stats.drop_stat_table(owname => user,
                              stattab => 'MYSTATS');

7.6 관리 오퍼레이션의 로깅

• dba_optstat_operations : 통계정보와 관련된 이력을 조회할 수 있다.

VARIABLE now VARCHAR2(14)

BEGIN
  SELECT to_char(sysdate,'YYYYMMDDHH24MISS') INTO :now FROM dual;
  dbms_stats.delete_system_stats();
  dbms_stats.gather_system_stats('noworkload');
END;
/

-- 통계를 삭제하고 수집한 이력이 조회된다.
SELECT operation, start_time,
       (end_time-start_time) DAY(1) TO SECOND(0) AS duration
FROM dba_optstat_operations
WHERE start_time > to_date(:now,'YYYYMMDDHH24MISS')
ORDER BY start_time;


OPERATION	     START_TIME 		       DURATION
-------------------- --------------------------------- ------------
delete_system_stats  16-NOV-17 01.26.22.317823 AM +09: +0 00:00:00
gather_system_stats  16-NOV-17 01.26.22.481168 AM +09: +0 00:00:16

-- 버전 12c 부터는 파라미터도 같이 조회할 수 있다.
SELECT x.*
FROM dba_optstat_operations o,
     XMLTable('/params/param'
              PASSING XMLType(notes)
              COLUMNS name VARCHAR2(20) PATH '@name',
                      value VARCHAR2(20) PATH '@val') x
WHERE start_time > to_date(:now,'YYYYMMDDHH24MISS')
AND operation = 'gather_system_stats';


NAME		     VALUE
-------------------- --------------------
gathering_mode	     noworkload
interval	     60
statid
statown
stattab

7.7 쿼리 옵티마이저에 미치는 영향

• 쿼리 옵티마이저는 I/O비용과 CPU 비용을 계산한다.
• CPU비용은 아래와 같은 식을 이용한다.
  • cpu비용 = 컬럼위치 * 20

-- 동일 ROW의 서로 다른 컬럼을 조회할 때의 비용이 20씩 늘어남을 확인 할 수 있다.
CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER, 
                c4 NUMBER, c5 NUMBER, c6 NUMBER, 
                c7 NUMBER, c8 NUMBER, c9 NUMBER);

INSERT INTO t VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9);

execute dbms_stats.gather_table_stats(user,'t')

EXPLAIN PLAN SET STATEMENT_ID 'c1' FOR SELECT c1 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c2' FOR SELECT c2 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c3' FOR SELECT c3 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c4' FOR SELECT c4 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c5' FOR SELECT c5 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c6' FOR SELECT c6 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c7' FOR SELECT c7 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c8' FOR SELECT c8 FROM t;
EXPLAIN PLAN SET STATEMENT_ID 'c9' FOR SELECT c9 FROM t;

SELECT statement_id, cpu_cost AS total_cpu_cost, 
       cpu_cost-lag(cpu_cost) OVER (ORDER BY statement_id) AS cpu_cost_1_coll,
       io_cost
FROM plan_table
WHERE id = 0 
ORDER BY statement_id;

STATEMENT_ID TOTAL_CPU_COST CPU_COST_1_COLL IO_COST
------------ -------------- --------------- -------
c1		      35757			  3
c2		      35777		 20	  3
c3		      35797		 20	  3
c4		      35817		 20	  3
c5		      35837		 20	  3
c6		      35857		 20	  3
c7		      35877		 20	  3
c8		      35897		 20	  3
c9		      35917		 20	  3

• SQL구문의 전체 비용은 아래와 같은 식을 이용하여 구한다.
  • 비용 ~ io_cost + (cpu_cost / (cpuspeed * sreadtime * 1000))
• 병렬로 실행되는 SQL구문의 비용은 아래와 같은 식을 이용하여 구한다. (DOP, degree of parallism)
  • parallel_io_cost ~ serial_io_cost / (dop * least(0.9, (slavethr * k) / mreadthr) )
• 시스템통계는 옵티마이저에게 현재 시스템의 정보를 알려준다. 그러므로 시스템 통계는 필수적이다.
• 주요한 하드웨어나 소프트웨어가 변경되면 시스템 통계를 다시 수집해야 한다.