Diagnosing Locking Problems using ASH – Part 4

Doug's Oracle Blog

  • Home
  • Papers
  • Books
  • C.V.
  • Fun
  • Oracle Blog
  • Personal Blog

Apr 9: Diagnosing Locking Problems using ASH – Part 4

Some features in this post require a Diagnostics Pack license.

No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!)
"It's probably worth pointing out that NO TOOL can claim to identify the blocking SID->SQL in such a case as there is no relationship kept in Oracle between locks acquired by users and the SQL that acquired the locks"

I’ll try to deal with that point and some other thoughts I had here in a further example. For this example, I decided to suggest how to create a quick table so that you can test this out for yourself.

SQL> create user testuser identified by testuser
  2  default tablespace USERS
  3  temporary tablespace TEMP;

User created.

SQL> grant create session, create table to testuser;

Grant succeeded.

SQL> connect testuser/testuser

Connected.

SQL> create table test_tab1 as select object_id pk_id, object_name from all_objects;

Table created.

As easy as that.

In the last part I showed that the SQL_ID for the last sample of a blocking session immediately before it went idle might not be the SQL statement holding the lock. In that case, it was fairly obvious that it wasn’t because it was a simple query that couldn’t have been responsible. However, it’s likely to be even more confusing if the SQL_ID captured by the ASH sample *looks* like it’s responsible, but isn’t, and the previous example didn't illustrate that clearly.

Session 1 – Connected as TESTUSER
SQL> select pk_id, object_name from test_tab1 order by pk_id desc for update;

<Trimmed>

       890 DBMS_STANDARD
       889 DBMS_STANDARD

     PK_ID OBJECT_NAME
---------- ------------------------------
       887 STANDARD
       886 RE$NV_LIST
       605 MAP_OBJECT
       319 STMT_AUDIT_OPTION_MAP
       317 STMT_AUDIT_OPTION_MAP
       316 TABLE_PRIVILEGE_MAP
       314 TABLE_PRIVILEGE_MAP
       313 SYSTEM_PRIVILEGE_MAP
       311 SYSTEM_PRIVILEGE_MAP
       259 DUAL
       258 DUAL

4477 rows selected.

SQL> rollback;

Rollback complete.

SQL> select pk_id from test_tab1 where object_name='SYSTEM_PRIVILEGE_MAP' for update;

     PK_ID
----------
       311
       313

SQL> rollback;

Rollback complete.

SQL> select pk_id from test_tab1 where pk_id=313 for update;

     PK_ID
----------
       313

SQL>

So session 1 has executed three different queries, all of which lock one or more rows including the row with PK_ID=313, has rolled back the first two (releasing the locks) and has just that row locked now.

Session 2 – Connected as TESTUSER
SQL> select pk_id from test_tab1 where pk_id=313 for update;

Session 2 hangs, waiting for the lock.

Session 1 – Connected as TESTUSER
SQL> rollback;

Rollback complete.

So the lock is released and session 2 acquires the lock and then releases it

Session 2 – Connected as Testuser
     PK_ID
----------
       313

SQL> rollback;

Rollback complete.

OK, time to look at the ASH data for the last two minutes for TESTUSER sessions. (Note that I have added each session’s serial# to the query. Graham Wood corrected a statement I made in the last post …

“who is to say that that session 148 is the same session as the blocking session 148 and not an earlier session that has disconnected?”

… by pointing out that session_serial# would allow me to differentiate between different sessions where SID=148. I’ve updated the previous post to that effect.)

break on MY_TIME
column my_sid format 999
column my_ser format 99999
column my_state format a30
column my_blkr format 999
select to_char(a.sample_time, 'HH24:MI:SS') MY_TIME,a.session_id MY_SID,a.session_serial# MY_SER,
        DECODE(a.session_state, 'WAITING' ,a.event, a.session_state) MY_STATE,a.xid, a.sql_id,
        a.blocking_session MY_BLKR
from v$active_session_history a, dba_users u
where u.user_id = a.user_id
and u.username = 'TESTUSER'
and a.sample_time > SYSTIMESTAMP-(2/1440);

MY_TIME  MY_SID MY_SER MY_STATE                       XID              SQL_ID        MY_BLKR
-------- ------ ------ ------------------------------ ---------------- ------------- -------
12:53:01    300   2739 enq: TX - row lock contention                   2pa7rnqf65gj3     304
12:53:00    300   2739 enq: TX - row lock contention                   2pa7rnqf65gj3     304
12:52:59    300   2739 enq: TX - row lock contention                   2pa7rnqf65gj3     304
12:52:50    304   1445 ON CPU                         0006000E0000884D 95k0s60jznz76

So what statement is SQL_ID ‘95k0s60jznz76’?

SQL> select sql_text from v$sql where sql_id='95k0s60jznz76';

SQL_TEXT
-----------------------------------------------------------------------------
select pk_id, object_name from test_tab1 order by pk_id desc for update

Let’s face it, if you were to look at this data, how likely is it that you would think that that statement was responsible for blocking session 2, when it wasn't?
Posted by Doug Burns Comments: (0) Trackbacks: (5)
Defined tags for this entry: ash, locking
Related entries by tags:
Network Events in ASH
Alternative Pictures Demo
That Pictures demo in full
Diagnosing Locking Problems using ASH/LogMiner – The End
Diagnosing Locking Problems using ASH/LogMiner – Part 9
Diagnosing Locking Problems using ASH/LogMiner – Part 8
Diagnosing Locking Problems using ASH/LogMiner – Part 7
Diagnosing Locking Problems using ASH – Part 6
Diagnosing Locking Problems using ASH – Part 5
Diagnosing Locking Problems using ASH - Part 3

Trackbacks
Trackback specific URI for this entry

Diagnosing Locking Problems using ASH – Part 6
&quot;Toto, I've a feeling we're not in Kansas any more.&quot;What started as a simple write-up of a course demo gone wrong (or right, depending the way you look at these things) has grown arms and legs and staggered away from the original intention to ta
Weblog: Doug's Oracle Blog
Tracked: Apr 20, 20:20
Diagnosing Locking Problems using ASH/LogMiner – Part 8
So what about those SELECT FOR UPDATEs?I know that they’ll generate redo entries and so something should appear in both log file dumps and the LogMiner output, but what exactly will appear? (This is all on Oracle 10.2.0.4)For this post I’ll go back to
Weblog: Doug's Oracle Blog
Tracked: Apr 23, 09:43
PingBack
Weblog: jonathanlewis.wordpress.com
Tracked: May 15, 10:24
PingBack
Weblog: karlarao.wordpress.com
Tracked: Aug 15, 12:59
PingBack
Weblog: www.oraclerealworld.com
Tracked: Jul 25, 16:32

Comments
Display comments as (Linear | Threaded)

No comments


Add Comment

Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
BBCode format allowed
 
 

Statistics on Partitioned Tables

Contents

Part 1 - Default options - GLOBAL AND PARTITION
Part 2 - Estimated Global Stats
Part 3 - Stats Aggregation Problems I
Part 4 - Stats Aggregation Problems II
Part 5 - Minimal Stats Aggregation
Part 6a - COPY_TABLE_STATS - Intro
Part 6b - COPY_TABLE_STATS - Mistakes
Part 6c - COPY_TABLE_STATS - Bugs and Patches
Part 6d - COPY_TABLE_STATS - A Light-bulb Moment
Part 6e - COPY_TABLE_STATS - Bug 10268597

A couple of posts about Incremental Stats confusion

Part 1
Part 2

Comments

personal blog about Moving Sideways
Wed, 01.06.2016 17:34
That is a good tip particularl y to those fresh to the blogos phere. Short [...]
odziezprestige.pl about Moving Sideways
Wed, 01.06.2016 16:07
Please let me know if you're l ooking for a article writer fo r your site. [...]
Doug Burns about Moving Sideways
Tue, 10.05.2016 21:43
Oh, I won't give it that long unless I enjoy it ;-)

Bookmark

Open All | Close All

Syndicate This Blog

  • XML RSS 2.0 feed
  • ATOM/XML ATOM 1.0 feed
  • XML RSS 2.0 Comments
  • Feedburner Feed

Powered by

Serendipity PHP Weblog

Show tagged entries

xml 11g
xml ACE
xml adaptive thresholds
xml ASH
xml Audit Vault
xml AWR
xml Blogging
xml conferences
xml Cuddly Toys
xml Database Refresh
xml dbms_stats
xml Direct Path Reads
xml Fun
xml Grid Control
xml hotsos 2010
xml listener
xml locking
xml oow
xml oow2009
xml optimiser
xml OTN
xml Parallel
xml partitions
xml Patching
xml Swingbench
xml The Reality Gap
xml Time Matters
xml ukoug
xml ukoug2009
xml Unix/Shell
xml Useful Links

Disclaimer

For the avoidance of any doubt, all views expressed here are my own and not those of past or current employers, clients, friends, Oracle Corporation, my Mum or, indeed, Flatcat. If you want to sue someone, I suggest you pick on Tigger, but I hope you have a good lawyer. Frankly, I doubt any of the former agree with my views or would want to be associated with them in any way.

Design by Andreas Viklund | Conversion to s9y by Carl