Prev: location of oracle asmlib disks in solaris
Next: Multiple SQL_IDs....why?
From: Steve Howard on 24 Feb 2010 13:16
On Feb 24, 9:12 am, John Hurley <johnbhur...(a)sbcglobal.net> wrote:
> On Feb 24, 8:10 am, Steve Howard <stevedhow...(a)gmail.com> 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 24 Feb 2010 14:50
On Feb 23, 9:25 am, Steve Howard <stevedhow...(a)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(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
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(a)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(a)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
From: joel garry on 25 Feb 2010 12:31
On Feb 25, 1:08 am, Randolf Geist <mah...(a)web.de> wrote:

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

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

Thanks!

jg
--
@home.com is bogus.
http://gizmodo.com/5021550/great-giz-ideas-harass-your-neighbors-with-your-wi+fi-hotspot-name
First  |  Prev  | 
Pages: 1 2
Prev: location of oracle asmlib disks in solaris
Next: Multiple SQL_IDs....why?