행위

오라클 튜닝 autotrace

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 11월 20일 (금) 14:11 판 (AUTOTRACE 옵션)
thumb_up 추천메뉴 바로가기


1 AUTOTRACE[편집]

SQL 플랜 정보 조회

1.1 AUTOTRACE 권한[편집]

  • PLAN_TABLE 필요 (10G 이상은 SYS.PLAN_TABLE$ 존재함, => 즉, 생성할 필요없음 , 없으면 시노님 생성 하면됨.)
  • 실행통계 확인시 V_$SESSSTAT,V_$STATNAME,V_$MYSTAT 읽기권한 필요

1.1.1 롤을 생성하여 부여하는 방법[편집]

SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;

1.2 AUTOTRACE 옵션[편집]

  1. set autotrace on
    1. SQL실행
    2. 실행 결과 집합
    3. 예상 실행 계획 출력
    4. 예상 실행 통계 출력
  2. set autotrace on explain
    1. SQL실행
    2. 실행 결과 집합
    3. 예상 실행 계획 출력
  3. set autotrace on statistics
    1. SQL실행
    2. 실행 결과 집합
    3. 실행 통계 출력
  4. set autotrace traceonly
    1. SQL실행
    2. 실행 결과 출력 하지 않음
    3. 예상 실행 계획 출력
    4. 실행 통계 출력
  5. set autotrace traceonly explain
    1. SQL실행 하지 않음
    2. 예상 실행 계획 출력
  6. set autotrace traceonly statistics
    1. SQL실행
    2. 실행 결과 출력 하지 않음
    3. 실행 통계 출력

1.3 PLAN_TABLE is older version 메시지[편집]

Note: PLAN_TABLE is older version: Using simple plan output.

PLAN_TABLE의 버전이 오래 되어서 없는 칼럼이 존재 하기 때문.

plan_table을 다시 생성해 주면 된다.

SQL>drop table plan_table;

Oracle 9i 에서 이런오류를 만났다면 8i이전 버전의 테이블이 생성된 것이므로 테이블 DROP 후 재생성해 주면된다.

Oracle 10g 제품 부터는 사용자가 plan_table을 생성할 필요가 없다.

SYS 스키마에 모든 유저가 사용할 수 있는 임시 테이블 PLAN_TABLE$과 PUBLIC SYNONYM PLAN_TABLE 존재.

사용자 스키마에 plan_table이 존재 할 경우 동의어보다 테이블을 우선 탐색하기 때문에 오류 메시가 발생.

10g와 11g 모두 36개 칼럼을 가지고 있다.

  1. PLAN_TABLE 확인
    desc PLAN_TABLE;
  2. PLAN TABLE 오너.테이블명
    desc sys.PLAN_TABLE$;
  3. PLAN TABLE 확인
select owner, table_name, tablespace_name
  from dba_tables
where table_name = 'PLAN_TABLE';
select owner, object_type, object_name
 from dba_objects
where object_name like 'PLAN_TABLE%';
  • 생성 SQL
create table PLAN_TABLE (
    statement_id varchar2(30), -- 사용자지정 식별자
    plan_id number, -- 플랜의 고유번호
    timestamp date, -- 실행계획이 수립된 날짜와 시간
    remarks varchar2(4000), -- 사용자가 부여한 주석(comments)
    operation varchar2(30), -- AND-EQUAL,INDEX, SORT 등과 같은 실행 연산자
    options varchar2(255),
    object_node varchar2(128), -- 사용한 데이타베이스 링크
    object_owner varchar2(30), -- 객체를 생성한 소유자
    object_name varchar2(30), -- 테이블, 인덱스, 클러스터 등 객체의 이름
    object_alias varchar2(65),
    object_instance numeric, -- FROM절에 기술된 객체에 좌에서우로 부여한 번호
    object_type varchar2(30), -- TABLE, INDEX 등 객체의 종류
    optimizer varchar2(255), -- CHOOSE, FIRST_ROW 등의 현재의 옵티마이져 모드
    search_columns number,
    id numeric, -- 실행계획의 단계별 일련번호
    parent_id numeric, -- 트리구조상 부모노드 일련번호
    depth numeric,
    position numeric, -- 부모노드기준 처리순서
    cost numeric, -- CBO에 의해서 추정된 현재의 연산비용
    cardinality numeric, -- 실행단계에서 처리될 Rows
    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
);

1.3.1 토드/오렌지에서 AUTOTRACE 활성화 옵션[편집]

  1. 토드 : sql창에서 마우스우클릭 > auto trace 선택
  2. 오렌지 : Action 메뉴 > Extract Statistics 선택 or 컨트롤+쉬프트+S