Hi Everyone,
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
I've a query which is picking up every possible index and the tables are properly analyzed as well, but when the nested loop join is happening, its generating too much of cost.
Please do have a look at the query and the plan, and help me out. I don't know what needs to be done here?
Query:
--SET AUTOTRACE ON EXPLAIN;
SELECT DISTINCT P.LAST_NAME
|| ', '
|| P.FIRST_NAME
|| NVL2 (P.MIDDLE_NAME,
' ' || P.MIDDLE_NAME,
P.MIDDLE_NAME
)
|| (SELECT ', ' || LOV_VALUE AS LOV_VALUE1
FROM LIST_OF_VALUE LOV
WHERE LOV.LOV_ID = P.SUFFIX_LID)
AS PATIENT_NAME,
P.EXTERNAL_ID,
P.SPECTRA_MRN,
ORH.REQUISITION_NUMBER AS REQUISITION_NUMBER,
F.FACILITY_NAME,
F.FACILITY_ID,
FAD.ACCOUNT_TYPE AS MODALITY,
FAD.ACCOUNT_NUMBER,
FAD.HLAB_NUM,
LOC.ORDER_DRAW_DT AS DRAW_DT,
C.CORPORATION_NAME AS CORPORATION_NAME,
C.CORPORATION_ACRONYM AS ACRONYM,
LOC.PATIENT_ID
FROM LAB_ORDER_OCC LOC,
LAB_ORDER_OCC_TEST LOCT,
FACILITY F,
CORPORATION C,
ORDER_REQUISITION_HEADER ORH,
ORDER_REQUISITION_DETAIL ORD,
PATIENT P,
PATIENT_FACILITY_MODALITY PFM,
FACILITY_ACCOUNT_MODALITY FAM,
FACILITY_ACCOUNT_DETAIL FAD
WHERE P.PATIENT_ID = LOC.PATIENT_ID
AND P.PATIENT_ID = PFM.PATIENT_ID
AND PFM.FACILITY_ACCOUNT_MODALITY_ID = FAM.FACILITY_ACCOUNT_MODALITY_ID
AND FAM.FACILITY_ACCOUNT_DETAIL_ID = FAD.FACILITY_ACCOUNT_DETAIL_ID
AND PFM.FACILITY_ID = LOC.FACILITY_ID
AND LOC.LAB_ORDER_OCC_ID = LOCT.LAB_ORDER_OCC_ID
AND LOC.FACILITY_ID = F.FACILITY_ID
AND F.CORPORATION_ID = C.CORPORATION_ID
AND ORH.PATIENT_ID = LOC.PATIENT_ID
AND ORH.FACILITY_ID = LOC.FACILITY_ID
AND ORH.DRAW_DT = LOC.ORDER_DRAW_DT
AND ORH.REQUISITION_HDR_ID = ORD.REQUISITION_HDR_ID
AND ORH.ORDER_TYPE = 'P'
AND LOC.ORDER_DRAW_DT BETWEEN TRUNC (SYSDATE) - 7 AND TRUNC (SYSDATE)
AND LOC.STATUS = 'A'
AND LOC.MSG_SENT_TO_LAB_YN = 'Y'
AND LOCT.STATUS <> 'R';
Plan:
Autotrace Enabled
Displays the execution plan only.
Plan hash value: 4259240673
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 3842 | 4106 (1)| 00:00:50 |
| 1 | TABLE ACCESS BY INDEX ROWID | LIST_OF_VALUE | 1 | 23 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | LOV_PK | 1 | | 1 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 17 | 3842 | 4106 (1)| 00:00:50 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS | | 17 | 3842 | 4105 (1)| 00:00:50 |
| 6 | NESTED LOOPS | | 2 | 440 | 4099 (1)| 00:00:50 |
| 7 | NESTED LOOPS | | 1 | 212 | 4094 (1)| 00:00:50 |
| 8 | NESTED LOOPS | | 1 | 184 | 4093 (1)| 00:00:50 |
| 9 | NESTED LOOPS | | 1 | 174 | 4092 (1)| 00:00:50 |
| 10 | NESTED LOOPS | | 1 | 134 | 4091 (1)| 00:00:50 |
| 11 | NESTED LOOPS | | 1 | 106 | 4090 (1)| 00:00:50 |
| 12 | NESTED LOOPS | | 6 | 552 | 4078 (1)| 00:00:49 |
|* 13 | HASH JOIN | | 6 | 318 | 4066 (1)| 00:00:49 |
|* 14 | TABLE ACCESS BY INDEX ROWID| LAB_ORDER_OCC | 2555 | 53655 | 1715 (1)| 00:00:21 |
|* 15 | INDEX SKIP SCAN | LAB_ORDER_OCC_IX_07 | 6586 | | 35 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID| ORDER_REQUISITION_HEADER | 15848 | 495K| 2350 (1)| 00:00:29 |
|* 17 | INDEX SKIP SCAN | ORDER_REQUISITION_HEADER_IX_04 | 843 | | 1816 (1)| 00:00:22 |
| 18 | TABLE ACCESS BY INDEX ROWID | PATIENT | 1 | 39 | 2 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PATIENT_PK | 1 | | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PATIENT_FACILITY_MOD_IX_01 | 1 | 14 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | FACILITY | 1 | 28 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | FACILITY_PK | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | CORPORATION | 1 | 40 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | CORPORATION_PK | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | FACILITY_ACCOUNT_MODALITY | 1 | 10 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | FACILITY_ACCOUNT_MODALITY_PK | 1 | | 0 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | FACILITY_ACCOUNT_DETAIL | 1 | 28 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | FACILITY_ACCOUNT_DETAIL_PK | 1 | | 0 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | LAB_ORDER_OCC_TEST | 7 | 56 | 5 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | LAB_ORDER_OCC_TEST_IX_01 | 8 | | 3 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | ORDER_REQUISITION_DETAIL_IX_01 | 7 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOV"."LOV_ID"=:B1)
4 - filter(TRUNC(SYSDATE@!)-7<=TRUNC(SYSDATE@!))
13 - access("ORH"."PATIENT_ID"="LOC"."PATIENT_ID" AND "ORH"."FACILITY_ID"="LOC"."FACILITY_ID" AND
"ORH"."DRAW_DT"="LOC"."ORDER_DRAW_DT")
14 - filter("LOC"."PATIENT_ID" IS NOT NULL AND "LOC"."MSG_SENT_TO_LAB_YN"='Y')
15 - access("LOC"."ORDER_DRAW_DT">=TRUNC(SYSDATE@!)-7 AND "LOC"."STATUS"='A' AND
"LOC"."ORDER_DRAW_DT"<=TRUNC(SYSDATE@!))
filter("LOC"."STATUS"='A')
16 - filter("ORH"."PATIENT_ID" IS NOT NULL)
17 - access("ORH"."DRAW_DT">=TRUNC(SYSDATE@!)-7 AND "ORH"."ORDER_TYPE"='P' AND
"ORH"."DRAW_DT"<=TRUNC(SYSDATE@!))
filter("ORH"."DRAW_DT">=TRUNC(SYSDATE@!)-7 AND "ORH"."ORDER_TYPE"='P' AND
"ORH"."DRAW_DT"<=TRUNC(SYSDATE@!))
19 - access("P"."PATIENT_ID"="LOC"."PATIENT_ID")
20 - access("P"."PATIENT_ID"="PFM"."PATIENT_ID" AND "PFM"."FACILITY_ID"="LOC"."FACILITY_ID")
22 - access("LOC"."FACILITY_ID"="F"."FACILITY_ID")
24 - access("F"."CORPORATION_ID"="C"."CORPORATION_ID")
26 - access("PFM"."FACILITY_ACCOUNT_MODALITY_ID"="FAM"."FACILITY_ACCOUNT_MODALITY_ID")
28 - access("FAM"."FACILITY_ACCOUNT_DETAIL_ID"="FAD"."FACILITY_ACCOUNT_DETAIL_ID")
29 - filter("LOCT"."STATUS"<>'R')
30 - access("LOC"."LAB_ORDER_OCC_ID"="LOCT"."LAB_ORDER_OCC_ID")
31 - access("ORH"."REQUISITION_HDR_ID"="ORD"."REQUISITION_HDR_ID")
>>Query Run In:Query Result
Regards,
BS2012.