Re: Help with sql query speed. Explain plan included

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 Received on Sun Mar 18 2007 - 12:39:10 CDT