출처 : http://jhroom.co.kr/12050

통계 정보 생성 Procedure GATHER_TABLE_STATS

[  참고 ]
 1. 메뉴얼 [ GATHER_TABLE_STATS ] 
 http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
 2. OPTIMIZER INVALIDATION PERIOD
 http://wiki.ex-em.com/index.php/OPTIMIZER_INVALIDATION_PERIOD

A.GATHER_TABLE_STATS Procedure

1. 개요

: Table,Column,  그리고 index 에 대한 통계 정보를 수집 하게 하는Procedure

2. Syntax

:DBMS_STATS.GATHER_TABLE_STATS (<?xml:namespace prefix = o /><?xml:namespace prefix = o />

   Ownname VARCHAR2,

   Tabname  VARCHAR2,

   Partname  VARCHAR2 DEFAULT NULL,

   Estimate_percent NUMBER DEFAULTto_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 DEFAULTNULL,

   no_invalidate   BOOLEAN DEFAULT

to_no_invalidate_type(get_param(‘NO_INVALIDATE’)),

   force           BOOLEAN DEFAULT FALSE );

3. Parameter 설명

: dbms_stats.set_param 에 의해서 디폴트 파라미터 설정 변경이 가능하다.

  [ 가능한 값은 
   CASCADE,  DEGREE,  ESTIMATE_PERCENT,  METHOD_OPT,  NO_INVALIDATE,   GRANULARITY,

   ==> 이상은 수동 통계정보 생성 시에 저정을 하지 않았을 때 적용되는 Default 값에 영향을 미치고

   AUTOSTATS_TARGET [ AUTO - Oracle이 자동으로 대상 Object 결정, 
                                      ALL - 대상 시스템의 모든 Objects
                                      ORACLE - SYS/SYSTEM OBJECT 만 ]

   ==> 자동 통계정보(GATHER_STATS_JOB) 시에만 영향을 미친다.

Default 값 확인

SYS>select dbms_stats.get_param('method_opt') from dual ;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

Default 값 변경

SYS>execute dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 1') ;

PL/SQL procedure successfully completed.

변경된 Default 값 확인

SYS>select dbms_stats.get_param('method_opt') from dual ;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1

예제 ) 참조– 메타링크 (일반 테이블- 237537.1, 파티션 테이블 - 237538.1 )

Cascade => TRUE

è 인덱스에 대한 통계정보도수집하라.

Cascade => FALSE

è 인덱스에 대한 통계정보도수집하라.

method_opt =>'FOR ALL COLUMNS SIZE 1'

 è 칼럼(High and Low Column Value)에 대한 통계정보도 수집하라.

method_opt =>'FOR COLUMNS'

 è 컬럼에 대한통계정보를 수집하지 마라

가 ) 일반 테이블

SQL> show user

USER is"SYS"

1.  SCOTT의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,

테이블, 칼럼(Highand Low Column Value), 연관 인덱스의

통계정보를 생성한다.( COMPUTE STATISTICS )

SQL> execdbms_stats.gather_table_stats(ownname => 'SCOTT',

tabname => 'BIG_TABLE', cascade =>TRUE,

method_opt => 'FOR ALL COLUMNS SIZE 1');

PL/SQL proceduresuccessfully completed.

2.      SCOTT 의 BIG_TABLE 의15% Row 를 가지고,

테이블, 칼럼, 연관인덱스의

통계정보를 생성한다. ( SAMPLE 15 PERCENT )

SQL> execdbms_stats.gather_table_stats(ownname => 'SCOTT',

 tabname=> 'BIG_TABLE', cascade => TRUE, estimate_percent => 15) ;

PL/SQL proceduresuccessfully completed.

3.  SCOTT 의 BIG_TABLE 의 의 전체 테이블과 모드 인덱스를 가지고,

테이블의통계정보를 수집하라. 인덱스와 칼럼에 대한 통계정보는 제외

SQL> execdbms_stats.gather_table_stats(ownname => 'SCOTT',

 tabname=> 'BIG_TABLE', cascade => FALSE, method_opt => 'FOR COLUMNS');

PL/SQL proceduresuccessfully completed.

4.  SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,

테이블과인덱스에 대한 통계정보를 수집하라. 칼럼에 대한 통계정보는 제외

SQL> execdbms_stats.gather_table_stats(ownname => 'SCOTT',

tabname => 'BIG_TABLE', cascade  => TRUE, method_opt =>'FOR COLUMNS');

PL/SQL proceduresuccessfully completed.

5.    SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,

테이블과칼럼(No Histogram) 그리고 인덱스에 대한 통계정보를 수집하라.

잠시 후에

인덱스 칼럼들의 Histogram 통계정보를 수집하라.

SQL> execdbms_stats.gather_table_stats(ownname => 'SCOTT',

tabname => 'BIG_TABLE', cascade => TRUE) ;

PL/SQL proceduresuccessfully completed.

잠시 후에..

SQL> exec  dbms_stats.gather_table_stats(ownname =>'SCOTT',

            tabname => 'BIG_TABLE', cascade=> TRUE,

method_opt => 'FOR ALL INDEXED COLUMNSSIZE 1');

PL/SQL proceduresuccessfully completed.

6. SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고,

   테이블과 인덱스칼럼(Only High and Low )에 대한 통계정보를 수집하라

   인덱스에 대한 통계정보는수집하지 마라.

SQL> execdbms_stats.gather_table_stats(ownname => 'SCOTT',

tabname => 'BIG_TABLE', cascade =>FALSE,

method_opt => 'FOR ALL INDEXED COLUMNSSIZE 1');

PL/SQL proceduresuccessfully completed.

나.) PartitionTable 의 경우

    추가적으로 granularity 정보를 ‘ALL’,’AUTO’,’PARITION’,

’GLOBAL AND PARTITION,’GLOBAL’,’SUBPARTITION’을 통해서

통계수집 대상 Table Segment 를 선정 가능하다.

참고 ] LOCK VS DBMS_STATS.GATHER_TABLE_STATS
        : DML 이 LOCK 이 발생 하여도 GATHER_TABLE_STATS 는 정상적으로 진행된다.
 SCOTT10> begin
    for i in 1001 .. 5000 loop
    insert into check_lock values ( i , i , 'lock');
    end loop ;
    end ;
     / 
 PL/SQL procedure successfully completed.

 SYS>@check_user_lock.sql
 Enter value for user_name: scott10
 old  46: and b.username =upper('&USER_NAME')
 new  46: and b.username =upper('scott10')

 USERNAME    SID LOCK_TYPE       MODE_HELD   MODE_REQUE LOCK_ID1 LOCK_ID2
 ---------- ---- --------------- ----------- ---------- -------- --------
 SCOTT10     151 DML             Row-X (SX)  None       51782    0
 SCOTT10     151 Transaction     Exclusive   None       131077   307


 SYS>execute dbms_stats.gather_table_stats(ownname =>'SCOTT10',tabname => 'CHECK_LOCK');
 PL/SQL procedure successfully completed.
 ==> DML LOCK 과는 무관하게 진행 된다.

제품 : ORACLE SERVER

작성날짜 : 2003-12-03


LOGON ON TRIGGER를 이용한 접속제한 | TRACE 설정
===============================================

PURPOSE
-------
데이터베이스에 접속하는 IP, USERNAME으로 접속을 제한하거나, TRACE를 설정하는 방법에 
대하여 알아본다.

Explanation
-----------
DB를 접속하는 사용자를 USER/ROLE로 구분하여 관리하는 Accecs Policy 라면 관계 없지만 
하나의 USER/PASSWORD로 Application을 이용하는 경우에는 Application을 통해서만 
DB에 접속하도록 통제하는 것이 불가능하게 된다.

즉 일부 사용자가 PC에 설치된 SQL*Net을 통하여 SQL*Plus나 3rd party TOOL로 DB에 접속하여 
데이터의 열람/조작을 한다면 이를 방지하거나 추적하기가 어렵다.

아래 예제는 DB server로의 TELNET접속을 통한 특정 DB User(SCOTT)의 접속을 원천적으로 막고, 
특정 IP(152.69.41.232)로부터 접속하는 Session에 trace를 설정하는 예제이다.

REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM This script is provided for educational purposes only. It is NOT 
REM supported by Oracle World Wide Technical Support. 
REM The script has been tested and appears to work as intended. 
REM You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 

-- Use an error number in the range of -20000 to -20999 --

CREATE OR REPLACE TRIGGER SCOTT_LOGON_TRACE
AFTER LOGON ON SCOTT.SCHEMA
BEGIN
-- LOCAL=YES로 접속 못 하도록 설정 --
IF ( ORA_CLIENT_IP_ADDRESS IS NULL ) THEN
RAISE_APPLICATION_ERROR ( -20001
, 'Local connection as SCOTT is not allowed!'
);
-- LOOPBACK으로 접속 못 하도록 설정(DB server IP:152.69.41.21) --
ELSIF ( ORA_CLIENT_IP_ADDRESS = '152.69.41.21' ) THEN
RAISE_APPLICATION_ERROR ( -20002
, 'IP '
|| ORA_CLIENT_IP_ADDRESS
|| ' is not allowed to connect database as SCOTT!'
);
-- 특정 IP에 대하여 TRACE 설정 --
ELSIF ( ORA_CLIENT_IP_ADDRESS = '152.69.41.232' ) THEN
SYS.DBMS_SESSION.SET_SQL_TRACE(TRUE);
END IF;
END;
/
----------- cut ---------------------- cut -------------- cut -------------- 


Example
-------
sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Nov 6 17:16:57 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Local connection as SCOTT is not allowed!
ORA-06512: at line 3


SQL> conn scott/tiger@kyulee
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: IP 152.69.41.21 is not allowed to connect database as SCOTT!
ORA-06512: at line 7


SQL> conn system/manager
Connected.


Reference Documents
-------------------
1.
<Note:178924.1>
http://metalink.oracle.com/metalink/plsql/ml2_documents.showNot?p_id=178924.1&p_font=

2.
Bulletin No: 11848
Product: ORACLE_SERVER
Subject: ORACLE 8I SYSTEM EVENT TRIGGER ( ORACLE 8.1.6 )

PURPOSE
 부정형의 비교를 긍정형 비교로 바꾸어서 인덱스 사용을 유도하는 방법에 대해서 알아본다.

KEY IDEA
 부정형의 비교에는 논리적으로 인덱스를 사용할 수 없다. 하지만 약간의 IDEA를 첨부한다면
부정형의 비교를 긍정형의 비교로 바꾸어서 인덱스의 사용을 유도할 수 있다.
(KEY WORD : INDEX 활용, 인덱스, 부정형 비교, NOT IN, NOT EXISTS, <> )

DESCRIPTION
   다음의 SQL을 보자.
     SELECT ‘Not found’  FROM EMP WHERE EMPNO <> ‘1234’

• 대개의 Application에서는 사용자가 처리한 데이터의 타당성을 검증하기 위해 이 값의
  존재 유무를 확인하는 경우가 빈번하게 발생한다.  
  이럴 경우 위의 예처럼 부정형의 문장을 사용하는 경우가 자주 있다.
  
• 하지만 아래와 같이 ‘NOT EXISTS’를 이용해서 서브쿼리(SUB-QUERY)내의 SQL을 긍정형으로 바꾸면 인덱스를 사용할 수 있다.
  
   SELECT ‘NOT FOUND’ FROM DUAL
      WHERE NOT EXISTS ( SELECT ‘X’ FROM EMP WHERE EMPNO = ‘1234’ )
  
  
• 그러나 ‘EXISTS’를 사용하는 것이 항상 유리한 것은 아니다. 다음의 3개의 SQL을 보자.
  
  [SQL1]
    SELECT * FROM TAB1
       WHERE YYYYMM = ‘199910’
           AND NOT EXISTS ( SELECT * FROM TAB2
                                     WHERE COL2 = COL1
                                         AND YYYYMM = ‘199910’ )
  [SQL2]
    SELECT * FROM TAB1
        WHERE YYYYMM =’199910’
            AND COL1 NOT IN (SELECT COL2 FROM TAB2
                                         WHERE YYYYMM = ‘199910’ )
  [SQL3]
     SELECT * FROM TAB1
         WHERE (YYYYMM, COL1) IN ( SELECT ‘199910’, COL1 FROM TAB1
                                                     WHERE YYYYMM = ‘199910’
                                                 MINUS
                                                 SELECT ‘199910’, COL2 FROM TAB2
                                                      WHERE YYYYMM = ‘199910’ )
  
  
• TAB1 테이블의 ‘YYYYMM’, ‘COL1’이 각각 인덱스로 생성되어 있고
  TAB2의 ‘YYYYMM’, ‘COL2’가 각각 인덱스로 생성되어 있다.
   
• [SQL1] 은 ‘TAB1’의 ‘YYYYMM’ 인덱스만을 사용하여 테이블의 로우를 엑세스하고
  각 로우마다 TAB2 테이블을 엑세스하는 서브쿼리가 수행되어 TAB2 에 존재하지 않는
  로우만 추출하게 된다. 이 SQL은 ‘199910’조건에 해당하는 모든 로우에 대해 서브
  쿼리가 랜덤엑세스를 수행한다. 왜냐하면 서브쿼리내에 메인쿼리의 컬럼인 'COL1'이
  존재하기 때문이다.
   
• [SQL2] 는 서브쿼리 내에 메인쿼리 컬럼을 없애기 위해 작성하였지만 동일한 결과를
  초래한다. 그 이유는 'NOT IN'을 사용한 서브쿼리는 항상 나중에 수행되거나
  필터링(Filtering) 조인방식으로 수행되기 때문이다.
   
• [SQL3]은 각 테이블에 ‘YYYYMM + COL1’, ‘YYYYMM + COL2’의 결합인덱스가 존재한
  다면 먼저 서브쿼리에서 두 개의 테이블을 ‘MINUS’하여 결과를 추출하고 그 결과를
  이용해 메인쿼리를 엑세스하게 할 수 있다. 이 경우에는 서브쿼리가 먼저 수행된다.
  인덱스만으로도 처리가 가능하기 때문에 테이블을 엑세스하지 않고 양쪽 테이블의
  인덱스들만 범위스캔(Range Scan)하여 ‘SORT-MERGE’방식으로 서브쿼리가 처리 된다.
   
• 위의 경우에서는 결과적으로 [SQL3]가 가장 유리한 처리방법이라 하겠다.
  

참고 : http://kdonghwa.tistory.com/58?srchid=BR1http%3A%2F%2Fkdonghwa.tistory.com%2F58

* 인덱스를 타지 않는 SQL
1. 인덱스 컬럼 절의 변형
2. 내부적인 데이터 변환
3. NULL 조건의 사용
4. 부정형 조건의 사용
5. LIKE 연산자 사용
6. 최적기가 판단

1 - 인덱스 컬럼 절의 변형
SQL> select ename from emp where sal * 2.1 > 950 --인덱스 사용불가
SQL> select ename from emp where sal > 950 /2.1  --인덱스 사용가능
SQL> select ename from emp where to_char(hiredate,'DDMMYY') = '250884' --인덱스 사용불가
SQL> select ename from emp where hiredate = to_date('250884','DDMMYY') --인덱스 사용가능

-> 인덱스 컬럼에 변형을 가하면은 사용할수 없습니다. 단 변형 가능하더라도 쓰고 싶다면은
    말리지는 않겠지만 create index .... on emp to_char(hiredate,'DDMMYY') 이렇게 하시면 됩니다.

2 - 내부적인 데이터 변환
SQL> select * from emp where hiredate ='14-JAN-85' --인덱스 사용불가
SQL> select * from emp hiredate = to_date('71-10-22','YY/DD/DD') --인덱스 사용가능
SQL> select * from emp where empno = '7936' --인덱스 사용불가
SQL> select * from emp where empno = to_number('7936') --인덱스 사용가능

-> 내부적인 데이터변환에서 가장 많이 실수하는 부분은 문자값 데이터타입을 갖는 컬럼에
    '값' -> 값 이렇게 하시는분이 많습니다. 딱맞는 데이터타입을 주세요 ^_^

3 - NULL 조건의 사용
SQL> select ename from emp where comm is null --인덱스 사용불가
SQL> select ename from emp where comm is not null --인덱스 사용불가
SQL> select ename from emp where ename > '' --인덱스 사용가능
SQL> select ename from emp where comm >= 0 --인덱스 사용가능

-> NULL조건으로 검색한다는 가정하에는 거의 인덱스 풀 스캔이 일어나겠죠. 적절히 사용합씨다.

4 - 부정형 조건의 사용
SQL> select ename from emp where deptno != 30 --인덱스 사용불가
SQL> select ename from emp where deptno < 30 and deptno > 30 --인덱스 사용가능

-> 논리적으로 부정형을 이용하여 인덱스를 사용하겠다는것은 말이 안되죠...
    이 쿼리문도 적절히 사용합씨다.

5 - Like 연산자 사용
SQL> select * from emp where ename like 'S%' --인덱스 사용가능
SQL> select * from emp where ename like '%S%' --인덱스 사용불가

-> %S% 부분을 꼭 쓰고싶다면은 이렇게 하세요 앞에부분을 다 넣는거죠. AS%, BS%...
    요즘 홈피를 보면 본문 찾기는 거의 없어져가고 있죠. 엔코아 경우 주제어를 검색을..

6 - 최적기가 판단

-> RBO경우 무조건 타죠 있으면은 이눔은 워낙 법을 좋아해서리..CBO는 통계값을 기준으로
    하기 때문에 DBA가 잘 해야겠죠. 그럼 우리가 판단하게 할라면은 HINT를 써서 이눔들을
    인도해야죠... 오늘도 전도를 ^____^


마지막으로 인덱스를 이용한 sort 회피 방법에 대해서 알아보겠습니다.
SQL> select empno, ename, job, comm from emp order by empno --sort 정렬
SQL> select empno, ename, job, comm from emp where empno >= 0 --sort 정렬제거

-> 인덱스는 값이 정렬되어 들어가있는 구조이기 때문에 처음부터 작은값 맨 끝값은
    최대값이 있겠죠. 잘 사용하면 최고의 튜닝이 되겠죠.