------------------------------------------------------------
-- file		timechk.sql
-- desc		Check to see if Oracle records time correctly.
-- author	Craig A. Shallahamer, craig@orapub.com
-- orig		09-oct-01
-- lst upt	13-jul-03 
-- copyright	(c)2001,2002,2003 OraPub, Inc.
-- usage 	start timechk ‹interval›
------------------------------------------------------------

start osmclear

def interval=&&1


prompt =====================================================
prompt INTRODUCTION
prompt =====================================================
prompt
prompt Below is a series of three timing tests.  In some versions of
prompt Oracle, bogus CPU consumption numbers are reported.  These
prompt invalid, incorrect, and extremely lame numbers can cause 
prompt problems with very fine tools, like the OSM tool kit's
prompt response time facilities.
prompt
prompt Because sometimes Oracle reports CORRECTLY and other times
prompt reports INCORRECTLY, run this timing check multiple times.
prompt
prompt For this script to work properly, timed_os_statistics must
prompt be set to, 1 ‹= timed_os_statistics ‹= 5, the lower the better.
prompt
@ip timed_os_statistics

prompt =====================================================
prompt TEST # 1 - SESSION LEVEL (V$SESSTAT) CPU Correctness
prompt =====================================================
prompt
prompt Testing for v$sesstat CPU correctness.
prompt This test will last &interval seconds.
prompt This test causes this session to sleep.

set feedback off verify off
set termout off

-- The users sid is captured to test "session" related views.

col val1 new_val sid noprint 
select ltrim(s.sid) val1
from   v$process p,
       v$session s
where  p.addr   = s.paddr
  and  s.audsid = userenv('SESSIONID')
/

col val1 new_val sesstat0
select value val1 
from   v$sesstat 
where  statistic# = (
	select statistic#
	from   v$statname
	where  name = 'OS User time used'
       )
  and  sid = &sid
/

set termout on
prompt Starting the test now.
prompt
set termout off

exec sys.dbms_lock.sleep(&interval);

col val1 new_val sesstat1
select value val1 
from   v$sesstat 
where  statistic# = (
	select statistic#
	from   v$statname
	where  name = 'OS User time used'
       )
  and  sid = &sid
/

col val1 new_val time_used
select ltrim((&sesstat1-&sesstat0)/100) val1
from   dual;

set heading off
set termout on
prompt During the past &interval seconds, there should have
prompt been 0 CPU time consumed by this session.  However, this test
prompt may consume up to 0.02 seconds of CPU time.  This session
prompt consumed &time_used CPU seconds.  Therefore, it appears

select 'Oracle is INCORRECTLY reporting CPU time at the session level.'
from   dual
where  &time_used › 0.02;

select 'Oracle is CORRECTLY reporting CPU time at the session level.'
from   dual
where  &time_used ‹= 0.02;

prompt
prompt =====================================================
prompt TEST # 2 - SESSION LEVEL (V$SESSTAT) CPU Correctness
prompt =====================================================
prompt
prompt Testing for v$sesstat CPU correctness.
prompt This test uses a running process to validate the CPU time.

set termout off

col val1 new_val sesstat0
select value val1 
from   v$sesstat 
where  statistic# = (
	select statistic#
	from   v$statname
	where  name = 'OS User time used'
       )
  and  sid = &sid
/

set termout on
prompt Starting the test now.
prompt
set termout off

col val1 new_val time0
select dbms_utility.get_time/100 val1
from   dual;

set termout off
select * 
from v$sqlarea
where buffer_gets › 0;

col val1 new_val time1
select dbms_utility.get_time/100 val1
from   dual;

col val1 new_val sesstat1
select value val1 
from   v$sesstat 
where  statistic# = (
	select statistic#
	from   v$statname
	where  name = 'OS User time used'
       )
  and  sid = &sid
/

col val1 new_val time_used
select ltrim((&sesstat1-&sesstat0)/100) val1
from   dual;

col val1 new_val wall_time
select ltrim(&time1-&time0) val1
from   dual;

set heading off
set termout on
prompt During the past &wall_time seconds, your session could
prompt have possibly used up to &wall_time seconds of CPU time,
prompt assumming a single server process was hanlding your request.
prompt Oracle reports, via v$sesstat, that your session consumed
prompt &time_used seconds.  Therefore, it appears

select 'Oracle is INCORRECTLY reporting CPU time at the session level.'
from   dual
where  &wall_time ‹ &time_used;

select 'Oracle is CORRECTLY reporting CPU time at the session level.'
from   dual
where  &wall_time ›= &time_used;

set feedback off verify off
set termout on
prompt
prompt =====================================================
prompt TEST # 3 - SYSTEM LEVEL (V$SYSSTAT) CPU Correctness
prompt =====================================================
prompt
prompt Testing for v$sysstat CPU correctness.
prompt This test will last &interval seconds.

set termout off

col val1 new_val cpu_count
select value val1 
from   v$parameter
where  name = 'cpu_count';

col val1 new_val sysstat0
select value val1 
from   v$sysstat 
where  statistic# = (
	select statistic#
	from   v$statname
	where  name = 'OS User time used'
       )
/

set termout on
prompt Starting the test now.
prompt
set termout off

set verify off

exec sys.dbms_lock.sleep(&interval);

col val1 new_val num_sessions1
select count(*) val1 from v$session;

col val1 new_val sysstat1
select value val1 
from   v$sysstat 
where  statistic# = (
	select statistic#
	from   v$statname
	where  name = 'OS User time used'
       )
/

col val1 new_val time_used
select ltrim((&sysstat1-&sysstat0)/100) val1
from   dual;

col val1 new_val max_possible
select ltrim(&cpu_count*&interval) val1
from   dual;

set termout on

prompt The database server has &cpu_count CPUs.  Since this
prompt test lasted &interval seconds, during this time there
prompt was &max_possible seconds of CPU time available.
prompt Oracle definately must have consumed some CPU.
prompt
prompt V$SYSSTAT reports that during this interval, Oracle
prompt consumed &time_used seconds of CPU.  Therefore, it appears

set heading off

select 'Oracle is INCORRECTLY reporting CPU time at the system level.'
from   dual
where  ( &max_possible ‹ &time_used
         or
	 &time_used = 0
       );

select 'Oracle may be CORRECTLY reporting CPU time at the system level.'
from   dual
where  ( &max_possible ›= &time_used
	 and
	 &time_used › 0 
       );

prompt

set heading on
start osmclear