Re: Speed up query

On Oct 8, 4:17 pm, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I would like to know how many rows in each table and how many rows are returned by each filter condition, that is, how many rows match queuename = 'CL318886012', messageparttype = 1, and how many rows are returned by the subquery.

The total run time should be provided by David's suggestion.

A list of available indexes doesn't hurt either though some of them can be inferred from the plan.

HTH -- Mark D Powell -- Received on Thu Oct 09 2008 - 09:32:48 CDT