`
san_yun
  • 浏览: 2651978 次
  • 来自: 杭州
文章分类
社区版块
存档分类
最新评论

优化数据库大幅度提高Oracle的性能

阅读更多
http://unixboy.iteye.com/blog/164399
几个简单的步骤大幅提高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来对比一下排序查询时间变化。注意,这个参数的最大值与平台有关系。

分享到:
评论

相关推荐

    ORACLE DATABASE 11G性能优化攻略_高清_美 Sam R Alapati.pdf

    - **案例一**:某大型电子商务网站通过调整SQL查询逻辑和增加索引来大幅度提高了订单查询的速度。 - **案例二**:一家银行系统通过优化内存配置和采用多租户架构实现了更高的并发处理能力。 - **案例三**:一家医疗...

    Oracle_数据库性能优化

    - **大数据分析平台优化**:利用分区和并行处理技术大幅度提升了复杂查询的执行效率。 #### 七、总结 《Oracle数据库性能与可扩展性:定量方法》不仅涵盖了Oracle数据库性能优化的基本理论知识,还结合了丰富的...

    大型ORACLE数据库优化设计方案

    合理设置这些内存缓冲区的大小,能够大幅度提升数据查询速度,特别是在大数据量环境下,一个足够大的SGA可以显著提高内存命中率,减少磁盘I/O,从而大幅提升数据库的整体性能。 #### 三、规范与反规范设计 数据库...

    提高ORACLE数据库的查询统计速度

    综上所述,要提高Oracle数据库的查询统计速度,需要从多个方面入手进行综合考虑与优化。这不仅涉及到硬件升级和软件配置调整,还需要关注SQL语句编写、索引管理和表空间规划等方面。只有全面细致地考虑这些因素,...

    让Oracle跑的更快2基于海量数据的数据库设计与优化

    通过优化SQL语句,可以大幅度提高数据库的处理能力。 5. 物理存储优化:物理存储设备的性能对Oracle数据库的响应速度同样具有重大影响。合理配置磁盘阵列的RAID级别、选择合适的文件系统、使用异步I/O等都是提升...

    计算机等考三级数据库知识辅导:提高ORACLE数据库的查询统计速度.docx

    总结起来,提高Oracle数据库查询统计速度涉及多个方面,包括合理分配和管理表空间,优化段和回滚段配置,以及确保并发处理能力和数据安全性。这些措施都是基于数据库物理设计和性能调优的理论,通过调整数据库的内部...

    提高ORACLE数据库的查询统计速度参考.pdf

    本文主要探讨了如何提高Oracle数据库的查询统计速度,这些方法基于实际项目开发经验,对于优化数据库性能具有实用价值。 首先,扩大数据表空间至500MB是为了满足大容量数据存储的需求。数据表空间是Oracle数据库中...

    ORACLE数据库优化之SQL语句的并行处理.pdf

    SQL语句的并行处理是Oracle数据库优化的重要手段,它能够充分利用多处理器或多核心的硬件优势,大幅度提升查询性能,尤其是在大数据量的环境下。然而,实现并行处理需要考虑数据库的配置、数据的分布特性以及系统的...

    Oracle数据库10g-数据库技术的革命.pptx

    它旨在提供前所未有的服务质量,同时大幅度降低成本,并简化数据库的管理。这个版本的主要目标包括:最高可用性、可靠性、安全性和性能,以及通过网格计算实现资源的优化利用。 在面对计算孤岛的问题时,Oracle 10g...

    小布老师oracle视频下载

    Oracle 9i是Oracle公司于2000年发布的一款数据库管理系统,它是Oracle 8i的后续版本,在功能上进行了大幅度的增强和优化。DBA(Database Administrator)Fundamentals I是Oracle针对初学者和初级数据库管理员设计的...

    医院信息系统数据库从Oracle8i到10gR2升级的实现.pdf

    通过这种硬件和软件平台的同步升级策略,不仅可以优化数据库性能,还可以确保系统的兼容性和连续性,避免因升级导致的服务中断。 升级后的医院信息系统能够更好地支持临床路径管理,这是一种提高医疗服务质量的重要...

    通过 Oracle Database c 进行内存数据库处理PPT学习教案.pptx

    内存数据库处理的重要性在于,随着内存容量的大幅度增加、成本的显著降低和速度的显著提升,传统的磁盘存储方式已经不能满足对实时交易处理(OLTP)和大数据分析的高要求。例如,DRAM(动态随机存取存储器)的价格自...

    Oracle Database 12C内存数据库处理.pdf

    随着内存技术的发展,内存速度的提升、成本的降低以及容量的大幅度增加,使得在内存中处理数据成为可能,从而显著提升了在线事务处理(OLTP)和商业智能(BI)的性能。 Oracle TimesTen 是Oracle公司的一款内存...

    读懂oracle sql执行计划

    对于复杂查询,执行计划的解读尤为关键,因为正确的执行顺序和选择合适的执行方法可以大幅度提高查询效率。 执行计划的顺序通常表示了Oracle数据库执行SQL语句的步骤顺序。了解执行计划的真实执行顺序有助于我们...

    SQL查询优化技术在Inform ix数据库的应用.pdf

    在实际工作中,优化查询语句可以大幅度提高程序的性能,有时甚至可以达到数十倍的提升效果。 总之,SQL查询优化技术在Informix数据库的应用中扮演着至关重要的角色。合理运用索引、精心设计where子句以及有效的临时...

    twp-oracle-database-in-memory-2245633-zhs.pdf

    Oracle Database In-Memory功能的引入使得分析和报告这样的任务可以几乎瞬间完成,从而大幅度提高业务效率和竞争力。 该技术利用了列式存储(Columnar Storage)和内存计算的优势,相比于传统的行式存储(Row ...

Global site tag (gtag.js) - Google Analytics