I'm using oracle 19c and facing a strange performance issue with a simple query. This is my query:
SELECT
TU.ID AS RecordID
FROM TABLE_U TU
JOIN (SELECT to_date('11-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
UNION ALL SELECT to_date('10-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
) PDATE ON (1=1)
JOIN TABLE_T t ON (t.PROCESSOR = TU.PROCESSOR AND TU.TTD = t.ID AND t.FROMDATE <=PDATE.PROCESS_DATE AND t.TODATE >= PDATE.PROCESS_DATE)
LEFT JOIN TABLE_TD TD ON (TD.PROCESSOR = t.PROCESSOR AND TD.ABC = t.ID)
WHERE t.CREATED <=PDATE.PROCESS_DATE
AND TU.FROMDATE <= PDATE.PROCESS_DATE
AND this is its execution plan
Plan hash value: 1872580574
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3745 | 369K| 35213 (1)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 3745 | 369K| 35213 (1)| 00:00:02 |
| 2 | NESTED LOOPS | | 3745 | 303K| 34957 (1)| 00:00:02 |
| 3 | NESTED LOOPS | | 77970 | 303K| 34957 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 7797 | 388K| 3853 (1)| 00:00:01 |
| 5 | VIEW | | 2 | 18 | 4 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_T | 3899 | 159K| 1924 (1)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | TABLE_T_FROMDATE | 23 | | 1916 (1)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK_TABLE_U | 10 | | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | TABLE_U | 1 | 32 | 4 (0)| 00:00:01 |
| 13 | INDEX STORAGE FAST FULL SCAN | TABLE_TD_IDX | 231K| 4066K| 256 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TD"."PROCESSOR"(+)="T"."PROCESSOR" AND "TD"."ABC"(+)="T"."ID")
10 - access("T"."TODATE">="PDATE"."PROCESS_DATE" AND "T"."FROMDATE"<="PDATE"."PROCESS_DATE" AND
"T"."CREATED"<="PDATE"."PROCESS_DATE")
filter("T"."CREATED"<="PDATE"."PROCESS_DATE" AND "T"."TODATE">="PDATE"."PROCESS_DATE")
11 - access("TU"."TTD"="T"."ID" AND "T"."PROCESSOR"="TU"."PROCESSOR")
filter("T"."PROCESSOR"="TU"."PROCESSOR")
12 - filter("TU"."FROMDATE"<="PDATE"."PROCESS_DATE")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$6
E - USEX_NL
(statistics are up to date) I have two issues with this:
- The query take more than 30 minutes to execute
- I don't understand why oracle is considering the join on TABLE_TD knowing that it is a left joi and it is not sued neither in the selected columns nor in the where condition.
I have forced the a nested loop hint as here:
SELECT /*+ USE_NL(t TD)*/
TU.ID AS RecordID
FROM TABLE_U TU
JOIN (SELECT to_date('11-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
UNION ALL SELECT to_date('10-04-2001','DD-MM-YYYY') AS PROCESS_DATE FROM DUAL
) PDATE ON (1=1)
JOIN TABLE_T t ON (t.PROCESSOR = TU.PROCESSOR AND TU.TTD = t.ID AND t.FROMDATE <=PDATE.PROCESS_DATE AND t.TODATE >= PDATE.PROCESS_DATE)
LEFT JOIN TABLE_TD TD ON (TD.PROCESSOR = t.PROCESSOR AND TD.ABC = t.ID)
WHERE t.CREATED <=PDATE.PROCESS_DATE
AND TU.FROMDATE <= PDATE.PROCESS_DATE
The new plan is more correct and the query execute in less than 1s!!:
Plan hash value: 3532208304
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3745 | 369K| 42448 (1)| 00:00:02 |
| 1 | NESTED LOOPS OUTER | | 3745 | 369K| 42448 (1)| 00:00:02 |
| 2 | NESTED LOOPS | | 3745 | 303K| 34957 (1)| 00:00:02 |
| 3 | NESTED LOOPS | | 7797 | 388K| 3853 (1)| 00:00:01 |
| 4 | VIEW | | 2 | 18 | 4 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_T | 3899 | 159K| 1924 (1)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | TABLE_T_FROMDATE | 23 | | 1916 (1)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE_U | 1 | 32 | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK_TABLE_U | 10 | | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | TABLE_TD_IDX | 1 | 18 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("T"."TODATE">="PDATE"."PROCESS_DATE" AND "T"."FROMDATE"<="PDATE"."PROCESS_DATE" AND
"T"."CREATED"<="PDATE"."PROCESS_DATE")
filter("T"."CREATED"<="PDATE"."PROCESS_DATE" AND "T"."TODATE">="PDATE"."PROCESS_DATE")
10 - filter("TU"."FROMDATE"<="PDATE"."PROCESS_DATE")
11 - access("TU"."TTD"="T"."ID" AND "T"."PROCESSOR"="TU"."PROCESSOR")
filter("T"."PROCESSOR"="TU"."PROCESSOR")
12 - access("TD"."ABC"(+)="T"."ID" AND "TD"."PROCESSOR"(+)="T"."PROCESSOR")
Any explanation why oracle can choose the first execution plan and how can I fix this without forcing hint?
You are using
TDwithout realizing it. Simply by joining to it, you have the possibility of multiplying the row count from your other tables if it should find more than oneTDrow per key (processor,abc). Just because you aren't applying a filter or asking for a column from it in your SELECT clause doesn't mean that join isn't doing anything. Oracle still has to perform the join.Oracle believes that after doing the other joins before reaching TD, it will have 3,745 rows. That is just over the threshhold for its cost math to predict that a hash join would be cheaper than a nested loops. If you'll notice your hinted version, it calculated cost 42K whereas its preferred plan is 35K - number very close to each other, so it's close to the inflection point. Small changes could result it in flipping back and forth between these two plans. This is typical for Oracle, which is always trying to choose what it believes is the best plan as data changes over time. Of course, it's imperfect and not infrequently feeds incorrect assumptions into its calculations, resulting in incorrect costing, and that means bad plans. We try to give it the best stats we can, and if we still have issues, hints do wonders at fixing things and keeping them stable. That being said, don't assume Oracle is wrong here.
Now, I suspect you are not timing your tests correctly. 1s sounds like you are timing to the first row that appears (response time) rather than timing until the last row is fetched (throughput). Changing from a hash join to a nested loops so that every join is nested loops can absolutely give you an immediate < 1s response (first row) because that's how nested loops work - you get the first row before it has worked on the next row, etc. If it's nested loops all the way down, you'll see that first row in your client right away. If you have but a single hash join anywhere, you'll have to wait until that hash join has been completed for all rows before you see your first row. If this is what is happening, then you need to change your test. Wrap your query in a
SELECT COUNT(*) FROM ([query])in order to test throughout of the entire SQL. I suspect you will find that Oracle's original plan is nearly the same as your new plan, or even slightly better.To actually tune your query, you need to find out where it is spending its time. After running it for a while, consult ASH (
v$active_session_history) for thesql_idof your SQL, which you can get fromv$session. Look at thesql_plan_line_id,sql_plan_operation,eventand count the rows. For example:This will show you how many seconds (
COUNT) were spent on which plan line and what event. This will tell you where the problem is in the query. Fixing it will depend on what the problem is. But this should get you started.