3 Replies Latest reply: Jan 2, 2013 2:54 AM by siebelD RSS

    help for query tuning

    siebelD
      hello all

      my one of query is returning result in 1-2 mins only for 1 lakh record but i am not sure if it showed me complete rows or not because when I an trying to get count of result ..its taking lot of time .when I am using this query on plsql code ..code is running slow so just wanted to confirm on query tuning point of view if its fine or not ..please look onto it and let me know if query is fine or not by explain plan .my oracle version is 11g

      this is my query

      SELECT ROWNUM , TRUNC(rownum/5000) + 20000 ,'FOR_UPDATE', sku_org.NAME ,
      acct_promo_sku.src_num , acct_promo_sku.sub_type ,
      promo_actual.sku_actual_pos
      FROM siebel.s_src acct_promo_hdr,
      siebel.s_src acct_title_format,
      siebel.s_src acct_promo_sku,
      siebel.s_src_x acct_promo_hdrx,
      siebel.s_src_x acct_promo_skux,
      siebel.s_prod_int prod,
      siebel.s_bu promo_hdr_org,
      siebel.s_bu sku_org,
      siebelwb.stg_sbl_acct_promo_actuals2 promo_actual
      WHERE acct_promo_hdr.sub_type = 'PLAN_ACCOUNT_PROMOTION'
      AND acct_promo_hdr.row_id = acct_title_format.par_src_id
      AND acct_title_format.sub_type = 'PLAN_ACCT_PROMOTION_CATEGORY'
      AND acct_title_format.row_id = acct_promo_sku.par_src_id
      AND acct_promo_sku.sub_type = 'PLAN_ACCOUNT_PROMOTION_PRODUCT'
      AND acct_promo_hdr.row_id = acct_promo_hdrx.par_row_id
      AND acct_promo_sku.row_id = acct_promo_skux.par_row_id(+)
      AND acct_promo_sku.prod_id = prod.row_id
      AND acct_promo_hdr.bu_id = promo_hdr_org.row_id
      AND acct_promo_sku.bu_id = sku_org.row_id
      AND prod.x_prod_material_num = promo_actual.material_number
      and prod.X_PROD_SALES_ORG=promo_actual.sales_org
      AND acct_promo_hdr.row_id = promo_actual.acct_promo_id
      and nvl(acct_promo_hdr.pr_accnt_id,0)=nvl(promo_actual.acct_siebel_rowid,0)
      and nvl(acct_promo_hdr.x_indirect_id,0)=nvl(promo_actual.indirect_acct_siebel_rowid,0)
      AND promo_actual.load_date >= TRUNC(SYSDATE)
      AND promo_actual.load_date < TRUNC(SYSDATE + 1)

      explain plan
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------------
      Plan hash value: 3864590768

      ----------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 298 | 2300 (1)| 00:00:28 |
      | 1 | COUNT | | | | | |
      |* 2 | FILTER | | | | | |
      | 3 | NESTED LOOPS | | | | | |
      | 4 | NESTED LOOPS | | 1 | 298 | 2300 (1)| 00:00:28 |
      | 5 | NESTED LOOPS OUTER | | 1 | 273 | 2298 (1)| 00:00:28 |
      | 6 | NESTED LOOPS | | 1 | 263 | 2296 (1)| 00:00:28 |
      | 7 | NESTED LOOPS | | 1 | 236 | 2295 (1)| 00:00:28 |
      | 8 | NESTED LOOPS | | 1 | 165 | 2292 (1)| 00:00:28 |
      | 9 | NESTED LOOPS | | 1 | 117 | 2289 (1)| 00:00:28 |
      | 10 | NESTED LOOPS | | 1 | 109 | 2289 (1)| 00:00:28 |
      | 11 | NESTED LOOPS | | 1 | 99 | 2287 (1)| 00:00:28 |
      |* 12 | TABLE ACCESS FULL | STG_SBL_ACCT_PROMO_ACTUALS2 | 1 | 49 | 2285 (1)| 00:0
      |* 13 | TABLE ACCESS BY INDEX ROWID| S_SRC | 1 | 50 | 2 (0)| 00:00:01 |
      |* 14 | INDEX UNIQUE SCAN | S_SRC_P1 | 1 | | 1 (0)| 00:00:01 |
      |* 15 | INDEX RANGE SCAN | S_SRC_X_U1 | 1 | 10 | 2 (0)| 00:00:01 |
      |* 16 | INDEX UNIQUE SCAN | S_BU_P1 | 1 | 8 | 0 (0)| 00:00:01 |
      |* 17 | TABLE ACCESS BY INDEX ROWID | S_SRC | 1 | 48 | 3 (0)| 00:00:01 |
      |* 18 | INDEX RANGE SCAN | S_SRC_F2 | 2 | | 2 (0)| 00:00:01 |
      |* 19 | TABLE ACCESS BY INDEX ROWID | S_SRC | 1 | 71 | 3 (0)| 00:00:01 |
      |* 20 | INDEX RANGE SCAN | S_SRC_F2 | 2 | | 2 (0)| 00:00:01 |
      | 21 | TABLE ACCESS BY INDEX ROWID | S_BU | 1 | 27 | 1 (0)| 00:00:01 |
      |* 22 | INDEX UNIQUE SCAN | S_BU_P1 | 1 | | 0 (0)| 00:00:01 |
      |* 23 | INDEX RANGE SCAN | S_SRC_X_U1 | 1 | 10 | 2 (0)| 00:00:01 |
      |* 24 | INDEX UNIQUE SCAN | S_PROD_INT_P1 | 1 | | 1 (0)| 00:00:01 |
      |* 25 | TABLE ACCESS BY INDEX ROWID | S_PROD_INT | 1 | 25 | 2 (0)| 00:00:
      ----------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter(TRUNC(SYSDATE@!)<TRUNC(SYSDATE@!+1))
      12 - filter("PROMO_ACTUAL"."LOAD_DATE">=TRUNC(SYSDATE@!) AND "PROMO_ACTUAL"."LOAD_DATE"<TRUNC(SYSD
      13 - filter("ACCT_PROMO_HDR"."SUB_TYPE"='PLAN_ACCOUNT_PROMOTION' AND
      NVL("ACCT_PROMO_HDR"."PR_ACCNT_ID",'0')=NVL("PROMO_ACTUAL"."ACCT_SIEBEL_ROWID",'0') AND
      NVL("ACCT_PROMO_HDR"."X_INDIRECT_ID",'0')=NVL("PROMO_ACTUAL"."INDIRECT_ACCT_SIEBEL_ROWID",'0'
      14 - access("ACCT_PROMO_HDR"."ROW_ID"="PROMO_ACTUAL"."ACCT_PROMO_ID")
      15 - access("ACCT_PROMO_HDR"."ROW_ID"="ACCT_PROMO_HDRX"."PAR_ROW_ID")
      16 - access("ACCT_PROMO_HDR"."BU_ID"="PROMO_HDR_ORG"."ROW_ID")
      17 - filter("ACCT_TITLE_FORMAT"."SUB_TYPE"='PLAN_ACCT_PROMOTION_CATEGORY')
      18 - access("ACCT_PROMO_HDR"."ROW_ID"="ACCT_TITLE_FORMAT"."PAR_SRC_ID")
      19 - filter("ACCT_PROMO_SKU"."PROD_ID" IS NOT NULL AND
      "ACCT_PROMO_SKU"."SUB_TYPE"='PLAN_ACCOUNT_PROMOTION_PRODUCT')
      20 - access("ACCT_TITLE_FORMAT"."ROW_ID"="ACCT_PROMO_SKU"."PAR_SRC_ID")
      22 - access("ACCT_PROMO_SKU"."BU_ID"="SKU_ORG"."ROW_ID")
      23 - access("ACCT_PROMO_SKU"."ROW_ID"="ACCT_PROMO_SKUX"."PAR_ROW_ID"(+))
      24 - access("ACCT_PROMO_SKU"."PROD_ID"="PROD"."ROW_ID")
      25 - filter("PROD"."X_PROD_MATERIAL_NUM" IS NOT NULL AND
      "PROD"."X_PROD_MATERIAL_NUM"="PROMO_ACTUAL"."MATERIAL_NUMBER" AND
      "PROD"."X_PROD_SALES_ORG"="PROMO_ACTUAL"."SALES_ORG")

      55 rows selected.

      thanks
        • 1. Re: help for query tuning
          Purvesh K
          Please use
           (exactly as written and with curly braces) tags before and after the SQL, as well as the Execution plan.                                                                                                                                                                                                                                                    
          • 2. Re: help for query tuning
            Nikolay Savvinov
            Hi,

            the plan you posted has the cost of 2300, i.e. 2300 single-block reads or equivalent number f multi-block reads. Even if none of the blocks is found in cache, 2300 reas shouldn't take more than a couple of minutes, beacause for most of the hard drives available today a disk read is typically within 5-10 ms.

            This means that if there is a problem, we will never find out about it by looking in the plan. And it's quite likely that there is, in fact, a problem, because the plan contains a bunch of nested joins, and the cost of each nested join is directly proportional to the cardinality of the previous nested loop. I.e. it suffices to make one bad mistake in estimating the number of rows coming fom one of the nested rows to screw up the entire plan and get all remaining estimates (including the total cost of the query) completely wrong.

            In order for us to be able to tell more, we need to see the plan with rowsource statistics, and please don't forget to use
             tags to preserve formatting (use the preview tab to make sure the posted plan is actually readable).
            
            Best regards,
              Nikolay                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: help for query tuning
              siebelD
              thank you for your response ..I will take care
               tag thing on my next post ..I was not aware about this .
              Please let me know how I can take explain plan with rows statistics ..is there any specific query for that .