Re: odd output from ASH query in 10g
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: oracledbaquestions@xxxxxxxxx
- Date: Thu, 4 Sep 2014 10:02:59 -0700
OBJECT_NAME is not filled out for cache buffer chains so AFAIK if there is
a value there it is a value left over from some previous wait event.
Similarly a "null" wait is a bug so questionable how much one can do with
that data.
db file scattered read should have the correct OBJECT_NAME though you can
check p1 and p2 and look it up in the extent map to double check it. I've
seen instances where OBJECT_NAME was incorrect and p1 and p2 were correct.
- Kyle
On Mon, Aug 11, 2014 at 2:07 PM, Dba DBA <oracledbaquestions@xxxxxxxxx>
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
>
Other related posts:
