ref: http://www.itpub.net/422928.html
[@more@]第二章 调整的信息来源 Alert Log 文件中记录的与性能有关的信息: |
第三章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优化器
· 优化器的职责是从多行种执行路径中选择一种最优的执行路径; 第三章SQL 调整 3. 稳定执行计划 改善应用程序性能 第三章SQL 调整 4. 物化视图 · 物化视图用数据段存储预连接,预汇总的查询数据,物化视图可以有索引,也可以分区,物化视图主要应用于数据仓库和决策支持系统; 第三章 SQL 调整 5.索引 索引 第三章 SQL 调整 6.分区 · 分区表是将大表的数据分成称为分区的许多小的子集,9i提供四种分区方法:范围分区,列表分区,哈希分区和混合分区; 第三章 SQL 调整 7.簇 · 簇是一个或多个表的组合,这些表的数据存储在相同的数据块中,当通过簇键查询这些表时,只需读一个数据块就能返回连接的多个表的数据; |
第四章 调整共享池
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中)。
测量共享池的性能
· 共享池的性能主要用库缓存命中率和数据字典缓存命中率来测量,前者更重要,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/