Wanted to see if anybody could give me pointers for a sql I'm trying to "tune". This is actually code written by Oracle (EBS standard) so I don't have too much control over the sql.
History first, this sql is part of a batch job that runs every day and is inserting records into xla_trial_balances table.
The sql is usually inserting around 5000-10.000 records in each batch job and the records are selected from 10 tables, some of them very large (will provide stats).
This query is taking about 10 hours or more to run which seems a lot of time for 5000-10000 rows.
Attached is the sql itself (fkzwp9dwy8ng0.txt) and the sql-monitor overview of the execution. Here is the explain plan from the latest execution:
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fkzwp9dwy8ng0',1));
SQL_ID fkzwp9dwy8ng0, child number 1
-------------------------------------
INSERT INTO xla_trial_balances ( record_type_code
,source_entity_id ,event_class_code
,source_application_id ,applied_to_entity_id
,applied_to_application_id ,gl_date
,trx_currency_code ,entered_rounded_dr
,entered_rounded_cr ,entered_unrounded_dr
,entered_unrounded_cr ,acctd_rounded_dr
,acctd_rounded_cr ,acctd_unrounded_dr
,acctd_unrounded_cr ,code_combination_id
,balancing_segment_value ,natural_account_segment_value
,cost_center_segment_value ,intercompany_segment_value
,management_segment_value ,ledger_id
,definition_code ,party_id ,party_site_id
,party_type_code ,ae_header_id ,generated_by_code
,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
Plan hash value: 853743902
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 3050 (100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 2 | HASH GROUP BY | | 1 | 412 | 3050 (2)| 00:00:37 | | |
|* 3 | FILTER | | | | | | | |
|* 4 | HASH JOIN | | 1 | 412 | 3049 (2)| 00:00:37 | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 307 | 18 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS OUTER | | 1 | 271 | 17 (0)| 00:00:01 | | |
| 8 | MERGE JOIN CARTESIAN | | 1 | 251 | 16 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 236 | 15 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS OUTER | | 1 | 183 | 13 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 126 | 8 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 74 | 6 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 | | |
| 15 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 7 | 1 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 0 (0)| | | |
| 17 | TABLE ACCESS BY INDEX ROWID | FND_CURRENCIES | 249 | 5229 | 1 (0)| 00:00:01 | | |
|* 18 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | | 0 (0)| | | |
| 19 | TABLE ACCESS BY GLOBAL INDEX ROWID| XLA_AE_LINES | 1 | 46 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 20 | INDEX RANGE SCAN | XLA_AE_LINES_C2_9529961 | 1 | | 3 (0)| 00:00:01 | | |
| 21 | PARTITION LIST ITERATOR | | 1 | 52 | 2 (0)| 00:00:01 | KEY | KEY |
|* 22 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 1 | 52 | 2 (0)| 00:00:01 | KEY | KEY |
|* 23 | INDEX UNIQUE SCAN | XLA_AE_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 24 | PARTITION LIST ITERATOR | | 1 | 57 | 5 (0)| 00:00:01 | KEY | KEY |
|* 25 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_DISTRIBUTION_LINKS | 1 | 57 | 5 (0)| 00:00:01 | KEY | KEY |
|* 26 | INDEX RANGE SCAN | XLA_DISTRIBUTION_LINKS_N3 | 11 | | 3 (0)| 00:00:01 | KEY | KEY |
|* 27 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 | | |
|* 28 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 53 | 2 (0)| 00:00:01 | | |
| 29 | BUFFER SORT | | 1 | 15 | 14 (0)| 00:00:01 | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | XLA_TB_DEFINITIONS_B | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 31 | INDEX RANGE SCAN | XLA_TB_DEFINITIONS_B_N1 | 1 | | 0 (0)| | | |
| 32 | TABLE ACCESS BY INDEX ROWID | XLA_LEDGER_OPTIONS | 2 | 40 | 1 (0)| 00:00:01 | | |
|* 33 | INDEX UNIQUE SCAN | XLA_LEDGER_OPTIONS_U1 | 1 | | 0 (0)| | | |
|* 34 | INDEX UNIQUE SCAN | XLA_EVENT_TYPES_B_U2 | 1 | | 0 (0)| | | |
| 35 | TABLE ACCESS BY INDEX ROWID | XLA_EVENT_TYPES_B | 1 | 36 | 1 (0)| 00:00:01 | | |
| 36 | TABLE ACCESS STORAGE FULL | XLA_TB_DEF_SEG_RANGES | 754K| 75M| 3025 (2)| 00:00:37 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:7<=:8)
4 - access("XTD"."DEFINITION_CODE"="XSR"."DEFINITION_CODE")
filter((NVL("GCC"."SEGMENT1",'0')>=NVL(NVL("XSR"."SEGMENT1_FROM","GCC"."SEGMENT1"),'0') AND
NVL("GCC"."SEGMENT1",'0')<=NVL(NVL("XSR"."SEGMENT1_TO","GCC"."SEGMENT1"),'0') AND
NVL("GCC"."SEGMENT2",'0')>=NVL(NVL("XSR"."SEGMENT2_FROM","GCC"."SEGMENT2"),'0') AND
NVL("GCC"."SEGMENT2",'0')<=NVL(NVL("XSR"."SEGMENT2_TO","GCC"."SEGMENT2"),'0') AND
NVL("GCC"."SEGMENT3",'0')>=NVL(NVL("XSR"."SEGMENT3_FROM","GCC"."SEGMENT3"),'0') AND
NVL("GCC"."SEGMENT3",'0')<=NVL(NVL("XSR"."SEGMENT3_TO","GCC"."SEGMENT3"),'0') AND
NVL("GCC"."SEGMENT4",'0')>=NVL(NVL("XSR"."SEGMENT4_FROM","GCC"."SEGMENT4"),'0') AND
NVL("GCC"."SEGMENT4",'0')<=NVL(NVL("XSR"."SEGMENT4_TO","GCC"."SEGMENT4"),'0') AND
NVL("GCC"."SEGMENT5",'0')>=NVL(NVL("XSR"."SEGMENT5_FROM","GCC"."SEGMENT5"),'0') AND
NVL("GCC"."SEGMENT5",'0')<=NVL(NVL("XSR"."SEGMENT5_TO","GCC"."SEGMENT5"),'0') AND
NVL("GCC"."SEGMENT6",'0')>=NVL(NVL("XSR"."SEGMENT6_FROM","GCC"."SEGMENT6"),'0') AND
NVL("GCC"."SEGMENT6",'0')<=NVL(NVL("XSR"."SEGMENT6_TO","GCC"."SEGMENT6"),'0') AND
NVL("GCC"."SEGMENT7",'0')>=NVL(NVL("XSR"."SEGMENT7_FROM","GCC"."SEGMENT7"),'0') AND
NVL("GCC"."SEGMENT7",'0')<=NVL(NVL("XSR"."SEGMENT7_TO","GCC"."SEGMENT7"),'0') AND
NVL("GCC"."SEGMENT8",'0')>=NVL(NVL("XSR"."SEGMENT8_FROM","GCC"."SEGMENT8"),'0') AND
NVL("GCC"."SEGMENT8",'0')<=NVL(NVL("XSR"."SEGMENT8_TO","GCC"."SEGMENT8"),'0') AND
NVL("GCC"."SEGMENT9",'0')>=NVL(NVL("XSR"."SEGMENT9_FROM","GCC"."SEGMENT9"),'0') AND
NVL("GCC"."SEGMENT9",'0')<=NVL(NVL("XSR"."SEGMENT9_TO","GCC"."SEGMENT9"),'0')))
16 - access("GL"."LEDGER_ID"=:9)
18 - access("FDC"."CURRENCY_CODE"="GL"."CURRENCY_CODE")
20 - access("XAL"."AE_HEADER_ID">=:7 AND "XAL"."ACCOUNTING_CLASS_CODE"='LIABILITY' AND "XAL"."AE_HEADER_ID"<=:8)
filter("XAL"."ACCOUNTING_CLASS_CODE"='LIABILITY')
22 - filter(("XAH"."UPG_BATCH_ID" IS NULL AND "XAH"."GROUP_ID"=:GROUP_ID AND "XAH"."LEDGER_ID"=:9 AND
"XAH"."EVENT_TYPE_CODE"<>'MANUAL' AND INTERNAL_FUNCTION("XAH"."GL_TRANSFER_STATUS_CODE")))
23 - access("XAH"."AE_HEADER_ID"="XAL"."AE_HEADER_ID" AND "XAH"."APPLICATION_ID"="XAL"."APPLICATION_ID")
filter(("XAH"."AE_HEADER_ID">=:7 AND "XAH"."AE_HEADER_ID"<=:8))
25 - filter("XAL"."APPLICATION_ID"="XDL"."APPLICATION_ID")
26 - access("XAL"."AE_HEADER_ID"="XDL"."AE_HEADER_ID" AND "XAL"."AE_LINE_NUM"="XDL"."AE_LINE_NUM")
filter(("XDL"."AE_HEADER_ID">=:7 AND "XDL"."AE_HEADER_ID"<=:8))
27 - access("XAL"."CODE_COMBINATION_ID"="GCC"."CODE_COMBINATION_ID")
28 - filter("GCC"."CHART_OF_ACCOUNTS_ID"=:COA_ID)
30 - filter("XTD"."ENABLED_FLAG"='Y')
31 - access("XTD"."LEDGER_ID"=:9)
33 - access("XLO"."APPLICATION_ID"="XAH"."APPLICATION_ID" AND "XLO"."LEDGER_ID"=:9)
34 - access("XAH"."APPLICATION_ID"="XET"."APPLICATION_ID" AND "XAH"."EVENT_TYPE_CODE"="XET"."EVENT_TYPE_CODE")
filter("XET"."EVENT_TYPE_CODE"<>'MANUAL')
105 rows selected.
SQL>
From the sql-monitor html I can see that there is a data-skew where the optimizer thinks it will get 1 row from xla_ae_lines but in fact gets 7347 rows.
99% of the execution is happening in the hash join of the query (nr 4) where it is joining 13k rows with the table in nr 36.
I have tried to use some hints (no_use_hash, index) but have been unable to get better results.