Hi That query seems do what I was looking for, thanks a lot! -- LSC On Sat, Nov 5, 2011 at 10:58 PM, Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx>wrote: > ** > 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_activity_new.gifhttp://download.oracle.com/docs/cd/E11882_01/server.112/e10897/img_text/top_activity_new.htm > > > 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 > > > > > -- //www.freelists.org/webpage/oracle-l