Oracle性能优化基本方法包括一下几个步骤.docx
《Oracle性能优化基本方法包括一下几个步骤.docx》由会员分享，可在线阅读，更多相关《Oracle性能优化基本方法包括一下几个步骤.docx（14页珍藏版）》请在冰豆网上搜索。
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视图执行下面的查询查看数据库中某些常见的等待事件：
1.select * from v$system_event
2.where event in （'buffer busy waits',
3.'db file sequential read',
4.'db file scattered read',
5.'enqueue',
6.'free buffer waits',
7.'latch free',
8.'log file parallel write',
9.'log file sync'）;
2）接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：
1.select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
2.from v$session s,v$session_event se
3.where s.sid = se.sid
4.and se.event not like 'SQL*Net%'
5.and s.status = 'ACTIVE'
6.and s.username is not null;
3）使用下面查询找到与所连接的会话有关的当前等待事件。
这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。
1.select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT
2.from v$session s,v$session_wait sw
3.where s.sid = sw.sid
4.and sw.event not like 'SQL*Net%'
5.and s.username is not null
6.order by sw.wait_time desc;
4）查询会话等待事件的详细信息
1.select sid,event,p1text,p1,p2text,p2,p3text,p3
2.from v$session_wait
3.where sid between &1 and &2
4.and event not like '%SQL%'
5.and event not like '%rdbms%';
5）利用P1、P2的信息，找出等待事件的相关的段
1.select owner,segment_name,segment_type,tablespace_name
2.from dba_extents
3.where file_id = &fileid_in
4.and &blockid_in between block_id and block_id + blocks - 1;
6）获得操作该段的sql语句：
1.select sid, getsqltxt（sql_hash_value,sql_address）
2.from v$session
3.where sid = &sid_in;
7）getsqltxt函数
getsqltxt函数
createorreplace
functionGetSQLtxt（hashaddr_ininv$sqltext.hash_value%type，
addr_ininv$sqltext.address%type）
returnvarchar2
is
temp_sqltxtvarchar2（32767）;
cursorsqlpiece_cur
is
selectpiece，sql_text
fromv$sqltext
wherehash_value=hashaddr_in
andaddress=addr_in
orderbypiece;
begin
forsqlpiece_recinsqlpiece_cur
loop
temp_sqltxt：
=temp_sqltxt||sqlpiece_rec.sql_text;
endloop;
returntemp_sqltxt;
endGetSQLtxt;
8）至此已经找到影响性能的对象和sql语句，可以有针对性地优化
4.把等待事件记入跟踪文件
重点：
如果在跟踪系统上的等待事件时，由于某种原因遇到了麻烦，则可以将这些等待事件记入一个跟踪文件。
方法如下：
1）对于当前会话：
1.alter session set timed_statistics=true;
2.alter session set max_dump_file_size=unlimited;
3.alter session set events '10046 trace name context forever, level 12';
2）执行应用程序，然后在USER_DUMP_DEST指出的目录中找到跟踪文件。
3）查看文件中以词WAIT开始的所有行。
4）对于其它的会话
5）确定会话的进程ID（SPID）。
下面的查询识别出名称以A开始的所有用户的会话进程ID：
1.select S.Username, P.Spid from V$SESSION S, V$PROCESS P
2.where S.PADDR = P.ADDR and S.Username like 'A%';
6）以sysdba进入sqlplus执行
1.alter session set timed_statistics=true;
2.alter session set max_dump_file_size=unlimited;
3.oradebug setospid
4.oradebug unlimit
5.oradebug event 10046 trace name context forever, level X /* Where X = （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等。
6．Oracle性能优化所需的成分（应用程序、数据库、I/O、争用、OS等）。
7．跟踪并实施更改控制过程。
8．测量并记录当前Oracle性能
9．重复步骤3到7，直到满足优化目标
几个简单的步骤大幅提高Oracle性能--我优化数据库的三板斧
数据库优化的讨论可以说是一个永恒的主题。
资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack，贴出数据库配置等等。
还有的人认为要抓出执行最慢的语句来进行优化。
但实际情况是，提出疑问的人很可能根本不懂执行计划，更不要说statspack了。
而我认为，数据库优化，应该首先从大的方面考虑：
网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整。
实际上网络、硬件等往往无法决定更换，应用程序一般也无法修改，因此应该着重从数据库配置、数据结构上来下手，首先让数据库有一个良好的配置，然后再考虑具体优化某些过慢的语句。
我在给我的用户系统进行优化的过程中，总结了一些基本的，简单易行的办法来优化数据库，算是我的三板斧，呵呵。
不过请注意，这些不一定普遍使用，甚至有的会有副作用，但是对OLTP系统、基于成本的数据库往往行之有效，不妨试试。
（注：
附件是Burleson写的用来报告数据库性能等信息的脚本，本文用到）
一．设置合适的SGA
常常有人抱怨服务器硬件很好，但是Oracle就是很慢。
很可能是内存分配不合理造成的。
（1）假设内存有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来直接分配。
（2）假设内存有1G，Oracle的SGA可以考虑分配500M：
共享池分配100M到150M，数据缓冲区分配300M到400M。
（3）内存2G，SGA可以考虑分配1.2G，共享池300M到500M，剩下的给数据块缓冲区。
（4）内存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>SETAUTOTRACEON
SQL>SETTIMINGON
通过SETAUTOTRACEON来查看语句的执行计划，通过SETTIMINGON来查看语句运行时间。
SQL>selectcount（*）fromCASE_GA_AJZLZ;
COUNT（*）
----------
346639
已用时间:
00:
00:
21.38
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=CHOOSE
1 0 SORT（AGGREGATE）
2 1 TABLEACCESS（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.71
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=FIRST_ROWS（Cost=351Card=1）
1 0 SORT（AGGREGATE）
2 1 INDEX（FASTFULLSCAN）OF'PK_AJZLZ'（UNIQUE）（Cost=351
Card=346351）
…………………………
请注意，这次时间仅仅用了0.71秒！
这要归功于INDEX（FASTFULLSCAN）。
通过分析表，查询使用了PK_AJZLZ索引，磁盘I/O大幅减少，速度也大幅提升！
下面的实用语句可以用来生成分析某个用户的所有表和索引，假设用户是GAXZUSR：
SQL>setpagesize0
SQL>spoold:
\analyze_tables.sql;
SQL>select'analyzetable'||owner||'.'||table_name||'computestatistics;'fromdba_tableswhereowner='GAXZUSR';
SQL>spooloff
SQL>spoolspoold:
\analyze_indexes.sql;
SQL>select'analyzeindex'||owner||'.'||index_name||'computestatistics;'fromdba_indexeswhereowner='GAXZUSR';
SQL>spooloff
SQL>@d:
\analyze_tables.sql
SQL>@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了。
六．调整排序参数
（1） SORT_AREA_SIZE:
默认的用来排序的SORT_AREA_SIZE大小是32K，通常显得有点小，一般可以考虑设置成1M（1048576）。
这个参数不能设置过大，因为每个连接都要分配同样的排序内存。
（2）SORT_MULTIBLOCK_READ_COUNT:
增大这个参数可以提高临时表空间排序性能，该参数默认是2，可以改成32来对比一下排序查询时间变化。
注意，这个参数的最大值与平台有关系。
七．调整其它几个关键的性能参数
很多人认为使用oracle数据库，系统的默认参数就是最好的，其实不是这样，很多参数都需要调整，而且调整前后性能大不一样。
（1）log_buffer
日志缓冲区大小默认设置32k太小了，建议设置成512K或者1M。
log_buffer=524288
（2）optimizer_index_caching
这个参数可以设置索引的缓冲度，范围是0到100，默认是0，可以考虑设置成90
（3）optimizer_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-10620:
Operationnotallowedonthissegment）在ORACLE805中，只能通过重新创建表来修改。
SQL>select*from
升级会员 