Oracle性能优化读书笔记(2)-SQL语句和索引优化

第四章：索引的使用

索引分类

如何有效建立索引

1.看sql执行计划，有没有全表扫描或者效率低的语句

2.看语句，找到相关表及其关联字段

3.选择可选性最高的字段建立索引：可选性就是字段不同记录值最多

如何避免索引被抑制

规范1：不要轻易在字段前面添加函数

规范2：尽量不要将字段添加到函数表达式中

复合索引--双刃剑 P62

前缀性：优先按某一字段排序的表会走包含那一个字段的复合索引

可选性：字段值越多，可选性越强

如何知道索引是否管用

第五章：SQL语句执行过程

Parse阶段：系统首先在share pool中搜索该语句，即判断该语句是否分析或执行过。如未发现，则检查语句语法语义，得出优化的执行计划。这个完整的执行过程叫做“硬解析”。如果在share pool中找到该语句，则只需检查语义和访问权限，省去大量工作。该过程叫“软解析”。

Bind阶段：当SQL语句中含有变量时，oracle通过这一阶段为变量传参或赋值。

Excute阶段：oracle将实施在Parse阶段生成的执行计划，开始DML、I/O及排序等操作。如果是DML/DDL，完成此阶段执行过程即结束。

Fetch阶段：此阶段仅适合select操作，即对查询结果的读取和排序。为提高性能，oracle建议以数组的形式成批提取记录，降低服务器和客户端的传输次数。

语句共享性原理

OLTP 联机事务处理系统

单笔查询资源消耗小，并发量高。针对上述系统应该优先优化系统响应速度。由于单个SQL语句的性能比较容易优化，应该尽量减少语句的parse次数

OLAP 决策支持系统

单笔查询资源消耗大，并发量不高。应该以系统整体的数据吞吐量作为优化目标。SQL语句主要消耗资源在Excute和Fetch阶段，应该保证这类执行语句路径最优化。

通过sql查询直接找到大量可共享的语句

v$sqlarea: 本视图持续跟踪所有shared pool中的共享cursor，在shared pool中的每一条SQL语句都对应一列。本视图在分析SQL语句资源使用方面非常重要。

SELECT sql_text FROM v$sqlarea t WHERE t.EXECUTIONS = 1 ORDER BY UPPER(t.SQL_TEXT);

V$SQLAREA中的信息列 

HASH_VALUE：SQL语句的Hash值。 

ADDRESS：SQL语句在SGA中的地址。 

这两列被用于鉴别SQL语句，有时，两条不同的语句可能hash值相同。这时候，必须连同ADDRESS一同使用来确认SQL语句。 

PARSING_USER_ID：为语句解析第一条CURSOR的用户 

VERSION_COUNT：语句cursor的数量 

SHARABLE_MEMORY：cursor使用的共享内存总数 

PERSISTENT_MEMORY：cursor使用的常驻内存总数 

RUNTIME_MEMORY：cursor使用的运行时内存总数。

SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。 

MODULE,ACTION：使用了DBMS_APPLICATION_INFO时session解析第一条cursor时的信息 

V$SQLAREA中的其它常用列 ：

SORTS: 语句的排序数 

CPU_TIME: 语句被解析和执行的CPU时间 

ELAPSED_TIME: 语句被解析和执行的共用时间 

PARSE_CALLS: 语句的解析调用(软、硬)次数 

EXECUTIONS: 语句的执行次数 

INVALIDATIONS: 语句的cursor失效次数 

LOADS: 语句载入(载出)数量 

ROWS_PROCESSED: 语句返回的列总数 

DISK_READS：物理读的数量

1.查看消耗资源最多的SQL： 

Sql代码  

SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls  

FROM V$SQLAREA  

WHERE buffer_gets > 10000000 OR disk_reads > 1000000  

ORDER BY buffer_gets + 100 * disk_reads DESC;  

2.查看某条SQL语句的资源消耗： 

Sql代码  

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls  

FROM V$SQLAREA  

WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');  

查找前10条性能差的sql语句 

Sql代码  

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea  

order BY disk_reads DESC )where ROWNUM<10 ; 

第六章：提高排序、表连接性能

多表连接过程优化？

到底使用in还是exists？

区别：没有绝对的优劣，两者主查询和子查询的执行顺序不同。in先执行子查询内容，exsits先执行主干查询内容。限制性强的sql语句判断条件在哪部分，就优先执行哪部分即可。

技术方面总结：

1.表连接次序的重要性：尽量将限制性最强的表作为驱动表

2.尽量避免用子查询，将子查询转化为多表连接，充分相信Oracle优化器

3.20/80原则

第七章：综合案例分析，hint，全表扫描，笛卡尔积

 上图加粗部分，使得两个查询表无法走索引，产生合并连接的笛卡尔积

优化器被搞晕了，没有选择最佳执行计划

应急办法。增加hint，让CBO不再走merge执行计划，而是先将select的两个表进行连接

更好的办法，上图加粗部分改为

D.REFNUM = B.REFNUM

提前对B.REFNUM进行预处理，如update。或者设置一个冗余字段，专门存储上图拼接信息。表结构设计很重要。

精髓：将最终的SQL查询 条件尽量简化成形如D.REFNUM = B.REFNUM的形式，不玩虚的。将复杂问题简单化，让Oracle优化器充分发挥优势。

简简单单就是真

关于全表扫描

导致数据库性能问题的常见原因

1.不合理的大表全表扫描

2.语句共享性不好：没有合理使用绑定变量，导致大量语句重复解析（Parse）操作，浪费大量内存空间

select * from v$session_longops中记录的查询时间超过6秒的sql语句大部分都是全表扫描