Blockers performance tuning queries

select name,open_mode,log_mode,host_name,database_role,to_char(startup_time,'dd/mm/yyyy hh:mm:ss')startup_time from v$database,v$instance;


SQL> -- Show all BLOCKERS and who they are blocking(BLOCKEES)...
SQL>
set lines 110 wrap on echo off
SQL> SQL> column id1 noprint
SQL> column id2 noprint
SQL> col TYPE format a10
SQL> col blockedby format a10
SQL> col Usr format a20
SQL> column minheld format 99999.9 heading "Mins|Held"
SQL>
SQL> select /*+ ORDERED */
  2         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
  3         l.sid, '         ' blockedby,
  4         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
  5         substr(s.module,1,30) module
  6    from v$lock l, v$session s, v$parameter p
  7   where l.block > 0
  8     and l.sid = s.sid
  9     and p.name = 'parallel_server' and p.value='FALSE'
 10  union
 11  select /*+ ORDERED */
 12         '  Blockee' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
 13         l.sid, substr(to_char(b.sid),1,9) blockedby,
 14         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
 15         substr(s.module,1,30) module
 16    from v$lock l, v$session s, v$parameter p,
 17         (select sid, id1, id2 from v$lock where block > 0) b
 18   where l.block = 0
 19     and l.sid = s.sid
 20     and l.id1 = b.id1
 21     and l.id2 = b.id2
 22     and p.name = 'parallel_server' and p.value='FALSE'
 23  union
 24  select /*+ ORDERED */
 25         '* Blocker' TYPE, rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
 26         l.sid, '         ' blockedby,
 27         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
 28         substr(s.module,1,30) module
 29    from v$lock l, v$session s, v$parameter p
 30   where l.block > 0
 31     and l.sid = s.sid
 32     and l.lmode = 6
 33     and l.type = 'TX'
 34     and p.name = 'parallel_server' and p.value='TRUE'
 35  union
 36  select /*+ ORDERED */
 37         '  Blockee', rpad(S.OSUSER,8)||'/'||rpad(S.USERNAME,8) Usr,
 38         l.sid, substr(to_char(b.sid),1,9) blockedby,
 39         s.serial#, l.type, l.lmode "LMode", l.ctime/60 minheld, l.id1, l.id2,
 40         substr(s.module,1,30) module
 41    from v$lock l, v$session s, v$parameter p,
 42         (select bl.sid, bl.id1, bl.id2 from v$lock bl
 43           where bl.block > 0
 44             and bl.lmode = 6
 45             and bl.type = 'TX') b
 where l.block > 0
 46   47     and l.sid = s.sid
 48     and l.id1 = b.id1
 49     and l.id2 = b.id2
 50     and l.lmode = 0
 51     and l.type = 'TX'
 52     and p.name = 'parallel_server' and p.value='TRUE'
 53   order by 1 desc, 8, 9;
--
-- Lock Types:
--   TM(DML)
--   TX(transaction enqueue)
--   UL(user supplied)
-- Lock Modes:
--   0 None
--   1 Null(NULL)
--   2 Row-S(SS)
--   3 Row-X(SX)
--   4 Share(S)
--   5 S/Row-X(SSX)
--   6 Exclusive(X)

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;

Flush bad sql plan from shared pool

If the query is running slower, then it is probably using the bad plan. This can be checked by running below query and observing the plan number for the latest plan.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';  select plan_hash_value, child_number, TIMESTAMP from v$sql_plan where sql_id ='49fhsvm5z337y' order by 3;

This can usually be resolved by flushing the query from the cache by using the below steps:

select address, hash_value from v$sqlarea where sql_id='49fhsvm5z337y';  Use the values from above query in the below purge statement:  exec sys.dbms_shared_pool.purge('C000000EF6D74030,3421605118','C');

Following this, the query should use the correct plan:

SQL> select plan_hash_value, child_number, TIMESTAMP from v$sql_plan where sql_id ='49fhsvm5z337y' order by 3;  PLAN_HASH_VALUE CHILD_NUMBER TIMESTAMP  --------- ------- ------------       1784340712 0 01-DEC-2012 04:55       1784340712 0 01-DEC-2012 04:55       1784340712 0 01-DEC-2012 04:55       1784340712 0 01-DEC-2012 04:55

If the above flush does not work, then another possible option could be to  analyze the table with a 100% estimate:

Begin   dbms_stats.gather_table_stats(ownname=>'EPUSER1',  tabname=>'LISTING_CUR_DIM',estimate_percent=>100,  method_opt=>' FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>2,granularity=>'DEFAULT',cascade=>TRUE,no_invalidate=>FALSE);  END;

Also, here's a very helpful query to check the execution history of the SQL along with the execution plan:

set pages 100 lines 204 wrap off column "Parsing Schema" format a15 column "ms Per Exec" format 9999999.99 column "Total Execs" format 999999999 column "Gets Per Exec" format 99999999 column "CPU %" format 99999999999 column "Rows Per Exec" format 99999999 column "Disk Reads Per Exec" format 99999999 Column "Start Time" format a18 Column "End Time" format a18 column " TPS" format 99999 select  a.snap_id "Snap Id", parsing_schema_name "Parsing Schema", plan_hash_value,         to_char(min(b.begin_interval_time),'MM/DD/YYYY HH24:MI') "Start Time", to_char(max(b.end_interval_time),'MM/DD/YYYY HH24:MI') "End Time",         a.sql_id, round(sum(elapsed_time_delta)/1000/sum(executions_delta),2) "ms Per Exec", sum(a.executions_delta) "Total Execs",          round(sum(buffer_gets_delta)/sum(executions_delta)) "Gets Per Exec",        round(sum(rows_processed_delta)/sum(executions_delta)) "Rows Per Exec" from ( select snap_id, sql_id, instance_number,parsing_schema_name, plan_hash_value, executions_delta, elapsed_time_delta,      buffer_gets_delta, cpu_time_delta, disk_Reads_delta,rows_processed_delta from dba_hist_sqlstat x where sql_id in ('49fhsvm5z337y') and   x.snap_id between  1 and 100000000) a, dba_hist_snapshot b where executions_delta >0 and   a.snap_id = b.snap_id and   a.instance_number=b.instance_number group by a.snap_id, a.sql_id, parsing_schema_name, plan_hash_Value order by 2,1;

The output of the above query will be as below. As can be seen from the execution history, when the plan_hash_value is 1784340712, thequery gets executed in < 10 ms per exec and performs <=6 block reads per execution ("Gets Per Exec"). Because of the low elapsed time for the query, there are also more executions of the query. With the bad plan, the execution time is very high and "gets per exec" also indicates there are more data blocks being read.

SQL Tuning Set

SQL tuning set (STS) is a database object that contains one or more SQL statements and the  associated execution statistics. You can populate a SQL tuning set from multiple sources, such as SQL recorded in the AWR and SQL in memory, or you can provide specific SQL statements. It’s critical that you be familiar with SQL tuning sets. This feature is used as an input to several of Oracle’s performance tuning and management tools, such as the SQL Tuning Advisor, SQL Plan Management, SQL Access Advisor, and SQL Performance Advisor. The key to understanding is that a SQL tuning set consists of the following:

• One or more SQL statements

• Associated metrics/statistics for each SQL statement

Creating a SQL tuning set:

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'HIGH_IO', description => 'High disk read tuning set');

 END; /

To check the information you can use the below views:

select id, name, created, statement_count from dba_sqlset;

To check the statements/query associated with a sqltune set

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text FROM dba_sqlset_statements;

Drop a sqlset:

EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET' );

Delete specific statements from the sqlset:

BEGIN

DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'IO_STS',basic_filter => 'disk_reads < 2000000');

END;/

Delete complete sqlset:

exec DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'IO_STS');

Populating SQL Tuning Set from High-Resource SQL in AWR:

Problem

You want to create a SQL tuning set and populate it with the top I/O-consuming SQL statements found in the AWR.

Solution

Use the following steps to populate a SQL tuning set from high resource-consuming statements in the AWR:

1. Create a SQL tuning set object.

2. Determine begin and end AWR snapshot IDs.

3. Populate the SQL tuning set with high-resource SQL found in AWR.

4. Load the sqltune set with the SQL found in step 3^rd

Step 1: Create a SQL Tuning Set Object

Create a SQL tuning set. This next bit of code creates a tuning set named IO_STS:

BEGIN

dbms_sqltune.create_sqlset( sqlset_name => 'IO_STS' description => 'STS from AWR');

END;

/

Step 2: Determine Begin and End AWR Snapshot IDs

If you’re unsure of the available snapshots in your database, you can run an AWR report or select the SNAP_ID from DBA_HIST_SNAPSHOTS:

select snap_id, begin_interval_time from dba_hist_snapshot order by 1;

Step 3 and 4^th : Populate the SQL Tuning Set with High-Resource SQL Found in AWR and load them in the sqlset

Now the SQL tuning set is populated with the top 15 SQL statements ordered by disk reads. The begin and end AWR snapshot IDs are 26800 and 26900 respectively:

DECLARE

base_cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN base_cur FOR

SELECT value(x)

FROM table(dbms_sqltune.select_workload_repository(

26800,26900, null, null,'disk_reads',

null, null, null, 15)) x;

--

dbms_sqltune.load_sqlset(

sqlset_name => 'IO_STS',

populate_cursor => base_cur);

END;

/

The prior code populates the top 15 SQL statements contained in the AWR ordered by disk reads.The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function is used to populate a PL/SQL cursor with AWR

information based on a ranking criterion. Next the DBMS_SQLTUNE.LOAD_SQLSET procedure is used to populate the SQL tuning set using the cursor as input.

You can view the details of the SQL tuning set (created in the “Solution” section) via this query:

SELECT

sqlset_name

,elapsed_time

,cpu_time

,buffer_gets

,disk_reads

,sql_text

FROM dba_sqlset_statements

WHERE sqlset_name = 'IO_STS';

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY

Before populating a SQL tuning set, you want to view high-load SQL statements in the AWR. You want to eventually use SQL contained in the AWR as input for populating a SQL tuning set. Basic filter ==> SQL predicate to filter SQL statements from workload; and ranking_measure==> Order by clause on selected SQL statement(s), such as elapsed_time, cpu_time, buffer_gets, disk_reads .result_limit to limit the number of rows returned as output.

SELECT  sql_id ,substr(sql_text,1,20),disk_reads ,cpu_time,elapsed_time

FROM

 table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 21730 ,

end_snap => 22900

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'disk_reads' ,

result_limit => 10)

)

ORDER BY disk_reads DESC;

Populating a SQL Tuning Set from Resource-Consuming SQL in Memory

You want to populate a tuning set from high resource-consuming SQL statements that are currently in the memory.

Use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to populate a SQL tuning set with statements currently in memory. This example creates a tuning set and populates it with high-load resource consuming statements not belonging to the SYS schema and having disk reads greater than 1,000,000:

-- Create the tuning set

EXEC DBMS_SQLTUNE.CREATE_SQLSET('HIGH_DISK_READS');

-- populate the tuning set from the cursor cache

DECLARE

cur DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN cur FOR

SELECT VALUE(x)

FROM table(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000',

NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;

--

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'HIGH_DISK_READS', populate_cursor => cur);

END; /

In the prior code, notice that the SYS user is bookended by sets of two single quotes (not double quotes). The SELECT_CURSOR_CACHE function loads the SQL statements into a PL/SQL cursor, and the LOAD_SQLSET procedure populates the SQL tuning set with the SQL statements.

The DBMS_SQLTUNE.SELECT_CURSOR_CACHE function allows you to extract from memory SQL statements and associated statistics into a SQL tuning set. The procedure allows you to filter SQL statements by various resource-consuming criteria, such as the following:

·         ELAPSED_TIME

·         CPU_TIME

·         BUFFER_GETS

·         DISK_READS

·         DIRECT_WRITES

·         ROWS_PROCESSED

This allows you a great deal of flexibility on how to filter and populate the SQL tuning set.

use DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to retrieve high resource-usage SQL from memory. The result set retrieved by this PL/SQL function can be used as input for populating SQL tuning sets

SELECT sql_id ,substr(sql_text,1,20)

,disk_reads ,cpu_time ,elapsed_time ,buffer_gets ,parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE( basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'cpu_time' ,result_limit => 10

));

Populating SQL Tuning Set with All SQL in Memory

Solution

Use the DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure to efficiently capture all of the SQL

currently stored in the cursor cache (in memory). This example creates a SQL tuning set named PROD_WORKLOAD and then populates by sampling memory for 3,600 seconds (waiting 20 seconds between each polling event):

BEGIN

-- Create the tuning set

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'PROD_WORKLOAD'

,description => 'Prod workload sample');

--

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

sqlset_name => 'PROD_WORKLOAD'

,time_limit => 3600

,repeat_interval => 20);

END;

/

The DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure allows you to poll for queries and memory

and use any queries found to populate a SQL tuning set. This is a powerful technique that you can use when it’s required to capture a sample set of all SQL statements executing.

You have a great deal of flexibility on instructing DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET to capture SQL statements in memory (see Table 11-7 for details on all parameters). For example, you can instruct the procedure to capture a cumulative set of statistics for each SQL statement by specifying a

CAPTURE_MODE of DBMS_SQLTUNE.MODE_ACCUMULATE_STATS.

BEGIN

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

sqlset_name => 'PROD_WORKLOAD'

,time_limit => 60

,repeat_interval => 10

,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);

END;

/

This is more resource-intensive than the default settings, but produces more accurate statistics for each SQL statement.

Transport SQL Tuning Set from Production to TEST Environment:

You’ve identified some resource-intensive SQL statements in a production environment. You want to transport these statements and associated statistics to a test environment, where you can tune the statements without impacting production.

Solution:

The following steps are used to copy a SQL tuning set from one database to another:

1. Create a staging table in source database.

2. Populate the staging table with STS data.

3. Copy the staging table to the destination database.

4. Unpack the staging table in the destination database.

Step 1: Create a Staging Table in the Source Database

Use the DBMS_SQLTUNE.CREATE_STGTAB_SQLSET procedure to create a table that will be used to contain the

SQL tuning set metadata. This example creates a table named STS_TABLE:

BEGIN

dbms_sqltune.create_stgtab_sqlset(

table_name => 'STS_TABLE'

,schema_name => 'MV_MAINT');

END;

/

Step 2: Populate Staging Table with STS Data

Now populate the staging table with STS metadata using DBMS_SQLTUNE.PACK_STGTAB_SQLSET:

BEGIN

dbms_sqltune.pack_stgtab_sqlset(

sqlset_name => 'IO_STS'

,sqlset_owner => 'SYS'

,staging_table_name => 'STS_TABLE'

,staging_schema_owner => 'MV_MAINT');

END;

/

If you’re unsure of the names of the STS you want to transport, run the following query to get the details:

SELECT name, owner, created, statement_count FROM dba_sqlset;

Step 3: Copy the Staging Table to the Destination Database

You can copy the table from one database to the other via Data Pump, the old exp/imp utilities, or by using a database link. This example creates a database link in the destination database and then copies the table from the source database:

create database link source_db connect to mv_maint identified by foo using 'source_db';

In the destination database, the table can be copied directly from the source with the CREATE TABLE AS SELECT statement:

SQL> create table STS_TABLE as select * from STS_TABLE@source_db;

Step 4: Unpack the Staging Table in the Destination Database

Use the DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET procedure to take the contents of the staging table and populate the data dictionary with the SQL tuning set metadata. This example unpacks all SQL tuning sets contained within the staging table:

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

sqlset_name => '%'

,replace => TRUE

,staging_table_name => 'STS_TABLE');

END;

/

How It Works

A SQL tuning set consists of one or more queries and corresponding execution statistics. You will occasionally have a need to copy a SQL tuning set from one database to another. For example, you might be having performance problems with a production database but want to capture and move the top resource-consuming statements to a test database where you can diagnose the SQL (within the STS) without impacting production.

Keep in mind that an STS can be used as input for any of the following tools:

·         SQL Tuning Advisor

·         SQL Access Advisor

·         SQL Plan Management

·         SQL Performance Analyzer

The prior tools are used extensively to troubleshoot and test SQL performance. Transporting a SQL tuning set from one environment to another allows you to use these tools in a testing or development environment.