Re: Top Activity in Percentage

From: LS Cheng <exriscer_at_gmail.com>
Date: Sun, 6 Nov 2011 14:30:16 +0100
Message-ID: <CAJ2-Qb-pbDWnSKpWU9nycqmqLKQbaFMBqJurLG2GnRedLV15qw_at_mail.gmail.com>

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_at_millerandbowman.com>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

>

>

> --http://www.freelists.org/webpage/oracle-l

>

>

>

>

>



--
http://www.freelists.org/webpage/oracle-l

Received on Sun Nov 06 2011 - 07:30:16 CST