Help interpreting Oracle CPU usage results on NT 4.0...

From: Denmark Weatherburne <denmark_weatherburne_at_hotmail.com>
Date: Thu, 23 Aug 2001 16:24:02 -0700
Message-ID: <F001.0037657C.20010823163551@fatcity.com>

Hi DBA's,

We are running Oracle 805 on NT in ARCHIVELOG MODE. I run the following script to list CPU usage by thread:

column program format a50
column username format a12

spool C:\WINNT\Profiles\aweatherburnedb\Desktop\DBA\NT_CPU_usage.out select p.spid thread, s.username,
decode(nvl(p.background,0),1,bg.description,

s.program) program, ss.value/100 CPU,physical_reads disk_io from v$process p,

        v$session s,
        v$sesstat ss,
        v$sess_io si,
        v$bgprocess bg
where s.paddr=p.addr
        and ss.sid=s.sid
        and ss.statistic#=12
        and si.sid=s.sid
        and bg.paddr(+)=p.addr
        order by ss.value desc;

spool off

This is the report:

THREAD USERNAME PROGRAM CPU DISK_IO

--------- ------------ -------------------------------------------------- 
--------- ---------
000C8     CASTILOL     D:\orant\BIN\RWRBE60.exe                              
112.42    132277
001AC     CASTILOW     ORACLE80.EXE (P000)                                   
  67.23         0
001C0     PEREZM       D:\orant\BIN\RWRBE60.exe                              
  60.11    120613
001C2     CASTILOW     ORACLE80.EXE (P001)                                   
  55.95         0
001DF     CASTILOW     ORACLE80.EXE (P002)                                   
   33.4         5
00175     CASTILOW     ORACLE80.EXE (P003)                                   
   30.1         5
001E2     MKENZIEL     C:\WINNT\Profiles\All Users\Desktop\sigtas.lnk        
  27.77    257276
000DC     CASTILOW     C:\WINNT\Profiles\castillowm\Desktop\Sigtas Belize    
  12.69     40611

Sorry about the formatting.

Nevertheless, what I'm trying to get a handle on is the CPU column. Notice the first row, thread 000C8 is using 112.42 ? of CPU. Looking at the SQL statement v$sesstat provides VALUE which are numbers from 0 to 438532196. This value is divided by 100 to get the CPU value. I still don't know what the number in "v$sesstat VALUE" represents? Also from which table is the value for Disk_IO (Physical Reads) retrieved?

Thanks in advance for your time.

Denmark Weatherburne

"Knowledge is power, but it is only useful if it is shared!"

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Denmark Weatherburne
INET: denmark_weatherburne_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Aug 23 2001 - 18:24:02 CDT