Re: Finding out historical cpu-usage

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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>


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


Other related posts: