此文为 转载。
几个简单的步骤大幅提高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,这个
另外,我们可以将优化模式进行
修改。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性能,oracle优化手册
Oracle 性能调优的基本方案 Oracle 性能调优是数据库管理员和开发者非常关心的问题。为了确保数据库的高效运行,需要对 Oracle 性能进行调优。...通过这八个步骤,可以提高 Oracle 数据库的性能,提高用户的满意度。
Oracle性能调优 Oracle性能调优 Oracle性能调优 Oracle性能调优 Oracle性能调优 Oracle性能调优 Oracle性能调优
### Oracle数据库性能分析 #### 一、Oracle体系结构 Oracle数据库是一个复杂且功能强大的...通过以上几个方面的深入理解和实践,你可以更好地掌握Oracle数据库性能分析的基本方法,从而有效地管理和优化数据库性能。
Oracle数据库是全球广泛使用的...以上这些知识点是提高Oracle数据库性能的基础,但每个系统都有其独特性,实际操作中需要结合具体情况进行分析和调整。通过持续监控、测试和优化,可以不断提升Oracle数据库的运行效率。
《Oracle性能优化绝版好书:高级OWI与ORACLE性能调整》这本书深入探讨了Oracle数据库性能优化的关键技术和策略,特别关注了Oracle Wait Interface (OWI)和性能调整的实践应用。OWI是Oracle数据库中用于监控和诊断...
在进行Oracle性能日志收集时需要注意以下几点: - **定期检查**:为了保持数据库性能的最佳状态,建议定期检查AWR和ADDM报告,及时发现并解决问题。 - **性能基线**:建立性能基线对于评估数据库性能变化至关重要。...
根据提供的信息,我们可以推断出这本名为“高级owi与oracle性能调整”的书籍主要讨论了高级owi技术以及Oracle数据库性能优化的相关内容。由于提供的部分页面信息仅包含联系方式,并没有具体的章节内容,因此以下将...
ORACLE 的这个功能大大地提高了 SQL 的执行性能并节省了内存的使用。 共享的语句必须满足三个条件: A. 字符级的比较:当前被执行的语句和共享池中的语句必须完全相同。 B. 两个语句所指的对象必须完全相同。 C....
总的来说,Oracle数据库性能优化是一个全面的过程,涵盖硬件配置、软件设置、SQL优化、存储策略等多个方面。需要根据系统负载、业务需求以及资源状况进行综合分析,持续监控和调整,以实现最优的数据库运行状态。...
本文将深入探讨“用智能优化限制提高Oracle数据库性能”的核心知识点,通过解析智能优化、Oracle数据库性能提升策略,以及具体的SQL执行计划调整技巧,为读者提供一份全面的指南。 ### 智能优化:提升Oracle数据库...
### Oracle性能优化技巧详解 ...理解并合理配置优化模式、选择合适的表扫描策略以及充分利用SQL重用机制等都是提升Oracle性能的关键步骤。希望本文能帮助读者更好地理解和掌握Oracle性能优化的核心技巧。
### Oracle性能优化求生指南 #### 一、Oracle数据库性能优化概述 在现代企业环境中,Oracle数据库因...通过学习本书提供的方法论和技术点,你将能够更加自信地面对各种挑战,并有效地提高Oracle系统的整体性能水平。
Oracle数据库作为全球广泛使用的商业数据库管理系统,其性能优化对于确保数据库稳定运行、提高应用响应速度至关重要。性能优化不仅需要通过合理设计数据库模式、编写高效SQL语句等手段,而且还需要利用专门的性能...
Oracle性能优化是数据库管理员和开发人员关注的重要领域,它涉及到一系列技术、工具和策略,旨在提升Oracle数据库的运行效率,减少资源消耗,提高系统响应速度,以及改善用户体验。以下是对Oracle性能优化的详细阐述...
本文将从多个角度出发,探讨Oracle性能诊断的方法与实践。 #### 一、性能诊断的重要性 在现代企业环境中,Oracle数据库承载着大量的数据处理任务,任何性能瓶颈都可能引发连锁反应,导致整个系统的响应时间增加,...
在数据库管理领域,Oracle是一个广泛使用的高性能关系型数据库系统。对于任何数据库系统而言,查询效率是衡量其性能的重要指标。本文将重点讨论如何提高Oracle查询效率,主要基于提供的压缩包文件"ORACLE_SQL性能...
根据提供的标题“高级owi与oracle性能调整”及描述“高级owi与oracle性能调整”,我们可以推断出这份文档主要探讨的是如何通过高级优化方法提升Oracle数据库的性能。虽然提供的具体内容并没有实质性的技术信息,但...