SQL Tuning之 OR的优化。
今天公司某Production DB时常LOADING飚起来，Monitor下发现一个很high的SQL:
SELECT COUNT(A.ISN) FROM MO_ROUTE A,MO B WHERE (A.ROUTE = :B2 OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1' AND (A.MO LIKE 'NFPQ%' OR A.MO LIKE 'NF1Q%' OR A.MO LIKE 'NF6Q%') AND A.MO = B.MO(+) AND A.INTIME BETWEEN :B1 - 1 AND :B1 AND B.CDATE BETWEEN :B1 - 1 AND :B1
其PLAN如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2195 Card=1 Bytes=49
)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 NESTED LOOPS (Cost=2195 Card=1 Bytes=49)
4 3 PARTITION RANGE (ITERATOR)
5 4 PARTITION HASH (ALL)
6 5 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE
' (Cost=2194 Card=1 Bytes=30)
7 6 INDEX (RANGE SCAN) OF 'MO_ROUTE11' (NON-UNIQUE
) (Cost=1242 Card=1741)
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=1 Card=1
Bytes=19)
9 8 INDEX (UNIQUE SCAN) OF 'MO1' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39135consistent gets
25654physical reads
106180redo size
521bytes sent via SQL*Net to client
656bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
很好，很强大的Gets，这可是OLTP环境。
MO_ROUTE是一个RANGE-HASH Partitioned Table，INTIME作为进行RANGE的Column.从PLAN可以看出，经过了Partition Pruning，再走MO_ROUTE11这个INDEX。 MO_ROUTE11是一个Complex Index，以Intime为首列（Intime,section,grp）。看起来选则的比较有道理。
然而这个Table即使是一天的数据量也很大。这个时侯注意到MO,MO的可选择性应该也是比较强的。
+HINT看一下：
SELECT /*+INDEX(A MO_ROUTE2)*/COUNT(A.ISN) FROM MO_ROUTE A,MO B WHERE
(A.ROUTE = 'VNMB'OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'
AND (A.MO LIKE 'NFPQ%' OR A.MO LIKE 'NF1Q%' OR A.MO LIKE 'NF6Q%')
AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate
AND B.CDATE BETWEEN sysdate - 1 AND sysdate；
EXPLAN:
SELECT STATEMENT, GOAL = CHOOSECost=7414Cardinality=1 Bytes=49
SORT AGGREGATECardinality=1Bytes=49
CONCATENATION
FILTER
NESTED LOOPSCost=85Cardinality=1 Bytes=49
PARTITION RANGE ITERATOR
PARTITION HASH ALL
TABLE ACCESS BY LOCAL INDEX ROWIDObject owner=TPObject name=MO_ROUTE Cost=84Cardinality=1 Bytes=30
INDEX RANGE SCANObject owner=TPObject name=MO_ROUTE2 Cost=66Cardinality=17
TABLE ACCESS BY INDEX ROWIDObject owner=TPObject name=MO Cost=1 Cardinality=1 Bytes=19
INDEX UNIQUE SCANObject owner=TPObject name=MO1 Cardinality=1
FILTER
NESTED LOOPSCost=85Cardinality=1 Bytes=49
PARTITION RANGE ITERATOR
PARTITION HASH ALL
TABLE ACCESS BY LOCAL INDEX ROWIDObject owner=TPObject name=MO_ROUTE Cost=84Cardinality=1 Bytes=30
INDEX RANGE SCANObject owner=TPObject name=MO_ROUTE2 Cost=66Cardinality=17
TABLE ACCESS BY INDEX ROWIDObject owner=TPObject name=MO Cost=1 Cardinality=1 Bytes=19
INDEX UNIQUE SCANObject owner=TPObject name=MO1 Cardinality=1
FILTER
NESTED LOOPSCost=85Cardinality=1 Bytes=49
PARTITION RANGE ITERATOR
PARTITION HASH ALL
TABLE ACCESS BY LOCAL INDEX ROWIDObject owner=TPObject name=MO_ROUTE Cost=84Cardinality=1 Bytes=30
INDEX RANGE SCANObject owner=TPObject name=MO_ROUTE2 Cost=66Cardinality=17
TABLE ACCESS BY INDEX ROWIDObject owner=TPObject name=MO Cost=1 Cardinality=1 Bytes=19
INDEX UNIQUE SCANObject owner=TPObject name=MO1 Cardinality=1
COST变成7K多，而且实际过程中我只能把它CANCEL掉，否则Server Loading会一下子飙起来。
计划里只是到最后的总COST很高，单步的COST却较小。
于是想到用Union All来替代OR:
---------------------------------------------------------------------
select sum(c.n) from
(SELECT COUNT(A.ISN) n FROM MO_ROUTE A,MO B WHERE
(A.ROUTE = 'VNMB' OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'
AND A.MO LIKE 'NFPQ%'
AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate
AND B.CDATE BETWEEN sysdate - 1 AND sysdate
union all
SELECT COUNT(A.ISN) n FROM MO_ROUTE A,MO B WHERE
(A.ROUTE = 'VNMB' OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'
AND A.MO LIKE 'NF1Q%'
AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate
AND B.CDATE BETWEEN sysdate - 1 AND sysdate
union all
SELECT COUNT(A.ISN) n FROM MO_ROUTE A,MO B WHERE
(A.ROUTE = 'VNMB' OR A.ROUTE = 'LNBR') AND A.GRP = 'AI1'
AND A.MO LIKE 'NF6Q%'
AND A.MO = B.MO(+) AND A.INTIME BETWEEN sysdate - 1 AND sysdate
AND B.CDATE BETWEEN sysdate - 1 AND sysdate) c
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=216 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=216 Card=3 Bytes=39)
3 2 UNION-ALL
4 3 SORT (AGGREGATE)
5 4 FILTER
6 5 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE
' (Cost=69 Card=1 Bytes=30)
7 6 NESTED LOOPS (Cost=72 Card=1 Bytes=49)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=
3 Card=1 Bytes=19)
9 8 INDEX (RANGE SCAN) OF 'MO1' (UNIQUE) (Cost
=2 Card=1)
10 7 PARTITION RANGE (ITERATOR)
11 10 PARTITION HASH (ALL)
12 11 INDEX (RANGE SCAN) OF 'MO_ROUTE2' (NON-U
NIQUE) (Cost=65 Card=3)
13 3 SORT (AGGREGATE)
14 13 FILTER
15 14 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE
' (Cost=69 Card=1 Bytes=30)
16 15 NESTED LOOPS (Cost=72 Card=1 Bytes=49)
17 16 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=
3 Card=1 Bytes=19)
18 17 INDEX (RANGE SCAN) OF 'MO1' (UNIQUE) (Cost
=2 Card=1)
19 16 PARTITION RANGE (ITERATOR)
20 19 PARTITION HASH (ALL)
21 20 INDEX (RANGE SCAN) OF 'MO_ROUTE2' (NON-U
NIQUE) (Cost=65 Card=3)
22 3 SORT (AGGREGATE)
23 22 FILTER
24 23 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MO_ROUTE
' (Cost=69 Card=1 Bytes=30)
25 24 NESTED LOOPS (Cost=72 Card=1 Bytes=49)
26 25 TABLE ACCESS (BY INDEX ROWID) OF 'MO' (Cost=
3 Card=1 Bytes=19)
27 26 INDEX (RANGE SCAN) OF 'MO1' (UNIQUE) (Cost
=2 Card=1)
28 25 PARTITION RANGE (ITERATOR)
29 28 PARTITION HASH (ALL)
30 29 INDEX (RANGE SCAN) OF 'MO_ROUTE2' (NON-U
NIQUE) (Cost=65 Card=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2592consistent gets
49physical reads
1336redo size
517bytes sent via SQL*Net to client
656bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
COST降低到200多点，Buffer Gets和Physical reads大幅度减少。
Tuning的目标达成。
扫一扫
215
到【灌水乐园】发言
