auto trace _ set autotrace on, set autotrace on explain, set autotrace on statistics, set autotrace traceonly, set autotrace traceonly explain, set autotrace traceonly statistics

■ autotrace 
1) 정의 

- SQL*PLUS나 SQL DEVELOPER 등에서 사용할 수 있는 도구로, SQL문 실행 후 실행계획, 실행 결과, 그리고 실행 통계 정보를 출력하는 기능.

2) 사용 가능 도구 

- SQL*PLUS, SQL DEVELOPER 가능

3) 사용 방법

- SQL*PLUS나 SQL DEVELOPER에서 `AUTOTRACE`를 활성화하여 SQL문을 실행하면 실행계획, 실행 결과, 통계 정보를 자동으로 출력함.

- 특정 세션에 대한 실행 통계 정보를 확인하기 위해서는 `PLAN_TABLE`이라는 테이블이 필요하며, `PLUSTRACE` 롤(role)에 접근 권한이 있어야 함.

4) 장점 

- SQL문의 성능을 빠르게 분석하고 최적화할 수 있는 편리한 도구.

- 실행계획, 결과, 통계 정보를 한눈에 확인하여 튜닝에 도움을 줌.

 

@$ORACLE_HOME/sqlplus/admin/plustrce.sql

#) 기존꺼 삭제
SYS@ora11g> drop role plustrace; => 기존꺼 삭제
#) 통계 정보 v$sess =: v_$sess 와 비슷한 개념이나, 더 상위 개념
SYS@ora11g> grant select on v_$sesstat to plustrace;
#) 나의 session 정보
SYS@ora11g> grant select on v_$mystat to plustrace;

#) hr 계정에서 autot 사용 못함 

-> 오류 확인

set autot on

#) sys session 에서 hr 계정에 권한 부여

grant plustrace to hr;

#) hr 계정에서 role 확인

select * from session_roles;

 

#) hr 계정에 대한 role 에 대한 권한 확인.

select * from role_tab_privs;

1. set autotrace on
- SQL문을 실제 수행하고 결과와 함께 실행계획, 실행통계 정보 출력

set autotrace on

#) 실행 계획 확인

select * from hr.employees where employee_id = 100;

Statistics => 통계정보
----------------------------------------------------------
          8  recursive calls
          0  db block gets -> DML 작업
          4  consistent gets -> select문
          1  physical reads -> select문 / DML 작업
          0  redo size
       1023  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

#설명)
1) recursive calls : 유저 레벨과 시스템 레벨에서 생성되는 재귀호출(SQL문장과 별개로 operation)의 수를 나타낸다. 실제 SQL문장을 실행되는 것과 별개로 oracle에서 내부적으로 사용하는 SQL문장.
☆ 2) db block gets : DML 작업 시에 확인. current한 block이 요청된 횟수. 메모리에서 access한 block 의 수
☆ 3) consistent gets : select 작업 시에 확인. 블록에 대한 일관성 읽기가 요청된 횟수(block access한 수/ data buffer cache 에서 읽음). 메모리에서 access한 block 의 수
☆ 4) physical reads : 디스크에서 읽은 데이터 블록의 수
5) redo size : DML 작업 시 사용한 redo size(byte 값)
6) bytes sent via SQL*Net to client : client 한테 보낸 총 byte 값
7) bytes received via SQL*Net from client : clien 한테 받은 byte 값
★ 8) SQL*Net roundtrips to/from client : clien 와 주고 받은 oracle net 메시지 수
9) sorts (memory) : 메모리에서 수행되어 디스크 쓰기가 필요하지 않은 정렬 작업의 수. PGA에서의 메모리 수
10) sorts (disk) : 최소한 하나의 디스크 쓰기가 필요한 정렬 작업의 수. 메모리에서 sort 작업이 디스크에서 작업한 수
11) rows processed : 처리한 행의 수

#) update문 실행했을 경우 (select문과 비교해보기)

update hr.employees set salary = salary * 1.1 where employee_id = 100;

#) 한번더 동일한 update문 실행 

update hr.employees set salary = salary * 1.1 where employee_id = 100;

 

#) 동일한 select문장을 다시 실행

select * from hr.employees where employee_id = 100;

2. set autotrace on explain
- SQL문을 실제 수행하고 결과와 함께 실행계획만 수행. 통계정보는 보이지 않는다.

set autotrace on explain

select * from hr.employees where employee_id = 100;

3. set autotrace on statistics
- SQL문을 실제 수행하고 결과와 함께 통계정보만 수행.

set autotrace on statistics

select * from hr.employees where employee_id = 100;

#) 사원번호 200으로 변경해보기 

select * from hr.employees where employee_id = 200;

#) 동일한 select문을 실행시, recursive calls 줄어들며 -> consistent gets 줄어든다.

4. set autotrace traceonly
- SQL문을 실제 수행하고 결과는 출력되지 않고 실행계획, 실행통계 출력
- 보편적인 명령문

set autotrace traceonly

select * from hr.employees where employee_id = 100;

5. set autotrace traceonly explain
- SQL문을 실제 수행하지 않고 실행계획만 출력

set autotrace traceonly explain

select * from hr.employees where employee_id = 100;

6. set autotrace traceonly statistics
- SQL문을 실제 수행하고 결과는 출력하지 않고 실행통계만 출력

set autotrace traceonly statistics

select * from hr.employees where employee_id = 100;