求知 文章 文库 Lib 视频 iProcess 课程 角色 咨询 工具 讲座 Modeler   Code  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
 
优化数据库大幅度提高Oracle的性能
 

2010-08-30 来源:网络

 

几个简单的步骤大幅提高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太多,而导致查询很慢。如果没有使用执行计划稳定性,则应该把表和索引都分析一下,这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。对于少于100万的表,可以考虑分析整个表,对于很大的表,可以按百分比来分析,但是百分比不能过低,否则生成的统计信息可能不准确。可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间,索引可以通过DBA_INDEXES的LAST_ANALYZED列。

下面通过例子来说明分析前后的速度对比。(表CASE_GA_AJZLZ大约有35万数据,有主键)首先在SQLPLUS中打开自动查询执行计划功能。(第一次要执行\RDBMS\ADMIN\utlxplan.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来对比一下排序查询时间变化。注意,这个参数的最大值与平台有关系。



MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号