Performace monitoring queries

################session informations########################

 col PROGRAM for a50
 col PREV_SQL_ID for a15
 col USERNAME for a20
col spid for a10
col SQL_ID for a15
set line 1000

SELECT
        s.sid,
        s.serial#,
        p.spid,
        s.username,
        s.program,sql_id,PREV_SQL_ID
 FROM   v$session s
        JOIN v$process p ON p.addr = s.paddr
 WHERe p.spid=&a


7mtg8t0m7rqxj

 /


select sql_text from v$sqltext where sql_id='9d0r1xjgt715z' order by piece ;

 select * from table(dbms_xplan.display_cursor('9d0r1xjgt715z',0));

SELECT * FROM TABLE(dbms_xplan.display_awr('bwwa83w67khfk'));

************************************long session ************************************************

set lines 200
col OPNAME for a40

Select
a.sid,
a.serial#,
b.status,
a.opname
--to_char(a.START_TIME,' dd-Mon-YYYY HH24:mi:ss') START_TIME,
--to_char(a.LAST_UPDATE_TIME,' dd-Mon-YYYY HH24:mi:ss') LAST_UPDATE_TIME,
--a.time_remaining as "Time Remaining Sec" ,
--a.time_remaining/60 as "Time Remaining Min",
--a.time_remaining/60/60 as "Time Remaining HR"
From v$session_longops a, v$session b
where a.sid = b.sid
and a.opname like '%Statistics%' order by status
--And time_remaining > 0;


************************************************************************************

SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
RPAD('USERNAME : ' || s.username, 80) ||
RPAD('OSUSER : ' || s.osuser, 80) ||
RPAD('PROGRAM : ' || s.program, 80) ||
RPAD('SPID : ' || p.spid, 80) ||
RPAD('SID : ' || s.sid, 80) ||
RPAD('SERIAL# : ' || s.serial#, 80) ||
RPAD('MACHINE : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal, 80) ||
RPAD('SQL TEXT : ' || q.sql_text, 1000)
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&PID_FROM_OS'
AND s.sql_address = q.address
AND s.sql_hash_value = q.hash_value;




SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
RPAD('USERNAME : ' || s.username, 80) ||
RPAD('OSUSER : ' || s.osuser, 80) ||
RPAD('PROGRAM : ' || s.program, 80) ||
RPAD('SPID : ' || p.spid, 80) ||
RPAD('SID : ' || s.sid, 80) ||
RPAD('SERIAL# : ' || s.serial#, 80) ||
RPAD('MACHINE : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal, 80)
FROM v$session s
,v$process p
WHERE s.paddr = p.addr
AND s.sid = '&sid'

############################## wait event ########################

set line 1000
 col WAIT_CLASS for a30
 Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
 Sum_Waits
 From v$system_wait_class
 Group by wait_class
Order by 3 desc;


*****************************

col event for a30

Select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
    From  v$session_event a, v$event_name b, v$session_wait_class c
   Where a.event_id=b.event_id
   and a.sid=c.sid
    And b.wait_class#=c.wait_class#
and  b.NAME = a.event
--AND b.wait_class <> 'Idle'
AND
       a.time_waited > 0 and a.wait_class  in ('Concurrency' , 'User I/O' ,'System I/O')
order by average_wait desc;

**************************************







   COLUMN wait_class format a20
COLUMN name       format a30
COLUMN time_secs  format 999,999,999,999.99
COLUMN pct        format 99.99

SELECT  
   wait_class,
   NAME,
   ROUND (time_secs, 2) time_secs,
   ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
   (SELECT
      n.wait_class,
      e.event NAME,
      e.time_waited / 100 time_secs
    FROM
      v$system_event e,
      v$event_name n
    WHERE
       n.NAME = e.event AND n.wait_class <> 'Idle'
    AND
       time_waited > 0
    UNION
    SELECT
      'CPU',
      'server CPU',
      SUM (VALUE / 1000000) time_secs
    FROM
      v$sys_time_model
  WHERE
 stat_name IN ('background cpu time', 'DB CPU')
)
ORDER BY
   time_secs DESC;

***************************



 select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL order by 1 ,5 desc
and a.event like '&a';


*************


 select session_id, session_state, event, sql_id, top_level_sql_id,  plsql_object_id, count(*)
  from V$ACTIVE_SESSION_HISTORY h
   where  sample_time between sysdate - 15/(24*60) and sysdate
   and session_state in ('ON CPU', 'WAITING')
  -- and h.session_id in (10295)
   group by session_id, session_state, event, sql_id, top_level_sql_id, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id
   order by session_i



***************


column "Inst" format 9999
column "SID" format 99999
column "Event" format a25
column "State" format a13
column "Username" format a15
column "SQL Id" format a15
column "Obj#" format 9999999
column "File#" format 999999
column "Row#" format 99999999
Column "Logon Time" format a15
Column "Blk SID" format a7
set pages 500 lines 232 wrap off feedback on
select inst_id "Inst", sid "SID", event "Event", p1, p2, p3, seconds_in_wait "In Wait(s)", wait_time "Wait Time(s)",state "State",
       username "Username", sql_id "SQL Id", row_wait_obj# "Obj#", row_wait_file# "File#", row_wait_block# "Block#", row_wait_row# "Row#",
       to_char(logon_time,'DD-MON HH24:MI:SS') "Logon Time",
       blocking_instance||':'||blocking_session "Blk SID"
from gv$session
where
-- wait_class != 'Idle'
--and (seconds_in_wait > 0 or wait_time > 0)
sid in (
order by 8,7 ;

SQL*Net message from client

************


select    /*+ all_rows */
          count(*)                  as Wait_Event_Count
         ,sum(ah.time_waited)       as Total_Time_Waited
         ,ah.session_id             as SID
         ,ev.name                   as Event_Name
         ,sql_id                    as SQL_ID
         ,ah.session_type           as Session_Type
from    
          v$active_session_history  ah
         ,v$event_name              ev
where     ah.sample_time between sysdate - 180/(24*60) and sysdate
and       ah.event#      = ev.event#(+)
and       ah.session_id  = &s_id
group by
          ev.name
         ,ah.session_id
         ,ah.sql_id
         ,ah.session_type
/


**************************

 Sometimes the SQL_ID in V$SESSION will be NULL even though a long running SQL statement appears to be executing. Reason being that something other than the SQL is in fact executing. It could be PL/SQL code, latching activity in the shared pool (which is common for very long running SQL statements) or a COMMIT is currently underway.

One can query the V$ACTIVE_SESSION_HISTORY dictionary view to identify the current elapsed time per wait event for the session;

select    /*+ all_rows */
          count(*)                  as Wait_Event_Count
         ,sum(ah.time_waited)       as Total_Time_Waited
         ,ah.session_id             as SID
         ,ev.name                   as Event_Name
         ,sql_id                    as SQL_ID
         ,ah.session_type           as Session_Type
from    
          v$active_session_history  ah
         ,v$event_name              ev
where     ah.sample_time between sysdate - 30/(24*60) and sysdate
and       ah.event#      = ev.event#(+)
and       ah.session_id  = &sid
group by
          ev.name
         ,ah.session_id
         ,ah.sql_id
         ,ah.session_type
/

The following statement indicates the sessions that have caused the most load as a percentage of all foreground sessions;

select ah.session_id
      ,ah.session_type
      ,nvl(ah.sql_id,'xx')                     as SQL_ID
      ,count(*)                                as Session_Cnt
      ,round(count(*)/sum(count(*)) over(), 2) as Percent_Load
from
       v$active_session_history ah
where
       ah.sample_time >to_date('31-OCT-11 11:40','dd-MON-yy hh24:mi')
and    ah.session_type='FOREGROUND'
group by
       ah.session_id
      ,ah.session_type
      ,ah.sql_id
order by count(*) desc
/

The following SQL shows information on the PL/SQL units, which might be relevant if PL/SQL is busy executing and the SQL_ID is null;


select    ah.session_id               as SID
         ,ah.session_state            as State
         ,event                       as Event_name
         ,ah.sql_id                   as SQL_ID
         ,ah.top_level_sql_id
         ,ah.plsql_entry_object_id
         ,ah.plsql_entry_subprogram_id
         ,ah.plsql_object_id
         ,ah.plsql_subprogram_id
         ,count(*)                    as Wait_Event_Count
from
          v$active_session_history    ah
where
          ah.sample_time     between sysdate - 60/(24*60) and sysdate
and       ah.session_state   in ('ON CPU', 'WAITING')
and       ah.session_id      = &sid
group by  ah.session_id
         ,ah.session_state
         ,ah.event
         ,ah.sql_id
         ,ah.top_level_sql_id
         ,ah.plsql_entry_object_id
         ,ah.plsql_entry_subprogram_id
         ,ah.plsql_object_id
         ,ah.plsql_subprogram_id
order by
          ah.session_id
/

The following SQL shows operations that are currently executing specific to a session, which will give you an indication of what the wait is for if the SQL_ID is null.


alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
col target format a25
col opname format a40
select sid
      ,opname
      ,target
      ,round(sofar/totalwork*100,2)   as percent_done
      ,start_time
      ,last_update_time
      ,time_remaining , start_time-last_update_time
from
       v$session_longops
where
       sid = &sid
/


##################Sql queries to check ACTIVE / INACTIVE Sessions######################################

************************************
Total Count of sessions
************************************

select count(s.status) TOTAL_SESSIONS
from gv$session s;

************************************
Total Count of Inactive sessions
************************************
select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

************************************************************************
SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR
************************************************************************
select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status='INACTIVE';

************************************************************************
COUNT OF ACTIVE SESSIONS
************************************************************************

select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';

************************************************************************
TOTAL SESSIONS COUNT ORDERED BY PROGRAM
************************************************************************

col program for a30
select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.program;

************************************************************************
TOTAL COUNT OF SESSIONS ORDERED BY MODULE
************************************************************************

col module  for a30
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.module;

************************************************************************
TOTAL COUNT OF SESSIONS ORDERED BY ACTION
************************************************************************

col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.action;

************************************************************************
INACTIVE SESSIONS
************************************************************************

prompt INACTIVE SESSIONS
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

************************************************************************
INACTIVE
************************************************************************

prompt INACTIVE SESSIONS
select count(s.status) INACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE';

************************************************************************
INACTIVE PROGRAMS
************************************************************************

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, v$process p
where  p.addr=s.paddr and
s.status='INACTIVE';

************************************************************************
INACTIVE PROGRAMS with disk reads
************************************************************************

prompt INACTIVE SESSIONS
select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
GROUP BY S.PROGRAM;

************************************************************************
INACTIVE SESSIONS COUNT WITH PROGRAM
************************************************************************

col program for a30
prompt TOTAL INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.program
order by 2 desc;

************************************************************************
TOTAL INACTIVE SESSIONS MORE THAN 1HOUR
************************************************************************

col program for a30
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
and s.last_call_et > (3600)
group by s.program
order by 2 desc;

************************************************************************
TOTAL INACTIVE SESSIONS GROUP BY  MODULE
************************************************************************

col program for a60
COL MODULE FOR A30
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.module,count(s.module) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.module;

************************************************************************
INACTIVE SESSION DETAILS MORE THAN 1 HOUR
************************************************************************

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (3600)
order by last_call_et;

************************************************************************
INACTIVE PROGRAM  --ANY--
************************************************************************

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
And s.program='&PROGRAM_NAME'
order by last_call_et;

************************************************************************
INACTIVE MODULES  --ANY--
************************************************************************

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
And s.module like '%order_cleanup_hazmat_v3.sql'
order by last_call_et;

************************************************************************
INACTIVE JDBC SESSIONS
************************************************************************

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

************************************************************************
COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR
************************************************************************

SELECT COUNT(P.SPID)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

************************************************************************
FORMS
TOTAL FORM SESSIONS
************************************************************************

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%');

************************************************************************
FORMS SESSIONS DETAILS
************************************************************************

col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600
order by spid;                    


col machine for a15
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process Client_Process,s.machine
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600;        
order by 4;                          

************************************************************************
INACTIVE FORMS SESSIONS DETAILS
************************************************************************

col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;

UNIQUE SPID

select unique(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

COUNT FORMS

select COUNT(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

ZERO HASH VALUE

select COUNT(p.spid)
from gv$session s,gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME

select count(s.sid) from v$session S
where s.action like ('%&ACTION%')
AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%')
group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152
col spid for a10
col process_spid for a10
col user_name for a20
col form_name for a20
select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,
d.user_form_name "FORM_NAME"
from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl d
where
a.login_id=b.login_id
and c.user_name like 'JROMO'
and a.user_id=c.user_id
and trunc(b.start_time) >trunc(sysdate -11)
and trunc(b.end_time) is null
and b.form_id=d.form_id
and d.language='US';

INACTIVE FORM

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like ('FRM%')
and s.last_call_et > (3600*3)
order by last_call_et;





INACTIVE FORM SESSIONS

col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id        = rf.form_id
AND ft.language       = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins           fl,
gv$process            vp,
apps.fnd_login_resp_forms rf,
gv$session            vs
WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */
AND fl.login_type   = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid          = vp.pid
AND fl.login_id     = rf.login_id
AND rf.end_time    IS NULL
AND rf.audsid       = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;

ACTIVE

prompt ACTIVE SESSIONS
select count(s.status) ACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='ACTIVE';

MODULE

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
and s.MODULE like ('&MODULE_NAME_1HR%')
and s.last_call_et > ('&TIME_HRS' * 3600)
order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S
where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0
AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.machine like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr and
s.status='INACTIVE';

GROUP BY  program

col program for a60
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr  AND
s.machine like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;