Recently I’ve been called to help with production issue that stopped all POS (Point of Sales) for 2 hours, producing lost for the affected company.
I’ve found relatively quickly problematic query, which looks at first very simple.
This is the problematic SQL code:
SELECT *
FROM
(SELECT DOCUMENT_DATE,
DOCUMENT_TYPE,
AMT_TOTAL,
DOCUMENT_NUM,
FISCALISED,
REF_DOCUMENT_NUM,
ORDER_TSF_NO,
STATUS_LOOKUP,
DOCUMENT_SUBTYPE,
CUSTOMER_ID,
RETURN_TSF_NO,
CHANNEL_LOOKUP,
CONS_IND,
ORDER_ID,
STATUS,
DOCUMENT_NUM_SORT,
FULL_USER_NAME,
DELIVERY_POINT_LOOKUP,
ISSUED_BY,
RETURN_ID,
DOCUMENT_TYPE_LOOKUP,
STORNO_IND,
CREATED_BY,
SOURCE,
CUSTOMER_LOOKUP,
LOCATION_LOOKUP,
SOURCE_LOOKUP,
DOCUMENT_ID
FROM VIEWC
ORDER BY DOCUMENT_NUM_SORT ASC
)
WHERE ROWNUM < 2001;
Unfortunately, VIEWC is view based on another view, and that view has another view behind.
After unpacking all views, I’ve ended up with almost 2.000 lines of SQL code.
And that suppose to be critical SQL.
When I’ve take a look at the number of execution plans, there were almost 20 different plans in the history.
PLAN_HASH_VALUE TIMESTAMP
————————————— ——————-
1877509988 25.04.2016 07:04:23
679471829 25.04.2016 07:02:52
3866795434 21.04.2016 20:09:16
2041514247 21.04.2016 18:34:04
3291387960 21.04.2016 10:53:39
1178951851 19.04.2016 19:51:02
2803136072 19.04.2016 19:32:05
3548552743 19.04.2016 08:50:53
356038866 19.04.2016 08:50:24
1383466028 19.04.2016 08:48:08
2467476840 19.04.2016 06:29:56
2756617589 18.04.2016 07:23:24
955268268 18.04.2016 07:11:59
4121285641 11.04.2016 06:29:36
2297246186 01.04.2016 05:49:18
4033195977 28.03.2016 07:13:21
Although execution speed for majority of those plans can be ignored, still there where two disastrous plans with a huge impact on production system.
To fix the incident, I’ve killed all sessions that execute that statement, remove the cursor from the shared pool and collect statistics for all objects involved in problematic SQL.
But I knew that is not going to last for a long time, and that incident will show up within a week or two again, as the execution plan is continue to change.
There are two ways to resolve the problem permanently:
1.
To remove all views, and to start writing a new, optimized version of the query from the beginning.
2.
To choose good execution plan and to try to stabilize it.
Although first solution is much better, as there are a lot of business logic built in those 2.000+ lines of SQL code, it is also a very time consuming
After I eliminate the first option, the only think I can do is to try to stabilize the execution plan.
I’ve executed the following query to find out how many cursors I have, and what is the reason of not sharing the cursor
SELECT * FROM v$sql_shared_cursor WHERE sql_id = 'gdv7gra49vg7j';
Column of interest in this case is:
USE_FEEDBACK_STATS = ‘Y’
Because of complexity of the query, optimizer feedback stats was responsible for all those execution plan changes (plus global context, RLS…).
For that reason I was not able to stabilize the plan by using standard features like SQL Plan Baseline or SQL Profiler.
I’ve decided to add two hints that could stabilize that critical SQL:
SELECT /*+ opt_param(‘_optimizer_use_feedback’,’false’) first_rows(1000) */
…
Second hint is self explanatory. As this main goal of this SQL is to return a few records fast (this is OLTP system), by adding first_rows hint I’ve put CBO in special mode with different objective: to return first few records as fast as possible (default mode for CBO is ALL ROWS).
First hint, although implemented in several older generations of Oracle Db as hidden parameter, with 12c become public.
Main task of that hint is to turn on/off some optimizer features on SQL statement level (there are also session and system level). In this case, I’ve turned off optimizer feedback feature for problematic query.
After implementing, problem was fixed permanently as there are only one execution plan that is running fast.
For a sake of completeness I’ll add bad and good execution plans, just to see complexity of that important query.
Bad plan:
Plan hash value: 955268268
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7043 (100)|
| 1 | SORT AGGREGATE | | 1 | 29 | | |
| 2 | VIEW | | 7 | 203 | | 19 (0)|
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 5 | 110 | | 10 (0)|
| 6 | NESTED LOOPS | | 1 | 11 | | 6 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 7 | | 3 (0)|
| 8 | INDEX UNIQUE SCAN | PK_DOCUMENTS | 1 | | | 2 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 1 | 14 | | 3 (0)|
| 10 | INDEX RANGE SCAN | DOCUMENT_NOTES#DOCUMENT_ID | 1 | | | 2 (0)|
| 11 | INDEX RANGE SCAN | DOCUMENT_NOTE_DETAILS#DOCUMENT | 11 | | | 2 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTE_DETAILS | 11 | 231 | | 4 (0)|
| 13 | NESTED LOOPS OUTER | | 1 | 11 | | 7 (0)|
| 14 | NESTED LOOPS | | 1 | 12 | | 5 (0)|
| 15 | NESTED LOOPS | | 1 | 18 | | 5 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 7 | | 3 (0)|
| 17 | INDEX UNIQUE SCAN | PK_DOCUMENTS | 1 | | | 2 (0)|
| 18 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NON_MERCH_DETAILS | 1 | 21 | | 2 (0)|
| 19 | INDEX RANGE SCAN | DOCUMENT_NON_MERCH_DETAILS#DOC | 1 | | | 1 (0)|
| 20 | INDEX UNIQUE SCAN | PK_EXPENSE_CODE_DETAILS | 1 | 6 | | 0 (0)|
| 21 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 1 | 9 | | 2 (0)|
| 22 | INDEX UNIQUE SCAN | PK_DOCUMENT_NOTES | 1 | | | 1 (0)|
| 23 | NESTED LOOPS | | 1 | 45 | | 2 (0)|
| 24 | INDEX UNIQUE SCAN | PK_EXPENSE_CODE_DETAILS | 1 | 6 | | 0 (0)|
| 25 | TABLE ACCESS FULL | VOLUME_REBATE_DETAILS | 1 | 39 | | 2 (0)|
| 26 | SORT AGGREGATE | | 1 | 21 | | |
| 27 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTE_DETAILS | 11 | 231 | | 5 (0)|
| 28 | INDEX RANGE SCAN | DOCUMENT_NOTE_DETAILS#DOCUMENT | 11 | | | 3 (0)|
| 29 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 18 | | 3 (0)|
| 30 | INDEX UNIQUE SCAN | PK_DOCUMENTS | 1 | | | 2 (0)|
| 31 | NESTED LOOPS OUTER | | 1 | 184 | | 5 (0)|
| 32 | TABLE ACCESS BY INDEX ROWID | CODE_DETAILS | 1 | 41 | | 2 (0)|
| 33 | INDEX UNIQUE SCAN | UK_CODE_DETAILS_CODE_CODE_TYPE | 1 | | | 1 (0)|
| 34 | VIEW | | 1 | 151 | | 3 (0)|
| 35 | NESTED LOOPS | | 1 | 46 | | 3 (0)|
| 36 | FAST DUAL | | 1 | | | 2 (0)|
| 37 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATIONS | 1 | 46 | | 1 (0)|
| 38 | INDEX UNIQUE SCAN | UK_CODE_TRANSLATIONS_LOCALE | 1 | | | 0 (0)|
| 39 | TABLE ACCESS BY INDEX ROWID | T_LDAP | 1 | 30 | | 2 (0)|
| 40 | INDEX RANGE SCAN | T_LDAP_IDX2 | 1 | | | 1 (0)|
| 41 | NESTED LOOPS OUTER | | 1 | 184 | | 5 (0)|
| 42 | TABLE ACCESS BY INDEX ROWID | CODE_DETAILS | 1 | 41 | | 2 (0)|
| 43 | INDEX UNIQUE SCAN | UK_CODE_DETAILS_CODE_CODE_TYPE | 1 | | | 1 (0)|
| 44 | VIEW | | 1 | 151 | | 3 (0)|
| 45 | NESTED LOOPS | | 1 | 46 | | 3 (0)|
| 46 | FAST DUAL | | 1 | | | 2 (0)|
| 47 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATIONS | 1 | 46 | | 1 (0)|
| 48 | INDEX UNIQUE SCAN | UK_CODE_TRANSLATIONS_LOCALE | 1 | | | 0 (0)|
| 49 | NESTED LOOPS OUTER | | 1 | 184 | | 5 (0)|
| 50 | TABLE ACCESS BY INDEX ROWID | CODE_DETAILS | 1 | 41 | | 2 (0)|
| 51 | INDEX UNIQUE SCAN | UK_CODE_DETAILS_CODE_CODE_TYPE | 1 | | | 1 (0)|
| 52 | VIEW | | 1 | 151 | | 3 (0)|
| 53 | NESTED LOOPS | | 1 | 46 | | 3 (0)|
| 54 | FAST DUAL | | 1 | | | 2 (0)|
| 55 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATIONS | 1 | 46 | | 1 (0)|
| 56 | INDEX UNIQUE SCAN | UK_CODE_TRANSLATIONS_LOCALE | 1 | | | 0 (0)|
| 57 | COUNT STOPKEY | | | | | |
| 58 | VIEW | | 8034 | 14M| | 7043 (5)|
| 59 | SORT ORDER BY STOPKEY | | 8034 | 7578K| 8048K| 7043 (5)|
| 60 | FILTER | | | | | |
| 61 | NESTED LOOPS OUTER | | 8034 | 7578K| | 5412 (6)|
| 62 | FILTER | | | | | |
| 63 | NESTED LOOPS OUTER | | 8034 | 7422K| | 5410 (6)|
| 64 | HASH JOIN | | 8034 | 7304K| | 5409 (6)|
| 65 | VIEW | | 4017 | 3185K| | 4527 (7)|
| 66 | UNION-ALL | | | | | |
| 67 | FILTER | | | | | |
| 68 | HASH JOIN | | 2025 | 1117K| | 3674 (2)|
| 69 | TABLE ACCESS FULL | CUSTOMER_ADDR | 39124 | 649K| | 543 (1)|
| 70 | HASH JOIN RIGHT OUTER | | 2003 | 811K| | 3130 (2)|
| 71 | TABLE ACCESS FULL | DOCUMENT_NOTES | 429K| 5029K| | 2758 (1)|
| 72 | HASH JOIN | | 2004 | 532K| | 366 (4)|
| 73 | TABLE ACCESS FULL | DELIVERY_POINT | 84445 | 2473K| | 356 (3)|
| 74 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 64336 | 8230K| | 8 (13)|
| 75 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 76 | BITMAP AND | | | | | |
| 77 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 78 | SORT ORDER BY | | | | | |
| 79 | INDEX RANGE SCAN | DOCUMENTS_DATE_IND | 8993 | | | 3 (0)|
| 80 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 81 | INDEX RANGE SCAN | DOCUMENTS_LOCATION_IND | 8993 | | | 4 (0)|
| 82 | FILTER | | | | | |
| 83 | HASH JOIN | | 1992 | 334K| | 853 (31)|
| 84 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 65208 | 7195K| | 66 (2)|
| 85 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 86 | BITMAP AND | | | | | |
| 87 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 88 | SORT ORDER BY | | | | | |
| 89 | INDEX RANGE SCAN | DOCUMENT_NOTES_DATE_IND | 1931 | | | 16 (0)|
| 90 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 91 | INDEX RANGE SCAN | DOCUMENT_NOTES_LOCATION_IND | 1931 | | | 43 (0)|
| 92 | HASH JOIN | | 17M| 914M| | 660 (21)|
| 93 | TABLE ACCESS FULL | DELIVERY_POINT | 4302 | 126K| | 354 (2)|
| 94 | TABLE ACCESS FULL | CUSTOMER_ADDR | 43329 | 803K| | 179 (1)|
| 95 | NESTED LOOPS | | 78908 | 9940K| | 881 (1)|
| 96 | NESTED LOOPS | | 2 | 200 | | 10 (0)|
| 97 | NESTED LOOPS | | 1 | 23 | | 6 (0)|
| 98 | NESTED LOOPS | | 1 | 13 | | 5 (0)|
| 99 | FAST DUAL | | 1 | | | 2 (0)|
| 100 | VIEW | | 1 | 13 | | 3 (0)|
| 101 | SORT AGGREGATE | | 1 | 20 | | |
| 102 | TABLE ACCESS BY INDEX ROWID | CHANNEL_LOCATIONS | 1 | 20 | | 3 (0)|
| 103 | INDEX RANGE SCAN | CHANNEL_IND1 | 1 | | | 2 (0)|
| 104 | TABLE ACCESS BY INDEX ROWID | CHANNELS | 1 | 18 | | 1 (0)|
| 105 | INDEX UNIQUE SCAN | CHANNELS_PK | 1 | | | 0 (0)|
| 106 | VIEW | | 2 | 154 | | 4 (0)|
| 107 | UNION-ALL | | | | | |
| 108 | FILTER | | | | | |
| 109 | TABLE ACCESS BY INDEX ROWID | WH | 1 | 35 | | 2 (0)|
| 110 | INDEX UNIQUE SCAN | PK_WH | 1 | | | 1 (0)|
| 111 | FILTER | | | | | |
| 112 | TABLE ACCESS BY INDEX ROWID | STORE | 1 | 22 | | 2 (0)|
| 113 | INDEX UNIQUE SCAN | PK_STORE | 1 | | | 1 (0)|
| 114 | TABLE ACCESS FULL | CUSTOMER_MASTER | 39454 | 1117K| | 435 (1)|
| 115 | TABLE ACCESS BY INDEX ROWID | RETURNS | 1 | 25 | | 1 (0)|
| 116 | INDEX UNIQUE SCAN | PK_RETURNS_ID | 1 | | | 0 (0)|
| 117 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 30 | | 2 (0)|
| 118 | INDEX UNIQUE SCAN | PK_ORDERS_ID | 1 | | | 1 (0)|
------------------------------------------------------------------------------------------------------------------------------
Good plan:
Plan hash value: 4033195977
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 129 (100)|
| 1 | SORT AGGREGATE | | 1 | 29 | |
| 2 | VIEW | | 7 | 203 | 18 (0)|
| 3 | UNION-ALL | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | 5 | 110 | 10 (0)|
| 6 | NESTED LOOPS | | 1 | 11 | 6 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 7 | 3 (0)|
| 8 | INDEX UNIQUE SCAN | PK_DOCUMENTS | 1 | | 2 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 1 | 14 | 3 (0)|
| 10 | INDEX RANGE SCAN | DOCUMENT_NOTES#DOCUMENT_ID | 1 | | 2 (0)|
| 11 | INDEX RANGE SCAN | DOCUMENT_NOTE_DETAILS#DOCUMENT | 11 | | 2 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTE_DETAILS | 11 | 231 | 4 (0)|
| 13 | NESTED LOOPS OUTER | | 1 | 22 | 6 (0)|
| 14 | NESTED LOOPS | | 1 | 23 | 5 (0)|
| 15 | NESTED LOOPS | | 1 | 29 | 5 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 7 | 3 (0)|
| 17 | INDEX UNIQUE SCAN | PK_DOCUMENTS | 1 | | 2 (0)|
| 18 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NON_MERCH_DETAILS | 1 | 32 | 2 (0)|
| 19 | INDEX RANGE SCAN | DOCUMENT_NON_MERCH_DETAILS#DOC | 1 | | 1 (0)|
| 20 | INDEX UNIQUE SCAN | PK_EXPENSE_CODE_DETAILS | 1 | 6 | 0 (0)|
| 21 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 1 | 9 | 1 (0)|
| 22 | INDEX UNIQUE SCAN | PK_DOCUMENT_NOTES | 1 | | 1 (0)|
| 23 | NESTED LOOPS | | 1 | 45 | 2 (0)|
| 24 | INDEX UNIQUE SCAN | PK_EXPENSE_CODE_DETAILS | 1 | 6 | 0 (0)|
| 25 | TABLE ACCESS FULL | VOLUME_REBATE_DETAILS | 1 | 39 | 2 (0)|
| 26 | SORT AGGREGATE | | 1 | 21 | |
| 27 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTE_DETAILS | 11 | 231 | 5 (0)|
| 28 | INDEX RANGE SCAN | DOCUMENT_NOTE_DETAILS#DOCUMENT | 11 | | 3 (0)|
| 29 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 18 | 3 (0)|
| 30 | INDEX UNIQUE SCAN | PK_DOCUMENTS | 1 | | 2 (0)|
| 31 | NESTED LOOPS OUTER | | 1 | 184 | 5 (0)|
| 32 | TABLE ACCESS BY INDEX ROWID | CODE_DETAILS | 1 | 41 | 2 (0)|
| 33 | INDEX UNIQUE SCAN | UK_CODE_DETAILS_CODE_CODE_TYPE | 1 | | 1 (0)|
| 34 | VIEW | | 1 | 151 | 3 (0)|
| 35 | NESTED LOOPS | | 1 | 46 | 3 (0)|
| 36 | FAST DUAL | | 1 | | 2 (0)|
| 37 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATIONS | 1 | 46 | 1 (0)|
| 38 | INDEX UNIQUE SCAN | UK_CODE_TRANSLATIONS_LOCALE | 1 | | 0 (0)|
| 39 | TABLE ACCESS BY INDEX ROWID | T_LDAP | 7 | 2730 | 1 (0)|
| 40 | INDEX RANGE SCAN | T_LDAP_IDX2 | 3 | | 1 (0)|
| 41 | NESTED LOOPS OUTER | | 1 | 184 | 5 (0)|
| 42 | TABLE ACCESS BY INDEX ROWID | CODE_DETAILS | 1 | 41 | 2 (0)|
| 43 | INDEX UNIQUE SCAN | UK_CODE_DETAILS_CODE_CODE_TYPE | 1 | | 1 (0)|
| 44 | VIEW | | 1 | 151 | 3 (0)|
| 45 | NESTED LOOPS | | 1 | 46 | 3 (0)|
| 46 | FAST DUAL | | 1 | | 2 (0)|
| 47 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATIONS | 1 | 46 | 1 (0)|
| 48 | INDEX UNIQUE SCAN | UK_CODE_TRANSLATIONS_LOCALE | 1 | | 0 (0)|
| 49 | NESTED LOOPS OUTER | | 1 | 184 | 5 (0)|
| 50 | TABLE ACCESS BY INDEX ROWID | CODE_DETAILS | 1 | 41 | 2 (0)|
| 51 | INDEX UNIQUE SCAN | UK_CODE_DETAILS_CODE_CODE_TYPE | 1 | | 1 (0)|
| 52 | VIEW | | 1 | 151 | 3 (0)|
| 53 | NESTED LOOPS | | 1 | 46 | 3 (0)|
| 54 | FAST DUAL | | 1 | | 2 (0)|
| 55 | TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATIONS | 1 | 46 | 1 (0)|
| 56 | INDEX UNIQUE SCAN | UK_CODE_TRANSLATIONS_LOCALE | 1 | | 0 (0)|
| 57 | COUNT STOPKEY | | | | |
| 58 | VIEW | | 4 | 7496 | 129 (4)|
| 59 | SORT ORDER BY STOPKEY | | 4 | 3860 | 129 (4)|
| 60 | MERGE JOIN CARTESIAN | | 4 | 3860 | 128 (3)|
| 61 | NESTED LOOPS | | | | |
| 62 | NESTED LOOPS | | 2 | 1776 | 120 (3)|
| 63 | FILTER | | | | |
| 64 | NESTED LOOPS OUTER | | 2 | 1742 | 118 (3)|
| 65 | FILTER | | | | |
| 66 | NESTED LOOPS OUTER | | 2 | 1702 | 116 (3)|
| 67 | NESTED LOOPS | | 2 | 1672 | 115 (3)|
| 68 | NESTED LOOPS | | 1 | 24 | 6 (0)|
| 69 | NESTED LOOPS | | 1 | 13 | 5 (0)|
| 70 | FAST DUAL | | 1 | | 2 (0)|
| 71 | VIEW | | 1 | 13 | 3 (0)|
| 72 | SORT AGGREGATE | | 1 | 20 | |
| 73 | TABLE ACCESS BY INDEX ROWID | CHANNEL_LOCATIONS | 1 | 20 | 3 (0)|
| 74 | INDEX RANGE SCAN | CHANNEL_IND1 | 1 | | 2 (0)|
| 75 | TABLE ACCESS BY INDEX ROWID | CHANNELS | 1 | 19 | 1 (0)|
| 76 | INDEX UNIQUE SCAN | CHANNELS_PK | 1 | | 0 (0)|
| 77 | VIEW | | 2 | 1624 | 109 (3)|
| 78 | UNION-ALL | | | | |
| 79 | FILTER | | | | |
| 80 | NESTED LOOPS OUTER | | 1 | 160 | 53 (2)|
| 81 | NESTED LOOPS | | 1 | 158 | 50 (2)|
| 82 | NESTED LOOPS | | 1 | 151 | 48 (3)|
| 83 | TABLE ACCESS BY INDEX ROWID | DOCUMENTS | 1 | 131 | 47 (3)|
| 84 | BITMAP CONVERSION TO ROWIDS | | | | |
| 85 | BITMAP AND | | | | |
| 86 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 87 | SORT ORDER BY | | | | |
| 88 | INDEX RANGE SCAN | DOCUMENTS_DATE_IND | 7354 | | 11 (0)|
| 89 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 90 | INDEX RANGE SCAN | DOCUMENTS_LOCATION_IND | 7354 | | 30 (0)|
| 91 | TABLE ACCESS BY INDEX ROWID | DELIVERY_POINT | 1 | 30 | 1 (0)|
| 92 | INDEX UNIQUE SCAN | DELIVERY_POINT_ID_UK1 | 1 | | 0 (0)|
| 93 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ADDR | 1 | 17 | 2 (0)|
| 94 | INDEX RANGE SCAN | CUSTOMER_ADDR_CHK3 | 1 | | 1 (0)|
| 95 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 1 | 12 | 3 (0)|
| 96 | INDEX RANGE SCAN | DOCUMENT_NOTES#DOCUMENT_ID | 1 | | 2 (0)|
| 97 | FILTER | | | | |
| 98 | NESTED LOOPS | | | | |
| 99 | NESTED LOOPS | | 1 | 138 | 56 (2)|
| 100 | NESTED LOOPS | | 1 | 133 | 55 (2)|
| 101 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_NOTES | 1 | 113 | 54 (2)|
| 102 | BITMAP CONVERSION TO ROWIDS | | | | |
| 103 | BITMAP AND | | | | |
| 104 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 105 | SORT ORDER BY | | | | |
| 106 | INDEX RANGE SCAN | DOCUMENT_NOTES_DATE_IND | 1512 | | 13 (0)|
| 107 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 108 | INDEX RANGE SCAN | DOCUMENT_NOTES_LOCATION_IND | 1512 | | 35 (0)|
| 109 | TABLE ACCESS BY INDEX ROWID | DELIVERY_POINT | 1 | 30 | 1 (0)|
| 110 | INDEX UNIQUE SCAN | DELIVERY_POINT_ID_UK1 | 1 | | 0 (0)|
| 111 | INDEX UNIQUE SCAN | CUSTOMER_ADDR_CHK3 | 1 | | 0 (0)|
| 112 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ADDR | 1 | 19 | 1 (0)|
| 113 | TABLE ACCESS BY INDEX ROWID | RETURNS | 1 | 25 | 1 (0)|
| 114 | INDEX UNIQUE SCAN | PK_RETURNS_ID | 1 | | 0 (0)|
| 115 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 30 | 2 (0)|
| 116 | INDEX UNIQUE SCAN | PK_ORDERS_ID | 1 | | 1 (0)|
| 117 | INDEX UNIQUE SCAN | CUSTOMER_MASTER_PK | 1 | | 0 (0)|
| 118 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_MASTER | 1 | 29 | 1 (0)|
| 119 | BUFFER SORT | | 2 | 154 | 128 (4)|
| 120 | VIEW | | 2 | 154 | 4 (0)|
| 121 | UNION-ALL | | | | |
| 122 | FILTER | | | | |
| 123 | TABLE ACCESS BY INDEX ROWID | WH | 1 | 35 | 2 (0)|
| 124 | INDEX UNIQUE SCAN | PK_WH | 1 | | 1 (0)|
| 125 | FILTER | | | | |
| 126 | TABLE ACCESS BY INDEX ROWID | STORE | 1 | 22 | 2 (0)|
| 127 | INDEX UNIQUE SCAN | PK_STORE | 1 | | 1 (0)|
-------------------------------------------------------------------------------------------------------------------------
Although I didn’t provide precise statistics for tables involved in the SQL, the main reason for huge difference in the execution times is order of joining tables and operation on those tables.
Lessons learned:
Try to avoid views when writing important SQLs.
In this case, main reason for such complex critical SQL is design flaw in data model, and developer
wanted to fix it by using SQL with very complex business logic implemented.
Critical SQL queries needs to be as simple as possible. If data model doesn’t allow to do that, although impact of changing it can be large, sooner or later you’ll have to change it.
Comments