This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,917 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Categories

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
· Share on TwitterShare on Facebook
«134

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 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
    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Oracle Certified Master DBA http://skillbuilders.comMember Posts: 4,563 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
    · Share on TwitterShare on Facebook
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 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
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    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

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    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
    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 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

    · Share on TwitterShare on Facebook
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 1, 2018 7:13AM

    Thanks a lot excellent explanation.

    · Share on TwitterShare on Facebook
This discussion has been closed.