Re: Top Activity in Percentage

From: Gerry Miller <gerry_at_millerandbowman.com>
Date: Sun, 06 Nov 2011 07:58:05 +1000
Message-ID: <4EB5B16D.1030002_at_millerandbowman.com>

 Hi LSC
A trace of the OEM session reveals that it is executing: SELECT event#, sql_id, sql_plan_hash_value, sql_opcode, session_id,

session_serial#, module, action, client_id, DECODE(wait_time, 0, 'W', 'C'),
1, time_waited, service_hash, user_id, program, sample_time, p1, p2, p3,
current_file#, current_obj#, current_block#, qc_session_id, qc_instance_id

FROM
v$active_session_history
WHERE sample_time between :1 and :2

so it looks like it retrieves all of the data it needs for the various displays and then calculates percentages etc from there.

However, you can get what you want by something like : select sql_id,
round(100*(count/sum(count) over ()),2) pct from (
SELECT sql_id, count(*) count,
FROM v$active_session_history
where sql_id is not null
and (sysdate-cast (sample_time as date))*24*60<=5 group by sql_id)
order by 2 desc
/

Regards

Gerry

LS Cheng wrote: Hi I wonder if anyone know how to obtain top activity in percentage using SQL as viewed from Enterprise Manager as explained in http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/img/top_activi ty_new.gif[1]
http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/img_text/top_a ctivity_new.htm[2] I believe it gathers the data from v$active_session_history using 5 minutes as time deltas? Thanks a lot -- LSC -- http://www.freelists.org/webpage/oracle-l[3]

• Links --- 1 http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/img/top_activity_new.gif 2 http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/img_text/top_activity_new.htm 3 http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 05 2011 - 16:58:05 CDT