SEO

I setup the SQL script that follows to show what I think is a bug in
Oracle. But it is possible that I also don't understand what these
statistics are recording. My understanding is that the OS statistics
that record time should total to the actual time spent by the user
process. Some of the statistics, such as the CPU ones, seem to work
fine. But 'OS User lock wait sleep time', and 'OS All other sleep
time' are way out of whack. This is on Solaris 8 with Oracle 9.2.0.5.
I expect the final value of "session_time_after" to be close to the
100 seconds that sqlplus waits on the sleep command, but it is way off
on my system.

Thanks for any comments/help.

- Bobby

set termout on
set echo on
spool osstatsproblem.log
alter session set timed_os_statistics=1;
drop table before;
create table before as
select sum(value)/100 session_time_before
from v$sesstat where sid=(SELECT sid from v$session where
audsid=USERENV('SESSIONID'))
and statistic# in
(select statistic# from v$statname where name in
('OS User level CPU time',
'OS System call CPU time',
'OS Other system trap CPU time',
'OS Text page fault sleep time',
'OS Data page fault sleep time',
'OS Kernel page fault sleep time',
'OS User lock wait sleep time',
'OS All other sleep time',
'OS Wait-cpu (latency) time'));
host sleep 100
select session_time_after-session_time_before elapsed_seconds
from
before,
(select sum(value)/100 session_time_after
from v$sesstat where sid=(SELECT sid from v$session where
audsid=USERENV('SESSIONID'))
and statistic# in
(select statistic# from v$statname where name in
('OS User level CPU time',
'OS System call CPU time',
'OS Other system trap CPU time',
'OS Text page fault sleep time',
'OS Data page fault sleep time',
'OS Kernel page fault sleep time',
'OS User lock wait sleep time',
'OS All other sleep time',
'OS Wait-cpu (latency) time')));
drop table before;
spool off