RE: Finding out historical cpu-usage

  • From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
  • To: "john.clarke@xxxxxxxxxxxx" <john.clarke@xxxxxxxxxxxx>, "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>, "jan.hendrik.boll@xxxxxxxxxxxxxx" <jan.hendrik.boll@xxxxxxxxxxxxxx>
  • Date: Tue, 11 Oct 2011 09:54:23 -0400

If you use Oracle Enterprise Manager (OEM), you can find numerous OEM metrics 
related to CPU utilization. They are under "CPU Usage" and "Load" metrics 
groups for the host.

You can have access to that data with simple SQL as well - all you need is to 
look at the OEM repository structures 

Iordan Iotzov

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of John Clarke
Sent: Tuesday, October 11, 2011 9:14 AM
To: exriscer@xxxxxxxxx; jan.hendrik.boll@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Finding out historical cpu-usage

Queries below against dba_hist_sysmetric_summary and dba_hist_sysstat will show 
host CPU utilization metrics ...
If you want per session, or maybe per-module statistics (which is often helpful 
if your application instruments things via dbms_application_info.set_module), 
something like the below would work, which summarizes over the last 10 days.

You can obviously add, select, and group any column you'd like from 
dba_hist_active_sess_history, or add the snap time to group by snapshot, day, 

SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait 
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-10
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0

From: LS Cheng <exriscer@xxxxxxxxx<mailto:exriscer@xxxxxxxxx>>
Reply-To: "exriscer@xxxxxxxxx<mailto:exriscer@xxxxxxxxx>" 
Date: Tue, 11 Oct 2011 08:00:55 -0400
To: "jan.hendrik.boll@xxxxxxxxxxxxxx<mailto:jan.hendrik.boll@xxxxxxxxxxxxxx>" 
Cc: "oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>" 
Subject: Re: Finding out historical cpu-usage

I use dba_hist_sysmetric_summary or dba_hist_sysstat

in case of sysmetric you can look for several cpu metrics such as Host
*CPU*Utilization, cpu usage per sec, db time cpu ratio

sysstat look for CPU system statistics


On Tue, Oct 11, 2011 at 11:32 AM, Jan-Hendrik Boll <
jan.hendrik.boll@xxxxxxxxxxxxxx<mailto:jan.hendrik.boll@xxxxxxxxxxxxxx>> wrote:

Hi Lister's,
i am currently investigating which session consume the most cpu-time per
In order to do so i am using the view dba_hist_active_sess_history

Tracking down the time spent waiting for typical wait-events like 'log file
sync' seems easy as it is the sum of time_wait for that event.

But how can i find out the CPU-Time?
Is CPU time the sum of wait_time in which the session_state equals 'ON CPU'

Any clarification about the meaning of these two columns would be greatly

Kind regards,
Jan-Hendrik Boll




This message and its attachments may contain legally privileged or confidential 
information. It is intended solely for the named addressee. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to the addressee), you may not copy or deliver this message or its attachments 
to anyone. Rather, you should permanently delete this message and its 
attachments and kindly notify the sender by reply e-mail. Any content of this 
message and its attachments that does not relate to the official business of 
News America Incorporated or its subsidiaries must be taken not to have been 
sent or endorsed by any of them. No warranty is made that the e-mail or 
attachment(s) are free from computer virus or other defect.

Other related posts: