Re: Q: v$sysstat and v$sesstat

In article <Pine.HPP.3.91.950210221821.17579A-100000_at_pogo.den.mmc.com>

           binder_at_den.mmc.com "Ernie Binder" writes:

:
: 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% )
:
: Table was 125,000 rows in length, 18 columns, 117 bytes average.
 

: Data was packaged into arrays of 100 elements and sent to the
: client over SQL*NET.
 

: 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?
:

My understanding is that the times in SESSTAT and SYSSTAT are the times spend in calls to Oracle kernel code. Since the time spent on each call is measured only to the nearest hundredth of a second the results should be treated with caution.

In your example, 125,000 rows in batches of 100 would presumably result in 1,250 EXECUTE calls to the kernel: as calls with a reasonble amount of work to do, one could expect an average error in timing on 625 hundredths of a second across the total number of calls: i.e. 6.25 seconds; which is actually more than the amount of time that you are trying to account for.

You could try the same experiment with a few variations in number of rows and sizes of batches (in particular lots more rows, and double or treble the size of batches) and correlate the results to see if the lost time is actually just lots of rounding errors.

-- 
Jonathan Lewis