[Oracle] 통계정보 수집 및 실행 계획 조회 방법

통계정보 수집 및 실행 계획 조회 방법 | Oracle

2012.06.30 14:14:20 | 2012.06.30 14:14:20 | 1749 | Aiden

1. 통계정보 수집

ANALYZE TABLE SJ_USER.COM_BOARD COMPUTE STATISTICS;     -- 해당 Table의 Table, Index, Cluster 개체 모두 통계 정보를 수집한다.
ANALYZE INDEX SJ_USER.IDX_SORT COMPUTE STATISTICS;      -- 해당 Index에 대한 통계 정보만 수집한다.

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SJ_USER', 'COM_BOARD');

2. 수집된 통계정보 조회

SELECT TO_CHAR(T.LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS'), T.*
FROM USER_TABLES T
WHERE T.TABLE_NAME = 'COM_BOARD';


SELECT TO_CHAR(T.LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS'), T.*
FROM USER_INDEXES T
WHERE T.TABLE_NAME = 'COM_BOARD';

* 통계 정보 수집후 실행 계획 돌려보면 실행 계획이 반영된다.
즉 비용기반 옵티마이저가 실행계획 수립을 위한 기초 데이터가 통계정보이므로 주기적인 Analyze가 수행되어야 한다.

3. ORACLE 10g부터는 통계정보가 자동수집 된다.
-- 자동통계정보 DISABLE 하기(11g에서는 아래의 JOB명으로 등록된 것이 없다. 확인필요하다. 단지 사용법만 참고하자)

EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

- 통계정보상태 확인하기
SELECT  JOB_NAME
,       ENABLED
,       SCHEDULE_NAME
,       STATE 
FROM DBA_SCHEDULER_JOBS ;

4. 실행계획 저장 PLAN_TABLE 생성

create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);

5. PLAN_TABLE 테이블 생성 후 수행 속도 향상과 동일한 statement_id가 생성되는 것을 방지하기 위해 index를 생성

CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id, id);

6. 실행계획 수립할 sql구문 분석

EXPLAIN PLAN SET STATEMENT_ID='a1' FOR 
select  mnu_uid
,       bod_uid
,       bod_subject
,       bod_ref
,       bod_level
,       bod_order
from com_board
where mnu_uid = 142
and bod_ref > 0;

7. 수립된 실행 계획 정보 조회

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost  estimate:' ||
       DECODE(position,'0','N/A',position),null) || ' ' ||options || DECODE(object_name,null,null,':') ||
       RPAD(object_owner, LENGTH(object_name)+1,',') || object_name ||
       DECODE (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) || 
       DECODE(object_instance,null,null,'('||object_instance||')')  "Explain Plan"
FROM PLAN_TABLE
START WITH ID= 0 and STATEMENT_ID = :id
CONNECT by prior ID=PARENT_ID and STATEMENT_ID=:id;