课程目标
完成本课程的学习后，您应该能够：
•优化器的作用
•优化器的类型
•优化器的优化步骤
•扫描的基本类型
•表连接的执行计划
•其他运算方式的执行计划
•如何看执行计划顺序
•如何获取执行计划
1.优化器概述
oracle中优化器(optimizer)是SQL分析和执行的优化工具，它负责制订SQL的执行计划，也就是负责保证SQL执行的效率最高。
优化器的类型：
基于规则的优化器(RBO,Rule-Based Optimizer)
基于成本的优化器(CBO,Cost-Based Optimizer)
1.1 RBO
基于规则的优化器诞生于早期关系型数据库，它的原理是基于一系列规则的优先顺序来分析出执行计划，以判断最优查询路径。
其中，排名越靠前，Oracle认为效率越高。例如：按索引访问的效率肯定高于全表扫描，多字段复合索引的效率高于单字段索引，等等。通俗地讲，RBO就是不关心被访问对象的实际数据分布情况、索引效率等，仅凭想象去决定应该如何去访问数据库。可见，RBO是一种非常粗放型的优化器。
RBO的优缺点:
缺点：
通过固定的规则来判断执行计划，容易制定出恶性执行计划。
不通过统计信息来判断，使得误差较大。
优点：
RBO的判断有规可循、有律可依，方便用户对优化器的选择进行正确的预测，可以按照我们所期望的方法引导优化器制定执行计划。
1.2 CBO
基于成本的优化器是关系型数据库所追求的理想型优化器，它的原理是计算了所有查询方法所需要的成本之后，从中选择一个成本最小的查询路径。
分析----CBO的数据来源
a.CBO是一个数学模型
b.需要准确的传入数据
c,通过精确的数据计算出精确的执行计划
CBO在判断最优路径时，需要通过分析相关的统计信息，这些信息包括:
表中的行数
数据块数
每个数据块中的平均行数
行的平均长度
每个列常数的种类
离散程度(直方图)
列值中null的个数
聚簇因子
索引的高度
最大最小值
叶块的数量
运行系统的IO和CPU的使用情况
select * from user_tables; select * from user_indexes;
CBO的优缺点:
缺点：
a.无法提前预测执行计划。
b.控制执行计划比较困难。
c.少数情况存在执行计划选择错误。
优点：
a.即使没有理解优化器的工作原理，大多数情况下也能得到最优化的性能。
b.通过统计信息控制优化。
优化器的优化步骤:
a.通过统计信息对所要执行的sql进行解析，在可能存在的执行计划中进行选择，之后制定出临时的执行计划。
b.优化器通过对直方图、表的存储结构特征、索引的结构、分区类型、比较运算符等信息进行分析之后计算出各个执行计划的成本。
c.优化器对各个执行计划的成本进行比较，并从中选择一个成本最低的执行计划。
优化器的组成
a.查询转换器
b.成本估算器
c.执行计划生成器
查询转换包括：
视图合并
子查询解嵌套
谓词前推
使用物化视图进行查询重写
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=100;
create table test2 as select * from dba_objects where rownum <=1000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2'); select count(1) from test1 t1,test2 t2 where t1.object_id=t2.object_id;
select count(1) from test1 t1 where t1.object_id in
(select t2.object_id from test2 t2);
select count(1) from test1 t1
where exists (select 1 from test2 t2 where t1.object_id = t2.object_id); --查看UNPARSED QUERY IS出即是查询转换
Alter system flush shared_pool;
alter session set tracefile_identifier = '';
alter session set events '10053 trace name context forever, level 1';
select count(1) from test1 t1,test2 t2 where t1.object_id=t2.object_id;
alter session set events '10053 trace name context off' ; Alter system flush shared_pool;
alter session set tracefile_identifier = 'in';
alter session set events '10053 trace name context forever, level 1';
select count(1) from test1 t1 where t1.object_id in
(select t2.object_id from test2 t2);
alter session set events '10053 trace name context off' ; Alter system flush shared_pool;
alter session set tracefile_identifier = 'exists';
alter session set events '10053 trace name context forever, level 1';
select count(1) from test1 t1
where exists (select 1 from test2 t2 where t1.object_id = t2.object_id);
alter session set events '10053 trace name context off' ; Set autotrace traceonly
select count(1) from test1 t1,test2 t2 where t1.object_id=t2.object_id;
select count(1) from test1 t1 where t1.object_id in
(select t2.object_id from test2 t2);
select count(1) from test1 t1
where exists (select 1 from test2 t2 where t1.object_id = t2.object_id);
Set autotrace off
实验:优化器RBO与CBO
1.2.1CBO为什么不走索引
为什么有时明显会走索引的情况却不走索引？
•统计信息陈旧、直方图信息有误。
•选择其他扫描的代价更低。
Drop table tt purge;
create table tt as select * from dba_objects;
create index ind_status on tt(status);
exec dbms_stats.gather_table_stats(user,'TT',cascade=>true);
set autotrace traceonly
set timing on
set linesize 1000
update tt set status='INVALID' where object_id in (10);
Commit;
select * from tt where status='INVALID';
exec dbms_stats.gather_table_stats(user,'TT',cascade=>true);
select * from tt where status='INVALID';
实验:CBO不走索引
RULE：基于规则的方式。
CHOOSE （默认） ：如果有统计信息，走CBO；如果没有统计信息且动态采集级别设置为0，走RBO。
CBO优化器有两种可选的运行模式：
FIRST_ROWS：以最低的成本返回查询的最先几行。
ALL_ROWS：以最低的成本返回所有行。
drop table test purge;
create table test as select * from dba_objects;
insert /*+append*/ into test select * from test;
Commit;
insert /*+append*/ into test select * from test;
Commit;
insert /*+append*/ into test select * from test;
Commit;
insert /*+append*/ into test select * from test;
Commit;
insert /*+append*/ into test select * from test;
commit;
select count(*) from test;
create index idx_name on test(owner,object_name) nologging;
exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true); Set timing on
Set autotrace traceonly
Set linesize 1000
SELECT /*+all_rows*/*
FROM (SELECT /*+all_rows*/
INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (select /*+all_rows*/
owner, object_name, created
from test
where owner = 'SYS'
order by object_name) INNER_TABLE
WHERE rownum <= 18)
WHERE OUTER_TABLE_ROWNUM > 1; SELECT /*+first_rows(18)*/*
FROM (SELECT /*+first_rows(18)*/
INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (select /*+first_rows(18)*/
owner, object_name, created
from test
where owner = 'SYS'
order by object_name) INNER_TABLE
WHERE rownum <= 18)
WHERE OUTER_TABLE_ROWNUM > 1;
优化器模式
2.执行计划
执行计划的重要性：当分析一条SQL语句的性能时，通常最先做的事情就是分析它的执行计划，如果连执行计划都看不懂，那SQL调优根本无从谈起。
执行计划：从表中读出数据并且生成查询语句所要求结果的查询路径。简单点说是SQL语句访问和处理数据的方式。
执行计划的类型：执行计划的最基本类型实际上就是查询且读取物理数据的方式，该方式被称为扫描。当需要从一个以上的表中读取数据时，必然需要将这些表进行连接，这样的执行类型被称为表连接。
a.扫描的执行计划
b.表连接的执行计划
c.其他运算方式的执行计划
2.1扫描的执行计划-全表扫描、rowid扫描
全表扫描(full table scan)扫描对象表中高水位线以下的所有数据块，包括空数据块，同时通过对where 条件中查询条件的过滤来筛选出满足所有条件的数据行的过程。
谓词access和filter的区别
drop table test purge;
create table test as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test');
set autotrace traceonly
select * from test where object_id=100;
create index ind_object_id on test(object_id) nologging;
select * from test where object_id=100;
谓词access和filter的区别
可以看到Access Predicate和Filter Predicate的重要区别：
Access Predicate在访问数据时做判断，不满足条件的数据不会形成Row Source；
Filter Predicate对已产生的Row Source再做判断，不满足条件的则被丢弃（Throw-Away）。
而降低执行计划中的Throw-Away是我们做SQL调优的一项重要参考指标，因此，一些将Filter Predicate转为Access Predicate的方法也是我们的重要调优手段。
2.2扫描的执行计划-索引扫描
索引扫描类型:
a.索引唯一扫描(index unique scan)
b.索引范围扫描(index range scan)
c.索引降序范围扫描(index range scan descending)
d.索引跳跃式扫描(index skip scan)
e.索引全扫描(index full scan)
f.索引快速全扫描(index fast full scan)
g.位图索引(bitmap index)
drop table test purge;
create table test as select * from dba_objects where object_id is not null;
alter table test modify object_id not null;
create unique index ind_object_id on test(object_id) nologging;
create index ind_object_name on test(object_name) nologging;
exec dbms_stats.gather_table_stats(user,'test',cascade => true);
set autotrace trace exp
--索引唯一扫描:
select * from test where object_id = 100;
--索引范围扫描
select * from test where object_name = 'C_COBJ#';
select * from test where object_id < 100;
--索引降序范围扫描
select * from test where object_id >100 and object_id <110 order by object_id desc;
--索引全扫描
select * from test order by object_id;
--索引快速全扫描
select object_id from test;
--索引跳跃式扫描
drop index ind_object_id;
create index ind_type_id on test(object_type,object_id);
select * from test where object_id=100;
--位图索引
create bitmap index bit_status on test(status);
select count(*) from test where status='VALID';
实验:索引扫描类型
2.3表连接的执行计划类型
表连接类型:
a.嵌套循环连接(nested loops join)
b.排序合并连接(sort merge join)
c.哈希连接(hash join)
d.半连接(semi join)
e.外连接(outer join)
f.索引连接(index join)
g笛卡尔连接(cartestian join)
2.3.1表连接的执行计划-嵌套循环连接
嵌套循环连接(nested loops join):
访问次数：驱动表返回几条，被驱动表访问多少次。
驱动表是否有顺序：有。
是否要排序：否。
应用场景: 1. 关联中有一个表比较小；
2. 被关联表的关联字段上有索引；
3. 索引的键值不应该重复率很高。
通过实验一起来探究nested loops join的原理吧！
set linesize 1000
Set pagesize 100
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=100;
create table test2 as select * from dba_objects where rownum <=1000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
1.创建嵌套环连接例子
basic的情况下,oracle关闭了所有性能数据的收集, 如果要关闭AWR收集
默认为typical的时,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集
all，所有的都要收集。
alter session set statistics_level=all;
select count(*) from test1 t1, test2 t2
where t1.object_id = t2.object_id;
设置SQL统计信息等级
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。
Used-Mem耗的内存
select /*+leading(t1) use_nl(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_nl(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id in (10, 11, 12);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_nl(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id =10;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_nl(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id =99999;
嵌套循环
驱动表的顺序对性能的影响（看Buffers）,大表驱动好，还是小表驱动好?
select /*+leading(t1) use_nl(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t2) use_nl(t1)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
驱动表的顺序对性能的影响
Drop table t_xiao purge;
Drop table t_da purge;
create table t_xiao(a1 number(10));
create table t_da(a2 number(10));
begin
for i in 1 .. 10 loop
insert into t_xiao values(i);
end loop;
commit;
end;
/
begin
for i in 1 .. 100000 loop
insert into t_da values(i);
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(user,'t_xiao');
exec dbms_stats.gather_table_stats(user,'t_da');
Set autotrace traceonly
select /*+leading(t1,t2) use_nl(t1,t2)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
select /*+leading(t2,t1) use_nl(t2,t1)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
Set autotrace off
select s.segment_name,s.blocks from user_segments s where s.segment_name in('T_XIAO','T_DA');
如果上述实验不够直观,那我们做下面的实验:
2.3.1表连接的执行计划-哈希连接
哈希连接(hash join):
访问次数：驱动表和被驱动表都只会访问0次或1次。
驱动表是否有顺序：有。
是否要排序：否。
应用场景： 1. 一个大表，一个小表的关联；
2. 表上没有索引；
3. 返回结果集比较大。
通过实验一起来探究hash join的原理吧！
set linesize 1000
Set pagesize 100
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=100;
create table test2 as select * from dba_objects where rownum <=1000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2'); alter session set statistics_level=all; select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_hash (t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id = 99999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_hash (t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
探究hash join的原理
select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t2) use_hash(t1)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
驱动表的顺序对性能的影响(看Used-Mem)
Drop table t_xiao purge;
Drop table t_da purge;
create table t_xiao(a1 number(10));
create table t_da(a2 number(10));
begin
for i in 1 .. 10 loop
insert into t_xiao values(i);
end loop;
commit;
end;
/
begin
for i in 1 .. 100000 loop
insert into t_da values(i);
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(user,'t_xiao');
exec dbms_stats.gather_table_stats(user,'t_da');
Set autotrace traceonly
select /*+leading(t1,t2) use_hash(t1,t2)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
select /*+leading(t2,t1) use_hash(t2,t1)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
再来做一个实验
2.3.2表连接的执行计划-排序合并连接
排序合并连接(sort merge join):
访问次数：两张表都只会访问0次或1次。
驱动表是否有顺序：无。
是否要排序：是。
应用场景：当结果集已经排过序。
通过实验一起来探究 sort merge join 的原理吧！
set linesize 1000
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=100;
create table test2 as select * from dba_objects where rownum <=1000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2'); alter session set statistics_level=all; select /*+ ordered use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+ ordered use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id = 99999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+ ordered use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
探究sort merge join的原理
Drop table t_xiao purge;
Drop table t_da purge;
create table t_xiao(a1 number(10));
create table t_da(a2 number(10));
begin
for i in 1 .. 10 loop
insert into t_xiao values(i);
end loop;
commit;
end;
/
begin
for i in 1 .. 100000 loop
insert into t_da values(i);
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(user,'t_xiao');
exec dbms_stats.gather_table_stats(user,'t_da');
Set autotrace traceonly
select /*+ leading(t1) use_merge(t2)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
select /*+ leading(t2) use_merge(t1)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
驱动表顺序
2.3.3表连接的执行计划-笛卡尔连接
笛卡尔连接(cartestian join):
在开发要消除它，它可能由下列几种情况引起：
SQL逻辑有问题，表之间没有关联关系；
表统计信息没有收集，产生错误的执行计划；
SQL过于复杂，CBO给出错误的执行计划。
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=8000;
create table test2 as select * from dba_objects where rownum <=8000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
Set autotrace traceonly
Select count(*) from test1,test2;
笛卡尔连接
2.3.4表连接的执行计划-半连接
半连接(semi join):
由各种运算所构成的子查询与主查询之间的连接。尽管子查询的种类众多，但其都是一种为实现子查询与主查询之间连接的表连接。
in 和 exists原理及效率
not in 和 not exists原理及效率
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=1000;
create table test2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
Set autotrace traceonly
--in 和exist原理和效率
select count(*) from test1 where object_id in(select object_id from test2);
select count(*) from test1 t1 where exists
(select 1 from test2 t2 where t1.object_id=t2.object_id); --not in 和not exist原理和效率
select count(*) from test1 where object_id not in(select object_id from test2);
select count(*) from test1 t1 where not exists
(select 1 from test2 t2 where t1.object_id=t2.object_id); --再看not in 和not exist的效率
Set autotrace off
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=5;
create table test2 as select * from dba_objects;
Insert into test2 select * from dba_objects;
Insert into test2 select * from test2;
Insert into test2 select * from test2;
Commit;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
Set autotrace traceonly
select count(*) from test1 where object_id not in(select object_id from test2);
select count(*) from test1 t1 where not exists
(select 1 from test2 t2 where t1.object_id=t2.object_id);
半连接
--值得注意的是：如果子查询的关联字段中存在null，not in 查出的结果是不正确的。
set autotrace off
Update test1 set object_id = null where rownum <10;
Commit;
select count(*) from test2 t2 where t2.object_id not in(select t1.object_id from test1 t1);
select count(*) from test2 t2 where not exists
(select 1 from test1 t1 where t1.object_id=t2.object_id); --not in 和 not exists等价
Oracle 10g下
select count(*) from test2 where object_id not in(select t1.object_id from test1 t1 where
T1.object_id is not null) and object_id is not null;
select count(*) from test2 t1 where not exists
(select 1 from test1 t2 where t1.object_id=t2.object_id);
Oracle 11g下在没有null的情况下就相同了
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=1000;
create table test2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
select count(*) from test2 t2 where t2.object_id not in(select t1.object_id from test1 t1);
select count(*) from test2 t2 where not exists
(select 1 from test1 t1 where t1.object_id=t2.object_id);
Set autotrace traceonly
select count(*) from test2 t2 where t2.object_id not in(select t1.object_id from test1 t1);
select count(*) from test2 t2 where not exists
(select 1 from test1 t1 where t1.object_id=t2.object_id);
Set autotrace off
Update test1 set object_id = null where rownum <10;
Commit;
select count(*) from test2 t2 where t2.object_id not in(select t1.object_id from test1 t1);
select count(*) from test2 t2 where not exists
(select 1 from test1 t1 where t1.object_id=t2.object_id);
半连接-查询字段为NULL
2.3.5表连接的执行计划-外连接
外连接(outer join):
是指以将要连接的两个表中的某个表为基准，即使连接时另一个对象表中没有找到对应的行，也同样要返回基准表中所有行的一种连接方式。
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum <=1000;
create table test2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
Set autotrace traceonly
select * from test1 t1,test2 t2 where t1.object_id(+) = t2.object_id;
外连接
2.3.6表连接的执行计划-索引连接
索引连接(index join):
在某个查询语句中所使用到的某个列存在一个以上的索引时，按照哈希连接方式将这些索引连接起来的方法。也就是说不是通过读取索引再读取表的方式，而是只通过索引连接来实现数据查询的方法。
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects where rownum <=1000;
Create index ind_t1_object_id on test1(object_id) nologging;
Create index ind_t1_object_type on test1(object_type) nologging;
Create index ind_t2_object_id on test2(object_id) nologging;
exec dbms_stats.gather_table_stats(user,'test1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'test2',cascade=>true);
Set autotrace traceonly
select /*+index_join(t1)*/ object_id,object_type from test1 t1
where object_id=100 and object_type='TABLE'; select t1.object_id from test1 t1,test2 t2
where t1.object_id = t2.object_id;
索引连接
2.3.7各类连接的限制场景
哈希连接不支持不等值<>，不支持> 、<的连接方式，也不支持like 的连接方式。
排序合并连接不支持不等值<>，也不支持like 的连接方式，但支持> 的连接的条件。
嵌套循环无限制。
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where rownum<100;
create table test2 as select * from dba_objects where rownum<1000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
Set autotrace traceonly
--<>实验
select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id <> t2.object_id;
--like实验
select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_type like t2.object_type;
--这种like是可以的
select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_type like '%TABLE%';
-- >实验
select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id > t2.object_id ;
--这种>是可以的
select /*+leading(t1) use_hash(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and t1.object_id >100;
哈希连接限制场景实验
select /*+leading(t1) use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id <> t2.object_id ; select /*+leading(t1) use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id like t2.object_id ; select /*+leading(t1) use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id > t2.object_id ;
排序合并连接限制场景实验
2.3.8其他运算方式的执行计划- IN-list 迭代
IN-list 迭代执行计划(INLIST ITERATOR):
在inlist interator 之下的查询过程被反复执行了多次，执行次数有in 中的值个数决定。
drop table test1 purge;
create table test1 as select * from dba_objects;
Create index ind_t1_object_id on test1(object_id) nologging;
exec dbms_stats.gather_table_stats(user,'test1',cascade=>true);
Set autotrace traceonly
Select * from test1 where object_id in(7,8,9,10);
Select * from test1 where object_id =7 or object_id =8 or object_id =9 or object_id =10;
IN-list
2.3.9其他运算方式的执行计划-连锁
连锁执行计划(CONCATENATION):
是指在使用or连接由不同列所构成的查询条件的情况下，按照各个查询条件将整个查询分成多个独立的查询，为各个独立查询制定最优查询路径后再将其连接起来的执行计划。
drop table test1 purge;
create table test1 as select * from dba_objects;
Create index ind_t1_object_id on test1(object_id) nologging;
Create index ind_t1_object_type on test1(object_type) nologging;
exec dbms_stats.gather_table_stats(user,'test1',cascade=>true);
Set autotrace traceonly
Select * from test1 where object_id=100 or object_type='TABLE';
连锁
2.4.0其他运算方式的执行计划-排序操作
排序操作执行计划:
sort(unique) 是指把输出结果变为唯一集合的过程，查询中用到了distinct 。
sort(aggregate) 是指在没有group by 的前提下，使用统计函数对全部数据进行运算时所显示的执行计划。在使用sum、count、min、max、avg等统计函数时并不执行我们所熟悉的一般排序。
sort(group by) 该操作是将数据行向不同分组中聚集的操作，即依据查询语句中所使用的group by 而进行的相关操作，为了进行分组就只能进行排序，因此所需分组的数据量越大则代价就越高。
sort(order by) 在查询语句中使用了order by。
drop table test1 purge;
create table test1 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1');
Set autotrace traceonly
--sort(unique)
select distinct object_id from test1 order by object_id; --sort(aggregate)
select sum(object_id) from test1; --sort(group by)
select object_type,count(*) from test1 group by object_type; --sort(order by)
select * from test1 order by object_id;
排序操作
2.4.1其他运算方式的执行计划-集合操作
集合操作执行计划:
SQL是处理集合的语言，包含读取集合、进行集合运算、输出集合。
并集(union 、union all)
交集(intersect)
差集(minus)
drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects where rownum <=1000;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
Set autotrace traceonly select * from test1
union
select * from test2; select * from test1
union all
select * from test2; select * from test1
intersect
select * from test2; select * from test1
minus
select * from test2;
集合操作
2.4.2其他运算方式的执行计划-COUNT(STOPKEY)
COUNT(STOPKEY)执行计划:
该计划指在查询语句的查询条件中使用了rownum时所显示出来的执行计划。
drop table test1 purge;
create table test1 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1');
Set autotrace traceonly
select * from test1 where rownum <10;
Count
2.4.3如何获取执行计划
autotrace(最常用的工具) autotrace traceonly explain 没有执行，其他都执行。
Explain plan for 引发硬解析，但并不会执行。
DBMS_XPLAN.DISPLAY_CURSOR 在Shared pool中取执行计划。
SQL_TRACE和10046(神器)
drop table test purge;
create table test as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1'); Autotrace
Set timing on
Set autotrace traceonly
Select * from test;
Set autotrace off 分析AWR时，可以根据SQL_ID找到真实的执行计划，绑定变量，统计信息
select hash_value, child_number, sql_text from v$sql s
where s.SQL_ID = '866n2xzvtyndu';
select * from table(dbms_xplan.display_cursor(hash_value, child_number, 'advanced')); 如果想要获得更多的信息
alter session set statistics_level=all;
Select count(*) from test;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); explain plan for
explain plan for select * from test;
select * from table(dbms_xplan.display);
示例
