oracle优化器以及SQL共享

1、oracle的优化器
============================
oracle有三种优化器
RULE
COST
CHOOSE
通过参数OPTIMIZER_MODE来设置优化器的类型，缺省情况下为选择性优化器，既是CHOOSE
SQL> show parameter OPTIMIZER_MODE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
optimizer_mode                       string      ALL_ROWS
在该参数的值中可能出现的有
ALL_ROWS
FIRST_ROWS
RULE
COST
CHOOSE
ALL_ROWS:是一种基于成本的优化器，它将选择一种在最短时间内返回所有数据的执行计划
FIRST_ROWS：和ALL_ROWS不同，他将选择一种最快返回结果集第一条记录的执行计划，不论这个执行计划是基于成本的还是基于规则的

<注意>：
在使用CBO时，必须经常使用analyze来对数据库对象进行统计分析以增加数据库中的对象信息的准确性
如果使用的是CHOOSE，选择性的优化器的话，那么实际使用的优化器将和是否使用过analyze命令有关。
如果使用过analyze，将使用CBO模式，反之为RULE模式
默认情况下数据库使用CHOOSE模式的优化器，但为了避免过多的全表扫描，最好尽量避免使用选择模式的优化器

=============================
2、访问表的方式

全表扫描：

采用顺序访问的方式访问每条记录，实际过程中oracle一次读入多个数据块来加快全表扫描
实际数据库中通过参数db_file_multiblock_read_count来控制一次读取的块的数量。对该参数进行合理配置可以优化I/O
SQL> show parameter db_file_multiblock_read_count
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
db_file_multiblock_read_count        integer     16

---------------------------------
通过ROWID访问

当使用索引的时候，索引通过键值来区分数据，每个键值都对应存储数据的ROWID，最终通过索引对应的ROWID找到数据，达到优化查询的效果

============================
 
共享sql

为了不重复解析相同的sql语句，在第一次解析后，oracle将sql语句放在library cache中，
当再次执行相同的sql语句时，将直接引用之前的执行计划。
library cache中存放的解析后的sql按照lru规则清空

要实现sql语句的共享，必须满足以下三个条件
--------------------
1、字符级比较
包括语句的
字母大小写
空格
tab键
的使用，必须完全一致
例：
SELECT * FROM EMP;
和
SELECT * from EMP;
将不会得到共享
--------------------
2、两个语句所指的对象必须完全相同
例：
user1有表t1
user2有表t1
两个用户分别建立自己的连接进入数据库，都执行
select * from t1;
 
因为owner不同，语句将不能共享
---------------------
3、两个语句中必须使用相同名字的帮定变量
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
可以共享
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
不能共享

本文出自“风中一叶”