Skip to content

script to find locks in awr

script to find locks in awr published on 1 комментарий к записи script to find locks in awr
select  cast(min (ash.SAMPLE_TIME) as date) as start#
     ,round (24*60*(cast (max(ash.SAMPLE_TIME) as date) - cast(min (ash.SAMPLE_TIME) as date) ),2) as duration#
     ,ash.sql_id,ash.top_level_sql_id,ash.BLOCKING_SESSION as B_SID,ash.BLOCKING_SESSION_SERIAL# as b_serial#
     ,ash2.SQL_EXEC_ID b_sql_exec_id
     ,ash.event,do.object_name
     ,sum(decode(ash.session_state,'ON CPU',1,0))     "CPU"
     ,sum(decode(ash.session_state,'WAITING',1,0))    -         sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0))    "WAIT" 
     ,sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0))    "IO" 
     ,sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
     ,du.username,ash2.SQL_EXEC_ID,
          dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null) as pl_sql_obj
          ,ash2.machine as blocking_machine
from dba_hist_active_sess_history ash
  left join dba_objects do on do.object_id=ash.CURRENT_OBJ#
  join dba_hist_active_sess_history ash2 on ash.BLOCKING_SESSION=ash2.session_id and ash.BLOCKING_SESSION_SERIAL#=ash2.session_serial# and ash.SNAP_ID=ash2.SNAP_ID
    join dba_users du on du.USER_ID=ash2.USER_ID
    left join dba_procedures dp on dp.object_id=ash2.PLSQL_ENTRY_OBJECT_ID and dp.subprogram_id=ash.PLSQL_ENTRY_SUBPROGRAM_ID
where ash.SQL_ID is not NULL       
and ash.SAMPLE_TIME >  trunc(sysdate)
group by ash.SQL_EXEC_ID,ash2.SQL_EXEC_ID, ash2.machine, ash.session_id,ash.session_serial#,ash.event,ash.sql_id,ash.top_level_sql_id,ash.BLOCKING_SESSION,ash.BLOCKING_SESSION_SERIAL#, ash2.sql_id    ,du.username,
          dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null)
               ,do.object_name
having  sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0))  >0
and max(ash.SAMPLE_TIME) - min (ash.SAMPLE_TIME) > interval '3' minute
order by 1,ash2.sql_exec_id;

result

START#                    |  DURATION# | SQL_ID        | TOP_LEVEL_SQL |      B_SID |  B_SERIAL# | B_SQL_EXEC_ID | EVENT                          | OBJECT_NAME          |        CPU |       WAIT |         IO |      TOTAL | USERNAME   | PL_SQL_OBJ | BLOCKING_MACHINE
------------------------- | ---------- | ------------- | ------------- | ---------- | ---------- | ------------- | ------------------------------ | -------------------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- | --------------------------
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |         21 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1832 |      38589 |      16777230 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |      15876 |          0 |      15876 | EOS        | <NULL>     | xxxxxxxapp11.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777232 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2500 |          0 |       2500 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777232 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2500 |          0 |       2500 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777263 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777263 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777483 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777483 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777991 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16777991 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16778307 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16778307 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16779789 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 08.34.21       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |        955 |      30987 |      16779789 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         50 |          0 |         50 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       1638 |          0 |       1638 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       1638 |          0 |       1638 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       1638 |          0 |       1638 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777326 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777326 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777326 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16779119 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16779119 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16779119 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16780460 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16780460 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.33       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16780460 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         26 |          0 |         26 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.40       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       1575 |          0 |       1575 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.40       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16777326 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.40       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16779119 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.40.40       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1520 |      40745 |      16780460 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp10.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 |      16777216 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 |      16777282 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3150 |          0 |       3150 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 |      16780480 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 |      16780498 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 |      25502663 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 |      28318376 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 09.51.05       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |          9 |      28281 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 10.04.18       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |        448 |      52711 |      16777231 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.04.18       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |        448 |      52711 |      16777298 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       1444 |          0 |       1444 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16777300 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |        650 |          0 |        650 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16777300 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |        650 |          0 |        650 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16777311 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16777311 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16777313 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16777313 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16779344 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16779344 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16782095 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16782095 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |        448 |      52711 |      16782272 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16794081 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 |      16794081 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.04.18       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1019 |       6185 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp09.xxxx.local
24.11.2015 10.10.39       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |        766 |      15691 |      16777299 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2812 |          0 |       2812 | EOS        | <NULL>     | xxxxxxxapp11.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777322 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2331 |          0 |       2331 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777322 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2331 |          0 |       2331 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777322 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2331 |          0 |       2331 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777322 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2331 |          0 |       2331 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16779690 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16779690 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16779690 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16779690 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16781164 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16781164 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16781164 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16781164 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         74 |          0 |         74 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         74 |          0 |         74 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         74 |          0 |         74 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.34       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         74 |          0 |         74 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.39       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777279 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.39       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16777322 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2394 |          0 |       2394 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.39       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16779690 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.39       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 |      16781164 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.38.39       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1074 |      21737 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         76 |          0 |         76 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 10.44.45       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1902 |      33545 |      16777324 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3800 |          0 |       3800 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.44.45       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1902 |      33545 |      16777324 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3800 |          0 |       3800 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.44.45       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1902 |      33545 |      16779227 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.44.45       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1902 |      33545 |      16779227 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.44.45       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1902 |      33545 |      16782940 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.44.45       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1902 |      33545 |      16782940 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         38 |          0 |         38 | EOS        | <NULL>     | xxxxxxxapp07.xxxx.local
24.11.2015 10.45.00       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1705 |      47971 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         49 |          0 |         49 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 10.45.00       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1705 |      47971 |      16777323 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       5537 |          0 |       5537 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 10.45.00       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1705 |      47971 |      16778606 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         49 |          0 |         49 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 10.45.00       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1705 |      47971 |      16779700 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         49 |          0 |         49 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 10.45.00       |          8 | by5pctpk8t9f6 | by5pctpk8t9f6 |       1705 |      47971 |      16781227 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         49 |          0 |         49 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 11.10.40       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2846 |      53187 |      16777344 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2812 |          0 |       2812 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 11.10.40       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2846 |      53187 |      16777723 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 11.10.40       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2846 |      53187 |      16778841 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 11.10.40       |          6 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2846 |      53187 | <NULL>        | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         37 |          0 |         37 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 11.19.02       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2521 |      48929 |      16777348 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       2850 |          0 |       2850 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 11.19.02       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2521 |      48929 |      16777366 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 11.19.02       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2521 |      48929 |      16781519 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp04.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777226 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777226 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777226 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777226 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777364 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3969 |          0 |       3969 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777364 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3969 |          0 |       3969 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777364 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3969 |          0 |       3969 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777364 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3969 |          0 |       3969 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16779386 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16779386 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16779386 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.14       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16779386 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.18       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777221 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.18       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777226 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.18       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16777364 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3969 |          0 |       3969 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.34.18       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |         10 |       3747 |      16779386 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp01.xxxx.local
24.11.2015 11.51.07       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2905 |      49181 |      33554433 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 11.51.07       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2905 |      49181 |      33554434 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 11.51.07       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2905 |      49181 |      33554435 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         25 |          0 |         25 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 11.51.07       |          4 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2905 |      49181 |      33554443 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3150 |          0 |       3150 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16777277 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16777308 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16777384 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |       3969 |          0 |       3969 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16777431 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16780048 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16782367 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.04.20       |         10 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2147 |      47127 |      16813229 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |         63 |          0 |         63 | EOS        | <NULL>     | xxxxxxxapp03.xxxx.local
24.11.2015 12.06.20       |         17 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2533 |      57525 |      16777257 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |        101 |          0 |        101 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 12.06.20       |         17 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2533 |      57525 |      16777260 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |        101 |          0 |        101 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 12.06.20       |         17 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2533 |      57525 |      16777390 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |      11413 |          0 |      11413 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 12.06.20       |         17 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2533 |      57525 |      16778344 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |        101 |          0 |        101 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local
24.11.2015 12.06.20       |         17 | by5pctpk8t9f6 | by5pctpk8t9f6 |       2533 |      57525 |      16779246 | enq: TX - row lock contention  | FDC_ROSIM_TICKETS    |          0 |        101 |          0 |        101 | EOS        | <NULL>     | xxxxxxxapp06.xxxx.local

1 комментарий

ash variant:

select  cast(min (ash.SAMPLE_TIME) as date) as start#
     ,round (24*60*(cast (max(ash.SAMPLE_TIME) as date) - cast(min (ash.SAMPLE_TIME) as date) ),2) as duration#
     ,ash.sql_id,ash.top_level_sql_id,ash.BLOCKING_SESSION as B_SID,ash.BLOCKING_SESSION_SERIAL# as b_serial#
     ,ash2.SQL_EXEC_ID b_sql_exec_id
     ,ash.event,do.object_name
     ,sum(decode(ash.session_state,'ON CPU',1,0))     "CPU"
     ,sum(decode(ash.session_state,'WAITING',1,0))    -         sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0))    "WAIT"
     ,sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0))    "IO"
     ,sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
     ,du.username,ash2.SQL_EXEC_ID,
          dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null) as pl_sql_obj
          ,ash2.machine as blocking_machine
from gv$active_session_history ash
  left join dba_objects do on do.object_id=ash.CURRENT_OBJ#
  join gv$active_session_history ash2 on ash.BLOCKING_SESSION=ash2.session_id and ash.BLOCKING_SESSION_SERIAL#=ash2.session_serial# 
    join dba_users du on du.USER_ID=ash2.USER_ID
    left join dba_procedures dp on dp.object_id=ash2.PLSQL_ENTRY_OBJECT_ID and dp.subprogram_id=ash.PLSQL_ENTRY_SUBPROGRAM_ID
where ash.SQL_ID is not NULL      
and ash.SAMPLE_TIME >  sysdate-1/24
group by ash.SQL_EXEC_ID,ash2.SQL_EXEC_ID, ash2.machine, ash.session_id,ash.session_serial#,ash.event,ash.sql_id,ash.top_level_sql_id,ash.BLOCKING_SESSION,ash.BLOCKING_SESSION_SERIAL#, ash2.sql_id    ,du.username,
          dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null)
               ,do.object_name
having  sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(ash.wait_class, 'User I/O',1,0),0))  >0
and max(ash.SAMPLE_TIME) - min (ash.SAMPLE_TIME) > interval '20' second
order by 1,ash2.sql_exec_id;

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика