Craig Shallahamer's Blog
|
You were brought to this page based on an internet search
and as a free service to Oracle DBAs.
The text below is an except from the book,
Oracle Performance Firefighting, written by
Craig Shallahamer of
OraPub, Inc.
Figures and tables are not included on this page, only their reference.
To order the book in either print or PDF form, click
here.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
-------------------------------
<p>Prior to Oracle Database 10g, the wait event for all enqueues was simply enqueue. This is indeed unfortunate, as it requires the firefighter to sample from either v$lock or v$session_wait to determine the enqueue name. The SQL to determine the enqueue name from v$session_wait, shown in Figure 6-31, is particularly unsavory. When run, the Figure 6-31 SQL provides details for every session waiting for an enqueue. Figure 6-32 shows one such example, where three sessions are involved with a table lock.
</p><p>In Figure 6-32, session 4388 has the table locked, is not waiting for the lock, and therefore is not shown. The first session in queue is session 4387, followed by session 4393. A simple way to determine the SQL being run and hence the table involved is to query the session's sql_address or sql_hash_value from v$session. For the TM enqueue, the table can be easily identified since the p2 column (also known as the ID 1 column) contains the object_id, which can be referenced in dba_objects. This makes determining the object of contention very simple. Oracle is constantly adding enqueues, so I have found the best way to get enqueue identifier details is to perform a very specific Internet search.
</p><p>Figure 6-31. The core and working SQL from the OSM swenq.sql script, which provides enqueue wait details for pre-Oracle Database 10g systems.
</p>
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
|
|
Know what's important before it's too late!
OraPub's
Performance Training
is like no other...
More Class Pics...
Get student testimonials!
|
