SELECT DISTINCT hash_value,
                sql_text
FROM   gv$sql sq
WHERE  hash_value IN (SELECT DISTINCT prev_hash_value
                      FROM   gv$session se
                      WHERE  sid IN (SELECT sid
                                     FROM   gv$lock l
                                     WHERE  type = 'TX'
                                            AND ctime >= 2000
                                            AND l.inst_id = se.inst_id
                                            AND l.sid = se.sid)
                             AND sq.inst_id = se.inst_id);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=1 Bytes=609)
   1    0   SORT (UNIQUE) (Cost=93 Card=1 Bytes=609)
   2    1     HASH JOIN (Cost=91 Card=1 Bytes=609)
   3    2       MERGE JOIN (CARTESIAN) (Cost=79 Card=5 Bytes=2850)
   4    3         VIEW* OF 'GV$LOCK'                                   :Q404510
                                                                       00

   5    4           HASH JOIN* (Cost=68 Card=1 Bytes=79)               :Q404510
                                                                       00

   6    5             HASH JOIN* (Cost=56 Card=4 Bytes=280)            :Q404510
                                                                       00

   7    6               VIEW OF 'GV$_LOCK' (Cost=44 Card=4 Bytes=152)
   8    7                 UNION-ALL
   9    8                   VIEW OF 'GV$_LOCK1' (Cost=22 Card=2 Bytes=
          178)

  10    9                     UNION-ALL
  11   10                       FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost
          =11 Card=1 Bytes=102)

  12   10                       FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=
          11 Card=1 Bytes=102)

  13    8                   FIXED TABLE (FULL) OF 'X$KTADM' (Cost=11 C
          ard=1 Bytes=102)

  14    8                   FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=11 C
          ard=1 Bytes=102)

  15    6               FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=11 Card=
          100 Bytes=3200)

  16    5             FIXED TABLE* (FULL) OF 'X$KSQRS' (Cost=11 Card=1 :Q404510
           Bytes=9)                                                    00

  17    3         BUFFER (SORT) (Cost=79 Card=5 Bytes=2640)
  18   17           VIEW* OF 'GV$SQL'                                  :Q404520
                                                                       00

  19   18             FIXED TABLE* (FULL) OF 'X$KGLOB' (Cost=11 Card=5 :Q404520
           Bytes=2765)                                                 00

  20    2       VIEW* OF 'GV$SESSION'                                  :Q404530
                                                                       00

  21   20         FIXED TABLE* (FULL) OF 'X$KSUSE' (Cost=11 Card=1 Byt :Q404530
          es=65)                                                       00



   4 PARALLEL_TO_SERIAL            SELECT A1."INST_ID",A1."SID",A1."TYPE",A1."C
                                   TIME" FROM "GV$LOCK" A1 WHERE A1."CT

   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_FROM_SERIAL
  16 PARALLEL_FROM_SERIAL
  18 PARALLEL_TO_SERIAL            SELECT A1."INST_ID",A1."SQL_TEXT",A1."HASH_V
                                   ALUE" FROM "GV$SQL" A1

  19 PARALLEL_COMBINED_WITH_PARENT
  20 PARALLEL_TO_SERIAL            SELECT A1."INST_ID",A1."SID",A1."PREV_HASH_V
                                   ALUE" FROM "GV$SESSION" A1

  21 PARALLEL_COMBINED_WITH_PARENT

SELECT DISTINCT hash_value,
                sql_text
FROM   gv$sql sq
WHERE  ( hash_value, address ) IN (SELECT prev_hash_value,
                                          sql_address
                                   FROM   gv$session se
                                   WHERE  sid IN (SELECT sid
                                                  FROM   gv$lock l
                                                  WHERE  type = 'TX'
                                                         AND ctime >= 2000
                                                         AND
                                                 l.inst_id = se.inst_id
                                                         AND l.sid = se.sid)
                                          AND sq.inst_id = se.inst_id);  

ECSCDAP4P > @v$sqlarea_elapsed.sql

TOT_SEC_ELAPSED HASH_VALUE EXECUTIONS INSTANC AVG_SEC_PER_EXEC SQL_TEXT
--------------- ---------- ---------- ------- ---------------- ---------
      57.039289  130260116         56 CSCDAP4       1.01855873 select *
      57.642821  535777823         56 CSCDAP4       1.02933609 select *
     16332.6634  304521275      13083 CSCDAP4       1.24838825 select *
     104.498913 3115159362         83 CSCDAP2       1.25902305 select *
      93.248114 1077395695         66 CSCDAP4       1.41285021 select *
      129.68471  188283226         83 CSCDAP2       1.56246639 select *
     910.364119 2431632681         56 CSCDAP1       16.2565021 select *
                           ----------
sum                             13483

ECSCDAP4P > select sql_text
  2  from gv$sqltext b
  3  where b.hash_value=2431632681
  4  and inst_id=1
  5* order by inst_id,hash_value,b.piece;

SQL_TEXT
----------------------------------------------------------------
select * from ( select audiovideo0_.id as id0_, audiovideo0_1_.E
XTERNALID as EXTERNALID0_, audiovideo0_1_.author as author0_, au
diovideo0_1_.body as body0_, audiovideo0_1_.byline as byline0_,
audiovideo0_1_.club_id as club18_0_, audiovideo0_1_.comments as
comments0_, audiovideo0_1_.copyright as copyright0_, audiovideo0
_1_.CONTENT_DATE as CONTENT8_0_, audiovideo0_1_.imageURL as imag
eURL0_, audiovideo0_1_.shortName as shortName0_, audiovideo0_1_.
SHOW_IN_DYNA_LISTS as SHOW11_0_, audiovideo0_1_.source as source
0_, audiovideo0_1_.status as status0_, audiovideo0_1_.summary as
 summary0_, audiovideo0_1_.thumbnailImagePath as thumbna15_0_, a
udiovideo0_1_.title as title0_, audiovideo0_1_.url as url0_, aud
iovideo0_.largeImagePath as largeIma2_79_, audiovideo0_.mediumIm
agePath as mediumIm3_79_, audiovideo0_.smallImagePath as smallIm
a4_79_, audiovideo0_.encodings as encodings79_, audiovideo0_.hig
hResolutionStream as highReso6_79_, audiovideo0_.length as lengt
h79_, audiovideo0_.lowResolutionStream as lowResol8_79_, audiovi
deo0_.mediumResolutionStream as mediumRe9_79_, audiovideo0_.MOBI
LE_STREAM_URL_1 as MOBILE10_79_, audiovideo0_.MOBILE_STREAM_URL_
2 as MOBILE11_79_, audiovideo0_.MOBILE_STREAM_URL_3 as MOBILE12_
79_, audiovideo0_.playType as playType79_, audiovideo0_.primaryC
hannel as primary14_79_, audiovideo0_.SINGLE_BITRATE_PATH as SIN
GLE15_79_, audiovideo0_.streamingServer as streami16_79_, audiov
ideo0_.type as type79_ from AUDIO_VIDEO audiovideo0_ inner join
CONTENT audiovideo0_1_ on audiovideo0_.id=audiovideo0_1_.id wher
e audiovideo0_1_.status = 'ACTIVE' and audiovideo0_1_.CONTENT_DA
TE <= CURRENT_TIMESTAMP and audiovideo0_.type='VIDEO' and audiov
ideo0_1_.SHOW_IN_DYNA_LISTS='Y' and audiovideo0_1_.club_id=:1 an
d (audiovideo0_.id in (select content1_.id from CONTENT content1
_ inner join CONTENT_PERSONNEL personnel2_ on content1_.id=perso
nnel2_.CONTENT_FK inner join PERSONNEL personnel3_ on personnel2
_.PERSONNEL_FK=personnel3_.id where personnel3_.id in (select ch
eerleade4_.id from CHEERLEADER cheerleade4_ inner join PERSONNEL
 cheerleade4_1_ on cheerleade4_.id=cheerleade4_1_.id))) order by
 audiovideo0_1_.CONTENT_DATE desc ) where rownum <= :2