Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted July 31, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Oracle Transaction En-queue Waits and Locked Objects

By David Fitzjarrell

Recently a question was posted on the Oracle General database forum asking how to find the locked object when the "Top Event P1/P2/P3" values section of an ASH report looks like this (obviously the Diagnostic Pack license is required for such an investigation):

Top Event P1/P2/P3 Values

    Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3
    TCP Socket (KGAS) 7.23 "6","0","0" 7.22      
    db file sequential read 3.60 "89","103 646","1" 0.01 file# block# blocks
    enq: TX - row lock contention 3.47 "1415053318","2293781","452437" 0.55 name|mode usn<<16 | slot sequence
    SQL*Net message from dblink 1.73 "1952673792&quo t;,"1","0" 1.73 driver id #bytes NOT DEFINED
    log file sync 1.59 "5","1137635569", "0" 0.00 buffer# sync scn NOT DEFINED

    Note the highlighted section; this is where the P1/P2/P3 values provide data that can be used to find the object locked by that transaction. Depending upon the length of time the AWR/ASH data is preserved it may be possible to determine the object that was locked at the time that transaction took place. Such an investigation requires a bit more data from the AWR/ASH snapshots than what is provided in an ASH report; fortunately, the ASH data records the current_obj# for such events so a query like the one below can be used to return that object name:

    
    PLAGNEZIUS @ flurgenhoot > with objid as(
      2  select /*+ materialize */ current_obj#
      3  from dba_hist_active_sess_history
      4  where p3 = &p3_val
      5  )
      6  select object_name
      7  from dba_objects
      8  where object_id in (select current_obj# from objid)
      9  /
    Enter value for p3_val: 452437
    old   4: where p3 = &p3_val
    new   4: where p3 = 452437
    
    OBJECT_NAME
    --------------------------------------------------------------------------------------------------------------------------------
    APLIGATO_MOOSHI
    
    PLAGNEZIUS @ flurgenhoot >
    

    To be honest the names have been changed to obscure any reference to actual production tables but the query works as advertised. Notice the /*+ materialize */ hint in the WITH clause; this allows for Oracle to have an object (a temporary table) that it can dynamically sample for statistics, which speeds the query considerably. Without that hint, the query runs and runs and runs without providing any output; the non-hinted query was cancelled an hour into its run.

    To determine the number of days the ASH history is retained the following query can be used:

    
    SQL> select (max(sample_time)-min(sample_time))
      2  from dba_hist_active_sess_history
      3  /
    
    (MAX(SAMPLE_TIME)-MIN(SAMPLE_TIME))
    ---------------------------------------------------------------------------
    +000000064 09:00:13.695
    
    SQL>
    

    For this particular database, the retention period for ASH data is around 64 days; each database configuration can be different so execute the query in your own database to return the retention window currently configured. With this information in hand it's easier to determine if the report is too old to return any object data and prevents the DBA from wasting time chasing down an object that is no longer in the ASH history.

    For those who are interested, the execution plan for the locked object query is shown below:

    
    PLAGNEZIUS @ flurgenhoot > select * From table(dbms_xplan.display_cursor())
      2  /
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  8pxvmxpk15z2w, child number 0
    -------------------------------------
    with objid as( select /*+ materialize */ current_obj# from
    dba_hist_active_sess_history where p3 = 452437 ) select object_name
    from dba_objects where object_id in (select current_obj# from objid)
    
    Plan hash value: 1487989412
    
    ----------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                             |       |       |   147K(100)|          |       |       |
    |   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |       |       |
    |   2 |   LOAD AS SELECT                         |                             |       |       |            |          |       |       |
    |*  3 |    HASH JOIN OUTER                       |                             |   197 | 12017 | 45516   (1)| 00:00:05 |       |       |
    |*  4 |     HASH JOIN OUTER                      |                             |   197 |  9259 | 45509   (1)| 00:00:05 |       |       |
    |   5 |      PARTITION RANGE ALL                 |                             |   197 |  5910 | 45496   (1)| 00:00:05 |     1 |    10 |
    |*  6 |       TABLE ACCESS FULL                  | WRH$_ACTIVE_SESSION_HISTORY |   197 |  5910 | 45496   (1)| 00:00:05 |     1 |    10 |
    |*  7 |      TABLE ACCESS FULL                   | WRM$_SNAPSHOT               |  1457 | 24769 |    13   (0)| 00:00:01 |       |       |
    |   8 |     INDEX FAST FULL SCAN                 | WRH$_EVENT_NAME_PK          |  1689 | 23646 |     7   (0)| 00:00:01 |       |       |
    |*  9 |   FILTER                                 |                             |       |       |            |          |       |       |
    |  10 |    VIEW                                  | DBA_OBJECTS                 |   101K|  7806K|   101K  (1)| 00:00:10 |       |       |
    |  11 |     UNION-ALL                            |                             |       |       |            |          |       |       |
    |* 12 |      FILTER                              |                             |       |       |            |          |       |       |
    |* 13 |       HASH JOIN                          |                             |   101K|  7407K|   885   (3)| 00:00:01 |       |       |
    |  14 |        INDEX FAST FULL SCAN              | I_USER2                     |  2255 |  9020 |     6   (0)| 00:00:01 |       |       |
    |* 15 |        HASH JOIN                         |                             |   101K|  7012K|   877   (2)| 00:00:01 |       |       |
    |  16 |         INDEX FAST FULL SCAN             | I_USER2                     |  2255 | 51865 |     6   (0)| 00:00:01 |       |       |
    |* 17 |         TABLE ACCESS FULL                | OBJ$                        |   101K|  4740K|   870   (2)| 00:00:01 |       |       |
    |* 18 |       TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$            |     1 |     6 |     2   (0)| 00:00:01 |       |       |
    |* 19 |        INDEX RANGE SCAN                  | I_USER_EDITIONING           |     2 |       |     1   (0)| 00:00:01 |       |       |
    |  20 |       NESTED LOOPS SEMI                  |                             |     1 |    29 |     3   (0)| 00:00:01 |       |       |
    |* 21 |        INDEX RANGE SCAN                  | I_OBJ4                      |     1 |     9 |     2   (0)| 00:00:01 |       |       |
    |* 22 |        INDEX RANGE SCAN                  | I_USER2                     |     1 |    20 |     1   (0)| 00:00:01 |       |       |
    |* 23 |       TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$            |     1 |     6 |     2   (0)| 00:00:01 |       |       |
    |* 24 |        INDEX RANGE SCAN                  | I_USER_EDITIONING           |     2 |       |     1   (0)| 00:00:01 |       |       |
    |* 25 |      HASH JOIN                           |                             |    78 |  1638 |     7   (0)| 00:00:01 |       |       |
    |  26 |       INDEX FULL SCAN                    | I_LINK1                     |    78 |  1326 |     1   (0)| 00:00:01 |       |       |
    |  27 |       INDEX FAST FULL SCAN               | I_USER2                     |  2255 |  9020 |     6   (0)| 00:00:01 |       |       |
    |* 28 |    VIEW                                  |                             |     1 |    13 |     1   (0)| 00:00:01 |       |       |
    |  29 |     TABLE ACCESS FULL                    | PLAGNEZIUS_TEMP_0FD9D7369_4350709E |   197 |   985 |     3   (0)| 00:00:01 |       |       |
    ----------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("ASH"."DBID"="EVT"."DBID" AND "ASH"."EVENT_ID"="EVT"."EVENT_ID")
       4 - access("ASH"."SNAP_ID"="SNAP_ID" AND "ASH"."DBID"="DBID" AND "ASH"."INSTANCE_NUMBER"="INSTANCE_NUMBER")
       6 - filter("ASH"."P3"=452437)
       7 - filter("STATUS"=0)
       9 - filter( IS NOT NULL)
      12 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
                  (((PLAGNEZIUS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
                  "U"."SPARE2"=TO_NUMBER(PLAGNEZIUS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL) AND  IS NOT NULL)))
      13 - access("O"."SPARE3"="U"."USER#")
      15 - access("O"."OWNER#"="U"."USER#")
      17 - filter(("O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0 AND "O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
                  "O"."NAME"<>'_default_auditing_options_'))
      18 - filter("TYPE#"=:B1)
      19 - access("UE"."USER#"=:B1)
      21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
      22 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
                  "U2"."SPARE2"=TO_NUMBER(PLAGNEZIUS_CONTEXT('userenv','current_edition_id')))
           filter("U2"."SPARE2"=TO_NUMBER(PLAGNEZIUS_CONTEXT('userenv','current_edition_id')))
      23 - filter("UE"."TYPE#"=:B1)
      24 - access("UE"."USER#"=:B1)
      25 - access("L"."OWNER#"="U"."USER#")
      28 - filter("CURRENT_OBJ#"=:B1)
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
       - this is an adaptive plan
       - 3 Sql Plan Directives used for this statement
    
    
    75 rows selected.
    
    PLAGNEZIUS @ flurgenhoot >
    

    Having a "handle" on what data the ASH snapshots provide can help the DBA when questions like the one posted in the Oracle General Database forum pop up at work. As long as the Diagnostic Pack is licensed for your site such investigations should take a minimal amount of time. Unfortunately, a Statspack report won't provide the necessary data to conduct such an investigation; you'll need to license at least the Diagnostics Pack to generate AWR and ASH reports to find such information.

    See all articles by David Fitzjarrell



    Oracle Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    Oracle Forum
    Topic By Replies Updated
    Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
    Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
    no matching unique or primary key rcanter 1 April 25th, 12:32 PM
    Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM