Hi All,
We are looking at a SQL as part of an ETL extraction process (source is an EBS database). The query performance is not terrible ... relatively speaking (we've had much longer queries to contend with in this ETL), but it does sometimes take over 10 minutes, even up to 20 minutes+. We'd like to improve it, if possible. Here is an execution plan, with estimated & actual numbers, using the /*+ GATHER_PLAN_STATISTICS */ hint:
Plan hash value: 905517984
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | | 762K(100)| 258K|00:07:49.26 | 6862K| 13 | | | |
| 1 | UNION-ALL | | 1 | | | | | 258K|00:07:49.26 | 6862K| 13 | | | |
| 2 | SORT GROUP BY | | 1 | 1291K| 101M| 134M| 27044 (1)| 256K|00:07:48.93 | 4758 | 12 | 29M| 1966K| 26M (0)|
|* 3 | HASH JOIN | | 1 | 1291K| 101M| 2280K| 1892 (6)| 256K|00:07:35.48 | 4758 | 12 | 4989K| 1857K| 8094K (0)|
|* 4 | TABLE ACCESS FULL | FND_FLEX_VALUES | 1 | 68625 | 1474K| | 848 (2)| 70187 |00:00:00.07 | 2926 | 12 | | | |
|* 5 | TABLE ACCESS FULL | FND_FLEX_VALUE_NORM_HIERARCHY | 1 | 83975 | 4920K| | 530 (2)| 84250 |00:00:01.02 | 1832 | 0 | | | |
| 6 | SORT GROUP BY | | 1 | 840 | 36960 | 5304K| 735K (1)| 1181 |00:10:49.53 | 6857K| 1 | 160K| 160K| 142K (0)|
|* 7 | FILTER | | 1 | | | | | 9970 |00:15:11.35 | 6857K| 1 | | | |
|* 8 | TABLE ACCESS FULL | FND_FLEX_VALUE_NORM_HIERARCHY | 1 | 83975 | 3608K| | 530 (2)| 84250 |00:00:00.17 | 1832 | 0 | | | |
| 9 | NESTED LOOPS | | 20130 | 1 | 86 | | 168 (0)| 17714 |00:10:48.41 | 6855K| 1 | | | |
| 10 | NESTED LOOPS | | 20130 | 1 | 59 | | 7 (0)| 20069 |00:00:02.42 | 190K| 1 | | | |
| 11 | NESTED LOOPS | | 20130 | 1 | 45 | | 6 (0)| 20069 |00:00:02.00 | 132K| 1 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUE_SETS | 20130 | 1 | 17 | | 2 (0)| 20130 |00:00:00.76 | 43126 | 0 | | | |
|* 13 | INDEX UNIQUE SCAN | FND_FLEX_VALUE_SETS_U1 | 20130 | 1 | | | 1 (0)| 20130 |00:00:00.39 | 22996 | 0 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUES | 20130 | 1 | 28 | | 4 (0)| 20069 |00:00:01.19 | 89176 | 1 | | | |
|* 15 | INDEX RANGE SCAN | FND_FLEX_VALUES_N1 | 20130 | 2 | | | 2 (0)| 30378 |00:00:00.57 | 58798 | 1 | | | |
|* 16 | INDEX UNIQUE SCAN | FND_FLEX_VALUES_TL_U1 | 20069 | 1 | 14 | | 1 (0)| 20069 |00:00:00.37 | 57989 | 0 | | | |
|* 17 | INDEX RANGE SCAN | FND_FLEX_VALUE_NORM_HIER_U1 | 20069 | 53 | 1431 | | 161 (0)| 17714 |00:10:45.82 | 6665K| 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
3 - access("FLEX_VALUE_SET_ID"="FLEX_VALUE_SET_ID")
filter(("FLEX_VALUE">="CHILD_FLEX_VALUE_LOW" AND "FLEX_VALUE"<="CHILD_FLEX_VALUE_HIGH" AND (("SUMMARY_FLAG"='Y' AND "RANGE_ATTRIBUTE"='P') OR ("SUMMARY_FLAG"='N' AND
"RANGE_ATTRIBUTE"='C'))))
4 - filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2' AND INTERNAL_FUNCTION("SUMMARY_FLAG")))
5 - filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2' AND INTERNAL_FUNCTION("RANGE_ATTRIBUTE")))
7 - filter( IS NULL)
8 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2')
13 - access("FLEX_VALUE_SET_ID"=:B1 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2')
14 - filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2' AND INTERNAL_FUNCTION("SUMMARY_FLAG")))
15 - access("FLEX_VALUE_SET_ID"="FLEX_VALUE_SET_ID" AND "FLEX_VALUE"=:B1)
filter("FLEX_VALUE_SET_ID"=:B1)
16 - access("FLEX_VALUE_ID"="FLEX_VALUE_ID" AND "LANGUAGE"=USERENV('LANG') AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2')
17 - access("FLEX_VALUE_SET_ID"=:B1 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2')
filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2' AND INTERNAL_FUNCTION("RANGE_ATTRIBUTE") AND "FLEX_VALUE_SET_ID"="FLEX_VALUE_SET_ID" AND (("FORMAT_TYPE"='N' AND
"FND_NUMBER"."CANONICAL_TO_NUMBER"("FLEX_VALUE")>="FND_NUMBER"."CANONICAL_TO_NUMBER"("CHILD_FLEX_VALUE_LOW") AND
"FND_NUMBER"."CANONICAL_TO_NUMBER"("FLEX_VALUE")<="FND_NUMBER"."CANONICAL_TO_NUMBER"("CHILD_FLEX_VALUE_HIGH")) OR (INTERNAL_FUNCTION("FORMAT_TYPE") AND
TO_DATE("FLEX_VALUE",DECODE("MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'DD-MON-YYYY',15,'DD-MON-RR HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR
HH24:MI:SS',20,'DD-MON-YYYY HH24:MI:SS'))>=TO_DATE("CHILD_FLEX_VALUE_LOW",DECODE("MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'DD-MON-YYYY',15,'DD-MON-RR
HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR HH24:MI:SS',20,'DD-MON-YYYY HH24:MI:SS')) AND
TO_DATE("FLEX_VALUE",DECODE("MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'DD-MON-YYYY',15,'DD-MON-RR HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR
HH24:MI:SS',20,'DD-MON-YYYY HH24:MI:SS'))<=TO_DATE("CHILD_FLEX_VALUE_HIGH",DECODE("MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'DD-MON-YYYY',15,'DD-MON-RR
HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR HH24:MI:SS',20,'DD-MON-YYYY HH24:MI:SS'))) OR ("FORMAT_TYPE"<>'N' AND "FORMAT_TYPE"<>'D' AND "FORMAT_TYPE"<>'T' AND
"FLEX_VALUE">="CHILD_FLEX_VALUE_LOW" AND "FLEX_VALUE"<="CHILD_FLEX_VALUE_HIGH")) AND (("SUMMARY_FLAG"='Y' AND "RANGE_ATTRIBUTE"='P') OR ("SUMMARY_FLAG"='N' AND
"RANGE_ATTRIBUTE"='C'))))
We can see that there is a fairly large discrepancy between estimated and actual rows within the nested loops. They look like indexes. By the way ... there is a 'nested loop' hint in the code within the where clause - /*+ NL_AJ */.
Does anyone know what may be causing the estimated and actuals to differ so much? And, if there may be an opportunity to improve this?
Thanks for any input or suggestions.
DB Version 11.2.0.4
Regards,
Charles
Message was edited by: Charles M
Updated some formatting on the plan/output, making it easier to read.