Oracle Active session on single Instance and RAC

 


Currently connected sessions waits, longops, sql, etc...
active session sessions 
Instance 

select/*+ RULE*/ distinct--because of multiple records in v$sql for PREV_SQL
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
, gv$session.SERVICE_NAME as service
, gv$session.INST_ID
, gv$session.SID
, gv$session.SERIAL#
, decode(gv$session_wait.state, 'WAITING', gv$session_wait.EVENT,
'On CPU / runqueue') as EVENT
, v$latchname.NAME as LATCHNAME
, gv$latchholder.sid
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE'
, DBMS_ROWID.ROWID_CREATE( 1, gv$session.ROW_WAIT_OBJ#
, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#
, gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P1RAW /* X$BH.HLADDR for LATCH: CACHE BUFFERS CHAINS */
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, ses_optimizer_env38.VALUE as ses_optimizer_mode
, ses_optimizer_env48.VALUE as ses_cursor_sharing
, gv$sql.SQL_ID as SQL_ID
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, gv$sql.SQL_PROFILE
, gv$session.SQL_CHILD_NUMBER SQL_CHILD_NUMBER
, sql1.SQL_ID as PREV_SQL_ID
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
, gv$session.PREV_CHILD_NUMBER
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
, 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#
||''';' as KILL_SESSION
--, gv$session.PROCESS
, gv$instance.HOST_NAME
, 'kill -9 '||gv$process.SPID as KILL_SPID
-- from 10g and above - SQL Trace info
, gv$session.SQL_TRACE, gv$session.SQL_TRACE_WAITS, gv$session.SQL_TRACE_BINDS
, 'begin sys.dbms_support.start_trace_in_session('||gv$session.SID||','||
gv$session.SERIAL#||', waits=>TRUE, binds=>TRUE );end;'
, 'begin sys.dbms_support.stop_trace_in_session('||gv$session.SID||','||
gv$session.SERIAL#||' );end;'
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$ses_optimizer_env ses_optimizer_env38
, gv$ses_optimizer_env ses_optimizer_env48
, gv$lock lockwait
, gv$lock lockhold
, gv$latchholder
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.SQL_CHILD_NUMBER = gv$sql.CHILD_NUMBER(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.PREV_CHILD_NUMBER = sql1.CHILD_NUMBER(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and gv$session.SERVICE_NAME not in ('SYS$BACKGROUND')
and gv$session.PROGRAM not like '%QMNC%' --Queue Monitor Coordinator excluded
and gv$session.PROGRAM not like '%q00%' --Queue monitor processes excluded
--and v$session.PROGRAM not like '%J00%' --DBMS_JOB processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session_wait.p1raw = gv$latchholder.laddr(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and (nvl(gv$session_longops.TIME_REMAINING, 1) > 0
     or
     nvl(gv$session_longops.START_TIME, sysdate) = (select max(START_TIME) from gv$session_longops gsl where gv$session.SID = gsl.sid and  gv$session.INST_ID = gsl.INST_ID and gv$session.SERIAL# = gsl.SERIAL# and gv$session.sql_address = gsl.SQL_ADDRESS and gv$session.sql_hash_value = gsl.SQL_HASH_VALUE))
and gv$session.sid = ses_optimizer_env38.sid(+)
and gv$session.INST_ID = ses_optimizer_env38.INST_ID(+)
and nvl(ses_optimizer_env38.id,38) = 38 --optimizer_mode
and gv$session.sid = ses_optimizer_env48.sid(+)
and gv$session.INST_ID = ses_optimizer_env48.INST_ID(+)
and nvl(ses_optimizer_env48.id, 48) = 48--cursor_sharing
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
order by tx_stime, status, LOGON_TIME, username, gv$session.sid;



=================



SET LINES 300 pagesize 300
col kill for a15
col UNAM2 format a20  word  heading "Oracle User/OS User"
col PROG format a15 word heading "Program|Terminal"
col SID  format a10 word heading "SID/|Serial#"
col STAT format A7 Heading "Status"
col LTIM format a20 word heading "Logon Time/|Last Activy time"
col TIMI format a20 word heading "Start time/|Last Update time|remaining->elapsed|[%done end time]"
col STMT format a64 heading "Statement" WORD
col OPNA format a10 WORD PRINT HEADING "Long Op"
col WORK format a12 WORD PRINT HEADING "Target|So far|Total Work"
col MESS format a50 WORD PRINT HEADING "Long op message" wrap
--NEWLINE
SELECT   
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill
,ses.sql_id
,lo.opname opna
        ,NVL(ses.username,lo.username)||' / '||ses.osuser||DECODE(ses.audsid,userenv('SESSIONID'),'**','')  unam2
        ,NVL(ses.status,'unknown') stat
--      ,TO_CHAR(ses.logon_time,'DD-MON-YYYY:HH24:MI:SS')||'  '
--        ||to_char(sysdate - (ses.last_call_et)/86400,'DD-MON-YYYY:hh24:mi:ss') ltim
         ,NVL(LPAD(TO_CHAR(target),11,'_'),'___________')||','
         ||LPAD(TO_CHAR(sofar),11,'_')||','
         ||LPAD(TO_CHAR(totalwork),11,'_')||' ('||units||') ['
         ||DECODE(0,totalwork,'n/a',CEIL(100*sofar/(totalwork)))||'%]'
         work
        ,start_time||' '
         ||last_update_time
         ||' '||TO_CHAR(time_remaining)||''
         ||'>;'||elapsed_seconds||' ['||
         DECODE(0,time_remaining+elapsed_seconds,'n/a',CEIL(100*elapsed_seconds/(time_remaining+elapsed_seconds)))||'% eta '
         ||TO_CHAR(sysdate+(time_remaining/(60*60*24)),'HH24:MIpm')||']' timi
        ,DECODE(ses.program,'','',ses.program||' '||ses.terminal)  prog
--      ||'('||RTRIM(ses.machine)||')'
--        ,lo.sid||'/'||lo.serial#||' ['||ses.audsid||']' sid
        ,message mess
		,REPLACE(s.sql_text,CHR(10),' ') stmt
--,context
FROM     gv$sql s   ,gv$session_longops lo  ,gv$session ses
WHERE   lo.sql_address=s.address
and     lo.inst_id=s.inst_id
AND     lo.sql_hash_value=s.hash_value
AND     lo.time_remaining!=0
AND     lo.sid=ses.sid(+)
AND     lo.serial#=ses.serial#(+)
/




set linesize 200 pages 200
col sid format 99999
col name format a36
col p1 format 999999999 Head 'P1'
col program format a25
col p2 format 999999999 Head 'P2'
col p3 format 999999999 Head 'P3'
col pgm format a15 head 'What'
col state format a15
col wt format 9999999 head 'Wait|Time'
col WaitEvent format a38 head 'Wait Event'
col lc format 99999999999.99 head 'last call'
col kill for a16
select 
--A.sid,
''''||b.sid ||','|| b.serial#||',@'||b.inst_id ||'''' kill,
decode(A.event,'null event','CPU Exec',A.event) WaitEvent,A.p1,A.p2,A.p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm,b.sql_id,PREV_SQL_ID
from gv$session_wait A,gv$session B
where 1=1
and (A.event like 'gc%'
or A.event like 'GC%'
or A.event like 'ge%')
and A.event not like '%remote message'
and A.event not like '%sleep'
and A.sid=B.sid
and B.status='ACTIVE'
and a.inst_id=b.inst_id
order by 1
/

   


define interval_mins=30
set lines 200
col samplestart format a30 head 'Begin Time'
col sampleend format a30 head 'End Time'
col aas format 999.99 head 'AAS'
col aas_event format 999.99 head 'AAS per |Event'
col dbt format 999999 head 'DB Time'
col event format a30
col time_waited format 999999 head 'Time (ms)'
col wait_pct format 999.99 head '% of DB Time'
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;

with xtimes (xdate) as
(select to_date('&_bt') xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,'DD-MON-RR HH24:MI:SS') samplestart,
to_char(s1.xdate+(&&interval_mins/1440),'DD-MON-RR HH24:MI:SS') sampleend,
s2.event,.001*((sum(s2.time_waited))) time_waited,
(count(s2.sample_id)/(60*&&interval_mins)) aas_event,
(count(s3.sample_id)) dbt,
round(100*(sum(s2.time_waited)/1000000/count(s3.sample_id)),2) as wait_pct
from xtimes s1,
     v$active_session_history s2,
     v$active_session_history s3
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s3.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s2.sample_id=s3.sample_id
and (s2.event  like 'gc%'
or s2.event like 'GC%'
or s2.event like 'ge%')
and s2.event not like '%remote message'
and s2.event not like '%sleep'
group by s1.xdate,s2.event
order by s1.xdate
/
undefine interval_mins






Measuring RAC Waits from DBA_HIST_ACTIVE_SESS_HISTORY




define interval_mins=30
set lines 200
col samplestart format a30 head 'Begin Time'
col sampleend format a30 head 'End Time'
col aas format 999.99 head 'AAS'
col aas_event format 999.99 head 'AAS per |Event'
col event format a30
col time_waited format 999999 head 'Time (ms)'
col wait_pct format 999.99 head 'Wait %'
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;
with xtimes (xdate) as
(select to_date('&_bt') xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,'DD-MON-RR HH24:MI:SS') samplestart,
to_char(s1.xdate+(&&interval_mins/1440),'DD-MON-RR HH24:MI:SS') sampleend,
s2.event,
.001*((sum(s2.time_waited))) time_waited,
10*(count(s2.sample_id)/(60*&&interval_mins)) aas_event,
10*(count(s3.sample_id)) dbt,
round(100*(sum(s2.time_waited)/1000000/(10*(count(s3.sample_id)))),2) as wait_pct
from xtimes s1,
     dba_hist_active_sess_history s2,
     dba_hist_active_sess_history s3
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s3.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s2.sample_id=s3.sample_id
and (s2.event  like 'gc%'
or s2.event like 'GC%'
or s2.event like 'ge%')
and s2.event not like '%remote message'
and s2.event not like '%sleep'
group by s1.xdate,s2.event
order by s1.xdate
/