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 Bwhich 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 InformationREM 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 TableInsert 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 TableInsert 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 TechniqueBreak 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 Executingset 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