如何提高oracle的性能
如何提高oracle的性能
一．设置合适的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太多，而导致查询很慢。如果没有使用执行计划稳定性，则应该把表和索引都分析一下，这样可能直接会使查询速度大幅提升。分析表命令可以用analyze table 分析索引可以用analyze index命令。对于少于100万的表，可以考虑分析整个表，对于很大的表，可以按百分比来分析，但是百分比不能过低，否则生成的统计信息可能不准确。可以通过dba_tables的last_analyzed列来查看表是否经过分析或分析时间，索引可以通过dba_indexes的last_analyzed列。
下面通过例子来说明分析前后的速度对比。（表case_ga_ajzlz大约有35万数据，有主键）首先在sqlplus中打开自动查询执行计划功能。(第一次要执行rdbmsadminutlxplan.sql来创建plan_table这个表)
sql> set autotrace on
sql>set timing on
通过set autotrace on 来查看语句的执行计划，通过set timing on 来查看语句运行时间。
sql> select count(*) from case_ga_ajzlz;
count(*)
----------
346639
已用时间: 00: 00: 21.38
execution plan
0 select statement optimizer=choose
1 0 sort (aggregate)
2 1 table access (full) of 'case_ga_ajzlz'
……………………
请注意上面分析中的table access(full)，这说明该语句执行了全表扫描。而且查询使用了21.38秒。这时表还没有经过分析。下面我们来对该表进行分析：
sql> analyze table case_ga_ajzlz compute statistics;
表已分析。已用时间: 00: 05: 357.63。然后再来查询：
sql> select count(*) from case_ga_ajzlz;
count(*)
----------
346639
已用时间: 00: 00: 00.71
execution plan
0 select statement optimizer=first_rows (cost=351 card=1)
1 0 sort (aggregate)
2 1 index (fast full scan) of 'pk_ajzlz' (unique) (cost=351
card=346351)
…………………………
请注意，这次时间仅仅用了0.71秒！这要归功于index(fast full scan)。通过分析表，查询使用了pk_ajzlz索引，磁盘i/o大幅减少，速度也大幅提升！下面的实用语句可以
用来生成分析某个用户的所有表和索引，假设用户是gaxzusr：
sql> set pagesize 0
sql> spool d:analyze_tables.sql;
sql> select 'analyze table '||owner||'.'||table_name||'
compute statistics;' from dba_tables where owner='gaxzusr';
sql> spool off
sql> spool spool d:analyze_indexes.sql;
sql> select 'analyze index '||owner||'.'||index_name||'
compute statistics;' from dba_indexes where owner='gaxzusr';
sql> spool off
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 * from mytable where name='tom'
sql> select * from mytable where name='turner'
这个方法可以大幅降低缓冲区利用率低的问题，避免语句重新解释。通过这个功能，可以很大程度上解决硬解析带来的性能下降的问题。个人感觉可根据系统的实际情况，决定是否将该参数改成force。该参数默认是exact。不过一定要注意，修改之前，必须先给oracle打补丁，否则改之后oracle会占用100%的cpu,无法使用。对于oracle9i，可以设置成similar，这个设置综合了force和exact的优点。不过请慎用这个功能，这个参数也可能带来很大的负面影响！
四．将常用的小表、索引钉在数据缓存keep池中
内存上数据读取速度远远比硬盘中读取要快，
，内存中数据读的速度是硬盘的14000倍！如果资源比较丰富，把常用的小的、而且经常进行全表扫描的表给钉内存中，当然是在好不过了。可以简单的通过alter table tablename cache来实现，在oracle8i之后可以使用alter table table storage(buffer_pool keep)。一般来说，可以考虑把200数据块之内的表放在keep池中，当然要根据内存大小等因素来定。关于如何查出那些表或索引符合条件，可以使用本文提供的access.sql和access_report.sql。这两个脚本是著名的oracle专家 burleson写的，你也可以在读懂了情况下根据实际情况调整一下脚本。对于索引，可以通过alter index indexname storage(buffer_pool keep)来钉在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来对比一下排序查询时间变化。注意，这个参数的最大值与平台有关系。