05 주차 정리 내용 - 길용현

[페르디안]

Chapter 03. 오라클 성능 관리

 - SQL 튜닝 절차

  1. Explain Plan 을 통해 실행계획을 먼저 확인

  2. AutoTrace 를 걸어 수행 시 실제 일량을 측정하거나 SQL 트레이스를 걸어 내부 수행 절차상 어느 단계에서 부하를 일으키는지

     눈으로 확인

  3. 문제점이 파악되면 쿼리를 변환하거나 옵티마이저 힌트를 사용해 튜닝을 실시하고, 그것만으로 부족하다고 판단될 때는 인덱스

     조정을 병행한다.

     그래도 만족을 못한다면 반정규화나 집계 테이블을 생성하도록 권고한다.

01.  Explain plan

 - 실행 계획을 확인하고자 할 때, explain plan 명령을 사용한다.

   이 명령어를 사용하려면 먼저 plan_table 을 생성해야 하는데, $ORACLE_HOME/rdbms/admin/utlxplan.sql SQL 을 수행하면

   plan_table 이 만들어 진다.

   하지만, 오라클 10g 부터는 설치 시 기본적으로 sys.plan_table$ 테이블이 만들어진다.

   그리고 이를 가리키는 public synonym 을 기본적으로 생성해 두기 때문에 사용자가 별도로 plan_table 을 만드는 수고를 하지

   않아도 된다.

-- plan_table 확인

SQL> select owner, synonym_name, table_owner, table_name

         from all_synonyms

         where synonym_name = 'PLAN_TABLE';

OWNER  SYNONYM_NAME    TABLE_OWNER   TABLE_NAME

---------- --------------- --------------- ---------------

PUBLIC   PLAN_TABLE   SYS   PLAN_TABLE$

-- 해당 쿼리에 대한 실행 계획 생성

SQL > explain plan for

          select * from emp where empno = 7900;

Explained.

-- 실행계획 확인

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |     |   1 |  38 |   1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |   1 |  38 |   1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN    | PK_EMP |   1 |     |   0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

02. AutoTrace

SQL> set autot on             

SQL> select *

         from scott.emp where empno = 7900;

     EMPNO ENAME      JOB       MGR HIREDATE    SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7900 JAMES      CLERK      7698 03-DEC-81    950   30

Execution Plan

----------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |     |   1 |  38 |   1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |   1 |  38 |   1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN    | PK_EMP |   1 |     |   0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7900)

Statistics

----------------------------------------------------------

 0  recursive calls

 0  db block gets

 2  consistent gets

 0  physical reads

 0  redo size

769  bytes sent via SQL*Net to client

409  bytes received via SQL*Net from client

 1  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

 1  rows processed

 ① set autotrace on

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

 ② set autotrace on explain

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

 ③ set autotrace on statistics 

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

 ④ set autotrace traceonly

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

 ⑤ set autotrace traceonly explain

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

 ⑥ set autotrace traceonly statistics

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

 ※ ① ~ ③ 은 수행결과를 출력해야 하므로 쿼리를 실제 수행한다.

     ④, ⑥ 는 실행통계를 보여줘야 하므로 쿼리를 실제 수행한다.

     ⑤ 는 실행계획만 출력하면 되므로 쿼리를 실제 수행하지 않는다. SQL*Plus 에서 실행 계획을 가장 쉽고 빠르게 확인해 

     볼 수 있는 방법이다.

     AutoTrace 기능을 실행계획 확인 용도로만 사용한다면 plan_table 만 생성돼 있으면 된다.

     하지만 실행통계까지 함께 확인하려면 v_$sesstat, v_$statname, v_$mystat  뷰에 대한 읽기 권한이 필요하다.

     따라서 dba, select_catalog_role 등의 롤을 부여받지 않은 일반사용자들에게는 별도의 권한 설정이 필요하다.

-- plustrace 롤 생성 스크립트 수행

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

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.

-- plustrace 롤을 scott 계정에 부여

SQL> grant plustrace to scott;

Grant succeeded.

-- session 정보 확인

SELECT USERNAME,

       OSUSER||'@'||TERMINAL "OSUser@Terminal",

       PROGRAM,

       STATUS,

       LOGON_TIME LoginTIM,

       SID,

       SERIAL#,

       PADDR OSPID

FROM V$SESSION

WHERE USERNAME IS NOT NULL

AND USERNAME NOT IN('SYSMAN', 'DBSNMP');

USERNAME   OSUser@Terminal PROGRAM    STATUS   LOGINTIM      SID    SERIAL# OSPID

---------- --------------- ------------------------------------------------ -------- --------- ---------- ---------- --------

SYS   oracle@pts/0    sqlplus@localhost.localdomain (TNS V1-V3)    ACTIVE   18-APR-14      145 171 837C5774

-- statistics 옵션 활성화

SQL> set autotrace on statistics

USERNAME   OSUser@Terminal PROGRAM    STATUS   LOGINTIM      SID    SERIAL# OSPID

---------- --------------- ------------------------------------------------ -------- --------- ---------- ---------- --------

SYS   oracle@pts/0    sqlplus@localhost.localdomain (TNS V1-V3)    INACTIVE 18-APR-14      143  34 837C5774

SYS   oracle@pts/0    sqlplus@localhost.localdomain (TNS V1-V3)    ACTIVE   18-APR-14      145 171 837C5774

※ statistics 모드로 AutoTrace 를 활성화하면 통계정보를 수집하는 세션을 하나 더 만들어서 해당 세션에서 통계를 수행한다.

-- explain 옵션 활성화

SQL> set autotrace on explain

USERNAME   OSUser@Terminal PROGRAM    STATUS   LOGINTIM      SID    SERIAL# OSPID

---------- --------------- ------------------------------------------------ -------- --------- ---------- ---------- --------

SYS   oracle@pts/0    sqlplus@localhost.localdomain (TNS V1-V3)    ACTIVE   18-APR-14      145 171 837C5774

03. SQL 트레이스

 (1) 자기 세션에 트레이스 걸기

-- 자기 세션에 트레이스 설정

SQL> alter session set sql_trace = true;

ERROR:

ORA-01031: insufficient privileges

※ 에러 발생 시, 권한 부족 이므로 system 계정으로 접속 후

    SQL> grant alter session to scott;

    명령어로 해당 계정에 alter session 권한을 준 후에 다시 설정한다.

SQL> select * from emp where empno = 7900;

SQL> select * from dual;

SQL> alter session set sql_trace = false;

-- USER_DUMP_DEST 경로로 이동 후 가장 최근에 생성된 .trc 파일 검색

$ cd /ora01/app/oracle/diag/rdbms/orcl/ORA11G/trace

$ ls -alrt

-rw-r-----  1 oracle oinstall     104  4월 18 07:43 ORA11G_ora_4155.trm

-rw-r-----  1 oracle oinstall    3177  4월 18 07:43 ORA11G_ora_4155.trc

-- 해당 trace 파일을 tkprof 를 사용해 포맷팅

$ tkprof ORA11G_ora_4155.trc Trace_01.txt sys=no

※ sys=no 옵션은 SQL 을 파싱하는 과정에서 내부적으로 수행되는 Recursive SQL 을 제외시킨다.

-- 포맷팅한 파일을 vi 편집기로 연다

>> select * from emp where empno = 7900

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.04          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          2          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.00       0.04          0          2          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=38 card=1)

      1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73182)

>> select * from dual

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          3          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)

트레이스 결과 분석

항목	설명
call	커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call 로 나누어 각각에 대한 통계정보를 보여준다.   - Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계   - Execute : 커서의 실행 단계에 대한 통계   - Fetch : 레코드를 실제로 Fetch 하는 데 대한 통계
count	Parse, Execute, Fetch 각 단계가 수행된 횟수
cpu	현재 커서가 각 단계에서 사용한 cpu time
elapsed	현재 커서가 각 단계를 수행하는 데 소요된 시간
disk	디스크로부터 읽은 블록 수
query	Consistent 모드에서 읽은 버퍼 블록 수
current	Current 모드에서 읽은 버퍼 블록 수
rows	각 단계에서 읽거나 갱신한 처리 건수

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

* db block gets                               = current

* consistent gets                            = query

* physical reads                             = disk

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

* rows processed                           = fetch rows

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

※ 옵션 별 설명

 - cr : 블록 읽기

 - pr : 디스크 블록 읽기

 - pw : 디스크 블록 쓰기

 - time : 소요시간

이벤트 트레이스

SQL> alter session set events '10046 trace name context forever, level 1';

SQL> alter session set events '10046 trace name context off';

-- Level 1

SQL ID: 8am3c6wqp3upw

Plan Hash: 2949544139

select *

from

 emp where empno = 7900

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          2          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=38 card=1)

      1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73182)

-- Level 4 ( Level 1 단계에서는 지원하지 않는 Bind 변수 정보 출력)

SQL ID: 8am3c6wqp3upw

Plan Hash: 2949544139

select *

from

 emp where empno = 7900

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        2      0.00       0.00          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        4      0.00       0.00          0          4          0           2

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        8      0.00       0.00          0          4          0           2

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=38 card=1)

      1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73182)

-- Level 8 (Level 4 에서 지원하지 않는 대기 이벤트 정보 출력)

SQL ID: 8am3c6wqp3upw

Plan Hash: 2949544139

select *

from

 emp where empno = 7900

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      3      0.00       0.00          0          0          0           0

Fetch        6      0.00       0.00          0          6          0           3

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       12      0.00       0.00          0          6          0           3

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=38 card=1)

      1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73182)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       3        0.00          0.00

  SQL*Net message from client                     3       11.57         19.09

-- Level 12 (Level 4 에서 지원하지 않는 대기 이벤트 정보 출력)

SQL ID: 8am3c6wqp3upw

Plan Hash: 2949544139

select *

from emp 

where empno = 7900

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        4      0.00       0.00          0          0          0           0

Execute      4      0.00       0.00          0          0          0           0

Fetch        8      0.00       0.00          0          8          0           4

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       16      0.00       0.00          0          8          0           4

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=38 card=1)

      1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73182)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       6        0.00          0.00

  SQL*Net message from client                     6       11.57         38.64

※ 10046 트레이스를 걸 때 레벨을 4 이상으로 설정하면 트레이스 파일이 매우 급격하게 커지므로 주의해서 사용해야 한다.

 Elapsed Time = CPU time + Wait time

  - Elapsed time = CPU time + Wait time

                        = Response 시점 - Call 시점

  - Elapsed time = Call 단위로 측정이 이루어지고, 사용자(App or WAS)로부터 데이터베이스 Call 을 받은 순간부터 

     Response 를 보내는 순간까지의 소요시간을 말하며, Response 시점에서 Call 시점을 차감해서 구한다.

     예를 들어, 13초에 Call 을 보냈는데 25초에 Response 를 받았다면 Call Elapsed time 은 12초가 된다.

  - Call 발생 횟수

   ● SELECT 문 : Parse Call + Execute Call + Fetch Call

   ● DML 문 : Parse Call + Execute Call

  - 특히, SELECT 문에서 다량의 데이터를 사용자에게 전송할 때는 Fetch Call 이 '전송 레코드 건수 / ArrarySize' 만큼 여러 번

     발생한다. 

     따라서 하나의 SQL을 수행할 때의 Total Elapsed time 은, 수행 시 발생하는 모든 Call 의 Elapsed time 을 더해서 구한다.

  - one-row fetch 

   : SQL*Plus 등에서 첫 번째 Fetch 는 ArraySize 와 무관하게 한 건만 가져오는 것

  - SQL*Net message from client 대기 이벤트

   : Idle 이벤트로서 오라클 서버 프로세스가 사용자에게 결과를 전송하고 다음 Fetch Call 이 올 때까지 대기한 시간을 더한값.

     오라클 서버 입장에서는 할 일 없이 대기한 시간이고, App 와 Network 구간에서 소모된 시간이다.

     커넥션을 맺은 상태에서 쿼리와 쿼리 수행 사이 thinking time이 긴 애플리케이션이나, 서버로부터 데이터를 Fetch 하고

     클라이언트 내부적으로 많은 연산을 수행한 후에 다음 Fetch Call 을 날리는 배치 프로그램에서 특히 이 값이 크게 나타난다.

  - SQL*Net message to client 대기 이벤트

   : Idle 이벤트로서 클라이언트에게 메시지를 보냈는데, 클라이언트가 너무 바쁘거나 네트워크 부하 때문에 메시지를 잘 받았다는

     신호가 정해진 시간보다 늦게 도착하는 경우에 발생되는 대기이벤트 이다.

  - db file sequential read 대기 이벤트

   : Single Block Read 방식으로 디스크 블록을 읽을 때 발생하는 대기 이벤트

  - SQL*Net more data to client

   : 클라이언트에게 전송할 데이터가 남았는데 네트워크 부하 때문에 바로 전송하지 못할 때 발생하는 대기 이벤트

     오라클 서버는 내부적으로 SDU(Session Data Unit) 단위로 패킷을 나누어 전송한다.

     하나의 SDU 단위 패킷을 전송했는데 잘 받았다는 신호가 정해진 시간보다 늦게 도착하면 대기가 발생하는데, 그때 발생하는

     대기 이벤트가 SQL*Net more data to client 이다.

 (2) 다른 세션에 트레이스 걸기

 -- DBMS_SYSTEM.SET_EV

EXEC DBMS_SYSTEM.SET_EV(125, 9, 10046, 8, '');

EXEC DBMS_SYSTEM.SET_EV(125, 9, 10046, 0, '');

-- DBMS_MONITOR

BEGIN

    DBMS_MONITOR.SESSION_TRACE_ENABLE(

        SESSION_ID => 125,

        SERIAL_NUM => 9,

        WAITS => TRUE,

        BINDS => TRUE

    );

END;

/

BEGIN

    DBMS_MONITOR.SESSION_TRACE_DISABLE(

        SESSION_ID => 125,

        SERIAL_NUM => 9

    );

END;

/

-- ORADEBUG

# ORADEBUG SETOSPID 4844

# ORADEBUG UNLIMIT -- 트레이스 파일의 크기 제한을 없앰

# ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8

# ORADEBUG TRACEFILE_NAME -- 트레이스 파일 이름 확인

# ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF

# ORADEBUG CLOSE_TRACE

-- 시스템 전체에 트레이스 설정(심각한 부하를 일으키므로 사용할 일이 없다)

ALTER SYSTEM SET SQL_TRACE = TRUE;

ALTER SYSTEM SET SQL_TRACE = FALSE;

 (3) Service, Module, Action 단위로 트레이스 걸기

-- 특정 Service 에 트레이스 설정 -- 

SQL> show parameter service_name

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

service_names     string ORCL

SQL> SELECT SID,

              SERVICE_NAME,

              MODULE,

              ACTION

         FROM V$SESSION

         WHERE SERVICE_NAME <> 'SYS$BACKGROUND';

       SID SERVICE_NAME MODULE ACTION

---------- -------------------- ------------------------------------------------ --------------------------------

22 SYS$USERS sqlplus@localhost.localdomain (TNS V1-V3)

       125 SYS$USERS sqlplus@localhost.localdomain (TNS V1-V3)

       140 SYS$USERS sqlplus@localhost.localdomain (TNS V1-V3)

-- 트레이스 설정

BEGIN

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(

        SERVICE_NAME => 'SYS$USERS',

        MODULE_NAME => DBMS_MONITOR.ALL_MODULES,

        ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS,

        WAITS => TRUE,

        BINDS => TRUE

    );

END;

/

-- 트레이스 설정 확인

SQL > SELECT PRIMARY_ID SERVICE_NAME,

                       QUALIFIER_ID1 MODULE,

                       QUALIFIER_ID2 ACTION,

                       WAITS,

                       BINDS

          FROM DBA_ENABLED_TRACES; 

SERVICE_NAME     MODULE      ACTION       WAITS BINDS

-------------------- ------------------------------------------------ -------------------------------- ----- -----

SYS$USERS       TRUE  TRUE

-- 트레이스 해제

BEGIN

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(

        SERVICE_NAME => 'SYS$USERS',

        MODULE_NAME => DBMS_MONITOR.ALL_MODULES,

        ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS

    );

END;

/

-- 특정 Module 에만 트레이스 설정 --

-- MODULE, ACTION 이름 변경 (접속한 세션의 MODULE, ACTION 변경)

BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(

        MODULE_NAME => 'emp manager',

        ACTION_NAME => 'select emp'

    );

END;

/

-- MODULE, ACTION 이름 확인

SELECT SID,

       SERVICE_NAME,

       MODULE,

       ACTION

FROM V$SESSION

WHERE SERVICE_NAME <> 'SYS$BACKGROUND';

       SID SERVICE_NAME MODULE ACTION

---------- -------------------- ------------------------------------------------ --------------------------------

22 SYS$USERS sqlplus@localhost.localdomain (TNS V1-V3)

       125 SYS$USERS sqlplus@localhost.localdomain (TNS V1-V3)

       140 SYS$USERS emp manager select emp

-- 특정 MODULE 트레이스 설정

BEGIN

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(

        SERVICE_NAME => 'SYS$USERS',

        MODULE_NAME => 'emp manager',

        ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS,

        WAITS => TRUE,

        BINDS => TRUE

    );

END;

/

-- 트레이스 설정 확인

SQL > SELECT PRIMARY_ID SERVICE_NAME,

                       QUALIFIER_ID1 MODULE,

                       QUALIFIER_ID2 ACTION,

                       WAITS,

                       BINDS

          FROM DBA_ENABLED_TRACES; 

SERVICE_NAME     MODULE      ACTION       WAITS BINDS

-------------------- ------------------------------------------------ -------------------------------- ----- -----

SYS$USERS     emp manager       TRUE  TRUE

-- 특정 MODULE 트레이스 해제

BEGIN

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(

        SERVICE_NAME => 'SYS$USERS',

        MODULE_NAME => 'emp manager',

        ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS

    );

END;

/

-- ▣ Action 별 트레이스 설정

-- Action 변경

DBMS_APPLICATION_INFO.SET_ACTION('update emp');

-- Action 별 트레이스 설정

BEGIN

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(

        SERVICE_NAME => 'SYS$USERS',

        MODULE_NAME => 'emp manager',

        ACTION_NAME => 'update emp',

        WAITS => TRUE,

        BINDS => TRUE

    );

END;

/

-- Action 별 트레이스 해제

BEGIN

    DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(

        SERVICE_NAME => 'SYS$USERS',

        MODULE_NAME => 'emp manager',

        ACTION_NAME => 'update emp'

    );

END;

/

-- ▣ client_identifier 별 트레이스 설정

-- client_identifier 설정

EXEC DBMS_SESSION.SET_IDENTIFIER('oraking');

-- client_identifier 별 트레이스 설정

BEGIN

    DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(

        CLIENT_ID => 'oraking',

        WAITS => TRUE,

        BINDS => TRUE

    );

END;

/

-- client_identifier 별 트레이스 해제

BEGIN

    DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(

        CLIENT_ID => 'oraking'

    );

END;

/

※ 지금까지 설명한 기능을 효과적으로 사용하려면 모든 프로그램 모듈 수행 전에 dbms_application_info.set_module 프로시저를

    한 번씩 호출하도록 프로그램을 변경해야 한다.

    데이터베이스 Call이 한번씩 더 발생하기는 하지만 I/O 작업이 전혀 없고 세션 Global 변수 값만 살짝 바꿔주기 때문에 부하가

    거의 없다고 할 수 있다.

    SQL 단위로 set_action 을 자주 수행하는 것은 시스템에 다소 부하를 줄 수 있으므로 SQL 수행이 빈번한 OLTP 성 프로그램보다

    배치 프로그램에만 적용하는 게 좋을 듯 싶다.

    어떤 단위로 정보 수집이 진행중인지 확인하려면 dba_enabled_aggregations 뷰를 이용하고, service, module, action 별로

    수집된 수행통계를 확인하려면 v$serv_mod_act_stats 뷰를 이용하면 된다.

    v$session 에서 client_identifier 별로 수행통계를 수집할 때는 client_id_stat_enable 과 client_id_stat_diable 프로시저를 이용

    하고, 수집된 수행통계를 확인하려면 v$client_stats 뷰를 이용한다.

04. DBMS_XPLAN 패키지

 (1) 예샹 실행계획 출력

select plan_table_output

from table(dbms_xplan.display('plan_table', null, 'serial');

- 첫 번째 인자에는 실행계획이 저장된 plan table 명을 입력하고, 두 번째 인자에는 statement_id 를 입력하면 된다.

  두 번째 옵션이 NULL 일 때는 가장 마지막 explain plan 명령에 사용했던 쿼리의 실행계획을 보여준다.

  병렬 쿼리에 대한 실행계획을 수집했다면 @?/rdbms/admin/utlxplp 스크립트를 수행함으로써 병렬 항목에 대한 정보까지

  볼 수 있다.

ex)

explain plan for

select *

from emp e, dept d

where d.deptno = e.deptno

and e.sal >= 1000;

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

(ID, OPERATION, NAME 컬럼만 출력)

------------------------------------------------

| Id  | Operation     | Name    |

------------------------------------------------

|   0 | SELECT STATEMENT     |       |

|   1 |  MERGE JOIN     |       |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |

|   3 |    INDEX FULL SCAN     | PK_DEPT |

|   4 |   SORT JOIN     |       |

|   5 |    TABLE ACCESS FULL     | EMP     |

------------------------------------------------

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

(ID, OPERATION, NAME, ROWS, BYTES, COST, TIME, PREDICATE 컬럼 및 옵션 출력)

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4 (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

   5 - filter("E"."SAL">=1000)

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

(ID, OPERATION, NAME, ROWS, BYTES, COST, TIME, PREDICATE 컬럼 및 옵션 출력)

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4 (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

   5 - filter("E"."SAL">=1000)

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

(PARTITION, PARALLEL, PREDICATE, PROJECTION, ALLIAS, REMOTE, NOTE 등 모든 옵션 출력)

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4 (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / D@SEL$1

   3 - SEL$1 / D@SEL$1

   5 - SEL$1 / E@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

   5 - filter("E"."SAL">=1000)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],

       "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],

       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],

       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]

   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]

   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],

       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],

       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]

   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],

       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],

       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

select * from table(dbms_xplan.display('PLAN_TABLE', NULL, 'OUTLINE'));

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

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4 (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")

      USE_MERGE(@"SEL$1" "E"@"SEL$1")

      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")

      FULL(@"SEL$1" "E"@"SEL$1")

      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.1')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

   5 - filter("E"."SAL">=1000)

select * from table(dbms_xplan.display('PLAN_TABLE', NULL, 'ADVANCED'));

(ALL 과 OUTLINE 을 함꼐 사용)

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6 (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4 (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / D@SEL$1

   3 - SEL$1 / D@SEL$1

   5 - SEL$1 / E@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")

      USE_MERGE(@"SEL$1" "E"@"SEL$1")

      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")

      FULL(@"SEL$1" "E"@"SEL$1")

      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.1')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

   5 - filter("E"."SAL">=1000)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],

       "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],

       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],

       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]

   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]

   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],

       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],

       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]

   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],

       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],

       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

 (2) 캐싱된 커서의 실제 실행계획 출력

  - 커서란? 

    하드 파싱 과정을 거쳐 메모리에 적재된 SQL 과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 

    SQL Area를 뜻함

  - 오라클은 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰 및 v$sql_plan, v$sql_plan_statistics_all 이 있다.

SQL> set serveroutput off 

(dbms_output.disable 프로시저를 호출하는 커서의 sql_id 를 찾지 않게 하기 위해서)

SQL> select *

         from emp e, dept d

         where d.deptno = e.deptno;

SQL> select prev_sql_id, prev_child_number

         from v$session

         where sid=userenv('sid') 

         and username is not null

         and prev_hash_value <> 0;

PREV_SQL_ID   PREV_CHILD_NUMBER

------------- -----------------

43j51v1k33jnp      0

SQL> select * from table(dbms_xplan.display_cursor('&sql_id', &child_no, 'BASIC ROWS BYTES COST PREDICATE'));

(라이브러리 캐시에 현재 캐싱돼 있는 SQL 커서의 실제 실행계획과, 실행계획을 만들면서 예상했던 Rows, Bytes, Cost, Time

 정보를 보여준다.)

old   1: select * from table(dbms_xplan.display_cursor('&sql_id', &child_no, 'BASIC ROWS BYTES COST PREDICATE'))

new   1: select * from table(dbms_xplan.display_cursor('43j51v1k33jnp', 0, 'BASIC ROWS BYTES COST PREDICATE'))

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

 select *   from emp e, dept d       where d.deptno = e.deptno

Plan hash value: 844388907

-----------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |       |       |     6 (100)|

|   1 |  MERGE JOIN     |       |    14 |   812 |     6 (17)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)|

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)|

|*  4 |   SORT JOIN     |       |    14 |   532 |     4 (25)|

|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)|

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

(3) 캐싱된 커서의 Row Source 별 수행 통계 출력

 - SQL 문에 gather_plan_statistics 힌트 사용

 - 시스템 또는 세션 레벨에서 statistics_level 파라미터를 all 로 설정

 - _rowsource_execution_statistics 파라미터를 true 로 설정하거나, SQL 트레이스를 걸어도 Row Source 별 수행 통계 수집

SQL> set serveroutput off

SQL> select /*+ gather_plan_statistics */ *

         from emp e, dept d

         where d.deptno = e.deptno

         and e.sal >= 1000;

-- RowSource_Plan.sql

set lines 140

set pages 3000

col "Row Source Operation" for a80

select last_output_rows "Rows",

       LPAD(OPERATION||' '||NVL2(OPTIONS, OPTIONS||' ', '')||NVL2(OBJECT_NAME, OBJECT_NAME||' ', '')||'(cr='||LAST_CR_BUFFER_GETS||

       ' pr='||LAST_DISK_READS||' time='||LAST_ELAPSED_TIME||' us)', LENGTH(OPERATION||' '||NVL2(OPTIONS, OPTIONS||' ', '')||

       NVL2(OBJECT_NAME, OBJECT_NAME||' ', '')||'(cr='||LAST_CR_BUFFER_GETS||' pr='||LAST_DISK_READS||' time='||LAST_ELAPSED_TIME||' us)')

       +DEPTH) "Row Source Operation"

from v$sql_plan_statistics_all

where sql_id = '&sql_id'

and child_number = &child_no

order by id;

      Rows Row Source Operation

---------- --------------------------------------------------------------------------------

  SELECT STATEMENT (cr= pr= time= us)

   MERGE JOIN (cr= pr= time= us)

    TABLE ACCESS BY INDEX ROWID DEPT (cr= pr= time= us)

     INDEX FULL SCAN PK_DEPT (cr= pr= time= us)

    SORT JOIN (cr= pr= time= us)

     TABLE ACCESS FULL EMP (cr= pr= time= us)

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('43j51v1k33jnp', 0, 'IOSTATS'));

 select *   from emp e, dept d       where d.deptno = e.deptno

Plan hash value: 844388907

---------------------------------------------------------

| Id  | Operation     | Name    | E-Rows |

---------------------------------------------------------

|   0 | SELECT STATEMENT     |       | |

|   1 |  MERGE JOIN     |       |     14 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |      4 |

|*  4 |   SORT JOIN     |       |     14 |

|   5 |    TABLE ACCESS FULL     | EMP     |     14 |

---------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('43j51v1k33jnp', 0, 'MEMSTATS'));

select *   from emp e, dept d       where d.deptno = e.deptno

Plan hash value: 844388907

------------------------------------------------------------------------------------

| Id  | Operation     | Name    | E-Rows |  OMem |  1Mem |  O/1/M   |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       | | | |   |

|   1 |  MERGE JOIN     |       |     14 | | |   |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 | | |   |

|   3 |    INDEX FULL SCAN     | PK_DEPT |      4 | | |   |

|*  4 |   SORT JOIN     |       |     14 |  2048 |  2048 |     1/0/0|

|   5 |    TABLE ACCESS FULL     | EMP     |     14 | | |   |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('43j51v1k33jnp', 0, 'IOSTATS MEMSTATS'));

 select *   from emp e, dept d       where d.deptno = e.deptno

Plan hash value: 844388907

------------------------------------------------------------------------------------

| Id  | Operation     | Name    | E-Rows |  OMem |  1Mem |  O/1/M   |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       | | | |   |

|   1 |  MERGE JOIN     |       |     14 | | |   |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 | | |   |

|   3 |    INDEX FULL SCAN     | PK_DEPT |      4 | | |   |

|*  4 |   SORT JOIN     |       |     14 |  2048 |  2048 |     1/0/0|

|   5 |    TABLE ACCESS FULL     | EMP     |     14 | | |   |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('43j51v1k33jnp', 0, 'ALLSTATS'));

 select *   from emp e, dept d       where d.deptno = e.deptno

Plan hash value: 844388907

------------------------------------------------------------------------------------

| Id  | Operation     | Name    | E-Rows |  OMem |  1Mem |  O/1/M   |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       | | | |   |

|   1 |  MERGE JOIN     |       |     14 | | |   |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 | | |   |

|   3 |    INDEX FULL SCAN     | PK_DEPT |      4 | | |   |

|*  4 |   SORT JOIN     |       |     14 |  2048 |  2048 |     1/0/0|

|   5 |    TABLE ACCESS FULL     | EMP     |     14 | | |   |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('43j51v1k33jnp', 0, 'ALLSTATS LAST'));

(format 옵션에 last 를 추가해주면 마지막 수행했을 때의 일량을 보여준다.)

 select *   from emp e, dept d       where d.deptno = e.deptno

Plan hash value: 844388907

------------------------------------------------------------------------------------

| Id  | Operation     | Name    | E-Rows |  OMem |  1Mem | Used-Mem |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       | | | |   |

|   1 |  MERGE JOIN     |       |     14 | | |   |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 | | |   |

|   3 |    INDEX FULL SCAN     | PK_DEPT |      4 | | |   |

|*  4 |   SORT JOIN     |       |     14 |  2048 |  2048 | 2048 (0)|

|   5 |    TABLE ACCESS FULL     | EMP     |     14 | | |   |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

       filter("D"."DEPTNO"="E"."DEPTNO")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

05. V$SYSSTAT

 - v$sysstat : 인스턴스 기동 후 현재까지 누적된 수행 통계치를 시스템 레벨로 확인

 - v$sesstat : 인스턴스 기동 후 현재까지 누적된 수행 통계치를 개별 세션별로 확인

 - v$mystat : 인스턴스 기동 후 현재까지 누적된 수행 통계치를 현재 접속한 본인 세션에 대해 확인

-- 각종 통계치 시스템 레벨로 확인

SELECT NAME,

             VALUE

FROM V$SYSSTAT

WHERE STATISTIC# IN (8, 63, 67, 72, 169, 555, 556, 557, 565, 566);

NAME      VALUE

---------------------------------------------------------------- ----------

recursive calls     280874

db block gets       7665

consistent gets     160160

physical reads      11790

redo size    1930548

bytes sent via SQL*Net to client       4441

bytes received via SQL*Net from client       1229

SQL*Net roundtrips to/from client  9

sorts (memory)       9169

sorts (disk)  0

 (1) 시스템 수행 통계 수집 및 분석

-- TEST 할 테이블 생성

CREATE TABLE SESS_STAT

AS

SELECT 1 NO,

       STATISTIC#,

       VALUE

FROM V$SESSTAT

WHERE SID = 125;

-- 원래 세션에서 배치 JOB 수행

-- BIG TABLE 생성

CREATE TABLE BIG_TABLE AS

SELECT ROWNUM ID, A.*

FROM ALL_OBJECTS A

WHERE 1=0;

-- 데이터 삽입

DECLARE L_CNT NUMBER;

 L_ROWS NUMBER := &1;

 BEGIN 

INSERT /*+ APPEND */

  INTO BIG_TABLE

SELECT ROWNUM,

       A.*

  FROM ALL_OBJECTS A

 WHERE ROWNUM <= &1;

 L_CNT := SQL%ROWCOUNT;

 COMMIT;

 WHILE(L_CNT < L_ROWS) LOOP 

INSERT /*+ APPEND */

  INTO BIG_TABLE

SELECT ROWNUM+L_CNT,

       OWNER,

       OBJECT_NAME,

       SUBOBJECT_NAME,

       OBJECT_ID,

       DATA_OBJECT_ID,

       OBJECT_TYPE,

       CREATED,

       LAST_DDL_TIME,

       TIMESTAMP,

       STATUS,

       TEMPORARY,

       GENERATED,

       SECONDARY,

       NAMESPACE,

       EDITION_NAME

  FROM BIG_TABLE

 WHERE ROWNUM <= L_ROWS - L_CNT;

 L_CNT := L_CNT + SQL%ROWCOUNT;

 COMMIT;

END LOOP;

END;

 /

-- 배치 작업 후에 세션 통계치 추가

INSERT INTO SESS_STAT

SELECT 2 NO,

       STATISTIC#,

       VALUE

FROM V$SESSTAT

WHERE SID = 125;

COMMIT;

-- 배치 작업 수행시 생성된 정보 확인

SELECT B.STATISTIC# STAT#,

       B.NAME,

       (B.VALUE-A.VALUE) DELTA_VALUE

FROM ( SELECT N.STATISTIC#,

              N.NAME,

              B.VALUE

       FROM V$STATNAME N, SESS_STAT B

       WHERE B.STATISTIC# = N.STATISTIC#

       AND B.VALUE > 0

       AND B.NO = 2

      ) B, SESS_STAT A

WHERE A.NO = 1

AND A.STATISTIC# = B.STATISTIC#

AND (B.VALUE - A.VALUE) > 0

ORDER BY DELTA_VALUE DESC;

     STAT# NAME    DELTA_VALUE

---------- ---------------------------------------------------------------- -----------

51 cell physical IO interconnect bytes       38043648

50 physical write total bytes       26812416

       169 redo size       12298760

       172 redo size for direct writes       12202564

90 physical write bytes       12148736

47 physical read total bytes       11231232

77 physical read bytes 7135232

32 session pga memory max 5111808

26 session uga memory max 4048960

       191 file io wait time 2011719

31 session pga memory 1048576

25 session uga memory 430992

       352 table scan rows gotten 236048

       399 index scans kdiixs1  98618

       539 buffer is pinned count  83158

11 session logical reads  55581

68 consistent gets from cache  52901

67 consistent gets  52901

22 non-idle wait count  52779

       265 no work - consistent read gets  49646

69 consistent gets from cache (fastpath)  48424

       567 sorts (rows)  33055

       261 undo change vector size  21868

       353 table scan blocks gotten   6966

       540 buffer is not pinned count   6550

       354 table fetch by rowid   6102

       398 index fetch by key   5623

8 recursive calls   4623

63 db block gets   2680

 (2) Ratio 기반 성능 분석

  - 자주 분석되는 항목들

   ● Buffer Nowait %

   ● Redo No Wait %

   ● Buffer Hit %

   ● Latch Hit %

   ● In-memory Sort %

   ● Library Hit %

   ● Soft Parse %

   ● Execute to Parse %

   ● Parse CPU to Parse Elapsed %:

   ● % Non-Parse CPU

   ● Memory Usage %

   ● % SQL with executions > 1

   ● % Memory for SQL w/exec > 1

  ▣ Buffer Nowait %

   - 버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율

SELECT ROUND(100 * (1-BFWT/GETS), 2) "Buffer Nowait %"

FROM (SELECT SUM(COUNT) BFWT FROM V$WAITSTAT),

     (SELECT VALUE GETS FROM V$SYSSTAT WHERE NAME = 'session logical reads')

;     

Buffer Nowait %

---------------

 99.99

  ▣ Redo NoWait %

   - Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율.

     이 비율이 낮다면, 로그 스위칭이 느리거나 너무 자주 발생함을 의미한다.

     로그 스위칭 횟수가 문제라면 Redo 로그 파일 크기를 증가시킬 필요가 있다.

     로그 스위칭이 자주 발생하지 않는데도 이 항목이 낮은 수치를 보인다면, I/O 서브 시스템이 느린 것이 원인일 것이다.

     Redo 로그 파일을 덜 바쁜 디스크 또는 Redo 로그 만을 위한 전용 디스크로 옮기는 것을 고려해야한다.

SELECT ROUND(100 * (1-RLSR/RENT), 2) "Redo NoWait %"

FROM (SELECT VALUE RLSR FROM V$SYSSTAT WHERE NAME = 'redo log space requests'),

     (SELECT VALUE RENT FROM V$SYSSTAT WHERE NAME = 'redo entries')

;

Redo NoWait %

-------------

 100

   - LGWR write

    LGWR 프로세스가 Redo 로그 버퍼를 Redo 로그에 기록하는 시점

     ① 3초마다 DBWR 프로세스로부터 신호를 받을 때

     ② 로그 버퍼의 1/3이 차거나 기록된 Redo 레코드량이 1MB 를 넘을 때

     ③ 사용자가 커밋 또는 롤백 명령을 날릴 때

   - ①, ② 는 LGWR 에 의해 비동기 방식으로 진행되기 때문에 트랜잭션이 Redo 레코드를 기록하면서 작업을 계속 진행하는 데에

     영향을 미치지 않는다.

     즉, LGWR 가 Redo 로그 버퍼를 로그에 기록하는 동안에도 서버 프로세스는 새로운 Redo 엔트리를 로그 버퍼에 기록할 수 있다.

     로그 버퍼가 로그 블록 단위(버퍼 캐시 블록 size 와는 다르며 OS 의 최소 I/O 단위에 의해 결정됨) 의 순환 방식으로 관리되기

     때문에 가능한 일이다.

     단, 이때도 LGWR 작업 속도보다 Redo 생성이 더 빨리 진행되면 Redo 엔트리를 기록할 공간이 부족하게 되므로 log buffer

     space 대기 이벤트가 발생할 수 있다.

     반면, ③ 은 Redo 레코드가 Redo 로그에 기록되었음이 보장되어야만 트랜잭션을 마칠 수 있기 때문에 동기 방식으로 진행된다.

     따라서 LGWR가 작업을 완료할 때까지 기다려야 하고, 이때 발생하는 것이 log file sync 대기 이벤트이다.

     커밋을 했는데 어떤 이유에서건 LGWR 가 작업을 빨리 마치치 못하면 1초 간격으로 log file sync 이벤트를 발생시키면서 계속

     대기하게 된다.

   - redo log space requests 는 말 그대로, Redo 로그 공간에 대한 요청이 발생하는 것을 의미하며, 이는 로그 스위치가 일어날때

     생기는 현상이다.

     로그 스위치가 일어나면 짧은 순간이지만 서버 프로세스는 LGWR 프로세스가 현재 Redo 로그에 쓰기를 완료하고 새로운 Redo

     로그를 오픈할 때까지 Redo 를 생성하지 못하고 계속 기다려야 하기 때문이다.

     그리고 이때 발생하는 대기 이벤트가 log file switch completion 이다.

     로그 스위치가 끝나면 그때까지 대기했던 서버 프로세스들이 동시에 Redo 레코드를 기록하므로 로그 버퍼가 금방 부족해져

     log buffer space 대기 이벤트가 발생할 가능성이 높다.

  ▣ Buffer Hit %

   - 디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율

SELECT ROUND(100 * (1-(PHYR-PHYRD-NVL(PHYRD1, 0))/GETS), 2) "Buffer Hit %"

FROM (SELECT VALUE PHYR FROM V$SYSSTAT WHERE NAME = 'physical reads'),

     (SELECT VALUE PHYRD FROM V$SYSSTAT WHERE NAME = 'physical reads direct'),

     (SELECT VALUE PHYRD1 FROM V$SYSSTAT WHERE NAME = 'physical reads direct (lob)'),

     (SELECT VALUE GETS FROM V$SYSSTAT WHERE NAME = 'session logical reads')

;

Buffer Hit %

------------

       97.13

  ▣ Latch Hit %

   - 래치 경합 없이 첫 번째 시도에서 곧바로 래치를 획득한 비율

SELECT ROUND(100 * (1-SUM(MISSES)/SUM(GETS)), 2) "Latch Hit %"

FROM V$LATCH;

Latch Hit %

-----------

      99.99

  ▣ Library Hit %

   - 이 항목부터 '% Non-Parse CPU'까지는 파싱 부하와 관련 있는 측정 항목들이다.

     라이브러리 캐시 히트율은 Get 히트율과 Pin 히트율로 나눌 수 있는데, 여기서는 그 중 Pin 히트율을 표시하고 있다.

     Pin 히트율은 실행 단계와 관련이 있다.

     라이브러리 캐시에 이미 적재된 SQL 커서를 실행하거나 오브젝트 정보를 읽으려 할 때 해당 커서 또는 오브젝트 정보가 힙 

     영역에서 찾아진다면 히트에 성공한 것이다.

     만약 캐시에서 밀려나 찾을 수 없는 경우가 빈번하게 발생한다면 히트율이 낮게 나타나고 그만큼 하드 파싱을 해야하므로 성능이

     떨어지게 된다.

SELECT ROUND(100 * SUM(PINHITS)/SUM(PINS), 2) "Library Cache Pin Hit %"

FROM V$LIBRARYCACHE;

Library Cache Pin Hit %

-----------------------

 93.77

SELECT ROUND(100 * SUM(GETHITS)/SUM(GETS), 2) "Library Cache Get Hit %"

FROM V$LIBRARYCACHE;

Library Cache Get Hit %

-----------------------

 75.55

  ▣ Soft Parse %

   - 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL 을 수행한 비율

     이 비율이 낮다면 바인드 변수를 사용하도록 애플리케이션을 개선해야 한다.

     (전체 Parse Call 횟수 - 하드파싱 횟수) / (전체 Parse Call 횟수) * 100)

SELECT ROUND(100 * (1-HPRS/PRSE), 2) "Soft Parse %"

FROM (SELECT VALUE HPRS FROM V$SYSSTAT WHERE NAME = 'parse count (hard)'),

     (SELECT VALUE PRSE FROM V$SYSSTAT WHERE NAME = 'parse count (total)')

;

Soft Parse %

------------

       94.76

  ▣ Execute to Parse %

   - Parse Call 없이 곧바로 SQL 을 수행한 비율.

     즉, 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율을 말한다.

SELECT ROUND((1-PRSE/EXE)*100, 2) "Execute to Parse %"

FROM (SELECT VALUE PRSE FROM V$SYSSTAT WHERE NAME = 'parse count (total)'),

     (SELECT VALUE EXE FROM V$SYSSTAT WHERE NAME = 'execute count')

;

Execute to Parse %

------------------

    45.95

  ▣ Parse CPU to Parse Elapsed %

   - 파싱 총 소요 시간 중 CPU time 이 차지한 비율.

SELECT DECODE(PRSELA,

              0, TO_NUMBER(NULL),

              ROUND(PRSCPU/PRSELA*100, 2)) "Parse CPU to Parse Elapsed"

FROM (SELECT VALUE PRSELA FROM V$SYSSTAT WHERE NAME = 'parse time elapsed'),

     (SELECT VALUE PRSCPU FROM V$SYSSTAT WHERE NAME = 'parse time cpu')

;

Parse CPU to Parse Elapsed

--------------------------

    27.43

   - 이 수치가 낮다면 Shared Pool 과 라이브러리 캐시에 경합이 많다는 것을 뜻한다.

     대개 하드 파싱 부하 때문이다.

  ▣ Non-Parse CPU %

   - SQL 을 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율

     이 비율이 낮다면 파싱 과정에서 소비되는 CPU time 비율이 높은 것이므로 파싱 부하를 줄이도록 애플리케이션을 개선해야 함.

SELECT DECODE(TCPU,

              0, TO_NUMBER(NULL),

              ROUND(100*(1-(PRSCPU/TCPU)), 2)) "Non-Parse CPU %"

FROM (SELECT VALUE TCPU FROM V$SYSSTAT WHERE NAME = 'CPU used by this session'),

     (SELECT VALUE PRSCPU FROM V$SYSSTAT WHERE NAME = 'parse time cpu')

;   

Non-Parse CPU %

---------------

 88.21

  ▣ In-memory Sort %

   - 전체 소트 수행 횟수에서 In-Memory 소트 방식으로 수행한 비율

SELECT DECODE((SRTM+SRTD),

              0, TO_NUMBER(NULL),

              ROUND(100 * SRTM/(SRTD+SRTM), 2)) "In-memory Sort %"

FROM (SELECT VALUE SRTM FROM V$SYSSTAT WHERE NAME = 'sorts (memory)'),

     (SELECT VALUE SRTD FROM V$SYSSTAT WHERE NAME = 'sorts (disk)')

;   

In-memory Sort %

----------------

    100

  ▣ Memory Usage %

   - Shared Pool 내에서 현재 사용 중인 메모리 비중

SELECT 100*(1-SUM(DECODE(NAME, 'free memory', bytes)) / SUM(BYTES))

FROM V$SGASTAT

WHERE POOL = 'shared pool';

Memory Usage %

--------------

    78.1173721

  ▣ SQL with executions > 1 %

   - 전체 SQL 개수에서 두 번 이상 수행된 SQL 이 차지하는 비중.

     이 값이 낮게 나타난다면 조건절에 바인드 변수를 사용하지 않고 Literal 상수 값을 이용하는 쿼리의 수행빈도가 높은 것을 의미.

  ▣ Memory for SQL w/exec > 1 %

   - 전체 SQL이 차지하는 메모리 중 두 번 이상 수행된 SQL 이 차지하는 메모리 비중.

     이 값이 낮게 나타난다면 조건절에 바인드 변수를 사용하지 않고 Literal 상수 값을 사용하는 쿼리에 의해 Shared Pool 이 

     낭비되고 있음을 의미.

06. V$SYSTEM_EVENT

 - v$system_event : 인스턴스 기동 후 현재까지 누적된 이벤트 발생 현황을 시스템 레벨로 확인

 - v$session_event : 개별 세션별로 누적치를 확인

 - v$session_wait : 세션별로 현재 진행 중이거나 바로 직전에 발생했던 이벤트 정보 확인

-- 현재 발생하는 이벤트 확인

SELECT EVENT,

       WAIT_TIME,

       SECONDS_IN_WAIT,

       STATE,

       P1TEXT||'->'||P1||','||P2TEXT||'->'||P2||','||P3TEXT||'->'||P3 PARAM

FROM V$SESSION_WAIT

WHERE SID = 125;

    WAIT    SECONDS

EVENT     TIME    IN WAIT STATE PARAM

------------------------------ ---------- ---------- ------------------- --------------------------------------------------

SQL*Net message to client       -1   0 WAITED SHORT TIME driver id->1650815232,#bytes->1,->0

● WAIT_TIME >0 : 마지막 대기이벤트를 대기한 시간

● WAIT_TIME = 0 : 이 이벤트를 현재 대기 중

● WAIT_TIME = -1 : 마지막 대기 이벤트를 대기한 시간이 10ms 미만

● WAIT_TIME = -2 : 타이밍이 활성화되지 않음

[페르디안]

완료