odd output from ASH query in 10g

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Mon, 11 Aug 2014 17:07:02 -0400
Message-ID: <CAE-dsOKO8Hk2t0-JktKnPs26QbtPaPNMtpbk-Tt0VB89TTq-jA_at_mail.gmail.com>

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 Mon Aug 11 2014 - 23:07:02 CEST