- 3,728,222 Users
- 2,245,576 Discussions
- 7,853,386 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.6K All Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.4K Databases
- 699 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 499 MySQL Community Space
- 10 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 426 SQLcl
- 69 SQL Developer Data Modeler
- 185.2K SQL & PL/SQL
- 21.1K SQL Developer
- 2.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 136K Development Tools
- 18 DevOps
- 3K QA/Testing
- 388 Java
- 16 Java Learning Subscription
- 14 Database Connectivity
- 72 Java Community Process
- 3 Java 25
- 14 Java APIs
- 141.2K Java Development Tools
- 15 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 18 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 159 LiveLabs
- 36 Workshops
- 13 Software
- 7 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 19 Español
- 1.9K Japanese
- 4 Portuguese
Select Query performance
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_OUTPUTSQL_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_idPlan 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 planOracle 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.
Answers
-
You're doing a large amount of work filtering data from GL_DAILY_RATES presumably largely because you're applying a function to the date column.
The name of the column suggests it's already a date.
TO_DATE usage on a DATE would be wrong.
TO_DATE usage without a format mask would also be wrong.
AND to_date(gdr.conversion_date) = to_date(rctd.gl_date)
| 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|
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"))))So, you use access predicates on the index to get all these dates matching from_currency, to_currency and conversion_type - I imagine that could be a lot - and then you have to throw away all those dates you're not interested in AFTER you've applied a function to the date.
I presume the index is on the unadulterated CONVERSION_DATE field therefore you have to modify your SQL logic to make the most of that and question why that TO_DATE function is erroneously being applied there.
Assuming that both these columns are both dates then do either of them have a non-"zero" time portion.
If not then you don't need any function.
AND gdr.conversion_date = rctd.gl_date
If they do have times then you're probably after either BETWEEN or >= and <
AND gdr.conversion_date >= trunc(rctd.gl_date) AND gdr.conversion_date < trunc(rctd.gl_date)+1
-
Thank you, let me try without using to_date and let you know.
-
Rajesh123, I see another potential logic problem in the SQL. If the author is trying to compare against all days in January then I think the BETWEEN is written wrong:
- -
" rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND To_date ('31-JAN-2018', 'DD-MON-YYYY') "
- -
The end date would default to midnight on the 31 and what is really desired is < 1-Feb-2018. Either the time 23:59:59 needs to be provided as part of the end date test or as is my preference the code should be rewritten as
rctd.gl_date >= To_date ('01-JAN-2018', 'DD-MON-YYYY') and rctd.gl_date < to_date(01-FEB-2018','DD-MON-YYYY')
- -
HTH -- Mark D Powell --
-
You are projecting six (six!) scalar subqueries. You could rewrite them to a join to just one subquery. Something like
select
sum(case line_type='LINE' then extended_amount else 0 end) inv_net_amount,
sum(case line_type='TAX' then extended_amount else 0 end) inv_tax_amount,
and so on.
-
JohnWatson2 wrote:You are projecting six (six!) scalar subqueries. You could rewrite them to a join to just one subquery. Something likeselectsum(case line_type='LINE' then extended_amount else 0 end) inv_net_amount,sum(case line_type='TAX' then extended_amount else 0 end) inv_tax_amount,and so on.
You missed the union all, there's 12!
But we can see from the row source execution statistics that Oracle spent a grand total of 4 seconds doing this. Also, since 12c Oracle has been able to unnest scalar subqueries itself, it has in this circumstance (which is why we see the VW_SSQ_ "views" in the plan) but this is quite limited and obviously didn't manage to see that they all share similar filters with slight modifications.
I think Dom is pointing in the right direction.
-
I agree;
And while we can't always trust the timing information on rowsource execution stats when the number of times a line runs is high and it's workload is small, there is some confirmation in line 42 itself that the work done is high: 33,000 starts to do a range scan leading to 1.8M buffer gets means the average range scan is about 60 blocks - which could be a very large number of index entries examined (with an expensive double-conversion of a data column) to find an average of less than one row.
Regards
Jonathan Lewis
-
Rajesh,
Your code and plan were long enough to offer a few interesting points, but short enough to make it possible to say something fairly constructive in a blog note - so I've written about it on my blog: https://jonathanlewis.wordpress.com/2018/09/30/case-study-2/
You might be particularly interested in the closing warning - the result you get from your query may depend on the nls_date_format set by the user.
Regards
Jonathan Lewis
-
Thank you Andrew,
I removed to_date from date columns still same plan it is showing.
-
Rajesh123 wrote:Thank you Andrew,I removed to_date from date columns still same plan it is showing.
Hard to help without seeing the new query and full execution plan
-
Thanks a lot excellent explanation.
-
Rajesh123,
Thanks.
A point I failed to make in the note (which I have now added) is that getting rid of the double conversion on the gdr.conversion rate may allow Oracle to do a much more precise index range scan - and if the performance is still not good enough the next step would be to see if there is a more precise index (perhaps very similar to the one Oracle is using with the columns in a different order) so that none of the access predicates end up also being filter predicates.
Regards
Jonathan Lewis
-
Hi John,
Thanks, i will get wrong output.
for Line Amount is 100, TAX has 0.
Populating 0 values for LINE type records , but it should be 100
Thank you
-
Hi ,
Here is the latest statistics , taking 7.33 minutes for 5693 rows.
Could you please help me on this?
SELECT /*+ gather_plan_statistics*/ DISTINCT 2, 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, sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end) inv_tax_amount, gll.currency_code func_currency, Round(sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end)*gdr.conversion_rate,2) func_tax_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_idAND hca.cust_account_id = hcasa.cust_account_idAND 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 (gdr.conversion_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 ('10-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;PLAN_TABLE_OUTPUTSQL_ID 7pz8x347z52k6, child number 0-------------------------------------SELECT /*+ gather_plan_statistics*/ DISTINCT 2, 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, sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end) inv_tax_amount, gll.currency_code func_currency, Round(sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end)*gdr.conversion_rate,2) func_tax_amount, glcc.segment1 company, glcc.segment2 accouPlan hash value: 1975678822------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 5693 |00:07:33.87 | 37M| 20179 || 1 | HASH UNIQUE | | 1 | 1 | 5693 |00:07:33.87 | 37M| 20179 || 2 | HASH GROUP BY | | 1 | 1 | 5693 |00:07:33.85 | 37M| 20179 || 3 | NESTED LOOPS | | 1 | | 6104 |00:07:33.80 | 37M| 20179 || 4 | NESTED LOOPS | | 1 | 1 | 6104 |00:07:31.11 | 37M| 16384 ||* 5 | HASH JOIN | | 1 | 1 | 6104 |00:07:29.13 | 37M| 13648 || 6 | NESTED LOOPS | | 1 | 1 | 6104 |00:07:29.05 | 37M| 13638 || 7 | NESTED LOOPS | | 1 | 1 | 6104 |00:07:26.75 | 37M| 10466 || 8 | NESTED LOOPS | | 1 | 1 | 6104 |00:07:24.71 | 37M| 7667 || 9 | NESTED LOOPS | | 1 | 1 | 6104 |00:07:22.76 | 37M| 5007 || 10 | NESTED LOOPS | | 1 | 1 | 6104 |00:07:20.58 | 37M| 2003 ||* 11 | HASH JOIN | | 1 | 1 | 6104 |00:07:19.09 | 37M| 2 || 12 | NESTED LOOPS | | 1 | 1 | 411K|00:07:16.44 | 37M| 2 || 13 | NESTED LOOPS | | 1 | 2 | 18M|00:04:09.60 | 18M| 2 ||* 14 | HASH JOIN | | 1 | 3 | 18M|00:00:22.50 | 156K| 2 || 15 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_DAILY_RATES | 1 | 129 | 19800 |00:00:00.15 | 14981 | 0 ||* 16 | INDEX SKIP SCAN | GL_DAILY_RATES_U1 | 1 | 129 | 19800 |00:00:00.10 | 5117 | 0 ||* 17 | HASH JOIN | | 1 | 138 | 9354 |00:00:02.34 | 141K| 2 ||* 18 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS | 1 | 35 | 1 |00:00:00.01 | 108 | 0 ||* 19 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N2 | 1 | 499 | 77 |00:00:00.01 | 3 | 0 ||* 20 | TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL | 1 | 229K| 370K|00:00:01.48 | 141K| 2 ||* 21 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 1 | 256K| 451K|00:00:00.47 | 2763 | 2 ||* 22 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 18M| 1 | 18M|00:02:37.71 | 18M| 0 ||* 23 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 18M| 1 | 18M|00:00:53.52 | 58940 | 0 ||* 24 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 18M| 1 | 411K|00:02:23.39 | 18M| 0 ||* 25 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 18M| 1 | 18M|00:00:52.92 | 28380 | 0 || 26 | TABLE ACCESS FULL | GL_LEDGERS | 1 | 1 | 39 |00:00:00.01 | 15 | 0 || 27 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 6104 | 1 | 6104 |00:00:01.45 | 18161 | 2001 ||* 28 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 6104 | 1 | 6104 |00:00:00.19 | 12057 | 197 || 29 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 6104 | 1 | 6104 |00:00:02.12 | 18196 | 3004 ||* 30 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 6104 | 1 | 6104 |00:00:00.56 | 12092 | 759 ||* 31 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 6104 | 1 | 6104 |00:00:01.93 | 17285 | 2660 ||* 32 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 6104 | 1 | 6104 |00:00:00.56 | 12059 | 767 || 33 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 6104 | 1 | 6104 |00:00:02.01 | 18177 | 2799 ||* 34 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 6104 | 1 | 6104 |00:00:00.60 | 12073 | 772 || 35 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 6104 | 1 | 6104 |00:00:02.27 | 18182 | 3172 ||* 36 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 6104 | 1 | 6104 |00:00:00.57 | 12078 | 765 || 37 | TABLE ACCESS BY INDEX ROWID BATCHED | HZ_GEOGRAPHIES | 1 | 168 | 257 |00:00:00.01 | 19 | 10 ||* 38 | INDEX SKIP SCAN | HZ_GEOGRAPHIES_N9 | 1 | 5812 | 257 |00:00:00.01 | 8 | 8 ||* 39 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 6104 | 1 | 6104 |00:00:01.96 | 12104 | 2736 || 40 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 6104 | 1 | 6104 |00:00:02.66 | 6124 | 3795 |------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("HL"."COUNTRY"="HG"."COUNTRY_CODE") 11 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID" AND "GDR"."TO_CURRENCY"="GLL"."CURRENCY_CODE") 14 - access("GDR"."CONVERSION_DATE"="RCTD"."GL_DATE" AND TRUNC(INTERNAL_FUNCTION("GL_DATE"))=TRUNC(INTERNAL_FUNCTION("GDR"."CONVERSION_DATE" ))) 16 - access("GDR"."CONVERSION_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GDR"."CONVERSION_TYPE"='Corporate' AND "GDR"."CONVERSION_DATE"<=TO_DATE(' 2018-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter(("GDR"."CONVERSION_TYPE"='Corporate' AND "GDR"."CONVERSION_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GDR"."CONVERSION_DATE"<=TO_DATE(' 2018-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 17 - access("CODE_COMBINATION_ID"="RCTD"."CODE_COMBINATION_ID") 18 - filter("SEGMENT1"='2600') 19 - access("SEGMENT2"='206911') 20 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL) 21 - 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-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 22 - filter("RCTL"."LINE_TYPE"='TAX') 23 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID") 24 - filter("GDR"."FROM_CURRENCY"="RCT"."INVOICE_CURRENCY_CODE") 25 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID") 28 - access("HCA"."CUST_ACCOUNT_ID"="RCT"."BILL_TO_CUSTOMER_ID") 30 - access("RCT"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID") 31 - filter("HCA"."CUST_ACCOUNT_ID"="HCASA"."CUST_ACCOUNT_ID") 32 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID") 34 - access("HCASA"."PARTY_SITE_ID"="HPS"."PARTY_SITE_ID") 36 - access("HPS"."LOCATION_ID"="HL"."LOCATION_ID") 38 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY') filter("HG"."GEOGRAPHY_TYPE"='COUNTRY') 39 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")Note----- - this is an adaptive plan -
Ignoring the scalar subqueries, your previous plan followed a reasonable join order and method for addressing the query with just the unfortunate problem that the nested loop join into the GL_DAILY_RATES table was using what look liked a highly appropriate index to find exactly one row in the table in a very expensive way thanks to a badly written predicate.
As a first step you need to understand why the index use was so expensive, and see if you can find a way of using that index (or another index which you may have to created) to get to that one row very efficiently.
What is the index definition for gl_daily_rates_u1, and what are the column types of the columns that are (directly, or indirectly) trying to join to the date column in gl_daily_rates. In particularly is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all.gl_date, and where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.
Regards
Jonathan Lewis
-
Jonathan Lewis wrote:Ignoring the scalar subqueries, your previous plan followed a reasonable join order and method for addressing the query with just the unfortunate problem that the nested loop join into the GL_DAILY_RATES table was using what look liked a highly appropriate index to find exactly one row in the table in a very expensive way thanks to a badly written predicate.As a first step you need to understand why the index use was so expensive, and see if you can find a way of using that index (or another index which you may have to created) to get to that one row very efficiently.What is the index definition for gl_daily_rates_u1, and what are the column types of the columns that are (directly, or indirectly) trying to join to the date column in gl_daily_rates. In particularly is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all.gl_date, and where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.RegardsJonathan Lewis
(1)
CREATE UNIQUE INDEX GL.GL_DAILY_RATES_U1 ON GL.GL_DAILY_RATES
(FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_TYPE)
(2)Varchar2
FROM_CURRENCY
TO_CURRENCY
CONVERSION_TYPE
CONVERSION_DATE --> Date
(3) is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all
Yes gl_date , invoices which are created in a particular month , i should use this column as parameter as i don't have another option.
(4)where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.
CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_UPG1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL
(CUSTOMER_TRX_ID, TRUNC("GL_DATE"), ACCOUNT_SET_FLAG)
CREATE INDEX AR.XXC_CUST_TRX__GL_DIST_N1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL
(GL_DATE, NVL("LATEST_REC_FLAG",'Y'), CODE_COMBINATION_ID)
CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_N1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL
(CUSTOMER_TRX_LINE_ID, ACCOUNT_SET_FLAG, GL_DATE, ACCOUNT_CLASS)
CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_N5 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL
(SET_OF_BOOKS_ID, POSTING_CONTROL_ID, GL_DATE)
-
Three points
1) In posting number 5 you said that you removed the to_date() and the plan was still the same.
How carefully did you check ? Did you actually try running it with rowsource execution stats enabled or did you just note that the plan looked the same of the plan hash value was the same. Did you check the predicate section - because the plan_hash_value and the "plan" could stay the same while the use of predicates changes and makes the plan more efficient. Please show us the plan with captured with rowsource excution stats enabled after the first 500 rows have been returned.
2) The index does not appear (from an intuitive viewpoint) to be the sensible option. It looks as if it ought to be "(from_currency, to_currency, conversion_type, conversion_date) compress 3" (And if the current ordering is actually ideal for some important queries it's possible that you still could do with another index with the same 4 columns in a different order with conversion_date at the end. In principle your original index shouldn't need this change as the current index looks as if it ought to be okay except Oracle has introduced that "to_date(internal_function(column_name)))" effect - the re-arranged index would at least minimise the overhead of that wierdness.
3) The trunc(gl_date) in the upg1 index shouldn't echo into the join condition. However, it has occurred to me that perhaps someone has added a couple of constraints to the tables of the form: (check gl_date = trunc(gl_date)), and the corresponding (check (conversion_date = trunc(conversion_date)). Can you check the two table definitions for check constraints on the columns.
Regards
Jonathan Lewis
-
In the current execution, the estimates are out quite badly.
So most of the time goes on NESTED LOOP row-by-row lookups on respectively 18 million and 411K rows before the join to GL_LEDGERS brings that back down to a few thousand.
At this point it would help to be more familiar with these tables to know how the query might be written differently (i.e. avoiding any significant manual hinting).
It's just heavier work if you don't know the tables you're dealing with...
i.e. we should be thinking about what the driving rowsources for this query should be and which are just boilerplate - i.e. the superfluous bits of additional information we need later once we have our final resultset.
RCTD + RCTL is presumably what really drives this query?
i.e. get me all the tax lines between these two dates.
-
This might be a giant waste of time but just following on from what I said before about driving data set, I've rearranged the query section you posted a couple of messages back.
There is about 0% chance that I haven't made a mistake somewhere as I've butchered it.
Also the formatting and rearranging is not a reflection on the original style, just a method for going through and double checking what it's all doing ( https://orastory.wordpress.com/2010/03/24/tuning_by_formatting/ ) which includes switching sides of some of the predicates - it's a bit mental I know...
Unfortunately though that comes with a good risk of messing something up along the way.
WITH subq_driving_data AS (SELECT /*+ no_merge */ rctd.gl_date , rctd.code_combination_id , rctl.line_number , rctl.extended_amount , rctl.line_type , rct.org_id, , rct.interface_header_attribute1 , rct.customer_trx_id , rct.trx_number , rct.trx_date , rct.creation_date , rct.invoice_currency_code , rct.bill_to_customer_id , rct.bill_to_site_use_id , gll.currency_code , (SELECT gdr.conversion_rate FROM apps.gl_daily_rates gdr WHERE gdr.from_currency = rct.invoice_currency_code AND gdr.to_currency = gll.currency_code AND gdr.conversion_date = rctd.gl_date AND gdr.conversion_type = 'Corporate') conversion_rate FROM apps.ra_cust_trx_line_gl_dist_all rctd , apps.ra_customer_trx_lines_all rctl , apps.ra_customer_trx_all rct , apps.gl_ledgers gll WHERE rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND To_date ('10-JAN-2018', 'DD-MON-YYYY') AND rctl.customer_trx_line_id = rctd.customer_trx_line_id AND rctl.line_type = 'TAX' AND rct.customer_trx_id = rctl.customer_trx_id AND gll.ledger_id = rct.set_of_books_id) SELECT /*+ gather_plan_statistics*/ DISTINCT 2 , dd.org_id, hzp.party_name, hca.account_number, dd.interface_header_attribute1 order_number, dd.customer_trx_id, dd.trx_number, dd.trx_date, dd.gl_date, dd.creation_date, dd.line_number, dd.invoice_currency_code inv_currency, sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end) inv_tax_amount, dd.currency_code func_currency , Round(sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end)*dd.conversion_rate,2) func_tax_amount, glcc.segment1 company, glcc.segment2 account,, hg.geography_name billing_country, dd.conversion_rateFROM subq_driving_data dd , apps.hz_parties hzp, apps.hz_cust_accounts hca, 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 hgWHERE hca.cust_account_id = dd.bill_to_customer_idAND hzp.party_id = hca.party_id AND hcsua.site_use_id = dd.bill_to_site_use_idAND hcasa.cust_account_id = hca.cust_account_id AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id AND hps.party_site_id = hcasa.party_site_idAND hl.location_id = hps.location_id AND hg.country_code = hl.country AND hg.geography_type = 'COUNTRY'AND glcc.code_combination_id = dd.code_combination_idAND glcc.segment1 = '2600' AND glcc.segment2 = '206911' GROUP BY hzp.party_name, hca.account_number, dd.interface_header_attribute1, dd.trx_number, dd.trx_date, dd.creation_date, dd.line_number, dd.unit_selling_price, dd.org_id, dd.gl_date, dd.customer_trx_id, glcc.segment1, glcc.segment2, hg.geography_name, dd.invoice_currency_code, dd.currency_code, dd.conversion_rate; -
Hi ,
I ran the query which was modified by you taking 9 minutes time to complete.
PLAN_TABLE_OUTPUTSQL_ID 3fmqn33ytdvj7, child number 0-------------------------------------WITH subq_driving_data AS (SELECT /*+ no_merge */ rctd.gl_date , rctd.code_combination_id , rctl.line_number , rctl.extended_amount , rctl.line_type , rct.org_id , rct.interface_header_attribute1 , rct.customer_trx_id , rct.trx_number , rct.trx_date , rct.creation_date , rct.invoice_currency_code , rct.bill_to_customer_id , rct.bill_to_site_use_id , gll.currency_code , (SELECT gdr.conversion_rate FROM apps.gl_daily_rates gdr WHERE gdr.from_currency = rct.invoice_currency_code AND gdr.to_currency = gll.currency_code AND gdr.conversion_date = rctd.gl_date AND gdr.conversion_type = 'Corporate') conversion_rate FROM apps.ra_cust_trx_line_gl_dist_all rctdPlan hash value: 357138763-----------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |-----------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 501 |00:09:16.40 | 9306K| 8194K|| 1 | TABLE ACCESS BY INDEX ROWID | GL_DAILY_RATES | 17504 | 1 | 7109 |00:00:00.13 | 38391 | 0 ||* 2 | INDEX UNIQUE SCAN | GL_DAILY_RATES_U1 | 17504 | 1 | 7109 |00:00:00.07 | 31282 | 0 || 3 | HASH GROUP BY | | 1 | 1 | 501 |00:09:16.40 | 9306K| 8194K|| 4 | NESTED LOOPS | | 1 | | 31898 |00:09:16.31 | 9306K| 8194K|| 5 | NESTED LOOPS | | 1 | 1 | 31898 |00:09:16.11 | 9274K| 8194K||* 6 | HASH JOIN | | 1 | 1 | 31898 |00:09:15.83 | 9212K| 8194K|| 7 | NESTED LOOPS | | 1 | 1 | 31898 |00:09:15.61 | 9211K| 8194K|| 8 | NESTED LOOPS | | 1 | 1 | 31898 |00:09:15.22 | 9118K| 8194K|| 9 | NESTED LOOPS | | 1 | 1 | 31898 |00:09:14.83 | 9025K| 8194K|| 10 | NESTED LOOPS | | 1 | 341 | 31898 |00:09:14.41 | 8938K| 8194K|| 11 | NESTED LOOPS | | 1 | 341 | 31898 |00:09:13.93 | 8844K| 8194K||* 12 | HASH JOIN | | 1 | 341 | 31898 |00:09:13.49 | 8751K| 8194K||* 13 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_CODE_COMBINATIONS | 1 | 35 | 1 |00:00:00.01 | 108 | 0 ||* 14 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N2 | 1 | 499 | 77 |00:00:00.01 | 3 | 0 || 15 | VIEW | | 1 | 575K| 578K|00:09:12.41 | 8751K| 8194K||* 16 | HASH JOIN | | 1 | 575K| 578K|00:09:11.21 | 8713K| 8194K|| 17 | TABLE ACCESS FULL | GL_LEDGERS | 1 | 38 | 39 |00:00:00.01 | 15 | 0 ||* 18 | HASH JOIN | | 1 | 575K| 578K|00:09:09.75 | 8713K| 8194K||* 19 | HASH JOIN | | 1 | 575K| 578K|00:06:49.08 | 6527K| 6011K||* 20 | TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL | 1 | 630K| 1388K|00:00:04.30 | 507K| 0 ||* 21 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 1 | 706K| 1738K|00:00:01.33 | 10653 | 0 ||* 22 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_LINES_ALL | 1 | 63M| 79M|00:03:50.21 | 6020K| 6011K|| 23 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_ALL | 1 | 16M| 16M|00:01:41.26 | 2185K| 2182K|| 24 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 31898 | 1 | 31898 |00:00:00.31 | 93142 | 0 ||* 25 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 31898 | 1 | 31898 |00:00:00.14 | 61244 | 0 || 26 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 31898 | 1 | 31898 |00:00:00.30 | 93417 | 0 ||* 27 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 31898 | 1 | 31898 |00:00:00.16 | 61519 | 0 ||* 28 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 31898 | 1 | 31898 |00:00:00.32 | 87027 | 0 ||* 29 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 31898 | 1 | 31898 |00:00:00.15 | 61546 | 0 || 30 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 31898 | 1 | 31898 |00:00:00.30 | 93397 | 0 ||* 31 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 31898 | 1 | 31898 |00:00:00.15 | 61499 | 0 || 32 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 31898 | 1 | 31898 |00:00:00.30 | 93416 | 0 ||* 33 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 31898 | 1 | 31898 |00:00:00.15 | 61518 | 0 || 34 | TABLE ACCESS BY INDEX ROWID BATCHED | HZ_GEOGRAPHIES | 1 | 168 | 257 |00:00:00.01 | 19 | 0 ||* 35 | INDEX SKIP SCAN | HZ_GEOGRAPHIES_N9 | 1 | 5812 | 257 |00:00:00.01 | 8 | 0 ||* 36 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 31898 | 1 | 31898 |00:00:00.18 | 62126 | 0 || 37 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 31898 | 1 | 31898 |00:00:00.10 | 32043 | 0 |-----------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("GDR"."FROM_CURRENCY"=:B1 AND "GDR"."TO_CURRENCY"=:B2 AND "GDR"."CONVERSION_DATE"=:B3 AND "GDR"."CONVERSION_TYPE"='Corporate') 6 - access("HG"."COUNTRY_CODE"="HL"."COUNTRY") 12 - access("CODE_COMBINATION_ID"="DD"."CODE_COMBINATION_ID") 13 - filter("SEGMENT1"='2600') 14 - access("SEGMENT2"='206911') 16 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID") 18 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID") 19 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID") 20 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL) 21 - 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-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 22 - filter("RCTL"."LINE_TYPE"='TAX') 25 - access("HCA"."CUST_ACCOUNT_ID"="DD"."BILL_TO_CUSTOMER_ID") 27 - access("HCSUA"."SITE_USE_ID"="DD"."BILL_TO_SITE_USE_ID") 28 - filter("HCASA"."CUST_ACCOUNT_ID"="HCA"."CUST_ACCOUNT_ID") 29 - access("HCASA"."CUST_ACCT_SITE_ID"="HCSUA"."CUST_ACCT_SITE_ID") 31 - access("HPS"."PARTY_SITE_ID"="HCASA"."PARTY_SITE_ID") 33 - access("HL"."LOCATION_ID"="HPS"."LOCATION_ID") 35 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY') filter("HG"."GEOGRAPHY_TYPE"='COUNTRY') 36 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")Note----- - this is an adaptive plan -
You do need to use format=>'adaptive' if you want to see the plan that is actually used.
-
Point# 3
conversation_date (gl_daily_rates) and its constraints.
RA_CUST_TRX_LINE_GL_DIST_ALL and its constraints.
Please let me know if anything missing from my side.
-
So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?
-
Yes for company segments
-
Hi ,
Could you please help me?
-
Rajesh123 wrote:Hi ,Could you please help me?
You haven't answered any of the questions in point 1 of my previous post.
I've already said that the first important point to address seems to be the need to make a critical index access access in the original plan more efficient - point 2 suggests re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away. In the interim you've been trying to produce a completely different plan and have apparently been ignoring my primary suggestion - if you want help from me you have to answer the questions I ask and supply me with the specific bits of information I ask for.
Regards
Jonathan Lewis
