Forum Stats

  • 3,767,998 Users
  • 2,252,739 Discussions
  • 7,874,404 Comments

Discussions

Select Query performance

Rajesh123
Rajesh123 Member Posts: 1,506
edited Oct 4, 2018 12:25PM in General Database Discussions

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

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited Sep 28, 2018 8:36AM

    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 Sep 28, 2018 9:33AM

    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 Sep 28, 2018 9:57AM

    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,327 Silver Crown
    edited Sep 28, 2018 1:59PM

    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 Sep 29, 2018 7:08AM
    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,786 Gold Crown
    edited Sep 30, 2018 11:38AM

    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,786 Gold Crown
    edited Sep 30, 2018 3:05PM

    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 Oct 1, 2018 6:15AM

    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 Oct 1, 2018 7:00AM
    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 Oct 1, 2018 7:13AM

    Thanks a lot excellent explanation.

This discussion has been closed.