Q: v$sysstat and v$sesstat

From: Ernie Binder <binder_at_den.mmc.com>
Date: Sat, 11 Feb 1995 05:20:51 GMT
Message-ID: <Pine.HPP.3.91.950210221821.17579A-100000_at_pogo.den.mmc.com>

I have a two part question relating to the accountability of the wall time as measured by Oracle v$sysstat. We have measured the CPU session time (v$sesstat), CPU time for the C program (clock), and the read time for the physical I/Os (v$filestat). The physical I/Os read have also been broken down for accountabilty of the indexes, and data time.                                               

    Measured Data:

        Session CPU time =        7.8150 sec. (v$sesstat)
        C program CPU time =      1.8800 sec. (clock)
        Phy. I/O read time =      0.4250 sec. (v$filestat)
                                --------------
        Total accountable time = 10.1200 sec

        Total wall time =        14.0000 sec. (v$sysstat)
        Unaccountable time =      3.8800 sec. ( 28% )

    System Configuration:
        Table was 125,000 rows in length, 18 columns, 117 bytes average.
        Selected 8 columns (41 bytes) for 2,782,178 bytes of data.
        Data was packaged into arrays of 100 elements and sent to the
           client over SQL*NET. 
        Data server was RS/6000 model 990 with 100 MB SGA.
        Client server was RS/6000 model 990.
        Ran uncontended, as determined with "ps -aux".
        SQL statement-
           EXEC SQL SELECT v$sysstat.value, v$sesstat.value 
             INTO :sys_cpu,:sess_cpu
             FROM v$sysstat,v$sesstat 
             WHERE v$sysstat.statistic#=v$sesstat.statistic# 
             AND v$sysstat.statistic#=12
             AND v$sesstat.sid = :sid.

1.) Does v$sesstat return CPU time associated with SQL*NET using the underlying TCP/IP on the FDDI network? If not, can it be captured in another statistic?

2.) Does any one have any idea what is causing the 28% unaccounted for time? The transfer of 2.782,178 bytes across the network should take roughly 0.5 seconds. Is the rest of the time associated with the operating system, background Oracle, or what? How do I measure it?

+--------------------------------+--------------------------------------+


| Ernie Binder                   |  Internet: binder_at_pogo.den.mmc.com   |

| Martin Marietta Corp., MDSO    |  Phone: 610-531-6565 (& voice mail)  |

| Bldg. E, Room 33E44            |                                      |


| PO Box 8048, Phila. Pa. 19101  |                                      |

+----- Opinions expressed here are my own and not my employer's. -----+ Received on Sat Feb 11 1995 - 06:20:51 CET