在给用户做优化的发现了一条SQL物理读比较高
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
————— ———— ————– —— ——– ——— ———-
1,342,021 19 70,632.7 59.6 216.01 348.49 2674796510
Module: ORACLE.EXE
SELECT /*+ */ “A3″.”NBXH”,”A1″.”CZRQ” FROM “HZ_QYHZNR” “A3″,”HZ_
QYFDDBR” “A2″,”SYS_LOG” “A1″ WHERE “A3″.”DJJG”=’330424′ AND (“A3
“.”ZT”=’K’ OR “A3″.”ZT”=’B') AND “A3″.”NBXH”=”A2″.”NBXH” AND “A3
“.”NBXH”=SUBSTR(“A1″.”NBXH”,7,16) AND (SUBSTR(“A1″.”CZMK”,1,6)=’
设立登记校对’ OR SUBSTR(“A1″.”CZMK”,1,6)=’变更登记校对’) AND “A1

由于是采集的6级的statspack，查看该语句的完整SQL、统计信息及执行计划
SQL Text
~~~~~~~~
SELECT /*+ */
“A3″.”NBXH”, “A1″.”CZRQ”
FROM “HZ_QYHZNR” “A3″, “HZ_QYFDDBR” “A2″, “SYS_LOG” “A1″
WHERE “A3″.”DJJG” = ‘330424′
AND (“A3″.”ZT” = ‘K’ OR “A3″.”ZT” = ‘B’)
AND “A3″.”NBXH” = “A2″.”NBXH”
AND “A3″.”NBXH” = SUBSTR(“A1″.”NBXH”, 7, 16)
AND (SUBSTR(“A1″.”CZMK”, 1, 6) = ‘设立登记校对’ OR
SUBSTR(“A1″.”CZMK”, 1, 6) = ‘变更登记校对’)
AND “A1″.”CZMK” NOT LIKE ‘%修改%’
AND “A1″.”CZRQ” >= SYSDATE@ ! -1
AND “A2″.”YDDH” LIKE ‘13%’
AND “A2″.”YDDH” > ‘13400000000′
AND “A1″.”CZRQ” >= SYSDATE@ ! -1

% Snap
Statement Total Per Execute Total
————— ————— ——
Buffer Gets: 6,009,268 316,277.3 32.44
Disk Reads: 1,342,021 70,632.7 59.65
Rows processed: 405 21.3
CPU Time(s/ms): 216 11,368.9
Elapsed Time(s/ms): 348 18,341.5
Sorts: 0 .0
Parse Calls: 19 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 31
Executions: 19

——————————————————————————–
| Operation | PHV/Object Name | Rows | Bytes| Cost |
——————————————————————————–
|SELECT STATEMENT |—– 2339037967 —-| | | 6940 |
|HASH JOIN | | 1 | 90 | 6940 |
| NESTED LOOPS | | 1 | 51 | 563 |
| TABLE ACCESS FULL |HZ_QYFDDBR | 1 | 25 | 561 |
| TABLE ACCESS BY INDEX ROWID |HZ_QYHZNR | 1 | 26 | 2 |
| INDEX UNIQUE SCAN |HZ_QYHZNR | 1 | | 1 |
| TABLE ACCESS FULL |SYS_LOG | 4K| 159K| 6376 |
——————————————————————————–

很明显，SYS_LOG表全表扫描并不是一个太合适的访问路径

SQL> select count(1),count(distinct substr(czmk,1,6)) from jxbase.sys_log;

COUNT(1) COUNT(DISTINCTSUBSTR(CZMK,1,6))
———- ——————————-
4759223 164

SQL> create index jxbase.idx_syslog_czmk_czrq ON jxbase.sys_log(czrq,substr(czmk,1,6)) parallel 4 online nologging tablespace INDX;

Index created.

SQL> exec dbms_stats.gather_table_stats(‘jxbase’,'SYS_LOG’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> SELECT “A3″.”NBXH”,”A1″.”CZRQ”
2 FROM jxbase.”HZ_QYHZNR” “A3″,jxbase.”HZ_QYFDDBR” “A2″,jxbase.”SYS_LOG” “A1″
3 WHERE “A3″.”DJJG”=’330424′ AND (“A3″.”ZT”=’K’ OR “A3″.”ZT”=’B')
4 AND “A3″.”NBXH”=”A2″.”NBXH” AND “A3″.”NBXH”=SUBSTR(“A1″.”NBXH”,7,16)
5 AND (SUBSTR(“A1″.”CZMK”,1,6)=’设立登记校对’ OR SUBSTR(“A1″.”CZMK”,1,6)=’变更登记校对’)
6 AND “A1″.”CZMK” NOT LIKE ‘%修改%’
7 AND “A1″.”CZRQ”>=SYSDATE@!-1
8 AND “A2″.”YDDH” LIKE ‘13%’
9 AND “A2″.”YDDH”>’13400000000′ AND “A1″.”CZRQ”>=SYSDATE@!-1
10 /

27 rows selected.

Elapsed: 00:01:02.73

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=839 Card=1 Bytes=91)
1 0 NESTED LOOPS (Cost=839 Card=1 Bytes=91)
2 1 NESTED LOOPS (Cost=563 Card=1 Bytes=51)
3 2 TABLE ACCESS (FULL) OF ‘HZ_QYFDDBR’ (Cost=561 Card=1 B
ytes=25)

4 2 TABLE ACCESS (BY INDEX ROWID) OF ‘HZ_QYHZNR’ (Cost=2 C
ard=1 Bytes=26)

5 4 INDEX (UNIQUE SCAN) OF ‘HZ_QYHZNR’ (UNIQUE) (Cost=1
Card=1)

6 1 TABLE ACCESS (BY INDEX ROWID) OF ‘SYS_LOG’ (Cost=276 Car
d=1 Bytes=40)

7 6 INDEX (RANGE SCAN) OF ‘IDX_SYSLOG_CZMK_CZRQ’ (NON-UNIQ
UE) (Cost=178 Card=522)

Statistics
———————————————————-
0 recursive calls
0 db block gets
2523559 consistent gets
5452 physical reads
208 redo size
1563 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
27 rows processed

看起来物理读下降不少，但是逻辑读增加了将近7倍，挺吓人。
set timing on 观察执行时间，执行时间甚至比以前时间更长。

尝试使用hash join

SQL> SELECT /*+ use_hash(A1 A2)*/ “A3″.”NBXH”,”A1″.”CZRQ”
2 FROM jxbase.”HZ_QYHZNR” “A3″,jxbase.”HZ_QYFDDBR” “A2″,jxbase.”SYS_LOG” “A1″
3 WHERE “A3″.”DJJG”=’330424′ AND (“A3″.”ZT”=’K’ OR “A3″.”ZT”=’B')
4 AND “A3″.”NBXH”=”A2″.”NBXH” AND “A3″.”NBXH”=SUBSTR(“A1″.”NBXH”,7,16)
5 AND (SUBSTR(“A1″.”CZMK”,1,6)=’设立登记校对’ OR SUBSTR(“A1″.”CZMK”,1,6)=’变更登记校对’)
6 AND “A1″.”CZMK” NOT LIKE ‘%修改%’
7 AND “A1″.”CZRQ”>=SYSDATE@!-1
8 AND “A2″.”YDDH” LIKE ‘13%’
9 AND “A2″.”YDDH”>’13400000000′ AND “A1″.”CZRQ”>=SYSDATE@!-1
10 /

27 rows selected.

Elapsed: 00:00:02.12

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=841 Card=1 Bytes=91)
1 0 HASH JOIN (Cost=841 Card=1 Bytes=91)
2 1 NESTED LOOPS (Cost=563 Card=1 Bytes=51)
3 2 TABLE ACCESS (FULL) OF ‘HZ_QYFDDBR’ (Cost=561 Card=1 B
ytes=25)

4 2 TABLE ACCESS (BY INDEX ROWID) OF ‘HZ_QYHZNR’ (Cost=2 C
ard=1 Bytes=26)

5 4 INDEX (UNIQUE SCAN) OF ‘HZ_QYHZNR’ (UNIQUE) (Cost=1
Card=1)

6 1 TABLE ACCESS (BY INDEX ROWID) OF ‘SYS_LOG’ (Cost=277 Car
d=2755 Bytes=110200)

7 6 INDEX (RANGE SCAN) OF ‘IDX_SYSLOG_CZMK_CZRQ’ (NON-UNIQ
UE) (Cost=179 Card=522)

Statistics
———————————————————-
0 recursive calls
0 db block gets
262607 consistent gets
5441 physical reads
104 redo size
1563 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
27 rows processed

看起来使用hash join 效果很明显，但是CBO为什么不选择hash join而选择效率远低于其的nested loop呢
仔细比较执行计划，
当使用NL join，SYS_LOG做innertable时索引访问cost为276，
当使用HJ ，SYS_LOG的索引访问cost为277
有些细微的区别
但是值得怀疑的是，根据 “A2″.”YDDH” LIKE ‘13%’ AND “A2″.”YDDH”>’13400000000′
这两个条件计算出来的HZ_QYFDDBR的全表扫描card为1，很明显误差偏大
做10053trace进行跟踪，查看跟踪文件关注到这一段内容：
SINGLE TABLE ACCESS PATH
Column: YDDH Col#: 28 Table: HZ_QYFDDBR Alias: A2
NDV: 115270 NULLS: 86469 DENS: 8.6753e-06
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: HZ_QYFDDBR ORIG CDN: 217829 ROUNDED CDN: 1 CMPTD CDN: 1
Access path: tsc Resc: 581 Resp: 581
****** trying bitmap/domain indexes ******
Access path: index (no sta/stp keys)
Index: HZ_QYFDDBR
TABLE: HZ_QYFDDBR
RSC_CPU: 0 RSC_IO: 1642
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Access path: index (no sta/stp keys)
Index: IND_HZQYFDDBR_NBXH
TABLE: HZ_QYFDDBR
RSC_CPU: 0 RSC_IO: 1540
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
****** finished trying bitmap/domain indexes ******
BEST_CST: 581.00 PATH: 2 Degree: 1

其中
TABLE: HZ_QYFDDBR ORIG CDN: 217829 ROUNDED CDN: 1 CMPTD CDN: 1
ORIG CDN没有问题，但是CMPTD CDN为1，根据公式CMPTD CDN=ORIN CDN*FF，可以看出FF计算可能存在偏差
在这里因为有两个条件，因此FF=FF1（”A2″.”YDDH” LIKE ‘13%’）*FF2（”A2″.”YDDH”>’13400000000′）
在YDDH字段没有绑定变量，也没有直方图信息，FF1=1/NDV=1/115270,又由于这个字段是varchar2类型，所以FF2的计算不能根据(Hi – value) / (Hi – Lo)来计算
但是即便不知道准确的FF2的值，我们也应该可以估算出CMPTD CDN为1。
问题其实应该出在FF1值的计算上，根据LIKE ‘13%’这么一个条件，（各位看官，看到前面大家心里也应该有个数了，YDDH字段大部分存储的是手机号码），过滤因子被计算为1/NDV，
而NDV高达115270，而如果没有直方图信息，该条件对应的FF的计算公式，只能是1/NDV。
那好吧，收集下直方图。
SQL> analyze table jxbase.HZ_QYFDDBR compute statistics for columns YDDH;

再查看执行计划

SQL> set timing on
SQL> /

28 rows selected.

Elapsed: 00:00:00.43

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2477 Card=92 Bytes=8
188)

1 0 NESTED LOOPS (Cost=2477 Card=92 Bytes=8188)
2 1 HASH JOIN (Cost=2201 Card=92 Bytes=6072)
3 2 TABLE ACCESS (BY INDEX ROWID) OF ‘SYS_LOG’ (Cost=277 C
ard=2755 Bytes=110200)

4 3 INDEX (RANGE SCAN) OF ‘IDX_SYSLOG_CZMK_CZRQ’ (NON-UN
IQUE) (Cost=179 Card=522)

5 2 TABLE ACCESS (FULL) OF ‘HZ_QYHZNR’ (Cost=1922 Card=726
1 Bytes=188786)

6 1 TABLE ACCESS (BY INDEX ROWID) OF ‘HZ_QYFDDBR’ (Cost=3 Ca
rd=1 Bytes=23)

7 6 INDEX (RANGE SCAN) OF ‘HZ_QYFDDBR’ (UNIQUE) (Cost=2 Ca
rd=1)

Statistics
———————————————————-
0 recursive calls
0 db block gets
20458 consistent gets
20 physical reads
0 redo size
1589 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed

执行计划大变（oracle果然牛X，原谅我没有这个眼力，因为我不是CBO也不是boypoo），执行时间和逻辑读、物理读均大大减少。