Re: Reverse engineer SQL_TEXT from SQL_ID?
Date: Thu, 25 Feb 2010 01:08:14 -0800 (PST)
Message-ID: <3d19df97-c909-4783-bdbf-d24877440e24_at_v20g2000yqv.googlegroups.com>
On Feb 24, 9:07 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Feb 24, 2:50 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
>
>
> > On Feb 23, 9:25 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > > 10.2.0.3 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
>
> > <<snip>>
>
> > 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
> > 8
> > 138 #=6 block=25167811
> > 14:40:15 oracle_at_esbdb02pxdu /u02/admin/dledb/udump> sp
>
> > SQL*Plus: Release 10.2.0.3.0 - 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....
>
> > Thanks,
>
> > Steve
>
> This was useful for my base problem, and got me at least to where I
> can definitively state the problem is with the LOB.
>
> http://pauloferreiraportugal.blogspot.com/2009/10/lob-segments-and-en...
>
> However, I still can't figure out why I can't see the SQL.
>
> Thanks John and Joel,
>
> Steve
Steve,
you might hit the same issue as Kerry Osborne did some time ago:
http://kerryosborne.oracle-guy.com/2009/04/hidden-sql-why-cant-i-find-my-sql-text/
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 10.2.0.4 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.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Thu Feb 25 2010 - 03:08:14 CST