실행계획

실행계획이란 SQL 이 최적의 방법으로 원하는 데이터를 찾을 수 있도록 만들어지는 계획이다.
이 실행계획을 확인 해야만 자신이 의도한대로 옵티마이저가 동작했는지 알 수 있다.
실행 계획 확인하는 방법중에는 상용툴(토드, 오렌지)을 사용하는 방법도 있지만 생략한다.



SQL*PLUS Auto Trace

---

 http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch8.htm#i1037682 
 인스턴스의 모든 세션 대상으로 하거나 특정 세션에서 실행하는 SQL에 대해 실행계획을 표시한다. 하지만 모든 세션을 대상으로 추적하면 시스템에 많은 부하가 발생한다. 그래서 일반적으로 특정 세션에 대해서만 추적한다. 

- SQL*PLUS auto Trace 
- TKPROF
- 실행 결과, 실행 계획, 통계 정보
- 테이블 권한 및 테이블 생성
- 실행계획은 하위의 deps를 먼저 수행한다. 같은 deps라면 위부터 수행한다.

사전작업

plustrce.sql 실행( sysdba )
 

@?/sqlplus/admin/plustrce.sql

사용자에게 권한 부여( sysdba )
 

SQL> GRANT plustrace TO scott;
SQL > GRANT alter session to scott;

사용자의 Plan Table 생성(scott)
 

@?/rdbms/admin/utlxplan.sql

 Autotrace 모드 변경
 

set autotrace on 
set autot off
set autotrace traceonly
set autotrace traceonly statistics 

세션 설정 변경(scott)
 

alter session set sql_trace=true;
alter session set timed_statistics=true; 
Execute dbms_session.set_sql_trace(true) 

테스트

-- 테이블생성
create table test01
( no number , name varchar2(10) );

-- 데이터 입력
begin
for i in 1..1000000 loop
insert into test01 values (i, dbms_random.string('A',9));
end loop;
commit;
end;
/
-- 쿼리
select * from test01 where no=12345

위의 설정을 마친후에 sql을 실행하면 USER_DUMP_DEST에 TRACE파일이 생성되는데 이 파일을 TKPROF를 통해 리포트로 변환한다.

$  pwd

/home/oracle/admin/testdb/udump

$ ls

testdb_ora_4277.trc

$ tkprof testdb_ora_4277.trc stat.txt explain=scott/tiger sys=no
 
$ ls 

stat.txt  testdb_ora_4277.trc

&