oracle.Performance.Tuning笔记 第二章 The Method Behind the Madness 当出现性能问题，调查v$system_event,v$session_event,v$session_wait是个好的习惯。 在时间点1创建1个快照：create table BEGIN_SYS_EVENT as select * from v$system_event; 在时间点2创建1个快照：create table END_SYS_EVENT as select * from v$system_event; 然后通过下面查询就可知道这段时间的主事件： select T1.event,(T2.total_waits-T1.total_waits) “total waits”, (T2.total_timeouts-T1.total_timeouts) “total timeouts”,(T2.time_waited-T1.time_waited) “time waited”,(T2.average_wait-T1.average_wait) “average wait” form BEGIN_SYS_EVENT T1, END_SYS_EVENT T2 where T1.event = T2.event;所 通过上面的查询，查看等待事件最多的事件，并把它们归类。是基于I/O的事件？象db file scattered read、db file sequential read或者是free buffer waits。还是基于内存的事件，象buffer busy waits。 然后进步下钻，根据查询v$system_event得来得有可能影响系统性能的系统级事件，从 v$session_event去查询同样的事件，和v$session联合使用： select S.username, S.program, SE.Total_Timeouts,SE.Time_Waited, SE.Average_Wait from v$session S, v$session_event SE where S.sid = SE.sid and SE.event not like ‘SQL*Net%’ and S.status = ‘ACTIVE’ and S.username is not null; 很有可能那些系统级的等待事件就是单一会话或几个会话引起的。 对很大的表，或者resource比较紧张，用 estimate来收集统计信息比较合适。 对library cache里的分享SQL，上次parse过了，如果底层数据表改变结构了，或者它们的statistics重新收集了，那么它们会标识成invalid，如果后来执行，会重新parse. 当1个会话里重用同个SQL，不关闭cursor，also不需软解析（soft parse）,减少软解析和减少硬解析相同，对提高性能有好处。 第八章 Database Tuning(配置数据库块尺寸) 确定数据库的BLOCK SIZE！最优化的数据库块尺寸是数据库中非常重要的1件事！！计划数据块尺寸时，考虑下面几方面： 1． 1个数据块的I/O处理花多长时间 2． 数据库的buffer cache的尺寸和usage 3． 大的index块对系统有何影响 4． 1行超过了1个数据块有什么影响 5． 1个块里剩下的空间较小有什么影响 6． 当update时增加了行的长度而影起行迁徙，有什么影响 7． 行迁徙、链接对I/O性能的影响 如果是象数据仓库系统，主要提供读的数据库系统，应该配置那个Oracle版本支持的最大块尺寸！对其他系统，最好把数据块尺寸和操作系统块尺寸配置相同大小！ Initrans：对数据块缺省是1，对index块缺省是2。每个oracle block有块头区域，放置了1个表字典和1个行字典和事务槽（transaction slot）, 当1个事务要修改块里的1行或多行前，事务用事务槽来确定该块是否属于自己。在oracle的行级锁和读一致视图技术里，这些槽担任主要角色！当事务要修改块里的行时，他首先要在块头里的可用事务槽里“签名”！然后事务修改的每行的锁字节被设置，标识那些行被锁定了。如果只1个槽或槽不够用，当另外1个事务需修改同个块里的其它行时（insert、update、delete），就需要去block的pctfree里借24字节来分配1个槽在block header area。如果这种情况经常发生就会造成： 1． 动态的分配槽，会降低性能，因为oracle要去进行块级别的空间管理，而不是仅仅修改块里的数据。 2． 因为需到pctfree里借24字节去动态产生槽，如果很多事务产生这种情况，会占比较大的空间，因为借的空间不会释放，pctfree的计算就会有误，潜在产生行迁移。 maxtrans：定义1个块可同时进行修改块里数据的最多事务，缺省值是255。但不意味着每个块能真正支持255个事务，当对块的并发事务时，需要事务槽被分配。 Freelists：存在表所在的段头（segment header）,可用的块在这个链表里，如果块满了，就会成这里摘掉。基本上1个buffer busy waits event表示有freelists竞争发生。 表空间规划 如果表空间里所有的的扩展相同，不会产生碎片！ 1．对表空间，创造4个逻辑buckets：small、medium、large和x-large。 2．把数据库里的对象归到上面4个buckets里去。即哪些是小对象，大对象及很大的对象。 3． 对每个buckets定义合适的表空间缺省存储参数（default storage），确保initial和next相同，pctincrease为0，用上面确定的缺省存储参数（default storage）新建表空间。 4．把合适的对象建在对应的表空间里，不用storage存储参数，使那些对象接受表空间的缺省存储参数。但对那些并发很高或者特别大的对象再考虑用storage存储参数单独定义。 5．重复上面的4步去定义每1类对象（table、index、cluster）。 1个700G数据库的例子： Bucker Size Limit Small Less than 64M Medium Large than small,but less than 256M Large Large than medium,but less than 1024M X-Large Large than 1024M Bucker Size for initial and next Small 256K Medium 1M Large 4M X-Large 16M 第九章 Parallel Query Tuning 3个途径设置parallelism级别： 1． 表和index级别 2． 查询是用parallel 提示 3． Oracle Base cpu个数，缺省使用级别 Create table test ( column1 number(4), column2 number(4) ) parallel (degree 4) 查看级别：select degree from user_tables where table_name=’TEST’; 修改级别：alter table test parallel(degree 6); create index myindex on test(column) storage(initial 1M next 1M) parallel (degree 4) 用SQL： select /*+ parallel (alias,6) */ coluns from test alias; select /*+ noparallel */ coluns from test; //取消表上的并行级别 select statistic,value from v$pq_sysstat; select * from v$pq_sesstat; 第十章 contention tuning 多版本读一致 每个block的header area里的事务槽（transaction slots）也叫做interested transaction lists(ITL)，TIL包含3个重要的结构：事务ID（transaction ID）－》TID，Undo Blcok Address（UBA，告诉before image存在哪个地方），如果事务提交了，还包含SCN。事务修改了块里数据后，就把它自己的事务ID存在事务槽里，对应的服务进程（servicing the transaction）然后把要修改的列的原来的值(before image) 拷贝到该事务指派的回滚段，一旦数据修改了，事务槽不马上清除，这个工作留给以后读这个块的进程，这种也叫做块延迟清除（delayed block cleanout）。 block的header area也包含一个system change number(不要和system commit number混淆)和一个序列号（sequence number），该序列号用来确定块的版本。当进行了数据修改，获得一个新SCN且序列号为一，每次行更新，该序列号加1，直到commit了或者序列号达到了254（序列号达到254，必须重新获取SCN）。和数据块的事务槽一样，每个回滚段在段的首块（the header block）保存了一个事务表，这个回滚段的事务表包含使用该回滚段的事务信息，也包含了用于那个事务的最后一个回滚块的数据块地址（DBA）。 当一个查询开始时，服务进程获得一个当前数据库的SCN，当服务进程读一个数据块，它检查在ITL里的system change number，确定该块是否读一致，如果这个块的ITL包含一个更高的number，它就知道了在查询开始后，这个块已经被改变且提交了，然后服务进程就必须要到回滚段里去找该块的读一致（和查询开始时相同的SCN）版本。服务进程如果去找到那个一致版本？它从那个块的ITL里得到一串transaction Id，然后根据该事务ID到对应得一个或多个回滚段里（可能该块有多个事务发生了）得到before image。事务ID很重要，所以不会从ITL里删除。 另外一种情况是在ITL里得system change number比查询时的SCN小，这表示在查询开始时数据提交了，这时，服务进程就直接读，它是current state。 最后一种情况是在ITL里面没有system change number，服务进程读回滚段头发现所以事务都提交了，如果当前SCN是存在回滚段头里的事务表里，Oracle把该SCN 拷贝到ITL里，然后再来读该块。但是如果没有找到当前SCN，Oracle将用当前的数据块版本和存储在回滚段里的块来制造一个数据块的读一致版本。存储在回滚段里的before image用来重建那个块的数据，oracle搜寻每个回滚段header block的事务表，查看有没有ITL里的事务ID。如果因为一些原因，服务进程不能重建读一致image（before image在回滚段里没存在），查询就会失败，报“ORA-01555－Snapshot too old”错误。 注意：当多个事务同时读和写同一个数据块，将会在buffer cache里有同一个数据块的多个版本。Oracle可能必须要重建这个数据块的before image几次，这叫做块克隆。Buffer cache里的数据块通过hash table来访问，同一个块的多个克隆是同一个hash地址（无论有多少个克隆，块的物理地址不会改变）。非常多的块克隆会引起“cache buffers chains latch”。 回滚段竞争 每个事务写回滚段里预分配的扩展是依次和循环进行的。当一个扩展已经被回滚条目填满了，Oracle继续写下一个可用的扩展。同一个事务写before image达到扩展的边界的次数就叫wrap，在V$ROLLSTAT里，列wraps就表示当事务写before image时，跨越extent的次数。 写回滚段也是在buffer cache里完成的，因为在回滚段能被操作前，需要把回滚段读到内存里去。回滚段的首块（header block）包含了事务表，这个header block里的事务表包含了段里所以活动的事务信息，会在内存里驻留很长时间，如果事务很多，它就会被经常访问，修改，所以在这个回滚段头块就会产生竞争。 怎样发现在数据库里有回滚段的竞争？由于buffer cache里的回滚段也是来自于数据库的buffer cache，所以如果有竞争，也反映的是“buffer busy waits”事件。可以查询v$system_event,v$session_event,v$session_wait联合 dba_data_files，dba_extents和uet$去确定段名。查询v$waitstat去检测回滚块等待： select * from v$waitstat where class in (‘undo header’,‘undo block’); class count time undo header 42331 1922 undo block 32541 1132 过段时间再查询，如果值都增加了，说明有竞争。 临时段竞争 当排序操作开始时，它会首先创造临时段，排序完后，就会删除该临时段。创造和删除数据库里的任何段，进程都需要获取一个“space management transaction(ST) enqueue”，在数据库里只有一个ST enqueue。所以会引发临时段竞争。 查询v$sort_segment可以帮助决定临时表空间的大小。它可以报告数据库里所有排序操作曾经用过的最大空间。 Latches Latch是一个被用来串行化访问SGA里的分享数据的串行化锁机制。3个需要关注的latch：cache buffers lru chain, redo copy, cache buffers chains。 第十一掌 I/O tuning  RAID1+0：对于读写较大的都比较合适  RAID5：对于读较大的比较合适，但写比较大的不合适  文件系统和RAW：Veritas的Quick I/O或者其他系统支持的direct I/O可以越过操作系统的缓存，直接对磁盘读写也都不比用 RAW差，并且易于管理。 第十二章 Operating System Tuning 配置内存 对于主频少于500MHz的cpu，每个cpu至少配512M内存。如果大于500MHz，至少每个cpu配1G内存。 配置内核 两个需要调整的重要的UNIX资源是：shared memory segments和semaphores。AIX不能调整shared memory segments和semaphores。下面列出可以调整的各种变量：  shared memory segments： SHMMAX：一个单一最大分享内存段，单位是字节，Oracle首先去获取充足大的分享内存段以适合SGA，如果没有，就请求多个分享内存段做为SGA的每个组件。但是，必须要一个充足大的连续分享内存段给最大的shared pool。 SHMMIN：最小的分享内存段，这个参数可以忽略。 SHMMNI：设置分享内存段的最多个数，一般缺省就可以了。 SHMSEG：设置一个处理器能使用的分享内存段的最多个数。  Semaphore： SEMMNS：设置系统可用的最多semaphore个数。 SEMMNI：最大的semaphore集(set)，UNIX分配semaphore是以集为单位。 SEMMSL：semaphore set 里最多的semaphore个数。  其它需要配置的：(可能每种unix不同) maxusers：OS级别的最多的用户session。它会影响其它的参数，如：npro,nfile,maxuprc。 npro：内核能支持的最多进程个数。包括用户进程和oracle后台进程。应该把它设置的比SEMMNS要高。 nfile：内核能支持的最多打开文件的个数，可以把它设高点，因为它需要的内存较小。 maxuprc： 每个用户能有的最多进程个数。一般，oracle相关的进程，后台进程等都是属于oracle用户。 异步IO asynchronous写磁盘的时候，不等写完就返回。操作系统设置DISK_ASYNCH_IO，如果设置了异步IO，把db_writer_processes设置成1，不要设置dbwr_io_slaves。Aix 可以用lsattr –E –l aio0来查看异步IO。