Hi, anandkl, I have checked that views:
Code:
select * from v$session_event where sid=9;
       SID EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ----------- -------------- ----------- ------------
         9 db file sequential read                 41              0           0            0
         9 SQL*Net message to client               65              0           0            0
         9 SQL*Net message to dblink               79              0           0            0
         9 SQL*Net message from client             65              0           0            0
         9 SQL*Net message from dblink             79              0           0            0
         9 SQL*Net more data from dblink            2              0           0            0
======================================
select * from v$system_event;
EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- -------------- ----------- ------------
pmon timer                           17193          17193           0            0
process startup                          6              0           0            0
rdbms ipc reply                         20              0           0            0
rdbms ipc message                    51641          51608           0            0
control file sequential read           122              0           0            0
control file parallel write             49              0           0            0
log file sequential read                10              0           0            0
log file single write                    7              0           0            0
log file parallel write                 46              0           0            0
log file sync                            2              0           0            0
db file sequential read                490              0           0            0
db file scattered read                 104              0           0            0
db file single write                    18              0           0            0
db file parallel write                   9              0           0            0
instance state change                    2              0           0            0
smon timer                             174            171           0            0
SQL*Net message to client              871              0           0            0
SQL*Net message to dblink               79              0           0            0
SQL*Net more data to client             50              0           0            0
SQL*Net message from client            870              0           0            0
SQL*Net more data from client          202              0           0            0
SQL*Net message from dblink             79              0           0            0
SQL*Net more data from dblink            2              0           0            0
SQL*Net break/reset to client           14              0           0            0
==================================
select * from v$session_wait;
       SID       SEQ# EVENT                          P1TEXT             P1 P1RAW    P2TEXT             P2 P2RAW    P3TEXT             P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ------------------------------ ---------- ---------- -------- ---------- ---------- -------- ---------- ---------- -------- ---------- --------------- -------------------
         1      17195 pmon timer                     duration          300 0000012C                     0 00                           0 00                0               0 WAITING
         2      17284 rdbms ipc message              timeout           300 0000012C                     0 00                           0 00                0               0 WAITING
         3      17344 rdbms ipc message              timeout           300 0000012C                     0 00                           0 00                0               0 WAITING
         6         33 rdbms ipc message              timeout        180000 0002BF20                     0 00                           0 00                0               0 WAITING
         4      17195 rdbms ipc message              timeout           300 0000012C                     0 00                           0 00                0               0 WAITING
         5        308 smon timer                     sleep time        300 0000012C failed              0 00                           0 00                0               0 WAITING
         7       1050 SQL*Net message from client    driver id  1413697536 54435000 #bytes              1 00000001                     0 00               -2               0 WAITED UNKNOWN TIME
         8         50 SQL*Net message from client    driver id  1413697536 54435000 #bytes              1 00000001                     0 00                0               0 WAITING
         9        332 SQL*Net more data from dblink  driver id  1129532494 43534C4E #bytes            195 000000C3                     0 00                0               0 WAITING

9 rows selected.
Originally posted by anandkl
Hi,
Your session could be hanging if its waiting for some resource,currently used by other sessions or process.Try quering v$system_event,v$session_event and finally v$session_wait to get an idea of the events waiting for the resource.Probabaly ur session might be experienceing "buffer busy waits",if ur triggering is doing some DML activites or having some I/O related problems.
regards
anandkl