Re: Query Tuning Help - Sum multiple columns

On Oct 16, 3: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.
>
>
> 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

Nothing in the above stands out to me, although there might be something that can be determined by looking at the wait events for the session that is processing the SQL statement. Possibly the best way to do that is to enable a 10046 trace at level 8 prior to executing the SQL statement:
  ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

You should be able to find the trace file on one of the servers in the RAC. Open the file and take a look at the wait events, and the c= and e= entries on the PARSE, EXEC, and FETCH lines. For example: PARSE #11:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1964640504 EXEC
#11:c=15625,e=2531,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1964645881 FETCH #11:c=0,e=120,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=1964646647

Among the PARSE, EXEC, and FETCH you should also see various wait events that might give an indication of what needs to be adjusted to improve performance. For example, if you see something similar to DIRECT PATH WRITE, it might mean that you need to adjust the SORT_AREA_SIZE parameter.

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