#40_140807_OWI_WAIT EVENT

# WAIT EVENT

l SQL 튜닝을 해도 전반적으로 오라클 DB의 성능이 저하되는 문제가 발생 한다면 OWI 활용한 서버튜닝을 해야한다

1.     OWI(Oracle Wait Interface) : 오라클에서 발생하는 성능상의 문제들에 대해서 오라클 개발자들이 원인에 대한 현상을 정의했는데 그 현상의 이름이 대기 이벤트이다

- 예) Oracle wait event 목록 확인

SQL> select name from v$event_name;

2.     총 대기시간(Response time) = 서비스 시간(Cpu time) + 대기시간(wait time)

• Wait event 테스트

1.     Orange ~> tool ~> session monitor

2.     Orange

- update 문이 왜 오래 걸리는지 이유를 알려주고 있다

- db file scattered read : full table scan

- db file sequential read : index 할 때 주로 나타남

- v$session과 v$session_wait를 조인해서 얻어진 결과가 session monitor에서 나온다

- v$session : 현재 DB에 접속된 세션들의 정보

- v$session_wait : 현재 DB에서 waiting 하는 세션들의 정보

n Session Monitoring SQL

select s.sid, s.serial#, s.username , s.process,        p.spid ,s.status,        decode(s.command,  '0','NO',  '1','Create Tab',  '2','Insert',  '3','Select',  '6','Update',  '7','Delete',  '9','Create Idx',  '10','Drop Idx',  '15','Alter Tbl',  '24','Create Proc',  '32','Create Link',  '33','Drop Link',  '36','Create RBS',  '37','Alter RBS',  '38','Drop RBS',  '40','Alter TBS',  '41','Drop TBS',  '42','Alter Sess',  '45','Rollback',  '47','PL/SQL Exe',  '62','Anal Table',  '63','Anal Index',  '85','Truncate') Command,         substr(s.program, 1, 10) prg,         w.Event from v$session s ,v$process p, v$session_wait w  where s.paddr(+) = p.addr    and s.status = 'ACTIVE'    and s.serial# <> 1    and s.sid=w.sid    and w.event not like '%rdbms ipc%'    and w.event not like '%pmon timer%'    and w.event not like '%smon timer%'    and w.event not like '%lock manager%'  order by w.event,s.sid /

l 성능상의 저하를 발생시키는 테스트 방법

15:16:55 SQL> @exec Scenario List -------------------------------------------------- cache_buffers_chains_latch buffer_busy_waits db_file_sequential_read shared_pool_latch library_cache_latch library_cache_pin sq_enqueue tx_enqueue log_buffer_space gc_buffer_busy gc_current_request dfs_lock_handle generic 13 rows selected. Elapsed: 00:00:00.03 Event name to simulate: buffer_busy_waits Session count [10]: 10 Expired by time(1) or looping count(2) [1]: 1 Execution internval(sec or count) [30]: 60 - 60초 동안 부하를 준다 Enable_trace (1=TRUE, 0=FALSE) [0]: 1 Exec method(0=Oracle Job, 1=Unix Shell) [0]: 0 Init data(1=TRUE, 0=FALSE) [1]: 1

항 목	정 의	비 고
V$EVENT_NAME	Instance에서  정의된 Wait Event의 정보를 가지는 참조성View	Event 명, Parameter1, Parameter2, Parameter3
V$SYSTEM_EVENT	Instance의 기동 후 , 전Session에서 발생한 Wait Event의 누계 통계치(Instance단위)	Instance의 전반적인 안정도를 판단할 수 있으며, 특정 시간대의 Delta 정보를 산출하여 구간 분석 가능
V$SESSION_EVENT	현재 접속되어 있는, 각Session 별 Wait Event 의 누계 통계치	접속한 Session에 대해, 각Event 별 통계정보를 파악
V$SESSION_WAIT	각 Session이 현재 대기하고 있는 Wait Event에 대한 상세 정보(P1,P2,P3,대기시간 등)를 제공	누적 Data가 아니고, 실시간Data이므로, 반복적으로, Query를 수행하여 Wait Event의 상황을 파악해야 함
V$SYSTEM_WAIT_CLASS	10g NF.Instance 기동 후 발생된 Wait Event를 관련된Class별로 Grouping 한 결과 제공	Wait Event의 클래스 단위로, Instance의 대기 상황 파악
V$SESSION_WAIT_CLASS	10g NF. 현재 접속되어 있는 각Session 별 Wait Event를Class별로 Grouping한 결과 제공	Wait Event의 클래스 단위로, Session의 대기 상황 파악
V$SESSION_WAIT_HISTORY	10g NF. 최근 10개의V$SESSION_WAIT 데이터 제공	Session Wait에 대한 아주 짧은 이력 정보 제공
V$EVENT_HISTOGRAM	10g NF. Instance 기동후의Wait Event에 대한 히스토그램 제공	각 버킷(대기시간의 구간)별Wait Event의 발생 이력 파악에 도움
V$ACTIVE_SESSION_HISTORY	10g NF. Active Session의 이력정보 제공	1초 단위로 각 Active Session 기본 정보,SQL수행정보, PQ 관련정보, 대기Event정보, 대기 Event관련OBJ#, FiLE#, BLOCK# 등의 정보 제공

 - v$session_wait : 지금 현재 waiting 하고 있는 세션들이 어떤 대기 이벤트를 일으키고 대기하는지 확인하는 뷰

l 튜닝을 편하게 할 수 있도록 10버전 이후부터 제공하는 튜닝 레포트 3가지

1.     AWR report : 특정시점에 발생했던 성능상의 이슈(악성 SQL, 대기 이벤트)를 확인

: @?/rdbms/admin/awrrpt.sql

2.     ADDM report : 특정시점에 발생했던 성능상의 이슈에 대한 해결책을 알려줌

: @?/rdbms/admin/addmrpt.sql

3.     ASH report : 가장 최근에 발생한 주요 악성 SQL과 대기 이벤트에 대한 정보를 보여주는 레포트

: @?/rdbms/admin/ashrpt.sql

4.     AWR compare report : 튜닝전후를 쉽게 비교 분석하도록 레포트

: @?/rdbms/admin/awrddrpt.sql)

문제1. buffer busy wait 대기 이벤트를 발생시키고 원인을 파악하시오(awr report 확인)

16:12:12 SQL> exec dbms_workload_repository.create_snapshot; 16:12:12 SQL> @exec - buffer busy wati event 발생(시간240초) 16:12:12 SQL> exec dbms_workload_repository.create_snapshot; 16:12:12 SQL> @?/rdbms/admin/addmrpt.sql Current Instance ~~~~~~~~~~~~~~~~    DB Id    DB Name      Inst Num Instance ----------- ------------ -------- ------------  3193357653 DBDB                1 dbdb ……………………………………………… 274 06 Aug 2014 15:30      2                                 275 06 Aug 2014 15:41      1                                 276 06 Aug 2014 15:48      1 Enter value for begin_snap: 275 Begin Snapshot Id specified: 275 Enter value for end_snap: 276 End   Snapshot Id specified: 276 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_275_276.html.  To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: report09.html

n report.html(home/oracle)

- report에서 대기이벤트와 악성 SQL을 확인해야 한다

1.     대기이벤트 - buffer busy waits : 이벤트들중에 가장 긴 시간동안 대기한 것을 확인할 수 있다 2.     악성 SQL - update 절을 악성 SQL로 확인됐다