1 2 3 Previous Next 33 Replies Latest reply on Oct 4, 2018 4:25 PM by Rajesh123

    Select Query performance

    Rajesh123

      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_OUTPUT
      
      
      SQL_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_id
      
      Plan 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.

        • 1. Re: Select Query performance
          Dom Brooks

          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
          
          1 person found this helpful
          • 2. Re: Select Query performance
            Rajesh123

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

            • 3. Re: Select Query performance
              Mark D Powell

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

              1 person found this helpful
              • 4. Re: Select Query performance
                JohnWatson2

                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.

                1 person found this helpful
                • 5. Re: Select Query performance
                  AndrewSayer

                  JohnWatson2 wrote:

                   

                  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.

                  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.

                  • 6. Re: Select Query performance
                    Jonathan Lewis

                    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

                    • 7. Re: Select Query performance
                      Jonathan Lewis

                      Rajesh,

                       

                      Your code and plan were long enough to offer a few interesting points, but short enough to make it possible to say something fairly constructive in a blog note - so I've written about it on my blog: https://jonathanlewis.wordpress.com/2018/09/30/case-study-2/

                       

                      You might be particularly interested in the closing warning - the result you get from your query may depend on the nls_date_format set by the user.

                       

                      Regards

                      Jonathan Lewis

                      1 person found this helpful
                      • 8. Re: Select Query performance
                        Rajesh123

                        Thank you Andrew,

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

                        • 9. Re: Select Query performance
                          AndrewSayer

                          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

                          • 10. Re: Select Query performance
                            Rajesh123

                            Thanks a lot excellent explanation.

                            • 11. Re: Select Query performance
                              Jonathan Lewis

                              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

                              • 12. Re: Select Query performance
                                Rajesh123

                                Hi John,

                                 

                                Thanks, i will get wrong output.

                                 

                                for Line Amount is 100, TAX has 0.

                                 

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

                                 

                                Thank you

                                • 13. Re: Select Query performance
                                  Rajesh123

                                  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_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             (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_OUTPUT
                                  
                                  
                                  SQL_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                                                           
                                        accou
                                  
                                  Plan 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
                                  
                                  
                                  • 14. Re: Select Query performance
                                    Jonathan Lewis

                                    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

                                    1 2 3 Previous Next