Re: tracking down hidden SQL???

From: Tom Dale <tom.dale_at_fivium.co.uk>
Date: Mon, 21 Oct 2013 15:41:03 +0100
Message-ID: <CAGDf7wQqbvyvPcP6ZRvP7vZaYuoLqKJSprJpkDTpz5Wrz35RbA_at_mail.gmail.com>

Hi David,
Something like that has helped me before. Basic idea : its not in v$sql, since you don't know what it is, but should be in ash since its causing you a problem! Then use the hash, this is one of my old sql's But as Kerry has said, his blog is the best info on this.

with hash_plans

--
-- sql plans for this sql_id
--
as
(
select
    --+ materialize
    distinct sql_plan_hash_value
from
    v$active_session_history a
where
    sql_id not in (select sql_id from v$sql)
and sql_id               = :sql_id
and sql_plan_hash_value != 0
and a.sample_time        > sysdate - 2/24
)
select
     --
     -- Get the real sql
     --
     distinct
         s.parsing_schema_name


,        sp.sql_id

,        s.sql_text

,        to_char( s.last_active_time, 'hh24:mi:ss' ) last_active_time_str

,        s.last_active_time

,        round( ( s.elapsed_time/decode( s.executions,0,1,s.executions

)/1000000 ), 2 ) exec_secs
from
     v$sql_plan sp
join hash_plans  hp on hp.sql_plan_hash_value = sp.plan_hash_value
join v$sql      s  on s.sql_id = sp.sql_id
where
     -- not sys etc
     s.parsing_user_id > 51
and  last_active_time  > sysdate - 2/24
     -- if we are looking at this then
     --  must have taken a bit of time
and  s.elapsed_time/decode(s.executions,0,1,s.executions) > 1000
order by
     s.last_active_time desc


On Mon, Oct 21, 2013 at 2:56 PM, David Kurtz <info_at_go-faster.co.uk> wrote:

> Can you find this SQL_ID in your ASH data (assuming the diagnostics pack is
> licensed)?
> If you know the SQL_PLAN_HASH_VALUE you could try to find other SQL_IDs
> with
> the same plan in DBA_HIST_SQL_PLAN.
> Is this a piece of recursive SQL?  Is SQL_ID the same as TOP_LEVEL_SQL_ID
> in
> the ASH data?
>
> If the app does set a specific module/action for this SQL (again look in
> the
> ASH data) then you can enable trace just for that module/action.
>
> regards
> _________________________
> David Kurtz
> tel: +44 (0)7771 760660
> mailto:david.kurtz_at_go-faster.co.uk
>
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Adric Norris
> Sent: 21 October 2013 14:40
> To: Chris Taylor
> Cc: jonathan_at_jlcomp.demon.co.uk; oracle-l
> Subject: Re: tracking down hidden SQL???
> Importance: High
>
> On Thu, Oct 17, 2013 at 9:48 AM, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
> > Hey, the other thing you could do, if you know some identifying
> > information for these sessions, like OSUSER, machine/terminal,
> > client_info etc, you could setup a LOGIN TRIGGER that enables a 10046
> > trace and capture any/all SQL executed in the session.
> >
> > Some apps (like PeopleSoft) will populate client_info so it makes it
> > easier to trace one session instead of all the connected sessions.
> > Maybe you have something available to you that will enable you to
> > create a limited trace on a few sessions.
> >
>
> So far there doesn't seem to be any distinguishing info for the sessions,
> which would make it easy to separate them from other app-related
> connections.  If I'm able to come up with something, however, I'll
> certainly
> consider doing this.
>
> Thanx!
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 21 2013 - 16:41:03 CEST