Re: Speed up query

"astalavista" <nobody_at_nowhere.com> schreef in bericht news:48ed0ded$0$1059$426a74cc_at_news.free.fr...
> Hi,
> Is it possible to speed up the query below (9.2.0.6) ?
> Thanks in advance
>
> 2 SELECT mds.messagepartdata, ms.status, mi.ID, mi.messageguid,
> mi.channel,
> 3 ROWNUM AS messagecount
> 4 FROM pfmq_messagedata md,
> 5 pfmq_messagedatastorage mds,
> 6 pfmq_messageinfo mi,
> 7 pfmq_messagestatus ms
> 8 WHERE ( mi.queuename = 'CL318886012'
> 9 AND mi.ID = ms.ID
> 10 AND mi.ID = md.ID
> 11 AND mi.ID = mds.ID
> 12 AND md.ID = mds.ID
> 13 AND md.messageparttype = mds.messageparttype
> 14 AND md.messageparttype = 1
> 15 AND (ms.statusrevisionnumber = (SELECT MAX
> (statusrevisionnumber)
> 16 FROM pfmq_messagestatus
> ms2
> 17 WHERE ms2.ID = ms.ID)
> 18 )
> 19 )
> 20 AND ((ms.status = 32) AND (mi.direction = 1) AND mi.messagetype =
> 0)
> 21 ORDER BY mi.sequenceordinalnumber, mi.senttime
> 22
>
> ----------------------------------------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost (%CPU)|
> ----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1
> | 283 | 119 (0)|
> | 1 | SORT ORDER BY | | 1
> | 283 | 119 (0)|
> | 2 | COUNT | | |
> | |
> |* 3 | FILTER | | |
> | |
> | 4 | NESTED LOOPS | | 1
> | 283 | 117 (0)|
> | 5 | NESTED LOOPS | | 1
> | 241 | 116 (0)|
> | 6 | NESTED LOOPS | | 1
> | 199 | 115 (0)|
> | 7 | TABLE ACCESS BY INDEX ROWID| PFMQ_MESSAGESTATUS | 1
> | 53 | 114 (0)|
> |* 8 | INDEX RANGE SCAN | PFMQ_MESSAGESTATUS_I1 |
> 248K| | 448 (0)|
> |* 9 | TABLE ACCESS BY INDEX ROWID| PFMQ_MESSAGEINFO | 1
> | 146 | 2 (50)|
> |* 10 | INDEX UNIQUE SCAN | XPKPF_MESSAGEINFO | 7
> | | |
> | 11 | TABLE ACCESS BY INDEX ROWID | PFMQ_MESSAGEDATASTORAGE | 1
> | 42 | 2 (50)|
> |* 12 | INDEX UNIQUE SCAN | XPKPF_MESSAGEDATASTORAGE | 2
> | | |
> |* 13 | INDEX UNIQUE SCAN | XPKPF_MESSAGEDATA | 1
> | 42 | |
> | 14 | SORT AGGREGATE | | 1
> | 50 | |
> | 15 | FIRST ROW | | 5
> | 250 | 3 (0)|
> |* 16 | INDEX RANGE SCAN (MIN/MAX) | XPKPF_MESSAGESTATUS |
> 227K| | 3 (0)|
> ----------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - filter("SYS_ALIAS_1"."STATUSREVISIONNUMBER"= (SELECT /*+ */
> MAX("MS2"."STATUSREVISIONNUMBER") FROM "PFMQ_MESSAGESTATUS"
> "MS2" WHERE "MS2"."ID"=:B1))
> 8 - access("SYS_ALIAS_1"."STATUS"=32)
> 9 - filter("MI"."QUEUENAME"='CL318886012' AND "MI"."DIRECTION"=1 AND
> "MI"."MESSAGETYPE"=0)
> 10 - access("MI"."ID"="SYS_ALIAS_1"."ID")
> 12 - access("MI"."ID"="MDS"."ID" AND "MDS"."MESSAGEPARTTYPE"=1)
> 13 - access("MD"."ID"="MDS"."ID" AND "MD"."MESSAGEPARTTYPE"=1)
> filter("MI"."ID"="MD"."ID")
> 16 - access("MS2"."ID"=:B1)
>
> 35 rows selected.
>
>

Without the exact model, contents and stats it is hard to find out. You could experiment with leaving out the part
> 15 AND (ms.statusrevisionnumber = (SELECT MAX
> (statusrevisionnumber)
> 16 FROM pfmq_messagestatus
> ms2
> 17 WHERE ms2.ID = ms.ID)
> 18 )
> 19 )

and select the max statusrevisionnumber afterwards. (It may depend on the number of rows in each table...)
Furthermore, I wonder if you repeat the query twice in a row, will the second one be faster?
Third: you use a lot of brackets with your AND clauses, with is not needed: (A AND B) AND (C AND D) is the same as A AND B AND C AND D. I guess the optimizer will see that, but I'm not too sure about that, looking at the filters.
Fourth: one of the conditions
> 11 AND mi.ID = mds.ID
> 12 AND md.ID = mds.ID

is obsolete, since
> 10 AND mi.ID = md.ID

I'd leave the one where there is a foreign key and delete the other one (and I guess there is a foreign key between md and mds)

Again, it's guessing....

Shakespeare Received on Fri Oct 10 2008 - 03:28:27 CDT