11 Replies Latest reply: Jun 14, 2014 11:08 PM by 1003614 RSS

    performance issue

    1003614

      Hi All,

      oracle 11g R2

      Linux

       

      below sql is taking 45mins of time to run. can anyone please suggest how to improve below sql performance.

       

      i have tried ORDERED CBO hint but no luck....

       

      SQL> explain plan for

        2  SELECT ' ' GFA, sp.NAME, COUNT(REQUEST_ITEM.REQUEST_ITEM_REVISION_ID), SUM(DECODE((SELECT  DISTINCT

      'x' FROM REQUEST_ITEM_STATUS_HISTORY RISH Where Rish.Status_Code In ('RIU', 'RIWU')

        3    4  And Rish.REQUEST_ITEM_REVISION_ID = Request_Item.REQUEST_ITEM_REVISION_ID and RISH.

        5  status_code=Request_Item_Status.Status_Code And Request_Item_Status.Status_Code Not

        6  In ('RIP', 'RINN')) , 'x', 0, 1)) From Work_Type, Request_Item, Request_Item_Status,

        7  REQUEST, SERVICE_PROVIDER sp Where Request.Received_Timestamp Between TO_DATE('02/04/14 00:00:00',

      'dd/mm/yy hh24:mi:ss') and TO_DATE('01/05/14 00:00:00', 'dd/mm/yy hh24:mi:ss')And Request.Sp_Id = Sp.Sp_Id Group By Sp.Name   8  ;

       

       

      Explained.

       

       

      SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

       

       

      PLAN_TABLE_OUTPUT

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

      Plan hash value: 4074424689

       

       

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

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

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

      |   0 | SELECT STATEMENT                  |                          |   621 | 36018 |       |   190G (99)|999:59:59 |

      |   1 |  SORT UNIQUE NOSORT               |                          |     1 |    12 |       |     4  (25)| 00:00:01 |

      |*  2 |   FILTER                          |                          |       |       |       |            |          |

      |*  3 |    INDEX RANGE SCAN               | REQ_ITEM_HISTORY_IDX     |     1 |    12 |       |     3   (0)| 00:00:01 |

      |   4 |  HASH GROUP BY                    |                          |   621 | 36018 |       |   190G (99)|999:59:59 |

      |*  5 |   FILTER                          |                          |       |       |       |            |          |

      |   6 |    MERGE JOIN                     |                          |  1293T|    66P|       |    10G (76)|999:59:59 |

      |   7 |     SORT JOIN                     |                          |  1365G|    53T|   129T|  2807M  (6)|999:59:59 |

      |   8 |      MERGE JOIN CARTESIAN         |                          |  1365G|    53T|       |   578M  (2)|999:59:59 |

      |   9 |       MERGE JOIN CARTESIAN        |                          |   184K|  6484K|       |  2823   (1)| 00:00:40 |

      |  10 |        MERGE JOIN CARTESIAN       |                          |  6831 |   240K|       |   254   (0)| 00:00:04 |

      |  11 |         INDEX FULL SCAN           | REQUEST_ITEM_STATUS_PK   |    11 |    55 |       |     1   (0)| 00:00:01 |

      |  12 |         BUFFER SORT               |                          |   621 | 19251 |       |   253   (0)| 00:00:04 |

      |  13 |          TABLE ACCESS FULL        | SERVICE_PROVIDER         |   621 | 19251 |       |    23   (0)| 00:00:01 |

      |  14 |        BUFFER SORT                |                          |    27 |       |       |  2800   (1)| 00:00:40 |

      |  15 |         INDEX FAST FULL SCAN      | WORK_TYPE_PK             |    27 |       |       |     0   (0)| 00:00:01 |

      |  16 |       BUFFER SORT                 |                          |  7406K|    49M|       |   578M  (2)|999:59:59 |

      |  17 |        INDEX FAST FULL SCAN       | REQUEST_ITEM_PK          |  7406K|    49M|       |  3137   (2)| 00:00:44 |

      |* 18 |     SORT JOIN                     |                          |   588K|  8614K|    27M| 12144   (2)| 00:02:51 |

      |* 19 |      VIEW                         | index$_join$_005         |   588K|  8614K|       | 10234   (2)| 00:02:24 |

      |* 20 |       HASH JOIN                   |                          |       |       |       |            |          |

      |* 21 |        INDEX RANGE SCAN           | REQUEST_REVISDTSTAMP_IDX |   588K|  8614K|       |   946   (2)| 00:00:14 |

      |  22 |        BITMAP CONVERSION TO ROWIDS|                          |   588K|  8614K|       |   501   (0)| 00:00:08 |

      |  23 |         BITMAP INDEX FULL SCAN    | SP_INDEX                 |       |       |       |            |          |

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

       

       

      thanks,

      Mike.

        • 1. Re: performance issue
          John Spencer

          As a start, I would look at the fact that you have 5 tables in the from clause but only one join predicate.  That seems odd (to say the least) to me.

           

          What is the realtionship beteeen those tables?  Based solely on the names, I would guess that request has a (at least implied) foreign ket relationship with all of the others, that is, there are columns in request that map to the PK columns of each of the other tables.

           

          John


          • 2. Re: performance issue
            sb92075

            SELECT ' ' GFA,

                   sp.name,

                   Count(request_item.request_item_revision_id),

                   SUM(Decode((SELECT DISTINCT 'x'

                               FROM   request_item_status_history RISH

                               WHERE  Rish.status_code IN ( 'RIU', 'RIWU' )

                                      AND Rish.request_item_revision_id =

                                          request_item.request_item_revision_id

                                      AND RISH. status_code = request_item_status.status_code

                                      AND request_item_status.status_code NOT IN ( 'RIP',

                                          'RINN' ))

                       ,

                           'x', 0,

                           1))

            FROM   work_type,

                   request_item,

                   request_item_status,

                   request,

                   service_provider sp

            WHERE  request.received_timestamp BETWEEN

                          To_date('02/04/14 00:00:00', 'dd/mm/yy hh24:mi:ss') AND To_date('01/05/14 00:00:00', 'dd/mm/yy hh24:mi:ss')

                   AND request.sp_id = Sp.sp_id

            GROUP  BY Sp.name;

            • 3. Re: performance issue
              EdStevens

              Look at all those Cartesian joins .. the result of not having join predicates on all of the tables.

               

              If you don't understand Cartesian products, let's take a simple example.  Suppose your EMP table has 100 rows, and your DEPT table has 5 rows.  If you select from both tables without a join predicate, the result will have a EVERY row in EMP matched with EVERY row in DEPT.  That's "emp rowcount" times "dept rowcount" --- 500 rows.  Now, figure what that means for the number of rows in your tables, and the fact that you have even more tables not being joined.

              • 4. Re: performance issue
                1003614

                thanks all for quick reply...can any one advise me ..any possibility on query rewrite?

                • 5. Re: performance issue
                  sb92075

                  1003614 wrote:

                   

                  thanks all for quick reply...can any one advise me ..any possibility on query rewrite?

                  We can't write the SQL for you since we don't know what question the SQL needs to provide an answer

                  when 5 tables exist in the FROM clause

                  at least 4  different filters need to exist in the WHERE clause to eliminate any Cartesian Product

                  • 6. Re: performance issue
                    1003614

                    thanks SB for your inputs.

                    • 7. Re: performance issue
                      1003614

                      Hi SB,

                       

                      one doubt..

                       

                      i have two test machines...

                      Test1 and test2

                       

                      all most all are similar..(tables data is little diff)

                       

                      in test1 query is not using much temp tablespace(below 1GB) but on test2 it is taking around 30GB and then it is failing with below error...any suggestion plz ?

                       

                      in test1 query returning with in 5 mins.

                       

                      ERROR at line 5:

                      ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

                      • 8. Re: performance issue
                        sybrand_b

                        Go to http://www.google.com

                         

                        Type ora-1652 in the search bar.

                         

                        Please don't ask this type of question here.

                        All of that is also in the Error Manual in the online documentation at http://docs.oracle.com

                         

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

                        Sybrand Bakker
                        Senior Oracle DBA

                        • 9. Re: performance issue
                          1003614

                          thanks!

                          • 10. Re: performance issue
                            EdStevens

                            1003614 wrote:

                             

                            Hi SB,

                             

                            one doubt..

                             

                            i have two test machines...

                            Test1 and test2

                             

                            all most all are similar..(tables data is little diff)

                             

                            in test1 query is not using much temp tablespace(below 1GB) but on test2 it is taking around 30GB and then it is failing with below error...any suggestion plz ?

                             

                            in test1 query returning with in 5 mins.

                             

                            ERROR at line 5:

                            ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

                            "Similar" != "Exact"

                             

                            Off hand, I'd say the data volumes are different enough to make the difference.  Especially since the query (You ARE talking about the exact same query, right?) is producing a Cartesian product.  Remember, Cartesian products have a multiplying effect.  A small increase in input data will make a huge increase in the size of the Cartesian set.  Again, a simple example.  Emp table has 100 rows.  DEPT table has 2 rows.  A Cartesian join will produce a set with 200 rows.  Now, simply add 2 more rows to DEPT and your cartesian set goes from 200 rows (100 x 2) to 400 rows (100 x 4).  Now, go back to your much more complex set of 5 tables and thousands of rows and think about it. 

                            • 11. Re: performance issue
                              1003614

                              yes stevens..your 100% correct...i have checked all the way but no luck....i will suggest the front end team to look-in for code change.