Re: Speed up query

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:48ef122b$0$185$e4fe514c_at_news.xs4all.nl...
>
> "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
>
>
>

On second thoughts: I don't think you need the md table at all....since A) you don't select anything from it and B) all join conditions are either hard coded or fullfilled by other tables.

So the only result by adding md could be returning some duplicate rows.

Your condition
>> 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)

Would then be
>> 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 mds.messageparttype = 1 -- NOTE THIS CHANGE!!!
>> 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)

It may be I missed something here, but it's a thing to concider.

Shakespeare Received on Fri Oct 10 2008 - 03:38:11 CDT