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
(http://iiotzov.wordpress.com/?attachment_id=48).
Iordan Iotzov
http://iiotzov.wordpress.com/
-----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,
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@xxxxxxxxx<mailto:exriscer@xxxxxxxxx>>
Reply-To: "exriscer@xxxxxxxxx<mailto:exriscer@xxxxxxxxx>"
<exriscer@xxxxxxxxx<mailto:exriscer@xxxxxxxxx>>
Date: Tue, 11 Oct 2011 08:00:55 -0400
To: "jan.hendrik.boll@xxxxxxxxxxxxxx<mailto:jan.hendrik.boll@xxxxxxxxxxxxxx>"
<jan.hendrik.boll@xxxxxxxxxxxxxx<mailto:jan.hendrik.boll@xxxxxxxxxxxxxx>>
Cc: "oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>"
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxxxxxxxxx<mailto:jan.hendrik.boll@xxxxxxxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
