Re: Reverse engineer SQL_TEXT from SQL_ID?
Date: Tue, 23 Feb 2010 09:12:48 -0800 (PST)
Message-ID: <f2544e6a-f922-44a8-a11d-d54a88fdc9b6_at_b36g2000pri.googlegroups.com>
On Feb 23, 6: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
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
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/feb/23/ucsd-media-outlets-see-funds-frozen/Received on Tue Feb 23 2010 - 11:12:48 CST