Re: Reverse engineer SQL_TEXT from SQL_ID?

From: Steve Howard <>
Date: Wed, 24 Feb 2010 11:50:14 -0800 (PST)
Message-ID: <>

On Feb 23, 9:25 am, Steve Howard <> 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 event.

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_at_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 tim=1237342826798559


518507:WAIT #8: nam='enq: HW - contention' ela= 13377 name| mode=1213661190 table space #=6 block=25167811 obj#=21898 tim=1237344106584665
518509:WAIT #8: nam='enq: HW - contention' ela= 110350 name| mode=1213661190 table space #=6 block=25167811 obj#=-1 tim=1237344106696135
14:40:04 oracle_at_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_at_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

SQL> 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 reaching....


Steve Received on Wed Feb 24 2010 - 13:50:14 CST

Original text of this message