sql调优-hash join应用一例

这是在系统中抓到的一个及其消耗cpu资源的一个语句，尝试进行调整。调整前后效果十分明显。[@more@]

SQL> select count(*) from DBGIFTRUN.WCHNGIFTAWARDIMPORT;

COUNT(*)
----------
41886

SQL> select count(*) from DBGIFTRUN.SCHNACTIVEGRADE;

COUNT(*)
----------
658

SQL> select count(*) from WAWARDDATA;

COUNT(*)
----------
3821381

SQL> SELECT count (1)
2 FROM DBGIFTRUN.WCHNGIFTAWARDIMPORT a, DBGIFTRUN.SCHNACTIVEGRADE b, WAWARDDATA c
3 WHERE a.grade_code = b.grade_code
4 AND a.login_accept = c.login_accept
5 AND a.phone_no = c.phone_no
6 AND c.flag = 'N'
7 AND a.grade_code = '10000743'
8 AND a.import_date >= '2007-08-16'
9 AND a.import_date <= '2007-08-16'
10 /

Elapsed: 00:16:27.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=1 Bytes=76)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'WAWARDDATA' (Cost=2 Card=1 Bytes=26)
3 2 NESTED LOOPS (Cost=414 Card=1 Bytes=76)
4 3 NESTED LOOPS (Cost=4 Card=205 Bytes=10250)
5 4 INDEX (UNIQUE SCAN) OF 'SCHNACTIVEGRADE_PK' (UNIQUE) (Cost=1 Card=1 Bytes=9)
6 4 TABLE ACCESS (FULL) OF 'WCHNGIFTAWARDIMPORT' (Cost=3 Card=205 Bytes=8405)
7 3 INDEX (RANGE SCAN) OF 'WAWARD_LOGIN_ACC' (NON-UNIQUE) (Cost=2 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20528998 consistent gets
596 physical reads 0 redo size
493 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT /*+ use_hash(c) */ count (1)
2 FROM DBGIFTRUN.WCHNGIFTAWARDIMPORT a, DBGIFTRUN.SCHNACTIVEGRADE b, WAWARDDATA c
3 WHERE a.grade_code = b.grade_code
4 AND a.login_accept = c.login_accept
5 AND a.phone_no = c.phone_no
6 AND c.flag = 'N'
7 AND a.grade_code = '10000743'
8 AND a.import_date >= '2007-08-16'
9 AND a.import_date <= '2007-08-16'
10 /

Elapsed: 00:00:17.18

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6082 Card=1 Bytes=76)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=6082 Card=1 Bytes=76)
3 2 NESTED LOOPS (Cost=4 Card=205 Bytes=10250)
4 3 INDEX (UNIQUE SCAN) OF 'SCHNACTIVEGRADE_PK' (UNIQUE) (Cost=1 Card=1 Bytes=9)
5 3 TABLE ACCESS (FULL) OF 'WCHNGIFTAWARDIMPORT' (Cost=3 Card=205 Bytes=8405)
6 2 TABLE ACCESS (FULL) OF 'WAWARDDATA' (Cost=6069 Card=1835490 Bytes=47722740)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
65530 consistent gets
56894 physical reads 0 redo size
493 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

分析

我们看到这里有着及其慢的响应速度，返回数据需要16分钟。经过查看发现a表和b表返回的记录数达到4万多条，而a表和c表返回的记录数也高达4万多条，而oracle优化器选择的是嵌套循环连接，这将导致4万*4万次循环，故而导致了很长的相应时间和极高的逻辑读，大量消耗cpu资源。

前后对比
响应时间由16分钟下降到17秒；
逻辑读由20528998下降到65530；
可以看到优化效果十分明显，极大的缩短了响应时间降低了逻辑读，从而提高程序的响应速度，节省了对系统cpu资源的消耗。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7794469/viewspace-964131/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7794469/viewspace-964131/