1 / 26

Oracle9 i Performance Tuning

Oracle9 i Performance Tuning. Chapter 9 Detecting Lock Contention. Chapter Objectives. Learn about locks and their uses Differentiate lock types Understand different lock modes Learn to distinguish between different locking levels Learn to use the LOCK statement

katell-williamson
Download Presentation

Oracle9 i Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle9iPerformance Tuning Chapter 9 Detecting Lock Contention

  2. Chapter Objectives • Learn about locks and their uses • Differentiate lock types • Understand different lock modes • Learn to distinguish between different locking levels • Learn to use the LOCK statement • Learn the impact of the SELECT...FOR UPDATE statement on transactions Chapter 9: Detecting Lock Contention

  3. Chapter Objectives (continued) • Work through some practical examples of locking • Work with the DML_LOCKS initialization parameter • Use the SET TRANSACTION statement in transactions • Understanding the workings of deadlocks • Detect and resolve lock contention • Learn the Best Practices for detecting lock contention Chapter 9: Detecting Lock Contention

  4. Oracle Locking Process Chapter 9: Detecting Lock Contention

  5. Lock Characteristics • Locks: • Enforce consistency and integrity; data and objects maintain their integrity and consistency for the duration of the transaction • Provide a queue structure that allows all sessions to join a queue for the object when the object is not available immediately • Oracle automatically handles lock mechanisms • The duration of the lock is equal to the length or processing time of the transaction submitted Chapter 9: Detecting Lock Contention

  6. Lock Types Chapter 9: Detecting Lock Contention

  7. Locks and Transactions Chapter 9: Detecting Lock Contention

  8. Lock Modes Chapter 9: Detecting Lock Contention

  9. Lock Modes and DML Statements Chapter 9: Detecting Lock Contention

  10. DDL Statements and Lock Modes Chapter 9: Detecting Lock Contention

  11. Locking Levels • There are four levels of locks that can be implemented in a database: • Database level • Table level • Row level • Column level Chapter 9: Detecting Lock Contention

  12. Using Oracle Enterprise Manager Chapter 9: Detecting Lock Contention

  13. SHARE (S) Lock Mode Chapter 9: Detecting Lock Contention

  14. ROW SHARE (RS) Lock Mode Chapter 9: Detecting Lock Contention

  15. ROW EXCLUSIVE (RX) Lock Mode Chapter 9: Detecting Lock Contention

  16. SHARE ROW EXCLUSIVE (SRX) Lock Mode Chapter 9: Detecting Lock Contention

  17. EXCLUSIVE (X) Lock Mode Chapter 9: Detecting Lock Contention

  18. SELECT…FOR UPDATE Chapter 9: Detecting Lock Contention

  19. DML_LOCKS Initialization Parameter • The DML_LOCKS initialization parameter: • Allows values in the range of 20 to an unlimited value, inclusive • Cannot be modified dynamically Chapter 9: Detecting Lock Contention

  20. SET TRANACTION Statement • SET TRANSACTION can be set to SERIALIZABLE or READ COMMITTED • SERIALIZABLE: If a DML statement is attempting to update data in an object that has been updated and committed by another session, the DML statement fails • READ COMMITTED:If a DML statement is attempting to update data in an object that has been updated by another session and not committed at any time during the session, the DML statement waits until the other session completes its transaction • This is the default behavior as shown in all previous examples Chapter 9: Detecting Lock Contention

  21. Deadlocks Chapter 9: Detecting Lock Contention

  22. Illustration of the Deadlock Process Chapter 9: Detecting Lock Contention

  23. Detecting and Resolving Lock Contention • Use the following tools to detect and resolve lock contention: • V$LOCK view • V$LOCKED_OBJECT view • DBA_BLOCKERS view • DBA_WAITERS view • UTLLOCKT.SQL script • DBMS_LOCK package Chapter 9: Detecting Lock Contention

  24. Detecting and Resolving Lock Contention (continued) Chapter 9: Detecting Lock Contention

  25. Using Oracle Enterprise Manager Chapter 9: Detecting Lock Contention

  26. Summary • A lock is a mechanism that protects a database object from being altered while it is being modified by other processes or users • An enqueue is a data structure for locks that informs Oracle of who is waiting for a resource that is locked by another session • Locks are held and released by Oracle automatically according to the start and completion of a transaction • Application logic indirectly controls locks • The lowest lock level Oracle provides is at the row level • DBAs use LOCK statements to manually lock a table in any desired lock mode Chapter 9: Detecting Lock Contention

More Related