Re: Finding out historical cpu-usage
- From: Jan-Hendrik Boll <jan.hendrik.boll@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Oct 2011 07:43:21 +0200
Thanks John, that was excactly what i needed.
Regards
2011/10/11 John Clarke <john.clarke@xxxxxxxxxxxx>
> 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>
> Reply-To: "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>
> Date: Tue, 11 Oct 2011 08:00:55 -0400
> To: "jan.hendrik.boll@xxxxxxxxxxxxxx" <jan.hendrik.boll@xxxxxxxxxxxxxx>
> Cc: "oracle-l@xxxxxxxxxxxxx" <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> 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:
