Re: Query Tuning Help - Sum multiple columns

From: DP <dxpesse_at_gmail.com>
Date: Tue, 16 Oct 2007 19:12:44 -0000
Message-ID: <1192561964.073390.128840@q3g2000prf.googlegroups.com>

On Oct 16, 6:01 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Thanks Charles for the help and suggestions.

To add a twist this query is running on a 4 node RAC cluster.

I did have the sql wrong.. It was one of those late nights.

Here is the correct SQL and an explain plain.. Everything looks OK, but it's just slow..

  SQL Statement from editor:

  SELECT a.fin_cutoff_dt, a.cms_id, a.rec_count, COUNT (*) recs,

           SUM (a.cost_paid + a.fee + a.tax_paid) total_cost,
           SUM (a.ab_amt),
           SUM (a.aba_amt),
           SUM (a.abb_amt),
           SUM (a.abc_amt),
           SUM (a.abd_amt),
           SUM (a.abe_amt),
           SUM (a.cost_paid),
           SUM (a.fee),
           SUM (a.tax_paid)
      FROM r102704.table_name a
      GROUP BY a.fin_cutoff_dt, a.cms_id, a.rec_count

  ORDER BY a.fin_cutoff_dt ASC

---

  Statement Id=4203110 Type=
  Cost=2.64022111505165E-308 TimeStamp=16-10-07::13::00:16

       (1)  SELECT STATEMENT  ALL_ROWS
     Est. Rows: 8  Cost: 3,080
       (3)  SORT GROUP BY
     Est. Rows: 8  Cost: 3,080
           (2)  TABLE TABLE ACCESS FULL R102704.TABLE_NAME[Analyzed]
           (2)   Blocks: 9,637 Est. Rows: 171,020 of 171,020  Cost:
3,064
                Tablespace: USERS

Dennis Pessetto
Oracle DBA - The Regence Group Received on Tue Oct 16 2007 - 14:12:44 CDT