Prev: location of oracle asmlib disks in solaris
Next: Multiple SQL_IDs....why?
From: Steve Howard on 23 Feb 2010 09:25
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
From: John Hurley on 23 Feb 2010 11:32
On Feb 23, 9:25 am, Steve Howard <stevedhow...(a)gmail.com> wrote:

snip

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

Just query against v$sqlarea for the sql_id?
From: joel garry on 23 Feb 2010 12:12
On Feb 23, 6: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

Must be possible, since dbconsole does it. However, I have noticed
some things with waits that don't seem to have SQL associated with it,
apparently associated with dbwriter, ckpt, logwriter, etc. I've also
noticed you can often figure out what dbconsole is doing with
dbconsole, since you can select for OMS and browse the cursors and
associated sql id's. Even if you totally despise GUI's, sometimes
they can give clues on what to do in your preferred tools.

Don't forget, the sql that is executing may only be part of a chain of
events that cause the waits. For example, I have one active
controlfile on my redo device, which makes for some odd waiting when
massive updates are happening, and I expect sql to be associated with
it, and there is none, which is obvious in retrospect.

Also see http://www.ora-solutions.net/web/2009/03/19/is-data-in-dba_hist_sqltext-aged-out/
in case it is very old sql. I think it is also possible it is just
flushed out of SGA too fast. There are plenty of scripts floating
about for showing the sql from the sql_id. But note this:

select count(*) from (
select sql_id from dba_hist_sqltext
where sql_id not in (select distinct sql_id from dba_hist_sqlstat)
)
/

I think AWR has some way to output html links to sql stuff too,
google. But of course, you are probably already doing the same thing.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2010/feb/23/ucsd-media-outlets-see-funds-frozen/
From: Steve Howard on 24 Feb 2010 08:10
On Feb 23, 12:12 pm, joel garry <joel-ga...(a)home.com> wrote:
>
> select count(*) from (
> select sql_id from dba_hist_sqltext
> where sql_id not in (select distinct sql_id from dba_hist_sqlstat)
> )
> /

> I think AWR has some way to output html links to sql stuff too,
> google.  But of course, you are probably already doing the same thing.
>
> jg
> --
> @home.com is bogus.http://www.signonsandiego.com/news/2010/feb/23/ucsd-media-outlets-see...

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.

Thanks,

Steve
From: John Hurley on 24 Feb 2010 09:12
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 )?

 |  Next  |  Last
Pages: 1 2
Prev: location of oracle asmlib disks in solaris
Next: Multiple SQL_IDs....why?