<转>oracle性能调整读书笔记(3)

最新推荐文章于 2022-12-28 17:19:55 发布

cuanshenqiao1119

最新推荐文章于 2022-12-28 17:19:55 发布

阅读量134

点赞数

第三章 SQL 调整 7.簇 o? { rPFR
b_xGCB C
· 簇是一个或多个表的组合，这些表的数据存储在相同的数据块中，当通过簇键查询这些表时，只需读一个数据块就能返回连接的多个表的数据； cp g +-Zf%
· 有两种簇，索引簇和哈希簇； Y R }P ;
· 符合下面的条件时适合使用簇： A*_ |/o
Ø 表初始装载以后，很少或者没有DML发生； 3D/
Ø 针对每一条主表记录，从表中的记录数大致相同并且范围可知； E3
Ø 更多地是对簇中的表一起查询，较少单独查询（适于索引簇）； y %^TZ[S
Ø 查询WHERE条件中使用针对簇键的相等匹配（适于哈希簇）。 8F*"z^ vD=
· 先创建簇，建簇时系统分配簇段，再在表创建时将表指定到簇，建表时不能指定表空间，所需空间从簇段中分配； w` ;>+_ E7
· 建簇时需指定簇键，不管有多个表或者多少条记录，同一键值在簇中只存储一次； ) @O KL 0t
· 建簇时指定SIZE关键值，这个值定义与一个簇键关联的记录占用的空间之和； ' E#;`}&Ah
· 索引簇的关键字是INDEX（这是默认值），哈希簇的关键字是HASHKEYS（这个子句后跟一个整数指定簇的哈希值个数，哈希值是簇键值通过簇哈希函数计算后的返回值，哈希值相同的行会一起存储）； u k )6%
· 索引簇在建簇后必须再建簇键栏位的索引，这时系统再分配一个索引段（不明白为什么系统不把这两步联起来，而要这样多此一举？）；哈希簇上不能建针对簇键的索引； n :P 5 m9T
· 针对索引簇表的查询和普通表相似，可能也是先索引再簇；针对哈希簇表的查询，如果WHERE条件中用到键栏位的相等匹配，用与插入时相同的哈希算法处理这个键值可以得出查询数据的位置，只需一个IO即可取得需要的数据； 5QoU& H v
· 使用簇时必须小心规划和测试，否则反而会给性能带来负面影响。 V.k 2t$@
R:LT hFx
!U2 Wiks
第四章调整共享池 1.理解共享池 EhBYmc" &
&o y j 8

理解共享池 lVmm`q 6n9
共享池是SGA的一部分，用来缓存SQL和PL/SQL语句，共享池用LRU算法来管理。 07? |"c.
B[I a8t
语句缓存的好处 EZ.| 6oug\
· ORACLE执行SQL语句时，先将SQL语句的字串通过一个哈希算法得出一个哈希值，然后检查共享池中是否已存在这个哈希值，若有就用已缓存的执行计划来执行这个语句（CACHE HIT 缓存命中），若没有（CACHE MISS 缓存缺失）则需进行解析，解析需要完成下面的工作： Y l Y3C
Ø 语法检查； jd &kak
Ø 语义检查，看参考对象是否存在，类型是否正确； qOV 6Kh )
Ø （如果是CBO优化模式）收集参考对象的统计； B Aq@H8*B
Ø 检查用户的权限是否足够；
Ø 从许多可能的执行路径中选择一条作为执行计划；
Ø 生成语句的编译版本（P-CODE）。 vq df-i
· 解析是一个昂贵的操作，因为过程中需要消耗许多资源； s4$ Z.xwr
· 最大化CACHE HIT是调整共享池的目标 *o/ Q#
6o _t;c pT
共享池的组件 p ~T)Af
Library Cache （库缓存） !,#42 TY*X
· 库缓存用来存放最近执行过的SQL和PL/SQL（包括过程，函数，包，触发器，匿名块和JAVA类），包括以下组件：语句文本，哈希值，P-CODE，相关的统计，执行计划； X]? qn s7
· 与库缓存相关的动态性能视图如下表： 7R 4 0t 3
视图名描述 8+Y+\X ZG
V$SQL 缓存SQL的统计（IO，内存等），是根椐SQL文本和执行计划来分组的 ZiB Te,;
V$SQLAREA 缓存SQL的统计（IO，内存等），是根椐SQL文本来分组的 qwlIz/ j
V$SQLTEXT 缓存SQL的完整文本，可能包括多行 ('/5#^% R
V$SQL_PLAN 缓存SQL执行计划的详细内容，相同的SQL文本可能有不同的执行计划 Rc2|o.'y
#]ZOi` ;
Data Dictionary Cache （数据字典缓存） bg W= .s
· 在处理SQL或PL/SQL语句时，系统要检查数据字典以确定需要的表是否存在，栏位名与数据类型是否正确，用户是否有足够的权限，这部分数据缓存在共享池的数据字典缓存中，这块内存区域同样用LRU算法来管理； V + /Vk1
· Oracle将数据字典缓存与库缓存分开的原因是： W lHw\\ur
Ø 用与库缓存独立的LRU算法来管理字典数据，可以使得这部分数据在内存中保持更长的时间； ^ c1%$@ H
Ø 后续用户发出与以前用户类似的语句时，也能在数据字典缓存中找到需要的数据。 &j } \Z D
2.=3:q!H
User Global Area （用户全局域） k~#F@_
· 只有在共享服务器选项下，共享池中才存在UGA； A~u gx~S0
· UGA用来缓存会话信息（在共享服务器选项下，一个会话的多条SQL或者PL/SQL语往往是由不同的服务器进程来完成的，因此有必要将会话信息存放在共享的位置；在专用服务器配置下，用户会话信息存放在PGA中）。

第四章调整共享池 2.测量共享池的性能 4J*% $Vx v
X_XeI!, b
&G ?b |Tb2
测量共享池的性能 s
· 共享池的性能主要用库缓存命中率和数据字典缓存命中率来测量，前者更重要，Oracle建议在未调整好库缓存之前，不必费力去调整数据字典缓存。 6JB E=9d-Q
B@dA ?w.x
测量库缓存的性能 : .Vn
· 库缓存的性能指标来自V$LIBRARYCACHE视图，这个视图的主要字段描述如下： K:/%7A_{
字段名描述可接受值(OLTP) E I6K0{'&X
NAMESPACE 主要的类型有SQL AREA (SQL语句), TABLE/PROCEDURE (存储过程或函数), BODY (包体), TRIGGER (触发器) 9r!psRA:`)
GETS 解析次数 z 7OTL
GETHITS 解析时发现语句的解析版本已存在于库缓存中（解析命中）的次数 GGsD R%U
GETHITRATIO 解析命中率大于90% Au08 k}h
PINS 执行次数 Z? Y14 L~%
PINHITS 执行命中次数 V9f$ zjpw
PINHITRATIO 执行命中率大于90% gt WJ R
RELOADS 重新解析的次数，重新解析的原因是已缓存的解析版本老化或失效 sum(reloads)/sum(pins) < 1% chW 1UE
INVALIDATIONS 语句缓存失效的次数，失效的原因是依赖对象被修改删除或者编译 4% )I[-sH
qB P UB (
· STATSPACK中存放关于库缓存性能的数据有两个地方： T7_i: HU%
Ø Instance Efficiency Percentages (Target 100%)（实例命中率） – Library Hit %； [A99 e `
Ø Library Cache Activity for DB（库缓存活动）。 *^ +8_%; 1
fH[Y c>(oj
测量数据字典缓存的性能 ak] :ir`o
· 数据字典缓存性能指标来自V$ROWCACHE视图，用下面的查询来获得命中率： *m H+ + 3h
select 1 – (sum(getmisses) / sum(gets)) from v$rowcache;可以接受的命中率是85%以上； ";\na !MT
· STATSPACK中关于数据字典缓存性能的数据存放在Dictionary Cache Stats for DB（数据字典缓存统计）。 YmXh_ bk
z&x ^ Dl
第四章调整共享池 3.改进共享池的性能 ?86q8E3 ;&
H 4 l *
?Ybg zb
改进共享池的性能 P_Rh& gkuK
改进共享池的性能就是提高库缓存和数据字典缓存的命中率，有以下一些方法： S3 Q^K.e?
)?es3Ehqq
增大共享池 1< 22,
· 增大共享池能减慢库缓存和数据字典缓存被LRU算法移出的速度，从而提高命中率； $Sz@u" ig%
· 库缓存命中率和数据字典缓存命中率通常都趋向于一致，极少出现一高一低的情形； # !: u*1
· 共享池的大小由初始化参数SHARED_POOL_SIZE来确定； >uc VrLm,X
· 已使用库缓存的大小可以通过sum(V$DB_OBJECT_CACHE.SHARABLE_MEM)（非SQL对象）和 sum(V$SQLAREA.SHARABLE_MEM)（SQL语句）来查询； zVeQKN 9^Z
· 可以用ALTER SYSTEM SET SHARED_POOL_SIZE = XX; 来动态更改共享池的大小，但需保证更改后SGA的大小不大于SGA_MAX_SIZE，否则会现ORA-04033的错误； G3Z>,"w;=
· 也可以通过关机更改初始参数SHARED_POOL_SIZE再重启来手工更改共享池的大小，这种方法适于同时改大SGA_MAX_SIZE这个参数； )1E[CIaXK
· 无统计数据时，可以按照下面的策略来为ORACLE服务器分配内存： d'b AM {R>
Ø 操作系统（NT需要的较UNIX多），ORACLE后台进程，服务器进程，其它非ORACLE进程共需内存= 服务器总内存 * 45%（1G以下时） or 25~40%（1G以上时）； B4uJT ~,7>
Ø TSGA（SGA总内存） = 服务器总内存 * 55%（1G以下时） or 60~75%（1G以上时）； kK62y z ,
Ø TSGAI（单个实例的SGA内存） = TSGA / 服务器上的实例数； n:T W Z.9
Ø 共享池 = TSGAI * 45%； 2 b&& 3u8
Ø 数据缓存 = TSGAI * 45%； !L| }/u3 v
Ø 日志缓存 = TSGAI * 10% （10%的日志缓存通常都偏大，可待有统计数据后视情形将多出的部分分给共享池，数据缓存或者大池）； JI)@h 4b
Ø 若需配置大池和JAVA池时，从本实例SGA总内存中分配。 ygN4%- [XA
d _ [l{
为大的PL/SQL语句留空间； xJNV^u
· 执行大的PL/SQL程序时，为了获得足够的缓存空间，LRU算法会移出许多已解析的语句，尔后这些刚被移出的语句可能又要重新装入，这样就降低的库缓存的命中率，为了避免这样的情形，可以设置共享池保留区给大的PL/SQL对象使用； ^yl)c \`
· 参数SHARED_POOL_RESERVED_SIZE用来指定共享池保留区的大小，最大可指定到共享池的一半，默认值是共享池的5%，ORACLE建议从共享池的10%开始调整； )XFaVkQ}
· 可以从V$DB_OBJECT_CACHE查到目前缓存对象及其使用空间（OWNER, NAME, SHARABLE_MEM）； *)8!~Hs
· 可以用V$SHARED_POOL_RESERVED视图来监控保留区的使用情况以决定正确的保留区大小： =Z(_l LNmh
Ø REQUESTS 从保留区中请求空间的次数； VfFX H,j
Ø REQUEST_MISSES 保留区无空间可用而需要从非保留区清洗对象的请求次数；这个值为零或者一直很稳定表示保留区空间设置偏大； d#W>"Cqxqa
Ø FREE_SPACE 保留区未用的空间；这个空间大于保留区的一半时表示保留区空间设置偏大； 3~ ptD5@WF
Ø REQUEST_FAILURES 请求无法满足，出现ORA-04031错误的次数；这个值非零或者稳定增加时表示保留区太小； 0!5 w0^1
Ø 隐含参数_shared_pool_reserve_min_alloc（4400）确定请求保留区的起始大小； 4?c4GT9(6S
Ø 调整保留区的目标是使REQUEST_MISSES, REQUEST_FAILURES接近零。 XD8Q 2 un
· 可以用DBMS_SHARED_POOL.ABORT_REQUEST_THRESHOLD这个包过程设置一个阀值，大于这个值的库缓存请求将会失败。 ej;\a :JL
S Kdh !*G
Keep PL/SQL py':U QS*q
· 将重用率高的PL/SQL代码KEEP在库缓存中可以提高命中率，这个过程又称Pinning，由DBMS_SHARED_POOL.KEEP过程来完成，这些对象存放在共享池保留区中； y yqya[-11
· 清洗共享池语句（ALTER SYSTEM FLUSH SHARED_POOL;）并不会清洗Pinned对象，但是系统重启后，这些对象将不再是Pinned。 ?(2^ lH~6h
· Keep相关： H"vy[/ UcR
Ø DBMS_SHARED_POOL这个包并没有在运行catproc.sql时安装，需运行一次dbmspool.sql这个脚本； .DN)ck :e;
Ø 可以用这个包中的KEEP, UNKEEP来pin 和unpin存储对象； 56l1&hp8In
Ø 可从V$DB_OBJECT_CACHE.KEEP这个栏位得知哪些对象已被Pinned。 +/|t8zFWs
· 要确定哪些对象适于KEEP，可以激活审计功能来查看是哪些对象被频繁地调用，要KEEP匿名的PL/SQL程序比较麻烦，建议将经常使用且长度超过500个字符的匿名块写成过程和包再进行KEEP； UWIw/(Mv/]
· 因为重启后所有的KEEP都将失效，所以将KEEP的动作放在系统触发器中（AFTER STARTUP ON DATABASE）是较合适的。 $P bwC6>8
;'7 g g ]
代码重用； [~U CY Yl
· 确定是否需要对语句进行（硬）解析时，是先比较语句的哈希值，下面的两种方法有助于获得相同的哈希值，从而可以实现重用代码，提高命中率： sUTf Y|<7|
Ø 开发组的所有成员都使用相同的编码规范（包括大小写，空格，换行等）； K'u66 %wAL
Ø 使用绑定变量（提高命中率的同时可能会产生不够好的执行计划，因为优化器不知道变量的确定值，在有栏位的柱状图统计数据时也不能够利用）。 % 5r C`9 ^
ZT1I N6;8W
调整相关初始化参数。 Udl8?EV Sz
OPEN_CURSORS h&O8e;S #
· 这个参数指定每个用户会话能打开的游标个数； CF0 i72ul5
· 增大这个值可以减少重新解析会话曾打开的语句的机会，提高命中率，但需要更大的共享池空间。 R\^ XF8n6/
aZBb@~ Y
CURSOR_SPACE_FOR_TIME 2 9 ~Bu5
· 这个参数设为真时，只有当所有引用共享SQL的游标都关闭后，LRU才有可能移出这个SQL所占的缓存空间来重用，默认值是FALSE； R} J}Q b
· 只有在共享池足够大的情况下才能考虑设为真，设为真时可以减少重解析，提高命中率，加快游标的执行（空间换时间）。 K% iA-h
dub %fs
SESSION_CACHED_CURSORS uqFY a b U
· 这个参数指定会话能够缓存游标的个数，默认值是零； 7ElU5I
· 如果会话反复执行某些语句，设置该值大于零能提高游标的执行速度，这些缓存也是用LRU算法来管理的。 e_Un :r@)
IqrT@jgN-
CURSOR_SHARING i B0#Z _
· 这个参数决定什么样的SQL语句能够共享游标，有三个取值：FORCE, SIMILAR, EXACT； r>Rm=eK J
Ø FORCE 两个语句的差异只在字面值，这些差异不会改变语句的含义时可共享游标； =QQTH L{ 3
Ø SIMILAR两个语句的差异只在字面值，这些差异不会改变语句的含义和执行计划时可共享游标（这个值是在9i 中引入，如果有差异的栏位上有柱状图统计，这时执行计划会改变，不能共享游标，行为同于EXACT，如果差异栏位上没有柱状图统计，执行计划相同，这时的行为同于FORCE）； H68~5lJY^]
Ø EXACT 两个语句必须精确匹配才能共享游标，这是默认值。 ?vn9HhT D
7>,( QHl
8DL j?M >N
第五章调整数据缓存 1.理解数据缓存 tkj QSz
[QZ g =. "
Q! *}^W
理解数据缓存 J
· 数据缓存是SGA的一部分，用于存放用户最近存取过的段的数据块的副本，这些段可能是数据段，索引段，簇段，LOB段，LOB索引段，回滚段，临时段，数据缓存单元的大小与数据库块大小一致。 [Zx v&$SQ
· 数据缓存用下面的方法进行管理： w 8B SY
v 1O1 -aM
LRU列表 ?%A9}"q]
· 在Oracle执行SQL语句的过程中，相关的段数据要复制到SGA的数据缓存中来，这个操作由用户的服务器进程来执行； .$ " 13"
· 与共享池类似，数据缓存也是由LRU算法来管理的，当数据缓存的缓存块被填满而又有新的缓存块请求时，LRU将最近最少使用的缓存块老化出去，而保留最近经常使用的缓存块，当一个用户发现要读取的数据块已在之前由其它用户读入时，就可以节约很多时间，因为从内存中读取比从磁盘中读取要快上数千倍； +oev NM
· LRU算法管理一个LRU列表，这个列表类似于一个输送带，服务器进程将刚刚存取过的数据块放在输送带的开始端，随着更多的块被读入，先前读入的块向输送带末端移动，如果在到达末端之前某个块被再一次存取，则这个块又被移到开始端，否则就会从输送带末端跌落（缓存块被老化）； P r qyJ
· LRU对全表扫描时读入缓存块的管理与上面的有些不同，这些块一读入就被放在LRU列表的末端，这样可以避免对大表全表扫描时把数据缓存中的所有缓存块都清洗出去； c 4 V% >A
· LRU算法管理下的缓存块有四种状态： 8vqx} 2
Ø Free 自数据库启动以来尚未被使用过的缓存块； V jLv{f

Ø Pinned 正在被服务器进程使用的缓存块； WCD)yTg:ES
Ø Clean 曾被使用过且可立即被重用的缓存块，读入后未经修改或者最近一次修改已被写回磁盘，缓存版本与文件数据块一致； F}~ qTF;H
Ø Dirty 曾被使用过且不能立即被重用的缓存块，读入后作过修改且最近一次修改未被写回磁盘，缓存版本与文件数据块不一致； {n\ Ai3F-
· 管理脏块时会用到一个脏块列表（Dirty List，又名写列表），这个列表被检查点队列使用，用于跟踪所有的脏块，以第一次修改时间排序，脏块由DBW0进程写回磁盘。 U# Wg" W{
[\3 W_jR
用户服务器进程 e zb *tN!
· 当需要读取某个数据块时，服务器进程先到数据缓存中查看该块是否已存在，若没有找到，就需要将数据块从数据文件读到数据缓存中来，这首先要在数据缓存中找到一个可用的缓存块来容纳数据块的副本，这个过程中服务器进程可能要和LRU列表以及脏列表打交道： ;,/4Ry22j-
Ø 在LRU列表上查找可用块的时候，服务器进程将查到的脏块从LRU列表移到脏列表； xz Hb+1+p
Ø 随着脏块的加入，脏列表不断变长，当长度超过某个预定义的长度时，触发DBW0将脏列表上的脏块写回磁盘； lK_T %1Gz
Ø 如果服务器进程在LRU列表查找很多的块（超过某个阀值）都没能找到可用的块时，触发DBW0进程，将脏块直接从LRU列表写回磁盘。 22 'Ra [
· 如果服务器进程发现要找的块已在数据缓存中，但块的版本要晚于一致性读需要的版本时（版本更早时可直接使用），服务器进程在数据缓存中创建一个新块利用回滚段数据回滚到需要的版本（如果是在序列化事务中，且造成更晚版本的已提交的修改不是由当前事务造成的话，则报错）。 g /fr g(KF
x R 8y"CpE
数据库写入进程（DBW0） aJ@l T &.
· DBW0进程负责将数据缓存中的脏块写回磁盘，这个操作在下面的情形下发生： ;d
Ø 服务器进程不断地将脏块从LRU列表移到脏列表，当脏列表的长度达到阀值时，DBW0将脏列表上的脏块写回磁盘； # &e i
Ø 服务器进程在LRU列表上检查太多的块都没能找到一个可用块的时候，DBW0直接从LRU列上将脏块写回磁盘； \u04m}h]
Ø DBW0进程每三秒被激活一次，将LRU列表上的脏块移到脏列表，若脏列表长度达到阀值时，从脏列表上将脏块写回磁盘； [nG[ x|;|
Ø 检查点发生时，DBW0将脏块从LRU列表移到脏列表，再从脏列表写脏块回磁盘； -g:i 'e
Ø 数据库关闭时（不包括Shutdown Abort），DBW0将所有脏块写回磁盘； Nv| 0Z' M
Ø 表空间热备前，DBW0将属于这个表空间的所有脏块从LRU列表移到脏列表，然后从脏列表将脏块写回磁盘； OsW * @v (
Ø 表空间离线时（Normal，Temporary），DBW0将属于这个表空间的所有脏块从LRU移到脏列表，然后从脏列表将脏块写回磁盘； P,b & F
Ø 删除段时，DBW0先将这个段的脏块写回磁盘。