性能调整读书笔记

重读 (sybex)_OCP Oracle9i Performance Tuning Study Guide.pdf 时作了一些读书笔记，感觉一边理解教材一边作些笔记，过些天再重阅这些笔记，效果要好很多。

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

第二章 调整的信息来源


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    自实例启动以来的统计
V$SESSTAT    目前连接会话的统计
V$SESSION    目前连接会话的信息
V$WAITSTAT    块竞争的统计

最常用的DBA视图（有些栏位的值需要分析表或索引后才会有值）：
表名    描述
DBA_TABLES    表的存储，统计等
DBA_INDEXES    索引的存储，统计等
INDEX_STATS    索引的深度和键值的离散度等
DBA_DATA_FILES    数据文件的命名，位置，大小
DBA_SEGMENTS    段的相关信息
DBA_HISTOGRAMS    表，索引的柱状图定义信息


Oracle 提供的脚本和包
下面提到的脚本都位于%ORACLE_HOME%\RDBMS\ADMIN目录下，有些只需运行一次，有些需要每次都运行。
·    UTLBSTAT.SQL 和 UTLESTAT.SQL
这些脚本的功能绝大部分已被STATSPACK所取代。
·    STATSPACK
?nbsp;   配置STATSPACK：在SQL*Plus下运行spcreate.sql，这个脚本运行时调用另外三个脚本（spcusr.sql，spctab.sql，spcpkg.sql），运行过程中会询问perfstat方案的密码，默认表空间和临时表空间，可以通过这三个脚本的运行日志（spcusr.lis，spctab.lis，spcpkg.lis）查看配置是否成功。
?nbsp;   perfstat方案的默认表空间需要有足够的空间来容纳目前的对象和将来的快照数据；
?nbsp;   查看日志如果发现有错误，运行spdrop.sql来清除已创建的对象，然后重新运行spcreate.sql；
?nbsp;   配置成功后生成快照有两种方法：手工运行exec statspack.snap; 运行spauto.sql脚本，创建job来定时生成快照；
?nbsp;   生成性能报表：运行spreport.sql脚本，脚本运行过程中要求输入三个参数：开始快照号，结束快照号，报表名；两次选择的快照号需要是在实例的一次运行期间的不同点的快照，否则就没有参考意义；
?nbsp;   statspack报表含有各个方面的性能数据，如何理解这些数据贯穿整本书。

第三章 SQL 调整 1.SQL性能测量工具


TKPROF
全名为Trace Kernel Profile，用来格式化跟踪文件，是一个命令行工具，基本的格式如下：
tkprof tracefile outputfile …
可选参数及说明如下：
Option    Description
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优化吞吐量；