0. 상세보고서

상세 보고서를 이용하여 AWR DB 보고서 생성 구간의 DB 상태에 대해 보다 자세한 정보를 파악할 수 있다.
상세 보고서는 속성에 다라 크게 12가지로 분류되며, 각 본류에 속한 단위 보고서는 다음 표와 같다.

항목	보고서
대기 이벤트	Wait Class, Wait Events, Background Wait Events
자원 사용	Time Model Statistics, Operating System Statistics, Service Statistics, Service Wait Class Stats, Instance Activity Stats, Instance Activity Stats - Absolute Values, Instance Activity Stats - Thread Activity, Resource Limit Stats
SQL	SQL ordered by Elapsed Time, SQL ordered by CPU Time, SQL ordered by Gets, SQL ordered by Reads, SQL ordered by Executions, SQL ordered by Parse Calls, SQL ordered by Sharable Memory, SQL ordered by Version Count, SQL ordered by Cluster Wait Time
I/O	Tablespace IO Stats, File IO Stats
(메모리) SGA	Buffer Pool Statistics, Buffer Pool Advisory, Shared Pool Advisory, SGA Target Advisory, Streams Pool Advisory, Java Pool Advisory, Buffer Wait Statistics, Dictionary Cache Stats, Dictionary Cache Stats (RAC), Library Cache Activity, Library Cache Activity (RAC), SGA Memory Summary, SGA breakdown difference
(메모리) PGA	PGA Aggr Summary, PGA Aggr Target Stats, PGA Aggr Target Histogram, PGA Memory Advisory, Process Memory Summary
컨트롤 파일	Instance Recovery Stats
락	Enqueue Activity, Latch Activity, Latch Sleep Breakdown, Latch Miss Sources, Parent Latch Statistics, Child Latch Statistics
언두	Undo Segment Summary, Undo Segment Stats
세그먼트	Segments by Logical Reads, Segments by Physical Reads, Segments by Row Lock Waits, Segments by ITL Waits, Segments by Buffer Busy Waits, Segments by Global Cache Buffer Busy, Segments by CR Blocks Received, Segments by Current Blocks Received
스트림즈	Streams CPU/IO Usage, Streams Capture, Streams Apply, Buffered Queues, Buffered Queue Subscribers, Rule Set
환경설정	init.ora Parameters
RAC	Global Messaging Statistics, Global CR Served Stats, GLOBAL CURRENT Served Stats, Global Cache Transfer Stats

1. Time Model Statistics

AWR DB 보고서 내용


Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time                          3,455,105.0         98.0
DB CPU                                              801,566.7         22.7
parse time elapsed                                  280,625.4          8.0
hard parse elapsed time                             228,768.2          6.5
PL/SQL execution elapsed time                        48,882.2          1.4
inbound PL/SQL rpc elapsed time                      18,706.6           .5
failed parse elapsed time                            12,608.2           .4
hard parse (sharing criteria) elapsed time            7,093.5           .2
failed parse (out of shared memory) elapse            4,039.0           .1
PL/SQL compilation elapsed time                       2,804.6           .1
connection management call elapsed time               2,106.2           .1
sequence load elapsed time                              689.7           .0
repeated bind elapsed time                              336.5           .0
hard parse (bind mismatch) elapsed time                 229.4           .0
Java execution elapsed time                              11.3           .0
DB time                                           3,526,796.1          N/A
background elapsed time                             251,439.3          N/A
background cpu time                                 129,042.9          N/A
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Statistic Name	통계 항목	STAT_NAME
Time (s)	각 통계 항목이 소요한 시간이 초 단위로 나타냄	VALUES / 1,000,000
% of DB Time	DB Time 중 서버 프로세스 관련 통계 항목이 차지하는 비율	100 * (1 - (VALUE WHER STAT_NAME NOT LIKE '%background%' / VALUE WHERE STAT_NAME = 'DB Time'))

설명

Time Model Statistics 단위 보고서는 DB 사용에 소요된 시간의 각 요소별 수치를 보여 주며 크게 두 개 부분으로 나눌 수 있음
- 오라클 백그라운드 프로세스가 DB를 사용한 시간
- 오라클 서버 프로세스가 DB를 사용한 시간

사용 Object
- DBA_HIST_SYS_TIME_MODEL 사용
- V$SYS_TIME_MODEL 참조

체크포인트
- 이 보고서를 이용하여 어느 항목들이 DB 자원을 사용하였는지 파악할 수 있으며, 이를 통해 튜닝 포인트를 설정할 수 있음.
- 'sql execute elapsed time' 항목이 높을 경우 SQL 수행속도 향상 튜닝포인트로 정하고, 'background elapsed time' 항목이 높을 경우 DB 인스턴스 튜닝을 튜닝포인트로 정함.

DB Time 구성 통계 항목

항목	의미
sql execute elapsed time	SQL 문 수행 시간의 총 합. SELECT 문의 경우 패치를 위해 소비된 시간까지 포함됨
DB CPU	서버 프로세스가 사용한 CPU 시간의 총 합. idel 시간은 제외
parse time elapsed	스냅샷 구간 중 소프트 파스 및 하드 파스에 소요된 총 시간.
hard parse elapsed time	스냅샷 구간 중 하드 파스에 소요된 총 시간
PL/SQL execution elapsed time	PL/SQL 인터프리터가 사용한 총 시간. 이 시간에는 재귀 SQL의 파스 및 수행시간과 내부 JAVA VM 수행시간은 제외됨
RMAN cpu time(backup/restore)	오라클 리커버리 매니저(RMAN)가 사용한 CPUU 총 시간.
inbound PL/SQL rpc elapsed time	인바운드 PL/SQL이 원격 프로시저 요청에 소요된 총 시간. 내부 SQL 수행 시간 및 내부 JAVA VM 수행 시간 포함.
hard parse (sharing criteria) elapsed time	하드 파스 중 라이브러리 캐시에 동일한 SQL이 없음을 확인하는데 소요된 총 시간
failed parse (out of shared memory) elapse	파스 실패가 발생한 시간의 총 합.
PL/SQL compilation elapsed time	PL/SQL 컴파일러가 사용한 총 시간.
connection management call elapsed time	스냅샷 구간 중 접속 요청 및 접속 중단 요청에 소요된 총 시간.
sequence load elapsed time	시퀀스 번호를 부여받기 위해 사용한 총 시간. 시퀀스 번호를 CACHE 옵션 단위로 메모리에 복사하는 시간이며, 캐싱된 시퀀스 번호를 사용할 때에는 소요시간 증가 안함.
repeated bind elapsed time
hard parse (bind mismatch) elapsed time	바인드 변수의 크기나 타입이 일치하지 않아 하드 파스된 경우 소요된 총 시간.
Java execution elapsed time	내부 JAVAJ VM이 수행된 총 소요 시간. 재귀 SQL의 파스 및 수행시간 및 내부 PL/SQL 수행 시간 제외됨
DB time	서버 프로세스가 사용한 CPU 시간. DB CPU Time + DB Wait Time

2. Wait Class


                                                                  Avg
                                       %Time       Total Wait    wait     Waits
Wait Class                      Waits  -outs         Time (s)    (ms)      /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Application                17,506,439   14.6        1,267,600      72       3.2
Cluster                   635,426,531     .7          711,948       1     117.2
User I/O                  880,141,107     .0          578,071       1     162.4
Concurrency               109,942,001    6.8          185,613       2      20.3
Other                      30,132,752   50.5           19,285       1       5.6
System I/O                  8,794,977     .0            7,775       1       1.6
Commit                      2,600,816     .0            2,515       1       0.5
Configuration                 301,612   62.9              484       2       0.1
Network                    48,482,502     .0              233       0       8.9
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Wait Class		WAIT_CLASS
Waits		SUM(TOTAL_WAIT) GROUP BY WAIT_CLASS
%Time-outs		100 * (SUM(TOTAL_TIMEOUTS) GROUP BY WAIT_CLASS / SUM(TOTAL_WAITS) GROUP BY WAIT_CLASS)
Total Wait Time (s)		(SUM(TIME_WAITED_MICRO) GROUP BY WAIT_CLASS) / 1,000,000
Avg wait (ms)		(SUM(TIME_WAITED_MICRO GROUP BY WAIT_CLASS) / SUM(TOTAL_WAIT) GROUP BY WAIT_CLASS) / 1,000
Waits/txn		---

설명

Wait Class 단위 보고서는 스냅샷 구간 중 발생한 대기 이벤트의 수치를 클래스별로 파악할 수 있도록 제공함.
- DBA_HIST_SYSTEM_EVENT 사용
- V$SYSTEM_EVENT 참조

체크포인트
- 'User I/O' 값이 높을 경우, 'Wait Evnet' 단위 보고서를 조회하여 'db file sequential read', 'db file scattered read' 값을 추가적으로 확인하여 체크 가능

3. Wait Event / Background Wait Events


                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
enq: TX - row lock contentio      2,605,003   97.8   1,252,860     481       0.5
db file sequential read         783,336,225     .0     443,806       1     144.5
gc cr multi block request       153,646,030     .0     339,938       2      28.3
gc buffer busy                   66,222,785    6.2     186,262       3      12.2
gc cr grant 2-way               388,687,761     .0     168,777       0      71.7
db file scattered read           74,247,271     .0      97,843       1      13.7
cursor: pin S wait on X           7,465,173  100.0      73,389      10       1.4
latch: library cache                213,230     .1      54,981     258       0.0
read by other session            16,992,985    1.9      28,905       2       3.1
library cache load lock              13,564   48.8      20,100    1482       0.0
SQL*Net break/reset to clien     14,757,524     .0      14,633       1       2.7
latch: shared pool                  112,838     .1      14,192     126       0.0
................................................................................
................................................................................
................................................................................

의미

항목	의미	관련 컬럼
Event		EVENT_NAME
Waits		TOTAL_WAIT
%Time-outs		100 * (TOTAL_TIMEOUTS / TOTAL_WAITS)
Total Wait Time (s)		TIME_WAITED_MICRO / 1,000,000
Avg wait (ms)		(TIME_WAITED_MICRO / TOTAL_WAIT) / 1,000
Waits/txn		---

설명

Wait Event / Background Wait Events 단위 보고서는 DB 경합이 발생한 대부분의 원인과 관련된 대기 이벤트 수치를 보여주므로, 튜닝 포인트 설정하는데 도움을 줌.
- DBA_HIST_SYSTEM_EVENT 사용
- V$SYSTEM_EVNET 참조

4. Operating System Statistics


Statistic                                       Total
-------------------------------- --------------------
NUM_LCPUS                                           0
NUM_VCPUS                                           0
AVG_BUSY_TIME                               3,645,419
AVG_IDLE_TIME                               6,972,575
AVG_IOWAIT_TIME                               880,304
AVG_SYS_TIME                                1,123,004
AVG_USER_TIME                               2,518,865
BUSY_TIME                                 204,340,075
IDLE_TIME                                 390,660,111
IOWAIT_TIME                                49,492,160
SYS_TIME                                   63,085,316
USER_TIME                                 141,254,759
LOAD                                                0
OS_CPU_WAIT_TIME                          205,369,500
RSRC_MGR_CPU_WAIT_TIME                              0
PHYSICAL_MEMORY_BYTES                 150,323,855,360
NUM_CPUS                                           56
NUM_CPU_CORES                                      28
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Statistic	통계 이름	STAT_NAME
Total	통계 수치	VALUE

설명

Operating System Statistics 단위 보고서는 오라클 프로세스가 사용한 시스템 자원 관련 통계 정보를 보여줌
- DBA_HIST_OSSTAT 사용
- V$OSSTAT 참조

주요 통계 의미

항목	의미
AVG_BUSY_TIME	모든 포르세스가 CPU를 사용한 평균 시간
AVG_IDLE_TIME	모든 포르세스가 대기 상태였던 평균 시간
AVG_IOWAIT_TIME	모든 포르세스가 Disk I/O가 완료되기를 기다린 평균 시간
AVG_SYS_TIME	OS 커널 프로세스가 CPU를 사용한 평균 시간
AVG_USER_TIME	사용자 프로세스가 CPU를 사용한 평균 시간
BUSY_TIME	모든 포르세스가 CPU를 사용한 총 시간. SYS_TIME + USER_TIME
IDLE_TIME	모든 포르세스가 IDEL 상태였던 총 시간
IOWAIT_TIME	모든 포르세스가 Disk I/O가 완료되기를 기다린 총 시간
SYS_TIME	OS 커널 프로세스가 CPU를 사용한 총 시간
USER_TIME	사용자 프로세스가 CPU를 사용한 총 시간
LOAD	OS 스케줄러에 의해 수행되고 있거나, 수행 대기 중인 프로세스 수
OS_CPU_WAIT_TIME	모든 프로세스가 CPU를 사용하기 위해 대기한 총 시간
RSRC_MGR_CPU_WAIT_TIME	오라클 리소스 매니저를 사용할 경우, 자원 사용 계획에 의해 오라클 프로세스가 CPU를 사용하기 위해 대기한 총 시간
PHYSICAL_MEMORY_BYTES	시스템에 장착되어 있는 물리적 메모리 용량
NUM_CPUS	사용 가능한 CPU 개수
NUM_CPU_CORES	사용 가능한 CPU 소켓 개수

5. Service Statistics


                                                             Physical    Logical
Service Name                      DB Time (s)   DB CPU (s)      Reads      Reads
-------------------------------- ------------ ------------ ---------- ----------
XSOFT                             3,205,894.7    634,272.2 ########## ##########
SYS$USERS                           320,781.2    167,219.5  6,312,044 ##########
SYS$BACKGROUND                            0.0          0.0  7,245,237 ##########
SYS.KUPC$C_2_20121207135406               0.0          0.0          0          0
SYS.KUPC$C_2_20121207135447               0.0          0.0          0          0
SYS.KUPC$S_2_20121207135406               0.0          0.0          0          0
SYS.KUPC$S_2_20121207135447               0.0          0.0          0          0
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Service Name	서비스 이름	SERVICE_NAME
DB Time (s)	서비스별 DB 사용시간	SUM(VALUE) WHERE STAT_NAME = 'DB Time' GROUP BY SERVICE_NAME
DB CPU (s)	서비스별 CPU 사용시간	SUM(VALUE) WHERE STAT_NAME = 'DB CPU' GROUP BY SERVICE_NAME
Physical Reads	서비스별 발생시킨 디스크 I/O	SUM(VALUE) WHERE STAT_NAME = 'physical reads' GROUP BY SERVICE_NAME
Logical Reads	서비스별 발생시킨 메모리 I/O	SUM(VALUE) WHERE STAT_NAME = 'session logical reads' GROUP BY SERVICE_NAME

설명

Service Statistics 단위 보고서는 서비스별 DB 자원 사용 현황을 보여줌
- DBA_HIST_SERVICE_STAT 사용
- V$SERVICE_STATS 참조

6. Service Wait Class Stats


Service Name
----------------------------------------------------------------
 User I/O  User I/O  Concurcy  Concurcy     Admin     Admin   Network   Network
Total Wts   Wt Time Total Wts   Wt Time Total Wts   Wt Time Total Wts   Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
XSOFT
#########  57359739  92432550  15591673         0         0  47480073     23018
SYS$USERS
  3713989    317226  17232673   2565568         0         0    408962       208
SYS$BACKGROUND
  2138368    129749    251659    387831         0         0         0         0
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Service Name		SERVICE_NAME
User I/O Total Wts		TOTAL_WAITS WHERE WAIT_CLASS = 'User I/O'
User I/O Wt Time		TOTAL_WAITED WHERE WAIT_CLASS = 'User I/O'
Concurcy Total Wts		TOTAL_WAITS WHERE WAIT_CLASS = 'Concurrency'
Concurcy Wt Time		TOTAL_WAITED WHERE WAIT_CLASS = 'Concurrency'
Admin Total Wts		TOTAL_WAITS WHERE WAIT_CLASS = 'Administrative'
Admin Wt Time		TOTAL_WAITED WHERE WAIT_CLASS = 'Administrative'
Network Total Wts		TOTAL_WAITS WHERE WAIT_CLASS = 'Network'
Network Wt Time		TOTAL_WAITED WHERE WAIT_CLASS = 'Network'

설명

Service Statistics 단위 보고서는 각 서비스가 발생시킨 대기 이벤트를 이벤트 클래스별로 보여줌.
- DBA_HIST_SERVICE_WAIT_CLASS 사용
- V$SERVICE_WAIT_CLASS 참조

7. SQL 수행 정보 단위 보고서

SQL 수행 정보 보고서는 스냅샷 구간 중 수행된 SQL에 대해 9가지 측면의 보고서 제공하며, 이를 통해 튜닝 대상 선정

튜닝 방향
- 1회 수행 시 성능이 느린 SQL 튜닝
- 1회 수행 시 성능은 빠르지만 리소스 과점으로 인해 타 프로그램에 영향을 주는 프로그램 튜닝

SQL_ID를 통해 추세를 파악하여 튜닝하는 방안
- AWR를 스냅샷 구간별로 저장한 뒤 시간 추이를 통해 느려진 SQL_ID를 튜닝함
- 이를 확인하는 Object는 DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT, DBA_HIST_SQLTEXT

구간별 SQL_ID 확인하는 스크립트


SELECT SRC.SQL_ID,         -- SQL_ID
       SRC.MODULE,         -- SQL_ID가 사용된 프로그램
       SRC.ELAP_TIME,      -- 전체 수행시간
       SRC.CPU_TIME,       -- CPU 시간
       SRC.EXEC,           -- 수행횟수
       SRC.ELAP_PER_EXEC,  -- 1회 수행 시 소요된 시간
       Q.SQL_TEXT          -- SQL FULL_TEXT
FROM   (SELECT S.DBID,
               S.MODULE,
               ROUND(SUM(S.ELAPSED_TIME_DELTA) / 1000000, 2) ELAP_TIME,
               ROUND(SUM(S.CPU_TIME_DELTA) / 1000000, 2) CPU_TIME,
               SUM(S.EXECUTIONS_DELTA) EXEC,
               CASE WHEN SUM(S.EXECUTIONS_DELTA) > 0 
                    THEN ROUND(SUM(S.ELAPSED_TIME_DELTA) / SUM(S.EXECUTIONS_DELTA) / 1000000, 2)
                    END ELAP_PER_EXEC,
               S.SQL_ID
        FROM   DBA_HIST_SQLSTAT  S,
               DBA_HIST_SNAPSHOT H
        WHERE  S.DBID                = H.DBID
        AND    S.SNAP_ID             = H.SNAP_ID
        AND    S.INSTANCE_NUMBER     = H.INSTANCE_NUMBER
        AND    H.BEGIN_INTERVAL_TIME >= TO_DATE('20121114 00:00:00', 'yyyymmdd hh24:mi:ss')
        AND    H.BEGIN_INTERVAL_TIME <  TO_DATE('20121115 00:00:00', 'yyyymmdd hh24:mi:ss')
        GROUP  BY S.DBID,
                  S.MODULE,
                  S.SQL_ID
       ) SRC,
       DBA_HIST_SQLTEXT Q
WHERE  SRC.DBID   = Q.DBID
AND    SRC.SQL_ID = Q.SQL_ID
ORDER  BY SRC.ELAP_PER_EXEC DESC
;

SQL_ID를 통해 바인드 변수 확인하는 스크립트


SELECT B.SNAP_ID,
       B.DBID,
       B.INSTANCE_NUMBER,
       B.SQL_ID,
       B.NAME,
       B.POSITION,
       B.DATATYPE_STRING,
       VALUE_STRING,
       LAST_CAPTURED
FROM   DBA_HIST_SQLBIND  B,
       DBA_HIST_SNAPSHOT H
WHERE  H.DBID                = B.DBID
AND    H.SNAP_ID             = B.SNAP_ID
AND    H.INSTANCE_NUMBER     = B.INSTANCE_NUMBER
AND    B.SQL_ID              = '6h228dtb8hp1t'
AND    H.BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20121114', 'yyyymmdd') 
                             AND     TO_DATE('20121115', 'yyyymmdd')
AND    VALUE_STRING IS NOT NULL
;

아래는 AWR 보고서에서 9가지 측면에서 SQL_ID를 분석한 내용을 기술함.
단, AWR에 저장된 전체 SQL을 보여주지 않으며, %TOTAL이 1%가 넘는 SQL 중에서 상위 64개 까지만 보여주고, 단 1%가 넘는 SQL이 10개 이하일 경우 1%가 넘지 않아도 10개까지만 보여줌

7-1) SQL ordered by Elapsed Time


  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
   892,986        369      153,055        5.8    25.3 fmpbxcbz6q0a1
Module: PROGRAM_1
UPDATE FND_CONCURRENT_PROCESSES SET PROCESS_STATUS_CODE = 'C', LAST_
UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = :mgrusrid, LAS
T_TXN_START = SYSDATE, LAST_TXN_END = NULL WHERE CONCURR
ENT_PROCESS_ID = :cpid

   148,040         15          772      191.8     4.2 9ajyd7y70xuv2   
Module: PROGRAM_2
UPDATE FND_CONCURRENT_PROCESSES SET GSM_INTERNAL_STATUS = :B3 , GSM_INTERNAL_INF
O = :B2 WHERE CONCURRENT_PROCESS_ID = :B1

   148,008         16          771      192.0     4.2 gaf2hsang61x7
................................................................................
................................................................................
................................................................................

의미

항목	의미	관련 컬럼
Elapsed Time (s)	SQL이 수행된 총 소요 시간(단위:초)	(SUM(A.ELAPSED_TIME_DELTA) GROUP BY SQL_ID) / 1,000,000
CPU Time (s)	SQL이 사용한 총 소요 시간(단위:초)	(SUM(A.CPU_TIME_DELTA) GROUP BY SQL_ID) / 1,000,000
Executions	SQL이 수행된 총 횟수	(SUM(A.EXECUTIONS_DELTA) GROUP BY SQL_ID) / 1,000,000
Elap per Exec (s)	SQL 1회 평균 수행 시간	Elapsed Time (s) / Executions
% Total DB Time	전체 DB Time 중 해당 SQL의 총 수행 시간이 차지하는 비율	(SUM(A.ELAPSED_TIME_DELTA) GROUP BY SQL_ID) / 1,000,000 / DB Time (s)
SQL Id	SQL의 ID	A.SQL_ID
Module	SQL 수행 모듈	MAX(A.MODULE) GROUP BY SQL_ID
SQL Text	SQL 문장	B.SQL_TEXT

A : DBA_HIST_SQLSTAT
B : DBA_HIST_SQLTEXT
C : DBA_HIST_SYSSTAT

설명

대부분의 경우 'SQL ordered by Elapsed Time' 항목을 체크한 후 나머지 8개 항목을 체크하는 순서로 진행함.

체크포인트
- 일반적으로 수행속도가 오래 걸린 SQL_ID가 문제의 SQL일 확률이 높으므로 우선적으로 튜닝을 진행함.
- 하지만, 병렬 처리를 사용하는 SQL이나, 평소에는 양호하나 가끔 느려지는 SQL의 경우 이 항목에서는 체크하기가 힘드므로, 나머지 항목에서 체크해야 함

7-2) SQL ordered by CPU Time


    CPU      Elapsed                  CPU per  % Total
  Time (s)   Time (s)  Executions     Exec (s) DB Time    SQL Id
---------- ---------- ------------ ----------- ------- -------------
   116,357    140,199            0         N/A     4.0 74tszw00kqfmv
Module: XXINVM3019
INSERT INTO XXINVM_SUPR_BILLTO_LINK_INFO ( ORGANIZATION_ID , VENDOR_SITE_ID , OR
G_ID , LEDGER_ID , CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE , LAST_UPDATED_
BY , LAST_UPDATE_LOGIN ) SELECT WL.ORGANIZATION_ID , TO_NUMBER (WL.ATTRIBUTE5 )
 VENDOR_SITE_ID , OOD.OPERATING_UNIT , OOD.SET_OF_BOOKS_ID , :b1 , :b2 , :b3 , :

    37,005    103,758            0         N/A     2.9 4z8cr86gxbgj6    
Module: JAINITCL
SELECT MSIT.CONCATENATED_SEGMENTS, RGIA.INVENTORY_ITEM_ID, SUM(DISTINCT(NVL(RGIA
.TEMPLATE_ID,0))) TEMPLATE_ID FROM MTL_SYSTEM_ITEMS_KFV MSIT, JAI_RGM_ITEM_ATTRI
B_V RGIA WHERE (CONCATENATED_SEGMENTS LIKE :1) AND ( MSIT.INVENTORY_ITEM_ID = RG
IA.INVENTORY_ITEM_ID AND MSIT.ORGANIZATION_ID = RGIA.ORGANIZATION_ID AND MSIT.IN
................................................................................
................................................................................
................................................................................

의미

항목	의미	관련 컬럼
CPU Time (s)	1회 수행에 사용한 평균 CPU 시간	CPU Time (s) / Executions

DBA_HIST_SQLSTAT

설명

체크포인트
- 1회 수행 시 사용하는 CPU 시간이 많은 SQL의 경우 수행 성능도 좋지 않을 뿐 아니라, CPU 자원 부족을 초래하여 전체 시스템에 영향을 미침.

해결방안
- 많은 데이터에 복잡한 함수를 사용할 경우 : 함수가 적용될 데이터 집합을 최소화한 후 반드시 필요한 함수만 적용토록 튜닝을 해야함.
- 리소스 과점 시 CPU 시간이 많이 걸리므로 'SQL ordered by Gets', 'SQL ordered by Reads' 2개 항목과 같이 비교해서 체크해야 함.

7-3) SQL ordered by Gets


-> Total Buffer Gets:  30,268,548,911
-> Captured SQL account for      59.4% of Total

                                Gets              CPU     Elapsed
  Buffer Gets   Executions    per Exec   %Total Time (s)  Time (s)    SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
 3,511,779,383           48 ############   11.6 ########  30904.05 72ud7wrkgbcdy
Module: XXOMV03820
BEGIN xxomv_dr_status_if_proc(:errbuf,:rc); END;

 3,359,890,313           60 ############   11.1 ########  28482.93 33jg5vtj2t49h
Module: XXOMV03820
SELECT OOH.ORG_ID ,OOS.NAME ORDER_SOURCE ,'D' || DECODE( OTTH.ATTRIBUTE4 ,'OMV_R
MA_H010', 'D' ,'OMV_RMA_H020', 'E' ) || OTTL.ATTRIBUTE9 ORDER_TYPE ,OOH.ORDER_NU
MBER ,OOH.ORDERED_DATE ,OOH.HEADER_ID ,OOH.BOOKED_FLAG ,OOH.FLOW_STATUS_CODE HEA
DER_STATUS_CODE ,OOH.ATTRIBUTE11 APPROVAL_STATUS ,OOH.TRANSACTIONAL_CURR_CODE ,O
................................................................................
................................................................................
................................................................................

의미

항목	의미	관련 컬럼
Total Buffer Gets	스냅샷 구간 중 발생한 총 버퍼 캐시 I/O 횟수	SUM(A.VALUE) WHERE STAT_NAME = 'session logical reads'
Captured SQL account for	스냅샷 구간 중 발생한 총 버퍼 캐시 I/O 횟수 중 캡처된 SQL이 발생시킨 버퍼 캐시 I/O 비율	100 * (SUM(B.BUFFER_GETS_DELTA) / Total Buffer Gets)
Buffer Gets	SQL이 발생시킨 버퍼 캐시 I/O 횟수	SUM(B.BUFFER_GETS_DELTA) GROUP BY SQL_ID
Gets per Exec	1회 수행에 사용한 평균 버퍼 캐시 I/O 횟수	Buffer Gets / Executions
%Total	전체 버퍼 캐시 I/O 중 해당 SQL에 의해서 유발된 버퍼 캐시 I/O가 차지하는 비율	100 * (Buffer Gets / Total Buffer Gets)

A : DBA_HIST_SYSSTAT
B : DBA_HIST_SQLSTAT

설명

체크포인트
- SQL 튜닝의 기본 원칙은 1히 수행 시 발생하는 I/O의 양을 줄이는 것이므로, 'SQL ordered by Elapsed Time ' 보고서에 나온 SQL_ID 보단 'SQL ordered by Gets'에 나온 SQL_ID 대상이 더 중요함
- 이 보고서에서는 'Gets per Exec', '%Total' 2가지 항목을 중점적으로 체크해야 함

7-4) SQL ordered by Reads


-> Total Disk Reads:   2,413,993,592
-> Captured SQL account for     31.1% of Total

                               Reads              CPU     Elapsed
Physical Reads  Executions    per Exec   %Total Time (s)  Time (s)    SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
   667,667,719           0           N/A   27.7 ######## ######### 4z8cr86gxbgj6
Module: JAINITCL
SELECT MSIT.CONCATENATED_SEGMENTS, RGIA.INVENTORY_ITEM_ID, SUM(DISTINCT(NVL(RGIA
.TEMPLATE_ID,0))) TEMPLATE_ID FROM MTL_SYSTEM_ITEMS_KFV MSIT, JAI_RGM_ITEM_ATTRI
B_V RGIA WHERE (CONCATENATED_SEGMENTS LIKE :1) AND ( MSIT.INVENTORY_ITEM_ID = RG
IA.INVENTORY_ITEM_ID AND MSIT.ORGANIZATION_ID = RGIA.ORGANIZATION_ID AND MSIT.IN

   154,389,151           1 #############    6.4 ########  48248.76 2bg6mdhzq9rry
Module: XXCDBM1915
BEGIN xxcdbm_dwbk_duty_anly_pkg.main(:errbuf,:rc,:A0,:A1,:A2,:A3,:A4); END;
..................................................................................
..................................................................................
..................................................................................

의미

항목	의미	관련 컬럼
Total Disk Reads	스냅샷 구간 중 발생한 총 디스크 I/O 횟수.	SUM(A.VALUE) WHERE STAT_NAME = 'physical reads'
Captured SQL account for	스냅샷 구간 중 발생한 총 디스크 I/O 횟수 중 캡처된 SQL이 발생시킨 디스크 I/O 비율	100 * (SUM(B.DISK_READS_DELTA) / Total tisk Reads)
Physical Reads	SQL이 발생시킨 디스크 I/O 횟수	SUM(B.DISK_READS_DELTA) GROUP BY SQL_ID
Reads per Exec	1회 수행에 발생하 평균 버퍼 디스크 I/O 횟수	Physical Reads / Executions
%Total	전체 디스크 I/O 중 해당 SQL이 발생시킨 디스크 I/O가 차지하는 비율	100 * (Physical Reads/ Total Disk Reads)

A : DBA_HIST_SYSSTAT
B : DBA_HIST_SQLSTAT

설명

체크포인트
- 이 보고서에서 체크해야 할 항목은 'Reads per Exec', '%Total' 이다.
- 이 대상에 대해 체크 후에도 Logical I/O 대비 Physical I/O가 많을 경우, 자주 사용되지 않는 이력 데이터를 최근에 자주 조회하는지 확인 필요

7-5) SQL ordered by Executions


-> Total Executions:     768,307,109
-> Captured SQL account for     63.2% of Total

                                              CPU per    Elap per
 Executions   Rows Processed  Rows per Exec   Exec (s)   Exec (s)     SQL Id
------------ --------------- -------------- ---------- ----------- -------------
 316,810,676     316,794,894            1.0       0.00        0.00 7bvgk9y5spmzb
Module: GSALES_OME_IF_9069
SELECT CURRENT_TIMESTAMP AT TIME ZONE TZ_OFFSET(:B1 ) FROM DUAL

  35,081,178      35,081,177            1.0       0.00        0.00 5q7zq1v84ngvn
Module: XXOMV02770
SELECT :B3 - (( TO_NUMBER(SUBSTR(TZ_OFFSET(:B2 ),1,3))+(TO_NUMBER(SUBSTR(TZ_OFFS
ET(:B2 ),1,1)||SUBSTR(TZ_OFFSET(:B2 ),5,2))/60))/24) + (( TO_NUMBER(SUBSTR(TZ_OF
FSET(:B1 ),1,3))+(TO_NUMBER(SUBSTR(TZ_OFFSET(:B1 ),1,1)||SUBSTR(TZ_OFFSET(:B1 ),
5,2))/60))/24) FROM DUAL
..................................................................................
..................................................................................
..................................................................................

의미

항목	의미	관련 컬럼
Total Executions	스냅샷 구간 중 모든 SQL 수행 횟수	SUM(A.VALUE) WHERE STAT_NAME = 'execute count'
Captured SQL account for	스냅샷 구간 중 모든 SQL 수행 횟수 중 캡처된 SQL의 수행 횟수 비율	100 * (SUM(B.EXECUTIONS_DELTA) / Total Executions)
Rows Processed	SQL이 발생시킨 디스크 I/O 횟수	SUM(B.DISK_READS_DELTA) GROUP BY SQL_ID
Rows per Exec	1회 수행당 발생시킨 평균 디스크 I/O 횟수	Physical Reads / Executions

설명

체크포인트
- 이 보고서에서 체크해야 할 항목은 'Total Executions', 'Rows per Exec' 이다.
- 수행횟수가 많을 경우, 약간의 사용 효율성 변화에도 DB 전체에 큰 영향을 미칠 수 있으므로 상시 체크 필요하고, 수행횟수 줄일 수 있는지 확인해야 함

7-6) SQL ordered by Parse Calls


-> Total Parse Calls:      37,202,169
-> Captured SQL account for      55.7% of Total

                            % Total
 Parse Calls  Executions     Parses    SQL Id
------------ ------------ --------- -------------
   3,839,612    3,839,645     10.32 2ym6hhaq30r73
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) fro
m seg$ where ts#=:1 and file#=:2 and block#=:3

   1,230,648    1,230,659      3.31 grwydz59pu6mc
select text from view$ where rowid=:1
..................................................................................
..................................................................................
..................................................................................

의미

항목	의미	관련 컬럼
Total Parse Calls	스냅샷 구간 중 모든 파스 콜 횟수	SUM(A.VALUE) WHERE STAT_NAME = 'parse count (total)'
Captured SQL account for	스냅샷 구간 중 모드 파스 콜 횟수 중 캡처된 SQL의 파스 콜 비율	100 * (SUM(B.PARSE_CALLS_DELTA) / Total Parse Calls)
Parse Calls	SQL이 발생시킨 전체 파스 콜 횟수	SUM(B.PARSE_CALLS_DELTA) GROUP BY SQL_ID
% Total Parses	전체 파스 콜 중 해당 SQL이 발생시킨 파스 콜이 차지하는 비율	100 * (Parse Calls / Total Parse Calls)

A : DBA_HIST_SYSSTAT
B : DBA_HIST_SQLSTAT

설명

체크포인트
- 하드 파싱이 많은 SQL_ID를 찾아 소프트 파싱으로 변경이 가능한지 검토함

7-7) SQL ordered by Sharable Memory


-> Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b)  Executions   % Total    SQL Id
---------------- ------------ -------- -------------
      20,363,454        1,829     0.32 gt83120pkp4u3
Module: XXINVS1195-1
INSERT INTO XXINVS_CURINV_SNAPSHOT XCS ( CREATED_BY ,CREATION_DATE ,OPERATING_UN
IT_ID ,INVENTORY_ORGANIZATION_ID ,INVENTORY_ORGANIZATION_CODE ,SUBINVENTORY_CODE
 ,GRADE_CODE ,LOT_NO ,PRODUCT1_CODE ,PRODUCT2_CODE ,PRODUCT3_CODE ,PRODUCT4_CODE
 ,HQ_ACCOUNTING_UNIT_CODE ,INVENTORY_ITEM_ID ,MODEL_CODE ,MODEL_DESCRIPTION ,MAR

      12,754,343        1,933     0.20 6qvvqy714u1xj
Module: XXOMV02770
SELECT DISTINCT * FROM ( SELECT /*+ index(xsli.ola oe_order_lines_xn15) *
/ * FROM XXOMV_SO_LINE_IF_V XSLI WHERE (XSLI.LINE_LAST_UPDATE_DATE BETWEEN :B2 A
ND :B1 ) UNION ALL SELECT /*+ index(xsli.oha oe_order_headers_all_xn7) */ * FRO
M XXOMV_SO_LINE_IF_V XSLI WHERE (XSLI.HEADER_LAST_UPDATE_DATE BETWEEN :B2 AND :B
..................................................................................
..................................................................................
..................................................................................

의미

항목	의미	관련 컬럼
Sharable Mem (b)		B.SHARABLE_MEM GROUP BY SQL_ID
% Total		100 * (B.SHARABLE_MEM GROUP BY SQL_ID / A.VALUE WHERE PARAMETER_NAME = '__shared_pool_size')

A : DBA_HIST_PARAMETER
B : DBA_HIST_SQLSTAT

설명

이 보고서에서 'Sharable Mem (b)' 크기가 큰 경우는 아래와 같다
- 자식 커서의 수가 많은 경우
- SQL문이 매우 긴 경우

체크포인트
- 자식 커서의 수가 과도하게 많은 경우 라이브러리 캐시 래치 경합을 발생시킬 수 있음
- 이를 해결하기 위해서는 V$SQL_SHARED_CURSOR 뷰를 통해 커서 공유 실패 원인 파악하여 대처해야 함

7-8) SQL ordered by Version Count


-> Only Statements with Version Count greater than 20 are displayed

 Version
  Count   Executions     SQL Id
-------- ------------ -------------
     168      351,636 37kp7bjxfn4u5
Module: JDBC Thin Client
SELECT NLS_DATE_LANGUAGE FROM (SELECT UTF8_DATE_LANGUAGE NLS_DATE_LANGUAGE FROM
FND_LANGUAGES WHERE NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')) IN ('UTF8', 'AL3
2UTF8') AND INSTALLED_FLAG <>'D' AND NLS_LANGUAGE = :B1 UNION SELECT LOCAL_DATE_
LANGUAGE NLS_DATE_LANGUAGE FROM FND_LANGUAGES WHERE NLS_CHARSET_NAME(NLS_CHARSET

      83       49,912 bkaaqaa5aj3gf
Module: GMFG_BOM_IF_0113
SELECT MESSAGE_TEXT, MESSAGE_NUMBER, TYPE, FND_LOG_SEVERITY, CATEGORY, SEVERITY
FROM FND_NEW_MESSAGES M, FND_APPLICATION A WHERE :B3 = M.MESSAGE_NAME AND :B2 =
M.LANGUAGE_CODE AND :B1 = A.APPLICATION_SHORT_NAME AND M.APPLICATION_ID = A.APPL
ICATION_ID
..................................................................................
..................................................................................
..................................................................................

의미

항목	의미	관련 컬럼
Version Count	SQL에 종속된 자식 커서의 수	VERSION_COUNT

DBA_HIST_SQLSTAT

설명

이 보고서는 자식 커서의 수가 많은 대상을 보여줌
자식 커서의 수가 증가되는 원인은 아래와 같음
- 여러 DB 유저가 동일한 이름의 오브젝트를 소유하고 있는 경우
- 실행 계획의 변경이 잦을 때
- 설정된 바인드 변수의 타입이 다른 경우

체크포인트
- 자식 커서가 공유되지 못하는 원인을 파악하기 위해서는 V$SQL_SHARED_CURSOR.BIND_MISMATCH 값을 체크해야 함


SELECT 
         SQL_ID                           
        ,CHILD_NUMBER          AS CHILD_NUM                 
        ,DECODE (UNBOUND_CURSOR            ,'N','',UNBOUND_CURSOR             ) AS UNBOUND_CURSOR                    
        ,DECODE (SQL_TYPE_MISMATCH         ,'N','',SQL_TYPE_MISMATCH          ) AS SQL_TYPE                          
        ,DECODE (OPTIMIZER_MISMATCH        ,'N','',OPTIMIZER_MISMATCH         ) AS OPTIMIZER                         
        ,DECODE (OUTLINE_MISMATCH          ,'N','',OUTLINE_MISMATCH           ) AS OUTLINE                           
        ,DECODE (STATS_ROW_MISMATCH        ,'N','',STATS_ROW_MISMATCH         ) AS STATS_ROW                         
        ,DECODE (LITERAL_MISMATCH          ,'N','',LITERAL_MISMATCH           ) AS LITERAL                           
        ,DECODE (SEC_DEPTH_MISMATCH        ,'N','',SEC_DEPTH_MISMATCH         ) AS SEC_DEPTH                         
        ,DECODE (EXPLAIN_PLAN_CURSOR       ,'N','',EXPLAIN_PLAN_CURSOR        ) AS EXPLAIN_PLAN_CURSOR               
        ,DECODE (BUFFERED_DML_MISMATCH     ,'N','',BUFFERED_DML_MISMATCH      ) AS BUFFERED_DML                      
        ,DECODE (PDML_ENV_MISMATCH         ,'N','',PDML_ENV_MISMATCH          ) AS PDML_ENV                          
        ,DECODE (INST_DRTLD_MISMATCH       ,'N','',INST_DRTLD_MISMATCH        ) AS INST_DRTLD                        
        ,DECODE (SLAVE_QC_MISMATCH         ,'N','',SLAVE_QC_MISMATCH          ) AS SLAVE_QC                          
        ,DECODE (TYPECHECK_MISMATCH        ,'N','',TYPECHECK_MISMATCH         ) AS TYPECHECK                         
        ,DECODE (AUTH_CHECK_MISMATCH       ,'N','',AUTH_CHECK_MISMATCH        ) AS AUTH_CHECK                        
        ,DECODE (BIND_MISMATCH             ,'N','',BIND_MISMATCH              ) AS BIND                              
        ,DECODE (DESCRIBE_MISMATCH         ,'N','',DESCRIBE_MISMATCH          ) AS DESCRIBE_MIS                      
        ,DECODE (LANGUAGE_MISMATCH         ,'N','',LANGUAGE_MISMATCH          ) AS LANGUAGE                          
        ,DECODE (TRANSLATION_MISMATCH      ,'N','',TRANSLATION_MISMATCH       ) AS TRANSLATION                       
        ,DECODE (ROW_LEVEL_SEC_MISMATCH    ,'N','',ROW_LEVEL_SEC_MISMATCH     ) AS ROW_LEVEL_SEC                     
        ,DECODE (INSUFF_PRIVS              ,'N','',INSUFF_PRIVS               ) AS INSUFF_PRIVS                      
        ,DECODE (INSUFF_PRIVS_REM          ,'N','',INSUFF_PRIVS_REM           ) AS INSUFF_PRIVS_REM                  
        ,DECODE (REMOTE_TRANS_MISMATCH     ,'N','',REMOTE_TRANS_MISMATCH      ) AS REMOTE_TRANS                      
        ,DECODE (LOGMINER_SESSION_MISMATCH ,'N','',LOGMINER_SESSION_MISMATCH  ) AS LOGMINER_SESSION                  
        ,DECODE (INCOMP_LTRL_MISMATCH      ,'N','',INCOMP_LTRL_MISMATCH       ) AS INCOMP_LTRL                       
        ,DECODE (OVERLAP_TIME_MISMATCH     ,'N','',OVERLAP_TIME_MISMATCH      ) AS OVERLAP_TIME                      
        ,DECODE (SQL_REDIRECT_MISMATCH     ,'N','',SQL_REDIRECT_MISMATCH      ) AS SQL_REDIRECT                      
        ,DECODE (MV_QUERY_GEN_MISMATCH     ,'N','',MV_QUERY_GEN_MISMATCH      ) AS MV_QUERY_GEN                      
        ,DECODE (USER_BIND_PEEK_MISMATCH   ,'N','',USER_BIND_PEEK_MISMATCH    ) AS USER_BIND_PEEK                    
        ,DECODE (TYPCHK_DEP_MISMATCH       ,'N','',TYPCHK_DEP_MISMATCH        ) AS TYPCHK_DEP                        
        ,DECODE (NO_TRIGGER_MISMATCH       ,'N','',NO_TRIGGER_MISMATCH        ) AS NO_TRIGGER                        
        ,DECODE (FLASHBACK_CURSOR          ,'N','',FLASHBACK_CURSOR           ) AS FLASHBACK_CURSOR                  
        ,DECODE (ANYDATA_TRANSFORMATION    ,'N','',ANYDATA_TRANSFORMATION     ) AS ANYDATA_TRANSFORMATION            
        ,DECODE (INCOMPLETE_CURSOR         ,'N','',INCOMPLETE_CURSOR          ) AS INCOMPLETE_CURSOR                 
        ,DECODE (TOP_LEVEL_RPI_CURSOR      ,'N','',TOP_LEVEL_RPI_CURSOR       ) AS TOP_LEVEL_RPI_CURSOR              
        ,DECODE (DIFFERENT_LONG_LENGTH     ,'N','',DIFFERENT_LONG_LENGTH      ) AS DIFFERENT_LONG_LENGTH             
        ,DECODE (LOGICAL_STANDBY_APPLY     ,'N','',LOGICAL_STANDBY_APPLY      ) AS LOGICAL_STANDBY_APPLY             
        ,DECODE (DIFF_CALL_DURN            ,'N','',DIFF_CALL_DURN             ) AS DIFF_CALL_DURN                    
        ,DECODE (BIND_UACS_DIFF            ,'N','',BIND_UACS_DIFF             ) AS BIND_UACS_DIFF                    
        ,DECODE (PLSQL_CMP_SWITCHS_DIFF    ,'N','',PLSQL_CMP_SWITCHS_DIFF     ) AS PLSQL_CMP_SWITCHS_DIFF            
        ,DECODE (CURSOR_PARTS_MISMATCH     ,'N','',CURSOR_PARTS_MISMATCH      ) AS CURSOR_PARTS                      
        ,DECODE (STB_OBJECT_MISMATCH       ,'N','',STB_OBJECT_MISMATCH        ) AS STB_OBJECT                        
        ,DECODE (ROW_SHIP_MISMATCH         ,'N','',ROW_SHIP_MISMATCH          ) AS ROW_SHIP                          
        ,DECODE (PQ_SLAVE_MISMATCH         ,'N','',PQ_SLAVE_MISMATCH          ) AS PQ_SLAVE                          
        ,DECODE (TOP_LEVEL_DDL_MISMATCH    ,'N','',TOP_LEVEL_DDL_MISMATCH     ) AS TOP_LEVEL_DDL                     
        ,DECODE (MULTI_PX_MISMATCH         ,'N','',MULTI_PX_MISMATCH          ) AS MULTI_PX                          
        ,DECODE (BIND_PEEKED_PQ_MISMATCH   ,'N','',BIND_PEEKED_PQ_MISMATCH    ) AS BIND_PEEKED_PQ                    
        ,DECODE (MV_REWRITE_MISMATCH       ,'N','',MV_REWRITE_MISMATCH        ) AS MV_REWRITE                        
        ,DECODE (ROLL_INVALID_MISMATCH     ,'N','',ROLL_INVALID_MISMATCH      ) AS ROLL_INVALID                      
        ,DECODE (OPTIMIZER_MODE_MISMATCH   ,'N','',OPTIMIZER_MODE_MISMATCH    ) AS OPTIMIZER_MODE                    
        ,DECODE (PX_MISMATCH               ,'N','',PX_MISMATCH                ) AS PX_MISMATCH                                
        ,DECODE (MV_STALEOBJ_MISMATCH      ,'N','',MV_STALEOBJ_MISMATCH       ) AS MV_STALEOBJ                       
        ,DECODE (FLASHBACK_TABLE_MISMATCH  ,'N','',FLASHBACK_TABLE_MISMATCH   ) AS FLASHBACK_TABLE                   
        ,DECODE (LITREP_COMP_MISMATCH      ,'N','',LITREP_COMP_MISMATCH       ) AS LITREP_COMP                       
FROM    V$SQL_SHARED_CURSOR SC
WHERE   SC.SQL_ID  = :V_SQL_ID
ORDER BY SC.SQL_ID,SC.CHILD_NUMBER 
;

7-9) SQL ordered by Cluster Wait Time


      Cluster   CWT % of     Elapsed         CPU
Wait Time (s) Elapsd Tim     Time(s)     Time(s)     Executions    SQL Id
------------- ---------- ----------- ----------- -------------- -------------
    49,327.16       44.1  111,939.18   14,956.62             28 c4vyq4y9uapwx
Module: XXETF15110
BEGIN xxetf_vat_interface_pkg.main_proc(:errbuf,:rc,:A0,:A1,:A2); END;

    46,280.10       99.8   46,386.90       67.11             39 d5s354pq6wj7w
Module: sqlplus@LGEGLERDSE1Q (TNS V1-V3)
SELECT T.INST_NO ,T.SESS_HASH_VALUE ,T.LOGTIME ,T.SQL_HASH_VALUE ,NVL(T.USERNAME
, 'APPS') PARSING_SCHEMA_NAME ,T.MODULE ,T.SQL_TEXT FROM KIC.EPI_SQL_201210_11 T
 WHERE T.SQL_HASH_VALUE BETWEEN :B2 AND :B1
..................................................................................
..................................................................................
..................................................................................

의미

항목	의미	관련 컬럼
Cluster Wait Time (s)	SQL이 공유 캐시 획득할 때 대기한 총 소요 시간(단위:초)	SUM(CLWAIT_DELTA) / 1,000,000 GROUP BY SQL_ID
CWT % of Elapsd Time	SQL이 수행된 시간 중 다른 인스턴스의 캐시 획득 시간이 차지하는 비율	100 * Cluster Wait Time (s) / Elapsed Time (s)

설명

SQL 수행 시간이 오래 걸리고 'CWT % of Elapsd Time' 항목의 비율이 높다면 아래의 사항 체크해야 함
- RAC를 구성하는 서버간 네트워크 병목
- RAC를 구성하는 각 인스턴스의 버퍼 캐시 경합
- RAC를 구성하는 서버의 네트워크 관련 커널 설정 값

8. Instance Activity Stats


Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session                 63,942,871          601.8          11.8
CPU used when call started               62,317,568          586.5          11.5
CR blocks created                        14,584,448          137.3           2.7
Cached Commit SCN referenced            115,528,127        1,087.3          21.3
Commit SCN cached                             5,942            0.1           0.0
DB time                                 787,703,446        7,413.5         145.3
DBWR checkpoint buffers written          42,393,320          399.0           7.8
................................................................................
................................................................................
................................................................................

의미

항목	의미	관련 컬럼
Statistic	통계 이름	STAT_NAME
Total	통계 정보 발생 횟수	SUM(VALUE) GROUP BY STAT_NAME
per Second	초당 통계 정보 발생 횟수	---
per Trans	트랜잭션당 통계 정보 발생 횟수	---

설명

Instance Activity Stats 단위 보고서는 스냅샷 구간 중 DB에 수집된 통계 정보 수치를 보여줌.
- DBA_HIST_SYSSTAT 사용
- V$SYSSTAT 참조

체크포인트
- 통계 정보는 인스턴스 운영 시 특정 작업이 수행되면 작업과 관련된 통계 수치가 증가하므로, 스냅샷 구간의 인스턴스 상태 분석 시 사용될 수 있음.

9. Instance Activity Stats - Absolute Values


Statistic                            Begin Value       End Value
-------------------------------- --------------- ---------------
session cursor cache count           121,940,084     129,411,331
opened cursors current                    19,310          29,084
workarea memory allocated                 49,719       1,724,148
logons current                               531             766
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Begin Value	시작 스냅샷의 통계 값	SUM(VALUE) GROUP BY STAT_NAME
End Value	종료 스냅샷의 통계 값	SUM(VALUE) GROUP BY STAT_NAME

설명

Instance Activity Stats - Absolute Values 단위 보고서는 통계 정보 중 특정 파라미터와 관련된 통계에 대한 수치를 보여줌.
- DBA_HIST_SYSSTAT 사용

Statistic 통계수치 의미

session cursor cache count	스냅샷 수행 시 전체 세션의 캐시에 저장된 커서 수
opened cursors current	스냅샷 수행 시 오픈된 전체 커서 수
workarea memory allocated	스냅샷 수행 시 전체 PGA 메모리의 크기
logons current	스냅샷 수행 시 로그온 되어 있는 세션의 총 수. 활동 중인 세션과 비활동 중인 세션 모두를 포함함.

session cursor cache count
- SESSION_CACHED_CURSORS 초기화 파라미터가 0 이상의 값으로 설정되어 있을 때만 수치가 증가함.
- AWR DB의 수치보다는, V$SESSTAT 동적 뷰를 검사하여, 대부분의 세션이 SESSION_CACHED_CURSORS 파라미터에 설정된 값에 도달해 있다면, 메모리 여유가 있을 경우 이 파라미터 값을 올려줌.


SELECT CASE WHEN VALUE >= 15 AND VALUE <= 20 THEN '15 ~ 20'
            WHEN VALUE >= 10 AND VALUE <= 14 THEN '10 ~ 14'
            WHEN VALUE >= 5  AND VALUE <= 9  THEN ' 5 ~  9'
            WHEN VALUE >= 0  AND VALUE <= 4  THEN ' 0 ~  4'
            END "CURSOR COUNT",
       COUNT(VALUE) "VALUE"
FROM   V$SESSTAT
WHERE  STATISTIC# = 283 -- session cursor cache count ID
GROUP  BY CASE WHEN VALUE >= 15 AND VALUE <= 20 THEN '15 ~ 20'
               WHEN VALUE >= 10 AND VALUE <= 14 THEN '10 ~ 14'
               WHEN VALUE >= 5  AND VALUE <= 9  THEN ' 5 ~  9'
               WHEN VALUE >= 0  AND VALUE <= 4  THEN ' 0 ~  4'
               END
ORDER  BY 1 DESC
/

opened cursors current
- OPEN_CURSORS 파라미터와 연관 있음
- AWR DB의 수치보다는, V$SESSTAT 동적 뷰를 검사하여, 세션의 최대 커서 오픈 수를 확인하고 이 수치가 OPEN_CURSORS 파라미터에서 지정한 값과 차이가 없다면 OPEN_CURSORS 수치를 높인다.


SELECT MAX(VALUE) "MAX CUR OPEN"
FROM   V$SESSTAT
WHERE  STATISTIC# = 3

workarea memory allocated
- PGA 크기와 관련 있음

logons current
- SESSIONS 파라미터와 연관이 있음

SESSION_CACHED_CURSORS	개별 세션이 사용하는 PGA 영역에 저장할 수 있는 커서의 개수를 지정함. 저장되어 있는 커서는 소프트 파스와 하드 파스 과정 없이 사용되며 기본 값은 20
OPEN_CURSORS	한 개의 세션이 오픈할 수 있는 최대 커서의 개수를 설정함. 세션이 이 파라미터에서 지정한 값 이상의 커서를 오픈하려고 하면 ORA-1000 "maximum open cursors exceeded" 에러가 발생함.
WORKAREA_SIZE_POLICY	PGA 영역 관리를 Oracle에서 할 것인지, 사용자가 할 것인지를 지정함. AUTO로 설정된 경우 PGA_AGGREATE_TARGET 파라미터에서 설정한 값 한도 내에서 각 세션이 필요한만큼 PGA 공간을 사용할 수 있으며, MANUAL로 설정된 경우 SORT_AREA_SIZE나 HASH_AREA_SIZE 등과 같은 PGA 관련 파라미터에서 설정한 값만큼 PGA를 사용함.
PGA_AGGREATE_TARGET	WORKAREA_SIZE_POLICY 파라미터가 AUTO로 설정되어 있는 경우, 전체 세션은 PGA_AGGREATE_TARGET에서 지정한 값 이하에서 자유롭게 PGA 공간을 사용할 수 있음
SESSIONS	DB에 접속할 수 있는 최대 세션 수를 지정함

10. Instance Activity Stats - Thread Activity


Statistic                                     Total  per Hour
-------------------------------- ------------------ ---------
log switches (derived)                           94      3.18
          -------------------------------------------------------------

의미

항목	의미	관련 컬럼
Total	발생한 로그 스위치 횟수	SEQUENCE#
per Hour	한 시간 평균 발생한 로그 스위치 횟수	---

설명

Instance Activity Stats - Thread Activity 단위 보고서는 리두 로그 수위치 횟수를 보여줌
- DBA_HIST_THREAD 사용
- V$THREAD 참조

체크포인트
- 로그 스위치 발생 시 쓰레드 체크포인트가 발생하며, 쓰레드 체크포인트는 체크포인트 큐와 파일 큐에 링크된 더티 버퍼를 내려쓰고, 모든 데이터 파일의 헤더를 갱신시키므로 많은 I/O가 발생하므로, 너무 잦은 로그 스위치 발생은 I/O 경합으로 이어질 수 있음
- 또한 아카이브 로그 모드인 경우 아카이브 파일 생성에 따른 I/O 증가 및 아카이브 파일 생성 속도보다 리두 로그 순환이 빠르면 인스턴스행(Hang) 현상이 발생하므로, 적정한 크기의 리두 로그 파일 설정이 필요함.

로그 파일 스위치 발생량을 줄이기 위한 방안
- 불필요한 DML 제거 : 과거 데이터 삭제를 위해서 발생하는 DELETE 작업은 테이블을 파티션으로 변경하여 파티션 삭제로 대체 |
- NOLOGGING 옵션 적용 : 대량의 리두 로그 데이터를 발생시키는 배치 업무에 NOLOGGING 옵션을 적용함 |
- IOT 테이블 적용으로 테이블과 인덱스에 대한 세그먼트 단일화 : 처리 이력 데이터가 주로 입력되는 테이블을 IOT로 변경하여 테이블과 인덱스에 발생하는 로그 발생량을 크게 줄일 수 있음 |

리두 로그 생성 및 디스크 쓰기 성능을 높이기 위한 방안
- 리두 로그 파일을 최적의 성능을 가지는 디스크에 생성
- 배치 업무 수행 시 배치 커밋 수행
- NOLOGGING 옵션을 적절히 사용

아래는 리두 로그 버퍼 및 파일 관련 대기 이벤트 설명

10-1) log buffer space

발생원인
- 'log buffer space' 이벤트는 리두 로그 데이터를 리두 로그 버퍼에 기록하려고 할 때 프리 공간을 확보하지 못하고 대기하면 발생함.
- 이 현상은 LGWR이 리두 로그 버퍼에 저장된 데이터를 리두 로그 파일에 기록해서 프리 공간을 확보하는 속도보다 DML이 생성하는 리두 로그 데이터가 생성되는 속도가 더 빠를 때 발생함.

발생상황 분류
- 'log buffer space' 대기 이벤트만 주로 발생
- 'log file sync', 'log buffer space' 대기 이벤트가 같이 높은 수치로 발생
- 'log file swich completion', 'log buffer space' 대기 이벤트가 같이 높은 수치로 발생

10-2) log file sync

발생원인
- 리두 로그 버퍼에서 리두 로그 파일로 쓰기를 할 때 'log file sync' 이벤트를 대기함.

발생상황 분류
- I/O 성능이 낮은 경우 : I/O 경합을 줄이기 위해 추가적인 디스크 분산을 고려해야 하며, 파일이 위치하는 I/O 시스템 성능 개선 필요
- 리두 로그 버퍼 크기가 너무 크게 설정 : 모니터링을 통해 적절한 크기 선정 필요하며, DB 구축 단계 시 1M ~ 5M 권장
- 커밋이 빈번히 발생

10-3) log file swich completion

발생원인
- 현재 로그 파일의 공간이 모두 사용되고 나면 로그 스위치가 발생하는데, 이 때 이벤트 대기함.
- 해결방안은 리두 로그 데이터의 발생량을 줄이고 디스크 I/O 성능을 개선해야 함.

11-4) log file parallel write

발생원인
- 리두 로그 버퍼의 리두 레코드를 LGWR이 리두 로그 파일로 기록하는 동안 발생하며, 물리적인 디스크별로 병렬로 수행됨.
- 해결방안은 'log file sync'과 동일함.

문서에 대하여

최초작성자 : ~xsoft
최초작성일 : 2013년 01월 05일
이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 '오라클 AWR을 이용한 고성능 데이터베이스 튜닝'을 참고하였습니다.*

상세 보고서 - Time Model Statistics (by xsoft) [2013.01.01]

목차

0. 상세보고서

1. Time Model Statistics

AWR DB 보고서 내용

의미

설명

2. Wait Class

의미

설명

3. Wait Event / Background Wait Events

의미

설명

4. Operating System Statistics

의미

설명

5. Service Statistics

의미

설명

6. Service Wait Class Stats

의미

설명

7. SQL 수행 정보 단위 보고서

7-1) SQL ordered by Elapsed Time

의미

설명

7-2) SQL ordered by CPU Time

의미

설명

7-3) SQL ordered by Gets

의미

설명

7-4) SQL ordered by Reads

의미

설명

7-5) SQL ordered by Executions

의미

설명

7-6) SQL ordered by Parse Calls

의미

설명

7-7) SQL ordered by Sharable Memory

의미

설명

7-8) SQL ordered by Version Count

의미

설명

7-9) SQL ordered by Cluster Wait Time

의미

설명

8. Instance Activity Stats

의미

설명

9. Instance Activity Stats - Absolute Values

의미

설명

10. Instance Activity Stats - Thread Activity

의미

설명

10-1) log buffer space

10-2) log file sync

10-3) log file swich completion

11-4) log file parallel write

문서에 대하여