Re: Query help please
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 18 Mar 2009 09:11:21 +0100
Message-ID: <49c0acb9$0$198$e4fe514c_at_news.xs4all.nl>
bob123 schreef:
> 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
>
>
>
Hard to tell without the actual data, stats etc, but it seems there is no index used for ms.status in the ms.status=64 part, causing a full table scan with cost 1170, which is the main part of your cost. Either there is no index usable, or status is not a very discriminating column. You could check that part, and check if statistics on this table and its indexes have been run lately.
Date: Wed, 18 Mar 2009 09:11:21 +0100
Message-ID: <49c0acb9$0$198$e4fe514c_at_news.xs4all.nl>
bob123 schreef:
> 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
>
>
>
Hard to tell without the actual data, stats etc, but it seems there is no index used for ms.status in the ms.status=64 part, causing a full table scan with cost 1170, which is the main part of your cost. Either there is no index usable, or status is not a very discriminating column. You could check that part, and check if statistics on this table and its indexes have been run lately.
Shakespeare Received on Wed Mar 18 2009 - 03:11:21 CDT