Forum Stats

  • 3,851,765 Users
  • 2,264,024 Discussions
  • 7,904,843 Comments

Discussions

Select Query performance

24

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Oct 1, 2018 7:34AM

    Rajesh123,

    Thanks.

    A point I failed to make in the note (which I have now added) is that getting rid of the double conversion on the gdr.conversion rate may allow Oracle to do a much more precise index range scan - and if the performance is still not good enough the next step would be to see if there is a more precise index (perhaps very similar to the one Oracle is using with the columns in a different order) so that none of the access predicates end up also being filter predicates.

    Regards

    Jonathan Lewis

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 1, 2018 8:45AM

    Hi John,

    Thanks, i will get wrong output.

    for Line Amount is 100, TAX has 0.

    pastedImage_0.png

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

    pastedImage_1.png

    Thank you

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 1, 2018 9:53AM

    Hi ,

    Here is the latest statistics , taking 7.33 minutes for 5693 rows.

    Could you please help me on this?

    SELECT /*+ gather_plan_statistics*/ DISTINCT 2,                rct.org_id,                 hzp.party_name,                 hca.account_number,                 rct.interface_header_attribute1 order_number,                 rct.customer_trx_id,                 rct.trx_number,                 rct.trx_date,                 rctd.gl_date,                 rct.creation_date,                 rctl.line_number,                 rct.invoice_currency_code inv_currency,                 sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end) inv_tax_amount,                  gll.currency_code                                    func_currency,                 Round(sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end)*gdr.conversion_rate,2) func_tax_amount,                glcc.segment1                                                                 company,                glcc.segment2                                                                 account,                hg.geography_name                                                             billing_country,                gdr.conversion_rate FROM            apps.hz_parties hzp,                 apps.hz_cust_accounts hca,                 apps.ra_customer_trx_all rct,                 apps.ra_customer_trx_lines_all rctl,                 apps.ra_cust_trx_line_gl_dist_all rctd,                 apps.gl_code_combinations_kfv glcc,                 apps.hz_cust_site_uses_all hcsua,                 apps.hz_cust_acct_sites_all hcasa,                 apps.hz_party_sites hps,                 apps.hz_locations hl,                 apps.hz_geographies hg,                 apps.gl_ledgers gll,                 apps.gl_daily_rates gdr WHERE           hzp.party_id = hca.party_id AND             hca.cust_account_id = rct.bill_to_customer_idAND             hca.cust_account_id = hcasa.cust_account_idAND             rct.customer_trx_id = rctl.customer_trx_id AND             rctl.customer_trx_line_id = rctd.customer_trx_line_id AND             glcc.code_combination_id = rctd.code_combination_id AND             rct.bill_to_site_use_id = hcsua.site_use_id AND             hcsua.cust_acct_site_id = hcasa.cust_acct_site_id AND             hcasa.party_site_id = hps.party_site_id AND             hps.location_id = hl.location_id AND             hl.country = hg.country_code AND             hg.geography_type = 'COUNTRY' AND             rctl.line_type = 'TAX'AND             gll.ledger_id = rct.set_of_books_id AND             gdr.from_currency = rct.invoice_currency_code AND             gdr.to_currency = gll.currency_code AND             (gdr.conversion_date) = (rctd.gl_date) AND             gdr.conversion_type = 'Corporate' AND             rctd.gl_date BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND  To_date ('10-JAN-2018', 'DD-MON-YYYY')AND             glcc.segment1 = '2600'AND             glcc.segment2 = '206911'GROUP BY        hzp.party_name,                 hca.account_number,                 rct.interface_header_attribute1,                 rct.trx_number,                 rct.trx_date,                 rct.creation_date,                 rctl.line_number,                 rctl.unit_selling_price,                 rct.org_id,                 rctd.gl_date,                 rct.customer_trx_id,                 glcc.segment1,                glcc.segment2,                 hg.geography_name,                 rct.invoice_currency_code,                 gll.currency_code,                 gdr.conversion_rate;PLAN_TABLE_OUTPUTSQL_ID  7pz8x347z52k6, child number 0-------------------------------------SELECT /*+ gather_plan_statistics*/ DISTINCT 2,                 rct.org_id,                  hzp.party_name,                  hca.account_number,                  rct.interface_header_attribute1 order_number,                  rct.customer_trx_id,                  rct.trx_number,                  rct.trx_date,                  rctd.gl_date,                  rct.creation_date,                  rctl.line_number,                  rct.invoice_currency_code inv_currency,                  sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end) inv_tax_amount,                   gll.currency_code                                    func_currency,                  Round(sum(case when rctl.line_type='TAX' then rctl.extended_amount else 0 end)*gdr.conversion_rate,2) func_tax_amount,                 glcc.segment1                                                                 company,                 glcc.segment2                                                                 accouPlan hash value: 1975678822------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                                          | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |------------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                                   |                              |      1 |        |   5693 |00:07:33.87 |      37M|  20179 ||   1 |  HASH UNIQUE                                       |                              |      1 |      1 |   5693 |00:07:33.87 |      37M|  20179 ||   2 |   HASH GROUP BY                                    |                              |      1 |      1 |   5693 |00:07:33.85 |      37M|  20179 ||   3 |    NESTED LOOPS                                    |                              |      1 |        |   6104 |00:07:33.80 |      37M|  20179 ||   4 |     NESTED LOOPS                                   |                              |      1 |      1 |   6104 |00:07:31.11 |      37M|  16384 ||*  5 |      HASH JOIN                                     |                              |      1 |      1 |   6104 |00:07:29.13 |      37M|  13648 ||   6 |       NESTED LOOPS                                 |                              |      1 |      1 |   6104 |00:07:29.05 |      37M|  13638 ||   7 |        NESTED LOOPS                                |                              |      1 |      1 |   6104 |00:07:26.75 |      37M|  10466 ||   8 |         NESTED LOOPS                               |                              |      1 |      1 |   6104 |00:07:24.71 |      37M|   7667 ||   9 |          NESTED LOOPS                              |                              |      1 |      1 |   6104 |00:07:22.76 |      37M|   5007 ||  10 |           NESTED LOOPS                             |                              |      1 |      1 |   6104 |00:07:20.58 |      37M|   2003 ||* 11 |            HASH JOIN                               |                              |      1 |      1 |   6104 |00:07:19.09 |      37M|      2 ||  12 |             NESTED LOOPS                           |                              |      1 |      1 |    411K|00:07:16.44 |      37M|      2 ||  13 |              NESTED LOOPS                          |                              |      1 |      2 |     18M|00:04:09.60 |      18M|      2 ||* 14 |               HASH JOIN                            |                              |      1 |      3 |     18M|00:00:22.50 |     156K|      2 ||  15 |                TABLE ACCESS BY INDEX ROWID BATCHED | GL_DAILY_RATES               |      1 |    129 |  19800 |00:00:00.15 |   14981 |      0 ||* 16 |                 INDEX SKIP SCAN                    | GL_DAILY_RATES_U1            |      1 |    129 |  19800 |00:00:00.10 |    5117 |      0 ||* 17 |                HASH JOIN                           |                              |      1 |    138 |   9354 |00:00:02.34 |     141K|      2 ||* 18 |                 TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS         |      1 |     35 |      1 |00:00:00.01 |     108 |      0 ||* 19 |                  INDEX RANGE SCAN                  | GL_CODE_COMBINATIONS_N2      |      1 |    499 |     77 |00:00:00.01 |       3 |      0 ||* 20 |                 TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL |      1 |    229K|    370K|00:00:01.48 |     141K|      2 ||* 21 |                  INDEX RANGE SCAN                  | RA_CUST_TRX_LINE_GL_DIST_N2  |      1 |    256K|    451K|00:00:00.47 |    2763 |      2 ||* 22 |               TABLE ACCESS BY INDEX ROWID          | RA_CUSTOMER_TRX_LINES_ALL    |     18M|      1 |     18M|00:02:37.71 |      18M|      0 ||* 23 |                INDEX UNIQUE SCAN                   | RA_CUSTOMER_TRX_LINES_U1     |     18M|      1 |     18M|00:00:53.52 |   58940 |      0 ||* 24 |              TABLE ACCESS BY INDEX ROWID           | RA_CUSTOMER_TRX_ALL          |     18M|      1 |    411K|00:02:23.39 |      18M|      0 ||* 25 |               INDEX UNIQUE SCAN                    | RA_CUSTOMER_TRX_U1           |     18M|      1 |     18M|00:00:52.92 |   28380 |      0 ||  26 |             TABLE ACCESS FULL                      | GL_LEDGERS                   |      1 |      1 |     39 |00:00:00.01 |      15 |      0 ||  27 |            TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS             |   6104 |      1 |   6104 |00:00:01.45 |   18161 |   2001 ||* 28 |             INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1          |   6104 |      1 |   6104 |00:00:00.19 |   12057 |    197 ||  29 |           TABLE ACCESS BY INDEX ROWID              | HZ_CUST_SITE_USES_ALL        |   6104 |      1 |   6104 |00:00:02.12 |   18196 |   3004 ||* 30 |            INDEX UNIQUE SCAN                       | HZ_CUST_SITE_USES_U1         |   6104 |      1 |   6104 |00:00:00.56 |   12092 |    759 ||* 31 |          TABLE ACCESS BY INDEX ROWID               | HZ_CUST_ACCT_SITES_ALL       |   6104 |      1 |   6104 |00:00:01.93 |   17285 |   2660 ||* 32 |           INDEX UNIQUE SCAN                        | HZ_CUST_ACCT_SITES_U1        |   6104 |      1 |   6104 |00:00:00.56 |   12059 |    767 ||  33 |         TABLE ACCESS BY INDEX ROWID                | HZ_PARTY_SITES               |   6104 |      1 |   6104 |00:00:02.01 |   18177 |   2799 ||* 34 |          INDEX UNIQUE SCAN                         | HZ_PARTY_SITES_U1            |   6104 |      1 |   6104 |00:00:00.60 |   12073 |    772 ||  35 |        TABLE ACCESS BY INDEX ROWID                 | HZ_LOCATIONS                 |   6104 |      1 |   6104 |00:00:02.27 |   18182 |   3172 ||* 36 |         INDEX UNIQUE SCAN                          | HZ_LOCATIONS_U1              |   6104 |      1 |   6104 |00:00:00.57 |   12078 |    765 ||  37 |       TABLE ACCESS BY INDEX ROWID BATCHED          | HZ_GEOGRAPHIES               |      1 |    168 |    257 |00:00:00.01 |      19 |     10 ||* 38 |        INDEX SKIP SCAN                             | HZ_GEOGRAPHIES_N9            |      1 |   5812 |    257 |00:00:00.01 |       8 |      8 ||* 39 |      INDEX UNIQUE SCAN                             | HZ_PARTIES_U1                |   6104 |      1 |   6104 |00:00:01.96 |   12104 |   2736 ||  40 |     TABLE ACCESS BY INDEX ROWID                    | HZ_PARTIES                   |   6104 |      1 |   6104 |00:00:02.66 |    6124 |   3795 |------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   5 - access("HL"."COUNTRY"="HG"."COUNTRY_CODE")  11 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID" AND "GDR"."TO_CURRENCY"="GLL"."CURRENCY_CODE")  14 - access("GDR"."CONVERSION_DATE"="RCTD"."GL_DATE" AND TRUNC(INTERNAL_FUNCTION("GL_DATE"))=TRUNC(INTERNAL_FUNCTION("GDR"."CONVERSION_DATE"              )))  16 - access("GDR"."CONVERSION_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GDR"."CONVERSION_TYPE"='Corporate' AND               "GDR"."CONVERSION_DATE"<=TO_DATE(' 2018-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))       filter(("GDR"."CONVERSION_TYPE"='Corporate' AND "GDR"."CONVERSION_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND               "GDR"."CONVERSION_DATE"<=TO_DATE(' 2018-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))  17 - access("CODE_COMBINATION_ID"="RCTD"."CODE_COMBINATION_ID")  18 - filter("SEGMENT1"='2600')  19 - access("SEGMENT2"='206911')  20 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)  21 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-10               00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  22 - filter("RCTL"."LINE_TYPE"='TAX')  23 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")  24 - filter("GDR"."FROM_CURRENCY"="RCT"."INVOICE_CURRENCY_CODE")  25 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")  28 - access("HCA"."CUST_ACCOUNT_ID"="RCT"."BILL_TO_CUSTOMER_ID")  30 - access("RCT"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID")  31 - filter("HCA"."CUST_ACCOUNT_ID"="HCASA"."CUST_ACCOUNT_ID")  32 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID")  34 - access("HCASA"."PARTY_SITE_ID"="HPS"."PARTY_SITE_ID")  36 - access("HPS"."LOCATION_ID"="HL"."LOCATION_ID")  38 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY')       filter("HG"."GEOGRAPHY_TYPE"='COUNTRY')  39 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")Note-----   - this is an adaptive plan
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Oct 1, 2018 10:31AM

    Ignoring the scalar subqueries, your previous plan followed a reasonable join order and method for addressing the query with just the unfortunate problem that the nested loop join into the GL_DAILY_RATES table was using what look liked a highly appropriate index to find exactly one row in the table in a very expensive way thanks to a badly written predicate.

    As a first step you need to understand why the index use was so expensive, and see if you can find a way of using that index (or another index which you may have to created) to get to that one row very efficiently.

    What is the index definition for gl_daily_rates_u1, and what are the column types of the columns that are (directly, or indirectly) trying to join to the date column in gl_daily_rates. In particularly is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all.gl_date, and where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.

    Regards

    Jonathan Lewis

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 1, 2018 12:57PM
    Jonathan Lewis wrote:Ignoring the scalar subqueries, your previous plan followed a reasonable join order and method for addressing the query with just the unfortunate problem that the nested loop join into the GL_DAILY_RATES table was using what look liked a highly appropriate index to find exactly one row in the table in a very expensive way thanks to a badly written predicate.As a first step you need to understand why the index use was so expensive, and see if you can find a way of using that index (or another index which you may have to created) to get to that one row very efficiently.What is the index definition for gl_daily_rates_u1, and what are the column types of the columns that are (directly, or indirectly) trying to join to the date column in gl_daily_rates. In particularly is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all.gl_date, and where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.RegardsJonathan Lewis

    (1)

    CREATE UNIQUE INDEX GL.GL_DAILY_RATES_U1 ON GL.GL_DAILY_RATES

    (FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_TYPE)

    (2)Varchar2

    FROM_CURRENCY

    TO_CURRENCY

    CONVERSION_TYPE

    CONVERSION_DATE --> Date

    (3) is there anything that might be worth mentioning about apps.ra_cust_trx_line_gl_dist_all

    Yes gl_date , invoices which are created in a particular month , i should use this column as parameter as i don't have another option.

    (4)where did the trunc() function come from in the predicates for operation 14 since there's no sign of a trunc() in the query.

    CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_UPG1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

    (CUSTOMER_TRX_ID, TRUNC("GL_DATE"), ACCOUNT_SET_FLAG)

    CREATE INDEX AR.XXC_CUST_TRX__GL_DIST_N1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

    (GL_DATE, NVL("LATEST_REC_FLAG",'Y'), CODE_COMBINATION_ID)

    CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_N1 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

    (CUSTOMER_TRX_LINE_ID, ACCOUNT_SET_FLAG, GL_DATE, ACCOUNT_CLASS)

    CREATE INDEX AR.RA_CUST_TRX_LINE_GL_DIST_N5 ON AR.RA_CUST_TRX_LINE_GL_DIST_ALL

    (SET_OF_BOOKS_ID, POSTING_CONTROL_ID, GL_DATE)

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,054 Blue Diamond
    edited Oct 1, 2018 1:56PM

    Three points

    1) In posting number 5 you said that you removed the to_date() and the plan was still the same.

    How carefully did you check ?  Did you actually try running it with rowsource execution stats enabled or did you just note that the plan looked the same of the plan hash value was the same.  Did you check the predicate section - because the plan_hash_value and the "plan" could stay the same while the use of predicates changes and makes the plan more efficient.  Please show us the plan with captured with rowsource excution stats enabled after the first 500 rows have been returned.

    2) The index does not appear (from an intuitive viewpoint) to be the sensible option. It looks as if it ought to be "(from_currency, to_currency, conversion_type, conversion_date) compress 3"  (And if the current ordering is actually ideal for some important queries it's possible that you still could do with another index with the same 4 columns in a different order with conversion_date at the end. In principle your original index shouldn't need this change as the current index looks as if it ought to be okay except Oracle has introduced that "to_date(internal_function(column_name)))" effect - the re-arranged index would at least minimise the overhead of that wierdness.

    3) The trunc(gl_date) in the upg1 index shouldn't echo into the join condition. However, it has occurred to me that perhaps someone has added a couple of constraints to the tables of the form:  (check gl_date = trunc(gl_date)), and the corresponding (check (conversion_date = trunc(conversion_date)).  Can you check the two table definitions for check constraints on the columns.

    Regards

    Jonathan Lewis

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Oct 2, 2018 4:26AM

    In the current execution, the estimates are out quite badly.

    So most of the time goes on NESTED LOOP row-by-row lookups on respectively 18 million and 411K rows before the join to GL_LEDGERS brings that back down to a few thousand.

    At this point it would help to be more familiar with these tables to know how the query might be written differently (i.e. avoiding any significant manual hinting).

    It's just heavier work if you don't know the tables you're dealing with...

    i.e. we should be thinking about what the driving rowsources for this query should be and which are just boilerplate - i.e. the superfluous bits of additional information we need later once we have our final resultset.

    RCTD + RCTL is presumably what really drives this query?

    i.e. get me all the tax lines between these two dates.

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Oct 2, 2018 5:26AM

    This might be a giant waste of time but just following on from what I said before about driving data set, I've rearranged the query section you posted a couple of messages back.

    There is about 0% chance that I haven't made a mistake somewhere as I've butchered it.

    Also the formatting and rearranging is not a reflection on the original style, just a method for going through and double checking what it's all doing ( https://orastory.wordpress.com/2010/03/24/tuning_by_formatting/ ) which includes switching sides of some of the predicates - it's a bit mental I know...

    Unfortunately though that comes with a good risk of messing something up along the way.

    WITH subq_driving_data AS     (SELECT /*+ no_merge */             rctd.gl_date       ,      rctd.code_combination_id      ,      rctl.line_number      ,      rctl.extended_amount      ,      rctl.line_type      ,      rct.org_id,      ,      rct.interface_header_attribute1      ,      rct.customer_trx_id      ,      rct.trx_number      ,      rct.trx_date        ,      rct.creation_date      ,      rct.invoice_currency_code      ,      rct.bill_to_customer_id      ,      rct.bill_to_site_use_id      ,      gll.currency_code      ,      (SELECT gdr.conversion_rate              FROM   apps.gl_daily_rates gdr              WHERE  gdr.from_currency     = rct.invoice_currency_code                 AND    gdr.to_currency       = gll.currency_code                 AND    gdr.conversion_date   = rctd.gl_date              AND    gdr.conversion_type   = 'Corporate') conversion_rate      FROM   apps.ra_cust_trx_line_gl_dist_all rctd      ,      apps.ra_customer_trx_lines_all    rctl      ,      apps.ra_customer_trx_all          rct      ,      apps.gl_ledgers          gll      WHERE  rctd.gl_date              BETWEEN To_date ('01-JAN-2018', 'DD-MON-YYYY') AND  To_date ('10-JAN-2018', 'DD-MON-YYYY')      AND    rctl.customer_trx_line_id       = rctd.customer_trx_line_id        AND    rctl.line_type                  = 'TAX'      AND    rct.customer_trx_id             = rctl.customer_trx_id      AND    gll.ledger_id                   = rct.set_of_books_id)  SELECT /*+ gather_plan_statistics*/        DISTINCT 2  ,      dd.org_id,      hzp.party_name,      hca.account_number,      dd.interface_header_attribute1 order_number,      dd.customer_trx_id,      dd.trx_number,      dd.trx_date,      dd.gl_date,      dd.creation_date,      dd.line_number,      dd.invoice_currency_code       inv_currency,      sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end) inv_tax_amount,      dd.currency_code               func_currency ,      Round(sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end)*dd.conversion_rate,2) func_tax_amount,      glcc.segment1                  company,      glcc.segment2                  account,,      hg.geography_name              billing_country,      dd.conversion_rateFROM   subq_driving_data             dd         ,      apps.hz_parties               hzp,      apps.hz_cust_accounts         hca,      apps.gl_code_combinations_kfv glcc,      apps.hz_cust_site_uses_all    hcsua   ,      apps.hz_cust_acct_sites_all   hcasa,      apps.hz_party_sites           hps,      apps.hz_locations             hl  ,      apps.hz_geographies           hgWHERE  hca.cust_account_id         = dd.bill_to_customer_idAND    hzp.party_id                = hca.party_id   AND    hcsua.site_use_id           = dd.bill_to_site_use_idAND    hcasa.cust_account_id       = hca.cust_account_id     AND    hcasa.cust_acct_site_id     = hcsua.cust_acct_site_id   AND    hps.party_site_id           = hcasa.party_site_idAND    hl.location_id              = hps.location_id   AND    hg.country_code             = hl.country   AND    hg.geography_type           = 'COUNTRY'AND    glcc.code_combination_id    = dd.code_combination_idAND    glcc.segment1               = '2600'  AND    glcc.segment2               = '206911'  GROUP BY          hzp.party_name,          hca.account_number,          dd.interface_header_attribute1,          dd.trx_number,          dd.trx_date,          dd.creation_date,          dd.line_number,          dd.unit_selling_price,          dd.org_id,          dd.gl_date,          dd.customer_trx_id,          glcc.segment1,         glcc.segment2,          hg.geography_name,          dd.invoice_currency_code,          dd.currency_code,          dd.conversion_rate;  
    Ian Baugaard
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 3, 2018 4:03AM

    Hi ,

    I ran the query which was modified by you taking 9 minutes time to complete.

    PLAN_TABLE_OUTPUTSQL_ID  3fmqn33ytdvj7, child number 0-------------------------------------WITH subq_driving_data AS        (SELECT /*+ no_merge */                rctd.gl_date          ,      rctd.code_combination_id         ,      rctl.line_number         ,      rctl.extended_amount         ,      rctl.line_type         ,      rct.org_id       ,      rct.interface_header_attribute1         ,      rct.customer_trx_id         ,      rct.trx_number         ,      rct.trx_date           ,      rct.creation_date         ,      rct.invoice_currency_code         ,      rct.bill_to_customer_id         ,      rct.bill_to_site_use_id         ,      gll.currency_code         ,      (SELECT gdr.conversion_rate                 FROM   apps.gl_daily_rates gdr                 WHERE  gdr.from_currency     = rct.invoice_currency_code                    AND    gdr.to_currency       = gll.currency_code                    AND    gdr.conversion_date   = rctd.gl_date                 AND    gdr.conversion_type   = 'Corporate') conversion_rate         FROM   apps.ra_cust_trx_line_gl_dist_all rctdPlan hash value: 357138763-----------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                                         | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-----------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                                  |                              |      1 |        |    501 |00:09:16.40 |    9306K|   8194K||   1 |  TABLE ACCESS BY INDEX ROWID                      | GL_DAILY_RATES               |  17504 |      1 |   7109 |00:00:00.13 |   38391 |      0 ||*  2 |   INDEX UNIQUE SCAN                               | GL_DAILY_RATES_U1            |  17504 |      1 |   7109 |00:00:00.07 |   31282 |      0 ||   3 |  HASH GROUP BY                                    |                              |      1 |      1 |    501 |00:09:16.40 |    9306K|   8194K||   4 |   NESTED LOOPS                                    |                              |      1 |        |  31898 |00:09:16.31 |    9306K|   8194K||   5 |    NESTED LOOPS                                   |                              |      1 |      1 |  31898 |00:09:16.11 |    9274K|   8194K||*  6 |     HASH JOIN                                     |                              |      1 |      1 |  31898 |00:09:15.83 |    9212K|   8194K||   7 |      NESTED LOOPS                                 |                              |      1 |      1 |  31898 |00:09:15.61 |    9211K|   8194K||   8 |       NESTED LOOPS                                |                              |      1 |      1 |  31898 |00:09:15.22 |    9118K|   8194K||   9 |        NESTED LOOPS                               |                              |      1 |      1 |  31898 |00:09:14.83 |    9025K|   8194K||  10 |         NESTED LOOPS                              |                              |      1 |    341 |  31898 |00:09:14.41 |    8938K|   8194K||  11 |          NESTED LOOPS                             |                              |      1 |    341 |  31898 |00:09:13.93 |    8844K|   8194K||* 12 |           HASH JOIN                               |                              |      1 |    341 |  31898 |00:09:13.49 |    8751K|   8194K||* 13 |            TABLE ACCESS BY INDEX ROWID BATCHED    | GL_CODE_COMBINATIONS         |      1 |     35 |      1 |00:00:00.01 |     108 |      0 ||* 14 |             INDEX RANGE SCAN                      | GL_CODE_COMBINATIONS_N2      |      1 |    499 |     77 |00:00:00.01 |       3 |      0 ||  15 |            VIEW                                   |                              |      1 |    575K|    578K|00:09:12.41 |    8751K|   8194K||* 16 |             HASH JOIN                             |                              |      1 |    575K|    578K|00:09:11.21 |    8713K|   8194K||  17 |              TABLE ACCESS FULL                    | GL_LEDGERS                   |      1 |     38 |     39 |00:00:00.01 |      15 |      0 ||* 18 |              HASH JOIN                            |                              |      1 |    575K|    578K|00:09:09.75 |    8713K|   8194K||* 19 |               HASH JOIN                           |                              |      1 |    575K|    578K|00:06:49.08 |    6527K|   6011K||* 20 |                TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL |      1 |    630K|   1388K|00:00:04.30 |     507K|      0 ||* 21 |                 INDEX RANGE SCAN                  | RA_CUST_TRX_LINE_GL_DIST_N2  |      1 |    706K|   1738K|00:00:01.33 |   10653 |      0 ||* 22 |                TABLE ACCESS FULL                  | RA_CUSTOMER_TRX_LINES_ALL    |      1 |     63M|     79M|00:03:50.21 |    6020K|   6011K||  23 |               TABLE ACCESS FULL                   | RA_CUSTOMER_TRX_ALL          |      1 |     16M|     16M|00:01:41.26 |    2185K|   2182K||  24 |           TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS             |  31898 |      1 |  31898 |00:00:00.31 |   93142 |      0 ||* 25 |            INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1          |  31898 |      1 |  31898 |00:00:00.14 |   61244 |      0 ||  26 |          TABLE ACCESS BY INDEX ROWID              | HZ_CUST_SITE_USES_ALL        |  31898 |      1 |  31898 |00:00:00.30 |   93417 |      0 ||* 27 |           INDEX UNIQUE SCAN                       | HZ_CUST_SITE_USES_U1         |  31898 |      1 |  31898 |00:00:00.16 |   61519 |      0 ||* 28 |         TABLE ACCESS BY INDEX ROWID               | HZ_CUST_ACCT_SITES_ALL       |  31898 |      1 |  31898 |00:00:00.32 |   87027 |      0 ||* 29 |          INDEX UNIQUE SCAN                        | HZ_CUST_ACCT_SITES_U1        |  31898 |      1 |  31898 |00:00:00.15 |   61546 |      0 ||  30 |        TABLE ACCESS BY INDEX ROWID                | HZ_PARTY_SITES               |  31898 |      1 |  31898 |00:00:00.30 |   93397 |      0 ||* 31 |         INDEX UNIQUE SCAN                         | HZ_PARTY_SITES_U1            |  31898 |      1 |  31898 |00:00:00.15 |   61499 |      0 ||  32 |       TABLE ACCESS BY INDEX ROWID                 | HZ_LOCATIONS                 |  31898 |      1 |  31898 |00:00:00.30 |   93416 |      0 ||* 33 |        INDEX UNIQUE SCAN                          | HZ_LOCATIONS_U1              |  31898 |      1 |  31898 |00:00:00.15 |   61518 |      0 ||  34 |      TABLE ACCESS BY INDEX ROWID BATCHED          | HZ_GEOGRAPHIES               |      1 |    168 |    257 |00:00:00.01 |      19 |      0 ||* 35 |       INDEX SKIP SCAN                             | HZ_GEOGRAPHIES_N9            |      1 |   5812 |    257 |00:00:00.01 |       8 |      0 ||* 36 |     INDEX UNIQUE SCAN                             | HZ_PARTIES_U1                |  31898 |      1 |  31898 |00:00:00.18 |   62126 |      0 ||  37 |    TABLE ACCESS BY INDEX ROWID                    | HZ_PARTIES                   |  31898 |      1 |  31898 |00:00:00.10 |   32043 |      0 |-----------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("GDR"."FROM_CURRENCY"=:B1 AND "GDR"."TO_CURRENCY"=:B2 AND "GDR"."CONVERSION_DATE"=:B3 AND "GDR"."CONVERSION_TYPE"='Corporate')   6 - access("HG"."COUNTRY_CODE"="HL"."COUNTRY")  12 - access("CODE_COMBINATION_ID"="DD"."CODE_COMBINATION_ID")  13 - filter("SEGMENT1"='2600')  14 - access("SEGMENT2"='206911')  16 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID")  18 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")  19 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")  20 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)  21 - access("RCTD"."GL_DATE">=TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RCTD"."GL_DATE"<=TO_DATE(' 2018-01-30               00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  22 - filter("RCTL"."LINE_TYPE"='TAX')  25 - access("HCA"."CUST_ACCOUNT_ID"="DD"."BILL_TO_CUSTOMER_ID")  27 - access("HCSUA"."SITE_USE_ID"="DD"."BILL_TO_SITE_USE_ID")  28 - filter("HCASA"."CUST_ACCOUNT_ID"="HCA"."CUST_ACCOUNT_ID")  29 - access("HCASA"."CUST_ACCT_SITE_ID"="HCSUA"."CUST_ACCT_SITE_ID")  31 - access("HPS"."PARTY_SITE_ID"="HCASA"."PARTY_SITE_ID")  33 - access("HL"."LOCATION_ID"="HPS"."LOCATION_ID")  35 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY')       filter("HG"."GEOGRAPHY_TYPE"='COUNTRY')  36 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")Note-----   - this is an adaptive plan
  • JohnWatson2
    JohnWatson2 Member Posts: 4,487 Silver Crown
    edited Oct 3, 2018 4:10AM

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

This discussion has been closed.