Re: Reverse engineer SQL_TEXT from SQL_ID?

From: joel garry <joel-garry_at_home.com>
Date: Thu, 25 Feb 2010 09:31:50 -0800 (PST)
Message-ID: <2d4cf409-0a0b-47f2-9cb6-70f2d4ca0155_at_a5g2000prg.googlegroups.com>

On Feb 25, 1:08 am, Randolf Geist <mah..._at_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

--
_at_home.com is bogus.
http://gizmodo.com/5021550/great-giz-ideas-harass-your-neighbors-with-your-wi+fi-hotspot-name

Received on Thu Feb 25 2010 - 11:31:50 CST