12 Replies Latest reply: Oct 12, 2012 7:31 AM by Purvesh K RSS

    Can any one help me to tune thish query as its taking more than 1 hour

    966404
      SELECT cb.INVOICE_NUMBER "DOCUMENT_NUMBER",
      cb.location_NUMBER "LOCATION_NUMBER",
      cb.INVOICE_DATE "DOCUMENT_DATE",
      cb.VENDOR_NUMBER "VENDOR NUMBER",
      vd.VENDOR_VNAME "VENDOR NAME",
      ct.TYPE_DESCRIPTION "TYPE_DESCRIPTION",
      ca.approver_name "CREATOR ",
      cb.SHORT_DESC "BRIEF DESCRIPTION",
      cb.REASON_CODE "REASON_CODE",
      cb.PRODUCT_GROUP_CODE "PRODUCT_GROUP_CODE",
      '$ ' || SUM(ci.Item_Price*ci.Item_Quantity) "AMOUNT" ,
      cd.DIST_LOC_NUMBER "DIST_LOC_NUMBER",
      cd.account_number "ACCOUNT NUMBER",
      '$ ' || cd.distribution_amount "DISTRIBUTION AMOUNT"
      from CBK_CHARGEBACK cb,CBK_PRODUCT_GROUP pg,CBK_TYPE ct,CBK_ITEM ci,cbk_distribution cd,cbk_approver ca,APVENMAST_V vd,cbk_audit dt
      where
      cb.TYPE_ID = 1 AND
      dt.exported_date>='09-SEP-12' and dt.exported_date <= '06-OCT-12'
      and cb.TYPE_ID = ct.TYPE_ID(+)
      and cb.INVOICE_NUMBER = dt.INVOICE_NUMBER(+)
      and cb.location_NUMBER = dt.location_NUMBER(+)
      and cb.INVOICE_NUMBER = ci.INVOICE_NUMBER(+)
      and cb.location_NUMBER = ci.location_NUMBER(+)
      and cb.INVOICE_NUMBER = cd.INVOICE_NUMBER(+)
      and cb.location_NUMBER = cd.location_NUMBER(+)
      and cb.PRODUCT_GROUP_CODE=pg.PRODUCT_GROUP_CODE(+)
      and trim(CAST(substr(trim(leading 'O' from cb.vendor_number),0,7) AS INT)) = TRIM(vd.vendor(+))
      and cb.creator_id = ca.approver_id(+)
      GROUP BY cd.product_group,vd.vendor,cb.INVOICE_NUMBER,dt.location_NUMBER,ci.INVOICE_NUMBER,cd.INVOICE_NUMBER,cb.INVOICE_DATE,ct.TYPE_DESCRIPTION,cd.distribution_amount,cd.DIST_LOC_NUMBER ,
      cb.VENDOR_NUMBER,cb.SHORT_DESC,cb.location_NUMBER,ci.location_NUMBER,cd.location_NUMBER,cb.PRODUCT_GROUP_CODE ,cb.REASON_CODE,cd.account_number,ca.approver_name,vd.VENDOR_VNAME
      ORDER BY
      cb.INVOICE_NUMBER,cd.account_number;





      Explain plan:


      Plan Table
      --------------------------------------------------------------------------------
      | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
      --------------------------------------------------------------------------------
      | SELECT STATEMENT | | 6K| 1M| 7618 | | |
      | SORT GROUP BY | | 6K| 1M| 7618 | | |
      | NESTED LOOPS OUTER | | 6K| 1M| 7408 | | |
      | NESTED LOOPS OUTER | | 1 | 212 | 6957 | | |
      | NESTED LOOPS OUTER | | 1 | 187 | 6956 | | |
      | NESTED LOOPS OUTER | | 1 | 172 | 6955 | | |
      | NESTED LOOPS OUTER | | 3 | 459 | 6943 | | |
      | NESTED LOOPS OUTER | | 1K| 119K| 2943 | | |
      | HASH JOIN | | 1K| 147K| 2943 | | |

      Plan Table
      --------------------------------------------------------------------------------
      | INDEX FAST FULL S|CBK_AUDIT | 8K| 182K| 493 | | |
      | TABLE ACCESS FULL|CBK_CHARG | 261K| 24M| 2421 | | |
      | INDEX UNIQUE SCAN |CBK_PRODU | 9 | 27 | | | |
      | TABLE ACCESS BY IND|CBK_DISTR | 2M| 60M| 4 | | |
      | INDEX RANGE SCAN |PK_CBK_DI | 2M| | 3 | | |
      | TABLE ACCESS BY INDE|CBK_ITEM | 4M| 83M| 4 | | |
      | INDEX RANGE SCAN |PK_CBK_IT | 4M| | 3 | | |
      | TABLE ACCESS BY INDEX|CBK_TYPE | 7 | 105 | 1 | | |
      | INDEX UNIQUE SCAN |PK_CBK_TY | 7 | | | | |
      | TABLE ACCESS BY INDEX |CBK_APPRO | 4K| 121K| 1 | | |
      | INDEX UNIQUE SCAN |PK_CBK_AP | 4K| | | | |

      Plan Table
      --------------------------------------------------------------------------------
      | REMOTE | | 603K| 13M| | | |
      --------------------------------------------------------------------------------
        • 1. Re: Can any one help me to tune thish query as its taking more than 1 hour
          966404
          Plan Table
          --------------------------------------------------------------------------------
          | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
          --------------------------------------------------------------------------------
          | SELECT STATEMENT | | 6K| 1M| 7618 | | |
          | SORT GROUP BY | | 6K| 1M| 7618 | | |
          | NESTED LOOPS OUTER | | 6K| 1M| 7408 | | |
          | NESTED LOOPS OUTER | | 1 | 212 | 6957 | | |
          | NESTED LOOPS OUTER | | 1 | 187 | 6956 | | |
          | NESTED LOOPS OUTER | | 1 | 172 | 6955 | | |
          | NESTED LOOPS OUTER | | 3 | 459 | 6943 | | |
          | NESTED LOOPS OUTER | | 1K| 119K| 2943 | | |
          | HASH JOIN | | 1K| 147K| 2943 | | |

          Plan Table
          --------------------------------------------------------------------------------
          | INDEX FAST FULL S|CBK_AUDIT | 8K| 182K| 493 | | |
          | TABLE ACCESS FULL|CBK_CHARG | 261K| 24M| 2421 | | |
          | INDEX UNIQUE SCAN |CBK_PRODU | 9 | 27 | | | |
          | TABLE ACCESS BY IND|CBK_DISTR | 2M| 60M| 4 | | |
          | INDEX RANGE SCAN |PK_CBK_DI | 2M| | 3 | | |
          | TABLE ACCESS BY INDE|CBK_ITEM | 4M| 83M| 4 | | |
          | INDEX RANGE SCAN |PK_CBK_IT | 4M| | 3 | | |
          | TABLE ACCESS BY INDEX|CBK_TYPE | 7 | 105 | 1 | | |
          | INDEX UNIQUE SCAN |PK_CBK_TY | 7 | | | | |
          | TABLE ACCESS BY INDEX |CBK_APPRO | 4K| 121K| 1 | | |
          | INDEX UNIQUE SCAN |PK_CBK_AP | 4K| | | | |

          Plan Table
          --------------------------------------------------------------------------------
          | REMOTE | | 603K| 13M| | | |
          --------------------------------------------------------------------------------
          • 2. Re: Can any one help me to tune thish query as its taking more than 1 hour
            Osama_Mustafa
            i am getting tired while reading your query , please format your question First
            • 3. Re: Can any one help me to tune thish query as its taking more than 1 hour
              JohnWatson
              This is unreadable. But even from the unformatted code, there are a few clear issues.
              First, this
              dt.exported_date>='09-SEP-12' and dt.exported_date <= '06-OCT-12' 
              is probably comparing a date with a string, which is a bug waiting to happen.
              Second, this
              and trim(CAST(substr(trim(leading 'O' from cb.vendor_number),0,7) AS INT)) = TRIM(vd.vendor(+))
              is using functions in a way that may inhibit use of indexes, and also looks like another data type mismatch.
              Third, all those outer joins - are they necessary? If you know and understand your data, you can probably get rid of them, which will give the optimizer more options.
              Fourth, that GROUP BY. Is it actually necessary? Again, do you really know and understand your data?
              • 4. Re: Can any one help me to tune thish query as its taking more than 1 hour
                966404
                Please find the formatted query.......

                --------------------
                SELECT cb.INVOICE_NUMBER "DOCUMENT_NUMBER", cb.location_NUMBER "LOCATION_NUMBER", cb.INVOICE_DATE "DOCUMENT_DATE",

                cb.VENDOR_NUMBER "VENDOR NUMBER", vd.VENDOR_VNAME "VENDOR NAME", ct.TYPE_DESCRIPTION "TYPE_DESCRIPTION",

                ca.approver_name "CREATOR ", cb.SHORT_DESC "BRIEF DESCRIPTION", cb.REASON_CODE "REASON_CODE", cb.PRODUCT_GROUP_CODE

                "PRODUCT_GROUP_CODE", '$ ' || SUM(ci.Item_Price*ci.Item_Quantity) "AMOUNT" , cd.DIST_LOC_NUMBER "DIST_LOC_NUMBER", cd.account_number "ACCOUNT

                NUMBER", '$ ' || cd.distribution_amount "DISTRIBUTION AMOUNT"


                from CBK_CHARGEBACK cb,CBK_PRODUCT_GROUP pg,CBK_TYPE ct,CBK_ITEM ci,cbk_distribution cd,cbk_approver ca,APVENMAST_V vd,cbk_audit dt

                where

                cb.TYPE_ID = 1 AND dt.exported_date>='09-SEP-12' and dt.exported_date <= '06-OCT-12' and cb.TYPE_ID = ct.TYPE_ID(+) and

                cb.INVOICE_NUMBER = dt.INVOICE_NUMBER(+) and cb.location_NUMBER = dt.location_NUMBER(+)

                and cb.INVOICE_NUMBER = ci.INVOICE_NUMBER(+) and cb.location_NUMBER = ci.location_NUMBER(+)

                and cb.INVOICE_NUMBER = cd.INVOICE_NUMBER(+) and cb.location_NUMBER = cd.location_NUMBER(+)

                and cb.PRODUCT_GROUP_CODE=pg.PRODUCT_GROUP_CODE(+) and trim(CAST(substr(trim(leading 'O' from cb.vendor_number),0,7) AS INT)) = TRIM(vd.vendor(+))

                and cb.creator_id = ca.approver_id(+)

                GROUP BY

                cd.product_group,vd.vendor,cb.INVOICE_NUMBER,dt.location_NUMBER,ci.INVOICE_NUMBER,cd.INVOICE_NUMBER,cb.INVOICE_DATE,ct.TYPE_DESCRIPTION,cd.di

                stribution_amount,cd.DIST_LOC_NUMBER ,

                cb.VENDOR_NUMBER,cb.SHORT_DESC,cb.location_NUMBER,ci.location_NUMBER,cd.location_NUMBER,cb.PRODUCT_GROUP_CODE

                ,cb.REASON_CODE,cd.account_number,ca.approver_name,vd.VENDOR_VNAME

                ORDER BY

                cb.INVOICE_NUMBER,cd.account_number;
                • 5. Re: Can any one help me to tune thish query as its taking more than 1 hour
                  JohnWatson
                  This is a formatted query and execution plan:
                  orcl> EXPLAIN PLAN FOR
                    2  SELECT ename,
                    3         dname
                    4  FROM   emp
                    5         NATURAL join dept
                    6  WHERE  dname = 'SALES';
                  
                  Explained.
                  
                  orcl> select * from table(dbms_xplan.display);
                  
                  PLAN_TABLE_OUTPUT
                  ----------------------------------------------------------------------------------------
                  
                  Plan hash value: 844388907
                  
                  ----------------------------------------------------------------------------------------
                  | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                  ----------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT             |         |     5 |   110 |     6  (17)| 00:00:01 |
                  |   1 |  MERGE JOIN                  |         |     5 |   110 |     6  (17)| 00:00:01 |
                  |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2   (0)| 00:00:01 |
                  |   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
                  |*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
                  |   5 |    TABLE ACCESS STORAGE FULL | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
                  ----------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     2 - filter("DEPT"."DNAME"='SALES')
                     4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
                         filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
                  
                  19 rows selected.
                  
                  orcl>
                  • 6. Re: Can any one help me to tune thish query as its taking more than 1 hour
                    966404
                    SQL> explain plan for SELECT cb.INVOICE_NUMBER "DOCUMENT_NUMBER",
                    cb.location_NUMBER "LOCATION_NUMBER",
                    2 3 cb.INVOICE_DATE "DOCUMENT_DATE",
                    4 cb.VENDOR_NUMBER "VENDOR NUMBER",
                    5 vd.VENDOR_VNAME "VENDOR NAME",
                    6 ct.TYPE_DESCRIPTION "TYPE_DESCRIPTION",
                    7 ca.approver_name "CREATOR ",
                    8 cb.SHORT_DESC "BRIEF DESCRIPTION",
                    9 cb.REASON_CODE "REASON_CODE",
                    10 cb.PRODUCT_GROUP_CODE "PRODUCT_GROUP_CODE",
                    11 '$ ' || SUM(ci.Item_Price*ci.Item_Quantity) "AMOUNT" ,
                    12 cd.DIST_LOC_NUMBER "DIST_LOC_NUMBER",
                    13 cd.account_number "ACCOUNT NUMBER",
                    14 '$ ' || cd.distribution_amount "DISTRIBUTION AMOUNT"
                    15 from CBK_CHARGEBACK cb,CBK_PRODUCT_GROUP pg,CBK_TYPE ct,CBK_ITEM ci,cbk_distribution cd,cbk_approver ca,APVENMAST_V vd,cbk_audit dt
                    16 where
                    17 cb.TYPE_ID = 1 AND
                    18 dt.exported_date>='09-SEP-12' and dt.exported_date <= '06-OCT-12'
                    19 and cb.TYPE_ID = ct.TYPE_ID(+)
                    20 and cb.INVOICE_NUMBER = dt.INVOICE_NUMBER(+)
                    21 and cb.location_NUMBER = dt.location_NUMBER(+)
                    22 and cb.INVOICE_NUMBER = ci.INVOICE_NUMBER(+)
                    23 and cb.location_NUMBER = ci.location_NUMBER(+)
                    24 and cb.INVOICE_NUMBER = cd.INVOICE_NUMBER(+)
                    25 and cb.location_NUMBER = cd.location_NUMBER(+)
                    26 and cb.PRODUCT_GROUP_CODE=pg.PRODUCT_GROUP_CODE(+)
                    27 and trim(CAST(substr(trim(leading 'O' from cb.vendor_number),0,7) AS INT)) = TRIM(vd.vendor(+))
                    28 and cb.creator_id = ca.approver_id(+)
                    29 GROUP BY cd.product_group,vd.vendor,cb.INVOICE_NUMBER,dt.location_NUMBER,ci.INVOICE_NUMBER,cd.INVOICE_NUMBER,cb.INVOICE_DATE,ct.TYPE_DESCRIPTION,cd.distribution_amount,cd.DIST_LOC_NUMBER ,
                    30 cb.VENDOR_NUMBER,cb.SHORT_DESC,cb.location_NUMBER,ci.location_NUMBER,cd.location_NUMBER,cb.PRODUCT_GROUP_CODE ,cb.REASON_CODE,cd.account_number,ca.approver_name,vd.VENDOR_VNAME
                    31 ORDER BY
                    32 cb.INVOICE_NUMBER,cd.account_number;

                    Explained.

                    SQL> @utlxpls.sql
                    Plan Table
                    --------------------------------------------------------------------------------
                    | Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
                    --------------------------------------------------------------------------------
                    | SELECT STATEMENT          |          |     6K|    1M|   7618 |       |       |
                    |  SORT GROUP BY            |          |     6K|    1M|   7618 |       |       |
                    |   NESTED LOOPS OUTER      |          |     6K|    1M|   7408 |       |       |
                    |    NESTED LOOPS OUTER     |          |     1 |  212 |   6957 |       |       |
                    |     NESTED LOOPS OUTER    |          |     1 |  187 |   6956 |       |       |
                    |      NESTED LOOPS OUTER   |          |     1 |  172 |   6955 |       |       |
                    |       NESTED LOOPS OUTER  |          |     3 |  459 |   6943 |       |       |
                    |        NESTED LOOPS OUTER |          |     1K|  119K|   2943 |       |       |
                    |         HASH JOIN         |          |     1K|  147K|   2943 |       |       |
                    
                    Plan Table
                    --------------------------------------------------------------------------------
                    |          INDEX FAST FULL S|CBK_AUDIT |     8K|  182K|    493 |       |       |
                    |          TABLE ACCESS FULL|CBK_CHARG |   261K|   24M|   2421 |       |       |
                    |         INDEX UNIQUE SCAN |CBK_PRODU |     9 |   27 |        |       |       |
                    |        TABLE ACCESS BY IND|CBK_DISTR |     2M|   60M|      4 |       |       |
                    |         INDEX RANGE SCAN  |PK_CBK_DI |     2M|      |      3 |       |       |
                    |       TABLE ACCESS BY INDE|CBK_ITEM  |     4M|   83M|      4 |       |       |
                    |        INDEX RANGE SCAN   |PK_CBK_IT |     4M|      |      3 |       |       |
                    |      TABLE ACCESS BY INDEX|CBK_TYPE  |     7 |  105 |      1 |       |       |
                    |       INDEX UNIQUE SCAN   |PK_CBK_TY |     7 |      |        |       |       |
                    |     TABLE ACCESS BY INDEX |CBK_APPRO |     4K|  121K|      1 |       |       |
                    |      INDEX UNIQUE SCAN    |PK_CBK_AP |     4K|      |        |       |       |
                    
                    Plan Table
                    --------------------------------------------------------------------------------
                    |    REMOTE                 |          |   603K|   13M|        |       |       |
                    --------------------------------------------------------------------------------
                    • 7. Re: Can any one help me to tune thish query as its taking more than 1 hour
                      Purvesh K
                      963401 wrote:
                      Please find the formatted query.......
                      No, it isn't formatted.

                      Use
                       (exactly the way written) before and after the SQL Query, Explain Plan.
                      
                      It makes the post readable.
                      
                      Also, I am not sure how you are fetching the explain plan, but correct way of doing so is:
                      
                      
                      select * from table(dbms_xplan.display);
                      Another fact that, it will be a lot better to have the actual run statistics and details fetched from the TKPROF and provided in Formatted way (as suggested using code tags). Edited by: Purvesh K on Oct 12, 2012 4:50 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                      • 8. Re: Can any one help me to tune thish query as its taking more than 1 hour
                        John Stegeman
                        963401,

                        You need to check this out: http://bit.ly/QVt8VC

                        John
                        • 9. Re: Can any one help me to tune thish query as its taking more than 1 hour
                          966404
                          My database is of 8i--

                          BANNER
                          ----------------------------------------------------------------
                          Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
                          PL/SQL Release 8.1.6.3.0 - Production
                          CORE 8.1.6.0.0 Production
                          TNS for Solaris: Version 8.1.6.3.0 - Production
                          NLSRTL Version 3.4.0.0.0 - Production

                          so i cant use dbms_xplan package to fetch the explain plan.. thanks
                          • 10. Re: Can any one help me to tune thish query as its taking more than 1 hour
                            John Stegeman
                            You didn't respond to JohnWatson's comments. It also appears to me as if you have a database link involved, which will obviously be slower than using local tables. You haven't provided tkprofs as requested either.

                            This isn't a "fix my query" forum. Particularly for people who don't read the replies they are given.
                            • 11. Re: Can any one help me to tune thish query as its taking more than 1 hour
                              Purvesh K
                              963401 wrote:
                              My database is of 8i--

                              BANNER
                              ----------------------------------------------------------------
                              Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
                              PL/SQL Release 8.1.6.3.0 - Production
                              CORE 8.1.6.0.0 Production
                              TNS for Solaris: Version 8.1.6.3.0 - Production
                              NLSRTL Version 3.4.0.0.0 - Production

                              so i cant use dbms_xplan package to fetch the explain plan.. thanks
                              I can't blame it on you to use an Age old and unsupported version of Oracle, but you definitely need an Upgrade atleast till 9i.

                              Now, since you can't provide explain plan, TKPROF is available and the best help.

                              Read this, if you are not aware of steps to fetching TKPROF. Without you providing details, do not expect us to help you.
                              • 12. Re: Can any one help me to tune thish query as its taking more than 1 hour
                                Osama_Mustafa
                                First : 8i is out of support .
                                second : did you read my post about formatting your question its unreadable to us