Oracle Wait Interface Seminar

Size: px

Start display at page:

Download "Oracle Wait Interface Seminar"

종수 공
6 years ago
Views:

2 목차 2 1. 성능문제와 OWI 분석방법론 2. OWI 구성요소 3. Latch & LOCK 4. Oracle I/O 5. Cache Buffer

3 3 성능문제와 OWI 분석방법론 성능지연사례 1) 평소에 20 분걸리던일 Batch 작업이어제새벽에 2 시간이걸렸네요? 원인이뭘까요? Source Code 가변경된것같은데요? 음.. 혹시 Index 생성, 삭제및통계정보재생성여부를확인해보겠습니다. Developer 지금돌리니까 20 분만에끝나네요? 음오늘새벽은잘될까?? DBA 잘모르겠습니다. 다시한번해당 Job 을돌려봐주세요. Monitoring 해보겠습니다. 1/3 2/3 3/3 Developer DBA

4 4 성능문제와 OWI 분석방법론 성능지연사례 2) 평소에 1 초이내수행되던화면이특정시간대에서 10 초이상소요되어온라인처리가지연되는현상이있습니다. 원인이뭘까요? Developer 확인해보겠습니다. 아니! CPU 사용율이 99% 어디에이상이있는걸까??? 왜? 자꾸이런일이생기는걸까혹시바이러스..? 아니면해킹..? 1/1 DBA

5 5 성능문제와 OWI 분석방법론 성능문제란? 인식 현재내가관리하는시스템성능에문제가있는가? 복잡성, 상대적, 주관적 진단 성능문제의원인은무엇인가? CPU, MEMORY, Response Time, Logical Reads, Physical Reads 튜닝 해결가능한가? 1/3 2/3 3/3

6 6 성능문제와 OWI 분석방법론 성능분석방법론 성능문제의분석작업을체계화한일련의 Process 분석을통해문제를객관적으로제시 원인규명및해결책제시 성능분석방법론의구성 성능문제인식 성능문제진단 성능문제해결

7 7 성능문제와 OWI 분석방법론 성능지연분석사례 1) 평소에 20 분걸리던일 Batch 작업이어제새벽에 2 시간이걸렸네요? 원인이뭘까요? 어제수행되었던일 Batch 작업분석결과, Data Fetch 작업시에수행된 SQL 은 Latch free (cache buffers chains ) 를 1 시간대기하였습니다. 원인은테스트프로그램에서수행된 SQL 이동일 Block 을 Access 하면서문제를유발한것입니다. 또한 Data 처리작업시에는 Enqueue Lock 을 40 분대기하였습니다. Lock Holder 는개발자가수행한 DML 입니다. Job Scheduling 및공지를통해이러한문제의재발을방지하도록하겠습니다. 오늘은무사히!! Developer 1/1 DBA

8 8 성능문제와 OWI 분석방법론 임계경로 (Critical Path) Response Time Web Server Application Server File Server DB Server Network Web Network file App Database 1/1

9 9 성능문제와 OWI 분석방법론 DB 성능문제는주로어떤경우에발생되는가? Mega-User, Mega-Resource -수천명이상의동시사용자 -수십장의 CPU -수십기가의버퍼캐시 -수백테라의스토리지 래치와락 경합증가 오라클은래치와락경합을줄이기위해끊임없이알고리즘을개선 - 래치와락의메커니즘에대한이해 -OWI 를이용한경합분석 - 개별대기이벤트에대한이해

10 10 성능문제와 OWI 분석방법론 동일자원의동시처리시어떤문제가있는가? Session A Lock or Latch or whatever Something to change Session B 대기!!!

11 11 성능문제와 OWI 분석방법론 Process Status & Oracle Wait Events IDLE RUNNING WAITING db file scattered read db file sequential read latch free enqueue log file sync 1/5 2/5 3/5 4/5 5/5

12 12 Wait Event Analysis Oracle7부터각프로세스들이 resource를요구할때마다 Wait Event를보고하게되어이를초점에맞추는성능진단이탄생 성능문제 Wait Time에초점을맞추어작업의비효율을분석 Wait의증가에따른 Wait 시간의증가로결국 Response Time도증가 Response Time = CPU Time + Wait Time 성능분석도구 시스템, 세션레벨의 Event 통계정보다이나믹뷰 (V$SYSTEM_EVENT, V$SESSION_EVENT)

13 13 Wait Event Analysis Top Wait 을통한 Wait Event Analysis 의예 성능문제의인식및진단 성능문제의해결 db file sequential read 해당이벤트는싱글블록 read ( 주로 index lookup) 시에발생함. 해당이벤트를감소시키기 위한일반적인방법은 I/O 의절대량을줄이는것과, 개별 I/O 성능을향상시키는것임. *) I/O 의절대량감소 1) db_block_buffers 설정값증가 buffer cache 의크기가너무작을경우, 과다한 Disk I/O 가발생할수있으므로, cache 의크기를증가시켜서 Disk I/O 횟수를감소시킴 2)..

14 14 OWI 구성요소 - Wait Event Wait Event 는 Database 내의각처리단계에설정된 Timer 를바탕으로, 각처리단계에서의자원획득을위한대기시간및횟수등을알려주는 Oracle 의통계지표 I/O 작업, Lock, Latch, DB Link, RAC 작업지연등과같은대기현상들을포함 Oracle 에서도입 Oracle Engine 개발자들이꾸준히 Wait Event 의수를늘려가고있음 1990 년대후반 Oracle User Group 에서다루어짐 2000 년 11 월 Oracle magazine(us) 에서 Wait Event-based 튜닝방법이소개됨 Oracle10g 에서는 800~900 개가까운 Wait Event 를지원함

15 15 OWI 구성요소 - Dynamic Performance View =statistic# V$STATNAME (Stat명) =statistic# V$SESSTAT ( 세션별 Stat 누적 ) V$SYSSTAT ( 시스템전체 Stat 누적 ) =sid V$SESSION_EVENT (Session 별 Event 누적 ) =sid V$SYSTEM_EVENT ( 시스템전체 Event 누적 ) V$EVENT_NAME (Event 별 p1,p2,p3 의미 ) V$SESSION_WAIT (Session 별최근 Event) =sid Event Stat V$PROCESS (Process ID) SPID addr=paddr =(sid,saddr) V$SESSION (Session 정보 ) taddr=addr (sql_address,sql_hash_value) =(address,hash_value) V$TRANSACTION (Undo 정보 ) XIDUSN START_TIME USED_UBLK USED_URED V$OPEN_CURSOR V$SQLAREA V$SQLTEXT (Session별 Open Cursor) (SQL별 Stat누적 ) (SQL별 Full Text) =(address,hash_value) =(address,hash_value)

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

17 Latch 개요 17 Latch 가벼운 Lock(Light-weight Lock) 공유 Memory 영역 (SGA) 을보호하기위한동기화객체 Latch 1. Latch 획득중 2. SGA 사용 Process Area1 Area2 Process 3. Latch 획득대기 Area3 Area4 SGA 1/3 2/3 3/3

18 Latch 개요 18 Latch 가보호하는 Resource Latch 는 SGA 의모든하위 Memory 영역을보호 Cache Buffer Shared Pool Library Cache Redo Buffer Memory 영역의속성에따라하나혹은여러개의 Latch 사용 여러개의 Latch를사용하는경우 Parent/Child의관계 Cache Buffer : 수천개 ~ 수만개의 Latch Shared Pool : 하나의 Latch Library Cache : CPU 개수에비례 Redo Buffer : 하나 ~ 여러개의 Latch

19 Lock 개요 19 Lock Latch보다무거운동기화객체 Database와관련된객체 (Object) 를보호하는동기화객체 Lock 1. Lock 획득중 2. Object 사용중 Process 3. Lock 획득대기 Table A Process 1/3 2/3 3/3 Database

20 Lock 개요 20 Lock 이보호하는 Resource Latch 가보호하는 Resource 외의모든 Database Object Tablespace Table Row Transaction LCO(Library Cache Object) RCO(Row Cache Object)

21 Lock 개요 21 Lock 분류 Enqueue Lock User Type: TX, TM, UL System Type: US, HW, SQ, SV, TT, WF, V$LOCK_TYPE 뷰참조 (10g) 기타 Lock library cache lock, library cache pin row cache lock buffer lock

22 Oracle I/O 22 Modern Enterprise Oracle I/O Architecture Server Process LGWR Cache Buffer DBWR OS File Cache SAN Switch Storage Cache Disk Log File Data File #2 Data File #1 Array Storage

23 Oracle I/O 23 Multi Block I/O 한번에최대 MBRC(DB_FILE_MUTLIBLOCK_READ_COUNT) 만큼 Block 읽음 Full Table Scan, Index Fast Full Scan 에서사용 Full Scan = Segment 의처음부터끝 (HWM) 까지 Multi Block I/O 로읽어들이는작업 HWM Block Block #12 #11 #10 #9 #8 #7 #6 #5 #4 #3 #2 #1 Header Physical Reads=1+8+4 = 13 1/4 2/4 3/4 4/4

24 Oracle I/O 24 Multi Block I/O Multi Block I/O 에서한꺼번에읽는 Block 수는? 최대값은 MBRC, 최소값은 2 MBRC=8 Cache Buffer Block Extent 경계 Multi Block I/O (6) Single Block I/O Physical Reads=6+1=7, Logical Reads=6+1+1=8 1/4 2/4 3/4 4/4

25 Oracle I/O 25 Index Full Scan vs Index Fast Full Scan Index Full Scan = Single Block I/O Index Fast Full Scan = Multi Block I/O Root Branch1 Branch2 Leaf1 Leaf2 Leaf3 Leaf4 Index Full Scan Branch1 Leaf1 Branch3 Leaf3 Leaf2 Branch2 Root Index Fast Full Scan 1/2 2/2

26 26 db file scattered read Event 정의 I/O System 에 Multi Block I/O 를요청하고응답이오기를기다리는 Event P1 = file# P2 = block# P3 = blocks( 실제로읽은블록수 ) FTS, IFFS(Index Fast Full Scan) 등에서발생 SQL> SELECT * FROM v$bh WHERE file#=<p1> AND block#=<p2> Or SQL> ALTER SYSTEM DUMP DATAFILE <P1> BLOCK MIN <P2> BLOCK MAX <P2+P3-1>

27 27 db file scattered read db file scattered read 대기과다원인 불필요한 Full Scan SQL Tuning, Index Tuning 비효율적인 Segment 공간 100 만건 Insert 후 100 만건 Delete? FTS 시간은 100 만건과동일 Table Reorg 필요 : Alter Table Move... I/O System 성능저하 Disk I/O 성능개선 Hot Spot 해소 : Logical Volume Level Striping, ASM 등

28 Oracle I/O 28 Single Block I/O 한번에하나의 Block만을읽어들임 Index Scan(IFFS 제외 ), ROWID에의한 Table Scan SELECT * FROM TAB WHERE ID = 100 Header Data Root Data Branch Branch ROWID ROWID=A, id=100 Data Leaf Leaf Leaf Leaf id=100,rowid=a Data Data Logical Reads = = 4 1/2 2/2

29 Oracle IO 29 Single Block I/O 기타 Single Block I/O 발생이유 Chained/Migrated Row Scan Multi Block I/O 수행도중하나의 Block만을읽을경우에도발생 Multi Block I/O 도중 Single Block I/O 발생 Extent 경계에닿은경우 MBRC=8 Extent 경계 Block Multi Block I/O (8) Single Block I/O Physical Reads = = 9

30 30 db file sequential read Event 정의 I/O System 에 Single Block I/O 를요청하고응답이오기를기다리는 Event P1 = file# P2 = block# P3 = 1 SQL> SELECT * FROM v$bh WHERE file#=<p1> AND block#=<p2> Or SQL> ALTER SYSTEM DUMP DATAFILE <P1> BLOCK <P2>

31 31 db file sequential read db file sequential read 발생사유 Single Block I/O Index를경유한 Scan Index Range Scan Index Unique Scan Index Full Scan Chained Row / Migrated Row Multi Block I/O 도중에도발생가능

32 32 db file sequential read db file sequential read 대기과다원인 비효율적인 Index Scan SQL Tuning Index 변경 지나치게높은 Clustering Factor Table Reorg 를통해해결 지나치게깊은 Index 깊이 Index Rebuild 를통해해결 I/O System 성능저하 Disk I/O 성능개선 Hot Spot 해소 : Logical Volume Level Striping, ASM 등

33 33 Cache Buffer Data Buffer Cache 구조도 LRU LRUW Bucket Bucket latch 1 Bucket Bucket Bucket latch 2 Bucket List Buffer Header List Buffer Cache 1/6 2/6 3/6 4/6 5/6 6/6

34 34 Cache Buffer Hash Chain 하나의 Hash Bucket 에연결된 Buffer Header 집합 Hash Chain Hash Bucket Buffer Header1 Buffer Header2 Buffer Header3 cache buffers chains latch Buffer Cache

35 35 Cache Buffer cache buffers chains latch Hash Chain을보호하는목적의 Latch Shared / Exclusive Mode 로획득가능 대부분의다른 Latch 들은 Exclusive Mode 만사용 하나의 Cache Buffers Chains Latch 가여러개의 Hash Chain 을보호 부모 / 자식 Latch V$LATCH_CHILDREN : 자식 Latch 통계정보

36 36 latch: cache buffers chains Event 정의 Cache Buffer Chain 을탐색하기위해 cache buffers chains latch 를획득하고자대기하는 Event P1 = address P2 = number P3 = tries 대기발생사유 여러세션이동시에동일 cache buffers chains latch 를 Exclusive Mode 로요청 동일세그먼트에대한동시다발적인 Logical Reads에의해유발 핫블록또는불량 SQL에의해발생

37 37 latch: cache buffers chains Hot Block 에의한 latch 경합 SQL1 Latch 획득대기중 SQL1 SQL1 Latch 획득 Bucket SQL1 SQL1 Data Buffer READ!!! 1/3 2/3 3/3

38 38 latch: cache buffers chains Bad SQL 에의한 latch 경합 Bad Bucket SQL1 Bad CBC latch Bucket Bucket CBC Latch 경합발생!! SQL2 Bucket Bad Bucket SQL3 CBC latch Bucket 1/8 2/8 3/8 4/8 5/8 6/8 7/8 8/8

39 39 latch: cache buffers chains Event 해소방안 Hot Block 인경우 블록분산 인덱스블록인경우인덱스키변경이나다른인덱스경유유도 SQL Tuning 블록에대한요청회수를줄일수있는방안모색 - 어플리케이션변경 Bad SQL 인경우 SQL Tuning

목 차

목 차 Oracle 9i Admim 1. Oracle RDBMS 1.1 (System Global Area:SGA) 1.1.1 (Shared Pool) 1.1.2 (Database Buffer Cache) 1.1.3 (Redo Log Buffer) 1.1.4 Java Pool Large Pool 1.2 Program Global Area (PGA) 1.3 Oracle