1 2 Previous Next 16 Replies Latest reply: Feb 26, 2014 10:33 AM by 926913 RSS

    Query running long time

    926913

      HI All,

       

      can some one help me to understand why this query is running long time. I waited for 4 hours and killed the session. Please let me know if you have any questions. Thanks in advance.

       

      Im using oracle 9

       

      SELECT

        ITEM_MASTER.ITEM_NUMBER,

        ITEM_MASTER.ITEM_DESC1,

        ITEM_MASTER.ITEM_DESC2,

        to_char(ITEM_MASTER.ITEM_TYPE_CODE),

        FISCAL_WEEK.FISCAL_MONTH,

        count(distinct INVOICE_LINE.DB_CODE||INVOICE_LINE.ORDER_NUMBER||INVOICE_LINE.INV_LINE),

        count(distinct INVOICE_LINE.DB_CODE||INVOICE_LINE.ORDER_NUMBER),

        count(distinct INVOICE_LINE.DB_CODE||INVOICE_LINE.INV_NB||INVOICE_LINE.ORDER_NUMBER||INVOICE_LINE.INV_LINE),

        count(distinct INVOICE_LINE.DB_CODE||INVOICE_LINE.INV_NB)

      FROM

        ITEM_MASTER,

        FISCAL_WEEK,

        INVOICE_LINE,

        DB_MASTER

      WHERE

        ( INVOICE_LINE.ITEM_ID = ITEM_MASTER.ITEM_ID  )

        AND  ( INVOICE_LINE.FISCAL_WEEK = FISCAL_WEEK.FISCAL_WEEK  )

        AND  ( INVOICE_LINE.DB_CODE = DB_MASTER.DB_CODE  )

        AND  ( (INVOICE_LINE.DB_CODE IN ('3180','3280','3500','3370'))  )

        AND

        (

         ( DB_MASTER.DB_CODE='3180'  )

         AND

         ITEM_MASTER.ITEM_NUMBER  IN

           (

           SELECT

             ITEM_MASTER.ITEM_NUMBER

           FROM

             ITEM_MASTER,

             INVENTORY_LOCATION,

             DB_MASTER

           WHERE

             ( DB_MASTER.DB_CODE = INVENTORY_LOCATION.DB_CODE  )

             AND  ( ITEM_MASTER.ITEM_ID = INVENTORY_LOCATION.ITEM_ID  )

        AND  ( (INVENTORY_LOCATION.DB_CODE IN ('3180','3280','3500','3370'))  )

             AND

             (

              to_char(ITEM_MASTER.ITEM_TYPE_CODE)  BETWEEN  '3A'  AND  '4D'

              AND

              INVENTORY_LOCATION.LOC_CODE  LIKE  '1%'

              AND

              ( DB_MASTER.DB_CODE='3180'  )

             )

           )

         AND

         FISCAL_WEEK.REL_MONTH  BETWEEN  -12  AND  -1

        )

      GROUP BY

        ITEM_MASTER.ITEM_NUMBER,

        ITEM_MASTER.ITEM_DESC1,

        ITEM_MASTER.ITEM_DESC2,

        to_char(ITEM_MASTER.ITEM_TYPE_CODE),

        FISCAL_WEEK.FISCAL_MONTH

        • 1. Re: Query running long time
          David Berger

          Hello 92..

           

          The information you gave are not enough in order we can give you advice...

           

          We should know more about the environment, table-size etc..

          • 2. Re: Query running long time
            926913

            Hello David,

             

            first of all thank you for the response, to be frank i do not have much hands on experience in SQl, This query is automatically generated from one of the reporting tools. and more over i think i just had a read access to DB. I will read from google to find out the table size and let you know.

             

            Thanks again.

            • 3. Re: Query running long time
              Brian Tkatch

              A minor comment, you can try changing the IN to an EXISTS.

              • 4. Re: Query running long time
                926913

                Thanks Brian, let me try this

                • 5. Re: Query running long time
                  926913

                  Brian,

                   

                  i believe you are referring to "IN" before sub query in the where clause. i did the same, but its throwing a error like invalid relational operator

                  • 6. Re: Query running long time
                    David Berger

                    Hello 92..

                     

                    It would be good if you could give the result of the following sql:

                     

                    You can execute this in sql*plus:

                    SET LINESIZE 150 PAGESIZE 9999 ECHO ON TERMOUT ON SERVEROUTPUT ON


                    EXPLAIN PLAN FOR

                    <your_sql_statement>

                    ;

                     

                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

                    • 7. Re: Query running long time
                      926913

                      Dear David,

                       

                      EXPLAIN PLAN FOR

                      <your_sql_statement>

                      ;  --- this part is working properly and in the results it is showing Explain plan succeeds.


                      But this statement is creating a problem -- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

                      error is

                      ORA-00907: missing right parenthesis

                      00907. 00000 -  "missing right parenthesis"

                      *Cause:   

                      *Action:

                      Error at Line: 57 Column: 46

                       

                      please let me know if i am missing some thing.


                      • 8. Re: Query running long time
                        David Berger

                        Ok, can you try it so:

                        SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'ALL'));

                        You need to execute the whole sql-block with the new row above!

                        • 9. Re: Query running long time
                          926913

                          Excellent... Please see

                           

                          Plan hash value: 1710602365

                           

                          -------------------------------------------------------------------------------------------------------------------------------

                          | Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                          -------------------------------------------------------------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT                      |                       |  2568 |   386K| 40269   (2)| 00:08:04 |       |       |

                          |   1 |  SORT GROUP BY                        |                       |  2568 |   386K| 40269   (2)| 00:08:04 |       |       |

                          |*  2 |   HASH JOIN                           |                       |  2568 |   386K| 40267   (2)| 00:08:04 |       |       |

                          |*  3 |    INDEX FAST FULL SCAN               | PK_INVENTORY_LOCATION | 10717 |   198K|    94   (3)| 00:00:02 |       |       |

                          |*  4 |    HASH JOIN                          |                       |  2492 |   328K| 40172   (2)| 00:08:03 |       |       |

                          |*  5 |     TABLE ACCESS FULL                 | FISCAL_WEEK           |    62 |   930 |     4   (0)| 00:00:01 |       |       |

                          |*  6 |     TABLE ACCESS BY GLOBAL INDEX ROWID| INVOICE_LINE          |    20 |   760 |   264   (1)| 00:00:04 | ROWID | ROWID |

                          |   7 |      NESTED LOOPS                     |                       | 28459 |  3335K| 40165   (2)| 00:08:02 |       |       |

                          |   8 |       NESTED LOOPS                    |                       |  1398 |   111K|  9946   (4)| 00:02:00 |       |       |

                          |   9 |        NESTED LOOPS                   |                       |  1295 | 44030 |  6040   (6)| 00:01:13 |       |       |

                          |  10 |         NESTED LOOPS                  |                       |     1 |    10 |     0   (0)| 00:00:01 |       |       |

                          |* 11 |          INDEX UNIQUE SCAN            | PK_DB_MASTER          |     1 |     5 |     0   (0)| 00:00:01 |       |       |

                          |* 12 |          INDEX UNIQUE SCAN            | PK_DB_MASTER          |     1 |     5 |     0   (0)| 00:00:01 |       |       |

                          |* 13 |         TABLE ACCESS FULL             | ITEM_MASTER           |  1295 | 31080 |  6040   (6)| 00:01:13 |       |       |

                          |  14 |        TABLE ACCESS BY INDEX ROWID    | ITEM_MASTER           |     1 |    48 |     3   (0)| 00:00:01 |       |       |

                          |* 15 |         INDEX RANGE SCAN              | IU_ITEM_NUMBER        |     1 |       |     2   (0)| 00:00:01 |       |       |

                          |* 16 |       INDEX RANGE SCAN                | I_INVOICE_ITEM        |   321 |       |     2   (0)| 00:00:01 |       |       |

                          -------------------------------------------------------------------------------------------------------------------------------

                           

                          Query Block Name / Object Alias (identified by operation id):

                          -------------------------------------------------------------

                           

                             1 - SEL$5DA710D3

                             3 - SEL$5DA710D3 / INVENTORY_LOCATION@SEL$2

                             5 - SEL$5DA710D3 / FISCAL_WEEK@SEL$1

                             6 - SEL$5DA710D3 / INVOICE_LINE@SEL$1

                            11 - SEL$5DA710D3 / DB_MASTER@SEL$1

                            12 - SEL$5DA710D3 / DB_MASTER@SEL$2

                            13 - SEL$5DA710D3 / ITEM_MASTER@SEL$2

                            14 - SEL$5DA710D3 / ITEM_MASTER@SEL$1

                            15 - SEL$5DA710D3 / ITEM_MASTER@SEL$1

                            16 - SEL$5DA710D3 / INVOICE_LINE@SEL$1

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                             2 - access("ITEM_MASTER"."ITEM_ID"="INVENTORY_LOCATION"."ITEM_ID")

                             3 - filter("INVENTORY_LOCATION"."LOC_CODE" LIKE '1%' AND "INVENTORY_LOCATION"."DB_CODE"='3180')

                             4 - access("INVOICE_LINE"."FISCAL_WEEK"="FISCAL_WEEK"."FISCAL_WEEK")

                             5 - filter("FISCAL_WEEK"."REL_MONTH">=(-12) AND "FISCAL_WEEK"."REL_MONTH"<=(-1))

                             6 - filter("INVOICE_LINE"."DB_CODE"='3180')

                            11 - access("DB_MASTER"."DB_CODE"='3180')

                            12 - access("DB_MASTER"."DB_CODE"='3180')

                            13 - filter("ITEM_MASTER"."ITEM_TYPE_CODE">='3A' AND "ITEM_MASTER"."ITEM_TYPE_CODE"<='4D')

                            15 - access("ITEM_MASTER"."ITEM_NUMBER"="ITEM_MASTER"."ITEM_NUMBER")

                            16 - access("INVOICE_LINE"."ITEM_ID"="ITEM_MASTER"."ITEM_ID")

                           

                          Column Projection Information (identified by operation id):

                          -----------------------------------------------------------

                           

                             1 - "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22], "ITEM_MASTER"."ITEM_DESC1"[VARCHAR2,24],

                                 "ITEM_MASTER"."ITEM_DESC2"[VARCHAR2,24], "ITEM_MASTER"."ITEM_TYPE_CODE"[VARCHAR2,8],

                                 "FISCAL_WEEK"."FISCAL_MONTH"[NUMBER,22], COUNT(DISTINCT "INVOICE_LINE"."DB_CODE"||"INVOICE_LINE"."INV_NB")[22],

                                 COUNT(DISTINCT "INVOICE_LINE"."DB_CODE"||"INVOICE_LINE"."INV_NB"||"INVOICE_LINE"."ORDER_NUMBER"||TO_CHAR("INVOICE_LINE"

                                 ."INV_LINE"))[22], COUNT(DISTINCT "INVOICE_LINE"."DB_CODE"||"INVOICE_LINE"."ORDER_NUMBER")[22], COUNT(DISTINCT

                                 "INVOICE_LINE"."DB_CODE"||"INVOICE_LINE"."ORDER_NUMBER"||TO_CHAR("INVOICE_LINE"."INV_LINE"))[22]

                           

                             2 - "FISCAL_WEEK"."FISCAL_MONTH"[NUMBER,22], "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22],

                                 "ITEM_MASTER"."ITEM_DESC1"[VARCHAR2,24], "ITEM_MASTER"."ITEM_DESC2"[VARCHAR2,24],

                                 "ITEM_MASTER"."ITEM_TYPE_CODE"[VARCHAR2,8], "INVOICE_LINE"."DB_CODE"[VARCHAR2,8], "INVOICE_LINE"."INV_NB"[VARCHAR2,8],

                                 "INVOICE_LINE"."INV_LINE"[NUMBER,22], "INVOICE_LINE"."ORDER_NUMBER"[VARCHAR2,8]

                           

                             3 - "INVENTORY_LOCATION"."ITEM_ID"[NUMBER,22]

                           

                             4 - "FISCAL_WEEK"."FISCAL_MONTH"[NUMBER,22], "ITEM_MASTER"."ITEM_ID"[NUMBER,22],

                                 "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22], "ITEM_MASTER"."ITEM_DESC1"[VARCHAR2,24],

                                 "ITEM_MASTER"."ITEM_DESC2"[VARCHAR2,24], "ITEM_MASTER"."ITEM_TYPE_CODE"[VARCHAR2,8],

                                 "INVOICE_LINE"."DB_CODE"[VARCHAR2,8], "INVOICE_LINE"."INV_NB"[VARCHAR2,8], "INVOICE_LINE"."INV_LINE"[NUMBER,22],

                                 "INVOICE_LINE"."ORDER_NUMBER"[VARCHAR2,8]

                           

                             5 - "FISCAL_WEEK"."FISCAL_WEEK"[NUMBER,22], "FISCAL_WEEK"."FISCAL_MONTH"[NUMBER,22]

                           

                             6 - "INVOICE_LINE"."DB_CODE"[VARCHAR2,8], "INVOICE_LINE"."INV_NB"[VARCHAR2,8],

                                 "INVOICE_LINE"."INV_LINE"[NUMBER,22], "INVOICE_LINE"."ORDER_NUMBER"[VARCHAR2,8],

                                 "INVOICE_LINE"."FISCAL_WEEK"[NUMBER,22]

                           

                             7 - "ITEM_MASTER"."ITEM_ID"[NUMBER,22], "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22],

                                 "ITEM_MASTER"."ITEM_DESC1"[VARCHAR2,24], "ITEM_MASTER"."ITEM_DESC2"[VARCHAR2,24],

                                 "ITEM_MASTER"."ITEM_TYPE_CODE"[VARCHAR2,8], "INVOICE_LINE".ROWID[ROWID,10]

                           

                             8 - "ITEM_MASTER"."ITEM_ID"[NUMBER,22], "ITEM_MASTER"."ITEM_ID"[NUMBER,22],

                                 "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22], "ITEM_MASTER"."ITEM_DESC1"[VARCHAR2,24],

                                 "ITEM_MASTER"."ITEM_DESC2"[VARCHAR2,24], "ITEM_MASTER"."ITEM_TYPE_CODE"[VARCHAR2,8]

                           

                             9 - "ITEM_MASTER"."ITEM_ID"[NUMBER,22], "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22]

                           

                            13 - "ITEM_MASTER"."ITEM_ID"[NUMBER,22], "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22]

                           

                            14 - "ITEM_MASTER"."ITEM_ID"[NUMBER,22], "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22],

                                 "ITEM_MASTER"."ITEM_DESC1"[VARCHAR2,24], "ITEM_MASTER"."ITEM_DESC2"[VARCHAR2,24],

                                 "ITEM_MASTER"."ITEM_TYPE_CODE"[VARCHAR2,8]

                           

                            15 - "ITEM_MASTER".ROWID[ROWID,10], "ITEM_MASTER"."ITEM_NUMBER"[VARCHAR2,22]

                           

                            16 - "INVOICE_LINE".ROWID[ROWID,10]

                          • 10. Re: Query running long time
                            David Berger

                            Ok, thanks.

                             

                            1.) What are about statistics? -> As you wrote you waited 4 hours and you killed the session .. -> Here we can see that the optimizer estimates about 8 minutes running time -> But it is only an estimating... we can not take it seriously..

                               You can check it in the dba_tables:

                            SELECT num_rows, sample_size, last_analyzed FROM DBA_TABLES WHERE table_name IN ('your_table_1', 'your_table_2', ... );

                             

                            2.) What I do not like is the complex statement after the IN - Operator. I Would make a structure change if it is possible...

                             

                            3.) There is filter-predicate where I ask it is really necessary to make a TO_CHAR? Is it a number column?:

                            to_char(ITEM_MASTER.ITEM_TYPE_CODE)  BETWEEN  '3A'  AND  '4D'

                             

                            4.) This is not lucky:

                              AND INVOICE_LINE.DB_CODE     = DB_MASTER.DB_CODE

                              AND INVOICE_LINE.DB_CODE     IN ('3180','3280','3500','3370')

                              AND DB_MASTER.DB_CODE        = '3180'

                             

                               So, in this case you get only the DB_CODE = 3180 -> You can omit the second row

                             

                            I tried to reconstruct your query but it is very hard if I know nothing about the requirements and the data in the table (duplicates, nulls.. etc)

                             

                            I tried it with EXISTS instead of IN ... Maybe it helps..

                             

                            Maybe somebody can see something in this restructured query.

                            SELECT ITE.ITEM_NUMBER

                                 , ITE.ITEM_DESC1

                                 , ITE.ITEM_DESC2

                                 , to_char(ITE.ITEM_TYPE_CODE)

                                 , FIW.FISCAL_MONTH

                                 , count(distinct INV.DB_CODE||INV.ORDER_NUMBER||INV.INV_LINE)

                                 , count(distinct INV.DB_CODE||INV.ORDER_NUMBER)

                                 , count(distinct INV.DB_CODE||INV.INV_NB||INV.ORDER_NUMBER||INV.INV_LINE)

                                 , count(distinct INV.DB_CODE||INV.INV_NB)

                              FROM

                                   ITEM_MASTER  ITE

                                 , FISCAL_WEEK  FIW

                                 , INVOICE_LINE INV

                                 , DB_MASTER    DBR

                            WHERE

                                   INV.ITEM_ID     = ITE.ITEM_ID

                               AND INV.FISCAL_WEEK = FIW.FISCAL_WEEK

                               AND INV.DB_CODE     = DBR.DB_CODE

                               AND INV.DB_CODE     IN ('3180','3280','3500','3370') --> This is not necessary

                               AND DBR.DB_CODE        ='3180'

                               AND FIW.REL_MONTH    BETWEEN  -12  AND  -1

                               AND

                                   EXISTS (SELECT 1

                                             FROM

                                                  ITEM_MASTER         ITM

                                                , DB_MASTER           DBM

                                                , INVENTORY_LOCATION  INL

                                            WHERE

                                                  DBM.DB_CODE   = INL.DB_CODE

                                              AND ITM.ITEM_ID   = INL.ITEM_ID

                                              AND to_char(ITM.ITEM_TYPE_CODE)  BETWEEN  '3A'  AND  '4D'

                                              AND INL.LOC_CODE   LIKE  '1%'

                                              AND INL.DB_CODE   IN ('3180','3280','3500','3370')  --> This is not necessary

                                              AND DBM.DB_CODE    ='3180'

                                              AND

                                                  ITM.ITEM_NUMBER = ITE.item_number

                                           )

                            GROUP BY ITE.ITEM_NUMBER, ITE.ITEM_DESC1, ITE.ITEM_DESC2, to_char(ITE.ITEM_TYPE_CODE), FIW.FISCAL_MONTH

                            ;

                            I hope I could help something if not much...

                            • 11. Re: Query running long time
                              Brian Tkatch

                              EXISTS:

                               

                              AND    EXISTS
                                  (
                                   SELECT
                                      *
                                   FROM
                                      ITEM_MASTER        IM_2,
                                      INVENTORY_LOCATION,
                                      DB_MASTER
                                   WHERE
                                      IM_2.ITEM_NUMBER            = ITEM_MASTER.ITEM_NUMBER
                                    AND    TO_CHAR(IM_2.ITEM_TYPE_CODE)    BETWEEN '3A' AND '4D'
                                      --
                                    AND    INVENTORY_LOCATION.LOC_CODE     LIKE  '1%'
                                    AND     INVENTORY_LOCATION.ITEM_ID    = IM_2.ITEM_ID
                                    AND   INVENTORY_LOCATION.DB_CODE    IN ('3180','3280','3500','3370')
                                      --
                                    AND    DB_MASTER.DB_CODE        ='3180'
                                    AND    DB_MASTER.DB_CODE        = INVENTORY_LOCATION.DB_CODE
                                  )
                              
                              

                               

                               

                               

                              Also:  TO_CHAR(IM_2.ITEM_TYPE_CODE) would negate the use of any INDEX on ITEM_TYPE_CODE. If ITEM_TYPE_CODE is indeed INDEXed (without the TO_CHAR) you might want to revisit that clause.

                              --

                              These are quick checks based on the SQL. Good things to keep in mind.

                              • 12. Re: Query running long time
                                926913

                                Thank you so much, i shall look in to it

                                • 13. Re: Query running long time
                                  AnnPricks E

                                  Changed your query as below.. You are accessing DB_MASTER and item_master two times avoid that and no need of using TO_CHAR here.. Try the below and let me know in case of any issues

                                  SELECT ITE.ITEM_NUMBER

                                         , ITE.ITEM_DESC1

                                         , ITE.ITEM_DESC2

                                         , ITE.ITEM_TYPE_CODE

                                         , FIW.FISCAL_MONTH

                                         , COUNT(DISTINCT INV.DB_CODE||INV.ORDER_NUMBER||INV.INV_LINE)

                                         , COUNT(DISTINCT INV.DB_CODE||INV.ORDER_NUMBER)

                                         , COUNT(DISTINCT INV.DB_CODE||INV.INV_NB||INV.ORDER_NUMBER||INV.INV_LINE)

                                         , COUNT(DISTINCT INV.DB_CODE||INV.INV_NB)

                                  FROM

                                       ITEM_MASTER  ITE

                                     , FISCAL_WEEK  FIW

                                     , INVOICE_LINE INV

                                     , DB_MASTER    DBR

                                  WHERE INV.ITEM_ID = ITE.ITEM_ID

                                  AND INV.FISCAL_WEEK = FIW.FISCAL_WEEK

                                  AND INV.DB_CODE = DBR.DB_CODE

                                  AND DBR.DB_CODE ='3180'

                                  AND FIW.REL_MONTH BETWEEN -12 AND -1

                                  AND EXISTS (SELECT 1

                                              FROM INVENTORY_LOCATION INL

                                              WHERE DBM.DB_CODE   = INL.DB_CODE

                                              AND ITE.ITEM_ID   = INL.ITEM_ID

                                              AND ITE.ITEM_TYPE_CODE BETWEEN '3A' AND '4D'

                                              AND INL.LOC_CODE LIKE '1%')

                                  GROUP BY ITE.ITEM_NUMBER, ITE.ITEM_DESC1, ITE.ITEM_DESC2, ITE.ITEM_TYPE_CODE, FIW.FISCAL_MONTH;

                                  • 14. Re: Query running long time
                                    926913

                                    wonderful the above query is working like a rocket. thank you so much Ann.

                                     

                                    but can you explain me this, will it give the same results as sub query in my main query.

                                    AND EXISTS (SELECT 1

                                     

                                                FROM INVENTORY_LOCATION INL

                                     

                                                WHERE DBM.DB_CODE   = INL.DB_CODE

                                     

                                                AND ITE.ITEM_ID   = INL.ITEM_ID

                                     

                                                AND ITE.ITEM_TYPE_CODE BETWEEN '3A' AND '4D'

                                     

                                                AND INL.LOC_CODE LIKE '1%')

                                    1 2 Previous Next