Forum Stats

  • 3,728,222 Users
  • 2,245,576 Discussions
  • 7,853,386 Comments

Discussions

Select Query performance

Rajesh123
Rajesh123 Member Posts: 1,506

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 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.

Jonathan LewisRajesh123Ian Baugaard
«1

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited September 2018

    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
    Rajesh123Rajesh123Jonathan LewisIan Baugaard
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited September 2018

    Thank you,  let me try without using to_date and let you know.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited September 2018

    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 --

    Rajesh123Rajesh123
  • JohnWatson2
    JohnWatson2 Member Posts: 4,227 Bronze Crown
    edited September 2018

    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.

    Rajesh123Rajesh123
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited September 2018
    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.

    Jonathan LewisIan Baugaard
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited September 2018

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited September 2018

    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

    Rajesh123Rajesh123
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    Thank you Andrew,

    I removed to_date from date columns still same plan it is showing.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited October 2018
    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

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    Thanks a lot excellent explanation.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited October 2018

    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

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    Hi John,

    Thanks, i will get wrong output.

    for Line Amount is 100, TAX has 0.

    pastedImage_0.png

    Populating 0 values for LINE type records , but it  should be 100

    pastedImage_1.png

    Thank you

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    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
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited October 2018

    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

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018
    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)

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited October 2018

    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

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited October 2018

    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.

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited October 2018

    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;  
    Ian Baugaard
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    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
  • JohnWatson2
    JohnWatson2 Member Posts: 4,227 Bronze Crown
    edited October 2018

    You do need to use format=>'adaptive' if you want to see the plan that is actually used.

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    Point# 3

    conversation_date (gl_daily_rates)  and its constraints.

    pastedImage_0.png

    RA_CUST_TRX_LINE_GL_DIST_ALL and its constraints.

    pastedImage_1.png

    Please let me know if anything missing from my side.

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited October 2018

    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?

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    Yes for company segments

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited October 2018

    Hi ,

    Could you please help me?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited October 2018
    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

This discussion has been closed.