Re: Query Tuning Help - Sum multiple columns

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 16 Oct 2007 05:01:21 -0700
Message-ID: <1192536081.429591.280720@v29g2000prd.googlegroups.com>

On Oct 16, 2:28 am, DP <dxpe..._at_gmail.com> wrote:
> I have a fact table with multiple columns that need to be summed.
>
> The sql is straight forward:
>
> select col1, col2, col3, sum(col4+col5+col6) value1, sum(col6),
> sum(col7), ....
> from table1
> group by col1, col2, col3, value1
> order by col1, col2
>
> The table has 171,000 rows, but the query takes 20 minutes to
> complete.
>
> Is there a more efficient way to write a query like this?
>
> Thanks,
> Dennis Pessetto
> DBA - The Regence Group

Something does not look right with your SQL statement - you alias SUM(COL4+COL5+COL6) as VALUE1 and then include VALUE1 in the GROUP BY clause.

A quick check on my system for the possible cause of the performance problem (Oracle 10.2.0.2):
CREATE TABLE T1 (

  COL1 VARCHAR2(30),
  COL2 VARCHAR2(30),
  COL3 VARCHAR2(30),
  COL4 NUMBER(12,2),
  COL5 NUMBER(12,2),

COL6 NUMBER(12,2),
COL7 NUMBER(12,2)); INSERT INTO T1
SELECT

  'COL1'||TO_CHAR(TRUNC((ROWNUM-1)/40)+1) COL1,
  'COL2'||TO_CHAR(TRUNC((ROWNUM+2)/15)+1) COL2,
  'COL3'||TO_CHAR(TRUNC((ROWNUM+5)/10)+1) COL3,
  ABS(ROUND(SIN(ROWNUM/180*3.141592)*200,2)) COL4,
  ABS(ROUND(COS(ROWNUM/180*3.141592)*200,2)) COL5,

  ABS(ROUND(SIN((ROWNUM+45)/180*3.141592)*200,2)) COL6,   ABS(ROUND(COS((ROWNUM+45)/180*3.141592)*200,2)) COL7 FROM
  INVENTORY_TRANS
WHERE
  ROWNUM<=171000;

171000 rows created.

COMMIT; Now, gather statistics on the table, and any indexes that might exist (none in my example):
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE); PL/SQL procedure successfully completed.

Let's give Oracle a hint to provide additional statistics when we try to retrieve the execution plan:
SELECT /*+ GATHER_PLAN_STATISTICS */

  COL1,
  COL2,
  COL3,
  SUM(COL4+COL5+COL6) VALUE1,

  SUM(COL6),
  SUM(COL7)
FROM
  T1
GROUP BY
  COL1,
  COL2,
  COL3
ORDER BY
  COL1,
  COL2; ...
32775 rows selected.

Now, retrieve the execution plan:
SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT

| 1 | SORT GROUP BY | | 1 | 172K| 32775 | 00:00:00.47 | 1558 | 228 | 3313K| 1535K| 2944K (0)|

|   2 |   TABLE ACCESS FULL| T1   |      1 |    172K|    171K|
00:00:00.01 |    1558 |    228 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

The SQL statement did a full tablescan, and then a sort and group - which required a bit of memory and 0.47 seconds.

Let's try again specifying the VALUE1 alias in the GROUP BY clause: SELECT /*+ GATHER_PLAN_STATISTICS */

  COL1,
  COL2,
  COL3,
  SUM(COL4+COL5+COL6) VALUE1,

  SUM(COL6),
  SUM(COL7)
FROM
  T1
GROUP BY

  COL1,
  COL2,
  COL3,

  VALUE1
ORDER BY
  COL1,
  COL2; ERROR at line 14:
ORA-00904: "VALUE1": invalid identifier

You might want to take a look at the SORT_AREA_SIZE, HASH_AREA_SIZE, and/or PGA_AGGREGATE_TARGET on the system after reviewing the execution plan for your query.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Oct 16 2007 - 07:01:21 CDT