CBO基础笔记

cursor_sharing 用于控制字面量替换
db_file_multiblock_read_count DFMRC用于在全表扫描和索引全扫描时计算成本
optimizer_index_caching 索引缓存率
optimizer_index_cost_adj 用于调整索引访问的成本
PGA_AGGREGATE_TARGET 用于控制hash和排序区的大小
optimizer_mode all_rows,first_rows_n,first_rows
star_transformation_enabled 是否允许星型转换
v$sql_cs_statistics 保存绑定变量的执行信息
低索引集群因子说明相近的锁银行集中少量列上

v$sql_plan,v$sql_plan_statistics和v$sql_plan_statistics_all可知plan
select plan_table_output from table(dbms_xplan.display());
select * from table(dbms_xplan.display('plan_table',null,'All'));
alter session set db_file_multiblock_read_count=16;

闪回查询前导列如何使用
object_id,object_value与对象表相关
全表扫描的成本计算公式：
1+高水位线下的块的数目/调整后的参数
单表选择率算法：
（numrows-numnull）/num_rows
alter session set "_optimizer_cost_model"=io;

begin
dbms_stats.gather_table_stats(user,'t1',estimate_percent=>null,method_opt=>'for all collumns size 120');
end;/

sql trace:
1 alter session set tracefile_identifier='fangyuan';
2 alter session set sql_trace=true;
do sth
3 alter session set sql_trace=false;
4 show parameter use_dump_dest
5 tkprof
or select spid from v$session s, v$precess p
where s.paddr=p.addr
and s.username=user
and module='SQL*PLUS';

10053 event:
alter session set event '10053 trace name context forever';
alter session set event '10053 trace name context off';

查询计划中的view_pushed predicate表示进行了谓词推进
优化器默认使用嵌套循环来处理anti_join,但是如果使用merge_aj,hash_aj,nl_aj的话，优化器能进行相应的转换。
优化器默认使用嵌套循环来处理semi_join,但是如果使用merge_sj,hash_sj,nl_sj的话，优化器能进行相应的转换。
但是在11g并且有统计信息的情况下，优化器不一定会使用NL来默认处理上述两种情况

内联视图
select p.pname ,c1_sum1,c2_sum2 from p,
(select id,sum(*) c1_sum1 from s1 group by id) s1,
(select id,sum(*) c2_sum2 from s2 group by id) s2
where p.id=s1.id and p.id=s2.id

标量子查询
select p.pname,
(select sum(p1) c1_sum1 from s1 where s1.id=p.id) c1_sum1,
(select sum(p2) c2_sum2 from s2 where s2.id=p.id) c2_sum2
from p

将子查询转换为内联视图是比较好的做法
提高CBO对于not in 的选择率精度，必须保证连接操作两端都不为NULL，负责可能出现错误结果

star提示可以用于进行星型连接
alter session set star_transformation_enabled=...
B树索引访问成本：
blevel+ceiling(leaf_blocks*effectvie index sel)+ceiling(clustering_factor+effectvie index sel)

连接操作选择率公式：
sel=(
(num_rows(t1)-num_nulls(t1,c1))/num_rows(t1)
*(num_rows(t2)-num_nulls(t2,c2))/num_rows(t2)
/greater(num_distinct(t1,c1),num_distinct(t2,c2))
)
基数公式：
card=
sel*filtered card(t1)*filtered card(t2)
当过滤谓词仅仅出现在一侧时，需要使用另一侧的distinct来替代最大值。
如果是多列连接，需要将多个连接条件的选择率相乘，如果在10g以后，可能会使用两个相同的条件相乘，并且选择较大的选择率
如果是范围连接，优化器使用类似与绑定变量的选择率处理
如果是不等值连接，选择率为1-等值连接的选择率

优化器在处理不等值连接且条件间为or时可能会有错误，如果加入no_expand提示就可以得到正确的结果，或者讲等值连接的条件分别写成查询最后union all.

在有重复史书的时候，计算成本总为1000k或者1，在有直方图的情况下，某些查询的成本会好一些，但是在数据不重叠时候，直方图会带来新的问题。
在建立直方图的时候，只有size远远大于列中不同的值的数量的时候，才可能使用频率直方图，否则都会使用高度均衡直方图。

如果在连接条件上有一个过滤条件，CBO可能会做闭包传递，导致过滤条件不可用以及忽略有过滤条件的连接条件，如果连接条件两边都有过滤条件，则有可能是成本和基数的差异都很大。

在进行多表连接时，需要将选择率相乘，而计算公式中的参数直接来自于语句中的谓词，在执行复杂查询的时候，连接条件非常重要

在估算code-业务表这种类型的连接查询时，如果讲很多的代码并入一张CODE表中，并且添加type列来进行区分，那么优化器将有可能进行错误的成本计算，即使建立了直方图也有这个问题。解决方法是对code表按type进行分区。

绑定变量的选择率为：density or 1/num_distinct
optimizer_index_caching设为75在OLTP系统中比较合理

嵌套循环的成本：
从第一个表取数据的成本+从第一个表得到结果的基数*对第二个表访问一次的成本
其实说白了就是嵌套循环的原理

hash_join的成本：
(探查遍数+1)*ceiling（大表数据块大小/调整IO后的单表值）+ceiling(小表表数据块大小/调整IO后的单表值)+hash_join前的成本和
其实就是hash_join的原理

10104事件用于查看hash_join的细节

dbms_lock.request(1,dbms_lock.xmode,release_on_commit=>true);
用于加排他锁，这时在其他会话中执行童谣的代码时就会等待，可以用这个方法来模拟并发。

begin
execute immediate 'purge recylebin';
end;

alter session set work_size_policy=mamual;
later session set hash_area_size=;

event 10032用于报告排序时系统内部相关活动的统计信息
event 10046 level 8用于记录等待状态
event 10033列出并发的IO细节
alter session flush shared_pool;
alter session flush buffer_cache;

一般来说，增加sort_area_size可以改善排序，但也可以增加对CPU的占用
在使用union,minus,intersect操作时，不要使用distinct关键字，否则计算card,cost有错误

10053事件的追踪报告通常包括：
绑定变量
参数设定
查询块
基础统计信息
完备性检查
一般执行计划：
在这里会衡量各个访问路径和顺序，但是在表的数量较多（4个表就有24种可能，4个表通常ORACLE只会估算13-16个路径）的情况下，ORACLE不会遍历所有的路径，这也是SQL需要优化的原因。