RE: Finding out historical cpu-usage

From: Iotzov, Iordan <IIotzov_at_newsamerica.com>
Date: Tue, 11 Oct 2011 09:54:23 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB6701D15FCAF7_at_nam-wil-exc-l03.newsamerica.com>

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 (http://iiotzov.wordpress.com/?attachment_id=48).

Iordan Iotzov
http://iiotzov.wordpress.com/

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Clarke Sent: Tuesday, October 11, 2011 9:14 AM
To: exriscer_at_gmail.com; jan.hendrik.boll_at_googlemail.com Cc: oracle-l_at_freelists.org
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, whatever.

SELECT mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait Time",

         SUM (cpu_time) + SUM (wait_time) "Total Time"     FROM (SELECT a.module mymodule,

                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) 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 ORDER BY 2 DESC From: LS Cheng <exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>> Reply-To: "exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>" <exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>> Date: Tue, 11 Oct 2011 08:00:55 -0400
To: "jan.hendrik.boll_at_googlemail.com<mailto:jan.hendrik.boll_at_googlemail.com>" <jan.hendrik.boll_at_googlemail.com<mailto:jan.hendrik.boll_at_googlemail.com>> Cc: "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: Finding out historical cpu-usage

Hi
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

Thanks

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

Hi Lister's,
i am currently investigating which session consume the most cpu-time per instance.
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 appreciated.

Kind regards,
Jan-Hendrik Boll

--

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

--

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

--

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

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

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 11 2011 - 08:54:23 CDT