一条SQL优化的过程分享，以及几个问题请教。

显示全部楼层 · 2005-10-30 17:05:33

ORACLE VERSION: 7.3.4.4.0
OS: Compaq Tru64 UNIX V5.1
这是在实际的系统中一个多表连接查询的例子，分析过程中对NESTED LOOP和SORT MERGE两种连接方式做了比较，可能有些地方分析的不对，还请各位高手多多指教。
SQL>SET AUTOTRACE TRACEONLY;
a：20万行
b：10万行
c ：1.6万行
d ：3800行
e：6000行
连接列上都建有索引，优化模式RULE
没有使用任何HINT的查询：
SQL>SELECT a.SEGMENT1, b.SECONDARY_INVENTORY, d.SEGMENT1,
a.ORGANIZATION_ID, c.SUBINVENTORY_CODE,a.DESCRIPTION
FROM a, b, c, d
WHERE a.SEGMENT1 NOT LIKE '*'
AND(a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID
AND a.ORGANIZATION_ID=b.ORGANIZATION_ID
AND b.ORGANIZATION_ID=c.ORGANIZATION_ID
AND b.INVENTORY_ITEM_ID=c.INVENTORY_ITEM_ID
AND b.SECONDARY_INVENTORY=c.SUBINVENTORY_CODE
AND d.ORGANIZATION_ID=c.ORGANIZATION_ID
AND d.INVENTORY_LOCATION_ID=c.SECONDARY_LOCATOR
ANDa.INVENTORY_ITEM_ID in (select uniquee.INVENTORY_ITEM_IDfrom e))
Elapsed: 00:04:41.66
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=RULE
10 NESTED LOOPS
21 NESTED LOOPS
32 NESTED LOOPS
43 NESTED LOOPS
54 VIEW
65
SORT (UNIQUE)
76
TABLE ACCESS (FULL) OF 'E'
84 TABLE ACCESS (BY ROWID) OF 'a'
98
INDEX (RANGE SCAN) OF 'a_U1' (UNIQUE)
103 INDEX (RANGE SCAN) OF 'b_U2' (UNIQUE)
112 TABLE ACCESS (BY ROWID) OF 'c'
12 11 INDEX (RANGE SCAN) OF 'c_U1' (UNIQUE)
131 TABLE ACCESS (BY ROWID) OF 'd'
14 13 INDEX (UNIQUE SCAN) OF 'd_U1' (UNIQUE)
Statistics
----------------------------------------------------------

0recursive calls

2db block gets
772030consistent gets

0physical reads

0redo size
158593bytes sent via SQL*Net to client
2331bytes received via SQL*Net from client
114SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)
1678rows processed
查询过程中监视CPU使用情况
$vmstat 3
procsmemorypages
intr cpu
r w uact free wire faultcow zero reactpin poutinsycs us sy id
...
4 26336 191K50K14K 2443668 0 68 0 6 222 418 501 49
5 26236 191K50K14K 000 000 668 411 500 50
4 26336 191K50K14K 000 000 147 382 500 50
4 26336 191K50K14K 000 000 023 389 500 50
4 26336 191K50K14K 000 000 257 386 500 50
4 26336 191K50K14K 000 000 268 412 500 50
5 26236 191K50K14K 000 00010 159K394 81 190
6 26036 191K50K14K 000 000 1 154K445 82 180
4 26336 191K50K14K 101 000 7 86K 495 69 11 20
5 26236 191K50K14K 000 0001028 407 500 50
...
可以看到，ORACLE使用NESTED LOOP连接表，查询过程中CPU的使用率保持在50%以上，由于数据已在BUFFER CACHE中，因此没有PHYSICAL READ,只有 772030 次consistent gets

现在使用USE_MERGE(TABLE)让ORACLE使用SORT MERGE方式连接：
SQL>SELECT
/*+ use_merge(a,b,c,d,E) */
a.SEGMENT1, b.SECONDARY_INVENTORY, d.SEGMENT1,
a.ORGANIZATION_ID, c.SUBINVENTORY_CODE,a.DESCRIPTION
FROM a, b, c, d
WHERE a.SEGMENT1 NOT LIKE '*'
AND(a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID
AND a.ORGANIZATION_ID=b.ORGANIZATION_ID
AND b.ORGANIZATION_ID=c.ORGANIZATION_ID
AND b.INVENTORY_ITEM_ID=c.INVENTORY_ITEM_ID
AND b.SECONDARY_INVENTORY=c.SUBINVENTORY_CODE
AND d.ORGANIZATION_ID=c.ORGANIZATION_ID
AND d.INVENTORY_LOCATION_ID=c.SECONDARY_LOCATOR
ANDa.INVENTORY_ITEM_ID in (select uniquee.INVENTORY_ITEM_IDfrom e))
1678 rows selected.
Elapsed: 00:00:04.80
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=RULE (Cost=31 Card=2 Bytes=822)
10 NESTED LOOPS (Cost=31 Card=2 Bytes=822)
21 HASH JOIN (Cost=30 Card=1 Bytes=363)
32 NESTED LOOPS (Cost=19 Card=5 Bytes=1750)
43 NESTED LOOPS (Cost=14 Card=5 Bytes=660)
54 TABLE ACCESS (FULL) OF 'c' (Cost=14 Card=2000 Bytes=132000)
64 INDEX (UNIQUE SCAN) OF 'b_U1' (UNIQUE) (Cost=26 Card=2000 Bytes=132000)
73 TABLE ACCESS (BY ROWID) OF 'a'
87 INDEX (UNIQUE SCAN) OF 'a_U1' (UNIQUE)
92 VIEW (Cost=10 Card=2000 Bytes=26000)
109 SORT (UNIQUE)
11 10 TABLE ACCESS (FULL) OF 'E' (Cost=1 Card=2000 Bytes=26000)
121 TABLE ACCESS (BY ROWID) OF 'd'
13 12 INDEX (UNIQUE SCAN) OF 'd_U1' (UNIQUE)
Statistics
----------------------------------------------------------
103recursive calls
22db block gets
117618consistent gets

0physical reads
1959redo size
158593bytes sent via SQL*Net to client
2445bytes received via SQL*Net from client
114SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)
1678rows processed

$vmstat 3
Virtual Memory Statistics: (pagesize = 8192)
procsmemorypages
intr cpu
r w uact free wire faultcow zero reactpin poutinsycs us sy id
4 26436 191K50K14K 3 15 18 0 270 445 42040 96
3 26437 191K50K14K 000 00014 147 471 460 54
3 26436 191K50K14K 000 00015 951 667 221 77
3 26436 191K50K14K 000 000 327 41900 100
3 26436 191K50K14K 000 00017 518 72551 95
3 26436 191K50K14K 000 000 135 40300 100

使用了HINT以后可以看出：
执行时间由04:41.66减少到00:04.80；
CPU负载降的不如查询时间那么明显，但已经降到50%以下；
consistent gets由772030 次降到117618次。
从这次分析可以得出：关联多张表，返回的行数相对比较多的话，使用MERGE比NESTED LOOP占用的资源更少，查询速度更快。

但是有几个问题
第二次使用USE_MERGE提示以后，执行计划里为什么还有NESTED LOOP，而且用的是HASH JOIN而不是SORT MERGE JOIN？
另外在执行计划里出现的a_U1,d_U2这样的字样是什么意思？
是否是我HINT写的不对，还是由其他的机制产生这种执行计划？

千问 · 2005-10-30 17:05:33

如果一个联接输入很小（比如不到 10 行），而另一个联接输入很大而且已在其联接列上创建索引，则索引嵌套循环是最快的联接操作，因为它们需要最少的 I/O 和最少的比较。
如果两个联接输入并不小但已在二者联接列上排序（例如，如果它们是通过扫描已排序的索引获得的），则合并联接是最快的联接操作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相似。然而，如果两个输入的大小相差很大，则哈希联接操作通常快得多

千问 · 2005-10-30 17:05:33

a_U1,d_U2应该是你的索引名
你的E表是inline view，不能够merge的
你的merge hint应该用在两个表上，不能那些表一起吧
即指定哪两个表进行jion
可以同时加merge和ordered提示看看，这样所有的join都应该是merge了

千问 · 2005-10-30 17:05:33

最初由 BruDog 发布
[B]
从这次分析可以得出：关联多张表，返回的行数相对比较多的话，使用MERGE比NESTED LOOP占用的资源更少，查询速度更快。
[/B]
from metalink:
Sort Merge Join
~~~~~~~~~~~~~~~
Rows are produced by Row Source 1 and are then sorted, Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently. Sorted rows from both sides are then merged together (joined)
MERGE
/ \
SORT SORT
| |
Row Source 1 Row Source 2
If the row sources are already (known to be) sorted then the sort operation is unnecessary as long as both 'sides' are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel.
Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

3.2.2 Nested Loops
~~~~~~~~~~~~
First we return all the rows from row source 1, Then we probe row source 2 once for each row returned from row source 1
Row source 1
~~~~~~~~~~~~
Row 1 -------------- -- Probe -> Row source 2
Row 2 -------------- -- Probe -> Row source 2
Row 3 -------------- -- Probe -> Row source 2
Row source 1 is known as the outer table, Row source 2 is known as the inner table. Accessing row source 2 is known as probing the inner table. For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

千问 · 2005-10-30 17:05:33

最初由 shahand 发布
[B]a_U1,d_U2应该是你的索引名
你的E表是inline view，不能够merge的
你的merge hint应该用在两个表上，不能那些表一起吧
即指定哪两个表进行jion
可以同时加merge和ordered提示看看，这样所有的join都应该是merge了 [/B]
同时使用两个HINT是这样用吗？
SELECT
/*+ use_merge(a b c d e) ordered */ ...
还有为什么产生的执行计划是HASH JOIN？明明用的是USE_MERGE

千问 · 2005-10-30 17:05:33

数据库版本比较低

千问 · 2005-10-30 17:05:33

还有一个问题：
103 recursive calls
22 db block gets
117618 consistent gets
这里22 db block gets的意思是整个查询只访问了22个数据块吗？

千问 · 2005-10-30 17:05:33

还是看不懂执行计划，刚才又做了另一个测试，HINT是USE_MERGE，但是执行计划里有HASH_JOIN，也有MERGE_JOIN，到底是什么因素影响执行计划使用哪种连接方式呢？
都是在RULE优化模式下

千问 · 2005-10-30 17:05:33

继续测试发现：使用USE_HASH和USE_MERGE产生的执行计划是一样的，而且最大的表a总是使用NESTED LOOP跟某一个表做连接，不管HINT怎么写：USE_MERGE/HASH(a)/(a b)(a c)/(a b c)...