Hi,
I'm trying to build a table similar to the view v$active_session_history (part of diagnostic pack) to monitor a database.
To achieve this, I created a job which insert into a table every second, all rows from v$session by using this query :
select sysdate sample_time,
s.sid,
s.serial#,
s.username,
s.command,
s.status,
s.schemaname,
s.osuser,
s.process,
s.machine,
s.terminal,
s.program,
s.type,
s.sql_id,
s.sql_hash_value,
s.module,
s.logon_time,
s.event,
s.event#,
s.wait_class,
s.state,
s.service_name,
s.inst_id,
sql.SQL_TEXT,
sql.EXECUTIONS,
sql.PARSE_CALLS,
sql.DISK_READS,
sql.DIRECT_WRITES,
sql.BUFFER_GETS,
sql.ROWS_PROCESSED,
sql.COMMAND_TYPE,
sql.SORTS,
sql.cpu_time,
sql.module sql_module,
sql.ELAPSED_TIME
from gv$session s,
gv$sqlarea sql
where s.sql_id = sql.sql_id
and s.wait_class!='Idle'
and s.status='ACTIVE'
It gives me a sample each second of all the active sessions in the database (So normally the same result than v$active_session_history)
This works pretty good but I have a problem with DB Time. Normally I should be able to get DB Time by counting all rows from my table but unfortunately it doesn't work.
Here is my test :
1 - I take a snapshot of DB Time in V$sys_time_model :
select sysdate from dual;
select value/1000000 from v$sys_time_model where upper(STAT_NAME)='DB TIME';
SYSDATE
-------------------
20/12/2013 09:30:43
VALUE/1000000
-------------
373536.692
and another one a little later :
select sysdate from dual;
select value/1000000 from v$sys_time_model where upper(STAT_NAME)='DB TIME';
SYSDATE
-------------------
20/12/2013 09:36:27
VALUE/1000000
-------------
373542.726
if I make the subtraction between the two snapshots I get this result :
select 373542.726 - 373536.692 from dual;
373542.726-373536.692
---------------------
6.034
So 6 seconds of DB_TIME.
Now if I count the lines in my table for the same period :
select count(*) from monitor.ash_data where sample_time between to_date('20/12/2013 09:30:43','dd/mm/yyyy hh24:mi:ss') and to_date('20/12/2013 09:36:27','dd/mm/yyyy hh24:mi:ss')
COUNT(*)
----------
346
Very different. What I'm doing wrong ? any idea?
Thanks by advance.