Oracle性能优化基本方法包括一下几个步骤

Oracle性能优化基本方法包括一下几个步骤，包括:1） 设立合理的Oracle性能优化目标。2） 测量并记录当前的Oracle性能。3） 确定当前Oracle性能瓶颈（Oracle等待什么、哪些SQL语句是该等待事件的成分）。4） 把等待事件记入跟踪文件。5） 确定当前的OS瓶颈。6） 优化所需的成分（应用程序、数据库、I/O、争用、OS等）。7） 跟踪并实施更改控制过程。8） 测量并记录当前性能9） 重复步骤3到7,直到满足优化目标下面来 详述。1•设立合理的Oracle性能优化目标重点：关于设立目标的最重要的一点是它们必须是可量化和可达到的。方法：目标必须是当前性能和所需性能的的陈述形式的语句。2•测量并记录当前Oracle性能重点：1） 需要在峰值活动时间获得当前系统性能快照2） 关键是要在出现性能问题的时间段内采集信息3） 必须在合理的时间段上采集，一般在峰值期间照几个为期15分钟的快照3•确定当前Oracle性能瓶颈重点：从Oracle等待接口v$system_event、v$session_event和v$session_wait中获得等待事件，进而找出影响性能的对象和sql语句。方法如下：1）首先，利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：select*fromv$system_eventwhereeventin（'bufferbusywaits','dbfilesequentialread','dbfilescatteredread','enqueue','freebufferwaits','latchfree',

'logfileparallelwrite','logfilesync'）;2） 接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：selectse.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_waitfromv$sessions,v$session_eventsewheres.sid=se.sidandse.eventnotlike'SQL*Net%'ands.status='ACTIVE'ands.usernameisnotnull;3） 使用下面查询找到与所连接的会话有关的当前等待事件。这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。selectsw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_waitSEC_IN_WAITfromv$sessions,v$session_waitswwheres.sid=sw.sidandsw.eventnotlike'SQL*Net%'ands.usernameisnotnullorderbysw.wait_timedesc;4） 查询会话等待事件的详细信息selectsid,event,p1text,p1,p2text,p2,p3text,p3fromv$session_waitwheresidbetween&1and&2andeventnotlike'%SQL%'andeventnotlike'%rdbms%';5） 利用Pl、P2的信息，找出等待事件的相关的段selectowner,segment_name,segment_type,tablespace_namefromdba_extentswherefile_id=&fileid_inand&blockid_inbetweenblock_idandblock_id+blocks-1;6） 获得操作该段的sql语句：selectsid,getsqltxt（sql_hash_value,sql_address）2.fromv$session2.fromv$session3.wheresid=&sid_in;getsqltxt函数getsqltxt函数createorreplacefunctionGetSQLtxt(hashaddr_ininv$sqltext.hash_value%type,addr_ininv$sqltext.address%type)returnvarchar2istemp_sqltxtvarchar2(32767);cursorsqlpiece_curisselectpiece,sql_textfromv$sqltextwherehash_value=hashaddr_inandaddress=addr_inorderbypiece;beginforsqlpiece_recinsqlpiece_curlooptemp_sqltxt:=temp_sqltxtIIsqlpiece_rec.sql_text;endloop;returntemp_sqltxt;endGetSQLtxt;至此已经找到影响性能的对象和sql语句，可以有针对性地优化4.把等待事件记入跟踪文件

重点：如果在跟踪系统上的等待事件时，由于某种原因遇到了麻烦，则可以将这些等待事件记入一个跟踪文件。方法如下：1） 对于当前会话：altersessionsettimed_statistics=true;altersessionsetmax_dump_file_size=unlimited;altersessionsetevents'10046tracenamecontextforever,level12!・;2） 执行应用程序，然后在USER_DUMP_DEST指出的目录中找到跟踪文件。3） 查看文件中以词WAIT开始的所有行。4） 对于其它的会话5） 确定会话的进程ID（SPID）。下面的查询识别出名称以A开始的所有用户的会话进程ID：selectS.Username,P・SpidfromV$SESSIONS,V$PROCESSPwhereS.PADDR=P.ADDRandS.Usernamelike'A%';6） 以sysdba进入sqlplus执行altersessionset timed_statistics=true;altersessionset max_dump_file_size=unlimited;oradebugsetospidoradebugunlimitoradebugevent10046tracenamecontextforever,levelX/*WhereX=（1,4,8,12）*/7） 跟踪某个时间间隔得会话应用程序。8） 在USER_DUMP_DEST的值指出的目录中利用SPID查看跟踪文件9） 查看文件中以词WAIT开始的所有行。5•确定当前OS瓶颈1）WindowsNT上的监控使用控制面板-〉管理工具-〉性能即可2）UNIX上的监控使用通用性的工具，包括sar、iostat、cpustat、mpstat、netstat、top、osview等。Oracle性能优化所需的成分（应用程序、数据库、I/O、争用、OS等）。

跟踪并实施更改控制过程。8•测量并记录当前Oracle性能9.重复步骤3到7,直到满足优化目标几个简单的步骤大幅提高Oracle性能--我优化数据库的三板斧数据库优化的讨论可以说是一个永恒的主题。资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack,贴出数据库配置等等。还有的人认为要抓出执行最慢的语句来进行优化。但实际情况是，提出疑问的人很可能根本不懂执行计划，更不要说statspack了。而我认为，数据库优化，应该首先从大的方面考虑：网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整。实际上网络、硬件等往往无法决定更换，应用程序一般也无法修改，因此应该着重从数据库配置、数据结构上来下手，首先让数据库有一个良好的配置，然后再考虑具体优化某些过慢的语句。我在给我的用户系统进行优化的过程中，总结了一些基本的，简单易行的办法来优化数据库，算是我的三板斧，呵呵。不过请注意，这些不一定普遍使用，甚至有的会有副作用，但是对OLTP系统、基于成本的数据库往往行之有效，不妨试试。(注：附件是Burleson写的用来报告数据库性能等信息的脚本，本文用到)一．设置合适的SGA常常有人抱怨服务器硬件很好，但是Oracle就是很慢。很可能是内存分配不合理造成的。假设内存有512M,这通常是小型应用。建议Oracle的SGA大约240M，其中：共享池(SHARED_POOL_SIZE)可以设置60M到80M，根据实际的用户数、查询等来定。数据块缓冲区可以大致分配120M-150M,8i下需要设置DB_BLOCK_BUFFERS,DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小。9i下的数据缓冲区可以用db_cache_size来直接分配。假设内存有1G,Oracle的SGA可以考虑分配500M：共享池分配100M到150M，数据缓冲区分配300M到400M。内存2G,SGA可以考虑分配1.2G，共享池300M到500M，剩下的给数据块缓冲区。内存2G以上：共享池300M到500M就足够啦，再多也没有太大帮助；(Biti_rainy有专述)数据缓冲区是尽可能的大，但是一定要注意两个问题：一是要给操作系统和其他应用留够内存，二是对于32位的操作系统，Oracle的SGA有1.75G的限制。有的32位操作系统上可以突破这个限制，方法还请看Biti的大作吧。二．分析表和索引，更改优化模式Oracle默认优化模式是CHOOSE,在这种情况下，如果表没有经过分析，经常导致查询使用全表扫描,而不使用索引。这通常导致磁盘I/O太多，而导致查询很慢。如果没有使用执行计划稳定性，则应该把表和索引都分析一下，这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZETABLE分析索引可以用ANALYZEINDEX命令。对于少于100万的表，可以考虑分析整个表，对于很大的表，可以按百分比来分析，但是百分比不能过低，否则生成的统计信息可能不准确。可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间，索引可以通过DBA_INDEXES的LAST_ANALYZED列。下面通过例子来说明分析前后的速度对比。(表CASE_GA_AJZLZ大约有35万数据，有主键)首先在SQLPLUS中打开自动查询执行计划功能。(第一次要执行\RDBMS\ADMIN\utlxplan.sql来创建PLAN_TABLE这个表)SQL>SETAUTOTRACEONSQL>SETTIMINGON通过SETAUTOTRACEON来查看语句的执行计划，通过SETTIMINGON来查看语句运行时间。SQL>selectcount(*)fromCASE_GA_AJZLZ;COUNT(*)346639已用时间:00:00:21.38ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE0SORT(AGGREGATE)1TABLEACCESS(FULL)OF'CASE_GA_AJZLZ'请注意上面分析中的TABLEACCESS(FULL),这说明该语句执行了全表扫描。而且查询使用了21.38秒。这时表还没有经过分析。下面我们来对该表进行分析：SQL>analyzetableCASE_GA_AJZLZcomputestatistics;表已分析。已用时间:00:05:357.63然后再来查询：SQL>selectcount(*)fromCASE_GA_AJZLZ;COUNT(*)346639已用时间:00:00:00.71ExecutionPlan0SELECTSTATEMENTOptimizer=FIRST_ROWS(Cost=351Card=1)0SORT(AGGREGATE)1INDEX(FASTFULLSCAN)OF'PK_AJZLZ'(UNIQUE)(Cost=351Card=346351)请注意，这次时间仅仅用了0.71秒！这要归功于INDEX(FASTFULLSCAN)。通过分析表，查询使用了PK_AJZLZ索引，磁盘I/O大幅减少，速度也大幅提升！下面的实用语句可以用来生成分析某个用户的所有表和索引，假设用户是GAXZUSR：SQL>setpagesize0SQL>spoold:\analyze_tables.sql;SQL>select'analyzetable'||owner||'.'||table_name||'computestatistics;'fromdba_tableswhereowner='GAXZUSR';SQL>spooloffSQL>spoolspoold:\analyze_indexes.sql;SQL>select'analyzeindex'||owner||'.'||index_name||'computestatistics;'fromdba_indexeswhereowner='GAXZUSR';SQL>spooloffSQL>@d:\analyze_tables.sqlSQL>@d:\analyze_indexes.sql解释：上面的语句生成了两个sql文件，分别分析全部的GAXZUSR的表和索引。如果需要按照百分比来分析表，可以修改一下脚本。通过上面的步骤，我们就完成了对表和索引的分析，可以测试一下速度的改进啦。建议定期运行上面的语句，尤其是数据经过大量更新。当然，也可以通过dbms_stats来分析表和索引，更方便一些。但是我仍然习惯上面的方法，因为成功与否会直接提示出来。另外，我们可以将优化模式进行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。对于OLTP系统，可以改成FIRST_ROWS，来要求查询尽快返回结果。这样即使不用分析，在一般情况下也可以提高查询性能。但是表和索引经过分析后有助于找到最合适的执行计划。三.设置cursor_sharing=FORCE或SIMILAR这种方法是8i才开始有的，oracle805不支持。通过设置该参数，可以强制共享只有文字不同的语句解释计划。例如下面两条语句可以共享：SQL>SELECT*FROMMYTABLEWHERENAME='tom'SQL>SELECT*FROMMYTABLEWHERENAME='turner'这个方法可以大幅降低缓冲区利用率低的问题，避免语句重新解释。通过这个功能，可以很大程度上解决硬解析带来的性能下降的问题。个人感觉可根据系统的实际情况，决定是否将该参数改成FORCE。该参数默认是exact。不过一定要注意，修改之前，必须先给ORACLE打补丁，否则改之后oracle会占用100%的CPU,无法使用。对于ORACLE9i,可以设置成SIMILAR，这个设置综合了FORCE和EXACT的优点。不过请慎用这个功能，这个参数也可能带来很大的负面影响！四•将常用的小表、索引钉在数据缓存KEEP池中内存上数据读取速度远远比硬盘中读取要快，据称，内存中数据读的速度是硬盘的14000倍！如果资源比较丰富，把常用的小的、而且经常进行全表扫描的表给钉内存中，当然是在好不过了。可以简单的通过ALTERTABLEtablenameCACHE来实现，在ORACLE8i之后可以使用ALTERTABLEtableSTORAGE(BUFFER_POOLKEEP)。一般来说，可以考虑把200数据块之内的表放在keep池中，当然要根据内存大小等因素来定。关于如何查出那些表或索引符合条件，可以使用本文提供的access.sql和access_report.sql。这两个脚本是著名的Oracle专家Burleson写的，你也可以在读懂了情况下根据实际情况调整一下脚本。对于索引，可以通过ALTERINDEXindexnameSTORAGE(BUFFER_POOLKEEP)来钉在KEEP池中。将表定在KEEP池中需要做一些准备工作。对于ORACLE9i需要设置DB_KEEP_CACHE_SIZE,对于8i，需要设置buffer_pool_keep。在8i中，还要修改db_block_lru_latches，该参数默认是1,无法使用buffer_pool_keep。该参数应该比2*3*CPU数量少，但是要大于1,才能设置DB_KEEP_CACHE_BUFFER。buffer_pool_keep从db_block_buffers中分配，因此也要小于db_block_buffers。设置好这些参数后，就可以把常用对象永久钉在内存里。五•设置optimizer_max_permutations对于多表连接查询，如果采用基于成本优化(CBO)，ORACLE会计算出很多种运行方案，从中选择出最优方案。这个参数就是设置oracle究竟从多少种方案来选择最优。如果设置太大，那么计算最优方案过程也是时间比较长的。Oracle805和8i默认是80000,8建议改成2000。对于9i,已经默认是2000了。六.调整排序参数SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是32K,通常显得有点小，一般可以考虑设置成1M(1048576)。这个参数不能设置过大，因为每个连接都要分配同样的排序内存。SORT_MULTIBLOCK_READ_COUNT:增大这个参数可以提高临时表空间排序性能，该参数默认是2，可以改成32来对比一下排序查询时间变化。注意，这个参数的最大值与平台有关系。七•调整其它几个关键的性能参数很多人认为使用oracle数据库，系统的默认参数就是最好的，其实不是这样，很多参数都需要调整，而且调整前后性能大不一样。log_buffer日志缓冲区大小默认设置32k太小了，建议设置成512K或者1M。log_buffer=524288optimizer_index_caching这个参数可以设置索引的缓冲度，范围是0到100，默认是0，可以考虑设置成90optimizer_index_cost_adj这个参数是一个百分比，表明索引扫描与全表扫描的代价范围是1到1000。默认=100表名索引扫描与全表扫描代价一样。将这个参数设小表名索引代价要小于全表扫描，这样就使得使用CBO进行成本计算时更倾向于使用索引扫描。建议把这个参数设置成30到50。八•改变联机日志文件大小(一般用于oracle805)Oracle805的联机日志文件默认只有1M大小，这实在是太小了，通过查看数据库的日志，很可能发现“checkpointnotcomplete”之类的错误提示。这会导致系统稳定性，同样也降低了数据库性能。建议修改成10M。修改方法是删除一个组、添加一个组，直到3个组都换成新的大小。说明：这个操作需要实施人员具有较多的数据库知识，如果不太了解，最好不要试验。九．改变数据块大小(一般用于oracle805)Oracle805默认的块(DB_BLOCK_SIZE)大小是2K,太小了，因为块小，所以请求同样的数据量的时候，读的次数就要增多，导致性能低下。当然如果服务器性能比较好，还是升级Oracle更好，如果服务器配置比较差，建议改成8K。但是数据块不能直接修改，唯一的办法就是将数据导出，重新创建数据库，然后将数据导入。说明：这个操作需要实施人员具有较多的数据库知识，如果不太了解，最好不要试验。十．设置合适的表存储参数对于有很多并发写入用户的系统来说，如果系统没有经过调整，经常会有数据等待现象。这是因为9i之前的表设置的默认的自由队列freelists为1,这样就可能造成数据等待。通过查看v$waitstat，如果发现datablock或者freelist类的count次数很大，则说明等待情况严重，需要增加freelists。这个参数在8i、9i中可以动态修改(需要打补丁，否则会有ORA-1O62O:Operationnotallowedonthissegment)在ORACLE8O5中，只能通过重新创建表来修改。SQL>select*fromv$waitstat;CLASSCOUNTTIMEdatablock11922013342456sortblock00saveundoblock00segmentheader10freelist00如果测算经常有10个并发的写用户，可以把表的freelists改成10。例如下面的脚本可以把GAXZUSR用户的所有表重新设置FREELISTS的语句写在D:\FREELISTS.SQL里：SQL>SETPAGESIZE0SQL>SPOOLD:\FREELISTS.SQLSQL>SELECT'ALTERTABLE'||TABLE_NAME||'STORAGE(FREELISTS10);'FROMDBA_TABLESWHEREOWNER='GAXZUSR';SQL>SPOOLOFF检查D:\FREELISTS.SQL,没有错误后运行修改FREELISTS：SQL>@D:\FREELISTS.SQL十一．重新组织表结构(1)按照主键重新排序。数据库运行了一段时间后，可能会有很多数据，而这些数据又可能是经常按照某个字段来选取区段数据。如果我们能够把主键按照顺序重新来组织一下表，那么用主键进行的查询就会明显快很多，主要是因为经过排序后，相似的编号都放在同一个数据块里，ORACLE在进行主键范围查找的时候，就会大大减少物理块度读取数量。在对表和索引分析之后，可以通过DBA_INDEXES的CLUSTERING_FACTOR列来判断表是否需要重新排序。如果该字段的值与表的BLOCK数量差不多，则不需要重新排序，如果和表的行数差不多，则应该考虑重新组织一下了。下面的例子示意性说明怎样对表CASE_GA_AJZLZ按照主键PK_AJZLZ进行重新排序：<1>将表CASE_GA_AJZLZ的索引、外间约束引用等找出来备用。SETPAGESIZE0SETLINESIZE300SPOOLDISABLE_CONSTRAINTS.SQLSELECT'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'FROMUSER_CONSTRAINTSWHERECONSTRAINT_TYPE='R'ANDR_CONSTRAINT_NAME='PK_AJZLZ';SPOOLOFFSPOOLCREATE_CONSTRAINTS.SQLSELECT'ALTERTABLE'||TABLE_NAME||'ADDCONSTRAINT'||CONSTRAINT_NAME||'FOREIGNKEY(CASEID)REFERENCESCASE_GA_AJZLZ(CASEID);'FROMUSER_CONSTRAINTSWHERECONSTRAINT_TYPE='R'ANDR_CONSTRAINT_NAME='PK_AJZLZ';SPOOLOFFSPOOLCREATE_INDEX.SQLSELECT'CREATEINDEX'||INDEX_NAME||'ON'||TABLE_NAME||'('||COLUMN_NAME||')TABLESPACEINDX;'FROMUSER_IND_COLUMNSWHERETABLE_NAME='CASE_GA_AJZLZ'ANDINDEX_NAME<>'PK_AJZLZ';<2>创建新的表CASE_GA_AJZLZ_NEW：SQL>CREATETABLECASE_GA_AJZLZ_NEWASSELECT/*+INDEX(CASE_GA_AJZLZPK_AJZLZ)*/*FROMCASE_GA_AJZLZ;注意，上面的注释(红颜色部分)表名按照PK_AJZLZ排序来重新组织表。<3>禁用CASE_GA_AJZLZ的夕卜间约束，将表CASE_GA_AJZLZTRUNCATE，然后册U除之<4>将表CASE_GA_AJZLZ_NEW更名为CASE_GA_AJZLZSQL>ALTERTABLECASE_GA_AJZLZ_NEWRENAMETOCASE_GA_AJZLZ;<5>创建CASE_GA_AJZLZ的所有索引、主键约束等。SQL>ALTE