SQL在Oracle中的执行路径

测试用例

sqlplus / as sysdba

--进入测试用户
conn test/test

--表t若已存在则删除
drop table t;
--建表
create table t as select * from all_objects;
--建立索引
create index idx_object_id on t(object_id);

--开始跟踪SQL指令的执行计划和执行的统计信息
set autotrace on
set linesize 1000
--跟踪该语句执行完成的时间
set timing on

--查询语句
select object_name from t where object_id=29;

image.png

第一次执行

image.png

第二次执行

image.png

首次执行产生了11次递归调用，79次逻辑读，4次物理读。

image.png

第二次执行产生了0次递归调用，4次逻辑读，0次物理读。

image.png

1. 用户首次执行该SQL指令时，该指令从磁盘中获取用户连接信息和相关权限信息，并保存在PGA内存里。当用户再次执行该指令时，由于SESSION之前未被断开重连，连接信息和相关权限信息就可以从PGA内存中直接获取，避免了物理读。
2. 首次执行该SQL指令结束之后，SGA内存区的共享池里已经保存了该SQL指令唯一的hash值，并保留了语法语义检查及执行计划等相关解析动作的劳动成果，当再次执行该SQL指令时，由于该SQL指令的hash值和共享池里保存的相匹配，所以之前的硬解析动作无须再做，不仅跳过了相关语法语义检查，对于该选取哪种执行计划也无须考虑，直接拿来就好。
3. 首次执行该SQL指令时，数据一般不在SGA的数据缓冲区里（除非被别的SQL读入内存了），只能从磁盘中获取，不可避免的产生了物理读，但是由于获取后会保存在数据缓冲区里，再次执行时可直接从数据缓冲区里获取，完全避免了物理读，没有物理读，数据全在缓存中，效率自然就高多了。

再来看看强制走全表扫描的情况

--强制SQL语句不走索引，走全表扫描
select /*+full(t)*/ object_name from t where object_id=29;

第一次执行

image.png

第二次执行

image.png

• 比较强制走全表扫描的SQL指令和此前Oracle自己选择用索引方式的SQL指令的执行时间，以及各自执行代价及逻辑读的大小，发现强制走全表扫描明显在性能上要大大逊色于Oracle自己选择的索引方式。

从Cost上看

image.png
image.png

从逻辑读上看

image.png
image.png

• 从上面可以看出Oracle自己选择的最优执行计划是非常正确的，而选择的结果能保留下来重用，避免了重复操作，性能就大幅得到提升了，Oracle这方面的设计确实很巧妙。