Hi ,
My SQL statement is taking long time approximately 2.30 hr to complete.
I have query like below
Select from tables
UNION
Select from tables
Here is the Gather Stat Plan.
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1d06k67yqr4hf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ a.customer_trx_id ,
a.trx_number FROM RA_TERMS_LINES
TL, RA_CUST_TRX_TYPES TYPES,
AR_LOOKUPS L_TYPES, HZ_CUST_ACCOUNTS
B, HZ_PARTIES
PARTY, HZ_CUST_SITE_USES U_BILL,
HZ_CUST_ACCT_SITES A_BILL, HZ_PARTY_SITES
PARTY_SITE, HZ_LOCATIONS
LOC, AR_ADJUSTMENTS COM_ADJ,
RA_CUSTOMER_TRX A,
AR_PAYMENT_SCHEDULES P, RA_TERMS
T WHERE A.BILL_TO_CUSTOMER_ID = B.CUST_ACCOUNT_ID
AND P.PAYMENT_SCHEDULE_ID + DECODE(P.CLASS,'INV', 0,'')=
COM_ADJ.PAYMENT_SCHEDULE_ID(+) AND COM_ADJ.SUBSEQUENT_TRX_ID IS NULL
AND 'C' = COM_ADJ.ADJUSTMENT_TYPE(+) AND A.COMPLET
Plan hash value: 513571495
| Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |1 | | 0 |00:45:59.99 | 10M| 1899K| | | |
|* 1 | FILTER | |1 | | 0 |00:45:59.99 | 10M| 1899K| | | |
|* 2 | FILTER | |1 | | 0 |00:45:59.99 | 10M| 1899K| | ||
| 3 | NESTED LOOPS OUTER | |1 | 1 | 0 |00:45:59.99 | 10M| 1899K| | ||
| 4 | NESTED LOOPS | |1 | 1 | 0 |00:45:59.99 | 10M| 1899K| | ||
| 5 | NESTED LOOPS | |1 | 1 | 0 |00:45:59.99 | 10M| 1899K| | ||
| 6 | NESTED LOOPS OUTER | |1 | 1 | 0 |00:45:59.99 | 10M| 1899K| | ||
| 7 | NESTED LOOPS | |1 | 1 | 0 |00:45:59.99 | 10M| 1899K| | ||
| 8 | NESTED LOOPS | |1 | 1 | 65 |01:01:37.21 | 10M| 1899K| | ||
| 9 | NESTED LOOPS | |1 | 1 | 463K|03:10:49.57 | 9496K| 1878K| | ||
| 10 | NESTED LOOPS | |1 | 1 | 463K|02:59:26.33 | 8571K| 1768K| | ||
| 11 | NESTED LOOPS | |1 | 1 | 463K|02:31:59.26 | 7184K| 1504K| | ||
| 12 | NESTED LOOPS | |1 | 1 | 463K|01:53:32.62 | 5795K| 1147K| | ||
| 13 | NESTED LOOPS | |1 | 1 | 463K|01:19:43.64 | 4407K| 818K| | ||
| 14 | NESTED LOOPS | |1 | 1 | 463K|00:46:44.81 | 3053K| 492K| | ||
| 15 | NESTED LOOPS | |1 | 1 | 463K|00:07:05.60 | 1697K| 118K| | ||
|* 16 | HASH JOIN | |1 | 1 | 1127 |00:00:00.01 | 129 | 123 | 2440K| 2440K| 1396K (0)|
|* 17 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 |1 | 3 | 9 |00:00:00.01 | 3 | 0 | | |
|
|* 18 | TABLE ACCESS FULL | RA_CUST_TRX_TYPES_ALL |1 | 136 | 1127 |00:00:00.01 | 126 | 123 | | |
|
|* 19 | TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL |1127 | 27 | 463K|00:19:33.38 | 1697K| 118K| | |
|
|* 20 | INDEX RANGE SCAN | FLT_RA_CUSTOMER_TRX_N25 |1127 | 5908 | 7036K|00:00:16.16 | 19299 | 4720 | | |
|
|* 21 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL |463K | 1 | 463K|00:38:30.56 | 1355K| 374K| | |
|
|* 22 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 |463K | 1 | 463K|00:07:40.63 | 924K| 71565 | | |
|
|* 23 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL |463K | 1 | 463K|00:33:16.12 | 1354K| 326K| | |
|
|* 24 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 |463K | 1 | 463K|00:05:38.03 | 924K| 51801 | | |
|
| 25 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES |463K | 1 | 463K|00:34:17.05 | 1388K| 328K| | |
|
|* 26 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 |463K | 1 | 463K|00:05:50.01 | 924K| 52349 | | |
|
|* 27 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS |463K | 1 | 463K|00:38:02.28 | 1388K| 356K| | |
|
|* 28 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 |463K | 1 | 463K|00:05:30.13 | 924K| 49544 | | |
|
| 29 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS |463K | 1 | 463K|00:27:50.01 | 1387K| 264K| | |
|
|* 30 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 |463K | 1 | 463K|00:02:38.97 | 923K| 23863 | | |
|
|* 31 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 |463K | 1 | 463K|00:11:59.18 | 925K| 109K| | |
|
|* 32 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL |463K | 1 | 65 |00:02:22.72 | 663K| 21146 | | |
|
|* 33 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_N2 |463K | 2 | 469K|00:00:30.44 | 502K| 4253 | | |
|
|* 34 | TABLE ACCESS BY INDEX ROWID | RA_TERMS_B |65 | 1 | 0 |00:00:00.01 | 69 | 0 | | |
|
|* 35 | INDEX UNIQUE SCAN | RA_TERMS_B_U1 |65 | 1 | 65 |00:00:00.01 | 4 | 0 | | |
|
|* 36 | INDEX UNIQUE SCAN | AR_CONS_BILL_CYCLES_TL_U1 |0 | 1 | 0 |00:00:00.01 | 0 | 0 | | |
|
|* 37 | INDEX UNIQUE SCAN | RA_TERMS_TL_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | |
|
|* 38 | INDEX UNIQUE SCAN | RA_TERMS_LINES_U1 |0 | 1 | 0 |00:00:00.01 | 0 | 0 | | |
|
|* 39 | TABLE ACCESS BY INDEX ROWID | AR_ADJUSTMENTS_ALL | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | |
|
|* 40 | INDEX RANGE SCAN | AR_ADJUSTMENTS_N3 |0 | 2 | 0 |00:00:00.01 | 0 | 0 | | |
|
| 41 | NESTED LOOPS | |0 | 1 | 0 |00:00:00.01 | 0 | 0 | | |
|
|* 42 | INDEX UNIQUE SCAN | ECE_TP_HEADERS_U1 |0 | 1 | 0 |00:00:00.01 | 0 | 0 | | ||
|* 43 | TABLE ACCESS BY INDEX ROWID | ECE_TP_DETAILS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | ||
|* 44 | INDEX UNIQUE SCAN | ECE_TP_DETAILS_U2 |0 | 1 | 0 |00:00:00.01 | 0 | 0 | | ||
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
2 - filter("SUBSEQUENT_TRX_ID" IS NULL)
16 - access("LV"."LOOKUP_CODE"=DECODE("TYPE",'DEP','INV',"TYPE"))
17 - access("LV"."LOOKUP_TYPE"='INV/CM/ADJ' AND "LV"."VIEW_APPLICATION_ID"=222
AND "LV"."SECURITY_GROUP_ID"=0 AND "LV"."LANGUAGE"=USERENV('LANG'))
filter(("LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"=0))
18 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))
19 - filter(("PRINTING_PENDING"='Y' AND INTERNAL_FUNCTION("PRINTING_OPTION") A
ND "ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')) AND
"COMPLETE_FLAG"='Y'))
20 - access("CUST_TRX_TYPE_ID"="CUST_TRX_TYPE_ID")
21 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))
22 - access("BILL_TO_SITE_USE_ID"="SITE_USE_ID")
23 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))
24 - access("CUST_ACCT_SITE_ID"="CUST_ACCT_SITE_ID")
26 - access("PARTY_SITE_ID"="PARTY_SITE"."PARTY_SITE_ID")
27 - filter(NVL("LOC"."LANGUAGE",'US')='US')
28 - access("LOC"."LOCATION_ID"="PARTY_SITE"."LOCATION_ID")
30 - access("BILL_TO_CUSTOMER_ID"="B"."CUST_ACCOUNT_ID")
31 - access("B"."PARTY_ID"="PARTY"."PARTY_ID")
32 - filter(("TERM_ID" IS NOT NULL AND "STATUS"='OP' AND INTERNAL_FUNCTION("OR
G_ID") AND "AMOUNT_DUE_REMAINING"<>0 AND
"TERMS_SEQUENCE_NUMBER">NVL("LAST_PRINTED_SEQUENCE_NUM",0)))
33 - access("CUSTOMER_TRX_ID"="CUSTOMER_TRX_ID")
filter("CUSTOMER_TRX_ID" IS NOT NULL)
34 - filter(NVL("B"."PRINTING_LEAD_DAYS",0)>0)
35 - access("B"."TERM_ID"="TERM_ID")
36 - access("B"."BILLING_CYCLE_ID"="BC"."BILLING_CYCLE_ID" AND "BC"."LANGUAGE"
=USERENV('LANG'))
37 - access("B"."TERM_ID"="T"."TERM_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
38 - access("TL"."TERM_ID"="B"."TERM_ID" AND "TL"."SEQUENCE_NUM"="TERMS_SEQUEN
CE_NUMBER")
39 - filter(("ADJUSTMENT_TYPE"='C' AND "ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_o
rg2','current_org_id'))))
40 - access("PAYMENT_SCHEDULE_ID"="PAYMENT_SCHEDULE_ID"+DECODE("CLASS",'INV',0
,NULL))
42 - access("ETH"."TP_HEADER_ID"=:B1)
43 - filter("ETD"."EDI_FLAG"='Y')
44 - access("ETD"."TP_HEADER_ID"=:B1 AND "ETD"."DOCUMENT_ID"='INO' AND "ETD"."
DOCUMENT_TYPE"=DECODE(:B2,'CM',DECODE(TO_CHAR(:B3),NULL,'OACM','CM'),:B4))
106 rows selected.
when i user /*+ PARALLEL(TYPES,4) PARALLEL(L_TYPES,4) PARALLEL(COM_ADJ,4) LEADING(P,4)*/ it was taking 14 to 20 minutes to complete.
Oracle Version: 11.2
Thanks
I suspect you have a statistics accuracy issue.
Look at line 19 - the database *estimated* we'd get 27 rows back RA_CUSTOMER_TRX_ALL. We actually got 463K !!!
It's very quickly going to be all downhill from there, because with a small estimate, we'll lean toward nested loops and index access, and doing those things thousands or millions of times will kill your sql performance.
So start with that table - look at ensuring up to date statistics, and possibly extended statistics on some of the columns that are critical to the predicates:
filter(("PRINTING_PENDING"='Y' AND INTERNAL_FUNCTION("PRINTING_OPTION") AND "ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')) AND ...
Get that estimate correct, and the rest might fall into place naturally.