Oracle 데이터베이스의 통계정보

haisins

7년 ago

Oracle Enhanced Statistics Gathering

Cost-Based Optimizer(CBO)는 데이터베이스의 통계 정보에 의존합니다. 따라서, CBO가 정확한 실행 계획(Execution Plan)을 생성하도록 하려면, 사용자는 CBO를 사용하기 전에 SQL 문에서 접근하는 모든 테이블, 인덱스, 클러스터 등의 데이터베이스 객체에 대한 통계 정보를 생성해 두어야 합니다. 만약, 테이블의 크기나 데이터 분포가 자주 변경된다면, 통계 정보의 정확성을 위해 사용자는 주기적으로 관련 객체에 대한 통계 정보를 생성해 두어야 합니다.

Oracle 에서는 통계 정보 생성 시 데이터의 샘플 크기를 자동으로 지정해 주는 기능, CPU와 I/O 등의 통계 정보를 Cost-Based Optimizer에서 사용할 수 있는 기능들이 추가되었습니다.

Oracle 에 새롭게 추가된 통계 정보 수집 관련 기능과 시스템 통계 정보를 수집하는 기능에 대해 살펴 보도록 합니다

New Gathering Statistics Estimation

정확한 통계 정보를 생성(CUMPUTE STATISTICS)하기 위해서는 필요한 객체에 대한 검색 및 정렬 작업 때문에 많은 양의 디스크/메모리 공간과 시간을 필요로 합니다. 따라서, 공간 낭비와 시간을 줄이기 위하여 통계 정보 생성 시 샘플링 기법(ESTIMATE STATISTICS)을 사용합니다. 하지만, 최적의 샘플 크기를 결정하는 것은 쉽지 않습니다.

샘플링 기법을 사용할 경우 테이블 데이터를 저장하고 있는 블록의 수, 인덱스 깊이(depth of index) 등은 정확하게 계산되지만, 모든 통계 정보가 정확하게 계산되지는 않습니다. 따라서, 정확한 통계 정보를 수집하기 위해서는 많은 양의 데이터를 샘플로 설정하여 통계 정보를 수집하여야 합니다. 하지만, 이럴 경우 통계 정보 수집 시 공간 낭비와 통계 정보 수집 시간이 길어집니다. 또한, 너무 적은 양의 샘플 크기를 지정할 경우 정확한 통계 정보 수집을 기대하기 힘듭니다. Oracle 에서는 위와 같은 샘플 크기를 결정하는 문제를 해결하기 위하여 통계 정보 계산의 정확성을 높이면서 통계 정보 수집 시 사용되는 공간 및 시간을 절약할 수 있도록 CBO가 샘플 크기를 자동으로 설정할 수 있도록 DBMS_STATS Package에 새로운 파라미터 값을 추가하였습니다.

ESTIMATE_PERCENT Parameter value of DBMS_STATS Package

Oracle 에서는 샘플의 크기를 자동으로 계산해 주기 위한 새로운 파라미터 값이 DBMS_STATS Package에 추가되었습니다. DBMS_STATS Package의 통계정보를 수집하는 프로시저의 ESTIMATE_PERCENT 파라미터 값으로 DBMS_STATS. AUTO_SAMPLE_SIZE 라는 새로운 값을 사용할 수 있게 되었습니다. 이 값을 사용하면 부정확한 통계 정보를 얻거나 혹은 통계 정보를 구하는데 드는 시간을 줄이고, 적절한 통계 정보의 정확도를 유지하고 최대의 성능 상의 이득을 얻을 수 있습니다. 예를 들어 “SCOTT” 스키마의 모든 테이블에 대한 테이블과 컬럼의 통계 정보를 구하는 방법은 다음과 같습니다.

EXECUTE DBMS_STATS.GET_SCHEMA_STATS( ‘SCOTT’, DBMS_STATS.AUTO_SAMPLE_SIZE );

METHOD_OPT Parameter Values of DBMS_STATS Package

Oracle 에서는 DBMS_STATS Package의 통계 정보 수집 프로시저들의 METHOD_OPT 파라미터에도 새로운 값을 추가하였습니다.

Size 절이
REPEAT로
설정되어
있으면, 컬럼에 Histogram이
있으면
동일한
수의 bucket을
이용하여 Histogram을
생성하고, 컬럼에 Histogram이
없으면
새롭게
생성하지
않습니다. 즉, REPEAT 옵션은
동일한 “Class”의
통계
정보를
수집합니다.

Size절이
AUTO로
설정되어
있으면, Oracle은
데이터
분산(skew)과
어플리케이션에서
컬럼을
접근하는
방법(sparsity)을
고려하여 Histogram을
생성할
것인지를
결정합니다. 하지만, 어플리케이션이
해당
컬럼에
대한
부하를
구하는데
충분한
시간동안
수행되지
않았다면
임시적으로 SKEWONLY 파라미터
값을
사용하는
것이
유용합니다.

Size절이
SKEWONLY로
설정되어
있으면, Oracle은
데이터의
분산
정도만
고려하여 Histogram을
생성할
것인지를
결정합니다. 이
옵션은
처음으로
통계
정보를
수집할
때
유용합니다. 즉, 어플리케이션이
충분히
수행되지
않아
컬럼에
대한
부하
자료가
없을
때
사용합니다.

아래의 예는 SCOTT 스키마의 모든 테이블, 인덱스, 컬럼에 대한 통계 정보를 구하는 예를 보여준다. 이 작업을 수행하면 Oracle이 Sampling Percentage를 얼마로 할지, Histogram이 필요한지를 결정하여 처리합니다.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(

Ownname => ‘SCOTT’,

Estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

Method_opt => ‘for all column size AUTO’ );

Gathering System Statistics

Oracle 에서는 Optimizer는 시스템 통계 정보를 이용하여 시스템의 I/O 및 CPU의 성능도 고려하여 Plan을 작성합니다. Optimizer는 모든 가능한 Plan에 대해 I/O 와 CPU의 예상 비용을 계산합니다. 따라서, I/O와 CPU 비용을 최적화한 효율적인 Plan을 얻기 위해서는 시스템의 특징을 알아 내는 것이 중요합니다.

CPU와 IO 특성은 많은 요소에 의해 영향을 받고 일정하게 유지되지 않습니다. 시스템 통계 정보 관리 기능을 이용하면, 데이터베이스 관리자는 시스템이 일반적인 작업을 수행하는 기간 동안의 통계 정보를 수집할 수 있습니다. 예를 들면, 주간에는 OLTP 트랜잭션이 수행되고 야간에는 OLAP 리포트 작업을 수행합니다고 하자. 이럴 경우, 관리자는 필요할 경우 OLTP 와 OLAP 작업이 처리되는 동안의 시스템 통계 정보를 수집할 수 있습니다. 이렇게 수집된 통계 정보를 고려하여 Optimizer는 상대적인 비용을 계산합니다.

Oracle은 시스템 통계 정보를 생성할 때 주어진 시간 동안의 시스템 활동을 분석합니다. 테이블, 인덱스, 컬럼 등의 통계 정보와 달리 시스템 통계 정보가 변경되었을 때 Oracle은 이미 Parse된 SQL 문장은 invalidate시키지 않고, 새로운 SQL 문장은 변경된 통계 정보를 이용하여 parsing합니다. Oracle에서는 시스템 통계 정보를 수집하도록 권장합니다.

DBMS_STATS.GATHER_SYSTEM_STATS 함수는 사용자가 정의한 기간 동안 시스템 통계 정보를 수집합니다. DBMS_STATS.SET_SYSTEM_STATS 함수를 사용하면 명시적으로 시스템 통계 정보 값을 지정할 수도 있습니다. 시스템 통계 정보 값을 확인하기 위해서는 DBMS_STATS.GET_SYSTEM_STATS 함수를 사용합니다.

DBMS_STATS Package Subprograms related to System Statistics

DBMS_STATS Package 중 시스템 통계 정보를 관리와 관련된 프로시저들은 다음과 같습니다.

DBMS_STATS.GATHER_SYSTEM_STATS

이 함수는 시스템 통계 정보를 수집합니다.

Syntax

DBMS_STATS.GATHER_SYSTEM_STATS (
gathering_mode VARCHAR2 DEFALUT ‘INTERVAL’,
interval INTEGER DEFAULT 60,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
Gathering_mode	Mode 값은 다음과 같다 : – INTERVAL : 지정 시간동안 시스템 활동 정보를 수집합니다. 사용자는 시간을 분단위로 지정할 수 있습니다. – START \| STOP : 지정한 시작 시간과 종료 시간 동안 시스템 정보를 수집하여 dictionary 나 stattab 을 변경합니다.
Interval	통계 정보를 수집하는 시간. 분단위로 지정합니다.
Stattab	통계 정보가 저장될 사용자 Stat 테이블
Statid	Stat 테이블에 저장될 통계 정보의 ID. (Optional)
Statown	Stat 테이블을 소유하는 스키마. 이 함수를 수행하는 사용자와 다를 경우 지정합니다.

DBMS_STATS.SET_SYSTEM_STATS

이 함수는 시스템 통계 정보를 사용자가 지정한 값으로 설정합니다.

Syntax

DBMS_STATS.SET_SYSTEM_STATS (
pname VARCHAR2,
pvalue NUMBER,
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
Pname	설정할 파라미터 이름. 다음 중 하나를 지정할 수 있습니다. – sreadtim : 한 블록을 읽기위한 대기 시간. Miliseconds 단위 – mreadtim : 다중 블록을 읽기 위한 대기 시간. Miliseconds 단위 – cpuspeed : 초당 cycle 수. Million 단위
Pvalue	설정할 파리미터 값
Stattab	통계 정보가 저장될 사용자 Stat 테이블
Statid	Stat 테이블에 저장될 통계 정보의 ID. (Optional)
Statown	Stat 테이블을 소유하는 스키마. 이 함수를 수행하는 사용자와 다를 경우 지정합니다

DBMS_STATS.GET_SYSTEM_STATS

이 함수는 Stattab으로 부터 시스템 통계 정보를 가져 온다. 만약 Stattab이 NULL 이면 dictionary로부터 가져온다.

Syntax

DBMS_STATS.GET_SYSTEM_STATS (
status OUT VARCHAR2,
dstart OUT DATE,
dstop OUT DATE,
pname VARCHAR2,
pvalue OUT NUMBER,
stattab IN VARCHAR2 DEFAULT NULL,
statid IN VARCHAR2 DEFAULT NULL,
statown IN VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
Status ( OUT )	출력될 수 있는 값은 다음과 같습니다. – COMPLETED – AUTOGATHERING – MANUALGATHERING – BADSTATS
Dstart ( OUT )	통계 정보 수집이 시작된 시간. 만약 status가 MANUALGATHERING 이면 시작 시간이 리턴됩니다.
Dstop ( OUT )	통계 정보 수집이 종료된 시간. Status가 COMPLTE면 종료 시간이 리턴됩니다. Status가 AUTOGATHERING이면 다음 종료 시간이 리턴됩니다. Status가 BADSTATS이면 had-to-be-finished-by 시간이 리턴됩니다.
Pname	구하고자 하는 파라미터 이름. 다음 중 하나를 지정할 수 있습니다. – sreadtim : 한 블록을 읽기 위한 대기 시간. Miliseconds 단위 – mreadtim : 다중 블록을 읽기 위한 대기 시간. Miliseconds 단위 – cpuspeed : 초당 cycle 수. Million 단위
Pvalue	구하고자 하는 파라미터 값
Stattab	통계 정보가 저장될 사용자 Stat 테이블
Statid	Stat 테이블에 저장될 통계 정보의 ID. (Optional)
Statown	Stat 테이블을 소유하는 스키마. 이 함수를 수행하는 사용자와 다를 경우 지정합니다

DBMS_STATS.EXPORT_SYSTEM_STATS

시스템 통계 정보를 추출하여 사용자 stattab에 저장합니다.

Syntax

DBMS_STATS.EXPORT_SYSTEM_STATS (
statab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
Stattab	통계 정보가 저장될 사용자 Stat 테이블
Statid	Stat 테이블에 저장될 통계 정보의 ID. (Optional)
Statown	Stat 테이블을 소유하는 스키마. 이 함수를 수행하는 사용자와 다를 경우 지정합니다

DBMS_STATS.IMPORT_SYSTEM_STATS

사용자 Stat 테이블의 시스템 통계 정보를 Dictionary에 저장합니다.

Syntax

DBMS_STATS.IMPORT_SYSTEM_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
Stattab	통계 정보를 추출할 사용자 Stat 테이블
Statid	Stat 테이블에 저장될 통계 정보의 ID. (Optional)
Statown	Stat 테이블을 소유하는 스키마. 이 함수를 수행하는 사용자와 다를 경우 지정합니다

DBMS_STATS.DELETE_SYSTEM_STATS

시스템 통계 정보를 삭제합니다.

Syntax

DBMS_STATS.DELETE_SYSTEM_STATS (
statab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
Stattab	통계 정보가 저장되어 있는 사용자 stat 테이블
Statid	Stat 테이블에 저장된 통계 정보의 ID. (Optional)
Statown	Stat 테이블을 소유하는 스키마. 이 함수를 수행하는 사용자와 다를 경우 지정합니다

테스트

Oracle에 새롭게 추가된 자동 샘플 크기 설정 기능과 시스템 통계 정보 관리 기능에 대한 테스트를 수행합니다.

New Gathering Statistics Estimation

테스트를 위해 Sales History(SH) 스키마를 사용합니다. SH 스키마의 SALES 테이블은 약 90만 건이 넘는 데이터가 있습니다. 이 경우, 통계 정보 계산 시 DBMS_STATS.AUTO_SAMPLE_SIZE 파라미터 값을 사용합니다.

먼저 테스트를 위해 사용자 STAT 테이블을 생성합니다.

SQL> execute dbms_stats.create_stat_table( ‘SH’, ‘STATTAB’ );

PL/SQL procedure successfully completed.

DBMS_STATS.GATHER_TABLE_STATS 함수를 이용하여 SALES 테이블의 통계 정보를 수집합니다.

SQL> execute dbms_stats.gather_table_stats(
ownname => ‘SH’,
tabname => ‘SALES’,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘for all columns size AUTO’,
stattab=>’STATTAB’ );

PL/SQL procedure successfully completed.

Gathering System Statistics

시스템 통계 정보 수집 관련 DBMS_STATS 함수들을 테스트해 보도록 합니다.

사용자 STAT 테이블 생성

일반적으로 데이터베이스 관리자는 Dictionary에 시스템 통계 정보를 저장하지만 테스트를 위해 사용자 스키마에 STAT 테이블을 생성합니다.

SQL>execute dbms_stats.create_stat_table(‘akira’,’STATTAB’);

PL/SQL procedure successfully completed.

SQL>select * from STATTAB;

No rows selected

위 예는 ‘AKIRA’ 스키마에 ‘STATTAB’ 이라는 이름으로 사용자 STAT 테이블을 생성하였습니다.

GATHER_SYSTEM_STATS

시스템 통계 정보를 수집할 때 사용하는 DBMS_STATS.GATHER_SYSTEM_STATS 함수는 두가지 방식으로 사용할 수 있습니다.

gathering_mode를 ‘INTERVAL‘로
설정하여
지정
시간동안
시스템
통계
정보를
수집하도록
합니다. 이
작업은
내부적으로
큐잉
기법을
사용합니다. 따라서, init.ora 의
job_queue_processes
값을 1 이상이
되도록
설정하여야
합니다.

gathering_mode를 ‘START | STOP‘ 으로
설정하여
사용자가
명시적으로
시작과
끝을
지정할
수
있습니다.

두 가지 방법 각각에 대한 테스트 결과는 다음과 같습니다.

—

— 3분
동안
시스템
통계
정보를
수집하도록
합니다.

—

SQL> execute

dbms_stats.gather_system_stats(‘INTERVAL’,3,’STATTAB’);

PL/SQL procedure successfully completed.

—

— 위의
작업을
수행한
후 USER_JOB 테이블을
확인해
봅니다.

—

SQL>select job,to_char(next_date,’YYYY/MM/DD HH:MI:SS’),what

2 from user_jobs;

JOB TO_CHAR(NEXT_DATE,’

———- ——————-

WHAT

————————————————————

10 2001/03/21 05:56:53

dbms_stats.gather_system_stats(gathering_mode => ‘AUTO_STOP’, stattab => ‘STATTAB’, statown => ‘AKIRA’);

—

— 3분
후 queue에
있는 job이
처리된
후 STATTAB을
확인해
봅니다.

— 하지만, Queue에
있는 job을
처리할
때
에러가
발생하기
때문에

— STATTAB 상태는 ‘BADSTATS’로
나온다.

—

SQL> select type, version, flags, c1 from stattab;

T VERSION FLAGS C1

– ———- ———- ——————————

S 4 0 BADSTATS

하지만 위와 같이 수행할 경우, 3분 후 큐에 있는 JOB 을 처리할 때 다음과 같은 에러가 발생합니다. 아직 원인은 알 수 없다.

*** SESSION ID:(13.157) 2001-03-21 17:56:54.836

*** 2001-03-21 17:56:54.836

ORA-12012: error on auto execute of job 10

ORA-23421: job number 10 is not a job in the job queue

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86

ORA-06512: at “SYS.DBMS_IJOB”, line 525

ORA-06512: at “SYS.DBMS_IJOB”, line 258

ORA-06512: at line 1

다음은 ‘gathering_mode’를 ‘START | STOP’으로 수행한 결과입니다. 이 경우에도 마찬가지로 STATTAB의 상태는 ‘BADSTATS’입니다. 원인 분석이 필요합니다.

—

— gathering_mode를 ‘START’로
하여
시스템
통계
정보
수집을

— 시작합니다.

SQL> execute dbms_stats.gather_system_stats ( gathering_mode=>’START’, stattab=>’STATTAB’ );

PL/SQL procedure successfully completed.

—

— gathering_mode를 ‘STOP’으로
하여
시스템
통계
정보
수집을

— 종료합니다.

SQL> execute dbms_stats.gather_system_stats ( gathering_mode=>’STOP’, stattab=>’STATTAB’ );

PL/SQL procedure successfully completed.

—

— STATTAB을
확인해
봅니다.

—

SQL> select type, version, flags, c1 from stattab;

T VERSION FLAGS C1

– ———- ———- ——————————

S 4 1 BADSTATS

SET_SYSTEM_STATS

먼저, GET_SYSTEM_STATS 함수를 사용하여 위에서 수집한 시스템 통계 정보를 확인해 보아야 하지만, 위의 경우처럼 시스템 통계 정보가 ‘BADSTATS’일 경우엔 에러가 발생합니다. 따라서, SET_SYSTEM_STATS 함수를 사용하여 시스템 통계 정보를 사용자가 정의한 값으로 설정합니다.

—

— 변경
가능한 ‘sreadtim’, ‘mreadtim’, ‘cpuspeed’
— 세가지
값을
변경해
봅니다.
—

SQL> execute dbms_stats.set_system_stats( pname=>’sreadtim’, pvalue=>99, stattab=>’STATTAB’);

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.set_system_stats( pname=>’mreadtim’, pvalue=>99, stattab=>’STATTAB’);

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.set_system_stats( pname=>’cpuspeed’, pvalue=>4, stattab=>’STATTAB’);

PL/SQL procedure successfully completed.

—
— STAT 테이블의
상태를
확인해
봅니다.
–- 정상적으로 SET_SYSTEM_STATS이
수행되었을
경우
–- 시스템
통계
정보는 ‘COMPLETED’ 라는
상태를
가집니다.

—

SQL> select type, version, flags, c1 from stattab;

T VERSION FLAGS C1

– ———- ———- ——————————

S 4 1 COMPLETED

GET_SYSTEM_STATS

위에서 SET_SYSTEM_STATS 으로 설정한 값을 확인해 봅니다.

SQL> set serveroutput on

SQL>

SQL> declare

2 v_status varchar2(20);

3 v_dstart STATTAB.D1%TYPE;

4 v_dstop STATTAB.D1%TYPE;

5 v_sreadtim NUMBER;

6 v_mreadtim NUMBER;

7 v_cpuspeed NUMBER;

8 begin

9 dbms_stats.get_system_stats( v_status, v_dstart, v_dstop, ‘sreadtim’, v_sreadtim, ‘STATTAB’ );

10 dbms_stats.get_system_stats( v_status, v_dstart, v_dstop, ‘mreadtim’, v_mreadtim, ‘STATTAB’ );

11 dbms_stats.get_system_stats( v_status, v_dstart, v_dstop, ‘cpuspeed’, v_cpuspeed, ‘STATTAB’ );

12 dbms_output.put_line( ‘STATUS=’ || v_status );

13 dbms_output.put_line( ‘START TIME=’ || to_char( v_dstart, ‘YY/MM/DD HH:MI:SS’));

14 dbms_output.put_line( ‘STOP TIME=’ || to_char(v_dstop, ‘YY/MM/DD HH:MI:SS’));

16 dbms_output.put_line( ‘sreadtim=’ || v_sreadtim );

17 dbms_output.put_line( ‘mreadtim=’ || v_mreadtim );

18 dbms_output.put_line( ‘cpuspeed=’ || v_cpuspeed );

19 end;

20 /

STATUS=COMPLETED

START TIME=01/03/22 10:21:00

STOP TIME=01/03/22 10:21:00

sreadtim=99

mreadtim=99

cpuspeed=4

PL/SQL procedure successfully completed.

위의 결과에서 보는 바와 같이 DBMS_STATS.SET_SYSTEM_STATS을 이용하여 사용자가 지정한 값을 확인해 볼 수 있습니다.

활용가이드 및 결론

Oracle에서는 통계 정보 생성 시 샘플 크기를 자동으로 지정해 주는 기능, CPU와 I/O 등의 시스템 통계 정보를 수집하는 기능 등이 추가되었습니다.

자동 샘플 크기 설정 기능을 사용하면, 사용자가 너무 적은 양의 샘플 크기를 설정하여 부정확한 통계 정보를 얻거나, 샘플 크기를 너무 크게 설정하여 통계 정보를 구하는데 많은 시간적, 공간적 비용을 낭비하는 것을 방지할 수 있습니다. 즉, 적절한 통계 정보의 정확성을 유지하면서 최대의 성능 상의 이득을 얻을 수 있습니다.

또한, Oracle에 새롭게 추가된 시스템 통계 정보를 수집하여 시스템의 I/O 및 CPU의 성능도 고려하여 Optimizer가 Plan을 작성하게 하여 성능 향상을 기대할 수 있습니다.