期刊摘录《ORACLE数据库SQL优化原则》

优化器选择：

RULE（基于规则）、CUST（基于成本）、CHOOSE（选择性）

init.ora文件中的OPTIMIZER_MODE参数

为了使用基于成本的优化器(CBO,Cost-Based Optimizer)，必须经常运行 analyze 命令 来 增 加 数 据 库 中 的 对 象 统 计 信 息 (objectstatistics)的准确性。如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为 CBO，反之，数据库将采用 RULE 形式的优化器。

在缺省情况下，Oracle 采用 CHOOSE 优化器，为了避免那些不必要的全表扫描(full table scan)，须尽量避免使用 CHOOSE 优化器，而直接采用基于规则或者基于成本的优化器。

执行过程：

当用户提交一条 SQL 语句时，Oracle 会做如下操作：
       第一步

执行。分：客户端把语句发给服务器端执行。
       第二步：语句解析。当客户端把 SQL 语句传送到服务器后，服务器进程会对该语句进行解析。服务器端具体做以下工作：

           (1)查询高速缓存。如果在高速缓存中刚好有其用户使用过这个查询语句，则服务器进程就会直接执行这个 SQL 语句，省去后续的工作。

           (2)语句合法性检查。

           (3)语言含义检查。

           (4)获得对象解析锁。语法、语义都正确后，系统就会对需要查询的对象加锁。

           (5)数据访问权限的核对。

           (6)确定最佳执行计划。服务器进程会根据一定的规则，对这条语句进行优化。当服务器进程的优化器确定这条查询语句的最佳执行计划后，就会将这条 SQL 语句与执行计划保存到数据高速缓存。等以后还有这个查询时，就会省略以上的语法、语义与权限检查的步骤，而直接执行 SQL 语句，提高 SQL 语句处理效率。
       第三步：语句两种情况：

          (1)被选择的数据块已经被读取到数据缓冲区，服务器进程会直接把这个数据传递给客户端。

          (2)数据不在缓冲区中，服务器进程将从数据库文件中查询相关数据，并把这些数据放入到数据缓冲区中。
第四步：提取数据。返回语句执行结果。从 SQL 语句执行过程来看，要想提高 SQL 语句执行效率，除了必备的 SQL 语句书写规范和技巧外，数 据 库 的 DB_CACHE_SIZE 和 SHARE_POOL_SIZE两个参数起着至关重要的作用。


监控数据库数据缓存命中率的语句：
Select round((1-(sum(decode(name, 'phy- sicalreads', value, 0))/(sum(decode(name, 'db block gets',value, 0))+sum(decode(name, 'consistent gets',value, 0))))), 2) "data buffer hit ratio " from v$sysstat;

监控数据库库缓存命中率的语句：
Select sum(pins)"total pins", sum(reloads) "totalreloads", round ((1-(sum (reloads) / sum (pins))), 4)"library cache hit ratio" from v$libr- arycache;

SQL查询性能优化原则

优化排序操作：

为了使用基于成本的优化器，必须经常运行 analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。
层次大于4 的索引宜重建;基表数据删除时，索引项也随之删除，当删除项占所有项超过 20%时，这个索引也需要重建。

书写 SQL 语句要尽可能使用索引
创建索引后，如果 SQL 语句书写不当，优化器生成的执行计划也不会包含索引，应该避免下列情况：

(1) 对 WHERE 条件后的列使用函数。
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM
WHERE UPPER(ITEM_ID)=‘A_INC’
即使对该表的 ITEM_ID 字段创建了索引，该执行计划也不会使用到索引

建立函数索引：CREATE INDEX UPPER_ITEM_ID ON CHECK_ITEM(UPER(ITEM_ID));
(2) 避免‘<>’和‘!=’操作符使用。
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM
WHERE ITEM_ID<>‘A_INC’
可以改写为：
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM

WHERE ITEM_ID>‘A_INC’ OR ITEM_ID<‘A_INC’

(3) 比较不匹配的数据类型，ITEM_ID 字段是VARCHAR2 类型,如下查询：
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM
WHERE ITEM_ID=100
Oracle 会自动将语句变为

WHERE TO_NUMBER(ITEM_ID)=100，导致索引无法使用，应将语句写为：

SELECT ITEM_ID,ITEM_DESC
FROM CHECK_ITEM WHERE ITEM_ID=‘100’

减少排序：
用 UNION ALL 代替 UNION。UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。如果表数据量大的话可能会导致用磁盘进行排序。
使用 WHERE 代替 HAVING，HAVING 只会在检索出所有记录之后才对结果集进行过滤。如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销。

低效：
SELECT SET_OF_BOOKS_ID_PERIOD_NAME,
SUM(QUARTER_TO_DATE_DR)
FROM GL_BALANCES
GROUP BY PERIOD_NAME
HAVING PERIOD_NAME<>‘JAN-06’
高效：
SELECT SET_OF_BOOKS_ID_PERIOD_NAME,
SUM(QUARTER_TO_DATE_DR)
FROM GL_BALANCES
WHERE PERIOD_NAME<>‘JAN-06’
GROUP BY PERIOD_NAME

减少 I/O：
过多的I/O操作会占用CPU时间、消耗大量内存，因此有必要对 SQL 的 I/O 进行优化。
(1) 尽量使用索引,避免全表扫描
(2) WHERE 子句中的连接顺序
Oracle 采用自下而上 WHERE 子句，根据这个原理，表之间的连接必须写在其他 WHERE 条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾。

SELECT PERIOD_NAME,SUM(QUARTER_TO_ DATE_
DR)
FROM GL_BALANCES
WHER EPERIOD_NAME=‘JAN-06’
AND ACTUAL_FLAG=‘A’
GROUP BY PERIOD_NAME
(低效的，执行时间为 15.2 秒)
SELECT PERIOD_NAME,SUM(QUARTER_TO_DATE_
DR)
FROM GL_BALANCES
WHERE ACTUAL_FLAG =‘A’
AND PERIOD_NAME =‘JAN-06’
GROUP BY PERIOD_NAME
(高效的，执行时间为 3.6 秒)
(3) 选择最有效率的表名顺序
Oracle 的解析器按照从右到左的顺序处理 FROM子句中的表名。在 FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。

减少表的访问次数：
执行 SQL 语句时，数据库内部要执行许多工作，对系统资源消耗严重。如果能将相关的 SQL 语句进行有效整合，可以大大降低数据库的访问次数，从而提高系统性能。

SELECT SET_OF_BOOKS_ID, PERIOD_NAME,
SUM(QUUARTER_TO_DATE_DR)
FROM GL_BALANCES
GROUP BY PERIOD_NAME
HAVING PERIOD_NAME=‘JAN-06’
SELECT SET_OF_BOOKS_ID, PERIOD_NAME,
SUM(QUUARTER_TO_DATE_DR)
FROM GL_BALANCES
GROUP BY PERIOD_NAME
HAVING PERIOD_NAME=‘JAN-08’
整合后：
SELECT SET_OF_BOOKS_ID, PERIOD_NAME,
SUM(QUUARTER_TO_DATE_DR)
FROM GL_BALANCES
WHERE PERIOD_NAME IN (‘JAN-06’,‘JAN-08’)
GROUP BY PERIOD_NAME

另附Oracle优化器相关博文地址： 点击打开链接

       SQL语句优化相关博文地址： 点击打开链接