튜닝 - SQL 실행 계획 확인하기 : SQL*Plus Auto Trace 활용

SQL 튜닝을 위한 기본적인 툴 : SQL Plus의 Autotrace 기능
 - 가장 일반적인 방법
 - 실행결과, 실행계획, 통계정보 를 보여준다.

▣ (오라클깔면) 기본적으로 설치는 되어 있지만 Enable 시켜줘야 한다.
 
1. PLUSTRACE 권한 설정

SQL> conn / as sysdba
SQL> @?/sqlplus/admin/plustrace.sql

 
2. 해당사용자에게 권한 부여

SQL> conn / as sysdba
SQL> grant plustrace to scott;

 
3. 해당 사용자로 로그인 후 PLAN TABLE 생성

SQL> conn scott/tiger
SQL> @?/rdbms/admin/utlxplan.sql

※ PLAN TABLE이 만들어져 있는 경우 지우고 다시 생성해주기

SQL> drop table PLAN_TABLE purge;

 
▶ 실행 해 보기
 
SQL> select * from emp where empno=700;
실행계획 안나옴
 

SQL> set autot on;  → 플랜테이블에있는 실행계획을 보여달라.

 
SQL> select * from emp where empno=700;
실행계획 나옴
 

SQL> set autot off; → 플랜테이블에있는 실행계획을 그만 보여달라.

---

위의 내용 실습 : 

SQL> @?/sqlplus/admin/plustrce.sql
 
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
 
 
SQL> create role plustrace;
 
Role created.
 
SQL>
SQL> grant select on v_$sesstat to plustrace;
 
Grant succeeded.
 
SQL> grant select on v_$statname to plustrace;
 
Grant succeeded.
 
SQL> grant select on v_$mystat to plustrace;
 
Grant succeeded.
 
SQL> grant plustrace to dba with admin option;
 
Grant succeeded.
 
SQL>
SQL> set echo off

===================================================

SQL> grant plustrace to scott;
 
Grant succeeded.
 
SQL> conn scott/tiger;
Connected.
SQL> @?/rdbms/admin/utlxplan.sql
 
Table created.

===================================================

SQL> set autotrace on

SQL> select * from tt10;
 
        NO NAME
---------- ----------
         1 AAAA
         1 BBBB
         1 CCCC
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2328555870
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    60 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TT10 |     3 |    60 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

 
 

SQL> set autotrace off → 끄기

===================================================
 



※ 참고
 

- 결과 부분 -
 
--------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time   |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |        3 |       60 |          3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL           | TT10   |        3 |      60 |          3   (0)| 00:00:01 |
--------------------------------------------------------------------------
이부분 안나오고 Temprarytable empty 라고 나오면, Temprarytable 만들어주면 된다.
 
 
 
SQL> select * from tt10;
 
        NO NAME
---------- ----------
         1 AAAA
         1 BBBB
         1 CCCC

==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==

- 실행결과 부분 -
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2328555870
 
--------------------------------------------------------------------------
| Id  | Operation                                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |        3 |      60 |            3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | TT10       |        3 |      60 |            3   (0)| 00:00:01 |
| * 2 |   INDEX UNIQUE SCAN                  |  PK_TT10 |        1 |          |                    |             |
--------------------------------------------------------------------------
→
 
Note
-----
   - dynamic sampling used for this statement

==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==

- 통계정보 부분-
 
 
Statistics
----------------------------------------------------------
         48  recursive calls       나는 쿼리 1개 날렸는데 서버프로세스가 recuresive calls 48개 날림
          0  db block gets         db buffer cache에서 블락읽은 갯수
         20  consistent gets      db buffer cache에서 블락읽은 갯수
          0  physical reads       file 에서 블락읽은 갯수
          0  redo size
        517  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)      메모리에서 정렬된 갯수
          0  sorts (disk)
          3  rows processed   

 

똑같은 쿼리 날리면 : 메모리 사용률 줄어든다.(소프트파스)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==

※ 보는 방법 : 실행되는 순서알기!!
SQL> create index idx_stud_deptno on student(deptno);
 
0  SELECT STATEMENT
1     MERGE JOIN
2       TABLE ACCESS BY INDEX ROWID
3          INDEX FULL SCAN
4       SORT JOIN
5         TABLE ACCESS FULL
 
순서 : 3-2-5-4-1-0