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 통계가 수집되는 동안 통계 계산값을 임시저장한다.

SYS@16-NOV-17> 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

SYS@16-NOV-17> 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) )
• 시스템통계는 옵티마이저에게 현재 시스템의 정보를 알려준다. 그러므로 시스템 통계는 필수적이다.
• 주요한 하드웨어나 소프트웨어가 변경되면 시스템 통계를 다시 수집해야 한다.