oracle_sql优化

参考自《Oracle SQL高级编程》

1.查询语句执行顺序

2.需要返回的数据越多，查询的时间越长

3.在查询过程中排序（order by）与要排序的数据大小有关，数据较小，排序在内存中完成，数据较大，排序使用临时磁盘空间来完成；排序是查询过程中开销较大的处理步骤，尤其是返回结果集较大时

4.Oracle Concept Guids中提供的Oracle结构图，下图中包含数据库（data files）、实例（Instance），其中data files为存储在磁盘中的数据库数据，实例为Oracle的内存结构：

一个实例（Instance）由系统全局内存区域SGA和一系列后台进程组成。每一个连接到数据库的用户都通过一个客户端进程来管理，客户端进程与服务器进程相连接，每一个服务器进程都会被分配一块私有的内存区域PGA，即进程共享内存区域。

（Oracle的本地接口为OCI，OCI将查询语句发送到数据库，不管是sql plus还是sql developer，都会使用OCI与数据库服务交互）

5.库高速缓存（Library Cache）

在SGA中的库高速缓存（Library Cache）中会缓存所有执行过的sql语句，当再次执行后，如果缓存中仍存在此sql，会直接调用缓存，库高速缓存中的sql语句是所有用户共享；库高速缓存的缓存策略是保存频繁使用的语句和最近使用的语句，如果存入其中的sql长时间未使用，将会从缓存中删除

（Oracle硬解析和软解析：https://blog.csdn.net/leshami/article/details/6195483）

库缓存判断sql是否一致的依据是根据sql语句生成散列值，通过散列值来判断，这就要求sql完全一致，包括where后的常量参数值、大小写、有无注释等完全一致才能得到相同的散列值

充分利用缓存的方法是使用绑定变量，即用变量代替常量，这样相同的sql参数不同，只会缓存一条sql

variable s_id varchar2(2)
exec:s_id='01'
select * from student where sid = :s_id

查询v$sql视图可以查看存放在库高速缓存中的sql

（variable与define：https://blog.csdn.net/ds986619036/article/details/80747937）

SGA中的库缓存缓存所有用户的sql语句，数据字典缓存（Data Dir Cache）存储了所有的数据库对象信息，SGA中还存储了Oralce的系统参数

6.锁存器

 

7.缓冲区缓存（Database Buffer Cache）

 

1.优化器

优化器（Optimizer）按照一定判断规则得到目标sql的最佳执行计划，分为RBO（Rule-Based Optimizer）和CBO（Cost-Based Optimizer），RBO基于规则的优化器，在Oracle10g之后已完全弃用，但仍可以通过修改优化器模式或使用RULE Hint来显式指定使用

RBO通过一系列规则来决定sql的执行计划，sql满足那个规则就走那个执行计划，并未考虑sql实际设计到的数据量和数据分布。

CBO，基于成本的优化器，所谓的成本是指消耗的系统I/O和CPU资源，是根据目标sql涉及到的表、索引、列等相关对象的统计信息计算得到的。统计信息存储在数据库字典中，包含数据库对象的实际数据量、数据分布信息等。

CBO在解析目标sql时，会首先对sql进行查询转换，然后计算查询转换后的sql的执行路径成本，选择成本值最小的路径作为sql的执行计划，然后去执行sql，结果返回给用户。

1.1 基数

基数（Cardianlity）是指指定结果集所包含的记录数，这里的结果集是指sql执行计划每一步执行之后的结果集，基数越大，意味着执行sql的IO成本也会越高

基数对应的也是执行计划中的Rows

可选择率（Selectivity），指施加谓语条件后的sql结果集与未施加谓语的sql结果集的比率，也就是select的结果集比上from的结果集，可选择率越大，返回的结果集数量越多，成本越高

在CBO计算成本之前，会对sql进行查询转换，查询转换是为了得到更多可能的执行计划，

这里提供一个简单的例子，首先明确一个前提：在数据量较小的情况下，索引范围扫描要优于全表扫描，

两张表student s和course sc，s中sid有索引ind_sid，sc中sid字段无索引，下面是一段sql

select s.*,sc.* from student s,score sc 
where s.sid=sc.sid and sc.sid='01'

查看这段sql的执行计划，可以看到id为3使用了索引范围扫描，条件为s.sid=01，但是以上sql中并无此条件，说明是CBO进行了查询转换，增加了s.sid='01'条件，从而可以使用索引范围扫描

1.2 优化器模式

show parameter optimizer_mode;       #查看优化器模式