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

        Rajesh123 wrote:

         

        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)

        Did you read the output you copied and pasted here? You tried to get the row source execution statistics for the statement:

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

         

        That is not the statement you care about. A google would have shown you that this statement is due to your client program trying to fetch data from the dbms_output buffer and can be turned off with

        set serverout off


        I suggest you turn off serveroutput, run the statement and run the dbms_xplan.display_cursor query again. Always take a moment to read over what you are copying and pasting first.

        • 31. Re: Select Query performance
          Rajesh123

          Hi,

           

          I ran the rowsource plan.

          SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT 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  
          ,      (SELECT SUM (extended_amount)
                    FROM apps.ra_customer_trx_lines_all
                   WHERE customer_trx_id =dd.customer_trx_id
                     AND line_type = 'LINE') inv_net_amount
          ,      sum(case when dd.line_type='TAX' then dd.extended_amount else 0 end) inv_tax_amount 
          ,      (SELECT SUM (extended_amount)
                    FROM apps.ra_customer_trx_lines_all
                    WHERE customer_trx_id =dd.customer_trx_id) inv_gross_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 (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  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) 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'    
          AND dd.gl_date              BETWEEN '01-JAN-2018' and '30-JAN-2018'
          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.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;
          
          
          
          
          SELECT * FROM TABLE(dbms_xplan.display_cursor('4gw9h15ttptrp', null, 'ALLSTATS LAST'));
          
          
          SQL_ID  4gw9h15ttptrp, child number 7
          -------------------------------------
          SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT 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   ,      (SELECT SUM 
          (extended_amount)           FROM apps.ra_customer_trx_lines_all         
           WHERE customer_trx_id =dd.customer_trx_id            AND line_type = 
          'LINE') inv_net_amount ,      sum(case when dd.line_type='TAX' then 
          dd.extended_amount else 0 end) inv_tax_amount  ,      (SELECT SUM 
          (extended_amount)           FROM apps.ra_customer_trx_lines_all         
            WHERE customer_trx_id =dd.customer_trx_id) inv_gross_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       
                     c
          
          Plan hash value: 1832523212
          
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                                           | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                                    |                              |      1 |        |    501 |00:10:09.14 |    9539K|   8194K|       |       |          |
          |   1 |  TABLE ACCESS BY INDEX ROWID                        | GL_DAILY_RATES               |  17504 |      1 |   7109 |00:00:00.13 |   38393 |      0 |       |       |          |
          |*  2 |   INDEX UNIQUE SCAN                                 | GL_DAILY_RATES_U1            |  17504 |      1 |   7109 |00:00:00.07 |   31284 |      0 |       |       |          |
          |   3 |  HASH GROUP BY                                      |                              |      1 |      1 |    501 |00:10:09.14 |    9539K|   8194K|   727K|   727K|          |
          |   4 |   NESTED LOOPS                                      |                              |      1 |        |  31898 |00:10:09.02 |    9539K|   8194K|       |       |          |
          |   5 |    NESTED LOOPS                                     |                              |      1 |      1 |  31898 |00:10:08.76 |    9507K|   8194K|       |       |          |
          |*  6 |     HASH JOIN                                       |                              |      1 |      1 |  31898 |00:10:08.41 |    9445K|   8194K|  6772K|  2147K| 8154K (0)|
          |   7 |      NESTED LOOPS                                   |                              |      1 |      1 |  31898 |00:10:08.17 |    9445K|   8194K|       |       |          |
          |   8 |       NESTED LOOPS                                  |                              |      1 |      1 |  31898 |00:10:07.77 |    9352K|   8194K|       |       |          |
          |   9 |        NESTED LOOPS OUTER                           |                              |      1 |      1 |  31898 |00:10:07.37 |    9258K|   8194K|       |       |          |
          |  10 |         NESTED LOOPS OUTER                          |                              |      1 |      1 |  31898 |00:10:06.13 |    9125K|   8194K|       |       |          |
          |  11 |          NESTED LOOPS                               |                              |      1 |      1 |  31898 |00:10:05.10 |    9024K|   8194K|       |       |          |
          |  12 |           NESTED LOOPS                              |                              |      1 |    341 |  31898 |00:10:04.67 |    8937K|   8194K|       |       |          |
          |  13 |            NESTED LOOPS                             |                              |      1 |    341 |  31898 |00:10:04.19 |    8844K|   8194K|       |       |          |
          |* 14 |             HASH JOIN                               |                              |      1 |    341 |  31898 |00:10:03.72 |    8751K|   8194K|  1344K|  1344K|  730K (0)|
          |* 15 |              TABLE ACCESS BY INDEX ROWID BATCHED    | GL_CODE_COMBINATIONS         |      1 |     35 |      1 |00:00:00.01 |     108 |      0 |       |       |          |
          |* 16 |               INDEX RANGE SCAN                      | GL_CODE_COMBINATIONS_N2      |      1 |    499 |     77 |00:00:00.01 |       3 |      0 |       |       |          |
          |  17 |              VIEW                                   |                              |      1 |    575K|    578K|00:10:02.59 |    8751K|   8194K|       |       |          |
          |* 18 |               HASH JOIN                             |                              |      1 |    575K|    578K|00:10:01.34 |    8712K|   8194K|  1817K|  1817K| 1604K (0)|
          |  19 |                TABLE ACCESS FULL                    | GL_LEDGERS                   |      1 |     38 |     39 |00:00:00.01 |      15 |      0 |       |       |          |
          |* 20 |                HASH JOIN                            |                              |      1 |    575K|    578K|00:09:59.83 |    8712K|   8194K|    63M|  4888K|   77M (0)|
          |* 21 |                 HASH JOIN                           |                              |      1 |    575K|    578K|00:07:25.19 |    6527K|   6011K|    94M|  6318K|   99M (0)|
          |* 22 |                  TABLE ACCESS BY INDEX ROWID BATCHED| RA_CUST_TRX_LINE_GL_DIST_ALL |      1 |    630K|   1388K|00:00:04.27 |     507K|      0 |       |       |          |
          |* 23 |                   INDEX RANGE SCAN                  | RA_CUST_TRX_LINE_GL_DIST_N2  |      1 |    706K|   1738K|00:00:01.32 |   10653 |      0 |       |       |          |
          |* 24 |                  TABLE ACCESS FULL                  | RA_CUSTOMER_TRX_LINES_ALL    |      1 |     63M|     79M|00:04:24.98 |    6020K|   6011K|       |       |          |
          |  25 |                 TABLE ACCESS FULL                   | RA_CUSTOMER_TRX_ALL          |      1 |     16M|     16M|00:01:54.31 |    2185K|   2182K|       |       |          |
          |  26 |             TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS             |  31898 |      1 |  31898 |00:00:00.30 |   93126 |      0 |       |       |          |
          |* 27 |              INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1          |  31898 |      1 |  31898 |00:00:00.14 |   61228 |      0 |       |       |          |
          |  28 |            TABLE ACCESS BY INDEX ROWID              | HZ_CUST_SITE_USES_ALL        |  31898 |      1 |  31898 |00:00:00.30 |   93397 |      0 |       |       |          |
          |* 29 |             INDEX UNIQUE SCAN                       | HZ_CUST_SITE_USES_U1         |  31898 |      1 |  31898 |00:00:00.16 |   61499 |      0 |       |       |          |
          |* 30 |           TABLE ACCESS BY INDEX ROWID               | HZ_CUST_ACCT_SITES_ALL       |  31898 |      1 |  31898 |00:00:00.33 |   87019 |      0 |       |       |          |
          |* 31 |            INDEX UNIQUE SCAN                        | HZ_CUST_ACCT_SITES_U1        |  31898 |      1 |  31898 |00:00:00.15 |   61541 |      0 |       |       |          |
          |  32 |          VIEW PUSHED PREDICATE                      | VW_SSQ_2                     |  31898 |      1 |  31898 |00:00:00.91 |     100K|      0 |       |       |          |
          |  33 |           SORT GROUP BY                             |                              |  31898 |      1 |  31898 |00:00:00.75 |     100K|      0 |  2048 |  2048 | 2048  (0)|
          |  34 |            TABLE ACCESS BY INDEX ROWID BATCHED      | RA_CUSTOMER_TRX_LINES_ALL    |  31898 |      8 |  85280 |00:00:00.46 |     100K|      0 |       |       |          |
          |* 35 |             INDEX RANGE SCAN                        | XXC_CUSTOMER_GETPAID         |  31898 |     12 |  85280 |00:00:00.21 |   79858 |      0 |       |       |          |
          |  36 |         VIEW PUSHED PREDICATE                       | VW_SSQ_1                     |  31898 |      1 |  31898 |00:00:01.14 |     132K|      0 |       |       |          |
          |  37 |          SORT GROUP BY                              |                              |  31898 |      1 |  31898 |00:00:01.02 |     132K|      0 |  2048 |  2048 | 2048  (0)|
          |  38 |           TABLE ACCESS BY INDEX ROWID BATCHED       | RA_CUSTOMER_TRX_LINES_ALL    |  31898 |     16 |    203K|00:00:00.71 |     132K|      0 |       |       |          |
          |* 39 |            INDEX RANGE SCAN                         | XXC_CUSTOMER_GETPAID         |  31898 |     16 |    203K|00:00:00.27 |   80742 |      0 |       |       |          |
          |  40 |        TABLE ACCESS BY INDEX ROWID                  | HZ_PARTY_SITES               |  31898 |      1 |  31898 |00:00:00.31 |   93390 |      0 |       |       |          |
          |* 41 |         INDEX UNIQUE SCAN                           | HZ_PARTY_SITES_U1            |  31898 |      1 |  31898 |00:00:00.16 |   61492 |      0 |       |       |          |
          |  42 |       TABLE ACCESS BY INDEX ROWID                   | HZ_LOCATIONS                 |  31898 |      1 |  31898 |00:00:00.30 |   93424 |      0 |       |       |          |
          |* 43 |        INDEX UNIQUE SCAN                            | HZ_LOCATIONS_U1              |  31898 |      1 |  31898 |00:00:00.15 |   61526 |      0 |       |       |          |
          |  44 |      TABLE ACCESS BY INDEX ROWID BATCHED            | HZ_GEOGRAPHIES               |      1 |    168 |    257 |00:00:00.01 |      19 |      0 |       |       |          |
          |* 45 |       INDEX SKIP SCAN                               | HZ_GEOGRAPHIES_N9            |      1 |   5812 |    257 |00:00:00.01 |       8 |      0 |       |       |          |
          |* 46 |     INDEX UNIQUE SCAN                               | HZ_PARTIES_U1                |  31898 |      1 |  31898 |00:00:00.22 |   62126 |      0 |       |       |          |
          |  47 |    TABLE ACCESS BY INDEX ROWID                      | HZ_PARTIES                   |  31898 |      1 |  31898 |00:00:00.13 |   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")
            14 - access("CODE_COMBINATION_ID"="DD"."CODE_COMBINATION_ID")
            15 - filter("SEGMENT1"='2600')
            16 - access("SEGMENT2"='206911')
            18 - access("GLL"."LEDGER_ID"="RCT"."SET_OF_BOOKS_ID")
            20 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
            21 - access("RCTL"."CUSTOMER_TRX_LINE_ID"="RCTD"."CUSTOMER_TRX_LINE_ID")
            22 - filter("RCTD"."CUSTOMER_TRX_LINE_ID" IS NOT NULL)
            23 - 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'))
            24 - filter("RCTL"."LINE_TYPE"='TAX')
            27 - access("HCA"."CUST_ACCOUNT_ID"="DD"."BILL_TO_CUSTOMER_ID")
            29 - access("HCSUA"."SITE_USE_ID"="DD"."BILL_TO_SITE_USE_ID")
            30 - filter("HCASA"."CUST_ACCOUNT_ID"="HCA"."CUST_ACCOUNT_ID")
            31 - access("HCASA"."CUST_ACCT_SITE_ID"="HCSUA"."CUST_ACCT_SITE_ID")
            35 - access("CUSTOMER_TRX_ID"="DD"."CUSTOMER_TRX_ID" AND "LINE_TYPE"='LINE')
            39 - access("CUSTOMER_TRX_ID"="DD"."CUSTOMER_TRX_ID")
            41 - access("HPS"."PARTY_SITE_ID"="HCASA"."PARTY_SITE_ID")
            43 - access("HL"."LOCATION_ID"="HPS"."LOCATION_ID")
            45 - access("HG"."GEOGRAPHY_TYPE"='COUNTRY')
                 filter("HG"."GEOGRAPHY_TYPE"='COUNTRY')
            46 - access("HZP"."PARTY_ID"="HCA"."PARTY_ID")
          
          Note
          -----
             - this is an adaptive plan
          
          
          
          
          
          
          
          
          
          • 32. Re: Select Query performance
            Jonathan Lewis

            Rajesh123,

             

            That's not the original query with the to_date() functions removed. It's a completely different query.

            This is what I asked for:

             

            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.

            Regards
            Jonathan Lewis
            • 33. Re: Select Query performance
              Rajesh123

              Hi,

               

              Sorry , i will run the original query removing with to_date from conversation_date and gl_date from where clause and let you know.

              1 2 3 Previous Next