Re: Top Activity in Percentage
- From: Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Sun, 06 Nov 2011 07:58:05 +1000
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
-- //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 //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
