title: How to tune this query in Oracle 11g
body: <p>I have been asked to tune the below query and would like to know if there is any better way to tune it?</p>
  
  <pre><code>SELECT req_dtl.lab_ord_occ_test_id ,
         req_dtl.order_ref_no ,
         req_dtl.accession_no ,
         req_dtl.test_code ,
         req_dtl.test_name ,
         req_dtl.test_id ,
         req_dtl.schedule_id ,
         req_dtl.lab_ord_occ_id ,
         req_dtl.order_type ,
         lab_occ.facility_id ,
         lab_occ.patient_id ,
         lab_occ.order_draw_dt ,
         hdr.source_system ,
        (SELECT CORPORATION_ACRONYM
         FROM corporation c,
         facility f
         WHERE c.corporation_id = f.corporation_id
         AND f.facility_id      = lab_occ.facility_id) AS corporation_acronym,
         tst.container ,
         lab_occ.order_duration_type ,
         occ_test.mnc_yn
   FROM ORDER_REQUISITION_HEADER hdr ,
        ORDER_REQUISITION_DETAIL req_dtl ,
        LAB_ORDER_OCC_TEST occ_test ,
        LAB_ORDER_OCC lab_occ ,
        TEST tst
   WHERE hdr.requisition_hdr_id       = in_requisition_hdr_id
   AND hdr.msg_sent_to_lab_yn         = 'Y'
   AND req_dtl.requisition_hdr_id     = hdr.requisition_hdr_id
   AND occ_test.lab_order_occ_test_id = req_dtl.lab_ord_occ_test_id
   AND req_dtl.test_id                = tst.test_id
   AND tst.accession_type             NOT LIKE 'CMP%'
   AND occ_test.status                      != 'R'
   AND occ_test.lab_order_occ_id             = lab_occ.lab_order_occ_id
   AND lab_occ.status                        = 'A'
   AND occ_test.created_dt                  &gt;= hdr.msg_sent_to_lab_dt
   AND NVL(occ_test.test_sent_to_lab_yn,'N') = 'N'
   AND NOT EXISTS
                (SELECT orddata.*
                 FROM MISSING_ORDER_DATA orddata,
                      TEST_CONFIG_HOLD_AOE tcha
                 WHERE orddata.test_id             = tcha.test_id
                 AND tcha.active_yn                = 'Y'
                 AND orddata.status_flag           = 'A'
                 AND orddata.answer               IS NULL
                 AND orddata.msg_sent_to_lab_yn    = 'N'
                 AND   orddata.lab_order_occ_test_id=occ_test.lab_order_occ_test_id
     )
     ORDER BY req_dtl.accession_no;
  </code></pre>
  
  <p>In the execution plan no tables are going for full table scan.Only nested loops are more.
  <strong>*Suggest better way to tune this query *</strong></p>