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 | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 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.
