Speed up query

From: astalavista <nobody_at_nowhere.com>
Date: Wed, 8 Oct 2008 21:45:49 +0200
Message-ID: <48ed0ded$0$1059$426a74cc@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. Received on Wed Oct 08 2008 - 14:45:49 CDT