Home » RDBMS Server » Performance Tuning » Group By
Group By [message #216595] Mon, 29 January 2007 11:05 Go to next message
tookep
Messages: 12
Registered: July 2005
Location: Ipswich
Junior Member
I am running the select statement below. The select without the group by returns in seconds. With the group by the select statement takes two minutes. Any advice you could give me on how to use group by efficiently would be appreciated.


Select Statement and Explain Plan

SELECT itl.user_id,
(SELECT au.notes FROM application_user au WHERE au.user_id = itl.user_id) AS description,
itl.code,
l.WORK_ZONE,
TRUNC(itl.dstamp),
COUNT(itl.KEY)
FROM
(SELECT client_id
FROM CLIENT_GROUP_CLIENTS
WHERE CLIENT_GROUP='CORBY') CLIENT,
LOCATION l,
inventory_transaction itl
WHERE itl.site_id='COR01'
AND (TRUNC(itl.dstamp) BETWEEN TO_DATE('29/DEC/2006','DD/MON/YYYY')
AND TO_DATE('29/JAN/2007','DD/MON/YYYY'))
AND itl.client_id = CLIENT.client_id
AND itl.from_loc_id = l.location_id
AND itl.site_id = l.site_id
GROUP BY itl.user_id, itl.code, l.work_zone, TRUNC(itl.dstamp)




Re: Group By [message #216600 is a reply to message #216595] Mon, 29 January 2007 11:37 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Run the statement through autotrace to see where the SORT is taking place - in memory or on disk.
Re: Group By [message #216601 is a reply to message #216595] Mon, 29 January 2007 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN_PLAN
Re: Group By [message #216672 is a reply to message #216601] Mon, 29 January 2007 20:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You will probably find that even though the no-group-by version is returning in seconds, it is just returning the first page of rows in seconds. It will probably still take a while to return ALL of the rows.

Compare the Explain Plan both with and without the GROUP BY. If the only difference is the GROUP BY step, then the above is true.

Also, you might get some improvement by removing the TRUNC() function.

SELECT   itl.user_id
         ,(SELECT au.notes
           FROM   application_user au
           WHERE  au.user_id = itl.user_id) AS description
         ,itl.code
         ,l.work_zone
         ,TRUNC(itl.dstamp)
         ,COUNT(itl.KEY)
FROM     (SELECT client_id
          FROM   client_group_clients
          WHERE  client_group = 'CORBY') client
         ,location l
         ,inventory_transaction itl
WHERE    itl.site_id = 'COR01'
  AND (itl.dstamp BETWEEN TO_DATE('29/DEC/2006','DD/MON/YYYY')
                                 AND TO_DATE('29/JAN/2007','DD/MON/YYYY')) + 0.99999
  AND itl.client_id = client.client_id
  AND itl.from_loc_id = l.location_id
  AND itl.site_id = l.site_id
GROUP BY itl.user_id
         ,itl.code
         ,l.work_zone
         ,TRUNC(itl.dstamp)


Ross Leishman
Re: Group By [message #216715 is a reply to message #216672] Tue, 30 January 2007 02:27 Go to previous messageGo to next message
tookep
Messages: 12
Registered: July 2005
Location: Ipswich
Junior Member
Please find below the results of the autotrace with the group by and without:

With the group by:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4769 Card=7 Bytes=54
          6)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_USER' (Cost=
          2 Card=1 Bytes=27)

   2    1     INDEX (UNIQUE SCAN) OF 'PK_APPLICATION_USER' (UNIQUE) (C
          ost=1 Card=482)

   3    0   SORT (GROUP BY) (Cost=4769 Card=7 Bytes=546)
   4    3     NESTED LOOPS (Cost=4759 Card=7 Bytes=546)
   5    4       NESTED LOOPS (Cost=4752 Card=7 Bytes=371)
   6    5         TABLE ACCESS (FULL) OF 'INVENTORY_TRANSACTION' (Cost
          =4752 Card=18 Bytes=684)

   7    5         INDEX (UNIQUE SCAN) OF 'PK_CLIENT_GROUP_CLIENTS' (UN
          IQUE)

   8    4       TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' (Cost=1 Ca
          rd=1 Bytes=25)

   9    8         INDEX (UNIQUE SCAN) OF 'U_LOCATION_ID' (UNIQUE)




Statistics
----------------------------------------------------------
         63  recursive calls
          0  db block gets
      81467  consistent gets
      55062  physical reads
          0  redo size
       1244  bytes sent via SQL*Net to client
        283  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         28  rows processed


Without the group by:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4759 Card=7 Bytes=54
          6)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_USER' (Cost=
          2 Card=1 Bytes=27)

   2    1     INDEX (UNIQUE SCAN) OF 'PK_APPLICATION_USER' (UNIQUE) (C
          ost=1 Card=482)

   3    0   NESTED LOOPS (Cost=4759 Card=7 Bytes=546)
   4    3     NESTED LOOPS (Cost=4752 Card=7 Bytes=371)
   5    4       TABLE ACCESS (FULL) OF 'INVENTORY_TRANSACTION' (Cost=4
          752 Card=18 Bytes=684)

   6    4       INDEX (UNIQUE SCAN) OF 'PK_CLIENT_GROUP_CLIENTS' (UNIQ
          UE)

   7    3     TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' (Cost=1 Card
          =1 Bytes=25)

   8    7       INDEX (UNIQUE SCAN) OF 'U_LOCATION_ID' (UNIQUE)




Statistics
----------------------------------------------------------
         63  recursive calls
          0  db block gets
      81697  consistent gets
      49902  physical reads
          0  redo size
      21003  bytes sent via SQL*Net to client
        633  bytes received via SQL*Net from client
         53  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        775  rows processed

[Updated on: Tue, 30 January 2007 19:29] by Moderator

Report message to a moderator

Re: Group By [message #216914 is a reply to message #216715] Tue, 30 January 2007 19:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Exactly! The plan is identical except for the SORT step. Without the GROUP BY appears to be faster because it shows you the first page of rows as soon as it retrieves them. The GROUP BY forces it to wait until all rows are fetched, performs a sort, then starts sending the rows to SQL*Plus.

Ross Leishman
Re: Group By [message #216961 is a reply to message #216595] Wed, 31 January 2007 01:31 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

IMO the performance problem is caused by FULL table scan on
inventory_transaction table.

Try creating an index:

CREATE INDEX inventory_transaction$SITE_DATE
ON inventory_transaction ( site_id, dstamp ) ...

Run the query with AUTOTRACE and post results.

HTH.


Previous Topic: Cost in the Explain Plan
Next Topic: Basic question in INDEXing
Goto Forum:
  


Current Time: Thu Dec 08 22:16:38 CST 2016

Total time taken to generate the page: 0.15035 seconds