Re: Finding out historical cpu-usage

From: John Clarke <john.clarke_at_centroid.com>
Date: Tue, 11 Oct 2011 09:13:32 -0400
Message-ID: <CAB9B7C0.5542B%john.clarke_at_centroid.com>

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 Received on Tue Oct 11 2011 - 08:13:32 CDT