Previous | Table of Contents | Next

Page 541

       65 16959     INACTIVE C:\ARMS\CS Windows PC JOHNT           .32
       35 17077     INACTIVE C:\ARMS\CS Windows PC ARMSLIVE        .30
       34 18995     INACTIVE C:\ARMS\CS Windows PC SUE             .23
       23 578       INACTIVE C:\ARMS\CS Windows PC DAVIDE          .20
       33 16965     INACTIVE C:\ARMS\CS Windows PC MARKF           .20
       71 16973     INACTIVE C:\ARMS\CS Windows PC GARYW           .19
       92 16989     INACTIVE C:\ARMS\CS Windows PC JUSTINP         .17
       82 8004      INACTIVE C:\ARMS\CS Windows PC MIKEG           .17
       96 17090     INACTIVE C:\ARMS\CS Windows PC CHANDRA         .16
       20 5466      INACTIVE C:\ARMS\CS Windows PC JAIM            .16
       29 17033     INACTIVE C:\ARMS\CS Windows PC AJAYD           .14
       43 16953     INACTIVE C:\ARMS\CS Windows PC ANDYJ           .13
       77 16947     INACTIVE C:\ARMS\CS Windows PC MANOJS          .12
       54 8971      INACTIVE C:\ARMS\CS Windows PC SMATHEW         .11

From Listing 22.2 you can conclude that sessions 45 and 95 are the top CPU users on the system, consuming 95 and 22 minutes, respectively. The only problem with this output is that it reflects cumulative statistics since start of the session rather than statistics in an interval.

To elaborate, if session A has used 50 minutes of CPU time over 8 hours and session B has used 30 minutes of CPU time over 1 hour, this output will report that session A is a higher CPU user than session B—which is not actually true, because the rate of CPU usage by session B far exceeds the rate of CPU usage of session A. This output does give a fairly accurate overview of the top CPU users on the system, however.

To be more accurate, you have to capture statistics over an interval. Select a time period on the system that is the busiest period of the day. Then capture the CPU usage of the session and store it in a temporary table. After a time period equal to the busy interval has elapsed again, capture the CPU usage of every session using V$SESSTAT and store in a temporary table. The information captured in the two snapshots stored in the temp table can be used to find the rate of CPU usage and the total CPU usage by individual session during the interval. This can be accomplished by the script in Listing 22.3.

Listing 22.3 Create the Temporary Tables to Store Snapshot Information
REM Table to store the start snapshot values
Create table stat$cpu_begin
(sid number,
spid varchar2(9),
schemaname varchar2(30),
cpu_usage number(10),
program varchar2(48),
time_stamp date);

REM Table to store the end snapshot values
Create table stat$cpu_end
(sid number,
spid varchar2(9),
schemaname varchar2(30),
cpu_usage number(10),
program varchar2(48),
time_stamp date);

Page 542

After the tables are created, the first snapshot can be taken using the script in Listing 22.4.

Listing 22.4 Script to Capture the Initial cpu Statistics in stat$cpu_begin Table
Insert Into stat$cpu_begin
Select a.sid,
     b.spid,
     a.schemaname,
     value,
     a.program,
     sysdate
From   v$session a,
     v$process b,
     v$sesstat c
Where  c.statistic# = 12
And    c.sid        = a.sid
And    a.paddr      = b.addr;

Similarly, after the busy period is over, you could again run script shown in Listing 22.5 and populate the table stat$cpu_end.

Listing 22.5 Script to Capture the End cpu Statistics in stat$cpu_end Table
Insert Into stat$cpu_end
Select a.sid,
     b.spid,
     a.schemaname,
     value,
     a.program,
     sysdate
From   v$session a,
     v$process b,
     v$sesstat c
Where  c.statistic# = 12
And    c.sid        = a.sid
And    a.paddr      = b.addr;

You are now ready to produce the heavy CPU user report with rate of CPU usage. This report is more accurate because it will report on the rate of CPU usage, which is a more important factor in determining a heavy resource user. This report shows the CPU consumption rate of each session at a minutes-per-hour level. This snapshotting technique will iron out the discrepancies in Listing 22.5. In Listing 22.6 is the script required to generate the cpu usage report.

Listing 22.6 Script to Produce the cpu Usage Report Using Snapshotting Technique
Break on sid on spid on tot_usg on mins_per_hr
Select e.sid,

Page 543

  e.spid,
     e.cpu_usage,
        b.cpu_usage,
           to_char((e.cpu_usage - b.cpu_usage)/(6000),'9999.99')
tot_usg_mins,
           to_char( ((e.cpu_usage - b.cpu_usage)/(6000))/( (e.time_stamp - b.ti
                        Âme_stamp)*24),'99.99')  mins_per_hr
           From   stat$cpu_end e, stat$cpu_begin b
           Where  b.sid =  e.sid
           And    b.spid = e.spid
           order by mins_per_hr;
NOTE
This SQL works on the basis of statistics captured using the V$SESSTAT view for the statistic whose value is 12, that is CPU used by this session. There could be a problem sometimes using this (that is, this value is incremented by Oracle on exit of the user call just before returning to the client process). Therefore, if you are monitoring a batch job whose user call has not yet returned, the CPU usage value may not be updated in the V$SESSTAT and you may get inaccurate results. Be aware of this when you are using the previous SQLs. Accurate results can be achieved if the monitoring interval is large, assuming all sessions will have the V$SESSTAT updated at some point during the interval.n

Now that you have identified which session is causing the CPU resource to be used heavily, you can determine what the session is executing for the CPU to be used so heavily.

The script shown in Listing 22.7 can be used to find the SQL statement the session is currently executing. This gives a fair indication of which program the session is executing, what is the current section of code, and whether any query optimization needs to be performed on the SQL.

Listing 22.7 Script to Find the Current SQL Statement the Session Is Executing
set head off
set long 9000
set linesize 100
set pagesize 30
set feedback off
set wrap on
set verify off

select sql_text
from v$sqltext , v$session
where sid = 45
and v$sqltext.address = v$session.sql_address
order by piece

SQL_TEXT
--------------------------------------
Select * from employee where emp_id =800
note

Previous | Table of Contents | Next