행위

오라클 통계정보 이동

DB CAFE

thumb_up 추천메뉴 바로가기

목차

1 통계정보 수집/추출/입력[편집]

1.1 통계정보수집[편집]

1.1.1 테이블,인덱스포함 통계정보 갱신[편집]

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', method_opt => 'for all indexed columns',cascade => true);
DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                  (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                                      get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'DATA',
   force            BOOLEAN  DEFAULT FALSE,
   context          DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
   options          VARCHAR2 DEFAULT get_param('OPTIONS'));
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ROWNER',
                                  TABNAME => 'TB_XXXX',
                                  CASCADE => TRUE,
                         ESTIMATE_PERCENT => NULL, 
                               METHOD_OPT =>'FOR ALL COLUMNS SIZE 120');
END;
  1. 디폴트 값은 NULL 이고, CREATE TABLE, ALTER TABLE 시 설정된 DEGREE 값에 의해 정해진다.
  2. AUTO_DEGREE 값은 병렬처리 정도를 자동으로 결정한다.
  3. 이것은 1 or DEFAULT_DEGREE [ Object Size 와 CPU Count 에 의해 결정 ]
Parameter Description
Ownname 분석할 테이블 소유자
tabname 테이블 이름
partname 파티션 이름, 지정 하지 않으면 NULL 값
Estimate_percent 분석할 Row의 Percentage, NULL 이면 Compute(Row 전체)

유효값은 1/1000000 ~ 100 디폴트로, DBMS_STATS.AUTO_SAMPLE_SIZE 에 의해서 최적의 값을 결정

Block_sample random block sampling or random row sampling 결정

random block sampling 이 좀더 효과적이다. 데이터의 블록 별 분포도가 안좋을 시에는 부적절한 정보 생성 디폴트 값이 False로, random row sampling 을 수행한다.

Method_opt Histogram 생성시 사용하는 옵션 
       FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ]
       FOR COLUMN [ size clause ] column | attribute [size clause]
                 [, column|attribute [ size clause ]…]
  • Size_clause := SIZE { integer | REPEAT | AUTO | SKEWONLY }

n Integer : Histogram Bucket 수, Max 는 1,254

n REPEAT : 이미 Histogram 이 있는 칼럼에 대해서만 생성

n AUTO : 데이터 분산도와 칼럼 부하 정도에 따라서 생성 결정

n SKEWONLY : 데이터 분산도에 따라서 생성 결정

디폴트 값은 FOR ALL COLUMNS SIZE AUTO

  • Histogram 의 생성여부를 Oracle 이 알아서 판단하게 된다.

이 경우 EX) method_opt => FOR ALL COLUMNS SIZE 1

  • 모든 컬럼에 대해서 Histogram bucket 의 수를 1로 한다.
  • 즉, 컬럼 내에 존재하는 여러 가지 값들의 cardinality 는 모두 동일한 값으로 간주한다. ( histogram 을 사용하지 않는다.)
  • 이것은 where 조건에 들어오는 특정 컬럼에 대한 값의 변화에 따라서 PLAN 이 변경될 가능성을 없애고자 함이다.
  • FOR ALL COLUMNS SIZE 1 로 설정하여 Histogram 정보가 생성되지 않도록 조치 한다.
granularity Parition table 에 대한 분석시 사용
  1. ‘ALL’ – Global, Partition, Subpartition 통계정보 수집 – Parition Table 대상
  2. ‘AUTO’ – 디폴트 값으로 ,Partition Type 에 따라서 결정 – 일반 Table 대상
  3. ‘DEFAULT’ – Global, Partition 통계정보 수집, Old Version 과 호환을 위해 사용
  4. ‘GLOBAL’ - Global 통계정보 수집
  5. ‘GLOBAL AND PARTITION’ – SubPartition 에 대한 통계정보는 수집되지 않는다.
  6. ‘PARTITION’ – Partition 통계정보 수집
  7. ‘SUBPARTITION’ – SubPartition 통계정보 수집
cascade 대상 테이블의 인덱스에 대한 통계수집 여부

인덱스 통계정보는 병렬처리가 불가능하다. TRUE – 대상 테이블에 관련된 index 에 대해서 통계정보 수집

stattab 통계수집을 통한 기존 통계정보 Update 전에,

기존에 존재하는 통계정보를 저장할 User Stat Table 을 지정

statid Stattab 와 연관된 구분자 값
statown Stattab 에 지정한 User Stat Table 의 소유자가 다를 경우 지정
no_invalidate
  • 의존적인 Cursor를 Invalidate 할지 , 안할지 결정
    True – 관련된 Cursor 를 invalidate 하지 않는다.
    False – 관련된 Cursor 를 Invalidate 한다.

Default 로 DBMS_STATS.AUTO_INVALIDATE 값이고, 의미는 DBMS 가 의존적 Cursor 를 언제 invalidate 할지 자동으로 결정 이때 작용하는 Parameter는 _OPTIMIZER_INVALIDATION_PERIOD 이고, Default 롤 18000 초(5시간) 이다. 

즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에  해당 Cursor가 실행될 때 invalidation이 발생한다.

이것을 Auto Invalidation이라고 부른다. 일정 시간에 걸쳐 랜덤하게 Cursor를 Invalidation함으로써 특정 시점에 Hard Parse가 한꺼번에 몰리는 현상을 피할 수 있다.

_OPTIMIZER_INVALIDATION_PERIOD 파라미터로 시간 조절 가능

force Lock 걸린 Table 에 대해서도 강제로 통계정보 생성


android 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다.


1.1.2 테이블 DBMS_STATS.GATHER_TABLE_STATS[편집]

  • 예시 : EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'CYKIM'
  , stattab=>'TB_MGR_STATS'
  , tabname=>'TB_DICT'
  , method_opt=>'FOR ALL INDEXED COLUMNS SIZE 74'
  , cascade=>TRUE);

보다자세히 

exec DBMS_STATS.GATHER_TABLE_STATS (
  'CYKIM'
,'TB_DICT5'
 , estimate_percent => 100
, method_opt => 'FOR ALL COLUMNS SIZE 1'
 , degree=> 10
, granularity => 'ALL'
, cascade => TRUE
, no_invalidate => FALSE);

1.1.3 파티션테이블[편집]

exec DBMS_STATS.GATHER_TABLE_STATS (
  'CYKIM'
, 'TB_DICT5'
, partname => 'P201102'   -- 파티션명
, estimate_percent => 5
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, degree=> 10
, granularity => 'ALL'
, cascade => TRUE
, no_invalidate => FALSE);

1.1.4 스키마 DBMS_STATS.GATHER_SCHEMA_STATS[편집]

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
      ownname => 'CYKIM'
    , method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
    , cascade=>TRUE);

1.1.5 인덱스 DBMS_STATS.GATHER_INDEX_STATS[편집]

EXEC DBMS_STATS.GATHER_INDEX_STATS(
'CYKIM'
,'IX_DICT_01');

1.1.6 파티션인덱스[편집]

EXEC DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'CYKIM'
, indname => 'IX_DICT_01'
, partname => 'P20110222');

1.1.7 데이터베이스 DBMS_STATS.GATHER_DATABASE_ STATS[편집]

DBMS_STATS.GATHER_DATABASE_ STATS

1.1.8 시스템통계정보 DBMS_STATS.GATHER_SYSTEM_STATS[편집]

DBMS_STATS.GATHER_SYSTEM_STATS

1.1.9 보다 상세한 통계정보[편집]

DBMS_STATS.GENERATE_STATS

1.2 테이블/인덱스 통계정보를 옮기는 방법.[편집]

  • DBMS_STATS.GATHER_TABLE_STATS 패키지 이용

android :1) 통계정보를 임시로 저장할 Table생성.

2) 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.
3) 통계정보 백업 테이블 통계 수집
4) 임시 table을 export, 그리고 target db로 import.
5) 임시 Table로 부터 통계치를 대상 테이블에 입력.
6) 통계정보 테이블 삭제


보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우.

  • 통계정보 추출
    • 테이블 통계 백업 dbms_stats.export_table_stats
    • 스키마 통계 백업 dbms_stats.export_schema_stats

1.2.1 통계정보를 임시로 저장할 Table생성.[편집]

EXEC DBMS_STATS.CREATE_STAT_TABLE (
           '스키마명'
          ,'TB_MGR_STATS'
          ,'테이블스페이스명-생략가능'
          );

1.2.2 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.[편집]

1.2.2.1 테이블 정보 EXPORT[편집]
  • dbms_stats.export_table_stats ('table_owner', 'table name', null, 'stat table','stat_id',true,'stat table owner');
EXEC DBMS_STATS.EXPORT_TABLE_STATS(
  'SCOTT'         -- 테이블 오너 
, 'TB_EMP'     -- 테이블명 
, NULL            -- 파티션명 
, 'TB_MGR_STATS'   -- 통계테이블명 
, 'TB_DICT_20161101' -- statid  옵션 식별자(버전)
, TRUE            -- cascade 인덱스포함하여
, 'CYKIM'         -- 통계테이블 소유자 
) ;
1.2.2.2 스키마 정보 EXPORT[편집]
  • dbms_stats.export_schema_stats ('schema name', 'stat table','stat_id','stat table owner');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (
  'SCOTT' -- schema name
 ,'TB_MGR_STATS' -- stat table
 ,'CYKIM_SCHEMA_20160101' -- stat_id(추후 복구용으로 사용할 ID)
 ,'CYKIM' -- stat table owner
);

1.2.3 통계정보 백업 테이블 통계 수집[편집]

EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);

1.2.4 임시 table을 export, 그리고 target db로 import.[편집]

-- 만약 동일 DB라면 이 과정은 생략 가능합니다.
$exp scott/tiger tables=STATS file=expstat.dmp
$imp scott/tiger file=expstat.dmp full=y log=implog.txt

1.2.5 임시 Table로 부터 통계치를 원하는 table에 넣는다.[편집]

1.2.5.1 테이블 통계정보 입력[편집]

EXEC DBMS_STATS.IMPORT_TABLE_STATS(
  'SCOTT'         -- 테이블 오너 
, 'TB_EMP'        -- 테이블명 
, NULL            -- 파티션명 
, 'TB_MGR_STATS'   -- 통계테이블명 
, 'TB_DICT_20161101' -- statid  옵션 식별자(버전)
, TRUE            -- cascade 인덱스포함하여
, 'CYKIM'         -- 통계테이블 소유자 
           );

1.2.5.2 스키마 정보 입력[편집]

DBMS_STATS.IMPORT_TABLE_STATS(
ownname, tabname, partname
, stattab, statid, cascade, statown);

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (
  'CYKIM' -- schema name
, 'TB_MGR_STATS' -- stat table
, 'CYKIM_TB_MGR_STATS' -- stat_id
, 'CYKIM' -- stat table owner
);

1.3 통계정보 테이블 삭제 시[편집]

EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');

1.4 ORA-38029: object statistics are locked 에러 발생시[편집]

exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명');

1.5 테이블/인덱스/컬럼 통계정보조회[편집]

1.5.1 테이블 통계정보 조회[편집]

p
SELECT OWNER
     , TABLE_NAME
     , BLOCKS  -- 해당 데이터가 저장되어 있는 블록 수.
     , NUM_ROWS  -- 데이터 행 수.
     , AVG_ROW_LEN  -- 하나의 행의 평균 길이.
     , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
  FROM USER_TABLES
-- [DBA_TABLES]       
-- [WHERE TABLE_NAME = '테이블명']
  • DBA_TAB_STATISTICS
SELECT * 
  FROM DBA_TAB_STATISTICS

1.5.2 컬럼 통계정보 조회[편집]

SELECT TABLE_NAME
     , COLUMN_NAME  -- 컬럼명
     , LOW_VALUE    -- 해당 컬럼에 저장되어 있는 최소값.
     , HIGH_VALUE      -- 해당 컬럼에 저장되어 있는 최대값.
     , NUM_DISTINCT   -- 유일한 값의 수. (히스토그램 기준)
     , NUM_BUCKETS    -- 히스토그램 
  --   , HISTOGRAM
  FROM DBA_TAB_COLUMNS
[WHERE TABLE_NAME = '테이블명']
  • DBA_TAB_COL_STATISTICS
SELECT * 
  FROM DBA_TAB_COL_STATISTICS

1.5.3 인덱스 통계정보 조회[편집]

SELECT INDEX_NAME
     , BLEVEL                        -- 인덱스의 깊미(Depth)
     , LEAF_BLOCKS              -- 리프 블록의 수.
     , DISTINCT_KEYS            -- 인덱스 컬럼의 유일한 값의 수.
     , CLUSTERING_FACTOR  -- 조건을 만족하는 데이터를 검색할 때 인덱스 키 값이 각 블록에 얼마나 잘 분산 저장되어 있는지를 나타내는 정도.
     , NUM_ROWS                 -- 전체 행수.
     , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
  FROM USER_INDEXES
-- [DBA_INDEXES]
  • DBA_IND_STATISTICS
SELECT * 
  FROM DBA_IND_STATISTICS

1.6 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신[편집]

ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;

1.6.1 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)[편집]

SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;' 
  FROM DBA_TABLES 
 WHERE OWNER = '스키마명';

1.6.2 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신[편집]

ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS 
  FROM INDEX_STATS;

​

1.6.2.1 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성[편집]

SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' 
  FROM DBA_INDEXES WHERE OWNER = '스키마명';
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_통계정보_이동&oldid=11420"