Understanding Lock Contention

1. Understanding Lock Contention

2. Objectives • After completing this lesson, you should be able to: • Explain the primary causes of lock contention • Describe the roles of the SMON process and their possible impact on performance: • Coalescing free space • Cleaning up temporary segments

3. Data Dictionary Locks • Preserve data dictionary integrity • Provide transactions with a consistent view of object definitions in the data dictionary • Are of three types: • Row cache locks • Library cache locks (breakable parse locks) • Library cache pins

4. Row Cache Locks • The Row Cache: • Stores data dictionary rows. • Is part of the shared pool • Reduces the physical I/O to the SYSTEM tablespace on disk • Uses Row Cache Locks to support fine-grain locking of data dictionary rows • Lock implementation: • The cached row acts as the resource structure. • Lock structures are allocated dynamically from the shared pool. • Types QA…QZ.

5. Wait Event: RowCacheLock • Indicates waiting to acquire a row cache lock • Wait duration is 3 seconds. • The wait parameters are: • P1: cache id (cache# in V$ROWCACHE) • P2: mode that is held • P3: mode that is requested • The lock request is abandoned after 1000 timeouts.

6. Library Cache Locks • Library cache locks are acquired on the library cache object handles. • During parse calls, these locks are acquired in: • Exclusive mode on parent and child handles • Share mode on all their dependencies • These locks are retained in Null mode to: • Detect invalidations • Avoid having to locate the handle again • Lock implementation: • The resource structure is the object handle. • Lock structures are allocated dynamically from the shared pool. • Types LA…LP.

7. Wait Event: LibraryCacheLock • The wait event library cache lock indicates a waiting to acquire a library cache lock • Wait duration is three seconds (only one second for PMON). • The wait parameters are: • P1: object handle address • P2: lock structure address • P3: 100 * mode requested + namespace# • Waits should be rare.

8. Library Cache Pins • Library cache pins are acquired on the library cache data heaps. • The lock on the handle must be acquired first. • During call execution, the pins are acquired: • In shared mode: • To read the data heaps • To prevent modification of dependent objects • In exclusive mode: • To modify the data heaps • Lock implementation: • The object handle is the resource structure. • Pins are allocated from the shared pool. • Types NA…NZ.

9. Wait Event: LibraryCachePin • The wait event library cache pin indicates a waiting to acquire a library cache pin • Wait duration is three seconds (only one second for PMON). • The wait parameters are: • P1: object handle address • P2: pin address • P3: 100 * mode requested + namespace# • Waits are rare, except on pipes and sequences.

10. DML Locks • DML locks can be either: • Table locks, which ensure object definition consistency for the duration of entire transactions • Row locks, which ensure data consistency for the duration of entire transactions

11. DML Table Locks: Implementation • DML table locks are implemented as TM enqueues. • DML locks are normal enqueues with an additional conversion history table. • The structure ktadm wraps around ksqlk to provide the required history table. • The wait parameters for TM enqueue waits are: • P1: name | mode • P2: object ID • P3: 0

12. Disabling DML Table Locks • Possible performance improvements: • Reducing locking overheads • Preventing blocking locks • Eliminating maintenance of foreign key indexes • Disabling drop and alter statements • DML table locks can be disabled in two ways: • Setting DML_LOCKS to zero • Using the ALTER TABLE command to disable table locks

14. DML Row Locks • Lock implementation is a combination of: • Row-level locks • Transaction locks • Row-level locks are implemented through: • Lock bytes in each row header • Interested transaction lists (ITLs) in each data or index block. • Transaction locks are implemented as TX enqueues.

15. Row-Level Locks Cache layer Transaction layer ITL1 XID 01 ITL2 XID 02 Tx2 row 1 2 row 2 1 Tx1 row 3 1

16. Row-Level Conflict EnqueueTX Cache layer Transaction layer Tx1:X Held ITL1 XID 01 ITL2 XID 02 ITL3 XID 03 Requested Tx2:X Tx3:X Tx2 row 1 2 row 2 1 Tx3 row 3 1

18. Transaction 1 Transaction 2 >UPDATE employee SET salary = salary * 1.1 WHERE employee_id=100; >1 row updated; > >UPDATE employee SET salary = salary * 1.1 WHERE employee_id=100; >1 row updated; > COMMIT / ROLLBACK > > 9:00 9:05 ...10:30 .. 11:30 >ALTER SYSTEM KILL SESSION '10,23'; Resolving Row-Level Conflict

20. ITL Contention Tx3 Cache layer MAXTRANS=2 Transaction layer ITL1 Tx4 XID 01 ITL2 XID 02 Tx2 row 1 2 row 2 1 Tx1 row 3 1

21. Transaction Locks • Transaction locks correspond to active transactions. • They are listed in the transaction table in the rollback segment header block. • The transaction identifiers in the ITLs point to these entries in the transaction table. • These locks are implemented as TX enqueues. • The wait parameters for TX enqueue waits are: • P1: name | mode • P2: rbs# | wrap# • P3: slot#

22. Transaction Identifiers • Transaction identifiers (XID) uniquely identify a transaction within the system. They are used within the ITL of a data or index block. • A transaction identifier consists of: • Rollback/undo segment number • Transaction table slot number • Sequence number or wrap# XID = usn# . slot# . wrap#

23. Transaction Identifiers ITL Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0005.00b.00000ce6uba: 0x00c0261.0304.01 ---- 1 fsc 0x0000.00000000 usn# XID 0x0005.00b.00000ce6 wrap# slot# 0x0b 0x0ce6 Data Block RBU Header

24. TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num ------------------------------------------------------------------------------ ------------------ 0x00 10 0x80 0x58c4 0x0002 0x0000.0126216e 0x0080019e 0x0000.000.00000000 0x00000001 0x00000000 0x01 9 0x00 0x58c3 0x0002 0x0000.01261e28 0x0080019d 0x0000.000.00000000 0x00000001 0x00000000 . . . . Output truncated 0x07 9 0x00 0x58c3 0x0008 0x0000.01261e8f 0x0080019d 0x0000.000.00000000 0x00000001 0x00000000 0x08 9 0x00 0x58c3 0x0009 0x0000.01261eaa 0x0080019d 0x0000.000.00000000 0x00000001 0x00000000 Transaction Table Dump SQL> alter system dump undo header '_SYSSMU1$';

25. Finding the Row That Is Locked • Waits for a TX enqueue do not indicate which row is causing the contention. • V$SESSION provides information to find out the actual row: • ROW_WAIT_OBJ# • ROW_WAIT_FILE# • ROW_WAIT_BLOCK# • ROW_WAIT_ROW#

26. Buffer Locks • Protect the integrity of blocks in the buffer cache • Lock implementation: • The buffer header acts as the resource structure. • Sessions use buffer handles to access buffers. • The buffer handles act as the lock structures. • Buffer handles are allocated dynamically from the shared pool.

27. Wait Event: BufferBusyWaits • The wait event buffer busy waits indicates waiting for a buffer lock because the buffer is being read or modified. • The wait parameters are: • P1: absolute file number • P2: block number • P3: ID (reason code) • The timeout is normally one second.

29. Buffer Lock Contention • Contention for blocks in the buffer cache is shown in • V$WAITSTAT according to the block class: • Bitmap block • Bitmap index block • Data block • Extent map • Free list • Save undo block • Save undo header • Segment header • Sort block • System undo block • System undo header • Undo block • Undo header

31. Data Block Contention • If waits are for reads, then eliminate the use of nonselective indexes. • If waits are for modifications, then: • Reduce the row density • Change PCTFREE and/or PCTUSED. • Increase INITRANS. • Reduce the number of rows per block by usingMINIMIZE RECORDS_PER_BLOCK. • Reduce DB_BLOCK_SIZE. • Avoid “right-hand” indexes

33. Undo Segment Contention • Waits for “undo header” is an indication that you need more undo segments. • Waits for “undo block” indicates inappropriate caching of undo segments. • Waits for “system undo block” and “system undo header”refer to the system RBU (very rare). • Waits for “save undo block” and “save undo header” refer to deferred RBU (extremely rare).

34. Diagnosing Undo Segment Header Contention • The ratio of the sum of waits to the sum of gets should be less than 5%. • If not, then create more rollback segments. SQL> SELECT sum(waits)* 100 /sum(gets) "Ratio", 2 sum(waits) "Waits", sum(gets) "Gets" 3 FROM v$rollstat; Ratio Waits Gets --------- --------- --------- 0.296736 5 1685

36. Index Block Contention • Similar to data block contention • Two notable exceptions: • Index block split • Bitmap index updates

37. Free List Contention • Occurs when multiple sessions are trying to allocate or deallocate blocks simultaneously • Is seen as waits for segment header blocks or waits for free-list blocks (V$WAITSTAT)

38. Resolving Free List Contention • After the lack of free lists has been identified (from • V$WAITSTAT) • Query V$SESSION_WAIT (get file/block) • Query DBA_EXTENTS (get object name) • Get free lists for segment • Re-create the object in question or allocate more free lists dynamically • Consider using ASSM segments SQL> ALTER TABLE tab1 STORAGE (freelists n);

39. Wait Event: WriteCompleteWaits • The wait event write complete waits indicates waiting for a buffer lock because the buffer is being written due to aging. • The wait parameters are: • P1: absolute file number • P2: block number • P3: ID (reason code) • The timeout for these waits is one second.

40. Sort Locks • Are of two types: • Temporary table locks (TS) for permanent tablespaces • Sort segment locks (SS) for temporary tablespaces • Keep track of disk sort space usage • Are implemented as fixed arrays in the SGA • Are sized by the SESSIONS parameter • Do not participate in lock conflicts, waits, or deadlocks

41. ORA-1575 • ORA-1575 represents a timeout waiting for the space management enqueue (ST) • To reduce contention for this enqueue: • Use locally managed tablespaces • Use temporary tablespaces for sort segments • Increase SORT_AREA_SIZE to reduce disk sorts • Use sensible extent sizes and set PCTINCREASE to 0

42. SMON Functions • SMON is implemented by ktmmon(). • The main functions of SMON are: • Merging or coalescing free extents • Cleaning up temporary segments • Cleaning up nonexistent objects in OBJ$ • Cleaning up IND$ if online builder crashes • Shrinking undo segments • Transaction recovery on startup • Transaction rollback (when posted by PMON)

43. Coalescing Free Space • SMON performs tablespace coalescing every 5 minutes. • It calls ktsclsb() to coalesce five groups of extents. • The function merges extents in system-managed tablespaces and where PCTINCREASE > 0 • The ST enqueue is held during each merge. • To identify if SMON has coalesced, the following query displays a summary by tablespace: SQL> SELECT count(*) 2 FROM dba_free_space_coalesced;

44. Disabling Background Coalescing • Background coalescing should not be a problem. • It can be reduced by: • Using locally managed tablespaces • Setting PCTINCREASE to 0 as the default for tablespaces • It can be disabled completely by setting event 10269.

45. Temporary Segment Cleanup • SMON performs this task every two hours. • It calls the function ktssdt_segs() to drop all the stray temporary segments. • If many processes are aborted while holding temporary segments, then the work is considerable. • The TS (temporary segment) enqueue is acquired each time SMON finds a segment to be dropped. • To identify if SMON is doing the cleanup, check the number of temporary segments. SQL> SELECT count(*) 2 FROM dba_extents 3 WHERE segment_type = 'TEMPORARY';

46. Disabling Temporary Segment Cleanup • Temporary segment cleanup should not be a problem. • It can be reduced by: • Using sort segments in temporary tablespaces • Encouraging users not to abort processes • It can be disabled completely by setting event 10061.

47. Summary • In this lesson, you should have learned about: • Internal implementation of different types of locks • Diagnosing and tuning lock contention • The main functions of the SMON process

48. References • WebIV Notes 34405.1, 34540.1, 34566.1, 34578.1, 34579.1, and 34609.1 • Source Code: ktm.c,kql.c,ktscl.c