Re: Query help please
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 18 Mar 2009 22:38:19 +0100
Message-ID: <49c169da$0$199$e4fe514c_at_news.xs4all.nl>
Shakespeare schreef:
> bob123 schreef:
>
> From what I see now, most time is spent in this step: (note that I
> slightly changed your output by adding some dots to compare numbers a
> bit more easily)
> TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEDATASTORAGE
> which uses an Index unique scan over XPKPF_MESSAGEDATASTORAGE
>
> with filter
> access("MD"."ID"="MDS"."ID" AND "MDS"."MESSAGEPARTTYPE"=1)
> filter("MI"."ID"="MDS"."ID")
>
> which is a bit strange filter, but it may be caused by the fact that you
> used one clause too much in your where clause:
> Note that in your query you wrote:
>
> 8 AND mi.ID = ms.ID
> 9 AND mi.ID = md.ID
> 10 AND mi.ID = mds.ID
> 11 AND md.ID = mds.ID
>
> from which you can take out the last part (11) because it is already
> solved by (9)and (10):
> if mi.id = md.id and mi.id = mds.id then automatically md.id = mds.id
>
> Maybe you should check the order of the columns in this index, and see
> if ID and MESSAGEPARTTYPE are the first 2 columns. If not, you could add
> an (extra) index on these columns and see if it helps.
>
> My advise:
> 1) check your statistics (plan does not seem to correspond to the tkprof
> output)
> 2) rewrite your where clause so (11) is taken out, see if that changes
> the plan and tkprof
> 3) check indexes to avoid the index range scan.
>
> Shakespeare
Date: Wed, 18 Mar 2009 22:38:19 +0100
Message-ID: <49c169da$0$199$e4fe514c_at_news.xs4all.nl>
Shakespeare schreef:
> bob123 schreef:
>> Thanks shakespeare end randolf >> tkprof gives : >> >> Rows Row Source Operation >> ------- --------------------------------------------------- >> 42210 SORT ORDER BY (cr=1385255 r=73108 w=2548 time=787.446.353 us) >> 71323 COUNT (cr=1385255 r=71572 w=0 time=2.485.287.401 us) >> 71323 FILTER (cr=1385255 r=71572 w=0 time=2.485.075.427 us) >> 71323 NESTED LOOPS (cr=1242562 r=71563 w=0 time=2.481.867.874 us) >> 71323 NESTED LOOPS (cr=1028551 r=7281 w=0 time=60655450 us) >> 71323 NESTED LOOPS (cr=885863 r=7266 w=0 time=58894577 us) >> 291183 TABLE ACCESS FULL PFMQ_MESSAGESTATUS (cr=12300 r=7266 >> w=0 time=53.594.430 us) >> 71323 TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEINFO (cr=873563 >> r=0 w=0 time=4.048.887 us) >> 291183 INDEX UNIQUE SCAN XPKPF_MESSAGEINFO (cr=582371 r=0 w=0 >> time=2.211.983 us)(object id 6256) >> 71323 INDEX UNIQUE SCAN XPKPF_MESSAGEDATA (cr=142688 r=15 w=0 >> time=1.304.993 us)(object id 6260) >> 71323 TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEDATASTORAGE >> (cr=214011 r=64282 w=0 time=2.420.654.752 us) >> 71323 INDEX UNIQUE SCAN XPKPF_MESSAGEDATASTORAGE (cr=142688 >> r=15 w=0 time=1.232.782 us)(object id 6258) >> 71323 SORT AGGREGATE (cr=142693 r=9 w=0 time=2140698 us) >> 71323 FIRST ROW (cr=142693 r=9 w=0 time=1516638 us) >> 71323 INDEX RANGE SCAN (MIN/MAX) XPKPF_MESSAGESTATUS >> (cr=142693 r=9 w=0 time=1161703 us)(object id 6254) >> >>
>
> From what I see now, most time is spent in this step: (note that I
> slightly changed your output by adding some dots to compare numbers a
> bit more easily)
> TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEDATASTORAGE
> which uses an Index unique scan over XPKPF_MESSAGEDATASTORAGE
>
> with filter
> access("MD"."ID"="MDS"."ID" AND "MDS"."MESSAGEPARTTYPE"=1)
> filter("MI"."ID"="MDS"."ID")
>
> which is a bit strange filter, but it may be caused by the fact that you
> used one clause too much in your where clause:
> Note that in your query you wrote:
>
> 8 AND mi.ID = ms.ID
> 9 AND mi.ID = md.ID
> 10 AND mi.ID = mds.ID
> 11 AND md.ID = mds.ID
>
> from which you can take out the last part (11) because it is already
> solved by (9)and (10):
> if mi.id = md.id and mi.id = mds.id then automatically md.id = mds.id
>
> Maybe you should check the order of the columns in this index, and see
> if ID and MESSAGEPARTTYPE are the first 2 columns. If not, you could add
> an (extra) index on these columns and see if it helps.
>
> My advise:
> 1) check your statistics (plan does not seem to correspond to the tkprof
> output)
> 2) rewrite your where clause so (11) is taken out, see if that changes
> the plan and tkprof
> 3) check indexes to avoid the index range scan.
>
> Shakespeare
On second thoughts, I think you should leave out (10), not (11), but you could try both (not at the same time though); and the index range scan I thought was there is an index unique scan so I might be completely wrong here, but give it a shot!
Shakespeare Received on Wed Mar 18 2009 - 16:38:19 CDT