--   FILE:    session_time.sql
--
--   AUTHOR:  Andy Rivenes, arivenes@appsdba.com
--            Copyright (c) 2005, AppsDBA Consulting. All Rights Reserved.
--
--   DATE:    08/22/2005
--
--   DESCRIPTION:
--            This script will display key fields in v$sess_time_model.
--
--
--   REQUIREMENTS:
--            Requires Oracle 10g and SELECT access to the v$sess_time_model view.
--
--
--   MODIFICATIONS:
--
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
SET PAGESIZE 9999;
SET VERIFY off;
SET FEEDBACK off;
SET LINESIZE 132;
--
COLUMN dbuser      HEADING "User"                         FORMAT A40;
COLUMN dbtim       HEADING "DB Elapsed|Time(sec)"         FORMAT 999,990.99;
COLUMN dbcpu       HEADING "DB CPU|Time(sec)"             FORMAT 999,990.99;
COLUMN jvtim       HEADING "Java|Time(sec)"               FORMAT 999,990.99;
COLUMN pltim       HEADING "PL/SQL|Time(sec)"             FORMAT 999,990.99;
COLUMN sqtim       HEADING "SQL|Time(sec)"                FORMAT 999,990.99;
COLUMN bktim       HEADING "Background|Time(sec)"         FORMAT 999,990.99;
--
PROMPT > Session Time Model Statistics ;
--
SELECT DECODE(se.type,'BACKGROUND',se.program,se.username) dbuser,
       st.sid "SID",
       MAX(DECODE(stat_name,'DB time',st.value,null))/1000000 dbtim,
       MAX(DECODE(stat_name,'DB CPU',st.value,null))/1000000 dbcpu,
       MAX(DECODE(stat_name,'Java execution elapsed time',st.value,null))/1000000 jvtim,
       MAX(DECODE(stat_name,'PL/SQL execution elapsed time',st.value,null))/1000000 pltim,
       MAX(DECODE(stat_name,'sql execute elapsed time',st.value,null))/1000000 sqtim,
       MAX(DECODE(stat_name,'background elapsed time',st.value,null))/1000000 bktim
  FROM (
    SELECT sid,
           stat_name,
           value, 
           row_number() 
           OVER (PARTITION BY sid
                 ORDER BY stat_id) rn
      FROM v$sess_time_model
     WHERE stat_name IN
       ('DB time',
        'DB CPU',
        'Java execution elapsed time',
        'PL/SQL execution elapsed time',
        'sql execute elapsed time',
        'background elapsed time')
    ) st,
    v$session se
 WHERE se.sid = st.sid
 GROUP BY DECODE(se.type,'BACKGROUND',se.program,se.username),
       st.sid
 ORDER BY 4 DESC 
/