Query with LONG RAW

From: bob123 <bob123_at_gmail.com>
Date: Tue, 2 Mar 2010 21:46:51 +0100
Message-ID: <4b8d7939$0$24818$426a34cc_at_news.free.fr>

Hi,

How can I speed up the query below (third party software) it takes 30 min to execute.
Oracle 9.2.0.6
LONG RAW on PFMQ_MESSAGEDATASTORAGE

Thanks in advance

---

SELECT mds.messagepartdata, ms.status, mi.ID, mi.messageguid, mi.channel,

         ROWNUM AS messagecount

    FROM pfmq_messagedata md,
         pfmq_messagedatastorage mds,
         pfmq_messageinfo mi,
         pfmq_messagestatus ms
   WHERE (    mi.queuename = 'CL312911032'
          AND mi.ID = ms.ID
          AND mi.ID = md.ID
          AND mi.ID = mds.ID
          AND md.ID = mds.ID
          AND md.messageparttype = mds.messageparttype
          AND md.messageparttype = 1
          AND (ms.statusrevisionnumber = (SELECT MAX (statusrevisionnumber)
                                            FROM pfmq_messagestatus ms2
                                           WHERE ms2.ID = ms.ID)
              )
         )
     AND ((ms.status = 64) AND (mi.direction = 1) AND mi.messagetype = 0)

ORDER BY mi.sequenceordinalnumber, mi.senttime

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4321 14.56 580.31 231750 746064 0 64806
------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4323 14.56 580.31 231750 746064 0 64806

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Rows Row Source Operation

-------  ---------------------------------------------------

  64806 SORT ORDER BY (cr=681258 r=184767 w=0 time=403515790 us)   64806 COUNT (cr=681258 r=184767 w=0 time=1729762996 us)   64806 NESTED LOOPS (cr=681258 r=184767 w=0 time=1729717540 us)

  64806     NESTED LOOPS  (cr=486600 r=92648 w=0 time=901417748 us)
  64806      NESTED LOOPS  (cr=356748 r=46572 w=0 time=268980743 us)
  64820       TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEINFO (cr=31514 r=23422 
w=0 time=44437657 us)
 120784        INDEX RANGE SCAN AK1_PFMQ_SEQUENCENUMBER (cr=3117 r=3062 w=0 
time=10896605 us)(object id 6511)
  64806       TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGESTATUS (cr=325234 
r=23150 w=0 time=224278563 us)
  64820        INDEX RANGE SCAN XPKPF_MESSAGESTATUS (cr=260414 r=15792 w=0 
time=208616639 us)(object id 6515)
  64820         SORT AGGREGATE (cr=129644 r=116 w=0 time=1973822 us)
  64820          FIRST ROW  (cr=129644 r=116 w=0 time=1810738 us)
  64820           INDEX RANGE SCAN (MIN/MAX) XPKPF_MESSAGESTATUS (cr=129644 
r=116 w=0 time=1756030 us)(object id 6515)
  64806      INDEX UNIQUE SCAN XPKPF_MESSAGEDATA (cr=129852 r=46076 w=0 
time=632244506 us)(object id 6505)
  64806     TABLE ACCESS BY INDEX ROWID PFMQ_MESSAGEDATASTORAGE (cr=194658 
r=92119 w=0 time=828055493 us)
  64806      INDEX UNIQUE SCAN XPKPF_MESSAGEDATASTORAGE (cr=129852 r=46036 

w=0 time=613528422 us)(object id 6507)

---

Received on Tue Mar 02 2010 - 14:46:51 CST