Re: odd output from ASH query in 10g

From: Seth Miller <sethmiller.sm_at_gmail.com>
Date: Wed, 3 Sep 2014 22:29:13 -0500
Message-ID: <CAEueRAU85YLET3Sndjh8Ja_GSq0ayo3kc=YCm5y+ZpuJRUHrjQ_at_mail.gmail.com>

You will have to post some examples of what you are seeing if you would like some feedback but I have a couple of observations.

Explain plan <> execution plan. The actual plan used can be completely different from the explain plan. Use autotrace or 10046 to see the actual execution plan.

Is there a possibility that the object you are seeing is being used by a recursive query rather than the original query?

Seth Miller

On Mon, Aug 11, 2014 at 4:07 PM, Dba DBA <oracledbaquestions_at_gmail.com> wrote:

> Yeah its old. Have some DBs in 10.2.0.4. Yeah I know desupported.
>
> This is a query I like to run in 11g. It tells me which objects I spend
> the most time on for a given query. helps when i have a complex plan.
>
> Query is below
>
> top events in order
> null, latch: cache buffers chains, db_file scattered read
>
> None of that is surprising. However, the top 'OBJECT_NAME', is an index
> thats not on a table used by the query and doesn't show up in the explain
> plan.
>
> What am I missing? I don't recall having this issue in 11.2+ Is this a 10g
> issue? Back in 10g, I didnt use ASH much. I usually used 10046 trace. So
> not sure.
>
> select event,object_name,count(*)
> from v$active_session_history a, dba_objects b
> where a.current_obj#=b.object_id
> and a.sample_time > SYSDATE - 2/24
> and sql_id = <MYQUERY_ID>'
> group by event,object_name
> order by 3 desc
>

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

Received on Thu Sep 04 2014 - 05:29:13 CEST