oracle 优化器

 

oracle的优化器

制定sql的执行计划，保证sql语句的执行效率最高。

oracle的优化器有两种
RBO :rule based optimizer,基于规则的优化器，【优化器中嵌入规则】oracle10g以前
CBO :cost base optimizer，基于成本的优化器

hint

create table as select 1 id,object_name from dba_objects;
select count(*) from t;
update t set id=99 where rownum=1;

commit;

数据分布不均匀。
创建索引
create index name on table_name(id)
create index my_index_id ont t(id);
select id,count(*) from t group by id;
发生了严重的数据倾斜

select * from t where id=99;
select * from t where id=1;
select /*+ rule */ from t where id=1;

CBO 思路：
1.获取所有执行计划的相关信息，然后对这些信息进行计算分析，
得到一个代价最小的执行计划作为最终的执行计划。

摘录oracle性能诊断艺术
当获取和质疑SQL语句的性能时，首先要做的就是得到执行计划。
1.获取
2.解释
3.判定效率

获取执行计划
1.执行SQL语句EXPLAIN PLAN，然后查询结果输出表
  SQL语句EXPLAIN PLAN 的目的是以一条SQL语句作为输入，得到这条语句的执行计划和
  和相关的信息，并将他们作为输出存储到计划表（plan table）中。


  explain plan for ...
  select *from table(dbms_xplan.display)

2.查询一张动态性能视图，显示缓存在库缓存中的执行计划
3.查询AWR或查询Statspack表，显示存储在资料库中的执行计划
4.启动执行计划的跟踪功能

动态性能视图
有4个动态性能视图展示库缓存中当前游标的信息

详解dbms_xplan包
这个包可以显示存储在三个不同地方的执行计划：计划表、库缓存、AWR中。
1.输出
讲述dbms_xplain包里面的函数所产生输出中包含的信息。
  a、识别父游标，只有当调用display_cursor、display_awr的时候
有此消息
  b、属于这个sql_id的子游标序号，可以标识出子游标，调用display_cursor会有次消息
  c、sql语句的内容，调用display_cursor和display_awr时会有此消息


执行计划
dbms_xplain.display
显示计划表中的内容
dbms_xplain.display_cursor
显示库缓存中的执行计划


create table t1(x int,y int);

索引
rowid

oracle读取数据块
从表中读取每一行 全表扫描
通过rowid一次读取一行

当访问大型表的少数的数据行时，走索引的性能是很高的。
反之，不成立。

索引改进性能取决于两个因素
1.索引的选择性
2.数据在数据块上的分布情况

如果选择性很高，但是相关的行在表中的存储位置并不连续，不相互靠近，
则会减少索引带来的好处。

表是段，索引也是段，索引是真真切切在物理上存在的。
行迁移

select table_name,index_name,index_type from dba_indexes where table_name='EMP';
select table_name,index_name,column_name,column_position from dba_ind_columns
where table_name='EMP';

索引的类型

根据索引的类型和where条件的限制不同，有4种索引扫描类型。
索引唯一扫描 index unique scan
  通过唯一键 主键 oracle 返回 一个数据行
索引范围扫描 index range scan
  在唯一键 使用了>< >= <= between and
  在不唯一的索引列进行查询
索引全扫描   index full scan
索引快速扫描 index fast full scan
扫描索引块中的所有数据块，和全扫描一样。不进行数据排序

索引的限制
1.where 条件子句中使用到了索引列
2.没有where，也有可能使用到索引。对索引列进行聚合函数调用 min max count
oracle阻止使用索引。本来列上有索引，但是由于执行了下面的操作，导致不走索引
1.使用不等于运算符
 != <>
2.使用 is null或者is not null
3.使用函数
  如果不使用基于函数的索引，如果使用到一些函数，就会使得优化器忽略掉索引。
  trunk，substr，to_date,instr,to_char
4.比较不匹配的数据类型
5.使用like 不走索引

索引的选择性
选择性越高，索引返回的数量越少

select table_name,index_name,num_rows from user_indexs

集群引子 cluster factor
通过一个索引访问一个表，需要访问的表的数据块的数量。
计算方法：
1.扫描一个索引
2.比较某一行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么集群引子+1
3.整个索引扫描完毕之后，就得到了该索引的集群引子。

集群引子最大 = 表的行数
集群引子最小 = 表所占的数据块的个数(倾向走索引)

select segment_name,blocks from dba_segments where segment_name='EMP';
grant all on dba_segments to scott;

select blocks from dba_extents where owner='SCOTT' and tablespace_name='USER';
select dbms_rowid.rowid_block_number(rowid) bk from emp;

二元高度 binary height
索引查找分为两个过程
1 根据树进行定位，找出rowid（索引查找）
2 根据rowid找出表中的数据行（表数据查找）

select * from t where id=3;

select blevel,index_name from dba_indexes wehre index_name=''

直方图 histograms all_tab_histograms 

用来记录数据的分布
功能：帮助CBO在表中出现严重的数据倾斜做出更好的规划

oracle 做直方图分析的时候，会将要分析的列上的数据分成很多数量相同的部分，
每一个部分称作一个bucket

create table t select 1 id,object_name from dba_objects;

update t set id = 100 where rownum = 1;
select id,count(*) from t group by id;

create index index_t_id on t(id)

select table_name,column_name,endpoint_number,endpoint_value from user_tab_histograms where table_name='T'


exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
--删除
exec dbms_stats.delete_column_stats(user,'t','id')

gather_table_stats

动态采样dynamic sampling
create table t2 as select owner,object_type from all_objects

select count(*) from t2;
--不采用动态采样
select /*+ dynamic_sampling(t20 0)*/count(*) from t2;

索引类型
B树索引 （平衡树）
位图索引
HASH索引
iot
反转索引
基于函数的索引

B树索引
默认创建的就是B树索引（支持单例索引、组合索引）
 create table myemp2(id,int, name varchar2(20),email varchar2(20))

位图索引 bitmap索引
适合于dss（决策系统）数据库仓库 OLAP【分析】


select  * from t20;

位图索引的优点：
存储密码大，节省空间，可以进行或运算

select * from t where a=red or b=blue
最大缺点：
并发性太差

create bitmap index name on table(col)

iot
create table bbb(id int, name varchar2(20),job varchar2(20))
insert into bbb select e.empno,e.ename,e.job from scott.emp e
commit;

create index id_bbb on bbb(id);

基于函数的索引只能针对一种函数，对其他的函数不起作用
create index func_name on bbb(upper(name));

索引和表一样的，也是segment，也是占用磁盘空间，
索引是有序排列的结构

select * from ... where ....