Oracle+9i+整体性能优化.docx

Oracle+9i+整体性能优化.docx

《Oracle+9i+整体性能优化.docx》由会员分享，可在线阅读，更多相关《Oracle+9i+整体性能优化.docx（118页珍藏版）》请在冰豆网上搜索。

Oracle+9i+整体性能优化

Oracle9i整体性能优化概述草稿之一:

调整争用

2.1优化维护4

2.2诊断LATCH竞争4

2.2.1概念4

2.2.2是否存在latch争用5

2.2.3检查Latch是否主要竞争5

2.2.4DBA关注的latch内容5

2.3诊断FREELIST竞争6

2.3.1概念6

2.3.2是否存在freelist争用6

2.3.3确定freelist争用的段7

2.3.4优化freelist争用7

2.4诊断LOCK竞争8

2.4.1概念8

2.4.2可能引起lockcontention的原因8

2.4.3锁解决办法9

2.4.4死锁9

2调整争用

争用：

每当一个Oracle进程试图访问一个Oracle结构，但由于该结构正由另一个进程结构使用而未能成功访问到它时，就发生对Oracle资源的争用。

常见的有latch、FreeList、lock争用。

主要维护的争用有：

Latch（锁存器）：

可作为内存性能的指标，说明内存需要调整。

FreeList：

会导致繁忙表上的DML操作性能很差。

Lock：

会遇到彻底的暂停，产生巨大的性能影响。

2.1优化维护

维护时，主要通过检查，判断存在的latch和freelist争用是否合理，不合理，则启动相应的优化工作。

而lock，在遇到问题的时候，可作为维护参考，平时不进行太多的维护（或从应用上考虑优化）。

（除了定期去$ORACLE_HOME/admin/$ORACLE_SID/udump查看死锁情况外）

2.2诊断latch竞争

2.2.1概念

Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffercache里的blocks信息。

一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。

不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。

1）Latch的作用：

A、序列化访问：

保护SGA中的共享数据结构；保护共享内存的分配。

B、序列化执行：

避免同时执行某些关键代码；避免互相干扰。

2）Latch请求的两种类型：

A、willing-to-wait：

请求的进程经过短时间的等待后再次发出请求，直到获得latch

B、immediate：

如果没有获得latch，请求的进程不等待，而是继续处理其他指令。

2.2.2是否存在latch争用

selectevent,total_waits,time_waited

fromv$system_event

whereevent=‘latchfree’;

如：

EVENTTOTAL_WAITSTIME_WAITED

--------------------------------

latchfree4320663779167

自实例启动以来，发生过4320663个latch争用，等候这些latch所花费的时间为779167毫秒。

2.2.3检查Latch是否主要竞争

检查latchfree是不是主要的waitevent：

Selectevent,total_waits,time_waitedfromv$system_eventorderbytime_waiteddesc;

2.2.4DBA关注的latch内容

DBA需要关注的latch有：

Selectname,gets,misses,sleeps,wait_time,spin_gets,immediate_gets,immediate_missesfromv$latch

wherenamelike‘%sharedpool%’

ornamelike‘%librarycache’

ornamelike‘%cachebufferslruchain%’

ornamelike‘%cachebufferschains%’

ornamelike‘%redoallocation%’

ornamelike‘%redocopy%’;

与willing-to-wait请求有关的列：

Selectname,gets,misses,sleeps,wait_time,spin_getsfromv$latch;

与immediate请求有关的列：

Selectname,immediate_gets,immediate_missesfromv$latch

Gets:

numberofsuccessfulwilling-to-waitrequestsforalatch;

Misses:

numberoftimesaninitialwiling-to-waitrequestwasunsuccessful;

Sleeps:

numberoftimesaprocesswaitedafteraninitialwilling-to-waitrequest;

Wait_time:

numberofmillisecondswaitedafterwilling-to-waitrequest;

Spin_gets:

getsthatmissesfirsttrybutsucceedafterspinning.

Immediate_gets:

numberofsuccessfulimmediaterequestsforeachlatch;

Immediate_misss:

numberofunsuccessfulimmediaterequestsforeachlatch;

sharedpool：

需要优化sharedpool。

（在没有配置largepool情况下使用sharedserver选件，会导致很高的sharedpoollatch.）

librarycache：

需要优化librarycache。

cachebuffersLRUchain：

管理databasecachebuffers上的LRUList上的块，自由缓冲区列表。

此争用有两种可能：

-导致严重的全部扫描的SQL语句或执行计划。

SQL需要优化。

-脏缓冲区写盘databasewriter未能跟上I/O请求。

优化磁盘I/O。

cachebufferschains：

预示某些某些缓冲块被重复搜索，块大小比较大的比块大小小的更容易遇见此latch。

优化块大小与I/O关系。

redoallocation：

许多用户同时放入redologbuffer，则产生该latch。

优化redologbuffer。

redocopy:

用户serverprocess复制信息到redologbuffer时发生。

优化redologbuffer。

一般无需调整latch（实际上在9i中，init.ora已经废弃了所有的latch调整参数），但是下列的措施是有用的（根据v$latch作为调整其他性能指标的指示器，而不是调整latch本身）：

A、对处于竞争中的latch做进一步的调查

B、如果竞争主要存在于sharedpool和librarycache中，可以考虑调整应用

C、如果进一步的调查显示需要调整sharedpool和buffercache，就进行调整

2.3诊断freelist竞争

（使用managementlocal的表空间不用调整，对表的insert和delete,update操作有重要指导意义）

判断表空间是否managementlocal：

selecttablespace_name,contents,extent_management,allocation_type,segment_space_management

fromdba_tablespaces;

2.3.1概念

每个段保持一个freelist来包含这个段中能接受新行的块。

比如如果应用程序有许多频繁插入行的用户，在试图访问该表的freelist就可能会经历等待。

2.3.2是否存在freelist争用

selectevent,total_waits,time_waitedfromv$system_event

whereevent=‘bufferbusywaits’;

自数据库启动来，可能发生的freelist争用。

还应经过以下查询确定：

selectclass,count,timefromv$waitstat

whereclassin

（‘freelist’,’segmentheader’）;

count:

访问一个块的等待次数。

Time：

等待访问块所花费的总时间。

例：

CLASSCOUNTTIME

--------------------------------------

segmentheader18691725

freelist00

说明：

针对段头部的freelist相关等待发生了1896次，针对自由列表发生的freelist为0次。

2.3.3确定freelist争用的段

selects.segment_name,s.segment_type,s.freelists

fromdba_segmentss,v$session_waitw

wherew.p1=s.header_file

andw.p2=s.header_block

andw.event='bufferbusywaits';

（确定当前的争用）

2.3.4优化freelist争用

1）增加附加的freelist

默认创建只有一个freelist，若要增加更多的freelist，可以使用

altertablehr.employeestorage（freelists5）;

进行修改。

2）把段转移到自动段管理表空间上（Oracle9i新特性）

managementlocal

使用自动段管理表空间，将不再利用freelist管理块，而是利用表空间数据文件头的位图来管理表空间中每个段的自由块分配（包括新建的任何附加段），此时该段在dba_segments视图中的freelists的值将是NULL空值。

（create一个managementlocal的表空间，使用

altertablehr.employeemovetablespaceapp1_data;

的方法把表从一个表空间移动到另外一个managementlocal的表空间）

（表里有long字段，得用其他方法移动）

2.4诊断lock竞争

2.4.1概念

lock用来保护对数据的访问的一致性，latch用来保护对内存的访问（latch从不在process之间共享）。

Lock通常发生在许多用户正在少量的表上执行ＤＭＬ操作的时候发生。

一旦lock申请获得，lock会一直保留给该用户，直到lock的用户发布一条commit或rollback为止。

使用Oracle默认加锁机制，可以：

１）修改同一个表的不同行，不用担心锁问题。

２）修改同一个表的同一行，由等待队列根据systemchangenumber（SCN）决定谁的修改结果是最终修改结果。

（默认队列最多数从init.ora的session获得，也可修改enqueue_resources手工调整）

如果需要严格的锁机制，可把init.ora的row_locking从always（rowlock）修改为intent的（tablelock）。

一共有两类锁“DML数据锁（Datalock）”和“DDL目录锁（Dictionarylock）”，有两种模式使用锁：

“独占型（Exclusivelock）”和“共享型（Sharelock）”模式，实现数据的并发性和一致性。

v$lock：

报告锁模式等情况。

v$locked_object:

报告被锁的对象信息。

dba_waiters:

报告阻塞的具体情况，有锁模式，waitsession和hold　session。

dba_blockers:

报告霸占资源导致别的session阻塞的sid.（如果没有，则执行$ORALCE_HOME/rdbms/admin/catblock.sql和utllockt.sql脚本创建）

DML事务使用row-levellocks,查询不会锁定数据。

锁有两种模式：

exlusive、share。

锁的类型：

•DMLordatalocks:

–Table-levellocks（TM的数量一般有init.ora的transactions获，但可修改dml_locks做调整）

–Row-levellocks（TX）

•DDLordictionarylocks

一个transaction至少获得两个锁：

一个共享的表锁，一个专有的行锁。

Oracleserver将所有的锁维护在一个队列里，队列跟踪了等待锁的用户、申请锁的类型以及用户的顺序信息。

Lock在下列情况会释放：

commit；rollback；terminated（此时由pmon清理locks）。

Quiesceddatabase：

一个数据库如果除了sys和system之外没有其他活动session，这个数据库即处于quiesced状态。

活动session是指这个session当前处于一个transaction中，或一个查询中，一个fetch中，或正占有某种共享资源。

2.4.2可能引起lockcontention的原因

不必要的高层次的锁；

长时间运行的transaction；

未提交的修改；

其他产品施加的高层次的锁。

2.4.3锁解决办法

如果出现了阻塞，则可通过下列方法之一解决这种争用：

１）修改应用程序，使之不要使用严格的锁和不要有太长的事务（可设逻辑提交点）。

（更不要使用显示锁select..forupdate,locktable..inexclusivemode;）

２）查出锁住对象，制造阻塞的session，通知用户commit或rollback。

可使用profile的方式，声明断开已空闲一段时间的用户。

３）使用altersystemkillsession‘sid,$serial’等方法杀死引起阻塞的session（如果该session没有在活动不会收到被kill的信息，直到发出新操作才得到kill提示）。

2.4.4死锁

Oracle自动检测和解决死锁，方法是通过回滚引起死锁的语句（statement），但是这条语句对应的transaction并没有回滚，因此当收到死锁的错误信息后，应该去回滚改transaction的剩余部分。

发生死锁后，会自动在$ORACLE_BASE/admin/$ORACLE_SID/udump目录下生成一个详细描述死锁的跟踪文件。

（Ｏracle会有租塞，但不会有死锁，因发生死锁后，导致发生死锁的操作会取消并报告“ORA-00060:

等待资源时检测到死锁”错误，但可能会继续出现阻塞（因无人commit或rollback）。

）

Oracle9i整体性能优化概述草稿之二:

SGA优化

3SGA优化9

3.1SHAREDPOOL9

3.1.1测量sharedpool性能11

3.1.2调整sharedpool性能12

3.2DATABASEBUFFERCACHE15

3.2.1测量databasebuffercache性能16

3.2.2改进buffercache性能17

3.3LARGEPOOL21

3.4JAVAPOOL21

3.5REDOLOGBUFFER22

3.5.1Oracle重做机制22

3.5.2调整RedoLogBuffer22

3.6其他SGA对象24

1SGA优化

相关视图说明，请参考A90190-01第3章。

1.1sharedpool

sharedpool使用LRU（leastrecentlyused）来进行管理。

（LRU，最近最少使用算法，有点象传送带，最近被使用的内容总是被放在头部，不常使用的会逐渐被排在尾部，在内存容量不够使用时会被标记自由被覆盖掉（若为databuffercache，则全表扫描是放在尾部的））

语法分析操作是高代价的。

应尽可能让SQL语句在sharedpool中有一个已解析版本。

若找到一个已解析的可用的版本，叫一次高速缓存区命中（cachehit），若找不到且必须进行语法分析，则叫一次脱靶（cachemiss）。

为了能命中，两条SQL语句必须一完全（包括大小写、空格等必须一样）。

Sharedpool由三部分组成：

librarycache（库高速缓存区）、datadictionarycache（数据目录高速缓存区）、userglobalarea（用户全局区）。

librarycache

缓存用户发布的SQL和PL/SQL（包括procedure,function,package,trigger,pl/sql,javaclass）。

一旦被缓存，这些语句就具有：

1）语句本身的实际文本。

2）语句所关联的散列值。

3）语句的P-CODE。

4）语句所关联的任何统计数据。

5）语句的执行计划。

可以查看上述5个属性的视图：

v$sql,v$sqltext,v$sqlarea,v$sql_plan,v$session

v$session的command列值说明：

2-insert

3-select

6-update

7-delete

26-locktable

44-commit

45-rollback

47-pl/sqlexecute

selecttype,count（executions）fromv$db_object_cache

groupbytype

orderby2desc;

高cursor执行次数，说明SQL是直接发布的。

而不是利用procedure或function。

datadictionarycache

缓存发布语句的：

检查的数据字典确保该语句引用的对象的存在，列名和数据类型的正确，所执行应具有的对象权限。

userglobalarea

共享模式中：

缓存用户会话信息，因可能启动和结束同一个事务的可能不是同一个共享服务器进程。

非共享模式中：

用户会话信息保存在PGA。

此时无UGA。

1.1.1测量sharedpool性能

重新启动的数据，需要一段时间给sharedpool预热一下，让应用重装载SQL，否则会有一个很底的命中率。

librarycache

应用程序在内存中查找到该SQL已缓存的频率

SQL>selectnamespace,gets,gethits,

1-（gets-gethits）/gets"gethitratio_1",

gethitratio,pins,pinhits,

1-（pins-pinhits）/pins"pinhitratio_1",

pinhitratio,

reloads,reloads/pins"reloadratio",invalidations

fromv$librarycache

wherenamespacein

（'SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER'）;

SQLAREA：

SQL语句

TABLE/PROCEDURE：

PL/SQL存储过程或函数

BODY：

PL/SQL程序包主体

TRIGGER：

PL/SQL触发器

Gethitratio：

至少要超过90%，越高越好（数据仓库和DSS具有特殊性，可能会经常低）。

（每执行一句，gets加1，找到一次，gethits加1）

Pinhitratio：

至少要超过90%，越高越好（数据仓库和DSS具有特殊性，可能会经常低）。

（每执行一条语句，就需要一个对象锁，pins加1；不等待获得，pinhits加1）

reloadratio：

显示已执行过有缓存副本的SQL，因一些原因（如引用的视图被重新编译，或表字段或类型被修改等）被设置为语法分析过期或作废而必须被重新语法分析的次数。

reloadratio至少应该小于1%。

reloads/pins>1%有两种可能，一种是librarycache空间不足，一种是sql中引用的对象不合法。

Invalidations：

SQL语句的语法分析被失效的总次数。

datadictionarycache

应用程序在内存中查找到所需数据字典信息的频率。

SQL>select

1-（SUM（getmisses）/SUM（gets））"datadictionaryhitratio"

fromv$rowcache;

datadictionaryhitratio：

至少不能低于85%。

1.1.2调整sharedpool性能

Sharedpool的优化应该放在优先考虑，因为一个cachemiss在sharedpool中发生比在databuffer中发生导致的成本更高。

修改的目标都是通过增加命中率来提高sharedpool的性能，方法有5类：

1）增大sharedpool

2）为大型PL/SQL语句腾出空间

3）将重要的PL/SQL代码保持在内存中

4）鼓励SQL重复利用

5）调整librarycache相关init.ora参数

增大sharedpool

增大sharedpool，由于是自动相对分配librarycache和datadictionarycache，可以减少已被缓存信息被LRU机制从内存中移走的可能。

（一般不会出现一个提高了，另一个很底的情况；往往会由于动态分配而统一提高）

确定当前被分配大小（默认64位操作系统被设置成64M，32位操作系统被设置成16M；若使用JAVA，至少要有50MJAVA类装入才能工作，或直接指定另一个参数java_pool_size）：

SQL>selectpool,sum（bytes）/1024/1024"SIZE"

fromv$sgastat

wherepool='sharedpool'

groupbypool;

************************************************************

setechooff

setfeedbackoff

setserveroutputon

declare

v_total_plsql_memnumber:

=0;

v_total_sql_memnumber:

=0;

v_total_sharable_memnumber:

=0;

begin

selectsum（sharable_mem）/1024/1024intov_total_plsql_mem

fr