-------------------------------------------------------------------------------
--
-- Script:	create_apt_tables
-- Purpose:	to the apt_extents table
-- For:		9i and 10g
--
-- Copyright:	(c) Ixora Pty Ltd for apt_extents tables
--              (c) Dsvolk for other
--
--
-------------------------------------------------------------------------------

drop table
  sys.apt_extents
/
create table
  sys.apt_extents
  (
    file_id      number,
    block_id     number,
    primary key (
      file_id,
      block_id),
    owner        varchar2(30),
    segment_name varchar2(81),
    extent_id    number,
    blocks       number
  )
organization index
compress 1
/
insert into sys.apt_extents
select
  file_id,
  block_id,
  owner,
  segment_name,
  extent_id,
  blocks
from
  sys.dba_extents
/

analyze table sys.apt_extents compute statistics;

drop table apt_objects
/

create table 
  sys.apt_objects
  (
    object_id      number, 
    primary key (
      object_id ),
    owner        varchar2(30),
    object_name varchar2(81)
  )
organization index
/

insert into sys.apt_objects 
select   
  data_object_id,
  owner, 
  object_name 
  from dba_objects where  (data_object_id, object_id) in 
 (
  select min(object_id),  data_object_id from dba_objects
     where  object_type in ('INDEX', 'TABLE') and data_object_id is not null
     group by data_object_id 
 )
/


analyze table sys.apt_objects compute statistics;

drop table  sys.apt_idle_events
/
create table  sys.apt_idle_events 
( 
  event_name varchar2(64)
)
/
insert into sys.apt_idle_events values ('rdbms ipc message');
insert into sys.apt_idle_events values ('SQL*Net message from client');
insert into sys.apt_idle_events values ( 'pipe get' );
insert into sys.apt_idle_events values ('pmon timer');
insert into sys.apt_idle_events values ('smon timer'); 
insert into sys.apt_idle_events values ('PL/SQL lock timer');
insert into sys.apt_idle_events values ( 'jobq slave wait'); 
insert into sys.apt_idle_events values ('Queue Monitor Wait'); 
insert into sys.apt_idle_events values ('wakeup time manager');
insert into sys.apt_idle_events values ('dispatcher timer');
insert into sys.apt_idle_events values ('virtual circuit status');
insert into sys.apt_idle_events values ('Queue Monitor Slave Wait');
insert into sys.apt_idle_events values ('Queue Monitor Task Wait');
insert into sys.apt_idle_events values ('rdbms ipc reply');
insert into sys.apt_idle_events values ('class slave wait');
insert into sys.apt_idle_events values ('SQL*Net more data from client');
commit;


-- Get event class for event
--  
--
create or replace function apt_sesstat_value (p_name varchar2, p_sid number default null )
return number 
is
 v_result number;
begin

 if (p_sid is NULL ) then 
	select  sum(vs.value) into v_result  
         from v$sesstat vs, v$statname st
	 where vs.statistic# = st.statistic# 
	 and st.name = p_name;
 else 
	select  sum(vs.value) into v_result  
         from v$sesstat vs, v$statname st
	 where vs.statistic# = st.statistic# 
	 and st.name = p_name and vs.sid = p_sid;
 end if;
 
 
 return v_result;
--

 exception when no_data_found then 
   return NULL;     	 
 
end;
/

create or replace function apt_event_class (p_event varchar2 )
return varchar2
is
 v_result varchar2(64);
 begin 
 select decode (p_event, 

 	'db file sequential read', 	'1 Normal I/O',

	'db file scattered read', 	'2 Full scans',

 	'direct path write temp', 	'3 Direct W I/O',
 	'direct path read temp', 	'3 Direct R I/O', 
 	'direct path write', 		'3 Direct W I/O',
 	'direct path read', 		'3 Direct R /O', 

	'control file single write',	'4 Background W I/O',
        'control file parallel write',  '4 Background W I/O',
        'control file sequential read', '4 Background R I/O',
        'log file sequential read',     '4 Background R I/O',
        'log file parallel write',      '4 Background W I/O',
        'log file single write',        '4 Background W I/O',
        'db file parallel write',       '4 Background W I/O',

	'latch free', 			'5 Locks',
	'enqueue', 			'5 Locks', 

	'buffer busy waits', 		'6 Concurrency', 

	'log file sync', 		'7 Commit', 

	'SQL*Net message to client', 	 '8 Network',
	'SQL*Net more data to client', 	 '8 Network',
	'SQL*Net break/reset to client', '8 Network',
        	                         '9 Other'
) into v_result from dual; 

 return v_result;

end;
/