Query help please

From: bob123 <bob123_at_gmail.com>
Date: Tue, 17 Mar 2009 21:37:24 +0100
Message-ID: <49c009fa$0$20335$426a74cc_at_news.free.fr>

Hi,

How can I optimize this query (30 min) (9.2.0.6)

Thanks for your help ....

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

  2           ROWNUM AS messagecount
  3      FROM pfmq_messagedata md,
  4           pfmq_messagedatastorage mds,
  5           pfmq_messageinfo mi,
  6           pfmq_messagestatus ms
  7     WHERE (    mi.queuename = 'CL318886012'
  8            AND mi.ID = ms.ID
  9            AND mi.ID = md.ID
 10            AND mi.ID = mds.ID
 11            AND md.ID = mds.ID
 12            AND md.messageparttype = mds.messageparttype
 13            AND md.messageparttype = 1
 14            AND (ms.statusrevisionnumber = (SELECT MAX 
(statusrevisionnumber)
 15                                              FROM pfmq_messagestatus ms2
 16                                             WHERE ms2.ID = ms.ID)
 17                )
 18           )
 19       AND ((ms.status = 64) AND (mi.direction = 1) AND mi.messagetype = 

0)
 20 ORDER BY mi.sequenceordinalnumber, mi.senttime  21 /

71310 rows selected.

Elapsed: 00:31:23.09

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1178 Card=1 Bytes=283)

          1                  0

  SORT (ORDER BY) (Cost=1178 Card=1 Bytes=283)

          2                  1

    COUNT

          3                  2
      FILTER


          4                  3
        NESTED LOOPS (Cost=1175 Card=1 Bytes=283)


          5                  4
          NESTED LOOPS (Cost=1173 Card=1 Bytes=241)


          6                  5
            NESTED LOOPS (Cost=1172 Card=1 Bytes=199)


          7                  6
              TABLE ACCESS (FULL) OF 'PFMQ_MESSAGESTATUS' (Cost=1170 Card=1 

Bytes=53)

          8                  6
              TABLE ACCESS (BY INDEX ROWID) OF 'PFMQ_MESSAGEINFO' (Cost=2 

Card=1 Bytes=146)

          9                  8
                INDEX (UNIQUE SCAN) OF 'XPKPF_MESSAGEINFO' (UNIQUE) (Cost=1 

Card=36)

         10                  5
            INDEX (UNIQUE SCAN) OF 'XPKPF_MESSAGEDATA' (UNIQUE) (Cost=1 

Card=1 Bytes=42)

         11                  4
          TABLE ACCESS (BY INDEX ROWID) OF 'PFMQ_MESSAGEDATASTORAGE' (Cost=2 

Card=1 Bytes=42)

         12                 11
            INDEX (UNIQUE SCAN) OF 'XPKPF_MESSAGEDATASTORAGE' (UNIQUE) 

(Cost=1 Card=1)

         13                  3
        SORT (AGGREGATE)


         14                 13
          FIRST ROW (Cost=3 Card=2 Bytes=100)


         15                 14
            INDEX (RANGE SCAN (MIN/MAX)) OF 'XPKPF_MESSAGESTATUS' (UNIQUE) 

(Cost=3 Card=740381)

Statistics

---

          0  recursive calls
          2  db block gets
    1456027  consistent gets
     137502  physical reads
        164  redo size
   28447285  bytes sent via SQL*Net to client
      52797  bytes received via SQL*Net from client
       4756  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      71310  rows processed


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


| Id  | Operation                         |  Name                     | Rows 

| Bytes | Cost |

---

| 0 | SELECT STATEMENT | |
1 | 283 | 1178 |
| 1 | SORT ORDER BY | |
1 | 283 | 1178 |
| 2 | COUNT | |
| | |

|*  3 |    FILTER                         |                           | 


|       |       |

|   4 |     NESTED LOOPS                  |                           | 

1 |   283 |  1175 |


|   5 |      NESTED LOOPS                 |                           | 

1 |   241 |  1173 |


|   6 |       NESTED LOOPS                |                           | 

1 |   199 |  1172 |
|*  7 |        TABLE ACCESS FULL          | PFMQ_MESSAGESTATUS        | 
1 |    53 |  1170 |
|*  8 |        TABLE ACCESS BY INDEX ROWID| PFMQ_MESSAGEINFO          | 
1 |   146 |     2 |
|*  9 |         INDEX UNIQUE SCAN         | XPKPF_MESSAGEINFO         | 
36 |       |     1 |
|* 10 |       INDEX UNIQUE SCAN           | XPKPF_MESSAGEDATA         | 
1 |    42 |     1 |


|  11 |      TABLE ACCESS BY INDEX ROWID  | PFMQ_MESSAGEDATASTORAGE   | 

1 |    42 |     2 |
|* 12 |       INDEX UNIQUE SCAN           | XPKPF_MESSAGEDATASTORAGE  | 
1 |       |     1 |


|  13 |     SORT AGGREGATE                |                           | 

1 |    50 |       |


|  14 |      FIRST ROW                    |                           | 

2 |   100 |     3 |
|* 15 |       INDEX RANGE SCAN (MIN/MAX)  | XPKPF_MESSAGESTATUS       | 
740K|       |     3 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   3 - filter("SYS_ALIAS_1"."STATUSREVISIONNUMBER"= (SELECT /*+ */

              MAX("MS2"."STATUSREVISIONNUMBER") FROM "PFMQ_MESSAGESTATUS" "MS2" WHERE "MS2"."ID"=:B1))
   7 - filter("SYS_ALIAS_1"."STATUS"=64)

   8 - filter("MI"."QUEUENAME"='CL318886012' AND "MI"."DIRECTION"=1 AND 
"MI"."MESSAGETYPE"=0)
   9 - access("MI"."ID"="SYS_ALIAS_1"."ID")
  10 - access("MI"."ID"="MD"."ID" AND "MD"."MESSAGEPARTTYPE"=1)
  12 - access("MD"."ID"="MDS"."ID" AND "MDS"."MESSAGEPARTTYPE"=1)
       filter("MI"."ID"="MDS"."ID")

  15 - access("MS2"."ID"=:B1)

Note: cpu costing is off

36 rows selected.

Elapsed: 00:00:06.09 Received on Tue Mar 17 2009 - 15:37:24 CDT