oracle性能调整笔记[zt]

ref: http://www.itpub.net/422928.html

[@more@]

第二章 调整的信息来源 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%RDBMSADMIN目录下，有些只需运行一次，有些需要每次都运行。 · UTLBSTAT.SQL 和 UTLESTAT.SQL 这些脚本的功能绝大部分已被STATSPACK所取代。 · STATSPACK ? 配置STATSPACK：在SQL*Plus下运行spcreate.sql，这个脚本运行时调用另外三个脚本（spcusr.sql，spctab.sql，spcpkg.sql），运行过程中会询问perfstat方案的密码，默认表空间和临时表空间，可以通过这三个脚本的运行日志（spcusr.lis，spctab.lis，spcpkg.lis）查看配置是否成功。 ? perfstat方案的默认表空间需要有足够的空间来容纳目前的对象和将来的快照数据； ? 查看日志如果发现有错误，运行spdrop.sql来清除已创建的对象，然后重新运行spcreate.sql； ? 配置成功后生成快照有两种方法：手工运行exec statspack.snap; 运行spauto.sql脚本，创建job来定时生成快照； ? 生成性能报表：运行spreport.sql脚本，脚本运行过程中要求输入三个参数：开始快照号，结束快照号，报表名；两次选择的快照号需要是在实例的一次运行期间的不同点的快照，否则就没有参考意义； ? 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%rdbmsadminutlxplan.sql，%ORACLE_HOME%sqlplusadminplustrce.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优化吞吐量； 第三章SQL 调整 3. 稳定执行计划 改善应用程序性能 包括两个方面：改进执行路径（稳定执行计划，物化视图）和最小化IO（索引，分区， 簇）； 稳定执行计划 · 可以用存储在OUTLN方案的公共大纲或者存储在本方案的私有大纲来稳定某些SQL语句的执行计划，这样这些语句的执行计划就不会因为统计数据或者会话优化模式的改变而发生改变； · 创建大纲时可以指定类别，默认的类别是DEFAULT； · 创建大纲时，是将SQL当前的执行计划和SQL文本一起存储起来； · 激活大纲有三种方式，SQL文本和大纲完全一致时才会使用大纲中存储的执行计划； ? 在参数文件中加上USE_STORED_OUTLINES=TRUE ? ALTER SYSTEM SET USE_STORED_OUTLINES={TRUE | FALSE | category_name}; ? ALTER SESSION SET USE_STORED_OUTLINES={TRUE | FALSE | category_name}; · 当SQL语句执行时，ORACLE确定执行计划是按下面的顺序进行的，检查共享池中是否有可用的大纲 à 检查字典中是否有可用的大纲à 检查共享池中是否有可共用的已解析SQL à 考查优化模式和相关统计产生并选择最优的执行计划，前面三种情形下都有现成的执行计划； · 相关的数据字典视图有：DBA_OUTLINES， DBA_OUTLINE_HINTS。 第三章SQL 调整 4. 物化视图 · 物化视图用数据段存储预连接，预汇总的查询数据，物化视图可以有索引，也可以分区，物化视图主要应用于数据仓库和决策支持系统； · 创建物化视图时需考虑以下因素： 1. 确定视图语句，是何种连接，何种汇总；可以借助Summary Advisor来确定最佳的物化视图SQL语句，并检查已创建物化视图的使用情况； 2. 确定刷新方式：NEVER REFRESH（不刷新），REFREST FAST（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）， REFRESH COMPLETE（先清除，再重装数据）， REFRESH FORCE（先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式）； 3. 确定刷新时机：ON COMMIT（事务提交时刷新）， ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS来手工刷新）， By Time（用START WITH 和 NEXT 子句创建的job来定时自动刷新）； 4. 创建方式：BUILD IMMEDIATE（立即生成数据）， BUILD DEFERRED（下一次刷新时生新数据）， ON PREBUILD TABLE（不创建新的数据段，用已存在的含有当前物化视图数据的表来代替）； 5. ENABLE | DISABLE QUERY REWRITE指定是否启用当前物化视图用于查询重写，启用该选项时，系统会检查以保证查询的可确定性（不允许有如序列数，USER， DATE等不确定的返回值），DISABLE时物化视图照样可以被刷新； · 与物化视图生效相关的设置 1. 初始化参数JOB_QUEUE_PROCESSES设置大于零，物化的自动刷新操作需要JOB QUEUE进程来执行； 2. 初始化参数OPTIMIZER_MODE要设成某种CBO优化模式； 3. 用户会话有QUERY_REWRITE（优化器能将查询重写到本方案物化视图）或GLOBAL_QUERY_REWRITE（优化器能将查询重写到其它方案的物化视图）系统权限； 4. 初始化参数QUERY_REWRITE_ENABLED 指示优化器是否动态重写查询来使用物化视图，这个参数可以在四个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION， HINTS）； 5. 初始化参数QUERY_REWRITE_INTEGRITY 指示优化器在不同的数据一致性情况下决定是否使用物化视图来重写查询，ENFORCED（只有在能确保数据一致的前提下才使用物化视图）， TRUSTED（数据不一定一致，只要有用维度对象定义的关系存在，就可使用物化视图）， STALE_TOLERATED（数据不一致，也没有相关的维度定义时仍可使用物化视图），这个参数可以在三个级别上进行设置（参数文件，ALTER SYSTEM， ALTER SESSION）； 第三章 SQL 调整 5.索引 索引 ORACLE 9i 中有六种索引：二叉树索引，压缩的二叉树索引，位图索引，基于函数的索引，反向索引，索引组织表； 二叉树索引 · 二叉树索引将索引栏位值和ROWID一起保存树状结构中，适用于只存取表中总记录的5%以下的查询； · 出现在SQL的WHERE条件中，集势高的栏位适于当作索引栏位； · 随着基表数据的不断增加，索引块会不断分裂以保持二叉树的平衡，树的层次（从根结点访问到叶结点要经过的数据块数，DBA_INDEXES.BLEVEL）也不断增加，层次大于4的索引宜重建； · 基表数据删除时，索引项也随之删除，但叶块上的空间并不能被重用，除非该叶块上所有的索引项都被删除，当删除项占所有项超过20%时，这个索引也需要重建（ANALYZE INDEX … VALIDATE STRUCTURE;分析索引后查看index_stats.del_lf_rows_len 和 index_stats.lf_rows_len 可以知道被删除的索引项占用的空间和所有叶行占用的空间）； · 有三种重建索引的方法： 1. 先删除再重建；这种方法耗费最多的资源，是早期版本的唯一方法； 2. ALTER INDEX … REBUILD; 这种方法高效快速，但需要额外的磁盘空间；用这种方法可以指定许多选项如ONLINE（在线重建可减少锁争用）， TABLESPACE（移动段到其它表空间）， COMPUTE STATISTICS（统计）， PARALLEL（并行）， NOLOGGING（尽可能少地产生日志）； 3. ALTER INDEX … COALESCE; 这种方法快速，无需额外空间，锁争用也少，缺点是选项少。 压缩的二叉树索引 · 压缩的二叉树索引对重复的索引键值只保存一次，后跟所有的有这个键值行的ROWID； · 有两种方法创建： 1. CREATE INDEX … COMPRESS; 2. 先创建普通索引，再更改：CREATE INDEX …; ALTER INDEX … COMPRESS; 位图索引 · 位图索引适于创建在数据相对稳定的表的低集势栏位上； · 位图索引针对每一个键值创建一个二进制位图，位图中的每位对应表中的一行，1表示这一行是当前键值，0表示这一行为其它键值； · 与二叉树索引相比，位图索引占的空间很少，如果WHERE条件中出现同一表的多个位图索引栏位时，无论是AND或是OR操作，都能用位图合并操作快速地定位到ROWID； · 当基表上有DML操作时，整个位图都会被锁住，但是，每一个DML操作，位图只需更新一次； · 相关参数： 1. SORT_AREA_SIZE 创建索引排序位图栏位和ROWID时会用到； 2. PGA_AGGREGATE_TARGET 创建位图索引以及位图合并时会用到； 3. CREATE_BITMAP_AREA_SIZE， BITMAP_MERGE_AREA_SIZE 这是8i的两个与位图创建和合并有关参数，在9i建议不再使用。 基于函数的索引 · 当栏位以函数表达式的形式出现在WHERE条件中，基于这些栏位的索引不会被优化器用到，除非是基于这些栏位的函数索引； · 要创建基于函数的索引需要QUERY REWRITE 或 GLOBAL QUERY REWRITE系统权限，有CREATE ANY INDEX权限是不行的； · 要想优化器使用基于函数的索引，OPTIMIZER_MODE必须设成某种CBO模式，QUERY_REWRITE_ENABLED须设成TRUE（这点从试验中无法证明，设成FALSE似乎也能用到函数索引）。 反向索引 · 反向索引是建立在索引栏位值的反向值上的，可以使相临的记录在索引段里变得离散，可以减少某些DML操作引发的块竞争； · 反向索引多建立在用序列号生成的栏位上，这样的栏位在普通索引中容易形成更多的层（设计过一些试验，但一直未观察到这种现象）； · 有两种产生方法：CREATE INDEX … REVERSE; ALTER INDEX … REVERSE; · 反向索引适于WHERE中的等于或不等于的比较，对于范围查找（>,< between）是无能为力的。 索引组织表 · 前面五种索引的基表数据存储是随机的，这种表称为堆表； · 索引组织表的数据存储在二叉树索引中，所以，如果通过主键来存取数据，索引组织表能更快地返回数据，因为不需要先取索引块再取数据块，可以直接从索引中返回数据； · 创建索引组织表时必须指定一个主键栏位，用这个作为索引； · 索引组织表上不能建唯一约束，也不能将索引组织表建立在簇上面； · 索引组织表的语法要点： ? ORGANIZATION INDEX 指明当前表是索引组织表； ? PCTTHRESHOD 后跟一个0到50的数，默认值为50，指明容纳一行数据可用使块空间的百分比； ? INCLUDING 后跟一个字段，如果数据行的长度超过了PCTTHRESHOLD指定的可用空间，从这个字段之后将数据行分为两段，后面的部分放入溢出段中； ? OVERFLOW TABLESPACE 指定溢出段所在的表空间； ? MAPPING TABLE 当在索引组织表上创建位图索引时创建关联的映射表；堆表位图索引的每个位对应到表的一个ROWID，索引组织表的ROWID会随着索引的分裂而发生改变，如果和堆表一样处理，位图索引很容易就失效或者维护成本很高，映射表就是为解决这个问题而引入的，映射表存放索引组织表的ROWID和逻辑行间的对应关系，索引位图中的位对应到逻辑行；dba_indexes.pct_direct_access可以用来指示映射表GUESS的效率，这个值大于30时推荐重建位图索引；一个索引组织表只有一个映射表。 · 通过dba_tables.iot_name， dba_tables.iot_type 可以查看到索引组织表的溢出表段，映射表段； · 索引组织表的相关段的段名都是由系统生成的，这些段名的共同特性是SYS_IOT_XXX_YYY， XXX in （TOP 索引段， OVER 溢出表段， MAP 映射表段），YYY是索引组织表的OBJECT_ID。 标识未使用过的索引 · 索引创建后是否使用是由优化器来控制的，某些索引可能不会使用到，这样的索引不仅加重了DML操作的负担，也占用空间；可以用下面的方法找出这样的索引，然后删除； ? 找出某个或某些被怀疑的索引； ? ALTER INDEX index_name MONITORING USAGE; ? 在数据库经历一定时间的活动后再执行: ALTER INDEX index_name NOMONITORING USAGE; ? 查询v$object_usage.index_name, v$object_usage.used，可以得知被监控的索引在这段时间内是否被使用到。 第三章 SQL 调整 6.分区 · 分区表是将大表的数据分成称为分区的许多小的子集，9i提供四种分区方法：范围分区，列表分区，哈希分区和混合分区； · 范围分区是根椐分区键的不同取值范围来划分子集的，关键字RANGE， VALUES LESS THAN； · 列表分区是根椐分区键的一些离散的取值来划分子集的，关键字LIST， VALUES； · 哈希分区是应用哈希算法将分区键对应到某个子集中去，关键字HASH， PARTITIONS； · 混合分区只能有两层，第一层是范围分区，第二层可以是列表分区或者哈希分区； · 范围分区和列表分区中，如果插入记录的分区键没有对应的容纳分区，会产生ORA-14400； · update操作如果会使记录从一个分区迁移到另一个分区，且分区表的ROW MOVEMENT属性是DISABLE，会产ORA-14402； · 分区表上的索引有两大类：普通的二叉树索引，分区索引，下面讲到的都是分区索引： · 按索引分区和表分区间的对应关系可以分为局部索引和全局索引； ? 局部索引的索引分区和表分区间是一一对应的，全局索引则相反； ? 局部索引的分区方法可以用上面提到四种的任何一种，全局索引的分区方法只有范围分区（而且最高的分区必须用MAXVALUE来定义）； ? ORACLE自动维护局部索引的分区，当表分区被合并，分裂或删除时，关联的索引分区也会被合并，分裂或删除；对分区表执行管理操作时会使其上的全局索引失效； ? 建在分区表的位图索引必须是局部分区索引； ? ORACLE推荐尽可能地使用局部索引； · 按索引栏位和分区键间的关系分为前缀索引和非前缀索引； ? 前缀索引最前面的栏位是分区键栏位，非前缀索引相反； · 在这两种分类方法的四种组合中，只有三种有效（局部前缀索引，局部非前缀索引，全局前缀索引），不存在全局非前缀索引； · 分区表给CBO带来很多选项，如分区排除，并行分区连接等。 第三章 SQL 调整 7.簇 · 簇是一个或多个表的组合，这些表的数据存储在相同的数据块中，当通过簇键查询这些表时，只需读一个数据块就能返回连接的多个表的数据； · 有两种簇，索引簇和哈希簇； · 符合下面的条件时适合使用簇： ? 表初始装载以后，很少或者没有DML发生； ? 针对每一条主表记录，从表中的记录数大致相同并且范围可知； ? 更多地是对簇中的表一起查询，较少单独查询（适于索引簇）； ? 查询WHERE条件中使用针对簇键的相等匹配（适于哈希簇）。 · 先创建簇，建簇时系统分配簇段，再在表创建时将表指定到簇，建表时不能指定表空间，所需空间从簇段中分配； · 建簇时需指定簇键，不管有多个表或者多少条记录，同一键值在簇中只存储一次； · 建簇时指定SIZE关键值，这个值定义与一个簇键关联的记录占用的空间之和； · 索引簇的关键字是INDEX（这是默认值），哈希簇的关键字是HASHKEYS（这个子句后跟一个整数指定簇的哈希值个数，哈希值是簇键值通过簇哈希函数计算后的返回值，哈希值相同的行会一起存储）； · 索引簇在建簇后必须再建簇键栏位的索引，这时系统再分配一个索引段（不明白为什么系统不把这两步联起来，而要这样多此一举？）；哈希簇上不能建针对簇键的索引； · 针对索引簇表的查询和普通表相似，可能也是先索引再簇；针对哈希簇表的查询，如果WHERE条件中用到键栏位的相等匹配，用与插入时相同的哈希算法处理这个键值可以得出查询数据的位置，只需一个IO即可取得需要的数据； · 使用簇时必须小心规划和测试，否则反而会给性能带来负面影响。 来自http://www.itpub.net/422928,2.html

第四章 调整共享池

1.理解共享池

理解共享池
共享池是SGA的一部分，用来缓存SQL和PL/SQL语句，共享池用LRU算法来管理。

语句缓存的好处
· ORACLE执行SQL语句时，先将SQL语句的字串通过一个哈希算法得出一个哈希值，然后检查共享池中是否已存在这个哈希值，若有就用已缓存的执行计划来执行这个语句（CACHE HIT 缓存命中），若没有（CACHE MISS 缓存缺失）则需进行解析，解析需要完成下面的工作：
? 语法检查；
? 语义检查，看参考对象是否存在，类型是否正确；
? （如果是CBO优化模式）收集参考对象的统计；
? 检查用户的权限是否足够；
? 从许多可能的执行路径中选择一条作为执行计划；
? 生成语句的编译版本（P-CODE）。
· 解析是一个昂贵的操作，因为过程中需要消耗许多资源；
· 最大化CACHE HIT是调整共享池的目标

共享池的组件
Library Cache （库缓存）
· 库缓存用来存放最近执行过的SQL和PL/SQL（包括过程，函数，包，触发器，匿名块和JAVA类），包括以下组件：语句文本，哈希值，P-CODE，相关的统计，执行计划；
· 与库缓存相关的动态性能视图如下表：
视图名 描述
V$SQL 缓存SQL的统计（IO，内存等），是根椐SQL文本和执行计划来分组的
V$SQLAREA 缓存SQL的统计（IO，内存等），是根椐SQL文本来分组的
V$SQLTEXT 缓存SQL的完整文本，可能包括多行
V$SQL_PLAN 缓存SQL执行计划的详细内容，相同的SQL文本可能有不同的执行计划

Data Dictionary Cache （数据字典缓存）
· 在处理SQL或PL/SQL语句时，系统要检查数据字典以确定需要的表是否存在，栏位名与数据类型是否正确，用户是否有足够的权限，这部分数据缓存在共享池的数据字典缓存中，这块内存区域同样用LRU算法来管理；
· Oracle将数据字典缓存与库缓存分开的原因是：
? 用与库缓存独立的LRU算法来管理字典数据，可以使得这部分数据在内存中保持更长的时间；
? 后续用户发出与以前用户类似的语句时，也能在数据字典缓存中找到需要的数据。

User Global Area （用户全局域）
· 只有在共享服务器选项下，共享池中才存在UGA；
· UGA用来缓存会话信息（在共享服务器选项下，一个会话的多条SQL或者PL/SQL语往往是由不同的服务器进程来完成的，因此有必要将会话信息存放在共享的位置；在专用服务器配置下，用户会话信息存放在PGA中）。

2.测量共享池的性能

测量共享池的性能
· 共享池的性能主要用库缓存命中率和数据字典缓存命中率来测量，前者更重要，Oracle建议在未调整好库缓存之前，不必费力去调整数据字典缓存。

测量库缓存的性能
· 库缓存的性能指标来自V$LIBRARYCACHE视图，这个视图的主要字段描述如下：
字段名 描述 可接受值(OLTP)
NAMESPACE 主要的类型有SQL AREA (SQL语句), TABLE/PROCEDURE (存储过程或函数), BODY (包体), TRIGGER (触发器)
GETS 解析次数
GETHITS 解析时发现语句的解析版本已存在于库缓存中（解析命中）的次数
GETHITRATIO 解析命中率 大于90%
PINS 执行次数
PINHITS 执行命中次数
PINHITRATIO 执行命中率 大于90%
RELOADS 重新解析的次数，重新解析的原因是已缓存的解析版本老化或失效 sum(reloads)/sum(pins) < 1%
INVALIDATIONS 语句缓存失效的次数，失效的原因是依赖对象被修改删除或者编译

· STATSPACK中存放关于库缓存性能的数据有两个地方：
? Instance Efficiency Percentages (Target 100%)（实例命中率） – Library Hit %；
? Library Cache Activity for DB（库缓存活动）。

测量数据字典缓存的性能
· 数据字典缓存性能指标来自V$ROWCACHE视图，用下面的查询来获得命中率：
select 1 – (sum(getmisses) / sum(gets)) from v$rowcache;可以接受的命中率是85%以上；
· STATSPACK中关于数据字典缓存性能的数据存放在Dictionary Cache Stats for DB（数据字典缓存统计）。

3.改进共享池的性能

改进共享池的性能
改进共享池的性能就是提高库缓存和数据字典缓存的命中率，有以下一些方法：

增大共享池
· 增大共享池能减慢库缓存和数据字典缓存被LRU算法移出的速度，从而提高命中率；
· 库缓存命中率和数据字典缓存命中率通常都趋向于一致，极少出现一高一低的情形；
· 共享池的大小由初始化参数SHARED_POOL_SIZE来确定；
· 已使用库缓存的大小可以通过sum(V$DB_OBJECT_CACHE.SHARABLE_MEM)（非SQL对象）和 sum(V$SQLAREA.SHARABLE_MEM)（SQL语句） 来查询；
· 可以用ALTER SYSTEM SET SHARED_POOL_SIZE = XX; 来动态更改共享池的大小，但需保证更改后SGA的大小不大于SGA_MAX_SIZE，否则会现ORA-04033的错误；
· 也可以通过关机更改初始参数SHARED_POOL_SIZE再重启来手工更改共享池的大小，这种方法适于同时改大SGA_MAX_SIZE这个参数；
· 无统计数据时，可以按照下面的策略来为ORACLE服务器分配内存：
? 操作系统（NT需要的较UNIX多），ORACLE后台进程，服务器进程，其它非ORACLE进程共需内存= 服务器总内存 * 45%（1G以下时） or 25~40%（1G以上时）；
? TSGA（SGA总内存） = 服务器总内存 * 55%（1G以下时） or 60~75%（1G以上时）；
? TSGAI（单个实例的SGA内存） = TSGA / 服务器上的实例数；
? 共享池 = TSGAI * 45%；
? 数据缓存 = TSGAI * 45%；
? 日志缓存 = TSGAI * 10% （10%的日志缓存通常都偏大，可待有统计数据后视情形将多出的部分分给共享池，数据缓存或者大池）；
? 若需配置大池和JAVA池时，从本实例SGA总内存中分配。

为大的PL/SQL语句留空间；
· 执行大的PL/SQL程序时，为了获得足够的缓存空间，LRU算法会移出许多已解析的语句，尔后这些刚被移出的语句可能又要重新装入，这样就降低的库缓存的命中率，为了避免这样的情形，可以设置共享池保留区给大的PL/SQL对象使用；
· 参数SHARED_POOL_RESERVED_SIZE用来指定共享池保留区的大小，最大可指定到共享池的一半，默认值是共享池的5%，ORACLE建议从共享池的10%开始调整；
· 可以从V$DB_OBJECT_CACHE查到目前缓存对象及其使用空间（OWNER, NAME, SHARABLE_MEM）；
· 可以用V$SHARED_POOL_RESERVED视图来监控保留区的使用情况以决定正确的保留区大小：
? REQUESTS 从保留区中请求空间的次数；
? REQUEST_MISSES 保留区无空间可用而需要从非保留区清洗对象的请求次数；这个值为零或者一直很稳定表示保留区空间设置偏大；
? FREE_SPACE 保留区未用的空间；这个空间大于保留区的一半时表示保留区空间设置偏大；
? REQUEST_FAILURES 请求无法满足，出现ORA-04031错误的次数； 这个值非零或者稳定增加时表示保留区太小；
? 隐含参数_shared_pool_reserve_min_alloc（4400）确定请求保留区的起始大小；
? 调整保留区的目标是使REQUEST_MISSES, REQUEST_FAILURES接近零。
· 可以用DBMS_SHARED_POOL.ABORT_REQUEST_THRESHOLD这个包过程设置一个阀值，大于这个值的库缓存请求将会失败。

Keep PL/SQL
· 将重用率高的PL/SQL代码KEEP在库缓存中可以提高命中率，这个过程又称Pinning，由DBMS_SHARED_POOL.KEEP过程来完成，这些对象存放在共享池保留区中；
· 清洗共享池语句（ALTER SYSTEM FLUSH SHARED_POOL;）并不会清洗Pinned对象，但是系统重启后，这些对象将不再是Pinned。
· Keep相关：
? DBMS_SHARED_POOL这个包并没有在运行catproc.sql时安装，需运行一次dbmspool.sql这个脚本；
? 可以用这个包中的KEEP, UNKEEP来pin 和unpin存储对象；
? 可从V$DB_OBJECT_CACHE.KEEP这个栏位得知哪些对象已被Pinned。
· 要确定哪些对象适于KEEP，可以激活审计功能来查看是哪些对象被频繁地调用，要KEEP匿名的PL/SQL程序比较麻烦，建议将经常使用且长度超过500个字符的匿名块写成过程和包再进行KEEP；
· 因为重启后所有的KEEP都将失效，所以将KEEP的动作放在系统触发器中（AFTER STARTUP ON DATABASE）是较合适的。

代码重用；
· 确定是否需要对语句进行（硬）解析时，是先比较语句的哈希值，下面的两种方法有助于获得相同的哈希值，从而可以实现重用代码，提高命中率：
? 开发组的所有成员都使用相同的编码规范（包括大小写，空格，换行等）；
? 使用绑定变量（提高命中率的同时可能会产生不够好的执行计划，因为优化器不知道变量的确定值，在有栏位的柱状图统计数据时也不能够利用）。

调整相关初始化参数。
OPEN_CURSORS
· 这个参数指定每个用户会话能打开的游标个数；
· 增大这个值可以减少重新解析会话曾打开的语句的机会，提高命中率，但需要更大的共享池空间。

CURSOR_SPACE_FOR_TIME
· 这个参数设为真时，只有当所有引用共享SQL的游标都关闭后，LRU才有可能移出这个SQL所占的缓存空间来重用，默认值是FALSE；
· 只有在共享池足够大的情况下才能考虑设为真，设为真时可以减少重解析，提高命中率，加快游标的执行（空间换时间）。

SESSION_CACHED_CURSORS
· 这个参数指定会话能够缓存游标的个数，默认值是零；
· 如果会话反复执行某些语句，设置该值大于零能提高游标的执行速度，这些缓存也是用LRU算法来管理的。

CURSOR_SHARING
· 这个参数决定什么样的SQL语句能够共享游标，有三个取值：FORCE, SIMILAR, EXACT；
? FORCE 两个语句的差异只在字面值，这些差异不会改变语句的含义时可共享游标；
? SIMILAR两个语句的差异只在字面值，这些差异不会改变语句的含义和执行计划时可共享游标（这个值是在9i 中引入，如果有差异的栏位上有柱状图统计，这时执行计划会改变，不能共享游标，行为同于EXACT，如果差异栏位上没有柱状图统计，执行计划相同，这时的行为同于FORCE）；
? EXACT 两个语句必须精确匹配才能共享游标，这是默认值。

第五章 调整数据缓存 1.理解数据缓存

理解数据缓存
· 数据缓存是SGA的一部分，用于存放用户最近存取过的段的数据块的副本，这些段可能是数据段，索引段，簇段，LOB段，LOB索引段，回滚段，临时段，数据缓存单元的大小与数据库块大小一致。
· 数据缓存用下面的方法进行管理：

LRU列表
· 在Oracle执行SQL语句的过程中，相关的段数据要复制到SGA的数据缓存中来，这个操作由用户的服务器进程来执行；
· 与共享池类似，数据缓存也是由LRU算法来管理的，当数据缓存的缓存块被填满而又有新的缓存块请求时，LRU将最近最少使用的缓存块老化出去，而保留最近经常使用的缓存块，当一个用户发现要读取的数据块已在之前由其它用户读入时，就可以节约很多时间，因为从内存中读取比从磁盘中读取要快上数千倍；
· LRU算法管理一个LRU列表，这个列表类似于一个输送带，服务器进程将刚刚存取过的数据块放在输送带的开始端，随着更多的块被读入，先前读入的块向输送带末端移动，如果在到达末端之前某个块被再一次存取，则这个块又被移到开始端，否则就会从输送带末端跌落（缓存块被老化）；
· LRU对全表扫描时读入缓存块的管理与上面的有些不同，这些块一读入就被放在LRU列表的末端，这样可以避免对大表全表扫描时把数据缓存中的所有缓存块都清洗出去；
· LRU算法管理下的缓存块有四种状态：
? Free 自数据库启动以来尚未被使用过的缓存块；
? Pinned 正在被服务器进程使用的缓存块；
? Clean 曾被使用过且可立即被重用的缓存块，读入后未经修改或者最近一次修改已被写回磁盘，缓存版本与文件数据块一致；
? Dirty 曾被使用过且不能立即被重用的缓存块，读入后作过修改且最近一次修改未被写回磁盘，缓存版本与文件数据块不一致；
· 管理脏块时会用到一个脏块列表（Dirty List，又名写列表），这个列表被检查点队列使用，用于跟踪所有的脏块，以第一次修改时间排序，脏块由DBW0进程写回磁盘。

用户服务器进程
· 当需要读取某个数据块时，服务器进程先到数据缓存中查看该块是否已存在，若没有找到，就需要将数据块从数据文件读到数据缓存中来，这首先要在数据缓存中找到一个可用的缓存块来容纳数据块的副本，这个过程中服务器进程可能要和LRU列表以及脏列表打交道：
? 在LRU列表上查找可用块的时候，服务器进程将查到的脏块从LRU列表移到脏列表；
? 随着脏块的加入，脏列表不断变长，当长度超过某个预定义的长度时，触发DBW0将脏列表上的脏块写回磁盘；
? 如果服务器进程在LRU列表查找很多的块（超过某个阀值）都没能找到可用的块时，触发DBW0进程，将脏块直接从LRU列表写回磁盘。
· 如果服务器进程发现要找的块已在数据缓存中，但块的版本要晚于一致性读需要的版本时（版本更早时可直接使用），服务器进程在数据缓存中创建一个新块利用回滚段数据回滚到需要的版本（如果是在序列化事务中，且造成更晚版本的已提交的修改不是由当前事务造成的话，则报错）。

数据库写入进程（DBW0）
· DBW0进程负责将数据缓存中的脏块写回磁盘，这个操作在下面的情形下发生：
? 服务器进程不断地将脏块从LRU列表移到脏列表，当脏列表的长度达到阀值时，DBW0将脏列表上的脏块写回磁盘；
? 服务器进程在LRU列表上检查太多的块都没能找到一个可用块的时候，DBW0直接从LRU列上将脏块写回磁盘；
? DBW0进程每三秒被激活一次，将LRU列表上的脏块移到脏列表，若脏列表长度达到阀值时，从脏列表上将脏块写回磁盘；
? 检查点发生时，DBW0将脏块从LRU列表移到脏列表，再从脏列表写脏块回磁盘；
? 数据库关闭时（不包括Shutdown Abort），DBW0将所有脏块写回磁盘；
? 表空间热备前，DBW0将属于这个表空间的所有脏块从LRU列表移到脏列表，然后从脏列表将脏块写回磁盘；
? 表空间离线时（Normal，Temporary），DBW0将属于这个表空间的所有脏块从LRU移到脏列表，然后从脏列表将脏块写回磁盘；
? 删除段时，DBW0先将这个段的脏块写回磁盘。

第五章 调整数据缓存 2.测量数据缓存的性能

数据缓存命中率
· 数据缓存命中率由下面四个来自V$SYSSTAT的统计值计算得来：
? physical reads 从磁盘读取的块数；
? physical reads direct 从磁盘读取不进入数据缓存的数据块数；
? physical reads direct (lob) 从磁盘读取不进入数据缓存的LOB对象块数；
? session logical reads 从数据缓存中读取的块数，包括当前读（Current, db_block_gets）和一致性读(consistent gets)；
· 命中率 = 1- ((physical reads – physical reads direct – physical reads direct (lob)) / session logical reads)，调整好的OLTP系统，数据缓存命中率应该在90%以上；
· 查询语句如下：
select 1 – ((physical.value – direct.value – lobs.value) / logical.value)
from v$sysstat physical,
v$sysstat direct,
v$sysstat lobs,
v$sysstat logical
where physical.name = ‘physical reads’
and direct.name = ‘physical reads direct’
and lobs.name = ‘physical reads direct (lob)’
and logical.name = ‘session logical reads’;

数据缓存等待
· free buffer inspected 用户服务器进程为了将数据从磁盘读到数据缓存，在数据缓存中查找可用的块，在找到可用块之前共查找过的缓存块数；
· free buffer waits 用户服务器进程为了将数据从磁盘读到某个脏块，需等待DBW0将这个脏块写回磁盘；
· buffer busy waits 用户服务器进程要存取某个缓存块，如果有其它会话正在使用它，必须等待其完成，可能的原因有：
? 并行插入时表的Free lists不足引发段头等待（改用LMT可消除此影响）；
? 并发事务较多时表的InitTrans太小引发事务等待（增大表的InitTrans）；
? 回滚段不足时引发undo header等待（增加回滚段的个数）；
· 一起查询时用下面的语句：
select name,value from v$sysstat where name = ‘free buffer inspected’
union
select event,total_waits from v$system_event where event in (‘free buffer waits’,’buffer busy waits’);

Statspack中存放数据缓存性能数据的位置
· Instance Efficiency Percentages (Target 100%)（实例命中率） – Buffer Hit %；
· Instance Activity Stat (实例的活动统计) – free buffer inspected， physical reads， physical reads direct， physical reads direct (lob)， session logical reads；
· Buffer Pool Statistics （数据缓存统计） -- free buffer waits, buffer busy waits。

第五章 调整数据缓存 3.增大数据缓存

改进数据缓存的性能
改进数据缓存的性能有下面一些方法：增大数据缓存，用多个数据缓存池，将小表CACHE到数据缓存，绕过缓存池，正确地使用索引

增大数据缓存
· 改进数据缓存性能最简单的方法就是增大这块内存，加大数据缓存后，缓存块就可以在LRU列表上呆更多的时间，从而提高命中率，free buffer inspected， buffer busy waits， free buffer waits这三个统计值也会相应降低；
· 与数据缓存相关的初始参数有下面的一些：
? DB_BLOCK_SIZE 主块字节数，在数据库创建时指定，不能更改，SYSTEM和TEMP表空间只能使用主块创建，后面提到的调整方法主要是针对主块缓存池来进行讨论的；
? DB_CACHE_SIZE 指定默认缓存池的大小，不能为零，用于存放主块；
? DB_KEEP_CACHE_SIZE 指定保持缓存池的大小，默认值为零，用于存放主块；
? DB_RECYCLE_CACHE_SIZE 指定回收缓存池的大小，默认值为零，用于存放主块；
? DB_nK_CACHE_SIZE 默认值为零，这些参数指定用于容纳不同于主块大小的数据块的缓存空间，创建其它表空间时可以使用与主块不同的块大小（先设置好对应参数再创建表空间，存在对应的表空间时，这些参数不能再更改回默认值），不要更改与主块大小相同的那个参数的默认值。
· 动态修改数据缓存的大小；可以用ALTER SYSTEM命令动态修改数据缓存的大小，但必须记住下面三条规则：
? 最终生效的大小是最小粒度的整数倍；
? 共享池，数据缓存，日志缓存之和不能大于SGA_MAX_SIZE的值；
? DB_CACHE_SIZE不能指定为零。
· 手工更改数据缓存的大小：关闭实例，更改参数，再重启，这种方法适于同时更改SGA_MAX_SIZE；
· 确定数据缓存的合适大小：用Buffer Cache Advisory
? 设置参数DB_CACHE_ADVISE的值为ON（设置为OFF时关掉ADVISORY，设为READY时预先给ADVISORY分配内存，但不分配CPU，以防将该参数改为ON时出现内存不足的错误）；
? 待ADVISORY运行一段时间（30分钟以上）后，再查询V$DB_CACHE_ADVICE，相关字段的意义如下表：
ü ID 缓存池编号
ü NAME 缓存池的名字
ü BLOCK_SIZE 块大小
ü ADVISE_STATUS 参数的状态：ON， OFF， READY
ü SIZE_FOR_ESTIMATE 评估缓存大小（M）
ü ESTD_PHYSICAL_READS 评估的物理读
? 评估的物理读趋于稳定时的评估缓存就是最合适的大小。

第五章 调整数据缓存 4.用多个数据缓存池

用多个数据缓存池
· 默认情况下，数据缓存中只存在一个（默认）缓存池，有可能出现这样的情形，某个用户程序偶尔存取一个很少使用的大表（非全表扫描），将缓存中其它用户经常使用的缓存块挤出，为了解决这个问题，可以定义多个缓存池，将段明确地指定给某个缓存池；
· 可以创建三种缓存池，根椐段被存取频率的不同将段指定给不同的池：
? Keep Pool 用于存放你想在缓存中保留时间尽可能长的段；
? Recycle Pool 用于存放你想在缓存中保留时间尽可能短的段；
? Default Pool 存放不同于上面的其它的段；
· 与8i不同，9i各个池的内存分配是相互独立的；
· 确定哪些段适于存放在哪个缓存池中；
? 要确定段与缓存池的对应关系，需要对应用程序，段及段的存取方式有一个整体的了解；
? 了解目前已缓存的段的信息有助于确定段与缓存池的关系，V$BH， V$CACHE中都含有缓存的详细数据，下面两个查询作用类似，都能确定已缓存块的段名，所属用户名，段类型及缓存块数：
ü Select Obj.Owner,
Obj.Object_Name,
Obj.Object_Type,
Count(Distinct Bh.Block#) "Num of Buffers"
From V$bh Bh, Dba_Objects Obj
Where Bh.Objd = Obj.Object_Id
And Owner <> ’SYS’
Group By Obj.Owner, Obj.Object_Name, Obj.Object_Type;
ü Select u.Username,
c.Name,
c.Kind,
Count(Distinct c.Block#) "Num of Buffers"
From V$cache c, Dba_Users u
Where c.Owner# = u.User_Id
And u.Username <> ’SYS’
Group By u.Username, c.Name, c.Kind
? 推荐经常使用且小于默认缓存池10%的段置于Keep池中；
? 推荐只由个别事务使用且大于默认缓存池200%的段置于Recycle池中；
· 确定每个池的合适大小；在确定了段和各个池的对应关系后，可以通过DBA_SEGMENTS.BLOCKS查出对应池的段块之和，计算出各个池占用比例，再根椐缓存池的总大小可以确定每个池的大小；在末经精确计算的情况下，ORACLE推荐先按下面的比例为各个池分配空间：Default Pool 60%， Keep Pool 30%， Recycle Pool 10%。
· 创建缓存池；根椐前面提到的方法，动态或手工更改这三个参数（DB_CACHE_SIZE， DB_KEEP_CACHE_SIZE， DB_RECYCLE_CACHE_SIZE），可以创建缓存池；
· 将段指定给缓存池；
? 可以在创建段时指定缓存池：Create … STORAGE ( BUFFER_POOL pool);
? 也可以更改段的缓存池属性：Alter … STORAGE ( BUFFER_POOL pool);
? 上面的pool可选值为KEEP， RECYCLE， DEFAULT（默认值）；
? DBA_SEGMENTS.BUFFER_POOL字段可以查到段的缓存池属性；
? 更改段的缓存池属性并不会立即生效，需等到段下一次从磁盘载入缓存池时才能生效。
· 监控各个缓存池的性能；
? V$BUFFER_POOL性能视图： Name（缓存池的名字）， Block_Size（池的块大小，Byte）， Current_Size（缓存池的当前大小，M）。
? V$BUFFER_POOL_STATISTICS，包含每个缓存池的相关统计，可用来计算单独缓存池的命中率，重要的字段如下：
ü NAME
ü DB_BLOCK_GETS
ü CONSISTENT_GETS
ü PHYSICAL_READS
ü FREE_BUFFER_INSPECTED
ü BUFFER_BUSY_WAITS
ü FREE_BUFFER_WAITS
ü Select Name "Buffer Pool",
1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets)) "Hit Ratio"
From V$buffer_Pool_Statistics
Where Db_Block_Gets + Consistent_Gets > 0;
ü 经过调整后各个缓存池的命中率，Keep池的应该很高，Recycle池的应该很低，Default池应该为70~80%。
· 命中率高并不总是代表性能好；
? 单纯的缓存命中率高并不代表缓存的性能很好，在RBO下，不正确的建立索引可能导致性能低下而缓存命中率却相当高，比如在一个经常使用表的低集势栏位上建二叉树索引就可能出现这种情形，系统不停而低效地扫描索引，导致出现虚高的缓存命中率；
? 通过分析V$BH，V$CACHE的查询结果，结合对应用程序工作方式的理解，可以发现这种情况；
? 按下面的处理可以消除这种可能：
ü 收集方案的统计并使用基于CBO的优化器；
ü 创建在Where条件中使用率高的栏位的柱状图统计；
ü 正确地使用多个缓存池。

第五章 调整数据缓存 5.其它提高数据缓存性能的方法

将小表CACHE到数据缓存
· 在多个缓存池的配置下，每个缓存池都是由独立的LRU算法来管理的，通常情况下刚存取的块都是放在LRU列表的开始端，而FTS（全表扫描）例外，刚读入的块放在LRU列表的末端；
· 上述算法会产生如下一些困境：CBO在读取一些小表或者读取表中的较多数据时会使用FTS，这时，即使这些表使用频率很高，也会因为上面提到的原因而很快被老化，在下次用到时又得重新读入；
· 为了解决这个难题，引进了Cache表的概念，将表设为Cache后，对表的FTS不再是将块放在LRU列表的末端，而是置于LRU列表的开始端；
· 有下面三种方法将表设为Cache：
ü 创建时指定CACHE关键字：Create table … cache;
ü 更改时指定CACHE关键字：Alter table … cache;
ü 查询时指定CACHE hint：select /*+ cache */ * from …;
· 从DBA_TABLES.CACHE字段可以查到由前两种方法设置的表的CACHE属性。

绕过缓存池
· 下面两种特定的操作可以绕过缓存池，读入的数据块不进入数据缓存，而存放在内存中由用户服务器进程控制的其它区域（UGA，PGA？）：
ü Sort Direct Writes（第八章调整IO时会讲到）；
ü Parallel DML（由多个服务器进程来执行一个DML操作，只有当系统中有多个CPU，多个IO子系统以及足够内存时才可考虑作这样的设置，并行DML超出本书讨论的范围）。

正确地使用索引
· 不正确的全表扫描会极大地降低数据缓存的命中率，创建正确的索引后可以提高数据缓存性能；
· 建议尽可能地在外键上创建索引，这可以提高多表连接的效率。

第六章 调整SGA的其它区域 1.共享服务器的概念

第六章 调整SGA的其它区域
· 已讨论过的共享池，数据缓存和下一章将要讨论的日志缓存是每一个ORACLE环境下的必备组件，这一章讨论SGA的另外三个可选组件：共享服务器，大池，Java池。

共享服务器的概念
· 每一个Oracle应用程序有两个与之相联的进程，一个是运行在客户端机器或者应用服务器上的用户进程，一个是运行在Oracle服务器上的服务器进程；
· 默认的专用服务器配置下，每一个服务器进程只专职于处理一个用户会话的请求，在Unix上，服务器进程是一个可用ps命令观测到的单独的进程，在Windows系统上，服务器进程是一个运行于oracle.exe进程中的线程；
· 另有一种配置称为共享服务器，少数一些服务器进程为所有的用户进程服务，在9i以前的版本，这种配置也称为MTS（多线程服务器）；
· 专用服务器和共享服务器的差别有点类似于外出时坐专车和打的士的区别，专用服务器能更有效地实现用户进程和实例间的通讯，但资源利用率低，共享服务器资源利用率高，在相同的CPU和内存条件下可以支持更多的并发用户，但配置不当时会引起用户进程较长时间的等待从而降低系统的性能和响应速度。

第六章 调整SGA的其它区域 2.共享服务器的架构

共享服务器的架构
共享服务器配置包含以下组件：
· 用户进程（User Process） 用户进程可能由客户端软件产生，也可能通过中间组件产生，用于管理到Oracle服务器的连接；
· 网络监听进程（Oracle Net Listen Process） 监听进程用于监听来自用户进程的对数据库的查询或DML请求，并将这些请求分配给当前比较清闲的某个调度进程；
· 调度进程（Dispatcher Process） 调度进程是Oracle的后台进程，用于接受来自用户进程的请求并返回结果，最多只能有五个同时运行的调度进程，在Unix上可以用ps命令看到，用Unix kill命令杀掉一个调度进程时会同时断开这个进程负责的所有用户进程；
· 请求队列（Request Queue） 请求队列用于存放调度进程接受到的请求，存在于SGA中，一个实例只有一个；
· 共享服务器进程（Shared Server Process） 共享服务进程是Oracle的后台进程，用于与SGA交互来处理用户进程的请求，功能与专用服务器类似（用于解析SQL语句，将数据块从磁盘读入数据缓存，等等），服务器进程的最大数量由操作系统指定；
· 响应队列（Response Queue） 响应队列用于存放共享服务器进程处理的结果，存在于SGA中，每个调度进程对应一个，调度进程将这些结果返回请求的用户进程；
· 必须满足下面条件时才可配置成共享服务器：
? 众多的应用程序用户； 一般并发用户数在200以上时需要考虑，具体要看服务器的CPU和内存资源；
? 事务时间短；长事务会造成少数用户独占服务器进程，而使其它用户长时间地等待，不适于配置成共享服务器。
? 不连续的事务；用户活动间有时常的停顿，通常OLTP事务就是如此，这时服务器进程能为其它用户提供服务。

第六章 调整SGA的其它区域 3.配置共享服务器

配置共享服务器
· 配置共享服务器时需要正确地设置下面几个初始化参数，这些参数用于指定实例启动时产生的调度进程和共享服务器进程的数量，以及运行期间如何管理这些进程；
? DISPATCHERS 指定实例启动时产生的对应网络协议的调度进程数量，可选值是0-5；
? MAX_DISPATCHERS 指定实例允许的最大调度进程数，默认值为5，调度进程的增减必须由管理员手工指定（Alter system set dispatchers = ?）；
? SHARED_SERVERS 指定实例启动时产生的共享服务器进程数量，设为零时禁用共享服务器选项，最小值为1，最大值依赖于操作系统；
? MAX_SHARED_SERVERS 指定实例允许的共享服务器的最大数量，最大值依赖于操作系统，服务器进程可由PMON后台进程根椐系统负载来动态增减；
? CIRCUITS 用于指定网络连接的虚拟电路最大数量；
? PROCESSES 用于指定实例允许的进程的最大数量；
· 管理员执行管理操作时不能使用共享服务器，必须启动专用的服务器进程。

第六章 调整SGA的其它区域 4.测量共享服务器的性能

测量共享服务器的性能
· 测量共享服务器配置的性能可用到下面一些性能视图：
? V$SHARED_SERVER 共享服务器进程的详细统计，包括每个进程的空闲和繁忙时间；
? V$QUEUE 包含请求队列和响应队列的信息；
? V$SHARED_SERVER_MONITOR 关于共享服务器进程的合计信息，包含经由PMON启动和终止的共享服务器进程数，共享服务器进程的高水位，并发会话的最大数量，虚抑电路的最大数量；
? V$DISPATCHER 调度进程的详细统计，包括每个进程的空闲和繁忙时间；
? V$DISPATCHER_RATE 包含调度进程服务的历史和实时的统计；
? V$CIRCUIT 包含连接路径（用户进程和调度进程，共享服务器进程的关系）的统计，
· 测量共享服务器进程的性能
? 共享服务器进程的忙百比例：
Select Name,
Decode(Busy + Idle, 0, 0, Round((Busy / (Busy + Idle)) * 100, 4)) "busy_rate"
From V$shared_Server
Where Status != ’QUIT’;

? 用户请求在请求队列中的平均等待时间(百分之一秒)
Select Decode(Totalq,
0,
’TOTALQ IS ZERO’,
Round(Sum(Wait) / Sum(Totalq), 4)) "AVG SHARED_SERVER WAIT"
From V$queue
Where Type = ’COMMON’
Group By Totalq;

? 当上面的数值很大或者不断上升时需要考虑增加SHARED_SERVERS的值；

· 测量调度进程的性能
? 调度时程的忙百分比（当这个比例超过50时，需要增加DISPATCHERS 的值）：
Select Name "Dispatcher",
Network,
(Round(Sum(Busy) / (Sum(Busy) + Sum(Idle)), 4)) * 100 "Busy_Rate"
From V$dispatcher
Group By Name, Network;

? 用户请求等待调度进程调度的平均时间(百分之一秒)
Select Decode(Totalq,
0,
’TOTALQ IS ZERO’,
Round(Sum(Wait) / Sum(Totalq), 4)) "AVG DISPATCHER WAIT"
From V$dispatcher d, V$queue q
Where d.Paddr = q.Paddr
And q.Type = ’DISPATCHER’
Group By Totalq;

? 调度进程服务的连接数量（当前，历史最高以及差异）
Select Name,
Cur_In_Connect_Rate,
Max_In_Connect_Rate,
Max_In_Connect_Rate - Cur_In_Connect_Rate "VARIANCE"
From V$dispatcher_Rate;

· 查询共享服务器环境下的累积活动：
Select * From V$shared_Server_Monitor;
· 查询用户进程，调度进程和共享服务器进程间的对应关系：
Select s.Username, d.Name "DISPATCHER", Ss.Name "SHARED_SERVER"
From V$circuit c, V$session s, V$dispatcher d, V$shared_Server Ss
Where c.Saddr = s.Saddr
And c.Dispatcher = d.Paddr
And c.Server = Ss.Paddr;

第六章 调整SGA的其它区域 5.改进共享服务器的性能

改进共享服务器的性能
· 共享服务器环境下的性能调整方向是，在现有的资源配置下支持更多的用户，而不是一定要如何提高系统的吞吐量或响应时间；
· 性能问题表现在以下三个方面：相关的SGA组件配置不充分，共享服务器进程太少，调度进程太少；
· 相关的SGA组件配置
? 共享服务器配置下，用户的会话与游标信息是存放于UGA中的（专用服务器配置下存放于PGA中），UGA通常存在于共享池中，共享池的主要功能是用来缓存SQL语句及数据字典的，这就要求共享池有足够的空间，不致于因UGA的消耗而降低系统的库缓存命中率和字典缓存命中率；
? 下面的查询语句可以看到系统UGA占用的空间，这个空间占用是动态变化的：
Select Sum(s.Value) "Total UGA Bytes"
From V$sesstat s, V$statname n
Where s.Statistic# = n.Statistic#
And n.Name = ’session uga memory max’;
? 在配置了大池后，部分UGA数据会从SGA中移到大池中；
· 有三种改变共享服务器进程数量的方法：
? PMON后台进程能够根椐系统的负载动态增减服务器进程的数量；
? 管理员动态增加服务器进程的数量：Alter system set shared_servers = xx;
? 管理员手工增加服务器进程的数量：更改初始参数shared_servers的值，再重启；
· 改变调度进程数量的方法只有两种
? 管理员动态增加调度进程的数量：Alter system set dispatchers = ‘tcp,5’;
? 管理员手工增加调度进程的数量：更改初始参数dispatchers的值，再重启；
? 用户进程在其生命周期里只和一个调度进程发生联系，新加入的调度进程只能为在它之后产生的用户进程服务；
? Oracle推荐配置是，每个调度进程服务250个左右的并发用户连接；

第六章 调整SGA的其它区域 6.大池的概念

大池的概念
· 共享池除了用于缓存SQL外，还有用于容纳下面的一些特殊用途的数据：DBW0附属进程，UGA，RMAN，Parallel Query，当这些选项被使用后，共享池的命中率可能会下降很多，为了解决这个问题，Oracle提供一个新的内存区域来存放这些数据，这就是大池；
· 用LARGE_POOL_SIZE这个参数来配置大池，这个参数默认值是零，可选值在600K到2G之间，一旦配置了大池，Oracle会自动使用大池来缓存上面提到的数据；
· PARALLEL_AUTOMATIC_TUNING置为真时，大池的空间是动态的，由系统控制；
· 可以使用下面的查询看到大池已使用和未使用的内存：Select * From V$sgastat Where Pool = ’large pool’;
· 持续观察上面的查询，如果发现未使用内存很大或者不断增加，表示大池可能分配了太多的内存，如果未使用内存很小或者不断减少，表示可能需要加大大池的内存。

第六章 调整SGA的其它区域 7.Java池的概念

Java池的概念
· 在Oracle中配置Java环境时有以下一些参数：
? SHARED_POOL_SIZE JVM缓存在共享池中；
? JAVA_POOL_SIZE 缓存与JAVA相关的会话数据，默认值20M，取值范围是1M到1G；（Oracle推荐，对于有JAVA的应用，将这个值设到50M或者更大）
? JAVA_SOFT_SESSIONSPACE_LIMIT 当某个JAVA进程请求的内存超过这个限制时，会写一条消息到用户跟踪文件，默认值是0，最大值是4G；
? JAVA_MAX_SESSIONSPACE_LIMIT 当JAVA进程请求的内存超过这个参数的限制时，返回ora-29554的错误，默认值是0，最大值是4G；
· 测量JAVA池的性能有下面两种方法：
? Select * From V$sgastat Where Pool = ’java pool’; 观察这个查询，如果发现未使用内存很大或者不断增加，表示JAVA池可能分配了太多的内存，如果未使用内存很小或者不断减少，表示可能需要加大JAVA池的内存。
? 观察Statspack中的SGA breakdown difference，里面有JAVA池free memory的起始值和终止值，如果终止值总是很小或者接近零，表示JAVA池可能太小了；
· 改进JAVA池的性能主要是增大JAVA_POOL_SIZE这个参数，这个参数不能动态调整。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12402/viewspace-897388/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12402/viewspace-897388/