11 Replies Latest reply: Nov 11, 2013 5:45 PM by aanto RSS

    Getting deadlock detected while waiting for resource error for select Query.....

    aanto

      Hi all,

       

      i am getting a below error whenever executing the below select query....

       

      some times it will show dead lock detected while waiting for resource and terminated...

      some times it executes and gives result..

      but all the time it writes an alert to alert log

       

      Plesae suggest how to resolve the issue..........

      Thanks in advance

      .

      Env: Linux / Oracle 11.2.0.3.3

      .

      .

      Error from alert log:

      Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:

      ORA-00060: deadlock detected while waiting for resource

      ORA-10387: parallel query server interrupt (normal)

       

       

      Trace file info... bdrdb_p017_6076.trc:

      Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1

      System name:    Linux

      Node name:      bdrdb.cteplindia.com

      Release:        2.6.18-308.el5PAE

      Version:        #1 SMP Fri Jan 27 17:40:09 EST 2012

      Machine:        i686

      Instance name: bdrdb

      Redo thread mounted by this instance: 1

      Oracle process number: 92

      Unix process pid: 6076, image: oracle@bdrdb.cteplindia.com (P017)

       

       

       

       

      *** 2013-11-04 23:18:57.915

      *** SESSION ID:(423.59970) 2013-11-04 23:18:57.915

      *** CLIENT ID:() 2013-11-04 23:18:57.915

      *** SERVICE NAME:(bdrdb) 2013-11-04 23:18:57.915

      *** MODULE NAME:() 2013-11-04 23:18:57.915

      *** ACTION NAME:() 2013-11-04 23:18:57.915

       

      .

      .

      *** 2013-11-04 23:18:57.915

      DEADLOCK DETECTED ( ORA-00060 )

      .

       

      [Transaction Deadlock]

       

       

      Deadlock graph:

                             ---------Blocker(s)--------  ---------Waiter(s)---------

      Resource Name          process session holds waits  process session holds waits

      PS-00000001-00000011        92     423     S             33     128     S     X

      BF-2ed08c01-00000000        33     128     S             92     423     S     X

       

       

      session 423: DID 0001-005C-00081126     session 128: DID 0001-0021-00067D23

      session 128: DID 0001-0021-00067D23     session 423: DID 0001-005C-00081126

       

       

      DEADLOCK DETECTED ( ORA-00060 )

       

       

      [Transaction Deadlock]

       

       

      Deadlock graph:

                             ---------Blocker(s)--------  ---------Waiter(s)---------

      Resource Name          process session holds waits  process session holds waits

      PS-00000001-00000011        92     423     S             33     128     S     X

      BF-2ed08c01-00000000        33     128     S             92     423     S     X

       

       

      session 423: DID 0001-005C-00081126     session 128: DID 0001-0021-00067D23

      session 128: DID 0001-0021-00067D23     session 423: DID 0001-005C-00081126

       

       

      Rows waited on:

        Session 423: no row

        Session 128: obj - rowid = 00021DC1 - AAAh3BAAVAAAQL/AAA

        (dictionary objn - 138689, file - 21, block - 66303, slot - 0)

       

       

      ----- Information for the OTHER waiting sessions -----

      Session 128:

        sid: 128 ser: 46176 audsid: 1836857 user: 102/DBLOCAL

          flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

          flags2: (0x40009) -/-/INC

        pid: 33 O/S info: user: oracle, term: UNKNOWN, ospid: 31611

          image: oracle@bdrdb.cteplindia.com

        client details:

          O/S info: user: masked, term: masked, ospid: 5924:568

          machine: masked program: Toad.exe

          application name: TOAD background query session, hash value=526966934

        current SQL:

       

       

          application name: TOAD background query session, hash value=526966934

        current SQL:

        SELECT  DISTINCT B_FP_TEST.TEST_ID

        FROM B_FP_TEST,

             B_USER_INFO,

             J_FP_INVESTIGATOR,

             L_TEST_STATUS,

             L_ATMS_TEST_TYPE,

             j_op_test_anml

      WHERE     B_FP_TEST.TEST_ID = J_FP_INVESTIGATOR.TEST_ID

             AND B_FP_TEST.TEST_TYPE_ID = L_ATMS_TEST_TYPE.ATMS_TEST_TYPE_ID

             AND B_USER_INFO.B_USER_INFO_ID = J_FP_INVESTIGATOR.INVESTIGATOR_ID

             AND B_FP_TEST.STATUS_ID = L_TEST_STATUS.STATUS_ID

             AND B_FP_TEST.IS_DELETED = :"SYS_B_00"

             AND B_FP_TEST.TEST_NUM NOT IN (:"SYS_B_01", :"SYS_B_02", :"SYS_B_03")

             AND L_ATMS_TEST_TYPE.IS_DELETED = :"SYS_B_04"

             AND J_FP_INVESTIGATOR.is_pi = :"SYS_B_05"

             AND L_TEST_STATUS.STATUS IN (:"SYS_B_06", :"SYS_B_07", :"SYS_B_08")

             AND j_op_test_anml.test_id = B_FP_TEST.TEST_ID

       

      .

      .

      ----- End of information for the OTHER waiting sessions -----

      .

      .

       

      *** 2013-11-04 23:18:57.916

      dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

      ----- Error Stack Dump -----

      ORA-00060: deadlock detected while waiting for resource

      ORA-10387: parallel query server interrupt (normal)

      ----- SQL Statement (None) -----

      Current SQL information unavailable - no cursor.

       

       

      ----- Call Stack Trace -----

      calling              call     entry                argument values in hex

      location             type     point                (? means dubious value)

       

      More......

      .

      .

      .

      .

      Query:

       

      SELECT DISTINCT B_FP_TEST.TEST_ID

        FROM B_FP_TEST,

             B_USER_INFO,

             J_FP_INVESTIGATOR,

             L_TEST_STATUS,

             L_ATMS_TEST_TYPE,

             j_op_test_anml

      WHERE     B_FP_TEST.TEST_ID = J_FP_INVESTIGATOR.TEST_ID

             AND B_FP_TEST.TEST_TYPE_ID = L_ATMS_TEST_TYPE.ATMS_TEST_TYPE_ID

             AND B_USER_INFO.B_USER_INFO_ID = J_FP_INVESTIGATOR.INVESTIGATOR_ID

             AND B_FP_TEST.STATUS_ID = L_TEST_STATUS.STATUS_ID

             AND B_FP_TEST.IS_DELETED = 0

             AND B_FP_TEST.TEST_NUM NOT IN (1, 2, 99)

             AND L_ATMS_TEST_TYPE.IS_DELETED = 0

             AND J_FP_INVESTIGATOR.is_pi = 1

             AND L_TEST_STATUS.STATUS IN ('Scheduled', 'In-Progress', 'Completed')

             AND j_op_test_anml.test_id = B_FP_TEST.TEST_ID

             AND (   (j_op_test_anml.end_date BETWEEN TO_DATE ('28-Oct-2013') - 1

                                                  AND TO_DATE ('04-Nov-2013') + 1)

                  OR (j_op_test_anml.start_date BETWEEN TO_DATE ('28-Oct-2013') - 1

                                                    AND TO_DATE ('04-Nov-2013') + 1)

                  OR (TO_DATE ('28-Oct-2013') BETWEEN j_op_test_anml.start_date

                                                  AND j_op_test_anml.end_date)

                  OR (TO_DATE ('04-Nov-2013') BETWEEN j_op_test_anml.start_date

                                                  AND j_op_test_anml.end_date))

             AND L_ATMS_TEST_TYPE.IS_DELETED = 0

             AND B_FP_TEST.DATASOURCE_ID = 9

      .

      .

      .

      Query Exp plan:

      Plan hash value: 3398228788

       

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                                   |                     |  1501 |   102K|  1929   (1)| 00:00:24 |       |       |        |      |            |

      |   1 |  HASH UNIQUE                                       |                     |  1501 |   102K|  1929   (1)| 00:00:24 |       |       |        |      |            |

      |   2 |   CONCATENATION                                    |                     |       |       |            |          |       |       |        |      |            |

      |   3 |    PX COORDINATOR                                  |                     |       |       |            |          |       |       |        |      |            |

      |   4 |     PX SEND QC (RANDOM)                            | :TQ30005            |   241 | 16870 |   800   (1)| 00:00:10 |       |       |  Q3,05 | P->S | QC (RAND)  |

      |*  5 |      HASH JOIN                                     |                     |   241 | 16870 |   800   (1)| 00:00:10 |       |       |  Q3,05 | PCWP |            |

      |   6 |       PX RECEIVE                                   |                     |   246 | 15990 |   797   (1)| 00:00:10 |       |       |  Q3,05 | PCWP |            |

      |   7 |        PX SEND HASH                                | :TQ30004            |   246 | 15990 |   797   (1)| 00:00:10 |       |       |  Q3,04 | P->P | HASH       |

      |*  8 |         HASH JOIN                                  |                     |   246 | 15990 |   797   (1)| 00:00:10 |       |       |  Q3,04 | PCWP |            |

      |   9 |          PX RECEIVE                                |                     |   573 | 29223 |   793   (1)| 00:00:10 |       |       |  Q3,04 | PCWP |            |

      |  10 |           PX SEND HASH                             | :TQ30003            |   573 | 29223 |   793   (1)| 00:00:10 |       |       |  Q3,03 | P->P | HASH       |

      |* 11 |            HASH JOIN                               |                     |   573 | 29223 |   793   (1)| 00:00:10 |       |       |  Q3,03 | PCWP |            |

      |  12 |             BUFFER SORT                            |                     |       |       |            |          |       |       |  Q3,03 | PCWC |            |

      |  13 |              PX RECEIVE                            |                     |       |       |            |          |       |       |  Q3,03 | PCWP |            |

      |  14 |               PX SEND BROADCAST                    | :TQ30000            |       |       |            |          |       |       |        | S->P | BROADCAST  |

      |  15 |                NESTED LOOPS                        |                     |       |       |            |          |       |       |        |      |            |

      |  16 |                 NESTED LOOPS                       |                     |   485 | 20855 |   781   (0)| 00:00:10 |       |       |        |      |            |

      |  17 |                  TABLE ACCESS BY GLOBAL INDEX ROWID| J_OP_TEST_ANML      |   485 | 10185 |   296   (0)| 00:00:04 | ROWID | ROWID |        |      |            |

      |* 18 |                   INDEX RANGE SCAN                 | IDX$$_2D190001      |   485 |       |     4   (0)| 00:00:01 |       |       |        |      |            |

      |* 19 |                  INDEX UNIQUE SCAN                 | FT_TEST_ID_PK       |     1 |       |     0   (0)| 00:00:01 |       |       |        |      |            |

      |* 20 |                 TABLE ACCESS BY GLOBAL INDEX ROWID | B_FP_TEST           |     1 |    22 |     1   (0)| 00:00:01 | ROWID | ROWID |        |      |            |

      |  21 |             PX BLOCK ITERATOR                      |                     | 70382 |   549K|    11   (0)| 00:00:01 |       |       |  Q3,03 | PCWC |            |

      |* 22 |              TABLE ACCESS FULL                     | J_FP_INVESTIGATOR   | 70382 |   549K|    11   (0)| 00:00:01 |       |       |  Q3,03 | PCWP |            |

      |  23 |          BUFFER SORT                               |                     |       |       |            |          |       |       |  Q3,04 | PCWC |            |

      |  24 |           PX RECEIVE                               |                     |     3 |    42 |     3   (0)| 00:00:01 |       |       |  Q3,04 | PCWP |            |

      |  25 |            PX SEND HASH                            | :TQ30001            |     3 |    42 |     3   (0)| 00:00:01 |       |       |        | S->P | HASH       |

      |* 26 |             TABLE ACCESS FULL                      | L_TEST_STATUS       |     3 |    42 |     3   (0)| 00:00:01 |       |       |        |      |            |

      |  27 |       BUFFER SORT                                  |                     |       |       |            |          |       |       |  Q3,05 | PCWC |            |

      |  28 |        PX RECEIVE                                  |                     |    30 |   150 |     3   (0)| 00:00:01 |       |       |  Q3,05 | PCWP |            |

      |  29 |         PX SEND HASH                               | :TQ30002            |    30 |   150 |     3   (0)| 00:00:01 |       |       |        | S->P | HASH       |

      |* 30 |          TABLE ACCESS FULL                         | L_ATMS_TEST_TYPE    |    30 |   150 |     3   (0)| 00:00:01 |       |       |        |      |            |

      |  31 |    NESTED LOOPS                                    |                     |       |       |            |          |       |       |        |      |            |

      |  32 |     NESTED LOOPS                                   |                     |     3 |   210 |   329   (1)| 00:00:04 |       |       |        |      |            |

      |  33 |      NESTED LOOPS                                  |                     |     3 |   195 |   329   (1)| 00:00:04 |       |       |        |      |            |

      |* 34 |       HASH JOIN                                    |                     |     2 |   114 |   325   (1)| 00:00:04 |       |       |        |      |            |

      |  35 |        NESTED LOOPS                                |                     |       |       |            |          |       |       |        |      |            |

      |  36 |         NESTED LOOPS                               |                     |     6 |   258 |   322   (1)| 00:00:04 |       |       |        |      |            |

      |  37 |          PARTITION RANGE SINGLE                    |                     |     6 |   126 |   316   (1)| 00:00:04 |     7 |     7 |        |      |            |

      |* 38 |           TABLE ACCESS FULL                        | J_OP_TEST_ANML      |     6 |   126 |   316   (1)| 00:00:04 |     7 |     7 |        |      |            |

      |* 39 |          INDEX UNIQUE SCAN                         | FT_TEST_ID_PK       |     1 |       |     0   (0)| 00:00:01 |       |       |        |      |            |

      |* 40 |         TABLE ACCESS BY GLOBAL INDEX ROWID         | B_FP_TEST           |     1 |    22 |     1   (0)| 00:00:01 | ROWID | ROWID |        |      |            |

      |* 41 |        TABLE ACCESS FULL                           | L_TEST_STATUS       |     3 |    42 |     3   (0)| 00:00:01 |       |       |        |      |            |

      |* 42 |       TABLE ACCESS BY INDEX ROWID                  | J_FP_INVESTIGATOR   |     1 |     8 |     2   (0)| 00:00:01 |       |       |        |      |            |

      |* 43 |        INDEX RANGE SCAN                            | FI_TEST_ID_PK       |     1 |       |     1   (0)| 00:00:01 |       |       |        |      |            |

      |* 44 |      INDEX UNIQUE SCAN                             | L_ATMS_TEST_TYPE_PK |     1 |       |     0   (0)| 00:00:01 |       |       |        |      |            |

      |* 45 |     TABLE ACCESS BY INDEX ROWID                    | L_ATMS_TEST_TYPE    |     1 |     5 |     1   (0)| 00:00:01 |       |       |        |      |            |

      |  46 |    PX COORDINATOR                                  |                     |       |       |            |          |       |       |        |      |            |

      |  47 |     PX SEND QC (RANDOM)                            | :TQ20003            |       |       |            |          |       |       |  Q2,03 | P->S | QC (RAND)  |

      |  48 |      NESTED LOOPS                                  |                     |       |       |            |          |       |       |  Q2,03 | PCWP |            |

      |  49 |       NESTED LOOPS                                 |                     |    33 |  2310 |   399   (2)| 00:00:05 |       |       |  Q2,03 | PCWP |            |

      |* 50 |        HASH JOIN                                   |                     |    33 |  2145 |   397   (2)| 00:00:05 |       |       |  Q2,03 | PCWP |            |

      |  51 |         PX RECEIVE                                 |                     |    78 |  3978 |   393   (1)| 00:00:05 |       |       |  Q2,03 | PCWP |            |

      |  52 |          PX SEND HASH                              | :TQ20002            |    78 |  3978 |   393   (1)| 00:00:05 |       |       |  Q2,02 | P->P | HASH       |

      |* 53 |           HASH JOIN                                |                     |    78 |  3978 |   393   (1)| 00:00:05 |       |       |  Q2,02 | PCWP |            |

      |  54 |            BUFFER SORT                             |                     |       |       |            |          |       |       |  Q2,02 | PCWC |            |

      |  55 |             PX RECEIVE                             |                     |       |       |            |          |       |       |  Q2,02 | PCWP |            |

      |  56 |              PX SEND BROADCAST                     | :TQ20000            |       |       |            |          |       |       |        | S->P | BROADCAST  |

      |  57 |               NESTED LOOPS                         |                     |       |       |            |          |       |       |        |      |            |

      |  58 |                NESTED LOOPS                        |                     |    66 |  2838 |   382   (1)| 00:00:05 |       |       |        |      |            |

      |  59 |                 PARTITION RANGE SINGLE             |                     |    66 |  1386 |   316   (1)| 00:00:04 |     7 |     7 |        |      |            |

      |* 60 |                  TABLE ACCESS FULL                 | J_OP_TEST_ANML      |    66 |  1386 |   316   (1)| 00:00:04 |     7 |     7 |        |      |            |

      |* 61 |                 INDEX UNIQUE SCAN                  | FT_TEST_ID_PK       |     1 |       |     0   (0)| 00:00:01 |       |       |        |      |            |

      |* 62 |                TABLE ACCESS BY GLOBAL INDEX ROWID  | B_FP_TEST           |     1 |    22 |     1   (0)| 00:00:01 | ROWID | ROWID |        |      |            |

      |  63 |            PX BLOCK ITERATOR                       |                     | 70382 |   549K|    11   (0)| 00:00:01 |       |       |  Q2,02 | PCWC |            |

      |* 64 |             TABLE ACCESS FULL                      | J_FP_INVESTIGATOR   | 70382 |   549K|    11   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |

      |  65 |         BUFFER SORT                                |                     |       |       |            |          |       |       |  Q2,03 | PCWC |            |

      |  66 |          PX RECEIVE                                |                     |     3 |    42 |     3   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |

      |  67 |           PX SEND HASH                             | :TQ20001            |     3 |    42 |     3   (0)| 00:00:01 |       |       |        | S->P | HASH       |

      |* 68 |            TABLE ACCESS FULL                       | L_TEST_STATUS       |     3 |    42 |     3   (0)| 00:00:01 |       |       |        |      |            |

      |* 69 |        INDEX UNIQUE SCAN                           | L_ATMS_TEST_TYPE_PK |     1 |       |     0   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |

      |* 70 |       TABLE ACCESS BY INDEX ROWID                  | L_ATMS_TEST_TYPE    |     1 |     5 |     1   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |

      |  71 |    PX COORDINATOR                                  |                     |       |       |            |          |       |       |        |      |            |

      |  72 |     PX SEND QC (RANDOM)                            | :TQ10003            |       |       |            |          |       |       |  Q1,03 | P->S | QC (RAND)  |

      |  73 |      NESTED LOOPS                                  |                     |       |       |            |          |       |       |  Q1,03 | PCWP |            |

      |  74 |       NESTED LOOPS                                 |                     |    33 |  2310 |   399   (2)| 00:00:05 |       |       |  Q1,03 | PCWP |            |

      |* 75 |        HASH JOIN                                   |                     |    34 |  2210 |   397   (2)| 00:00:05 |       |       |  Q1,03 | PCWP |            |

      |  76 |         PX RECEIVE                                 |                     |    78 |  3978 |   393   (1)| 00:00:05 |       |       |  Q1,03 | PCWP |            |

      |  77 |          PX SEND HASH                              | :TQ10002            |    78 |  3978 |   393   (1)| 00:00:05 |       |       |  Q1,02 | P->P | HASH       |

      |* 78 |           HASH JOIN                                |                     |    78 |  3978 |   393   (1)| 00:00:05 |       |       |  Q1,02 | PCWP |            |

      |  79 |            BUFFER SORT                             |                     |       |       |            |          |       |       |  Q1,02 | PCWC |            |

      |  80 |             PX RECEIVE                             |                     |       |       |            |          |       |       |  Q1,02 | PCWP |            |

      |  81 |              PX SEND BROADCAST                     | :TQ10000            |       |       |            |          |       |       |        | S->P | BROADCAST  |

      |  82 |               NESTED LOOPS                         |                     |       |       |            |          |       |       |        |      |            |

      |  83 |                NESTED LOOPS                        |                     |    66 |  2838 |   382   (1)| 00:00:05 |       |       |        |      |            |

      |  84 |                 PARTITION RANGE SINGLE             |                     |    66 |  1386 |   316   (1)| 00:00:04 |     7 |     7 |        |      |            |

      |* 85 |                  TABLE ACCESS FULL                 | J_OP_TEST_ANML      |    66 |  1386 |   316   (1)| 00:00:04 |     7 |     7 |        |      |            |

      |* 86 |                 INDEX UNIQUE SCAN                  | FT_TEST_ID_PK       |     1 |       |     0   (0)| 00:00:01 |       |       |        |      |            |

      |* 87 |                TABLE ACCESS BY GLOBAL INDEX ROWID  | B_FP_TEST           |     1 |    22 |     1   (0)| 00:00:01 | ROWID | ROWID |        |      |            |

      |  88 |            PX BLOCK ITERATOR                       |                     | 70382 |   549K|    11   (0)| 00:00:01 |       |       |  Q1,02 | PCWC |            |

      |* 89 |             TABLE ACCESS FULL                      | J_FP_INVESTIGATOR   | 70382 |   549K|    11   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

      |  90 |         BUFFER SORT                                |                     |       |       |            |          |       |       |  Q1,03 | PCWC |            |

      |  91 |          PX RECEIVE                                |                     |     3 |    42 |     3   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |  92 |           PX SEND HASH                             | :TQ10001            |     3 |    42 |     3   (0)| 00:00:01 |       |       |        | S->P | HASH       |

      |* 93 |            TABLE ACCESS FULL                       | L_TEST_STATUS       |     3 |    42 |     3   (0)| 00:00:01 |       |       |        |      |            |

      |* 94 |        INDEX UNIQUE SCAN                           | L_ATMS_TEST_TYPE_PK |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |* 95 |       TABLE ACCESS BY INDEX ROWID                  | L_ATMS_TEST_TYPE    |     1 |     5 |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         5 - access("B_FP_TEST"."TEST_TYPE_ID"="L_ATMS_TEST_TYPE"."ATMS_TEST_TYPE_ID")

         8 - access("B_FP_TEST"."STATUS_ID"="L_TEST_STATUS"."STATUS_ID")

        11 - access("B_FP_TEST"."TEST_ID"="J_FP_INVESTIGATOR"."TEST_ID")

        18 - access("J_OP_TEST_ANML"."START_DATE">=TO_DATE(' 2013-10-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-11-05

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

        19 - access("J_OP_TEST_ANML"."TEST_ID"="B_FP_TEST"."TEST_ID")

        20 - filter("B_FP_TEST"."DATASOURCE_ID"=9 AND "B_FP_TEST"."IS_DELETED"=0 AND "B_FP_TEST"."TEST_NUM"<>1 AND "B_FP_TEST"."TEST_NUM"<>2 AND

                    "B_FP_TEST"."TEST_NUM"<>99)

        22 - filter("J_FP_INVESTIGATOR"."IS_PI"=1)

        26 - filter("L_TEST_STATUS"."STATUS"='Completed' OR "L_TEST_STATUS"."STATUS"='In-Progress' OR "L_TEST_STATUS"."STATUS"='Scheduled')

        30 - filter("L_ATMS_TEST_TYPE"."IS_DELETED"=0)

        34 - access("B_FP_TEST"."STATUS_ID"="L_TEST_STATUS"."STATUS_ID")

        38 - filter("J_OP_TEST_ANML"."END_DATE">=TO_DATE(' 2013-10-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J_OP_TEST_ANML"."END_DATE"<=TO_DATE(' 2013-11-05

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND (LNNVL("J_OP_TEST_ANML"."START_DATE">=TO_DATE(' 2013-10-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR

                    LNNVL("J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-11-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))

        39 - access("J_OP_TEST_ANML"."TEST_ID"="B_FP_TEST"."TEST_ID")

        40 - filter("B_FP_TEST"."DATASOURCE_ID"=9 AND "B_FP_TEST"."IS_DELETED"=0 AND "B_FP_TEST"."TEST_NUM"<>1 AND "B_FP_TEST"."TEST_NUM"<>2 AND

                    "B_FP_TEST"."TEST_NUM"<>99)

        41 - filter("L_TEST_STATUS"."STATUS"='Completed' OR "L_TEST_STATUS"."STATUS"='In-Progress' OR "L_TEST_STATUS"."STATUS"='Scheduled')

        42 - filter("J_FP_INVESTIGATOR"."IS_PI"=1)

        43 - access("B_FP_TEST"."TEST_ID"="J_FP_INVESTIGATOR"."TEST_ID")

        44 - access("B_FP_TEST"."TEST_TYPE_ID"="L_ATMS_TEST_TYPE"."ATMS_TEST_TYPE_ID")

        45 - filter("L_ATMS_TEST_TYPE"."IS_DELETED"=0)

        50 - access("B_FP_TEST"."STATUS_ID"="L_TEST_STATUS"."STATUS_ID")

        53 - access("B_FP_TEST"."TEST_ID"="J_FP_INVESTIGATOR"."TEST_ID")

        60 - filter("J_OP_TEST_ANML"."END_DATE">=TO_DATE(' 2013-11-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-11-04

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND (LNNVL("J_OP_TEST_ANML"."END_DATE">=TO_DATE(' 2013-10-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR

                    LNNVL("J_OP_TEST_ANML"."END_DATE"<=TO_DATE(' 2013-11-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND (LNNVL("J_OP_TEST_ANML"."START_DATE">=TO_DATE(' 2013-10-27

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR LNNVL("J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-11-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))

        61 - access("J_OP_TEST_ANML"."TEST_ID"="B_FP_TEST"."TEST_ID")

        62 - filter("B_FP_TEST"."DATASOURCE_ID"=9 AND "B_FP_TEST"."IS_DELETED"=0 AND "B_FP_TEST"."TEST_NUM"<>1 AND "B_FP_TEST"."TEST_NUM"<>2 AND

                    "B_FP_TEST"."TEST_NUM"<>99)

        64 - filter("J_FP_INVESTIGATOR"."IS_PI"=1)

        68 - filter("L_TEST_STATUS"."STATUS"='Completed' OR "L_TEST_STATUS"."STATUS"='In-Progress' OR "L_TEST_STATUS"."STATUS"='Scheduled')

        69 - access("B_FP_TEST"."TEST_TYPE_ID"="L_ATMS_TEST_TYPE"."ATMS_TEST_TYPE_ID")

        70 - filter("L_ATMS_TEST_TYPE"."IS_DELETED"=0)

        75 - access("B_FP_TEST"."STATUS_ID"="L_TEST_STATUS"."STATUS_ID")

        78 - access("B_FP_TEST"."TEST_ID"="J_FP_INVESTIGATOR"."TEST_ID")

        85 - filter("J_OP_TEST_ANML"."END_DATE">=TO_DATE(' 2013-10-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-10-28

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND (LNNVL("J_OP_TEST_ANML"."END_DATE">=TO_DATE(' 2013-11-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR

                    LNNVL("J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-11-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND (LNNVL("J_OP_TEST_ANML"."END_DATE">=TO_DATE(' 2013-10-27

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR LNNVL("J_OP_TEST_ANML"."END_DATE"<=TO_DATE(' 2013-11-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND

                    (LNNVL("J_OP_TEST_ANML"."START_DATE">=TO_DATE(' 2013-10-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR LNNVL("J_OP_TEST_ANML"."START_DATE"<=TO_DATE(' 2013-11-05

                    00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))

        86 - access("J_OP_TEST_ANML"."TEST_ID"="B_FP_TEST"."TEST_ID")

        87 - filter("B_FP_TEST"."DATASOURCE_ID"=9 AND "B_FP_TEST"."IS_DELETED"=0 AND "B_FP_TEST"."TEST_NUM"<>1 AND "B_FP_TEST"."TEST_NUM"<>2 AND

                    "B_FP_TEST"."TEST_NUM"<>99)

        89 - filter("J_FP_INVESTIGATOR"."IS_PI"=1)

        93 - filter("L_TEST_STATUS"."STATUS"='Completed' OR "L_TEST_STATUS"."STATUS"='In-Progress' OR "L_TEST_STATUS"."STATUS"='Scheduled')

        94 - access("B_FP_TEST"."TEST_TYPE_ID"="L_ATMS_TEST_TYPE"."ATMS_TEST_TYPE_ID")

        95 - filter("L_ATMS_TEST_TYPE"."IS_DELETED"=0)