SQL*Plus의 Autotrace 활용

Autotrace 란?


SQL*Plus 툴이 제공하는 기능이다. 실행계획은 예상 계획을 뿐 실제 적용이 보장되는 것은 아니다. 실행계획에서 확인되는 rows, bytes, cost 등은 optimizer의 예측에 불과하기 때문에 실제 결과와 다를 수 있다.



Autotrace 활성화

SQL> conn / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public;

Oracle 9i 이하 버전에서 추가 (10g 이상에서는 할 필요 없음)
SQL> @?/rdbms/admin/utlxplan.sql

Autotrace 옵션

	질의결과	실행계획	통계
set autotrace on	O	O	O
set autotrace on statistics	O	X	O
set autotrace on explain	O	O	X
set autotrace traceonly	X	O	O
set autotrace traceonly statistics	X	X	O
set autotrace traceonly explain	X	O	X
set autotrace off	X	X	X

Autotrace의 Statistics에 대한 이해

SQL> set autot traceonly statistics
SQL> select * from emp

 Statistics
 ----------------------------------------------------------
           0  recursive calls
           0  db block gets
           8  consistent gets
           0  physical reads
           0  redo size
        1415  bytes sent via SQL*Net to client
         385  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
          14  rows processed

1. recusive call

다른 SQL문의 실행으로 인해 자동으로 실행된 SQL의 실행횟수 이다. 가능한 피해야 하지만 피할 수 없는 상활도 많으며 이때는 그냥 수용해야 한다.

(1) Hard Parses : 객체 , 사용 권한 등을 확인하기 위해 실행된 SQL

conn scott/tiger

alter system flush shared_pool;

 Hard Parses 테스트

 첫번째 실행
select * from emp;

 두번째 실행
select * from emp;

(2) PL/SQL Function Calls
 

create or replace function some_function return number
 as
    l_user     varchar2(30) default user;
    l_cnt      number;
 begin
    select count(*) into l_cnt from dual;
    return l_cnt;
 end;
 /

 PL/SQL Function Calls 테스트
 set autotrace on statistics;

  첫번째 실행
 select ename, some_function from scott.emp;

  두번째 실행
 select ename, some_function from scott.emp;

  세번째 실행
 select ename, some_function from scott.emp;

  PL/SQL 루틴을 SQL에 직접 표현하면 recursive calls를 줄일 수 있다.
 prompt select ename, (select count(*) from dual) from emp;

  첫번째 실행
 select ename, (select count(*) from dual) from emp;

  두번째 실행
 select ename, (select count(*) from dual) from emp;

  세번째 실행
 select ename, (select count(*) from dual) from emp;

(3) Side Effects from Modifications : Trigger, FBI 등에 의한 재귀적 호출

drop table t;
 create table t ( x int );

 create trigger t_trigger before insert on t for each row
 begin
     for x in ( select *
                  from dual
                 where :new.x > (select count(*) from emp)) loop
         raise_application_error( -20001, 'check failed' );
     end loop;
 end;
 /

 
 첫번째 실행
 insert into t select 1 from all_users;

 set autotrace traceonly statistics

 두번째 실행
 insert into t select 1 from all_users;

 세번째 실행
 insert into t select 1 from all_users;

(4) Space Requests : 유저의 quota를 확인하고 수정하기 위해 Recursive SQL 발생한다.

2. db block gets 및  consistent gets

Logical IO : db block gets 및 consistent gets

db block gets : 반드시 마지막 버전의 데이터가 필요한 수정문들은 db block gets 수치를 높인다.
consistent gets : 쿼리문은 읽기 일관성이 필요하므로 consistent gets 수치를 높인다.

Losical IO의 수치를 줄이기 위한 방법들

(1) Query Tuning

쿼리 튜닝에서 성공하려면 먼저 SQL 언어의 기능을 샅샅이 이해해야 하고, 사용 가능한 액세스 경로를 철저히 이해해야 한다.

 alter system set cursor_sharing=exact;  cf.CURSOR_SHARING 파라미터 = exact | force | similar --> 8i .. 11g
 drop table MAP;
 drop table I1;
 drop table I2;

테이블 및 인덱스의 구조를 잘 살펴보고 어떤 access path가 가능한지 논해보자.

 create table I1(n number constraint i1_pk primary key, v varchar2(10));
 create table I2(n number constraint i2_pk primary key, v varchar2(10));

 create table MAP
 (n number primary key,
  i1 number referencing I1(n),
  i2 number referencing I2(n));

 create unique index IDX_MAP on MAP(i1, i2);

 insert into i1 select rownum, rpad('*',10,'*') from all_objects;
 insert into i2 select rownum, rpad('*',10,'*') from all_objects;
 insert into map select rownum, rownum, rownum from all_objects;

 set autotrace traceonly

 alter session set optimizer_mode=rule;

 
RBO가 적절하지 않은 실행계획을 선택하는 예제.
- 실행계획이 적절하지 않으므로 IO 수치가 높을 것이다.

 

select *
   from i1, map, i2
  where i1.n = map.i1
    and i2.n = map.i2
    and i1.v = 'x'
    and i2.v = 'y';

 -----------------------------------------------
 | Id  | Operation                     | Name  |
 -----------------------------------------------
 |   0 | SELECT STATEMENT              |       |
 |   1 |  NESTED LOOPS                 |       |
 |   2 |   NESTED LOOPS                |       |
 |   3 |    TABLE ACCESS FULL          | MAP   |
 |*  4 |    TABLE ACCESS BY INDEX ROWID| I2    |
 |*  5 |     INDEX UNIQUE SCAN         | I2_PK |
 |*  6 |   TABLE ACCESS BY INDEX ROWID | I1    |
 |*  7 |    INDEX UNIQUE SCAN          | I1_PK |
 -----------------------------------------------

  -->          0  db block gets
  -->     100456  consistent gets

 analyze table i1 compute statistics;
 analyze table i2 compute statistics;
 analyze table map compute statistics;

통계 생성을 통해 CBO가 실행계획을 선택하도록 유도하는 예제.
- IO 수치가 현저히 낮아질 것이다.

 

select *
  from i1, map, i2
  where i1.n = map.i1
  and i2.n = map.i2
  and i1.v = 'x'
  and i2.v = 'y';

 -----------------------------------------------------------------------------------------
 | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |         |     1 |    40 |    45   (3)| 00:00:01 |
 |   1 |  NESTED LOOPS                 |         |     1 |    40 |    45   (3)| 00:00:01 |
 |   2 |   NESTED LOOPS                |         |     1 |    26 |    44   (3)| 00:00:01 |
 |*  3 |    TABLE ACCESS FULL          | I1      |     1 |    14 |    42   (3)| 00:00:01 |
 |   4 |    TABLE ACCESS BY INDEX ROWID| MAP     |     1 |    12 |     2   (0)| 00:00:01 |
 |*  5 |     INDEX RANGE SCAN          | IDX_MAP |     1 |       |     1   (0)| 00:00:01 |
 |*  6 |   TABLE ACCESS BY INDEX ROWID | I2      |     1 |    14 |     1   (0)| 00:00:01 |
 |*  7 |    INDEX UNIQUE SCAN          | I2_PK   |     1 |       |     0   (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------

  -->          0  db block gets
  -->        202  consistent gets

 set autotrace off

테이블에 적절한 인덱스를 추가해서 보다 나은 실행계획을 유도하는 예제

 

alter session set optimizer_mode=choose;

 create index i1_idx on i1(v);

 analyze table i1 compute statistics;

 set autotrace traceonly

  select *
   from i1, map, i2
  where i1.n = map.i1
  and i2.n = map.i2
  and i1.v = 'x'
  and i2.v = 'y';

 -----------------------------------------------------------------------------------------
 | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |         |     1 |    40 |     5   (0)| 00:00:01 |
 |   1 |  NESTED LOOPS                 |         |     1 |    40 |     5   (0)| 00:00:01 |
 |   2 |   NESTED LOOPS                |         |     1 |    26 |     4   (0)| 00:00:01 |
 |   3 |    TABLE ACCESS BY INDEX ROWID| I1      |     1 |    14 |     2   (0)| 00:00:01 |
 |*  4 |     INDEX RANGE SCAN          | I1_IDX  |     1 |       |     1   (0)| 00:00:01 |
 |   5 |    TABLE ACCESS BY INDEX ROWID| MAP     |     1 |    12 |     2   (0)| 00:00:01 |
 |*  6 |     INDEX RANGE SCAN          | IDX_MAP |     1 |       |     1   (0)| 00:00:01 |
 |*  7 |   TABLE ACCESS BY INDEX ROWID | I2      |     1 |    14 |     1   (0)| 00:00:01 |
 |*  8 |    INDEX UNIQUE SCAN          | I2_PK   |     1 |       |     0   (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------

  -->          0  db block gets
  -->          2  consistent gets

 set autotrace off

(2) Array Size Effects

The array size is the number of rows fetched(or sent, in the case of inserts, updates, and deletes) by the server at a time
It can have dramatic effect on performance.
과도한 RAM 사용을 피하려면 array size는 100 ~ 500 정도가 좋다(Thomas kyte)
Pro*C, OCI, JDBC, VB/ODBC 등 거의 모든 프로그래밍 환경에서 array size 조정이 가능하다.

 conn scott/tiger

 drop table t;
 create table t as select * from all_objects;

 set autotrace traceonly statistics;

  arraysize 2
 set arraysize 2
 select * from t;                --> 25426  consistent gets

 -- arraysize 5
 set arraysize 5
 select * from t;

 -- arraysize 10
 set arraysize 10
 select * from t;                --> 5647  consistent gets

 -- arraysize 15
 set arraysize 15
 select * from t;

 -- arraysize 100
 set arraysize 100
 select * from t;                -->  1192  consistent gets

 -- arraysize 5000
 set arraysize 5000
 select * from t;

 set autotrace off

 spool off

 cf.O3 교재 157p 참조.

3. physical reads

데이터 파일에서 데이터 읽기 : 테이블, 인덱스 등을 파일에서 읽는 경우이며, 곧이어 logical IO가 발생한다.
                                          효과적인 access path를 사용하고 있을 경우 이 값을 줄이기 위해 할 수 있는 것은 Buffer cache를 늘리는 정도이다.

TS에서 데이터 읽기 : 정렬, 해시로 인한 임시 데이터를 읽는 경우이며, Direct read를 수행하므로 logical IO가 발생하지 않는다.
 
Tempoary TS에서 데이터 읽기에 영향을 주는 예제

 conn scott/tiger

 alter session set workarea_size_policy = manual;
 alter session set hash_area_size = 1024;

 drop table t;
 create table t as select * from all_objects;

 analyze table t compute statistics
 for table for columns object_id;

 set autotrace traceonly

SORT Area : 100K일 경우 :
논리적 읽기보다 물리적 읽기가 더 많다는 사실에 주목해야 하며, 이는 임시 작업 공간에 대한 IO가 있었음을 뜻한다.  
sorts (disk)의 수치를 보면 좀 더 정확한 판단이 가능하다. 단, 해시 조인처럼 sort를 하지 않는 IO도 있으므로 주의해야 한다.

 alter session set sort_area_size = 102400;

 select *
   from t t1, t t2
  where t1.object_id = t2.object_id
 /

SORT Area : 1M일 경우 :

alter session set sort_area_size = 1024000;
 select *
   from t t1, t t2
  where t1.object_id = t2.object_id
 /

SORT Area : 10M일 경우 :
physical reads 및 sorts (disk)가 0이 되었음을 확인
 

 alter session set sort_area_size = 10240000;
 
 select *
   from t t1, t t2
  where t1.object_id = t2.object_id
 /

PGA_AGGREGATE_TARGET 설정이 되어 있을 경우 :
해시 조인으로 실행되어 sort가 없어졌음을 확인

 show parameter pga

 alter system set pga_aggregate_target=200m;
 alter session set workarea_size_policy = auto;

 select *
   from t t1, t t2
  where t1.object_id = t2.object_id
 /

 set autotrace off

 
pga_aggregate_target를 이용한 Workarea 관련 메모리 관리 기법을 연구하자.

 select * from v$fixed_table where name like 'V%ADVICE%'
 SELECT * FROM V$PGA_TARGET_ADVICE;

4. redo size

단일행 insert, Merge, Update, Delete 등에 의한 redo 발생은 대부분 피할 수 없으며, Direct path insert 또는 CTAS 등의 효율성을 판단하는데 도움이 되며, 때에 따라 튜닝 대상이 된다.

(1) Bulk Loads

Q> 개발 환경에서는 insert /*+ append */ 문이 매우 빠르게 처리되었는데 프로덕션에서는 같은 문장이 매우 느립니다. 무엇이 잘못된 것일까요?

A> 잘못된 것은 없으며 프로덕션은 Archivelog 모드이기 때문입니다. 보시다시피, 테스트 환경은 프로덕션 환경과 거의 같아야 합니다.

참고 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#i8327

 drop table big_table;

 create table big_table
 as
 select *
   from all_objects
  where 1=0;

 set autotrace on statistics;

 일반 insert :
 발생하는 redo의 양을 주목하세요. 약 5MB 발생   --> 5682332

 insert into big_table select * from all_objects;

 Direct path insert :
 발생하는 redo의 양이 변화하지 않았다.              --> 5682332          이유는? Archivelog 모드이므로

 insert /*+ APPEND */ into big_table select * from all_objects NOLOGGING;

 하지만 일반적으로 nologging + append 설정이 필요하다.
 발생하는 redo의 양을 주목한다. 약 20KB 발생      --> 19956 

 alter table big_table nologging;
 insert /*+ APPEND */ into big_table select * from all_objects;

 commit;

 alter table big_table logging;

 insert /*+ APPEND */ into t values (...) 문은 redo가 남는다. append는 사용할 필요 없다.
 APPEND는 redo 발생 절약 및 입력 속도에는 긍정적이지만 복구 및 공간 사용 등에는 부정적이므로 
 개발자는 DBA와 잘 협의해서 이 기능을 사용해야 한다.

(2) Redo and Index Operations

 alter table big_table nologging;

 create index big_table_idx on
 big_table(owner,object_type,object_name);

인덱스 1개 추가한 결과 발생하는 redo의 양이 5배가량 증가했음을 확인
발생하는 redo의 양을 주목한다. 약 25MB 발생

 insert /*+ APPEND */ into big_table select * from all_objects;
 commit;

DW처럼 대량의 데이터 로딩이 많은 경우 다음과 같은 방식이 바람직하다.

 alter index big_table_idx unusable;

 alter session set skip_unusable_indexes=true;

 insert /*+ APPEND */ into big_table select * from all_objects;

 alter index big_table_idx rebuild nologging;

5. SQL*Net 통계

 ~ bytes received via SQL*Net from client
 ~ bytes sent via SQL*Net to client : 필요한 컬럼만 select절에 나열하라.
 ~ SQL*Net roundtrips to/from client : array size 설정에 영향을 받는다.


6. sorts (memory), sorts (disk) 및 rows processed

 ~ sorts (disk)가 0일지라도 Temp space에 대한 disk IO가 있을 수 있다.