Re: Speed up query
Date: Wed, 8 Oct 2008 13:17:11 -0700 (PDT)
Message-ID: <53066df7-595e-40c4-8a12-a81bbe0f570b@z18g2000prn.googlegroups.com>
On Oct 8, 2:45 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> 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.
Please set autotrace on, execute the query and post the plan and query statistics.
David Fitzjarrell Received on Wed Oct 08 2008 - 15:17:11 CDT