You Lead!!

[출처] 10g 자동화 통계정보 수집에 관하여... [2008/05/11 Update]|작성자 쏘심이

출처 : http://www.oracleclub.com/article/23928
        http://blog.naver.com/xsoft?Redirect=Log&logNo=150043500269

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 생성시 사용하는 옵션 l        FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ] l        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 정보가 생성되지 않도록 조치 한다.
degree	병렬처리 정도 디폴트 값은 NULL 이고, CREATE TABLE, ALTER TABLE 시 설정된 DEGREE 값에 의해 정해진다. AUTO_DEGREE 값은 병렬처리 정도를 자동으로 결정한다. 이것은 1 or DEFAULT_DEGREE [ Object Size 와 CPU Count 에 의해 결정 ]
granularity	Parition table 에 대한 분석시 사용 ‘ALL’ – Global, Partition, Subpartition 통계정보 수집 – Parition Table 대상 ‘AUTO’ – 디폴트 값으로 ,Partition Type 에 따라서 결정 – 일반 Table 대상 ‘DEFAULT’ – Global, Partition 통계정보 수집, Old Version 과 호환을 위해 사용 ‘GLOBAL’ - Global 통계정보 수집 ‘GLOBAL AND PARTITION’ – SubPartition 에 대한 통계정보는 수집되지 않는다. ‘PARTITION’ – Partition 통계정보 수집 ‘SUBPARTITION’ – SubPartition 통계정보 수집
cascade	대상 테이블의 인덱스에 대한 통계수집 여부 인덱스 통계정보는 병렬처리가 불가능하다. TRUE – 대상 테이블에 관련된 index 에 대해서 통계정보 수집
stattab	통계수집을 통한 기존 통계정보 Update 전에, 기존에 존재하는 통계정보를 저장할 User Stat Table 을 지정
statid	Stattab 와 연관된 구분자 값
statown	Stattab 에 지정한 User Stat Table 의 소유자가 다를 경우 지정
no_validate	의존적인 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가  한꺼번에 몰리는 현상을 피할 수 있다.
force	Lock 걸린 Table 에 대해서도 강제로 통계정보 생성

출처 : http://www.oracleclub.com/article/23893

[ 출처 ]
1. 10g Optimizer개념 및 통계치 생성 방법,SQL Tuning 방법
http://www.dbguide.net/club/board/download.jsp?maskname=274&fileName=10g+Optimizer+to+public.pdf

2.  10g 자동화 통계정보 수집에 관하여... [2008/05/11 Update]
http://blog.naver.com/sungeunn/120051268815

3.  어떤 STATISTICS_LEVEL 을 사용할 것인가 ?
http://cafe.naver.com/prodba/9293

4. 메타링크
  4.1 Two types of automatic statistics collected in 10g [ 559029.1  ]
  4.2 How to Disable Automatic Statistics Collection in 10G ? [ 311836.1 ]
5. 10G References [ STATISTICS_LEVEL ]
  http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#REFRN10214

 [ 결론 ]
1. System 통계정보는 따로 생성 하지 않는다.
    어느 시간이 최적인지 모른다. DBA 가 알수 있으면 정기적으로 생성해서 활용한다.
2. 자동 통계정보 수집(CBO) 는 disable 하고, 수동으로 통계정보를 생성한다.
   운영 중에 통계정보 생성으로 인해 업무의 성능 저하를 사전에 예방하자.
3. STATISTICS_LEVEL = TYPICAL 로 유지 하자

4. 자동 통계정보 수집 대상은 User Objects,  Sys/System Object 가 대상이다.

  
[ 요약 ]

대상	설명	TATISTICS_LEVEL=BASIC	STATISTICS_LEVEL=TYPICAL
System Statistics	System 성능 ( CPU, DISK )
Fixed Objects Statistics	DBMS 성능 ( X$, V$)
Dictionary Statistics	SYS/SYSTEM USER TABLE	자동 수집되지 않는다.	자동 수집된다.
User Table Statistics	일반 유저 TABLE	자동 수집되지 않는다	자동 수집된다.

 

통계정보의 종류
: 크게 4가지로 구분할 수 있다.
[ 1.System Statistics ]
: 개요 - System Statistics 는 System Hardware 의 I/O, CPU 의 특성을 분석하여
         Optimizer 가 CPU Costing 을 계산할 때 사용하는 정보로써,
         이를 기반으로 Optimizer 가 임의의 SQL에 대한 실행 계획을 수립할 때
         이를 기반으로 계산하게 된다.
  수행주기 - 초기 1회
             시스템 자원의 변경이 생겼을 경우 ( Memory, CPU, I/O 등 )
  9i - 처음 소개된 개념이고, DBA가 수동으로 수집 하지 않으면 기본적으로
       존재하지 않는 정보이다. 기본적으로 I/O Model로 비용산정
       System Statistics 정보가 있으면 Optimizer 가 비용 산정을 CPU Model 로 하고,
       System Statistics 정보가 없다면 Optimizer 가 비용 산정을 I/O Model 로 한다.
  10G - System Statistics 정보를 수집 하지 않는다면 Noworkload System Statistics 가
       사용된다. 10G 에서는 Optimizer 가 비용 산정을 CPU Model 로 한다. [ Default ]
       [ CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 로 구성된다. ]
       수동으로 System Statistics 수집 시에는 Workload System Statistics 라고 한다.
       결론적으로 운영 시스템의 최적의 System Statistics 를 생성하여, 적절히
       적용 하여 사용하는 것이 최선이나, 보통은 NoWorkload System Statistics 를
       그대로 사용한다.
 주의사항 : RAC 에서 NODE 가 서로 같은 시스템 사양을 같지 않을 경우에는
       System Statistics 를 Node 별로 나누어 관리 되지 않으므로 전체 System 의
       대표성을 가지는 Node 에서 수행을 한다.
       특히 위와 같은 결정을 하기 위해서는, 각각의 Node 별로 통계치를 생성해
       보고 비교해 본 후에 결정할 수 있다.
       노드의 사양이 동일한 경우 가장 일반적인 Node 에서 수행한다.  

실습
: 시스템 통계정보는 Optimizer 가 실행 계획 세움에 있어서 지대한 영향을
  미치므로, 항상 기존의 시스템 통계자료를 백업 후 진행 하자.
  OLTP 와 OLAP 성 통계정보를 생성하고, IMPORT 해보자

 
-- 1. 시스템 통계정보 확인
SELECT * FROM SYS.AUX_STATS$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33
SYSSTATS_INFO                  FLAGS                        0
SYSSTATS_MAIN                  CPUSPEEDNW                1489
SYSSTATS_MAIN                  IOSEEKTIM                   10
SYSSTATS_MAIN                  IOTFRSPEED                4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

-- 2. 기존 통계정보 백업 받을 테이블 생성
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- 기존 통계정보 백업
SQL> execute DBMS_STATS.EXPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS
PL/SQL procedure successfully completed.
-- 백업 받은 시스템 통계정보 데이타 확인
SQL> SELECT STATID, C1, C2, C3 FROM ORIGIN ;
--  C1 = COMPLETED     --> 수집 완료

-- 3. 시스템 통계정보 수집
-- 사전에 JOB PROCESS 확인 ( 1보다 커야 한다.)
SQL> show parameters job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL> alter system set job_queue_processes = 5 ;
System altered.

SQL> show parameters job_queue
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     5

 

-- 4.  OLTP 성 시스템 통계정보 수집하기
-- OTLP용 시스템 통계정보 생성을 위한 통계정보 테이블 생성
-- OWNER, TABLE 이름, TABLESPACE 이름 순
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLTP’,’USERS’);
PL/SQL procedure successfully completed.
--  지금 부터 INTERVAL 로 지정된 시간 동안 시스템 통계정보를 생성 하라..
--  2분동안 시스템 통계정보를 수집 하라
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLTP’, STATID => ’OLTP’);
PL/SQL procedure successfully completed.
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP                 AUTOGATHERING                  02-24-2009 08:54               02-24-2009 08:56
OLTP
--  C1 = AUTOGATHERING --> 수집 중
--  C1 = COMPLETED     --> 수집 완료 로 변경된다. [ 2분 경과 후 ]

--  10분동안 시스템 통계정보를 수집 하라 [앞서 수행한 2분동안 통계정보는 Update 된다.  ]
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID => ’OLTP’);
-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLTP                 AUTOGATHERING                  02-24-2009 08:56               02-24-2009 08:58
OLTP
--  C1 = AUTOGATHERING --> 수집 중

-- 시스템 통계정보 수집 중지 하기 [ gathering_mode=>’STOP’ ]
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’STOP’,INTERVAL=> 10, STATTAB => ’OLTP’, STATID => ’OLTP’);

-- 시스템 통계정보 수집 확인
SQL> select STATID, C1, C2, C3 from OLTP;
--  C1 = AUTOGATHERING --> 수집 중
--  C1 = COMPLETED     --> 수집 완료 [ 강제로 중지 하여도 COMPLETED 로 나온다. ]

 

--  5. OLAP 성 시스템 통계정보 수집하기
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’OLAP’,’USERS’);
PL/SQL procedure successfully completed.
execute DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE =>’INTERVAL’,INTERVAL=> 2, STATTAB => ’OLAP’, STATID => ’OLAP’);
SQL> SELECT STATID, C1, C2, C3 FROM OLAP ;

STATID               C1                             C2                             C3
-------------------- ------------------------------ ------------------------------ ------------------------------
OLAP                 COMPLETED                      02-24-2009 09:04               02-24-2009 09:06
OLAP
-- C1 => AUTOGATHERING -- 수집 중
-- C1 => COMPLETED     -- 수집 완료 로 변경된다.  [ 2분 경과 후 ]

 

-- 6. 생성한 OLAP_STATS 시스템 통계정보 IMPORT 하기
-- 기존 통계정보 삭제
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- 기존 통계정보를 삭제 하면,
-- SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 값만 DELETE 시점으로 변경된다.
SQL> select  * from sys.aux_stats$;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-24-2009 09:08
SYSSTATS_INFO                  DSTOP                          02-24-2009 09:08

-- OLTP 시스템 통계정보 IMPORT 하기
execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’OLTP’, STATID =>’OLTP’,STATOWN =>’SYS’);
-- Import 된 시스템 통계정보 확인
-- OLTP 시스템 통계정보를 수행한 시간으로 SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP
-- 값이 변경되어 진다.
SQL> select * from sys.aux_stats$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-24-2009 08:56
SYSSTATS_INFO                  DSTOP                          02-24-2009 08:58

-- 7. 다시 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$ ;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33
--  SYSSTATS_INFO.DSTART 와 SYSSTATS_INFO.DSTOP 로 ORIGIN 으로 변경됨

-- 8. 수동으로 시스템 통계정보 생성하기 [ 파라미터 개별 설정 ]
-- 기존 시스템 통계정보 삭제 하기
execute DBMS_STATS.DELETE_SYSTEM_STATS ;
-- CPUSPEED 설정
-- CPUSPEED : Wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’CPUSPEED’, PVALUE=>400);
PL/SQL procedure successfully completed.
-- CPUSPEED 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’CPUSPEED’;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN                  CPUSPEED                   400
-- SREADTIM 설정
-- SREADTIM : wait time to read single block, in milliseconds
SQL> execute DBMS_STATS.SET_SYSTEM_STATS(PNAME =>’SREADTIM’, PVALUE=>100);
PL/SQL procedure successfully completed.
-- SREADTIM 값 변경 확인
SQL> select * from sys.aux_stats$ WHERE PNAME =’SREADTIM’;
SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_MAIN                  SREADTIM                   100

 

-- 9. 원복 하기
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab =>’ORIGIN’, STATID =>’ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 원복 결과 확인
SQL> select * from sys.aux_stats$ ;

SNAME                          PNAME                    PVAL1 PVAL2
------------------------------ -------------------- --------- ------------------------------
SYSSTATS_INFO                  STATUS                         COMPLETED
SYSSTATS_INFO                  DSTART                         02-13-2009 13:33
SYSSTATS_INFO                  DSTOP                          02-13-2009 13:33

 

[ 2.Fixed Objects Statistics ]
  개요 -  Dynamic Performance View와 같이 fixed view(x$ tables)에 대한 통계치는
          Manual 한 Gathering 이 필요하다. 이 Fixed Objects Statistics 는
          Database 의 Activity 를 기록하게 되므로 database 가 일반적인 운영 상태
          일때 gathering  하여야 한다.
          일반적으로 Fixed Object Statistics 통계치는 V$ view 를 조회하는
          사용자 Query에 필요하다.
  수행주기 - 초기 1회
             추가적인 Application 이나 변경으로 동시 사용자 등의 변경 발생시
  주의사항 - 자동화 대상이 아니다. [ 자동으로 Fixed Object의 통계정보가 생성되지 않는다. ]
             RAC 에서는 아직 Fixed Objects Statistics 를 Instance 별로 구분하지
             않기 때문에, 가장 부하가 많은 Node(Instance)에서 통계치를 조사한다.

 
실습
: Fixed Object 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
  항상 기존의 Fixed Object 통계정보를 백업 후 진행 하자.
  OLTP 와 OLAP 성 Fixed Object 통계정보를 생성하고, IMPORT 해보자

 

-- 1. 기존의 Fixed Object 통계정보 백업 테이블 생성   
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_ORIGIN’,’USERS’);           
-- EXPORT_FIXED_OBJECTS_STATS 를 통한 백업 수행
SQL> execute DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’,STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

 

-- 2. 신규로 Fixed Object 통계정보 테이블 생성  
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’FIX_NEW’,’USERS’); 
-- 신규 Fixed Object 통계정보 수집
SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’);
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’SYS’,’FIX_NEW’,’SYS’); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 13578
ORA-06512: at "SYS.DBMS_STATS", line 13892
ORA-06512: at "SYS.DBMS_STATS", line 14420
ORA-06512: at line 1

SQL> execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(’FIX_NEW’);
PL/SQL procedure successfully completed.

 

-- 3. 기존 Fixed Object 통계정보 확인 하기
SQL>  select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA                       
X$KQFVI                       
X$KQFVT                       
X$KQFDT                       

-- 4. 신규 Fixed Object 통계정보 IMPORT
--  FIXED TABLE 의 통계정보 삭제
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_NEW’, STATID =>’FIX_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 5. 신규 Fixed Object 통계정보 확인 하기
SQL>  select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ --------------------------
X$KQFTA                        20090224 09:50:34 09:50:34
X$KQFVI                        20090224 09:50:34 09:50:34
X$KQFVT                        20090224 09:50:34 09:50:34
X$KQFDT                        20090224 09:50:34 09:50:34
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.

 

6. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab =>’FIX_ORIGIN’, STATID =>’FIX_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 복원된 정보 확인 하기
SQL > select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from dba_tab_statistics
      where table_name like ’X$%’ ;

 
[ 3.Dictionary Statistics ]
  개요 - DBMS_STATS.GATHER_DICTIONARY_STATS 를 이용하여 Sys, System Schema 의
        Object를 Gathering 한다. 이 procedure 는 또한 DRSYS 나 CTX user Schema의
        Object 도 함께 Gathering 한다.
  수행주기 - 초기 1회
             Database Object(사용자 Table, PL/SQl, User생성) 의 변경이 있는 경우
            
실습
: Dictionary 통계정보는 DBMS 성능에 지대한 영향을 미치므로,
  항상 기존의 Dictionary 통계정보를 백업 후 진행 하자.
  OLTP 와 OLAP 성 Dictionary 통계정보를 생성하고, IMPORT 해보자

 

-- 1. 기존의 Dictionary 통계정보 통계정보 백업 테이블 생성   
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_ORIGIN’,’USERS’);
PL/SQL procedure successfully completed.
-- EXPORT_DICTIONARY_STATS 를 통한 백업 수행
SQL>  execute DBMS_STATS.EXPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’,STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 2. 신규로 Dictionary 통계정보 테이블 생성  
SQL> execute DBMS_STATS.CREATE_STAT_TABLE(’SYS’,’SYS_NEW’,’USERS’); 
PL/SQL procedure successfully completed.
-- 신규 Dictionary 통계정보 수집
SQL> execute DBMS_STATS.GATHER_DICTIONARY_STATS(’SYS_NEW’);
PL/SQL procedure successfully completed.

 

3. 신규 Dictionary 통계정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2
....
X$LOGMNR_OBJ$                  20090224 10:07:20 10:07:20
X$LOGMNR_TABCOMPART$           20090224 10:07:20 10:07:20
X$LOGMNR_USER$                 20090224 10:07:20 10:07:20
SUMDELTA$
SDO_TOPO_DATA$
...
일부는 last_analyzed 가 Update 되지 않는다.
Note that the database can decide not to collect stats for objects
that were either never used or are totally volatile.

4. Dictionary 통계정보 IMPORT 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.
-- 신규 Fixed Object 통계정보 Import
SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_NEW’, STATID =>’SYS_NEW’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- IMPORT 된  Dictionary 통계정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2;
SRS$                           20081229 22:00:05 22:00:05
X$LOGMNR_ATTRIBUTE$            20090224 10:07:19 10:07:19
X$LOGMNR_COLTYPE$              20090224 10:07:19 10:07:19
X$LOGMNR_IND$                  20090224 10:07:19 10:07:19
X$LOGMNR_COL$                  20090224 10:07:19 10:07:19
X$LOGMNR_DICT$                 20090224 10:07:19 10:07:19
==> 테스트 에서는 실제로 Dictionary 통계정보를 가진 Table Count 가 오히려 줄어들었다.
      
5. Fixed Object 통계정보 원복 하기
SQL> execute DBMS_STATS.DELETE_DICTIONARY_STATS ;
PL/SQL procedure successfully completed.

SQL> execute DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab =>’SYS_ORIGIN’, STATID =>’SYS_ORIGIN’,STATOWN =>’SYS’);
PL/SQL procedure successfully completed.

-- 복원된 정보 확인 하기
SQL>   select table_name, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
       from dba_tab_statistics
       where table_name like ’%$’
       order by 2;

 

[ 4.User Table Statistics ]
: 사용자의 Object 에 대한 통계치 수집은 10G 에서 기본적인 GATHER_STATS_JOB 을
이용한다. 이 JOB 은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가
변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행한다.
또한 분석하는 순서 역시 우선순위 순으로 수행한다.
만약 Object 에 많은 Data 가 Load 되거나 변경이 있는 경우에는  Manual 하게
수행한다.
- 10G 에서 말하는 자동 통계정보 수집 기능(Automatic Statistics) 의 대상은
  1. AWR(Automactic Workload Repository)
   - 분석, Self-Tuing 및 일반적인 Tuning 을 목적으로 하는 자료로
     특정 시간 동안 데이타 베이스에서 발생한 여러 가지 상황 정보를 이른다.
     Wait Events, Latces, Enqueues, Cpu Consumption, SGA Compoenent, PGA 등에
     대한 자료 이다.
      ’STATISTICS_LEVEL’ 에 의해서 수집되는 자료의 LEVEL 이 결정된다.
     AWR 정보는 디폴트로 7일간 보관한다. [ SYSAUX TABLESPACE 에 ]
    
  2. CBO(Cost-Based Optimizer)
   - Database 의 Object 즉,  Application 및 Oracle Internal (Sys/System) 유저의
     Table, Index 에 대한 통계정보 수집
     이 수집된 정보는 Optimizer 가 수행계획 수립 시에 의해 사용된다.
     GATHER_STATS_JOB 에 의해서 수집 된다. [DB 생성시 자동 생성됨]
     Optimizer historical 통계정보는 디폴트로 31일간 보관한다.
     STATISTICS_LEVEL=BASIC 이면 CBO 통계정보가 수집되지 않는다.
        - Automatic Optimizer Statistics Collection
        - Object level Statistics  
     [ SYSAUX TABLESPACE 에 ]  
    
-- SYSAUX 사용 현황 파악 하기
 SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;
 1. SM/AWR 
  - AWR 정보 수집
  - AWR 정보 수집 옵션 확인
    SQL>  SELECT DBID, RETENTION, TOPNSQL  FROM DBA_HIST_WR_CONTROL ;
          DBID RETENTION                      TOPNSQL
    ---------- ------------------------------ ----------
    2466823093 +00007 00:00:00.0              DEFAULT
    보존 변경은 dbms_workload_repository.modify_snapshot_settings 를 통해서 가능
   
    -- AWR 정보 한달 보관주기로 변경 [ 60*24*31 = 44640 분 ]
    SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 44640 );
    PL/SQL procedure successfully completed.
    -- 변경되 AWR 정보 확인
    SQL> SELECT DBID, RETENTION, TOPNSQL  FROM DBA_HIST_WR_CONTROL ;
   
          DBID RETENTION                      TOPNSQL
    ---------- ------------------------------ ----------
    2466823093 +00031 00:00:00.0              DEFAULT

 2. SM/ADVISOR
    SQL Tuning Advisor, SQL Access Advisor, ADDM 이 사용하는 정보 저장소

 3. SM/OPTSTAT    
    - 구버전(Old) Optimizer 통계정보 저장소
    - SM/OPTSTAT 저장 기간 확인
      SQL> select dbms_stats.get_stats_history_retention from dual;
      GET_STATS_HISTORY_RETENTION
      ---------------------------
                               31
      ==> Default 로 31일
    -- 10일로 조절
    SQL> exec dbms_stats.alter_stats_history_retention(10);
    PL/SQL procedure successfully completed.

    SQL> select dbms_stats.get_stats_history_retention from dual;
    GET_STATS_HISTORY_RETENTION
    ---------------------------
                         10
    -- 원복
    SQL> exec dbms_stats.alter_stats_history_retention(31);
    PL/SQL procedure successfully completed.

  4. SM/OTHER
     - Alert History 등의 저장소

--  STATISTICS_LEVEL PARAMETER
    : Database 와 OS 의 통계정보의 수집 Level 을 제어 하는 파라미터
    1.Typical
      - Default, 일반적인 환경에 가장 적합
    2. ALL
      - typical + Timed OS Statistics + Plan Execution Statistics
    3. BASIC
      - 아래 기능을에 필요한 중요한 통계정보를 수집 할 수 없다.
        - AWR
        - ADDM
        - All Server-Generated Alerts
        - Automatic SGA Memory Management
        - Automatic Optimizer Statistics Collection
        - Object level Statistics
        등...
           
 
실습 : 여기서 말하는 자동 통계수집 이란 CBO 에 대한 것을 이른다.
     - 1. 자동 통계수집일정을 확인하고
     - 2. 자동 통계수집을 Disable 해보자
     - 3. 통계정보 백업 / 복구 하기 
     - 4. 특정 테이블 통계수집 중지 하기
 
-- 1.1 자동통계정보 수집 확인 하기
SQL >  select job_name, job_type, program_name, schedule_name, job_class
      from dba_scheduler_jobs
       where job_name =’GATHER_STATS_JOB’;
JOB_NAME             JOB_TYPE         PROGRAM_NAME         SCHEDULE_NAME                  JOB_CLASS
-------------------- ---------------- -------------------- ------------------------------ ------------------------------
GATHER_STATS_JOB                      GATHER_STATS_PROG    MAINTENANCE_WINDOW_GROUP       AUTO_TASKS_JOB_CLASS

-- 1.2 자동통계정보 수집 시 실행 되는 Program 확인
SQL> select program_Action from dba_scheduler_programs where program_name =’GATHER_STATS_PROG’;

PROGRAM_ACTION
--------------------------------------------------
dbms_stats.gather_database_stats_job_proc

--  1.3 자동통계정보 수집 시 스케줄 확인
SQL> select * from dba_scheduler_wingroup_members where window_group_name =’MAINTENANCE_WINDOW_GROUP’;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

 

--  1.4 자동통계정보 수집 시 스케줄 상세 확인
SQL> select window_name, repeat_interval, duration
     from dba_scheduler_windows
     where window_name in (’WEEKNIGHT_WINDOW’,’WEEKEND_WINDOW’);

WINDOW_NAME          REPEAT_INTERVAL                                                                  DURATION
-------------------- -------------------------------------------------------------------------------- --------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0            +000 08:00:00
WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                              +002 00:00:00

-- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행 된다.
-- 토요일 0시에 수행되어 이틀 동안 수행된다.

 

-- 2.1 자동통계정보 수집 중지
-- STATISTICS_LEVEL=BASIC 이면 자동통계정보 수집(CBO)
SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     SCHEDULED

SQL> exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     DISABLED

 

-- 2.2 자동통계정보 수집 재설정
SQL> exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL> select job_name, state from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;

JOB_NAME             STATE
-------------------- ---------------
GATHER_STATS_JOB     SCHEDULED

 

-- 3.1 통계정보 백업 / 복구 하기
-- 유저 테이블 통계정보 백업 받을 테이블 생성하기
SQL> execute dbms_stats.create_stat_table(’SYS’,’USER_STATS’,’USERS’);
PL/SQL procedure successfully completed.
-- SCOTT 유저 테이블 통계정보 백업 받기
SQL> execute dbms_stats.export_schema_stats(’SCOTT’,’USER_STATS’,’SCOTT’,’SYS’);
PL/SQL procedure successfully completed.
-- 백업된 SCOTT 유저의 통계정보 확인
SQL> select STATID, C1, C2, C4, D1 from  USER_STATS ;

-- 3.2 신규로 유저 테이블 통계정보 생성
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
     GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
     CASCADE => TRUE );
-- 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

 

-- 3.3 유저 테이블 통계정보 원복하기
SQL> exec dbms_stats.delete_schema_stats(’SCOTT’);
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.import_schema_stats(’SCOTT’,’USER_STATS’,’USER_STATS’,’SYS’);
PL/SQL procedure successfully completed.
 
-- 3.4 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

 

-- 4.1 특정 테이블 통계수집 중지 하기
-- 수동으로 통계정보 수집 하여 LAST_ANALYZED  Update 하기
SQL>  execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
     GRANULARITY => ’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
      CASCADE => TRUE );
PL/SQL procedure successfully completed.
-- 4.2 신규로 유저 테이블 통계정보 확인
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
from  dba_tab_statistics
WHERE OWNER=’SCOTT’
ORDER BY LAST_ANALYZED DESC ;  
-- 날짜에 주목하자

-- 특정 테이블 통계정보 수집 막기
SQL> execute dbms_stats.lock_table_stats(’SCOTT’,’T1’);
PL/SQL procedure successfully completed.

-- 특정 테이블 통계정보 수집 막음 확인
SQL> SELECT owner, table_name, stattype_locked
     FROM dba_tab_statistics
     WHERE OWNER=’SCOTT’
     and stattype_locked is not null;
    
OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SCOTT                          T1                             ALL

-- 확인을 위해서 수동으로 통계정보 수집 하여 LAST_ANALYZED  Update 하기
SQL> execute dbms_stats.gather_schema_stats(ownname=>’SCOTT’, ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE,-
> GRANULARITY =>’AUTO’, DEGREE => null, METHOD_OPT => ’FOR ALL COLUMNS SIZE 1’, -
> CASCADE => TRUE );
PL/SQL procedure successfully completed.

SQL>  select OWNER,TABLE_NAME, to_char(last_analyzed, ’yyyymmdd hh24:mi:ss hh24:mi:ss’)
      from  dba_tab_statistics
      WHERE OWNER=’SCOTT’
     and table_name in (’T1’,’EMP’,’DEPT’);

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,’YYY
------------------------------ ------------------------------ --------------------------
SCOTT                          DEPT                           20090224 11:37:57 11:37:57
SCOTT                          EMP                            20090224 11:37:57 11:37:57
SCOTT                          T1                             20090224 11:32:53 11:32:53

==> Lock 되어진 T1 에 대해서는 테이블 통계정보가 생성되지 않았음을 확인

통계청에서 제공하는 사회지표를 보니 재미있는 내용이 많은거 같다.

- 외국인과의 혼인비율이 11.1%나 차지한다. 2005년에는 13.6%까지 올라갔었다.

- 평균 초혼연령은 남성 31.1세, 여성 28.1세로 계속 증가추세

- 사망률 순위 : 자살로 인한 사망률이 1997년에 비해 거의 1.7배정도 는데 비해 운수사고로 인한 사망률이 반으로 떨어졌다. 사망원인 순위는 암-> 뇌혈관 질환 -> 심장질환 -> 자살 -> 당뇨병 순

 - 한국영화 관객점유율은 2006년 63.8을 피크로 감소추세. 2008년에는 42.1%

 - 범죄발생건수는 2008년 196만 6천건으로 전년에 비해 7.5%증가(절도, 상해의 증가가 큼)

http://wiki.oracleclub.com/pages/viewpage.action?pageId=4325453

*  DBMS_XPLAN

1) dbms_xplan.display
 - explain plan for    select * from t1;

 -  select * from table(dbms_xplan.display('plan_table', null, 'typical', null ));   
          -- 디폴트(dbms_xplan.display) 와 동일한 결과
 -  select * from table(dbms_xplan.display('plan_table', null, 'all'));  
          -- Query Block 정보, 추출하는 컬럼정보, /*+ qb_name(x) */ 힌트로 query block 명 조작가능
 -  select * from table(dbms_xplan.display('plan_table', null, 'outline'));   -- 필요한(사용된) 힌트를 나열
 -  select * from table(dbms_xplan.display('plan_table', null, 'advanced'));   -- all, outline 정보 같이 보여줌

2) dbms_xplan.display_cursor

 - 통계정보 생성(t1테이블)
    exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, no_invalidate=>false);

   : 인덱스가 있는 컬럼에 대해 histogram 생성하면서 통계생성
    exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all indexed columns size skewonly');

 - gather_plan_statistics 힌트 : 쿼리 수행시 예측 Row수와 실제 Row수를 기록한다.
  select /*+ gather _plan_statistics */ count(*) from t1 where c1 = 'Many2';

  - select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));   
     -- 현재 세션에 대한 최근 쿼리 결과조회 ( SQL ID, Child number, 옵션)
 
  - 테이블 정보조회
     select     a.table_name, a.num_rows, a.blocks, a.sample_size, a.last_analyzed,
       s.column_name, s.num_distinct, s.num_nulls, s.density, s.low_value, s.high_value, s.histogram
     from user_tab_statistics a, user_tab_cols s
     where a.table_name = 'T1'
     and a.table_name = s.table_name;

     select * from user_histograms
     where table_name = 'T1'
     order by column_name, endpoint_value;


* Index and CBO
 1) Clustering Factor
    - Index Scan의 Cost 계산
      Cost = Blevel + Leaf Blocks * index Selectivity + Clustering Factor * Table Selectivity + Adjusted CPU

   - index 생성시 sequence key 값을 사용하거나, 시간순으로 date형을 사용하면 clustering factor가 좋게(낮게) 나온다. clustering factor를 개선시키려면 테이블을 다시 생성(order by 로 reinsert)해야 함.

oracle 에 global temporary table이라는게 있다.
PLAN TABLE같은데 적용해놓으면 편할 듯 하다.

CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  OTHER_XML          CLOB
)
ON COMMIT PRESERVE ROWS
NOCACHE;

GRANT DELETE, INSERT, SELECT, UPDATE ON  PLAN_TABLE TO PUBLIC;


Oracle8.1에서는 session내에서 임시로 사용할 data들을 영구적인 segment형태가
아닌 temporary structure에서 관리할 수 있다.
이러한 Temporary Table들은 그 생성문장에 의해서 definition이 dictionary에
저장되고, 각 session에서 해당 table을 사용할 때마다 definition을 이용하여
memory에 table 구조를 생성하게 된다.


CHARACTERISTICS
---------------
1. data는 session private하다. (특정 session에서 사용하는 temporary table
data는 다른 session에서 access할 수 없다.)
2. CREATE GLOBAL TEMPORARY TABLE ...
ON COMMIT [DELETE|PRESERVE] ROWS ; 문을 이용하여 생성한다.
3. data의 유지기간은 transaction단위 또는 session단위이다.
'ON COMMIT DELETE ROWS'로 생성되었다면 transaction단위이며 이때 data는
commit이 되는 시점에 자동으로 제거된다.
'ON COMMIT PRESERVE ROWS'로 생성되었다면 session단위이며 이때 data는
해당 session이 종료되면서 사라진다.
default는 'ON COMMIT DELETE ROWS'이다.
4. table의 definition은 dictionary에 permanently 저장된다.
*_tables의 TEMPORARY, DURATION column이 temporary table과 관련이 있다.
TEMPORARY - 'Y' : temporary type table
'N' : permanent type table
DURATION - 'SYS$SESSION' : data의 유지기간이 session단위
'SYS$TRANSACTION' : data의 유지기간이 transaction단위
NULL : 해당 table은 temporary type이 아님
5. session간에 data에 대한 contention이 발생되지 않기 때문에 DML문에 대한
lock이 필요하지 않다.
6. 임시적으로만 관리되는 data이므로 DML문에 대해서 redo log를 발생시키지
않는다.
7. index, view, trigger를 생성하여 사용할 수 있다. Temporary Table의
column에 생성되는 index도 temporary type이다.
8. table의 definition은 export utility를 이용하여 export할 수 있다.
그러나 그 row들은 export의 대상이 될 수 없다.


RESTRICTIONS
------------
1. partitioned, index-organized, clustered table로 생성할 수 없다.
2. foreign key constraint를 설정할 수 없다.
3. nested table이나 varray type의 column은 포함할 수 없다.
4. 다음과 같은 LOB_storage_clause들은 지정할 수 없다. : TABLESPACE,
storage_clause, LOGGING 또는 NOLOGGING, MONITORING 또는 NOMONITORING,
또는 LOB_index_clause.
5. parallel DML이나 parallel query는 지원되지 않는다. (parallel hint는
무시될 것이며, table생성시 parallel clause를 지정하면 error를
return한다.)
6. storage나 tablespace는 지정할 수 없다.
7. 분산 transaction은 지원되지 않는다.


SAMPLE
------

----------------------------------------------------------------------
-- temporary type table을 생성하되 duration은 transaction단위로 한다.
----------------------------------------------------------------------
SQL> create global temporary table temp_tab
2 (col1 number, col2 char(10))
3 on commit delete rows ;


-----------------------------------------------------------------------
-- 생성된 table에 대한 정보를 조회한다. *_tables의 TEMPORARY, DURATION
-- column이 temporary type table과 관련이 있다.
-----------------------------------------------------------------------
SQL> select temporary, duration
2 from user_tables
3 where table_name = 'TEMP_TAB' ;

T DURATION
- ---------------
Y SYS$TRANSACTION


-----------------------------------------------------------------------
-- temporary table에 row insert
-----------------------------------------------------------------------
SQL> insert into temp_tab values (1, 'wookpark') ;

SQL> select * from temp_tab ;

COL1 COL2
--------- ----------
1 wookpark


-----------------------------------------------------------------------
-- transction을 commit한다.
-----------------------------------------------------------------------
SQL> commit ;
Commit complete.


-----------------------------------------------------------------------
-- duration이 transaction단위이기 때문에 commit을 수행하면 모든 data가
-- 사라진다.
-----------------------------------------------------------------------
SQL> select * from temp_tab ;
no rows selected

from oracle

 ppt형식으로 되어있는데, 생각보다 내용설명이 간결하면서 이해하기 쉽게 되어있다.

 - RAC환경에서 주로 발생하는 gc cr/current 류의 wait event 는 주로 Global Cache 동기화(Cache Fusion)와 관련하여 발생
   gc 는 read시, current는 변경시 발생


 - Sequence 의 nocache : nextval 호출시마다 Dictionary 변경필요함.
   cf) RAC의 경우 cache를 주면 cache를 소진할 동안은 상호 통신이 필요없다. 단 ordered 속성을 주면 SV락을 통한 동기화가 필요하다. 특별한 필요가 없으면 cache와 noorder 속성으로 만들자.

 - db file scattered read 이벤트관련(full scan)
   : Insert, Delete 가 빈번한 interface성, temp성 테이블의 경우 delete 이후 통계정보가 생성되었다면 full scan이 발생할 수 있다.(주의)

 - latch: cache buffers chains 이벤트
  : 주로 hot block이나 bad SQL에 의해 동일 블록에 대한 접근이 동시에 일어날때 발생
   cf) Bad SQL의 문제일 경우 parallel query 로 변경하는것도 검토필요하다. parallel query의 경우 SGA를 거치지 않기 때문에 버퍼캐쉬 경합 자체가 없다.(parallel query실행시 더티버퍼를 디스크에 쓰도록 Segement 체크포인트가 수행됨으로 성능문제 고려)

 - 오라클의 physical I/O
   : OS상에서의 캐쉬, 스토리지 캐쉬가 있기 때문에 모두가 디스크 I/O 라고 보기는 힘들다.

 -  Keep 버퍼풀 사용하기
     1) DB_KEEP_CACHE_SIZE = 352321536 또는 alter system set DB_KEEP_CACHE_SIZE = 300M;
     2) 인덱스를 keep 버퍼에 올리기
         alter index schema.index_name storage(buffer_pool keep);

  - 다이렉트 로그 인서트 ( insert /*+ append */ into t1 select * from t2)
    : high water 마크 이후부터 insert 작업을 함에 따라 테이블에 X모드의 TM락을 획득하고 작업함.
      (insert, update, delete 동시 실행 못함)

        



 

카네기 멜론의 랜디 포시 교수의 마지막 강의..

- 아이들의 상상력, 자신감, 성취감을 위해 방안을 마음대로 그리게 하고, 자신의 새차 뒷자석에 콜라를 쏟어버리는 그의 행동
- 어릴적 꿈을 향해 끊임 없이 노력하고, 다른 사람들에게 베풀고자 함

- 헤드 페이크 :  쉬는 시간에 물을 향해 뛰는 아이들을 마구 야단치던 축구코치, 힘들때마다 생각하며 노력
     과정에 푹 빠져들때까지 배우는 사람으로 하여금 자신이 진정 배우는 것이 무엇인지 모르게 하는 속임수

- 장벽은 나를 막기 위해 있는 것이 아니라, 나보다 그것을 덜 갈망하는 사람들을 막기위해 있는 것이라고..

사이즈 추정하기

작성: 김도근

물리 모델 단계에서 로우 사이즈 예측하기가 힘들때가 많다. variable 사이즈를 많이 쓴다면 그에 대한 최대값과 최소값의 편차가 심할 가능성이 크다. 예전에 자료를 만든 'Oracle 9i 의 물리 설계' 를 토대로 예측을 해보자.

 

데이터 형	고정/가변	데이터 저장 시의 길이
CHAR	고정/가변	·         바이트수 지정시는 테이블이 정의한 길이의 고정 길이 ·         문자 수지정시는 문자 수에 대해서 고정이지만 실점유 바이트 수는 캐릭터 셋 및 실제의 데이터에 의해 다르다. 예를 들면KO16KSC5601의 DB로 「char(6 char)」의 컬럼에 「AAA」라고하는 데이터를 넣으면 3바이트 소비되지만 「하하하」라고 하는 2바이트 한글 데이터를 넣으면 6바이트를 소모한다.
VARCHAR2	가변	실제로 저장되고 있는 데이터의 길이(바이트 수 지정시  문자수지정시 )
NCHAR	고정	테이블 정의 지정 문자수의 두배(AL32UTF8 지정시)
NVARCHAR2	가변	저장 문자수의 2배(AL32UTF8 지정시)
NUMBER	가변	길이 = 1 + CEIL (n / 2) ·         CEIL는 올림 값 (예: ceil(1.2) = 2 ) ·         n는 저장된 수치의 정수부 및 소수부를 합한 총자리수. n>38의 경우에는38 이다 ·         유효 자리수 38자리수 미만의 음수의 경우는 1바이트 더한다.
DATE	고정	7 바이트
TIMESTAMP	가변	초의 소수부분에 데이터가 있는 경우：11바이트 고정 초의 소수부분에 데이터가 없는 경우：7바이트 고정
TIMESTAMP WITH TIME ZONE	고정	13바이트
TIMESTAMP WITH LOCAL TIME ZONE	고정	11바이트
INTERVAL YEAR TO MONTH	고정	5바이트
INTERVAL DAY TO SECOND	고정	11바이트
RAW	가변	실제로 저장되어 있는 데이터의 길이
LONG	가변	실제로 저장되어 있는 데이터의 길이
LONG RAW	가변	실제로 저장되어 있는 데이터의 길이
BLOB/CLOB/NCLOB	지정에 따라	DISABLE IN ROW 지정시：20바이트 ENABLE IN ROW 지정해서 저장시：실 데이터 길이+36바이트 ENABLE IN ROW지정해서 행 이외(LOB 테이블)에 저장시：실 데이터 길이에 따라 36～86 바이트. 저장시에는 여유 있게 86 바이트로 고정해서 계산한다. LOB테이블의 산출에 대해서는 뒷부분을 참조. EMPTY의 경우는 데이터 길이의 부분은 0으로 계산
BFILE	고정	530 바이트
ROWID	고정	6 바이트(～Oracle7), 10바이트(Oracle8～)

위의 수치를 토대로 예측단계로 넘어가보자. 일반적으로 varchar 타입의 경우에는 약 70% 정도의 값이 들어간다고 가정한다.

 

SQL> desc emp

 

 컬럼명        NULL유무      데이터타입

 ----------------------------------------- -------- ------------------------

 EMPNO        NOT NULL       NUMBER(4)

 ENAME                       VARCHAR2(10)

 JOB                         VARCHAR2(9)

 MGR                         NUMBER(4)

 HIREDATE                    DATE

 SAL                         NUMBER(7,2)

 COMM                        NUMBER(7,2)

 DEPTNO                      NUMBER(2)

 

 

평균 레코드길이를 구하자