| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql query speed. Explain plan included
On Mar 21, 7:34 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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 |
> E-Rows | A-Rows | A-Time | Buffers | Reads |
> --------------------------------------------------------------------------------------------------------------------
> |* 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-
Lines from the 10046 trace:
EXEC #2:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=20019968953
FETCH
#2:c=5631636,e=5562750,p=0,cr=6818,cu=0,mis=0,r=0,dep=0,og=1,tim=20025531842
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=6818 pr=0 pw=0
time=5562816 us)'
STAT #2 id=2 cnt=1497257 pid=1 pos=1 obj=0 op='HASH GROUP BY (cr=6818
pr=0 pw=0 time=10349366 us)'
STAT #2 id=3 cnt=2064509 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=6818
pr=0 pw=0 time=18580687 us)'
STAT #2 id=4 cnt=73 pid=3 pos=1 obj=14766 op='TABLE ACCESS BY INDEX ROWID T1 (cr=77 pr=0 pw=0 time=1943 us)' STAT #2 id=5 cnt=73 pid=4 pos=1 obj=14776 op='INDEX RANGE SCAN T1_INDEX1 (cr=4 pr=0 pw=0 time=480 us)'
The above was translated by a program that I wrote specifically to
help me with performance tuning:
Execute at 0.555570 (Parse to Exec 0.000489),CPU Time
0.000000,Elapsed Time 0.000141,Rows Affected 0,Blks from Buff 0,Blks
from Disk 0,Goal=ALL_ROWS
Fetch at 6.118459 (Parse to Fetch 5.563378),CPU Time
5.631636,Elapsed Time 5.562750,Rows Retrievd 0,Blks from Buff
6818,Blks from Disk 0
(Rows 0) FILTER (cr=6818 pr=0 pw=0 time=5562816 us)
(Rows 1497257) HASH GROUP BY (cr=6818 pr=0 pw=0 time=10349366 us)
(Rows 2064509) NESTED LOOPS (cr=6818 pr=0 pw=0 time=18580687 us)
(Rows 73) TABLE ACCESS BY INDEX ROWID T1 (cr=77 pr=0 pw=0
time=1943 us)
(Rows 73) INDEX RANGE SCAN T1_INDEX1 (cr=4 pr=0 pw=0
time=480 us)
(Rows 2064509) INDEX RANGE SCAN T1_INDEX3 (cr=6741 pr=0 pw=0
time=8260306 us)
I finally had a chance to read through what you wrote earlier. I think that you have an excellent idea to use clustering on the table data to reduce the clusting factor - I have been looking for a good excuse to experiment with clustering. You are probably right that the data distribution is not an even distribution.
Your database must be very busy - your execution took three times longer than the execution on my database running on Vista. If I have a chance I will try to duplicate your suggestion on my system so that the numbers are comparable.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Mar 21 2007 - 20:14:14 CDT
 |
 |