Re: Query help please

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 18 Mar 2009 22:32:22 +0100
Message-ID: <49c16875$0$190$e4fe514c_at_news.xs4all.nl>

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 Received on Wed Mar 18 2009 - 16:32:22 CDT