unit1 intro to waits

8/13/2019 Unit1 Intro to Waits

1/48

Waits Defined

Kyle Hailey

http://perfvision.com
http://perfvision.com/http://perfvision.com/


2/48

#.2

Copyright 2006 Kyle Hailey

Top 36 Foreground Waits

19. write complete waits

20. library cache lock21. SQL*Net more data from dblink

22. log file switch (checkpoint incomplete)

23. library cache load lock

24. row cache lock

25. local write wait

26. sort segment request27. process startup

28. unread message

29. file identify

30. pipe put

31. switch logfile command

32. SQL*Net break/reset to dblink33. log file switch (archiving needed)

34. Wait for a undo record

35. direct path write (lob)

36. undo segment extension

1. db file sequential read

2. log file sync3. db file scattered read

4. latch free

5. enqueue

6. SQL*Net more data from client

7. direct path read

8. direct path write9. buffer busy waits

10. SQL*Net more data to client

11. log buffer space

12. log file switch completion

13. library cache pin

14. SQL*Net break/reset to client15. io done

16. file open

17. free buffer waits

18. db file parallel read

minus OPS, RAC, PQ, Resource Mgr http://www.oraperf.com


3/48

#.3


Tuning Methodology

Machine

Run queue (CPU)

reduce CPU usage or add CPUsPaging

Reduce memory usage or add memory

Oracle

Waits >> CPU Tune waits

We are going toconcentrate here onWAITS


4/48

#.4


For Analysis

CPU Time

For comparision with Wait Time

Wait Time

Top Waits If Wait Time >> CPU Time, need top waits

Wait Arguments p1, p2, p3

For detailed analysis of some waitsSQL that caused waits

For detailed analysis of some waits


5/48

#.5


Wait and CPU Data Sources

I Statspack

Top 5 Timed Events

II10g ASHOEM

ASH Report : ashrpt.sql

Custom queries on v$active_session_history

IIIV$session & v$session_wait

Custom queries


6/48

#.6


I Statspack

Look at Top 5 Timed Events

Top 5 Timed Events~~~~~~~~~~~~~~~~~~ % TotalEvent Waits Time (s) Call Time-------------------------------------------- ------------ ----------- ---------buffer busy waits 2,748 250 78.72CPU time 32 10.16free buffer waits 1,588 15 4.63write complete waits 10 8 2.51log buffer space 306 5 1.51


7/48

#.7


II OEM 10g Performance Tab


8/48

#.8


II OEM 10g Zoom-In


9/48

#.9


II Top Activity Page


10/48

#.10


II 10g v$active_session_history

The best source

Only in 10g

Can simulate in any version since v7


11/48

#.11


II Querying ASH

selectevent as "TOP 5 Timed Events",cnt as "Count" from (

select decode(ash.session_state,

'ON CPU','CPU',en.name) event ,count(decode(ash.session_state,'ON CPU','CPU',en.name)) cnt

fromv$active_session_history ash,v$event_name en

where en.event# (+) = ash.event#and SAMPLE_TIME > sysdate - (5/(24*60))

group by decode(ash.session_state,'ON CPU','CPU',en.name)order by count(decode(ash.session_state,

'ON CPU','CPU',en.name)) desc

) where rownum < 6;

Top 5 Timed Events Count------------------------------------ ---------

log file parallel write 8CPU 7control file parallel write 3null event 3log file sync 2


12/48

#.12


II Querying ASH for P1,P2,P3

SID EVENT P1 P2 P3

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

236 db file scattered read 3 35291 16

245 db file scattered read 1 13646 2248 read by other session 1 13646 1



245 db file sequential read 1 13591 1

select

session_id,

event,

p1, p2, p3from v$ash


13/48

#.13


II ASH ReportASH report

@?/rdbms/admin/ashrpt.sql

Pick interval over last 7 days !

1) General info

2) Top User Events ***

3) Top Background Events

4) Top Event P1/P2/P3 Values

5) Top Service/Module

6) Top Client IDs

7) Top SQL Command Types

8) Top SQL Statements ***

9) Top SQL using literals

10) Top Sessions ***

11) Top Blocking Sessions

12) Top Sessions running PQs

13) Top DB Objects

14) Top DB Files

15) Top Latches

16) Activity Over Time ***


14/48

#.14


III V$session_wait

select

decode(w.wait_time, 0, w.event , 'CPU') as "TOP 5 Timed Events,

count(*)

from v$session s,v$session_wait w

where w.sid=s.sid

and s.status='ACTIVE'

and s.type='USER'

and w.event not in ('jobq slave wait',

'rdbms ipc reply')

group by

decode(w.wait_time, 0, w.event , 'CPU')

order by count(*) desc;

Moment in Time data


15/48

#.15


III V$session_wait

Top 5 Timed Events COUNT

----------------------------------- ----------enq: UScontention 4

CPU 3

buffer busy waits 1latch: row cache objects 1


16/48

#.16


III V$session_wait

col status for a35select s.sid,

s.sql_hash_value,

decode(w.wait_time, 0, w.event , 'CPU') as status,

w.p1, w.p2, w.p3from v$session s,

v$session_wait w

where w.sid=s.sid

and s.status='ACTIVE'and s.type='USER'

and w.event not in ('jobq slave wait',

'rdbms ipc reply');


17/48

#.17


III V$session_wait

SID SQL_HASH STATUS P1 P2 P3

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

234 82347421 CPU 1431502854 39 0

235 3336613934 enq: US - contention 1431502854 44 0





248 3333220954 CPU 1650815232 1 0252 323960517 enq: US - contention 1431502854 44 0

260 1272059733 CPU 1431502854 44 0

Moment in Time data


18/48

#.18


Waits we will Ignore

Background

Idle

RACResource Manager


19/48

#.19


Background Waits

Filter Out Background Waits

StatspackASH : SESSION_TYPE='FOREGROUND

V$session_wait : type='USER'


20/48

#.20


Background Waits

ASHAvoid Background waits in ASH with

V$session_wait joined to v$session

Select from v$active_session_history

where SESSION_TYPE='FOREGROUND'

select

from v$session s,v$session_wait w

where w.sid=s.sid

and s.type='USER'


21/48

#.21


Idle Waits

Filter Out

10g

where wait_class != IdleCreate a list

9iCreate a list with

Documentation

List created from 10g

Select name from v$event_name wherewait_class=Idle;


22/48

#.22


Parallel Query Waits

Filter Out

Parallel Query Wait events are unusable

Save waits are both idle and waitsParallel Query Waits start with PX or KX

PX Deq: Par Recov Reply

PX Deq: Parse Reply


23/48


24/48

#.24


Resource Manager Waits

Resource manager throttles user

Creates wait

Obfuscates problems 10g

select name from v$event_name where

wait_class='Scheduler';


25/48

#.25


Wait Arguments: P1,P2,P3

Each Wait has a 3 parameters P1,P2,P3

Give detailed information

Meaning different for each wait

Meaning definitions in V$event_nameSelect

name,

parameter1,parameter2,

parameter3

from v$event_name;


26/48

#.26


Wait Arguments ExampleNAME PARAMETER1 PARAMETER2 PARAMETER3

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

latch: cache buffers chains address number tries

free buffer waits file# block# set-id#

buffer busy waits file# block# class#

latch: redo copy address number tries

log buffer space

switch logfile command

log file sync buffer#

db file sequential read file# block# blocks

enq: TM - contention name|mode object # table/partition

undo segment extension segment#

enq: TX - row lock contention name|mode usn


27/48

#.27


Waits

I/O

Library Cache

Locks

Undo

Redo

Buffer Cache

SQL*Net

Wait Tree


28/48

#.29


Top 12 Waits

NAME Count % Total

1. db file sequential read 23,850.00 11.67%2. log file sync 20,594.00 10.08%

3. db file scattered read 15,505.00 7.59%

4. latch free 11,078.00 5.42%

5. enqueue 7,732.00 3.78%

6. SQL*Net more data from client 7,510.00 3.67%7. direct path read 5,840.00 2.86%

8. direct path write 4,868.00 2.38%

9. buffer busy waits 4,589.00 2.25%

10. SQL*Net more data to client 3,805.00 1.86%

11. log buffer space 2,990.00 1.46%

12. log file switch completion 2,878.00 1.41%

Above is over 80% of wait times reported

Anjo Kolk www.oraperf.com


29/48

#.30


Top 36 Foreground Waits19. write complete waits

20. library cache lock

21. SQL*Net more data from dblink

22. log file switch (checkpoint incomplete)

23. library cache load lock

24. row cache lock

25. local write wait26. sort segment request

27. process startup

28. file identify

29. pipe put

30. switch logfile command

31. SQL*Net break/reset to dblink32. log file switch (archiving needed)

33. Wait for a undo record

34. direct path write (lob)

35. undo segment extension

36. undo segment tx slot

1. db file sequential read

2. log file sync3. db file scattered read

4. latch free

5. enqueue

6. SQL*Net more data from client

7. direct path read

8. direct path write9. buffer busy waits

10. SQL*Net more data to client

11. log buffer space

12. log file switch completion

13. library cache pin

14. SQL*Net break/reset to client

15. io done

16. file open

17. free buffer waits

18. db file parallel read

minus OPS, RAC, PQ, Resource Mgr


30/48

#.31


Waits 1-9

1. db file sequential readTune SQL, speed up disks (5-15ms), increase buffer cache

2. log file syncCommit less, put redo logs on faster disks

3. db file scattered read

FTS - Tune SQL, add indexes, speed up disks (5-15ms)4. latch freeneed p1,p2,p3

5. enqueueneed p1,p2,p3 and SQL

6. SQL*Net more data from clientUsually OK, reduce data transferred, possible Network problems

7. direct path readsorts or PQO- tune IO, sort less

8. direct path writedirect path load or temp io, improve disk speed

9. buffer busy waitsneed p1,p2,p3 and SQL

# 32


31/48

#.32


Waits 10-18

10. SQL*Net more data to clientUsually OK, reduce amount of data transferred, possible Network tuning needed11. log buffer space

Increase log buffer

12. log file switch completionIncrease log file sizes

13. library cache pinReduce parsing the same cursor concurently

14. SQL*Net break/reset to clientCheck for errors in sql statement

15. io doneIo done by IO slaves, oracleIxxx could also be an idle waitsort of worthless

16. file open

Reduce logons/logoffs or keep log files open17. free buffer waits

Increase buffer cache size

18. db file parallel readTune sql, tune io subsystem, increase buffer cache

# 33


32/48

#.33


Waits 19-27

19. write complete waits

Increase buffer cache (or tune DBWR)20. library cache lock

Avoid hard parsing same cursor at same time

21. SQL*Net more data from dblinkReduce data transfer, check net response

22. log file switch (checkpoint incomplete)Add log files (or increase log file size)23. library cache load lock

Avoid hard parsing same cursor at same time

24. row cache lockneed p1

25. local write waittruncates / reduce cache size

26. sort segment requestSMON busy, process rollback problem

27. process startupIf MTS, increase min servers and dispatchers in init.ora

# 34


33/48

#.34


Waits 28-36

28. file identifyKeep log files open, reduce checkpoints

29. pipe putSpeed up pipe readers

30. switch logfile commandAvoid switching log files

31. SQL*Net break/reset to dblinkCheck for errors in sql statement sent

32. log file switch (archiving needed)Archive log running out of space

33. Wait for a undo record??

34. direct path write (lob)Improve IO, reduce lob write size

35. undo segment extensionUse UNDO or with RBS, increase RBS size, avoid OPTIMAL

36. undo segment tx slotUse UNDO, increae # of RBS segs

# 35


34/48

#.35


Difficult Waits

Latches

Locks

Buffer BusyRow Cache Lock

# 36


35/48

#.36


Concepts: Latches

Protect areas of memory from concurrent use

Light weight locks

Usually a held in a bit in memoryOften supported by atomic processor call

Fast and cheap

Gone if memory is lost

Often used in cache coherency managementChanges to a datablock

Exclusive GenerallySharing reading has been introduced for some latches

# 37


36/48

#.37


Finding Latches

latch free

Covers many latches, find the problem latch by

1. select name from v$latchname where latch# = p1;OR

2. Find highest sleeps in Statspack latch section

In 10g, important latches have a wait event

latch: cache buffers chains latch: shared pool

latch: library cache

# 38


37/48

#.38


Latches : Find Highest Sleeps

Use Statspack (or AWR report)

Latch Name Get Requests Misses Sleeps Spin Gets Sleep1------------------- -------------- ----------- --------------------- -----

shared pool 3,044,462 184,517 19,098 169,226 0

library cache 2,242,805 79,554 15,198 66,497 0

kks stats 263,464 2,842 1,774 1,152 0

row cache object 434,844 955 147 816 0library cache lock 666,668 734 54 680 0

library cache pin 741,963 635 42 593 0

enqueues 133,577 373 40 333 0

enqueue hash chains 133,751 89 19 70 0

# 39


38/48

#.39


Important Latches Cache Buffers Chains

Hot blocks, distribute activity, reduce update time, avoid select for update Cache Buffers LRU Chain

Too much activity , uses multiple buffer caches and/or increse LRU latches

Shared Pool

Too much hard parsing, too small a shared pool

Library Cache Latch

Hard Parsing

Use bind variables

Uses cursor_sharing=force

Increase shared pool size if reloads are high

Avoid invalidations

Soft Parsing

Hold cursors open

Use session_cached_cursors

Use cursor_space_for_time

# 40


39/48

#.40


Concepts: Locks

Enqueue wait covers all locks pre 10

Protect data against concurrent changes

Lock info written into data structuresBlock headers

Data blocks

Written in cache structures

Shareable in compatible modes

# 41


40/48

#.41


Finding Locks

Statspack not much help

V$session_wait needs lots of decoding

P1 tells Lock Type and Mode P2,P3 give more data

Usually Need SQL to solveselect sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue;

SID EVENT Ty mode P2 P3

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

240 enqueue TX 6 2686995 433

# 42


41/48

#.42


Locks 10g

10g breaks Enqueues out enq: HW - contention Configuration

enq: TM - contention Application

enq: TX - allocate ITL entry Configuration

enq: TX - index contention Concurrency

enq: TX - row lock contention Application

enq: UL - contention Application

# 43


42/48

#.43


Locks : TM & TX

select * from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

151 TX 589855 1592 0 6 4049 0

135 TM 53737 0 3 0 4058 0151 TM 53737 0 3 0 4049 0

135 TX 589855 1592 6 0 4058 1


43/48

# 45


44/48

#.45


Buffer Busy Waits

Statspack

Buffer wait Statistics for DB: ORA9 Instance: ora9 Snaps: 2 -3

-> ordered by wait time desc, waits descTot Wait Avg

Class Waits Time (s) Time (ms)

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

data block 1,490 2 1

undo header 2 0 0

# 46


45/48

#.46


Buffer Busy Solutions

IF Buffer Busy Waits high

First look at v$waitstat Class

Time/Count

SQL> select * from v$waitstat;

CLASS COUNT TIME

------------------ ---------- ----------data block 1 1segment header 0 0free list 0 0undo header 0 0undo block 0 0

#.47


46/48

#.47


Row Cache Lock

Need p1 to see the cache type

SQL> select cache#, parameter from v$rowcache;CACHE# PARAMETER

---------- --------------------------------1 dc_free_extents4 dc_used_extents2 dc_segments0 dc_tablespaces5 dc_tablespace_quotas6 dc_files

7 dc_users3 dc_rollback_segments8 dc_objects17 dc_global_oids12 dc_constraints

#.48


47/48

#.48


Row Cache Lock

Statspack

^LDictionary Cache Stats for DB: ORA9 Instance: ora9 Snaps: 1 -2

->"Pct Misses" should be very low (< 2% in most cases)

->"Cache Usage" is the number of cache entries being used

->"Pct SGA" is the ratio of usage to allocated size for that cache

Get Pct Scan Pct Mod Final

Cache Requests Miss Reqs Miss Reqs Usage

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

dc_object_ids 45 0.0 0 0 958

dc_objects 89 0.0 0 0 1,129dc_segments 69 0.0 0 0 807

dc_tablespaces 12 0.0 0 0 13

dc_usernames 22 0.0 0 0 19

dc_sequences 120,003 0.0 0 120,003 5

#.49


48/48

# 9

Areas of Contention

Buffer Cache

Disk I/O

Library Cache Redo

SQL*Net

Undo

We will go through these areas and the wait events

that are relevant to them.

unit1 intro to waits

Documents