Re: Query Tuning Help - Sum multiple columns

On Oct 16, 2:12 pm, DP <dxpe..._at_gmail.com> wrote:
> 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

Set autotrace on and run this again; you may see something in the statistics that may be key to unlocking this 'mystery'.

David Fitzjarrell Received on Tue Oct 16 2007 - 14:29:51 CDT