From: Steve Howard on
On Feb 24, 9:12 am, John Hurley <johnbhur...(a)> wrote:
> On Feb 24, 8:10 am, Steve Howard <stevedhow...(a)> wrote:
> snip
> > That is what is odd.  I couldn't find it anywhere, in any of the live
> > views, DBA_HIST views, GRID, etc.  An ASH report said "SQL
> > unavailable", but yet it had the SQL_ID.  The only time I have ever
> > seen this is when it was DDL , but nothing shows up as having been
> > changed during or since that time period.
> > It was executed several times over a 35 minute period (I noticed it
> > after the fact in a daily health check), yet no SQL was recorded.
> Did you look in the v$ views ( v$sqlarea or v$sql etc )?

Yes, sorry I wasn't clear. That is what I meant by "live" views.
From: Steve Howard on
On Feb 23, 9:25 am, Steve Howard <stevedhow...(a)> wrote:
> 64 bit Standard on SLES 10 two node RAC
> Hi All,
> Does anyone know if it is possible to reverse engineer the SQL_TEXT
> for a given SQL_ID?  We had large waits yesterday afternoon on 'enq:
> HW contention'.  I found the SQL_ID in DBA_HIST_ACTIVE_SESS_HISTORY
> that was being executed when the event was posted.  I also found the
> current_obj# for the sessions in the same view, but I don't know why
> it would be waiting on this event.  However, there is no SQL recorded
> in DBA_HIST_SQLTEXT, which makes me wonder if it was DDL.  However,
> there is nothing in DBA_OBJECTS with a DDL time in the last 24 hours.
> I would really like to look at the SQL if possible.  Is there any way
> to do this?
> Thanks,
> Steve

The plot thickens. I have a 10046 trace on the affected sessions, and
can see the event being posted. There is no SQL in the trace file
(no, I couldn't properly scope it per Cary Milsap, although I may have
to to now). I can get the object as that is posted as part of the

However, even taking that into consideration, the tablespace id and
block noted do not exist?? Or maybe I have the wrong assumption that
block= means a database block??

14:39:29 oracle(a)esbdb02pxdu /u02/admin/dledb/udump> grep -n HW *20691*
585:WAIT #8: nam='enq: HW - contention' ela= 2 name|mode=1213661190
table space #=6 block=25167811 obj#=21948 tim=1237342826601801
586:WAIT #8: nam='enq: HW - contention' ela= 196741 name|
mode=1213661190 table space #=6 block=25167811 obj#=21948


518507:WAIT #8: nam='enq: HW - contention' ela= 13377 name|
mode=1213661190 table space #=6 block=25167811 obj#=21898
518509:WAIT #8: nam='enq: HW - contention' ela= 110350 name|
mode=1213661190 table space #=6 block=25167811 obj#=-1
14:40:04 oracle(a)esbdb02pxdu /u02/admin/dledb/udump> grep -n HW *20691*
| awk '{print $12,$13}' | sort -n | uniq -c
138 #=6 block=25167811
14:40:15 oracle(a)esbdb02pxdu /u02/admin/dledb/udump> sp

SQL*Plus: Release - Production on Wed Feb 24 14:40:21 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> select segment_name from dba_extents where file_id in (select
file# from v$datafile where ts# = 6) and 25167811 between block_id and
block_id + blocks;

no rows selected


The tables noted as being the object= do have LOB's in them, so maybe
there could be some sorting internally?...yes, I know that's


From: joel garry on
On Feb 25, 1:08 am, Randolf Geist <mah...(a)> wrote:

> you might hit the same issue as Kerry Osborne did some time ago:

Dang, I knew it sounded familiar! Should've checked Kerry first.

> For LOB operations Oracle opens a separate cursor that is
> unfortunately not showing up in most of the instrumented areas. Kerry
> shows some ways how to obtain the information using V$OPEN_CURSOR and
> using the PREV_SQL_ID for identifying the statement causing the LOB
> operation.
> The contention on the LOB operation could be caused by what is
> described in the MOS article 740075.1. Since you're not on
> yet you probably can't use the EVENT 44951to work around the problem
> if you're using an ASSM tablespace.
> Your query on DBA_EXTENTS does not work since you first need to
> determine the correct file# and block from the DBA as shown in article
> 419348.1 - in a nutshell use DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE and
> DATA_BLOCK_ADDRESS_BLOCK to get the correct information.


-- is bogus.