Monitor Active and Pending Transactions and Parallel Queries

Find active transactions in oracle database:

col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

Find distributed pending transactions in oracle DB:

COL local_tran_id FORMAT a13
COL in_out FORMAT a6
COL database FORMAT a25
COL dbuser_owner FORMAT a15
COL interface FORMAT a3
SELECT local_tran_id, in_out, database, dbuser_owner, interface
FROM dba_2pc_neighbors
/

Monitor parallel queries in oracle db:

col username for a9

col sid for a8

set lines 299

select

s.inst_id,

decode(px.qcinst_id,NULL,s.username,

' - '||lower(substr(s.program,length(s.program)-4,4) ) )

"Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "Slave Set",

to_char(s.sid) "SID",

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

px.req_degree "Requested DOP",

px.degree "Actual DOP", p.spid

from

gv$px_session px,

gv$session s, gv$process p

where

px.sid=s.sid (+) and

px.serial#=s.serial# and

px.inst_id = s.inst_id

and p.inst_id = s.inst_id

and p.addr=s.paddr

order by 5 , 1 desc;

Find user commits per minute in oracle DB:

User commits is the number of commits happening the database. It will be helpful in tracking the

number of transactions in the database. STAT_PER_MIN -Number of commits per minutes,

during that snap time

col STAT_NAME for a20

col VALUE_DIFF for 9999,999,999

col STAT_PER_MIN for 9999,999,999

set lines 200 pages 1500 long 99999999

col BEGIN_INTERVAL_TIME for a30

col END_INTERVAL_TIME for a30

set pagesize 40

set pause on

select hsys.SNAP_ID,

hsnap.BEGIN_INTERVAL_TIME,

hsnap.END_INTERVAL_TIME,

hsys.STAT_NAME,

hsys.VALUE,

hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS

"VALUE_DIFF",

round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY

hsys.SNAP_ID)) /

round(abs(extract(hour from (hsnap.END_INTERVAL_TIME -

hsnap.BEGIN_INTERVAL_TIME))*60 +

extract(minute from (hsnap.END_INTERVAL_TIME -

hsnap.BEGIN_INTERVAL_TIME)) +

extract(second from (hsnap.END_INTERVAL_TIME -

hsnap.BEGIN_INTERVAL_TIME))/60),1)) "STAT_PER_MIN"

from dba_hist_sysstat hsys, dba_hist_snapshot hsnap

where hsys.snap_id = hsnap.snap_id

and hsnap.instance_number in (select instance_number from v$instance)

and hsnap.instance_number = hsys.instance_number

and hsys.STAT_NAME='user commits'

order by 1;