Re: Help with sql query speed. Explain plan included

On Mar 18, 5:39 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 18, 11:13 am, JL19..._at_HOTMAIL.CO.UK wrote:
>
>
>
> > My apologies, here is the plan for my refreshed dataset. My database
> > will not be renewed so we will not have to focus on a moving target
> > again.
> > Thank you again
> > John
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­---------------------------------------------------------
> > Plan hash value: 2630738338
>
> > ---------------------------------------------------------------------------­-----------
> > | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> > (%CPU)| Time |
> > ---------------------------------------------------------------------------­-----------
> > | 0 | SELECT STATEMENT | | 23275 | 500K| | 18449
> > (7)| 00:03:42 |
> > |* 1 | FILTER | | | |
> > | | |
> > | 2 | HASH GROUP BY | | 23275 | 500K| 14M| 18449
> > (7)| 00:03:42 |
> > |* 3 | HASH JOIN | | 465K| 9M| | 17212
> > (8)| 00:03:27 |
> > |* 4 | INDEX RANGE SCAN | IDXE | 22 | 242 | | 3
> > (0)| 00:00:01 |
> > |* 5 | TABLE ACCESS FULL| TEST1 | 26M| 278M| | 16868
> > (6)| 00:03:23 |
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­---------------------------------------------------------
> > ---------------------------------------------------------------------------­-----------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > 1 - filter(COUNT(*)>10)
> > 3 - access("T0"."QUESTIONNUMBER"="TX"."QUESTIONNUMBER" AND
> > "T0"."ANSWER"="TX"."ANSWER")
> > filter("T0"."RESIDENTID"<>"TX"."RESIDENTID")
> > 4 - access("T0"."RESIDENTID"=1486674)
> > 5 - filter("TX"."RESIDENTID"<>1486674)
>
> > 22 rows selected.
>
> It would appear you are now constrained by hardware (CPU, memory);
> improve that situation and you *may* see a further reduction in the
> time required. A little over 3 seconds is not bad for a hash
> operation of this size (the estimated temp space required for this is
> reported at 14M, down from 21M in your original plan). You've also
> reduced the number of rows in your hash operation by roughly 10000
> (you now have 23275 rows 'spilled' to temp, when originally you had
> 33,322 rows written to the temp tablespace). Had you more/faster CPUs
> or more memory (to increase your hash_area_size further and reduce, or
> possibly eliminate, the use of the temp tablespace) you *might* see
> further improvement. You've reduced the query time by 50-60 seconds
> with your current changes. I know the users want 'blazing speed', and
> trying for a reduction of an additional 5 to 10 seconds is worth some
> effort, however, in my opinion, it is not worth being obsessivly
> driven to disappointment if you cannot achieve it.
>
> David Fitzjarrell

Thanks David.

If I recall correctly, Cary Millsap wrote something about Obsessive Tuning Disorder and I think this is what you are talking about too. 70 seconds was a show stopper - 10-20 seconds is far better. At least no one suggested we restructure our data. We were prepared to do it but wanted to canvas opinion on tuning rather than software rewrites to start with.

Thank you again.
John Received on Sun Mar 18 2007 - 12:58:07 CDT