频率直方图 与 绑定变量
下面两个执行计划 是上次日志中依据频率直方图选择的执行计划
select count(1) from tab_01_03 t where b=1
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1 | |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX RANGE SCAN | IDX_B | 2 | 6 | 1 | 00:00:01 |
-------------------------------------+-----------------------------------+
select count(1) from tab_01_03 t where b=1000
============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL | TAB_01_03| 10K | 29K | 6 | 00:00:01 |
---------------------------------------+-----------------------------------+
现在把这个语句改改 绑定变量 先赋值10000 再试试 1看看
trace出来 看看:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TAB_01_03 Alias: T
#Rows: 11116 #Blks: 20 AvgRowLen: 6.00
Index Stats::
Index: IDX_B Col#: 2
LVLS: 1 #LB: 22 #DK: 5 LB/K: 4.00 DB/K: 3.00 CLUF: 18.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#2): B(NUMBER)
AvgLen: 3.00 NDV: 5 Nulls: 0 Density: 4.4980e-005 Min: 1 Max: 10000
Histogram: Freq #Bkts: 5 UncompBkts: 11116 EndPtVals: 5
Table: TAB_01_03 Alias: T
Card: Original: 11116 Rounded: 10001 Computed: 10000.50 Non Adjusted: 10000.50
Access Path: TableScan
Cost: 6.44 Resp: 6.44 Degree: 0
Cost_io: 6.00 Cost_cpu: 2587949
Resp_io: 6.00 Resp_cpu: 2587949
Access Path: index (index (FFS))
Index: IDX_B
resc_io: 7.00 resc_cpu: 2046392
ix_sel: 0.0000e+000 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 7.35 Resp: 7.35 Degree: 1
Cost_io: 7.00 Cost_cpu: 2046392
Resp_io: 7.00 Resp_cpu: 2046392
Access Path: index (AllEqRange)
Index: IDX_B
resc_io: 20.00 resc_cpu: 2143479
ix_sel: 0.89965 ix_sel_with_filters: 0.89965
Cost: 20.37 Resp: 20.37 Degree: 1
Best:: AccessPath: TableScan
Cost: 6.44 Degree: 1 Resp: 6.44 Card: 10000.50 Bytes: 0
Current SQL statement for this session:
select count(1) from tab_01_03 t where b=:x
============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL | TAB_01_03| 10K | 29K | 6 | 00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("B"=:X)
------------------------------------------------------------------------------
发现我们执行了两次查询,但ORACLE只生成了一次执行计划,而且执行计划的选择以第一个绑定值为准,
后续的调用都共享一个执行计划。如果应用对不同的值希望有不同的执行计划,则不合适使用绑定变量,
或者说在数据分布不均匀的列中,不宜使用绑定变量。
因此结论是:对于倾斜的列,使用柱状图是常用的优化方案 不过最好不要使用绑定变量
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21993926/viewspace-667150/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21993926/viewspace-667150/