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语句大部分都是全表扫描