Re: Help with sql query speed. Explain plan included

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 22 Mar 2007 00:34:14 +0100
Message-ID: <4601C0F6.7060902@gmail.com>

Charles Hooper schrieb:
> ------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers |
> ------------------------------------------------------------------------------------------------------
> |* 1 | FILTER | | 1 |
> | 0 |00:00:05.56 | 6818 |
> | 2 | HASH GROUP BY | | 1 |
> 104K| 1497K|00:00:10.35 | 6818 |
> | 3 | NESTED LOOPS | | 1 |
> 2089K| 2064K|00:00:18.58 | 6818 |
> | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 75
> | 73 |00:00:00.01 | 77 |
> |* 5 | INDEX RANGE SCAN | T1_INDEX1 | 1 | 75
> | 73 |00:00:00.01 | 4 |
> |* 6 | INDEX RANGE SCAN | T1_INDEX3 | 73 | 27678
> | 2064K|00:00:08.26 | 6741 |
> ------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> 1 - filter(COUNT(*)>10)
> 5 - access("T0"."RESIDENTID"=1486674)
> 6 - access("T0"."QUESTIONNUMBER"="TX"."QUESTIONNUMBER" AND
> "T0"."ANSWER"="TX"."ANSWER")
> filter(("T0"."RESIDENTID"<>"TX"."RESIDENTID" AND
> "TX"."RESIDENTID"<>1486674))
>
> So, the NESTED LOOPS operation took 18.58 seconds, the HASH GROUP BY
> 10.35 seconds, and the entire query returned in 5.56 seconds?
>

At least here i can't reproduce

| Id  | Operation                      | Name           | Starts |

|*  1 |  FILTER                        |                |      1 | 
   |      0 |00:00:19.17 |    1566 |   2900 |
|   2 |   HASH GROUP BY                |                |      1 | 
42549 |    731K|00:00:17.61 |    1566 |   2900 |
|   3 |    NESTED LOOPS                |                |      1 | 
850K|    859K|00:00:12.05 |    1566 |   2900 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1             |      1 | 
51 |     52 |00:00:00.01 |      56 |     56 |
|*  5 |      INDEX RANGE SCAN          | T1_IDX         |      1 | 
51 |     52 |00:00:00.01 |       4 |      4 |
|*  6 |     TABLE ACCESS CLUSTER       | T1             |     52 | 
16550 |    859K|00:00:06.95 |    1510 |   2844 |
|*  7 |      INDEX UNIQUE SCAN         | T1_CLUSTER_IDX |     52 | 
1 |     52 |00:00:00.03 |      54 |     21 |
--------------------------------------------------------------------------------------------------------------------

The only common thing between that 2 is - both runs on unsupported plattforms - yours on 10.2.0.3/Vista, my on 10.2.0.3/Fedora Core 6

;-)

> 10053 TRACE:
> ---------------------------------------------------
> +-----------------------------------+
> | Id | Operation | Name | Rows | Bytes |
> Cost | Time |
> ---------------------------------------------------
> +-----------------------------------+
> | 0 | SELECT STATEMENT | | | |
> 11K | |
> | 1 | FILTER | | |
> | | |
> | 2 | HASH GROUP BY | | 102K | 2244K |
> 11K | 00:01:02 |
> | 3 | NESTED LOOPS | | 2040K | 44M |
> 6944 | 00:00:38 |
> | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 75 | 825
> | 80 | 00:00:01 |
> | 5 | INDEX RANGE SCAN | T1_INDEX1| 75 |
> | 4 | 00:00:01 |
> | 6 | INDEX RANGE SCAN | T1_INDEX3| 27K | 297K
> | 92 | 00:00:01 |
> ---------------------------------------------------
> +-----------------------------------+
> Predicate Information:
> ----------------------
> 1 - filter(COUNT(*)>10)
> 5 - access("T0"."RESIDENTID"=1486674)
> 6 - access("T0"."QUESTIONNUMBER"="TX"."QUESTIONNUMBER" AND
> "T0"."ANSWER"="TX"."ANSWER")
> 6 - filter(("T0"."RESIDENTID"<>"TX"."RESIDENTID" AND
> "TX"."RESIDENTID"<>1486674))
>
> So, the NESTED LOOPS operation took 38 seconds, and the HASH GROUP BY
> took 62 seconds?
>

I think, these timese is running total (by tree traversal of course)

> 10046 TRACE STAT LINES:
> CPU Time 5.631636,Elapsed Time 5.562750,Rows Retrievd 0,Blks from Buff
> 6818,Blks from Disk 0

Where did you got these lines in 10046? - i don't see them

Best regards

Maxim Received on Wed Mar 21 2007 - 18:34:14 CDT