grid control/dbconsole queries

From: LS Cheng <exriscer_at_gmail.com>
Date: Mon, 13 Aug 2007 22:56:00 +0200
Message-ID: <6e9345580708131356x4187e553oe11a3a5f044c34f0@mail.gmail.com>

Hi

I was wondeing if anyone knows how dbcontrol (or grid control) accounts CPU usage in Average Active Session graph shown in Performance tab?

I cacthed a query and it does this

SELECT begin_time, wait_class#, (time_waited) / (intsize_csec / 100)

    FROM v$waitclassmetric
UNION ALL
SELECT begin_time, -1, VALUE

    FROM v$sysmetric
   WHERE metric_name = 'CPU Usage Per Sec' AND GROUP_ID = 2 ORDER BY begin_time, wait_class#

If we look historical data the query is this

SELECT dat.sample_time, dat.NAME, dat.session_id, dat.session_serial#,

         dat.client_id, dat.sql_id, dat.service_hash, dat.module, dat.action ,

         dat.p1, dat.p2, dat.p3, dat.current_obj#, dat.current_file#,
         dat.user_id, dat.program, dat.samples, dat.time_waited / 1000000,
         dat.sql_opcode

    FROM (SELECT 'CPU Used' NAME, sample_time, session_id, session_serial#,

                   client_id, sql_id, service_hash, module, action, p1, p2,
                   p3, current_obj#, current_file#, MAX (user_id) user_id,
                   MAX (program) program, COUNT (*) samples,
                   SUM (time_waited) time_waited, sql_opcode
              FROM dba_hist_active_sess_history
             WHERE dbid = 3289503984
               AND instance_number = 1
               AND snap_id BETWEEN 87 AND 88
               AND wait_time <> 0
               AND 'CPU Used' = 'CPU Used'
          GROUP BY sample_time,
                   session_id,
                   session_serial#,
                   client_id,
                   sql_id,
                   service_hash,
                   module,
                   action,
                   p1,
                   p2,
                   p3,
                   current_obj#,
                   current_file#,
                   sql_opcode) dat

ORDER BY dat.sample_time

Does these queries makes sense?

Thanks

--

LSC
--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 13 2007 - 15:56:00 CDT