《基于成本的Oracle优化法则》章节试读

《基于成本的Oracle优化法则》的笔记-第93页 - cluster_factor

集群因子--cluster_factor
1 cluster_factor是一个判断表上的数据分布是不是和索引一样有序的一个值。
它的取值范围在表的block的个数和表的行数之间，越接近block的个数说明表的数据分布越有序（和索引一样有序），越是接近行数越说明表上的数据分布是混乱的，可以这样理解，当我们按照索引上的顺序访问表的时候，每当我们需要的数据不在当前block上的时候，我们就要“跳到”其他block上进行访问。如果表上的数据排列和索引是完全一样的话，我们跳的次数等于表的Block的个数。如果是另一个极端，表的数据分布极其的混乱，我们访问每一行数据都要跳一次，那我们最后跳的次数就等于行数，跳的次数就是CLUSTER_FACTOR
2 CLUSTER_FACTOR对Oracle执行计划会产生重要影响。这个值越高，说明索引的使用效率将会越差。这个值会存在于一个区间内，区间的最小值为表占用的数据块数，最大值为表拥有的数据行数
SQL> CREATE TABLE yangtab( ID NUMBER(32), NAME VARCHAR2(80)) TABLEspace USERS pctfree 0 INITRANS 1 maxtrans 255 STORAGE(INITIAL 1M next 1M minextents 1 maxextents UNLIMITED );
Table created.
SQL> begin
for i in 1..80000 loop
insert into yangtab(id,name)
values(i,dbms_random.string('a',80));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
创建yangtab2无序插入
SQL> CREATE TABLE yangtab2(ID NUMBER(32), NAME VARCHAR2(80) )TABLESPACE USERS pctfree 0 initrans 1 maxtrans 255 STORAGE(INITIAL 1M NEXT 1M minextents 1 maxextents UNLIMITED );
Table created.
SQL> insert into yangtab2 nologging SELECT * FROM yangtab order by dbms_random.random;
80000 rows created.
SQL> COMMIT;
Commit complete.
SQL> alter TABLE yangtab add constraint pk_yangtab primary key(id);
Table altered.
SQL> alter TABLE yangtab2 add constraint pk_yangtab2 primary key(id);
Table altered.
SQL> col segment_name for a25
SQL> SELECT t.SEGMENT_NAME, t.SEGMENT_TYPE, t.BLOCKS FROM user_segments t WHERE t.SEGMENT_NAME like '%yangtab%';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
yangtab TABLE 1024
yangtab2 TABLE 1024
PK_yangtab INDEX 256
PK_yangtab2 INDEX 256
分别统计数据CLUSTER_FACTOR
SQL>begin
dbms_stats.gather_TABLE_stats(ownname => user,tabname => 'yangtab',cascade => true);
end;
4 /
PL/SQL procedure successfully completed.
SQL>begin
dbms_stats.gather_TABLE_stats(ownname => user,tabname => 'yangtab2',cascade => true);
end;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT i.index_name, i.CLUSTERING_FACTOR FROM dba_indexes i WHERE i.index_name like 'PK_yangtab%';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
PK_yangtab 908 靠，这个接近行块数1024
PK_yangtab2 79919 这个接近于行数80000
这说明，当数据行的存储顺序和索引顺序越接近，CLUSTER_FACTOR越小，越有利于使用索引。
下面来测试下对执行计划的影响
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
SQL> set linesize 180
SQL> SELECT * FROM yangtab WHERE id > 2000 and id < 8000;
5999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1785914651
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6006 | 498K| 83 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| yangtab | 6006 | 498K| 83 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_yangtab | 6006 | | 14 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">2000 AND "ID"<8000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
877 consistent gets
0 physical reads
0 redo size
590743 bytes sent via SQL*Net to client
4881 bytes received via SQL*Net from client
401 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5999 rows processed
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
SQL> SELECT * FROM yangtab2 WHERE id > 2000 and id < 8000;
5999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4079432394
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6000 | 498K| 222 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| yangtab2 | 6000 | 498K| 222 (1)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<8000 AND "ID">2000)
Statistics
----------------------------------------------------------
150 recursive calls
0 db block gets
1378 consistent gets
969 physical reads
0 redo size
590743 bytes sent via SQL*Net to client
4881 bytes received via SQL*Net from client
401 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5999 rows processed
从上面看出来CLUSTER_FACTOR对执行计划的巨大影响，前者走索引扫描，COST只有83。而后者全表扫描，COST为222，接近于前者的四倍左右
前者的一致性读与物理读也远远低于后者。
索引中的列 顺序
3 基于区间的谓词如何降低索引后面谓词的作用。当计算索引有效选择率的时候，在基于区间的谓词之后的所有基于列的谓词都将被忽略，但是计算表的有效选择率的时候还会使用它们这样将导致oracle 计算得出的索引成本很高。
4 为了将选择率最低(重复最多)的谓词放到最前面以提高索引的压缩比.
5 重新排列这些列的顺序以使得某些常用的查询不使排序的前提下就能够使用order by.

《基于成本的Oracle优化法则》的笔记-第161页 - 直方图--选择率/密度

Density的出现就是为了分析高频率出现的值的影响。没有histograms信息的时候，Density永远等于1/NUM_DISTINCT，但是当我们统计了histograms之后，Density就会发生改变。
Density的计算方法如下：
如果想计算一张表的DENSITY，首先运行下列语句收集信息：select min(minbkt),
maxbkt,
substrb(dump(min(val), 16, 0, 32), 1, 120) minval,
substrb(dump(max(val), 16, 0, 32), 1, 120) maxval,
sum(rep) sumrep,
sum(repsq) sumrepsq,
max(rep) maxrep,
count(*) bktndv,
sum(case
when rep = 1 then
1
else
0
end) unqrep
from (select val,
min(bkt) minbkt,
max(bkt) maxbkt,
count(val) rep,
count(val) * count(val) repsq
from (select
/*+
cursor_sharing_exact
use_weak_name_resl -- 10g hint
dynamic_sampling(0)
no_monitoring
*/
目标列 val, ntile(10) over(order by目标列) bkt
from 目标表名 t
where目标列is not null)
group by val)
group by maxbkt
order by maxbkt;通过上面收集的信息计算densitiy：
1 基本公式：
(Sum(sumrepsq)-sum(maxrep(i)*maxrep(i))/(sum(sumrep)*(sum(sumrep)-sum(maxrep(i))))
注：i表示只有某些特定的行从查询结果中选出来计算maxrep值，这些行必须满足条件：
maxbkt>min(minbkt)+1
或者
min(val)=max(val)
2 简化公式（在没有高频值出现的情况下使用）：
Sum(sumrepsq)/(sum(sumrep)*sum(sumrep))

《基于成本的Oracle优化法则》的笔记-第77页 - 简单的B 树访问

1
CPU costing model:
Cost=(
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles/cupspeed
)/sreadtim
成本是语句的预计执行时间的总和，以‘单块读取时间单元’的形式来表示。
2 选择率实际上就是优化器基于相关谓词所期望返回数据行数量的分数
索引成本计算的基本公式
cost=
blevel +
ceilling(leaf_blocks* effective index selectivity) +
ceilling(clustering_factor * effective table selectivity)
3使用Btree 索引的典型成本包括3个部分--基于level 的索引深度，将要访问的索引也的数目(leaf_blocks 参数)和访问表块的数目(基于clustering_factor参数)

《基于成本的Oracle优化法则》的笔记-第9页

Oracle基于成本优化的4中策略：
1.传统的：仅计算读取请求数目 7
2.系统统计（1）：读取请求的大小和时间 8i
3.系统统计（2）：CPU成本，读取请求的大小和时间 9i，10g
4.系统统计（3）：缓存，CPU成本及读取请求大小和时间
*传统的成本计算方法，表扫描成本：
高水位标记之后的数据块/adjusted dbf_mbrc
adjusted dbf_mbrc：调整多块成本

《基于成本的Oracle优化法则》的笔记-第40页 - 单表的选择率

单表的选择率
优化器并没有智能--只是一个软件！
1 对含有空值的字段
基本选择率=1/num_distinct
num_nulls=120
num_rows=1200
选择率=基本选择率*(num_rows-num_nulls)/num_rows=1/12*((1200-120)/1200)=0.075
调整后的基数=1200*0.075=90
2 对于in-list
比如：
select count(1) from aud where month_no in (6,7,8);
oracle 会在解析sql的时候将in list转换为or 的形式 ：month_no=6 or month_no=7 or month_no=8
选择率类似联合概率：
P(A OR B ORC)=P(A)+ P(B)+P(C)-P(A AND B)-P(A AND C)-P(C AND B)+P(A AND B AND C)
3/12-3/144+1/1728=0.22975
基数=1200*0.22975=275.69
3 对于区间谓词
month_no > 8
选择率=(high_value-limit)/(high_value-low_value)=(12-8)/(12-1)=4/11
month_no >=8
选择率=(high_value-limit)/(high_value-low_value)+1/num_distinct =(12-8)/(12-1)+1/12=4/11+1/12
month_no <8
选择率=(limit-low_value)/(high_value-low_value)=(8-1)/(12-1)=7/11
month_no <=8
选择率=(limit-low_value)/(high_value-low_value)+1/num_distinct=(8-1)/(12-1)+1/12=7/11+1/12
month_no between 6 and 9
month_no>=6 and month_no<=9
选择率=(hight_limit-low_limit)/(high_value-low_value)+1/num_distinct+1/num_distinct=(9-6)/(12-1)+1/12+1/12
month_no>=6 and month_no<9
选择率 =(9-6)/(12-1)+1/12
month_no>6 and month_no<=9
选择率 =(9-6)/(12-1)+1/12
month_no>6 and month_no<9
选择率 =(9-6)/(12-1)
4 双谓词
month_no>=8 or month_no<8
P(A OR B)=P(A)+ P(B)-P(A AND B)
P(A AND B)=P(A)* P(B)
NOT P(A)=1-P(A)

oracle 在解析执行计划的时候，将各个谓词视为独立的谓词，当一个表使用多个谓词时，必须确保各个谓词之间没有必然联系，否则cbo 将得不到准确的执行计划

《基于成本的Oracle优化法则》的笔记-第4页

CPU costing model:
Cost=(
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles/cupspeed
)/sreadtim
成本是语句的预计执行时间的总和，以‘单块读取时间单元’的形式来表示。

《基于成本的Oracle优化法则》的笔记-第148页 - 直方图--绑定变量与 cursor_sharing

1 绑定变量窥视
绑定变量窥视是9i以后的一个新特性。它使CBO优化器在计算访问代价时，将绑定变量传入的值考虑进去，从而计算出更合理的成本（否则，将会计算平均成本）
绑定变量窥视只发生在硬解析，执行计划是根据第一次传递进来的实际参数来确定的，以后在执行该语句时都采用这个查询计划，而不再考虑以后绑定变量的具体值。这样会存在绑定变量选择错误执行计划的可能。
2 cursor_sharing
cursor_sharing参数有三个值可选，exact、similar、force。
a 当值为exact时为默认值，也是oracle的默认处理方式。当一个statement parse的时候，首先到shared pool区查看是否有exact statement存在（是否在shared pool中有和当前要解析的statement完全一样的语句存在），如果不存在，就执行hard parse
b 如果该参数设置为similar，那么如果在shared pool中无法找到exact statement的存在的时候，就会在shared pool进行一次新的查找，就是查找和当前要解析的语句是否是similar statement的语句。
注意similar statement进行解释，similar statement就是除了value of some literal不同的语句，别的地方都相同的语句。比如下面：
select * from a where a=1;
select * from a where a=2;
当cursor_sharing设置为similar时，如果在shared pool中查找到这样的语句，就会做下一步的检查，看shared pool中缓存的这个语句的execution plan是否适合当前解析的语句，如果适合，就会使用shared pool的语句，而不去做hard parse。

c 如果cursor_sharing设置为force的时候，当在shared pool中发现了similar statement之后，就不会再去检查执行计划了，而直接使用在shared pool的这个语句了。
将cursor_sharing设置为force实际上是危险的。这会可能形成sub optimal的执行计划。比如对于一个范围查找的语句，比如
select * from a where a>10 and a<20这样类型的语句，缓存中的语句的执行计划可能对于正在解析的语句就是不适合的，不是最优的执行计划。
3 直方图与 cursor_sharing
若存在object_id的 histograms ,则每次是不同的 值 的时候都产生硬解析 ,若不存在 histograms ，则不产生硬解析 。换句话说，当表的字段被分析过存在histograms的时候，similar 的表现和exact一样，当表的字段没被分析不存在histograms的时候,similar的表现和force一样。这样避免了一味地如force一样转换成变量形式，因为有hostograms的情况下转换成变量之后就容易产生错误的执行计划，没有利用上统计信息。而exact呢，在没有hostograms的情况下也要分别产生硬解析，这样的话，由于执行计划不会受到数据分布的影响（因为没有统计信息）重新解析是没有实质意义的。而similar则综合了两者的优点。