1 2 3 Previous Next 33 Replies Latest reply on Oct 4, 2018 4:25 PM by Rajesh123 Go to original post
      • 15. Re: Select Query performance
        Rajesh123

        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.

         

        Regards

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

        • 16. Re: Select Query performance
          Jonathan Lewis

          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

          • 17. Re: Select Query performance
            Dom Brooks

            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.

            • 18. Re: Select Query performance
              Dom Brooks

              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_rate
              FROM   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           hg
              WHERE  hca.cust_account_id         = dd.bill_to_customer_id
              AND    hzp.party_id                = hca.party_id   
              AND    hcsua.site_use_id           = dd.bill_to_site_use_id
              AND    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_id
              AND    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_id
              AND    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;  
              
              • 19. Re: Select Query performance
                Rajesh123

                Hi ,

                 

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

                 

                PLAN_TABLE_OUTPUT
                
                
                SQL_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 rctd
                
                Plan 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
                
                
                • 20. Re: Select Query performance
                  JohnWatson2

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

                  • 21. Re: Select Query performance
                    Rajesh123

                    Point# 3

                     

                    conversation_date (gl_daily_rates)  and its constraints.

                    RA_CUST_TRX_LINE_GL_DIST_ALL and its constraints.

                    Please let me know if anything missing from my side.

                    • 22. Re: Select Query performance
                      Dom Brooks

                      So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?

                      • 23. Re: Select Query performance
                        Rajesh123

                        Yes for company segments

                        • 24. Re: Select Query performance
                          Rajesh123

                          Hi ,

                           

                          Could you please help me?

                          • 25. Re: Select Query performance
                            Jonathan Lewis

                            Rajesh123 wrote:

                             

                            Hi ,

                             

                            Could you please help me?

                            You haven't answered any of the questions in point 1 of my previous post.

                             

                            I've already said that the first important point to address seems to be the need to make a critical index access access in the original plan more efficient - point 2 suggests re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away.  In the interim you've been trying to produce a completely different plan and have apparently been ignoring my primary suggestion - if you want help from me you have to answer the questions I ask and supply me with the specific bits of information I ask for.

                             

                            Regards

                            Jonathan Lewis

                            • 26. Re: Select Query performance
                              Rajesh123

                              1# first important point to address seems to be the need to make a critical index access in the original plan more efficient

                              Could you please explain little bit more, i am completely new to this.

                               

                              2# re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away

                              Could you please explain little bit more, i am completely new to this.

                               

                              Thank you

                              • 27. Re: Select Query performance
                                Jonathan Lewis

                                Rajesh123 wrote:

                                 

                                1# first important point to address seems to be the need to make a critical index access in the original plan more efficient

                                Could you please explain little bit more, i am completely new to this.

                                 

                                2# re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away

                                Could you please explain little bit more, i am completely new to this.

                                 

                                Thank you

                                 

                                1# - re-read the comments Dom Brooks and I made in the early stages of this thread.

                                2# - might be irrelevant, misleading, and a total waste of time, so I won't do it until you've answered the questions I reminded you about in my previous posting.  It's possible that the answers you give (especially the plan) may mean that the suggestion is irrelevant, on the other hand they may make it much easier to give you an explanation you understand of what you need to do and how to do it.

                                 

                                Regards

                                Jonathan Lewis

                                • 28. Re: Select Query performance
                                  Rajesh123

                                  Dom Brooks wrote:

                                   

                                  So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?

                                  Hi Dom,

                                   

                                  Could you please explain little more ? in technical level

                                  • 29. Re: Select Query performance
                                    Rajesh123

                                    I did rowsource plan using with GATHER PLAN hint in Select statement

                                    SELECT *

                                    FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

                                     

                                    PLAN_TABLE_OUTPUT

                                     

                                    SQL_ID  103z7pvxjhwgn, child number 0

                                    begin dbms_output.get_line(line => :line, status => :status); end;

                                     

                                    NOTE: cannot fetch plan for SQL_ID: 103z7pvxjhwgn, CHILD_NUMBER: 0

                                          Please verify value of SQL_ID and CHILD_NUMBER;

                                          It could also be that the plan is no longer in cursor cache (check v$sql_plan)