Проблема проявилась в запросах типа:
SELECT DISTINCT G.REC_PRINT_ID,
G.REC_REPORT_ID,
H.PARTY_ID,
apps.CUST_PACKAGE.GET_INFO('CUSTOMER_ID',H.PARTY_ID,H.K_HEADER_ID) CUSTOMER_ID -- any PL/SQL function
FROM apps.CUST_TABLE G,
apps.CUST_VIEW H -- view with ORDER BY
WHERE G.REC_REPORT_ID = H.REC_REPORT_ID
AND G.REC_GROUP_ID = :1;
с использованием view with ORDER BY и PL/SQL функции
При наличии всех необходимых индексов план выглядел пессимистично:
------------------------------------------------------------------------------------------------
| Id | Operation | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 4 | 148 | | 9851K (1)| 00:06:25 |
| 1 | HASH UNIQUE | 4 | 148 | | 9851K (1)| 00:06:25 |
|* 2 | HASH JOIN | 4 | 148 | | 9851K (1)| 00:06:25 |
| 3 | JOIN FILTER CREATE | 4 | 76 | | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| 4 | 76 | | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | 4 | | | 3 (0)| 00:00:01 |
| 6 | VIEW | 6785K| 116M| | 9851K (1)| 00:06:25 |
| 7 | SORT ORDER BY | 6785K| 1857M| 33G| 9851K (1)| 00:06:25 |
|* 8 | FILTER | | | | | |
| 9 | JOIN FILTER USE | 115M| 30G| | 2720K (2)| 00:01:47 |
|* 10 | HASH JOIN RIGHT OUTER | 115M| 30G| | 2720K (2)| 00:01:47 |
|* 11 | INDEX RANGE SCAN | 44 | | | 4 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | 115M| 25G| 126M| 2719K (1)| 00:01:47 |
|* 13 | TABLE ACCESS FULL | 4406K| 75M| | 521K (1)| 00:00:21 |
| 14 | TABLE ACCESS FULL | 115M| 23G| | 955K (3)| 00:00:38 |
|* 15 | COLLECTION ITERATOR PICKLER FETCH| 82 | 164 | | 30 (4)| 00:00:01 |
------------------------------------------------------------------------------------------------
со странным комментарием в трейсе:
SVM: SVM bypassed: ORDER BY clause.
, несмотря на:
SQL> @param+ _optimizer_order_by_elimination_enabled
NAME SESS_VALUE INST_VALUE Default Value IS_MODIFIED IS_SESS_MOD IS_SYS_MOD IS_PDB_MODIFIABLE IS_ADJ DSC
---------------------------------------- ---------- ---------- ------------- ----------- ----------- ---------- ----------------- -------- ------------------------------------------------------------
_optimizer_order_by_elimination_enabled TRUE TRUE TRUE FALSE TRUE IMMEDIATE TRUE FALSE Eliminates order bys from views before query transformation
— до этого я как-то искренне считал, что проблема Order BY Elimination (OBYE) для Oracle 19 давно неактуальна
Легковоспроизводимый тесткейс с заменой VIEW -> INLINE VIEW и использованием inline PL/SQL function: (more…)
Oracle mechanics 