待执行的SQL语句进入数据库系统后都会进入查询优化器进行查询转换.

在Oracle 10g之前, Oracle会默认使用RBO规则

数据库会将查询条件分成15个等级, 通过判断条件(动作)的级别高低来决定那个谓词被优先执行

例如 基于rowid的查询级别是最高的为1, 全表扫描的执行级别最低为15

基于RBO优化器的一个明显缺点在于, 依靠硬编码来确定的规则很难被动态改变, 编写SQL语句的时候需要十分注意

这里如果你跟我一样使用的是10g之后的版本,我们需要使用下面的命令将优化器暂时改成RBO ( 只对当前session有效,不用再改回来)

alter session set optimizer_mode=’RULE’;

然后我执行一个执行计划

EXPLAIN PLAN FOR select * from INO_OUTAGES.OUTAGE where SUBCATEGORY=’NBN Outage’;
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

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

Oracle 自7开始引入了CBO.

Oracle会根据cpu,内存以及I/O的开销来进行SQL优化

可选择率是CBO的一个核心概念

1. 可选择率

可选择率 = 指定谓词返回的结果集记录数/未使用谓词返回的结果集记录数

取值范围是 0到1 , 值越小 表明 可选择性 越好

2. 可传递性

2.1 简单谓词传递

2.2 连接谓词传递

2.3 外链接谓词传递

实际上就是 a1=a2 and a1=10 等价于 a1=10 and a2=10

为关系型数据库添加索引是一种最基础的优化方式

基础虽基础, 但其实并不简单, 记得几年前去一家国内大公司的云计算部门面试

提到索引的时候面试官说”加索引都是最基础的”, 我不赞同这种说法, 基础但并不简单

考虑下面这种情况

1 索引的原理是什么

2 我应该在哪些字段上加索引

3 我应该对每个字段使用独立的索引还是应该使用联合索引

4 加了索引之后的坏处是什么

5 哪些情况下没有索引反而要比有索引更快

下面我们进一步讨论这些问题

1 索引的原理是什么

索引的工作方式其实和view差不多, 每个索引都会构成一个单独的索引表, 这里我不想讨论B tree之类的问题

当你为字段A创建了一个索引之后,数据库会生成一个独立的索引表

2 我应该在哪些字段上加索引

根据问题1, 实际上如果你直接使用select A from Table 时,数据并不需要对主表进行查询,而是直接查询索引表,这样可以避免全表扫描

下面这个表只有一个聚簇索引(主键索引,一般来说数据是按照聚簇索引来物理排序的,磁盘上的存储位置是连续的)

不过我的表里数据只有100条左右,所以看不出耗时差别

EXPLAIN PLAN FOR  select * from INO_OUTAGES.OUTAGE;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));EXPLAIN PLAN FOR  select ID from INO_OUTAGES.OUTAGE;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

检索所有列时数据库会进行全表扫描

只检索索引列时, 只扫描索引表

如果是既查询索引列,又有额外的非索引列, 实际上也会进行全表扫描

EXPLAIN PLAN FOR  select ID,DURATION,DESCRIPTION from INO_OUTAGES.OUTAGE;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

接下来看看谓词的情况

EXPLAIN PLAN FOR  select * from INO_OUTAGES.OUTAGE where id=2146;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

可以看到当谓词是索引列时, 没有进行全表扫描 ( 不过如果你使用id<2146这种范围查询,oracle还是会使用全表扫描的方式)

还有order by 之类的谓词需要注意

根据上面的测试结果, 只有当 select用到的查询列, where使用的谓词, order by的谓词全部均为索引列时, 数据库才会使用索引表进行查询

如果一个查询需要经常被调用(此时可以忽略索引带来的更新和空间消耗), 最好将select,where,order by等使用的列全部添加索引

但是我们还会有下面的疑问

3 我应该对每个字段使用独立的索引还是应该使用联合索引

当KEY, AREA_KEY, MANDATOR_KEY为联合索引,你尝试select这三列时,会得到一个INDEX FAST FULL SCAN

区别在于

索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序, INDEX FAST FULL SCAN的性能要大大优于INDEX FULL SCAN

EXPLAIN PLAN FOR  select KEY, AREA_KEY, MANDATOR_KEY from INO_ONCONFIG.LOOKUP_OPTIONS;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

谓词方面复杂一些

第一个例子, 如果不使用联合索引中全部的3个列,会得到一个全表扫描

EXPLAIN PLAN FOR  select * from INO_ONCONFIG.LOOKUP_OPTIONS where AREA_KEY=’IFMS_SYMPTOM_CODE’ and MANDATOR_KEY=’PLATFORM’;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

第二个例子, 如果使用全部的三个索引列做谓词,会得到一个索引表扫描

EXPLAIN PLAN FOR  select * from INO_ONCONFIG.LOOKUP_OPTIONS where KEY >’1′ and AREA_KEY=’INVOICE_INTERVALS’ and MANDATOR_KEY=’PLATFORM’;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

上面这个例子如果连select列都为索引列的话你甚至可以只使用索引表,不用查询主表

EXPLAIN PLAN FOR  select AREA_KEY,MANDATOR_KEY from INO_ONCONFIG.LOOKUP_OPTIONS where KEY >’1′ and AREA_KEY=’INVOICE_INTERVALS’ and MANDATOR_KEY=’PLATFORM’;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

4 加了索引之后的坏处是什么

增加了存储空间和插入及修改数据时造成的索引维护

5 哪些情况下没有索引反而要比有索引更快

这里有一种极端情况就是数据库所使用的硬盘随机读操作比较慢,而连续读比较快

假设谓词是 id in (1,10,13,45,234,676,233,234,67,89), 整个表的容量是100M

对于现代硬盘读取100M可能只需要1秒, 而使用索引时,由于先扫描索引表,然后通过索引产生10次随机硬盘读取, 如果每次平均随机读时间超过了100毫秒, 则使用索引反而会消耗更多时间

需要打开计时器的话可以参考

set timing on;

在开始之前需要强调的是

任何并发操作都是以硬件换速度, 如果没有足够的硬件不要轻易使用并发,另外如果是大访问量的网站系统请慎用,数据库并发查询一般还是用在夜晚的大批量batch批处理上

1. 直接改表

ALTER TABLE INO_ONCONFIG.LOOKUP_OPTIONS PARALLEL 4;

要关闭的话使用直接NOPARALLEL参数或者直接设置线程数为1好像也可以

ALTER TABLE INO_ONCONFIG.LOOKUP_OPTIONS NOPARALLEL;

ALTER TABLE INO_ONCONFIG.LOOKUP_OPTIONS PARALLEL 1;

2. 使用hint

需要注意的是如果需要指定schema名的话hint的时候需要把schema名去掉

select /*+ parallel(LOOKUP_OPTIONS 8) */ count(*) from INO_ONCONFIG.LOOKUP_OPTIONS;

下面我使用一个关联查询的sql来看看并行与非并行的区别

(我这里并发的消耗要比直接全表扫描join更大)

EXPLAIN PLAN FOR  select   * from INO_OUTAGES.OUTAGE o   join INO_OUTAGES.BASE_LOOKUP b on o.id=b.OUTAGE_ID; SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));EXPLAIN PLAN FOR  select /*+ parallel(o 4) */ /*+ parallel(b 4) */  * from INO_OUTAGES.OUTAGE o   join INO_OUTAGES.BASE_LOOKUP b on o.id=b.OUTAGE_ID; SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

PX 进程： Parallel Execution Slaves

Parallel to Serial（P->S）: 并发到串行

Parallel to Parallel（P->P）：并发到并发

Parallel Combined with parent(PCWP): 同一个从属进程执行的并行操作，同时父操作也是并行的。

Parallel Combined with Child(PCWC): 同一个从属进程执行的并行操作，子操作也是并行的。

Serial to Parallel（S->P）: 串行到并发

用户进程发起请求之后, oracle会使用一个QC进程(Query Coordinator)来协调这个用户进程与oracle进程之间的工作

它会决定这个请求的并发度,控制执行过程,汇总结果等 ( 上图中的PX COORDINATOR)

一般来说你只会有一个QC, 不过如果使用了类似UNIONALL这样的操作,就会出现多个QC

每个QC都包含一个树形结构, 这个树包含若干DFO节点(Data Flow Operator) , DFO是可以并行执行的单元, QC持有一到两组QSS(Query Slaves Set),每个QSS包含若干QS(Query Slaves),QSS会扮演生产者和消费者的角色,(每个QSS可能同为生产者,也可能同为消费者,也可能一个生产者一个消费者), 我们通常所说的并行度指的是一组QSS里并行的QS个数,所以如果oracle使用了两组QSS,那么实际运行的线程数会是并行度的两倍

TQ(table queue), 意为表队列。它是一个逻辑上的概念。在并行执行中，用TQ来实现query slave set 之间以及query slave set 和QC之间数据的传递和重新分布。可以简单认为就是存储并行执行过程中的中间结果的临时表

简单归纳一下

oracle进程

    -> 启动1个QC或者多个QC

          -> 每个QC = 一个DFO树

          -> 每个QC = 最多2个QSS (查看TQ列)

              -> 每个QSS = 若干QS

所以上图中

启动一个QC,也就是一个DFO树( 参考id 1 )

有两组QSS(TQ列的Q10,01和Q10,02) ( id 2,3,4,5 为Q10,02   id 9,10,11,12为Q10,01)

Q10,01为生产者 (它在执行TABLE ACCESS FULL和 PX BLOCK ITERATOR id 11,12) , Q10,01 先以并行的方式对OUTAGE table进行了全表扫描

同时, Q10,02在一开始也作为生产者扫描了BASE_LOOKUP 表, 然后转为消费者在id 03 处对两个结果集进行了join, 然后输出了合并后的结果

为了观察的更细致一些使用union all关键字可以得到两个QC的结果

EXPLAIN PLAN FOR  select /*+ parallel(o 4) */ /*+ parallel(b 4) */  o.id,b.OUTAGE_ID,o.OUTAGE_TYPE from INO_OUTAGES.OUTAGE o   left join INO_OUTAGES.BASE_LOOKUP b on o.id=b.OUTAGE_ID union all select /*+ parallel(o 4) */ /*+ parallel(h 4) */  h.id,h.OUTAGE_ID,o.OUTAGE_TYPE from INO_OUTAGES.OUTAGE o   left join INO_OUTAGES.HISTORY h on o.id=h.OUTAGE_ID ; SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));