Re: Finding out historical cpu-usage
- From: John Clarke <john.clarke@xxxxxxxxxxxx>
- To: "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>, "jan.hendrik.boll@xxxxxxxxxxxxxx" <jan.hendrik.boll@xxxxxxxxxxxxxx>
- Date: Tue, 11 Oct 2011 09:13:32 -0400
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
Other related posts:
