[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

[ndu35]

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

[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.

[andrewst]

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.

[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 !!

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.

[andrewst]

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:

SQL> create table t as select object_name, owner, object_type
  2  from
  3  (
  4  select object_name, owner, object_type
  5  from all_objects
  6  order by dbms_random.value
  7  )
  8* where 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:

SQL> create index t_idx on t (owner, object_type);

Index created.

SQL> analyze 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:

SQL> set autotrace on
SQL> select object_type, owner, count(*)
  2  from t
  3  where owner like 'F%'
  4  group by object_type, owner;

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 SCAN) OF '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:

SQL> select object_type, owner, count(*)
  2  from t
  3  where owner like 'F%'
  4  group by object_type, owner
  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 SCAN) OF '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?

[AlanP]

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

[avr]

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

[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.

[andrewst]

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?