Hi All,
I have below which taking 14 minutes to complete for 30,360 rows.
Here is the gather statistics.
SELECT /*+ gather_plan_statistics*/ DISTINCT
rct.org_id,
hzp.party_name,
hca.account_number,
rct.interface_header_attribute1 order_number,
rct.customer_trx_id,
rct.trx_number,
rct.trx_date,
rctd.gl_date,
rct.creation_date,
rctl.line_number,
rct.invoice_currency_code inv_currency,
(
SELECT SUM (rct_1.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_1
WHERE rct_1.customer_trx_id = rct.customer_trx_id
AND rct_1.line_type = 'LINE') inv_net_amount,
(
SELECT SUM (rct_2.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_2
WHERE rct_2.customer_trx_id = rct.customer_trx_id
AND rct_2.line_type = 'TAX') inv_tax_amount,
(
SELECT SUM (rct_3.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_3
WHERE rct_3.customer_trx_id = rct.customer_trx_id) inv_gross_amount,
gll.currency_code func_currency,
Round((
(
SELECT SUM (rct_4.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_4
WHERE rct_4.customer_trx_id = rct.customer_trx_id
AND rct_4.line_type = 'LINE')*gdr.conversion_rate),2) func_net_amount,
Round((
(
SELECT SUM (rct_5.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_5
WHERE rct_5.customer_trx_id = rct.customer_trx_id
AND rct_5.line_type = 'TAX')*gdr.conversion_rate),2) func_tax_amount,
Round((
(
SELECT SUM (rct_6.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_6
WHERE rct_6.customer_trx_id = rct.customer_trx_id)*gdr.conversion_rate),2) func_gross_amount,
glcc.segment1 company,
glcc.segment2 account,
hg.geography_name billing_country,
gdr.conversion_rate
FROM apps.hz_parties hzp,
apps.hz_cust_accounts hca,
apps.ra_customer_trx_all rct,
apps.ra_customer_trx_lines_all rctl,
apps.ra_cust_trx_line_gl_dist_all rctd,
apps.gl_code_combinations_kfv glcc,
apps.hz_cust_site_uses_all hcsua,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_geographies hg,
apps.gl_ledgers gll,
apps.gl_daily_rates gdr
WHERE hzp.party_id = hca.party_id
AND hca.cust_account_id = rct.bill_to_customer_id
AND hca.cust_account_id = hcasa.cust_account_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND glcc.code_combination_id = rctd.code_combination_id
AND rct.bill_to_site_use_id = hcsua.site_use_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = hg.country_code
AND hg.geography_type = 'COUNTRY'
AND rctl.line_type = 'TAX'
AND gll.ledger_id = rct.set_of_books_id
AND gdr.from_currency = rct.invoice_currency_code
AND gdr.to_currency = gll.currency_code
AND to_date(gdr.conversion_date) = to_date(rctd.gl_date)
AND gdr.conversion_type = 'Corporate'
AND rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND To_date ('31-JAN-2018', 'DD-MON-YYYY')
AND glcc.segment1 = '2600'
AND glcc.segment2 = '206911'
GROUP BY hzp.party_name,
hca.account_number,
rct.interface_header_attribute1,
rct.trx_number,
rct.trx_date,
rct.creation_date,
rctl.line_number,
rctl.unit_selling_price,
rct.org_id,
rctd.gl_date,
rct.customer_trx_id,
glcc.segment1,
glcc.segment2,
hg.geography_name,
rct.invoice_currency_code,
gll.currency_code,
gdr.conversion_rate
UNION ALL
SELECT /*+ gather_plan_statistics*/ DISTINCT
rct.org_id,
hzp.party_name,
hca.account_number,
rct.interface_header_attribute1 order_number,
rct.customer_trx_id,
rct.trx_number,
rct.trx_date,
rctd.gl_date,
rct.creation_date,
rctl.line_number,
rct.invoice_currency_code inv_currency,
(
SELECT SUM (rct_1.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_1
WHERE rct_1.customer_trx_id = rct.customer_trx_id
AND rct_1.line_type = 'LINE') inv_net_amount,
(
SELECT SUM (rct_2.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_2
WHERE rct_2.customer_trx_id = rct.customer_trx_id
AND rct_2.line_type = 'TAX') inv_tax_amount,
(
SELECT SUM (rct_3.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_3
WHERE rct_3.customer_trx_id = rct.customer_trx_id) inv_gross_amount,
gll.currency_code func_currency,
round((
(
SELECT SUM (rct_4.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_4
WHERE rct_4.customer_trx_id = rct.customer_trx_id
AND rct_4.line_type = 'LINE')*1),2) func_net_amount,
round((
(
SELECT SUM (rct_5.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_5
WHERE rct_5.customer_trx_id = rct.customer_trx_id
AND rct_5.line_type = 'TAX')*1),2) func_tax_amount,
round((
(
SELECT SUM (rct_6.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_6
WHERE rct_6.customer_trx_id = rct.customer_trx_id)*1),2) func_gross_amount,
glcc.segment1 company,
glcc.segment2 account,
hg.geography_name billing_country,
1 conversion_rate
FROM apps.hz_parties hzp,
apps.hz_cust_accounts hca,
apps.ra_customer_trx_all rct,
apps.ra_customer_trx_lines_all rctl,
apps.ra_cust_trx_line_gl_dist_all rctd,
apps.gl_code_combinations_kfv glcc,
apps.hz_cust_site_uses_all hcsua,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_geographies hg,
apps.gl_ledgers gll
WHERE hzp.party_id = hca.party_id
AND hca.cust_account_id = rct.bill_to_customer_id
AND hca.cust_account_id = hcasa.cust_account_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND glcc.code_combination_id = rctd.code_combination_id
AND rct.bill_to_site_use_id = hcsua.site_use_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hl.country = hg.country_code
AND hg.geography_type = 'COUNTRY'
AND glcc.segment1 = '2600'
AND glcc.segment2 = '206911'
AND rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND To_date ('31-JAN-2018', 'DD-MON-YYYY')
AND rctl.line_type = 'TAX'
AND gll.ledger_id = rct.set_of_books_id
AND rct.invoice_currency_code = gll.currency_code
GROUP BY hzp.party_name,
hca.account_number,
rct.interface_header_attribute1,
rct.trx_number,
rct.trx_date,
rct.creation_date,
rctl.line_number,
rctl.unit_selling_price,
rct.org_id,
rctd.gl_date,
rct.customer_trx_id,
glcc.segment1,
glcc.segment2,
hg.geography_name,
rct.invoice_currency_code,
gll.currency_code;
PLAN_TABLE_OUTPUT
SQL_ID 6dunkgnhhnjkr, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/ DISTINCT
rct.org_id, hzp.party_name,
hca.account_number, rct.interface_header_attribute1
order_number, rct.customer_trx_id,
rct.trx_number, rct.trx_date,
rctd.gl_date, rct.creation_date,
rctl.line_number, rct.invoice_currency_code
inv_currency, ( SELECT SUM
(rct_1.extended_amount) FROM
apps.ra_customer_trx_lines_all rct_1 WHERE
rct_1.customer_trx_id = rct.customer_trx_id AND
rct_1.line_type = 'LINE') inv_net_amount, (
SELECT SUM (rct_2.extended_amount)
FROM apps.ra_customer_trx_lines_all rct_2
WHERE rct_2.customer_trx_id = rct.customer_trx_id
Plan hash value: 1502822854
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 501 |00:13:20.17 | 3579K|
| 1 | UNION-ALL | | 1 | | 501 |00:13:20.17 | 3579K|
| 2 | HASH UNIQUE | | 1 | 1 | 501 |00:13:20.17 | 3579K|
| 3 | HASH GROUP BY | | 1 | 1 | 19827 |00:13:20.15 | 3579K|
| 4 | NESTED LOOPS | | 1 | | 21808 |00:13:10.26 | 3579K|
| 5 | NESTED LOOPS | | 1 | 1 | 21808 |00:13:10.11 | 3578K|
| 6 | NESTED LOOPS OUTER | | 1 | 1 | 21808 |00:13:09.90 | 3576K|
| 7 | NESTED LOOPS OUTER | | 1 | 1 | 21808 |00:13:09.25 | 3501K|
| 8 | NESTED LOOPS OUTER | | 1 | 1 | 21808 |00:13:08.48 | 3426K|
| 9 | NESTED LOOPS OUTER | | 1 | 1 | 21808 |00:13:07.66 | 3333K|
| 10 | NESTED LOOPS OUTER | | 1 | 1 | 21808 |00:13:06.92 | 3258K|
| 11 | NESTED LOOPS OUTER | | 1 | 1 | 21808 |00:13:06.08 | 3183K|
| 12 | NESTED LOOPS | | 1 | 1 | 21808 |00:13:04.69 | 3090K|
| 13 | NESTED LOOPS | | 1 | 1 | 21808 |00:13:05.75 | 3026K|
| 14 | NESTED LOOPS | | 1 | 1 | 21808 |00:13:03.30 | 2961K|
| 15 | NESTED LOOPS | | 1 | 1 | 33459 |00:00:04.33 | 1123K|
| 16 | NESTED LOOPS | | 1 | 351 | 33459 |00:00:03.67 | 1025K|
| 17 | NESTED LOOPS | | 1 | 351 | 33459 |00:00:03.06 | 926K|
| 18 | NESTED LOOPS | | 1 | 351 | 33459 |00:00:02.47 | 827K|
|* 19 | HASH JOIN | | 1 | 351 | 33459 |00:00:01.90 | 730K|
| 20 | TABLE ACCESS FULL | GL_LEDGERS | 1 | 38 | 39 |00:00:00.01 | 15 |
| 21 | NESTED LOOPS | | 1 | | 33459 |00:00:01.75 | 730K|
| 22 | NESTED LOOPS | | 1 | 351 | 33459 |00:00:01.44 | 696K|
| 23 | NESTED LOOPS | | 1 | 351 | 33459 |00:00:01.11 | 646K|
|* 24 | HASH JOIN | | 1 | 385 | 33459 |00:00:00.40 | 526K|
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS | 1 | 35 | 1 |00:00:00.01 | 108 |
|* 26 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N2 | 1 | 499 | 77 |00:00:00.01 | 3 |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL | 1 | 651K| 1458K|00:00:02.22 | 526K|
|* 28 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 1 | 728K| 1820K|00:00:01.60 | 11147 |
|* 29 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 33459 | 1 | 33459 |00:00:00.53 | 119K|
|* 30 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 33459 | 1 | 33459 |00:00:00.31 | 86364 |
|* 31 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 33459 | 1 | 33459 |00:00:00.21 | 49850 |
| 32 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 33459 | 1 | 33459 |00:00:00.20 | 33459 |
| 33 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 33459 | 1 | 33459 |00:00:00.42 | 97887 |
|* 34 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 33459 | 1 | 33459 |00:00:00.24 | 64428 |
| 35 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 33459 | 1 | 33459 |00:00:00.44 | 98783 |
|* 36 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 33459 | 1 | 33459 |00:00:00.26 | 65175 |
| 37 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 33459 | 1 | 33459 |00:00:00.46 | 98374 |
|* 38 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 33459 | 1 | 33459 |00:00:00.28 | 64915 |
|* 39 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 33459 | 1 | 33459 |00:00:00.45 | 98195 |
|* 40 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 33459 | 1 | 33459 |00:00:00.26 | 64736 |
| 41 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_DAILY_RATES | 33459 | 1 | 21808 |00:12:44.59 | 1838K|
|* 42 | INDEX RANGE SCAN | GL_DAILY_RATES_U1 | 33459 | 1 | 21808 |00:13:08.16 | 1837K|
| 43 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 21808 | 1 | 21808 |00:00:00.35 | 64339 |
|* 44 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 21808 | 1 | 21808 |00:00:00.23 | 42531 |
| 45 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 21808 | 1 | 21808 |00:00:00.33 | 64353 |
|* 46 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 21808 | 1 | 21808 |00:00:00.18 | 42545 |
| 47 | VIEW PUSHED PREDICATE | VW_SSQ_1 | 21808 | 1 | 21808 |00:00:01.17 | 93476 |
| 48 | SORT GROUP BY | | 21808 | 1 | 21808 |00:00:01.06 | 93476 |
| 49 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 21808 | 16 | 145K|00:00:00.84 | 93476 |
|* 50 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 21808 | 16 | 145K|00:00:00.36 | 59938 |
| 51 | VIEW PUSHED PREDICATE | VW_SSQ_2 | 21808 | 1 | 21808 |00:00:00.69 | 74433 |
| 52 | SORT GROUP BY | | 21808 | 1 | 21808 |00:00:00.59 | 74433 |
| 53 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 21808 | 8 | 92201 |00:00:00.49 | 74433 |
|* 54 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 21808 | 12 | 92201 |00:00:00.24 | 59903 |
| 55 | VIEW PUSHED PREDICATE | VW_SSQ_3 | 21808 | 1 | 21808 |00:00:00.61 | 74852 |
| 56 | SORT GROUP BY | | 21808 | 1 | 21808 |00:00:00.51 | 74852 |
| 57 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 21808 | 8 | 53060 |00:00:00.38 | 74852 |
|* 58 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 21808 | 12 | 53060 |00:00:00.19 | 59148 |
| 59 | VIEW PUSHED PREDICATE | VW_SSQ_4 | 21808 | 1 | 21808 |00:00:00.70 | 93490 |
| 60 | SORT GROUP BY | | 21808 | 1 | 21808 |00:00:00.61 | 93490 |
| 61 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 21808 | 16 | 145K|00:00:00.63 | 93490 |
|* 62 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 21808 | 16 | 145K|00:00:00.25 | 59950 |
| 63 | VIEW PUSHED PREDICATE | VW_SSQ_5 | 21808 | 1 | 21808 |00:00:00.63 | 74427 |
| 64 | SORT GROUP BY | | 21808 | 1 | 21808 |00:00:00.54 | 74427 |
| 65 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 21808 | 8 | 92201 |00:00:00.44 | 74427 |
|* 66 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 21808 | 12 | 92201 |00:00:00.21 | 59900 |
| 67 | VIEW PUSHED PREDICATE | VW_SSQ_6 | 21808 | 1 | 21808 |00:00:00.59 | 74846 |
| 68 | SORT GROUP BY | | 21808 | 1 | 21808 |00:00:00.50 | 74846 |
| 69 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 21808 | 8 | 53060 |00:00:00.35 | 74846 |
|* 70 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 21808 | 12 | 53060 |00:00:00.17 | 59144 |
|* 71 | INDEX RANGE SCAN | HZ_GEOGRAPHIES_N11 | 21808 | 5812 | 21808 |00:00:00.13 | 2684 |
| 72 | TABLE ACCESS BY INDEX ROWID | HZ_GEOGRAPHIES | 21808 | 168 | 21808 |00:00:00.07 | 620 |
| 73 | HASH UNIQUE | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 74 | HASH GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 75 | NESTED LOOPS OUTER | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 76 | NESTED LOOPS OUTER | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 77 | NESTED LOOPS OUTER | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 78 | NESTED LOOPS OUTER | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 79 | NESTED LOOPS OUTER | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 80 | NESTED LOOPS OUTER | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 81 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 82 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 83 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 84 | NESTED LOOPS | | 0 | 351 | 0 |00:00:00.01 | 0 |
| 85 | NESTED LOOPS | | 0 | 351 | 0 |00:00:00.01 | 0 |
| 86 | NESTED LOOPS | | 0 | 351 | 0 |00:00:00.01 | 0 |
| 87 | NESTED LOOPS | | 0 | 351 | 0 |00:00:00.01 | 0 |
|* 88 | HASH JOIN | | 0 | 351 | 0 |00:00:00.01 | 0 |
| 89 | TABLE ACCESS FULL | GL_LEDGERS | 0 | 38 | 0 |00:00:00.01 | 0 |
| 90 | NESTED LOOPS | | 0 | | 0 |00:00:00.01 | 0 |
| 91 | NESTED LOOPS | | 0 | 351 | 0 |00:00:00.01 | 0 |
| 92 | NESTED LOOPS | | 0 | 351 | 0 |00:00:00.01 | 0 |
|* 93 | HASH JOIN | | 0 | 385 | 0 |00:00:00.01 | 0 |
|* 94 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_CODE_COMBINATIONS | 0 | 35 | 0 |00:00:00.01 | 0 |
|* 95 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N2 | 0 | 499 | 0 |00:00:00.01 | 0 |
|* 96 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUST_TRX_LINE_GL_DIST_ALL | 0 | 651K| 0 |00:00:00.01 | 0 |
|* 97 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 0 | 728K| 0 |00:00:00.01 | 0 |
|* 98 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 99 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
|*100 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 101 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 0 | 1 | 0 |00:00:00.01 | 0 |
| 102 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 0 | 1 | 0 |00:00:00.01 | 0 |
|*103 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 104 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 0 | 1 | 0 |00:00:00.01 | 0 |
|*105 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 106 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 0 | 1 | 0 |00:00:00.01 | 0 |
|*107 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 108 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 0 | 1 | 0 |00:00:00.01 | 0 |
|*109 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 110 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 0 | 1 | 0 |00:00:00.01 | 0 |
|*111 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 112 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 0 | 1 | 0 |00:00:00.01 | 0 |
|*113 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 114 | TABLE ACCESS BY INDEX ROWID BATCHED | HZ_GEOGRAPHIES | 0 | 168 | 0 |00:00:00.01 | 0 |
|*115 | INDEX RANGE SCAN | HZ_GEOGRAPHIES_N11 | 0 | 5812 | 0 |00:00:00.01 | 0 |
| 116 | VIEW PUSHED PREDICATE | VW_SSQ_7 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 117 | SORT GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 118 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 0 | 16 | 0 |00:00:00.01 | 0 |
|*119 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 0 | 16 | 0 |00:00:00.01 | 0 |
| 120 | VIEW PUSHED PREDICATE | VW_SSQ_8 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 121 | SORT GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 122 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 0 | 8 | 0 |00:00:00.01 | 0 |
|*123 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 0 | 12 | 0 |00:00:00.01 | 0 |
| 124 | VIEW PUSHED PREDICATE | VW_SSQ_9 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 125 | SORT GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 126 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 0 | 8 | 0 |00:00:00.01 | 0 |
|*127 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 0 | 12 | 0 |00:00:00.01 | 0 |
| 128 | VIEW PUSHED PREDICATE | VW_SSQ_10 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 129 | SORT GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 130 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 0 | 16 | 0 |00:00:00.01 | 0 |
|*131 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 0 | 16 | 0 |00:00:00.01 | 0 |
| 132 | VIEW PUSHED PREDICATE | VW_SSQ_11 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 133 | SORT GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 134 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 0 | 8 | 0 |00:00:00.01 | 0 |
|*135 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 0 | 12 | 0 |00:00:00.01 | 0 |
| 136 | VIEW PUSHED PREDICATE | VW_SSQ_12 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 137 | SORT GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 138 | TABLE ACCESS BY INDEX ROWID BATCHED | RA_CUSTOMER_TRX_LINES_ALL | 0 | 8 | 0 |00:00:00.01 | 0 |
|*139 | INDEX RANGE SCAN | XXC_CUSTOMER_GETPAID | 0 | 12 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
19 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID")
24 - access("CODE_COMBINATION_ID"="RCTD"."CODE_COMBINATION_ID")
25 - filter("SEGMENT1"='2600')
26 - access("SEGMENT2"='206911')
27 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)
28 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
29 - filter("RCTL"."LINE_TYPE"='TAX')
30 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")
31 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
34 - access("HCA"."CUST_ACCOUNT_ID"="RCT"."BILL_TO_CUSTOMER_ID")
36 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")
38 - access("RCT"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID")
39 - filter("HCA"."CUST_ACCOUNT_ID"="HCASA"."CUST_ACCOUNT_ID")
40 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID")
42 - access("GDR"."FROM_CURRENCY"="RCT"."INVOICE_CURRENCY_CODE" AND "GDR"."TO_CURRENCY"="GLL"."CURRENCY_CODE" AND
"GDR"."CONVERSION_TYPE"='Corporate')
filter(("GDR"."CONVERSION_TYPE"='Corporate' AND TO_DATE(INTERNAL_FUNCTION("GDR"."CONVERSION_DATE"))=TO_DATE(INTERNAL_FUNCTION("RCTD"."
GL_DATE"))))
44 - access("HCASA"."PARTY_SITE_ID"="HPS"."PARTY_SITE_ID")
46 - access("HPS"."LOCATION_ID"="HL"."LOCATION_ID")
50 - access("RCT_6"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID")
54 - access("RCT_5"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_5"."LINE_TYPE"='TAX')
58 - access("RCT_4"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_4"."LINE_TYPE"='LINE')
62 - access("RCT_3"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID")
66 - access("RCT_2"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_2"."LINE_TYPE"='TAX')
70 - access("RCT_1"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_1"."LINE_TYPE"='LINE')
71 - access("HL"."COUNTRY"="HG"."COUNTRY_CODE" AND "HG"."GEOGRAPHY_TYPE"='COUNTRY')
88 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID" AND "RCT"."INVOICE_CURRENCY_CODE"="GLL"."CURRENCY_CODE")
93 - access("CODE_COMBINATION_ID"="RCTD"."CODE_COMBINATION_ID")
94 - filter("SEGMENT1"='2600')
95 - access("SEGMENT2"='206911')
96 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)
97 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
98 - filter("RCTL"."LINE_TYPE"='TAX')
99 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")
100 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
103 - access("RCT"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID")
105 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID")
107 - access("HCASA"."PARTY_SITE_ID"="HPS"."PARTY_SITE_ID")
109 - access("HPS"."LOCATION_ID"="HL"."LOCATION_ID")
111 - access("HCA"."CUST_ACCOUNT_ID"="HCASA"."CUST_ACCOUNT_ID")
filter("HCA"."CUST_ACCOUNT_ID"="RCT"."BILL_TO_CUSTOMER_ID")
113 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")
115 - access("HL"."COUNTRY"="HG"."COUNTRY_CODE" AND "HG"."GEOGRAPHY_TYPE"='COUNTRY')
119 - access("RCT_6"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID")
123 - access("RCT_5"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_5"."LINE_TYPE"='TAX')
127 - access("RCT_4"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_4"."LINE_TYPE"='LINE')
131 - access("RCT_3"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID")
135 - access("RCT_2"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_2"."LINE_TYPE"='TAX')
139 - access("RCT_1"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID" AND "RCT_1"."LINE_TYPE"='LINE')
Note
-----
- this is an adaptive plan
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Could you please help me on this? which one is causing the issue.
Thanks in advanced.