How to tune this query in Oracle 11g

Question

I have been asked to tune the below query and would like to know if there is any better way to tune it?

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                  >= 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;

In the execution plan no tables are going for full table scan.Only nested loops are more. *Suggest better way to tune this query *

Questions seeking debugging help ("why isn't this code working? ot why my code is slow") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. — TheGameiswar, Aug 03 '17 at 11:18
see this on how to create a repro :https://stackoverflow.com/help/mcve or this one,which is more suitable for tsql :https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Aug 03 '17 at 11:18
Tip of today: Switch to modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read an maintain, and easier to convert to outer join if needed! — jarlh, Aug 03 '17 at 11:19

score 0 · Answer 1 · answered Aug 03 '17 at 11:25

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
          )

could be moved to FROM

FROM 
   ... 
      LEFT JOIN (SELECT DISTINCT orddata.lab_order_occ_test_id
           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'
          ) missing ON missing.lab_order_occ_test_id = occ_test.lab_order_occ_test_id
WHERE missing.lab_order_occ_test_id IS NULL

Also you should move the acronym

FROM
...
INNER JOIN (SELECT CORPORATION_ACRONYM, f.facility_id
   FROM corporation c,
   facility f
   WHERE c.corporation_id = f.corporation_id) acr ON 
   acr.facility_id      = lab_occ.facility_id)

Thanks!! I tried this approach but the cost is little more after adding this change — kushi, Aug 07 '17 at 07:20

score 0 · Answer 2 · answered Aug 03 '17 at 20:07

...Additionally, the TEST object must have an index on accession_type otherwise the tst.accession_type not like 'CMP%' clause will be slower than necessary.

Also, the clause: NVL(occ_test.test_sent_to_lab_yn,'N') = 'N' is essentially an outer join on partially-validated data. Does the test_sent_to_lab_yn column in occ_test contain nulls? If not, consider using an IN clause along with a valid list. [it looks like a yes/no column, maybe this should be equality on 'Y' and get someone to clean up the nulls?]

Please post the explain plan so we can suggest a re-ordering of the predicates in order to minimize the row-returns in first clause....and make HINT suggestions.

How to tune this query in Oracle 11g

2 Answers2