13 Replies Latest reply: Sep 18, 2013 10:36 AM by user10955371 RSS

    Query Performance

    user10955371

      Dear Gurus,

       

      Could not find specific location to post regarding Performance Tuning, hence it's in General Questions.

       

      We have a query 

       

      Select c1,c2,c3,c121222,c134223,c4555 from T211 where c4='INDIA' and c3<3;

       

      This query takes 3 minutes to fetch the output and total no.of rows for the same are 230 out of 150000.

       

      However the same query when given c4='UK' gives the results in <= 1sec. Number of rows accounts to more than 2000.

       

      Why is the time difference when only the where clause is changed.

       

      Table has been analyzed last night and even indexes are rebuilt. Stats are also gathered last night.

       

      Our database is Oracle 10.2.0.3 running on solaris

       

       

      Regards,

      Sunil

        • 1. Re: Query Performance
          Mohamed Houri

          Could you post the execution plans of both queries

           

          Could you also post the content of the following query

           

           

          select c4, count(1)

          from T211

          group by c4;

           

           

          and in the meantime read this jonathan lewis article

           

          http://allthingsoracle.com/histograms-part-1-why/

           

          Best regards

           

          Mohamed Houri

          www.hourim.wordpress.com

          • 2. Re: Query Performance
            user10955371

            Thank you Mohamed for the link,

            I'll provide you the details shortly.

             

            Regards,

            Sunil

            • 3. Re: Query Performance
              user10955371

              Here is the output which is trncated.

               

              C4               count(1)

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

              INDIA     53127

              UK          157562

              Cunnigham     3500

              Coca Cola       2564

              • 4. Re: Query Performance
                bmpatil

                Hi,

                 

                Please provide the explain plan for the same like below.

                SET LINES 10000;
                  SET PAGES 10000;
                  SET TRIMSPOOL ON;
                  SET ECHO ON;
                  
                   
                Set time on;
                Set timing on;
                alter session set nls_date_format ='dd-mm-yyyy hh24:mi:ss';

                 

                set autotrace on explain statistics;

                 

                Select c1,c2,c3,c121222,c134223,c4555 from T211 where c4='INDIA' and c3<3;

                 

                same for second query

                run the same using SQL*PLUS.

                 

                Regards,

                Bhushan

                • 5. Re: Query Performance
                  user10955371

                  Hi Bhushan,

                   

                  Please find the sql pland.

                   

                  Column names are replaced:

                  Plan hash value: 437261624

                  --------------------------------------------------------------------------
                  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                  --------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT  |      |   790 |   108K| 33910   (1)| 00:06:47 |
                  |*  1 |  TABLE ACCESS FULL| T217 |   790 |   108K| 33910   (1)| 00:06:47 |
                  --------------------------------------------------------------------------

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

                     1 - filter("T217"."C536870921"<2 AND "T217"."C60000001"='Ealing')

                  13 rows selected.


                  Regards,

                  Sunil

                  • 6. Re: Query Performance
                    Martin Preiss

                    the plan shows T217 - not T211 - and no predicate for C4 - so it seems it's not the fitting one.

                    • 7. Re: Query Performance
                      user10955371

                      Hi Martin,

                       

                      I'm sorry, there are two database and the query which I provided is for another.

                       

                      Below is the actual query:

                      SELECT

                      T217.C1,T217.C1,C60000001,C536870921,C7,C8,C536870913,C536900006,C536870919,C4,C536871210,C536871207,C536870916

                      FROM T217 WHERE ((T217.C60000001 = 'Ealing') AND (T217.C536870921 < 2)) ORDER BY 1 ASC


                      And exection which I provided is for the same.


                      Regards,

                      Sunil

                      • 8. Re: Query Performance
                        Mohamed Houri

                        That is not the exact query that corresponds to the execution plan you've posted above. In the already provided execution plan there is no ORDER BY operation and there is no indication that the CBO has taken an advantage of an ordered access to avoid the sort operation.

                         

                        So, if you want a help you could do the following

                         

                         

                        SELECT /*+ gather_plan_statistics */

                        T217.C1,T217.C1,C60000001,C536870921,C7,C8,C536870913,C536900006,C536870919,C4,C536871210,C536871207,C536870916

                        FROM T217 WHERE ((T217.C60000001 = 'Ealing') AND (T217.C536870921 < 2)) ORDER BY 1 ASC;


                        select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'));

                         

                        and post the execution plan

                         

                        Best regards

                        Mohamed Houri

                        www.hourim.wordpress.com

                        • 9. Re: Query Performance
                          user10955371

                          Hi Mohamed,

                           

                          Please find the output:

                          PLAN_TABLE_OUTPUT

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

                          SQL_ID  0zzqwawu7j5rm, child number 0

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

                          SELECT /*+ gather_plan_statistics */ T217.C1,T217.C1,C60000001,C536870921,C7,C8,C536870913,C536900006,C536870919,

                          C4,C536871210,C536871207,C536870916 FROM ARADMIN.T217 WHERE ((T217.C60000001 = :"SYS_B_0") AND (T217.C536870921

                          < :"SYS_B_1")) ORDER BY :"SYS_B_2" ASC

                          Plan hash value: 405555953

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

                          | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

                          PLAN_TABLE_OUTPUT

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

                          |   0 | SELECT STATEMENT   |      |      1 |        |    258 |00:00:02.55 |     153K|    331 |       |       |          |

                          |   1 |  SORT ORDER BY     |      |      1 |    755 |    258 |00:00:02.55 |     153K|    331 | 61440 | 61440 |55296  (0)|

                          |*  2 |   TABLE ACCESS FULL| T217 |      1 |    755 |    258 |00:00:01.16 |     153K|    331 |       |       |          |

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

                          Predicate Information (identified by operation id):

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

                             2 - filter(("T217"."C536870921"<:SYS_B_1 AND "T217"."C60000001"=:SYS_B_0))

                          • 10. Re: Query Performance
                            Mohamed Houri

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

                            | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |

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

                            |   0 | SELECT STATEMENT   |      |      1 |        |    258 |00:00:02.55 |

                            |   1 |  SORT ORDER BY     |      |      1 |    755 |    258 |00:00:02.55 |

                            |*  2 |   TABLE ACCESS FULL| T217 |      1 |    755 |    258 |00:00:01.16 |

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

                            Predicate Information (identified by operation id):

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

                               2 - filter(("T217"."C536870921"<:SYS_B_1 AND "T217"."C60000001"=:SYS_B_0))

                              

                               So what is the problem with this query? it is taking about 3 seconds to complete (half for the table access and the remaining 1,5 seconds for the ORDER BY)

                              

                               Is it, in your point of view, performing poorly? is 3 seconds a non acceptable response time?

                               Have this query performed well and now started taking a non acceptable response time of 3 seconds?

                              

                               How many records are there in T217 table?

                               What indexes have you on this T217 table ?

                             

                              Best regards

                              Mohamed Houri

                            www.hourim.wordpress.com

                            • 11. Re: Query Performance
                              user10955371

                              Hi Mohamed,

                               

                              Sorry, I was down with fever yesterday and so could not reply. I can provide you the details you are looking for once i'm into office.

                               

                              But 3.sec is a random time for the execution of query, sometimes the same query when searched with Ealing is taking more than 3 mins.

                              But this is not the case if we are searching for someother in pleace of Ealing..

                               

                              Regards,

                              Sunil

                              • 12. Re: Query Performance
                                jgarry

                                You need to post the plan for when it is taking a long time, too.  This may show that sometimes, a full table scan is quicker.  Does the same query without the gather_plan_statistics perform poorly?  Perhaps the statistics that are gathered normally for the table sometimes don't reflect the proper arithmetic for the less than query.

                                 

                                Edit:  Here is HOW TO: Post a SQL statement tuning request - template posting

                                Except, ignore the formatting instructions, use a fixed font instead.

                                • 13. Re: Query Performance
                                  user10955371

                                  Thank you Garry,


                                  Sorry for late update as I was on vacation.

                                   

                                  The issue is fixed after we created an index on a colummn.

                                   

                                  Regards,

                                  Kumar