This discussion is archived
12 Replies Latest reply: Oct 12, 2012 5:31 AM by Purvesh K RSS

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

966404 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    First : 8i is out of support .
    second : did you read my post about formatting your question its unreadable to us

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points