AniBumiRami의 블로그

1. 실행계획

1. AutoTrace 이용

§ [sy]

set autotrace on

select * from emp where empno=7900;

i. set autotrace on

sql을 실제 수행하고 그 결과와 함께 실행계획 및 실행통계를 출력한다.

ii. set autotrace on explain

sql을 실제 수행하고 그 결과와 함께 실행계획을 출력한다.

iii. set autotrace on statistics

sql을 실제 수행하고 그 결과와 함께 실행통계를 출력한다.

iv. set autotrace traceonly

sql을 실제 수행하지만 그 결과는 출력하지 않고 실행계획과 통계만을 출력한다.

v. set autotrace traceonly explain

sql을 실제 수행하지 않고 실행계획만을 출력한다.

vi. set autotrace traceonly statistics

sql을 실제 수행하지만 그 결과는 출력하지 않고 실행통계만을 출력한다.

○ 1,2,3,4,6 은 수행결과 및 실행통계를 보여줘야 하므로 쿼리를 실제 수행한다. 

○ 5는 실행계획만 출력하면 되므로 쿼리를 실제 수행하지 않는다.

2. DBMS_XPLAN 패키지

  • PLAN_TABLE에 저장된 실행계획을 좀 더 쉽게 출력해 볼 수 있다.

  • [sy]

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'SERIAL'));

  • 첫번째 인자는 실행계획이 저장된 PLAN TABLE명을 입력

  • 두번째 인자는 STATEMENT_ID를 입력, NULL일경우 마지막 EXPLAIN PLAN 명령에 사용했던 쿼리의 실행계획을 보여준다.

  • 세번째 인자는 포맷옵션 선택

  • 예)

explain plan [set statement_id = 'SQL1'] for

select * from emp e, dept d

where d.deptno=e.deptno

and e.sal>=1000;

§ set statement_id 를 설정해주지 않으면 마지막에 실행한 쿼리를 null을 이용해 볼 수 있다.

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1'(null), 'BASIC'));  

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'TYPICAL'));

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'SERIAL'));

select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ALL'));

  • 옵션종류

§ BASIC

§ TYPICAL

§ ROWS

§ BYTES

§ COST

§ PARTITION

§ PARALLEL

§ PREDICATE

§ PROJECTION

§ ALIAS

§ REMOTE

§ NOTE

  • 모두 다 출력해 보이려면 ALL 옵션을 사용하면 된다.

  • 실행계획을 수립하는데 필요한 힌트 목록을 출력.

§ outline

  • all과 outline 을 함께 사용하여 출력

§ advanced

2. SQL 트레이스 수집 및 분석

가. SQL 트레이스 수집

1. 현재 자신이 접속해 있는 세션에만 트레이스 설정

alter session set sql_trace=true;

select * from emp where empno=7900;

select * from dual;

alter session set sql_trace=false;

2. user_dump_dest 파라미터로 지정된 서버 디렉토리 밑에 트레이스 파일이 생성된다. 다음 쿼리를 실행하면 찾을 수 있다.

select r.value || '/' || lower(t.instance_name) || '_ora_' || ltrim(to_char(p.spid)) 

|| '.trc' trace_file

from v$process p, v$session s, v$parameter r, v$instance t

where p.addr = s.paddr

and r.name = 'user_dump_dest'

and s.sid = (select sid from v$mystat where rownum = 1);

나. SQL 트레이스 포맷팅

• 세션에서 트레이스 생성되게 설정 변경 후 생성된 트레이스 파일을 tkprof 포맷팅 하기

§ 트레이스 이름 설정

alter session set tracefile_identifier='AAA'; => 이후 생성되는 트레이스 파일은 뒤에 AAA가 붙는다.

§ alter session set sql_trace=true;

§ alter session set timed_statistics=true;

§ select * from emp where empno=7900;

• 저장경로 확인

§ show parameter user_dump_dest;

• tkprof 형식으로 바꾸기

§ 트레이스가 있는 폴더로 이동 후 명령어 입력

□ tkprof 원본이름 출력될이름.txt explain=kmj/kmj sys=no

□ 같은 폴더에 출력될이름.txt 가 생성됨.

다. SQL 트레이스 분석

○ 앞선 AutoTrace 실행 통계 항목과 비교해 보면 다음과 같다

§ db block gets                               = current

§ consistent gets                              = query

§ physical reads                               = disk

§ SQL*Net roundtrips to/from client     = fetch count

§ rows processed                             = fetch rows

• Call 통계 아래쪽 Row Source Operation

§  Rows 는 각 수행 단계에서 출력(Flow-Out) 된 로우 수를 의미한다.

§ cr, pr, pw, time 은 Consistent 모드 블록 읽기, 디스크 블록 읽기, 디스크 블록 쓰기, 소요시간(us=microsecont)

§ 부모는 자식 노드의 값을 누적한 값을 갖는다. 

예를들어 emp 테이블 액세스 단계는 cr=2이고, 그 자식 노드인 emp_pk 인덱스 액세스 단계는 cr=1이므로, 인덱스를 읽고 난 후 테이블을 액세스하는 단계에서 순수하게 일어난 cr 개수는 1이다.