Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 469 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 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.
