Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    99

    Unanswered: help in tuning this sql code.!!!

    Hi,

    the following query takes forever to complete and eats the temp tablespace like no tommorow, any suggections on tuning this.


    My assumption is the problem lies? any suggestions!

    SELECT DISTINCT sc.SearchID, ho.UpsellLevel,avg(sc.Ranking) AS Action3
    FROM tracking.AT_Search_Child sc, Hme_Reporting ho
    WHERE sc.SearchID BETWEEN #min_ID# AND #max_ID#
    AND sc.HmeID = ho.ID
    GROUP BY sc.SearchID, ho.UpsellLevel
    ORDER BY ho.UpsellLevel

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: help in tuning this sql code.!!!

    Can you post explain plan, please ?


    Originally posted by zaki_mtk
    Hi,

    the following query takes forever to complete and eats the temp tablespace like no tommorow, any suggections on tuning this.


    My assumption is the problem lies? any suggestions!

    SELECT DISTINCT sc.SearchID, ho.UpsellLevel,avg(sc.Ranking) AS Action3
    FROM tracking.AT_Search_Child sc, Hme_Reporting ho
    WHERE sc.SearchID BETWEEN #min_ID# AND #max_ID#
    AND sc.HmeID = ho.ID
    GROUP BY sc.SearchID, ho.UpsellLevel
    ORDER BY ho.UpsellLevel

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    post the explain plan for a modified query - after removing DISTINCT and ORDER BY from query. The GROUP BY would take care of these two.
    Oracle can do wonders !

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    post the explain plan for a modified query - after removing DISTINCT and ORDER BY from query. The GROUP BY would take care of these two.
    I agree about removing the DISTINCT, but not the ORDER BY. GROUP BY does not guarantee the order of data returned. In any case, in this particular example the ORDER BY clause is different from the GROUP BY clause.

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Do you think ORDER BY is required even if the group by caluse is changed to
    GROUP BY ho.UpsellLevel, sc.SearchID
    ?

    I have always found group by returning the data sorted in sequence we group it in. What are the situations when it will not? Guess I have deviated from the original post, but this reply would defi help !!



    Originally posted by andrewst
    I agree about removing the DISTINCT, but not the ORDER BY. GROUP BY does not guarantee the order of data returned. In any case, in this particular example the ORDER BY clause is different from the GROUP BY clause.
    Oracle can do wonders !

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    Do you think ORDER BY is required even if the group by caluse is changed to
    GROUP BY ho.UpsellLevel, sc.SearchID
    ?

    I have always found group by returning the data sorted in sequence we group it in. What are the situations when it will not? Guess I have deviated from the original post, but this reply would defi help !!
    Yes, ORDER BY is always required if you really care about the order of the data, since GROUP BY does not guarantee to order it for you. If it does, that is a happy accident. Basically, if there is a way Oracle can get the groups together without the expense of sorting the data, it will do so. Here is an example:

    PHP Code:
    SQLcreate table t as select object_nameownerobject_type
      2  from
      3  
    (
      
    4  select object_nameownerobject_type
      5  from all_objects
      6  order by dbms_random
    .value
      7  
    )
      
    8where rownum <= 5000;

    Table created
    The above code creates a 5000 row table with an assortment of database objects in it. I used the "order by dbms_random.value" in it just to ensure I got a good mix of values rather than getting 5000 objects all belonging to SYS for example.

    PHP Code:
    SQLcreate index t_idx on t (ownerobject_type);

    Index created.

    SQLanalyze table t compute statistics;

    Table analyzed
    I have now created a handy index and analyzed the table. Now let's do a GROUP BY query:
    PHP Code:
    SQLset autotrace on
    SQL
    select object_typeownercount(*)
      
    2  from t
      3  where owner like 
    'F%'
      
    4  group by object_typeowner;

    OBJECT_TYPE        OWNER                            COUNT(*)
    ------------------ ------------------------------ ----------
    JAVA CLASS         FDW                                     6
    JAVA RESOURCE      FDW                                     4
    TABLE              FDW                                     5
    SEQUENCE           FFION                                   1
    FUNCTION           FRB                                     2
    PACKAGE            FRB                                   122
    SEQUENCE           FRB                                     8
    TABLE              FRB                                   108
    VIEW               FRB                                    50
    FUNCTION           FSC                                     2
    PACKAGE            FSC                                    19
    PROCEDURE          FSC                                     1
    SEQUENCE           FSC                                     1
    TABLE              FSC                                    22
    TYPE               FSC                                    24
    VIEW               FSC                                     5

    16 rows selected
    .


    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=22 Bytes=286)
       
    1    0   SORT (GROUP BY NOSORT) (Cost=3 Card=22 Bytes=286)
       
    2    1     INDEX (RANGE SCANOF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=
              
    435 Bytes=5655
    See what happened? I didn't specify an ORDER BY, and the data has come back NOT ordered according to the GROUP BY clause. You can see why: the optimizer found my handy index on (owner, object_type) and used that to collect the data, hence getting it in the order (owner, object_type). Since I didn't specify any ORDER BY clause, that is good enough, hence the (GROUP BY NOSORT) in the plan.

    Now with ORDER BY:
    PHP Code:
    SQLselect object_typeownercount(*)
      
    2  from t
      3  where owner like 
    'F%'
      
    4  group by object_typeowner
      5  order by object_type
    owner;

    OBJECT_TYPE        OWNER                            COUNT(*)
    ------------------ ------------------------------ ----------
    FUNCTION           
    FRB                                     2
    FUNCTION           FSC                                     2
    JAVA 
    CLASS         FDW                                     6
    JAVA RESOURCE      FDW                                     4
    PACKAGE            FRB                                   122
    PACKAGE            FSC                                    19
    PROCEDURE          FSC                                     1
    SEQUENCE           FFION                                   1
    SEQUENCE           FRB                                     8
    SEQUENCE           FSC                                     1
    TABLE              FDW                                     5
    TABLE              FRB                                   108
    TABLE              FSC                                    22
    TYPE               FSC                                    24
    VIEW               FRB                                    50
    VIEW               FSC                                     5

    16 rows selected
    .


    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=22 Bytes=286
              
    )

       
    1    0   SORT (GROUP BY) (Cost=57 Card=22 Bytes=286)
       
    2    1     INDEX (RANGE SCANOF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=
              
    435 Bytes=5655
    Different plan, different result.

    Another potential cause would be parallel query execution, but you get the idea. Yes, probably in your experience and mine, 98% of the time the GROUP BY gets you the data in the order you want it. But we shouldn't write code that only probably works, should we?

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Order by is required as you cannot guarantee the order returned from the group by.

    Anyway to speed up the query you may find (if you dont already) that an index on searchid may help (remember to analyze it) and also look to your sort_area_* parameters as this may improve sorting efficiency.


    Alan

  8. #8
    Join Date
    Jul 2003
    Posts
    38
    Anyway you are grouping the result by searchID...then what for do you need the distinct...I think this unnecessary Distinct will increase the query time

  9. #9
    Join Date
    Jan 2004
    Posts
    99
    I have removed the distinct clause from the query, the explain plan is still the same. Could the following avg(sc.Ranking) be causing a problem if the table has many rows?

    thanks.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by zaki_mtk
    I have removed the distinct clause from the query, the explain plan is still the same. Could the following avg(sc.Ranking) be causing a problem if the table has many rows?

    thanks.
    Not really. Can you post the output from EXPLAIN PLAN? How big are the tables?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •