性能调整读书笔记

现陆续将这些笔记贴出来，欢迎大家讨论和指正。

第二章 调整的信息来源


Alert Log 文件中记录的与性能有关的信息：
1.        ORA-01652，不能扩展临时段；
2.        ORA-01653，不能扩展表段；
3.        ORA-01650，不能扩展回滚段；
4.        ORA-01631，到达了表的最大的Extents；
5.        检查点未完成（Checkpoint not complete）；
6.        线程推进日志序列（Thread n Advanced to Log Sequence n）；
7.        ORA-01555，快照过旧。

后台跟踪文件
自动生成
相关参数：BACKUPGROUND_DUMP_DEST

事件跟踪文件
设置后生成
相关参数：EVENT， BACKUPGROUND_DUMP_DEST， USER_DUMP_DEST

用户跟踪文件
相关参数：SQL_TRACE，USER_DUMP_DEST， MAX_DUMP_FILE_SIZE
激活用户跟踪的三种方法：
1.        实例层次的跟踪：配置参数SQL_TRACE = TRUE | FALSE，然后重启实例；
2.        用户层次的自行实置：Alter session set sql_trace = true | false；
3.        用户层次的DBA设置：Exec dbms_system.set_sql_trace_in_session(SID, SERIAL#, TRUE | FALSE)

V$视图和DBA_视图
区别：
1.        V$视图通常是单数，DBA视图通常是复数，例如V$DATAFILE与DBA_DATA_FILES；
2.        当数据库处于Nomout或者Mount时，许多V$视图已经是可用的，而DBA视图必须在数据库处于Open时才可用；

3.        V$视图查询出来的数据多小写，DBA视图查询出的数据通常大写，所以在写WHERE条件时需特别小心；
4.        V$视图中包含的是自实例启动以来的动态数据，在数据库关闭后会消失，查询V$视图时必须关注时效性，DBA视图中包含的是静态数据；
5.        V$视图的基表是X$表，X$表是存在于内存中的虚表，DBA视图的基表是数据字典表，如SYS.OBJ$， SYS.FILE$等，这两种基表都很少有文档。

最常用的V$视图：
表名        描述
V$SGASTAT        显示SGA组件大小的信息
V$EVENT_NAME        显示当前版本的所有等待事件
V$SYSTEM_EVENT        自实例启动已来的等待事件
V$SESSION_EVENT        目前连接会话的等待事件
V$SESSION_WAIT        目前连接会话正在发生的等待事件
V$STATNAME        显示当前版本的所有统计名称
V$SYSSTAT        自实例启动以来的统计
EXPLAIN        为每个SQL语句产生执行计划，指定用户方案和密码，使用其中的PLAN_TABLE表
TABLE        当EXPLAIN选项生效时可用，指定替代PLAN_TABLE的表
SYS        启用该选项时不包含递归调用
SORT        指定SQL语句的排序方式
RECORD        指定文件名，将SQL语句写入，排除递归的SQL
PRINT        限定只输出指定数量的分析SQL语句
INSERT        指定一个文件名，容纳相关的插入SQL语句，将分析的SQL及统计插入TKPROF_TABLE
AGGREGATE        指定多用户共用的SQL语句统计方式
WAITS        指定是否统计跟踪文件中的等待事件

SORT选项可分三类，解析选项，执行选项和取数据选项（PRS,EXE,FCH），根椐这三种操作占用相关资源来排序。

TKPROF针对SQL的统计也分为三类：解析，执行，取数，具体的统计选项见下表：
统计名称        统计描述
Count        Parse,Execute,Fetch发生的次数
CPU        占用CPU秒
Elapsed        经过秒
Disk        从磁盘读取的数据块数
Query        从SGA中读取的回滚段块数
Current        从SGA中读取的一致性数据块数
Rows        执行INSERT，UPDATE，DELETE影响的行数或者FETCH返回的行数

从TKPROF的统计结果观察，发现下列特征的SQL可能需要调优：
1.        消耗过度的CPU资源；
2.        花费太长的时间在Parse，Execute，Fetch阶断；
3.        从磁盘读数据块太多而从SGA中读块很少；
4.        存取太多的数据块（从SGA或者磁盘）而返回的行数很少；

TOP SQL
这个工具从Enterprise Manager Console中启动，有与TKPROF类似的统计，用于确定需要调整的SQL语句。

EXPLAIN PLAN
用EXPLAIN PLAN FOR sql语句产生执行计划，然后再从PLAN_TABLE中查询；
执行计划中各步骤的执行顺序遵照如下原则：
1.        从上到下；
2.        从右到左（或者说，从缩进最多的到缩进最少的）；
3.        索引操作不参与上面的规则，索引从属于表操作，先于表操作。

执行计划的详尽解释超出本书范围，需要参考其它资料；
AUTOTRACE
AUTOTRACE综合了TKPROF和EXPLAIN PLAN FOR的功能；
配置AUTOTRACE包括以下步骤：
1.        保证需要AUTOTRACE功能的用户下有PLAN_TABLE或者有该表的全局同义词和那个基表的存取权限；
2.        创建PLUSTRACE角色，并将该角色权限赋给相应的用户，或者相应会话有这个角色对应的权限；
3.        相关脚本：%ORACLE_HOME%\rdbms\admin\utlxplan.sql，%ORACLE_HOME%\sqlplus\admin\plustrce.sql

AUTOTRACE的特点：
1.        只能在SQL*Plus的会话中执行；
2.        产生执行计划前会实际执行SQL，这点与EXPLAIN PLAN FOR不同；
3.        在发出SQL之前，需执行SET AUTOTRACE ON语句。

SET AUTOTRACE语句各选项的意义：
选项        描述
ON        显示查询结果，执行计划，统计
ON STATISTICS        显示查询结果，统计
ON EXPLAIN        显示查询结果，执行计划
TRACEONLY        显示执行计划，统计
TRACEONLY STATISTICS        显示统计
OFF        关掉AUTOTRACE
     
STATSPACK
STATSPACK中也包含几种方法排序的SQL，通常只显示排序值大于一定阀值的SQL，这些阀值可能改变：
SQL类型        说明
SQL ordered by Gets        根椐逻辑I/O来排序
SQL ordered by Reads        根椐物理I/O来排序
SQL ordered by Executions        根椐执行次数来排序
SQL (Executions) ordered by Parse Calls        根椐分析次数来排序

第三章 SQL 调整 2. 理解ORACLE优化器


·        优化器的职责是从多行种执行路径中选择一种最优的执行路径；
·        有两种优化模式：RBO（基于规则），CBO（基于成本）；
·        RBO根椐一系列规则来确定执行计划，不考虑表的大小，栏位的集势等统计数据，主要用于早期的版本或者新版本的递归SQL；
·        CBO会考查表或索引的统计，然后比较不同执行计划的IO成本，CPU成本，临时表空间的需求，得出一个综合成本最小的执行计划；
·        CBO考查的统计包括：表或索引的大小，表或索引的行数，表或索引的数据块数，表行的长度，索引栏位的集势等；
·        默认情况下，字典里并不包含表或索引的统计，这些数据是在分析表，索引，方案或整个数据库的时候写进字典里的。
·        分析可以使用两个方式：COMPUTE STATISTICS（整体分析）， ESTIMATE STATISTICS（样本分析，可以用SAMPLE子句指定样本行数或者比例）；
·        可以分析栏位的柱状图以指示优化器栏位数据的离散分布状况，ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE integer_value(1-254)，默认分析选项下只保存栏位的最大最小值，优化器假设栏位值是均匀分布的，在某些不均衡的情况下，优估器可能产生效率极低的执行计划，因此分析柱状图显得相当重要；
·        可以用包程序来分析整个方案或者整个数据库，DBMS_UTILITY， DBMS_STATS；
·        在ORACLE9中推荐使用DBMS_STATS，它有以下新特性：
v        可以在分析前备份现有的统计，用于当更新统计后CBO性能反而下降的情形下恢复以前的统计；
v        样本分析时随机取数据块，而不只是数据行；
v        可以在并行模式下收集统计；
v        DBMS_STATS.GATHER_SCHEMA_STATS可以定期自动收集高变更的表的统计，也可以用来自动收集柱状图的统计并自动决定切片数以及哪些栏位需要柱状图统计；
v        DBMS_STATS.GATHER_SYSTEM_STATS可以用来收集系统的CPU和IO负载统计，为CBO决策提供参考，避免系统产生CPU或IO瓶颈；
v        可以用于将生产环境的统计转移到开发环境，这一点对于从开发环境调优生产环境相当重要。
·        统计结果存放在下面一些字典里面：DBA_TABLES， DBA_INDEXES， DBA_TAB_COL_STATISTICS， DBA_HISTOGRAMS等。
设置优化器模式
·        可以在下面三种级别配置优化器模式：实例级，会话级，语句级，优先级从低到高；
·        设定优化器行为版本差异的9i新参数：OPTIMIZER_FEATURES_ENABLE；
·        OPTIMIZER_MODE初始化参数确定实例中所有会话默认的优化模式，可选值有RULE， CHOOSE（默认值）， FIRST_ROWS， FIRST_ROWS_n， ALL_ROWS（与CHOOSE似乎没有区别？）；
·        会话级的优化模式设置用ALTER SESSION SET OPTIMIZER_MODE = mode，可选项同上;
·        在SQL语句中内嵌提示/*+ mode */ 可用来指示当前语句的优化模式，mode选项可用上面提到的除CHOOSE外的其它四种，另外还有四十多种提示可用；
·        在RULE模式下，如果SQL涉及到下面的特性时还是会用CBO：分区表和分区索引，索引组织表，反向索引，基于函数的索引，位图索引，查询重写，物化视图；
·        在非RULE的其它四种模式下，如果涉及到的所有的表或者索引的统计都不存在，使用RBO模式，其它情况下使用CBO；
·        FIRST_ROWS， FIRST_ROWS_n 优化响应时间， ALL_ROWS优化吞吐量；