CBO基础概念

2023-08-13 22:45:52

CBO基础概念

CBO：评估 I/O,CPU,网络（DBLINK）等消耗的资源成本得出

一、cardinality

cardinality：集合中包含的记录数。实际CBO评估目标SQL执行具体步骤的记录数，cardinality和成本是相关的，cardinality越大，执行步骤中的成本就越大

二、Selectivity

Selectivity ：谓词的过滤条件返回的结果的行数占未加谓词过滤条件的行数

公式：=

范围0-1，值越小，说明选择性越好返回的cardinality 越小；值越大，选择性越差，返回的cardinality 越大。

未加任何谓词条件结果集： original cardinality，增加谓词条件为：computed cardinality。

computed cardinality=original cardinality*selectivity

在列上没有NULL或者直方图的情况下，selectivity取值

SELECTIVITY=

NUM_DISTINCT ：表示为列中不同值的个数

三、transitivity（可传递性）

CBO会对现有的sql语句做等价转换，谓词条件、字段连接

优化器基础概念

一、优化类型：RBO和CBO

（一）、RBO

RBO基于规则的优化器access paths优先级:

RBO Path 1: Single Row by Rowid
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan

注意在不违反如上优先级的前提下，若有2个优化级一样的索引可用，则RBO会选择晚建的那个索引，解决方法是重建你想要让RBO使用的那个索引，或者使用CBO……..

（二）、optimizer_mode :优化器的模式

1、RULE：使用rbo的规则

2、CHOOSE:9i中默认值，SQL语句全部的对象中无统计信息，使用RBO，其他使用CBO

3、FIRST_ROWS_n（n=1,10,100,1000）:CBO会把哪些能够最快响应速度返回的成本设置为很小的值

4、ALL_ROWS：oracle10g 以后的默认值，会使用CBO来解析SQL，计算各个执行路径中的成本值最佳的吞吐量（最小的I/O和CPU资源）

二、结果集

执行计划中row，CBO评估的cardinality

三、访问数据的方法

（一）、访问表的方法：全表扫描和rowid访问

全表扫描：从高水位线下全部数据块读取，DELETE不会下降高水位线

ROWID访问:通过物理存储地址直接访问，rowid是个伪列，但是他能快速的定位的数据文件、块、行

scott@GULL> select empno,rowid ,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid)||'_'||dbms_rowid.rowid_row_number(rowid) location_row from emp;

     EMPNO ROWID              LOCATION_ROW

---------- ------------------ ------------------------------

      7369 AAASZHAAEAAAACXAAA 4_151_0

      7499 AAASZHAAEAAAACXAAB 4_151_1

      7521 AAASZHAAEAAAACXAAC 4_151_2

      7566 AAASZHAAEAAAACXAAD 4_151_3

      7654 AAASZHAAEAAAACXAAE 4_151_4

      7698 AAASZHAAEAAAACXAAF 4_151_5

      7782 AAASZHAAEAAAACXAAG 4_151_6

      7788 AAASZHAAEAAAACXAAH 4_151_7

      7839 AAASZHAAEAAAACXAAI 4_151_8

      7844 AAASZHAAEAAAACXAAJ 4_151_9

      7876 AAASZHAAEAAAACXAAK 4_151_10

      7900 AAASZHAAEAAAACXAAL 4_151_11

      7902 AAASZHAAEAAAACXAAM 4_151_12

      7934 AAASZHAAEAAAACXAAN 4_151_13

（二）、索引访问

索引访问的成本：一部分时访问相关的B树索引的成本，另一个成本是回表的成本（根据索引中的rowid）

1、索引唯一性扫描（index unique scan）：建立是唯一索引，能建立唯一索引的一定要建立唯一索引

2、索引范围扫描（index range scan）：谓词条件中>、<等

3、索引全扫描（index full scan）：扫描目标索引中所有的块的所有索引行。从最左的叶子节点读到底，因为叶子是一个双向链表

索引全扫描的执行结果是有有序的，并且是按照该索引的索引键值列来排序，这也意味走索引全扫描能够既达到排序的效果，又同时避免了对该索引的索引键值列的真正的排序操作。
索引全扫描时有序的就决定了不能够并行执行，索引全扫描时单块读
oracle中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL

4、索引快速全扫描（INDEX FAST FULL SCAN）

索引全扫描类似，读取所有叶子块的索引行

与全索引扫描不同点

索引快速全扫描只适用于CBO
索引快速全扫描可以使用多块读，也可以并行执行
索引快速全扫描的执行结果不一定是有序的

5、索引跳跃式扫描（INDEX SKIP SCAN）

适用复合B数索引，谓词中的过滤条件不是以索引前导列。

只是对前导列做distinct：如create index ind_1 ON emp(job,empno)

Select * from emp where empno=7499

如果job有两个CLERK,SALESMAN

等同的语句

Select * from emp where job='CLERK' AND empno=7499

UNION ALL

Select * from emp where job='SALESMAN' AND empno=7499

oracle中的索引跳跃式扫描仅仅适用那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减

四、表连接

（一）、表连接顺序

不管目标SQL中有多少个表做表连接，ORACLE在实际执行都是先做两两做表连接，在依次执行这样的两两表连接过程，直到目标SQL中所有表都已经连接完毕。

表连接很重要的是驱动表（outer table）和被驱动表（inner table）。

（二）、表连接方法

嵌套循环连接（nested loop）

1、如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性很好的非唯一性索引），那么此时使用嵌套循环连接的执行效率非常高。

2、大表也可以作为驱动表，关键看大表的谓词条件是否可以把驱动表的结果集的数据量下降下来

哈希连接（hash join）

1、哈希连接不一定会排序，或者说大多数情况下都不需要排序

2、哈希连接的驱动表所对应的连接列的可选择性应尽可能好，因为这个可选择性会影响对应hash bucket中的记录数，而hash bucket中的记录数又会直接影响从该hash bucket中查询匹配记录的记录。如果一个hash bucket里所包含的记录数过多，则可能或严重降低所对应哈希连接的执行效率，此时典型的表现就是该哈希连接执行了很多时间都没有结束，数据库所在数据库服务器上的CPU占用率很高，但目标SQL所消耗的逻辑读却很低，因为此时大部分时间都耗费在了遍历上述Hash Bucket里的所有记录上，而遍历Hash Bucket里的记录这个动作发生在PGA的工作区里，所以不耗费逻辑读。

3、哈希连接只适用于CBO，它也只能用于等值连接条件（即使是哈希反连接，Oracle实际上也是讲其转换成了等价的等值连接）。

4、哈希连接很适合小表和大表之间做连接且连接结果集的记录数较多的情形，特别是在小表的连接列的可选择性非常好的情况下，这时候哈希连接的执行时间就可以近似看作和全表扫描那个大表所耗费的时间相当。

5、当两个表做哈希连接时，如果在施加目标SQL中指定的谓词条件（如果有的话）后得到的数据量较小的那个结果集所对应的hash table、能够完全容纳在内存中（PGA的工作区），则此时的哈希连接的执行效率会非常高

排序合并连接（merge jion）

1、通常情况下，排序合并连接的执行效率会远不如哈希连接，但前者的使用范围更广，因为哈希连接通常只能用于等值连接条件，而排序合并连接还能用于其他连接条件（<,<=,>,>=）

2、通常情况下，排序合并连接并不适合OLTP类型的系统，器本质原因是因为对于OLTP类型的系统而言，排序很昂贵的操作，当然，如果能避免排序操作，那么即使是OLTP类型的系统，也还是可以使用排序合并连接的。

3、排序合并连接并不存在驱动表的概念

笛卡尔积连接（Cartesian join）

1、无任何连接条件，在执行计划中有cartesian，通常来说，有笛卡尔积，sql语句是有问题的

（三）、表连接的类型

1、内连接（Inner JOIN）

--使用=

select * from emp a , dept b  where a.deptno=b.deptno

--join

select * from emp a join dept b on a.deptno=b.deptno

2、外连接（outer join）

左连接--使用（+）

select * from emp a,dept b where a.deptno=b.deptno(+)

--使用left join

select * from emp a left outer join dept b on a.deptno=b.deptno

右连接

--使用（+）

select * from emp a,dept b where a.deptno(+)=b.deptno

--使用left join

select * from emp a right outer join dept b on a.deptno=b.deptno

外连接的驱动表以（+）对面的表，如以上左连接，驱动表为a，右连接的驱动表为b

3、反连接（anti join）

连接中not in、not exists 、<>

sql A：select * from emp a WHERE A.DEPTNO NOT  IN (SELECT DEPTNO FROM dept b where a.deptno=b.deptno)

sql B：select * from emp a WHERE NOT EXISTS (SELECT 1 FROM dept b where a.deptno=b.deptno)

sql C：select * from emp a WHERE A.DEPTNO <> all (SELECT DEPTNO FROM dept b where a.deptno=b.deptno)

4、半连接（semi join）

连接中用in，exists，any

select * from emp a WHERE A.DEPTNO   IN (SELECT DEPTNO FROM dept b where a.deptno=b.deptno)

select * from emp a WHERE  EXISTS (SELECT 1 FROM dept b where a.deptno=b.deptno)

select * from emp a WHERE A.DEPTNO =any(SELECT DEPTNO FROM dept b where a.deptno=b.deptno)

5、星形连接

在OLTP基本不会用到，在OLAP中用到

参考：崔华《基于ORACLE的SQL优化》

码农公寓