前言
在过去的十年中,Oracle已经成为世界上最专业的数据库之一。对于IT专家来说,就是要确保利用Oracle的强大特性来提高他们公司的生产力。最有效的方法之一是通过Oracle调优。它有大量的调整参数和技术来改进你的Oracle数据库的性能。
Oracle调优是一个复杂的主题。关于调优可以写整整一本书,不过,为了改善Oracle数据库的性能,有一些基本的概念是每个Oracle DBA都应该遵从的。
在这篇简介中,我们将简要地介绍以下的Oracle主题:
--外部调整:我们应该记住Oracle并不是单独运行的。因此我们将查看一下通过调整Oracle服务器以得到高的性能。
--Row re-sequencing以减少磁盘I/O:我们应该懂得Oracle调优最重要的目标是减少I/O。
--Oracle SQL调整。Oracle SQL调整是Oracle调整中最重要的领域之一,只要通过一些简单的SQL调优规则就可以大幅度地提升SQL语句的性能,这是一点都不奇怪的。--调整Oracle排序:排序对于Oracle性能也是有很大影响的。
--调整Oracle的竞争:表和索引的参数设置对于UPDATE和INSERT的性能有很大的影响。
我们首先从调整Oracle外部的环境开始。如果内存和CPU的资源不足的话,任何的Oracle调整都是没有帮助的。
外部的性能问题
Oracle并不是单独运行的。Oracle数据库的性能和外部的环境有很大的关系。这些外部的条件包括有:
.CPU--CPU资源的不足令查询变慢。当查询超过了Oracle服务器的CPU性能时,你的数据库性能就受到CPU的限制。
.内存--可用于Oralce的内存数量也会影响SQL的性能,特别是在数据缓冲和内存排序方面。
.网络--大量的Net8通信令SQL的性能变慢。
许多新手都错误的认为应该首先调整Oracle数据库,而不是先确认外部资源是否足够。实际上,如果外部环境出现瓶颈,再多的Oracle调整都是没有帮助的。
在检查Oracle的外部环境时,有两个方面是需要注意的:
1、当运行队列的数目超过服务器的CPU数量时,服务器的性能就会受到CPU的限制。补救的方法是为服务器增加额外的CPU或者关闭需要很多处理资源的组件,例如Oracle Parallel Query。
2、内存分页。当内存分页时,内存容量已经不足,而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存,减少Oracle SGA的大小,或者关闭Oracle的多线程服务器。
可以使用各种标准的服务器工具来得到服务器的统计数据,例如vmstat,glance,top和sar。DBA的目标是确保数据库服务器拥有足够的CPU和内存资源来处理Oracle的请求。
以下让我们来看一下Oracle的row-resequencing是如何能够极大地减少磁盘I/O的。
Row-resequencing(行的重新排序)
就象我们上面提到的,有经验的Oracle
DBA都知道I/O是响应时间的最大组成部分。其中磁盘I/O特别厉害,因为当Oracle由磁盘上的一个数据文件得到一个数据块时,读的进程就必须等待
物理I/O操作完成。磁盘操作要比数据缓冲慢10,000倍。因此,如果可以令I/O最小化,或者减少由于磁盘上的文件竞争而带来的瓶颈,就可以大大地改
善Oracle数据库的性能。
如果系统响应很慢,通过减少磁盘I/O就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索primary-key索引来访问表,那么重新
以CTAS的方法组织表将是你减少I/O的首要策略。通过在物理上将行排序为和primary-key索引一样的顺序,就可以加快获得数据的速度。
就象磁盘的负载平衡一样,行的重新排序也是很简单的,而且也很快。通过与其它的DBA管理技巧一起使用,就可以在高I/O的系统中大大地减少响应的时间。
在高容量的在线事务处理环境中(online transaction
processing,OLTP),数据是由一个primary索引得到的,重新排序表格的行就可以令连续块的顺序和它们的primary索引一样,这样
就可以在索引驱动的表格查询中,减少物理I/O并且改善响应时间。这个技巧仅在应用选择多行的时候有用,或者在使用索引范围搜索和应用发出多个查询来得到
连续的key时有效。对于随机的唯一primary-key(主键)的访问将不会由行重新排序中得到好处。
让我们看一下它是如何工作的。考虑以下的一个SQL的查询,它使用一个索引来得到100行:
select
salary
from
employee
where
last_name like 'B%';
这个查询将会使用last_name_index,搜索其中的每一行来得到目标行。这个查询将会至少使用100次物理磁盘的读取,因为employee的行存放在不同的数据块中。
不过,如果表中的行已经重新排序为和last_name_index的一样,同样的查询又会怎样处理呢?我们可以看到这个查询只需要三次的磁盘I/O就读完全部100个员工的资料(一次用作索引的读取,两次用作数据块的读取),减少了97次的块读取。
重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何,以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度,可以查看数据字典中的dba_indexes和dba_tables视图得到。
在dba_indexes的视图中,查看clustering_factor列。如果clustering_factor的值和表中的块数目大致一
样,那么你的表和索引的顺序是一样的。不过,如果clustering_factor
的值接近表中的行数目,那就表明表格中的行和索引的顺序是不一样的。
行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中,行重新排序可以令查询的性能提高三倍。
一旦你已经决定重新排序表中的行,你可以使用以下的工具之一来重新组织表格。
. 使用Oracle的Create Table As Select (CTAS) 语法来拷贝表格
. Oracle9i自带的表格重新组织工具
以下,我们来看以下SQL语句的调优。
SQL调优
Oracle的SQL调优是一个复杂的主题,甚至是需要整本书来介绍Oracle SQL调优的细微差别。不过有一些基本的规则是每个Oracle DBA都需要跟从的,这些规则可以改善他们系统的性能。SQL调优的目标是简单的:
.
消除不必要的大表全表搜索:不必要的全表搜索导致大量不必要的I/O,从而拖慢整个数据库的性能。调优专家首先会根据查询返回的行数目来评价SQL。在一
个有序的表中,如果查询返回少于40%的行,或者在一个无序的表中,返回少于7%的行,那么这个查询都可以调整为使用一个索引来代替全表搜索。对于不必要
的全表搜索来说,最常见的调优方法是增加索引。可以在表中加入标准的B树索引,也可以加入bitmap和基于函数的索引。要决定是否消除一个全表搜索,你
可以仔细检查索引搜索的I/O开销和全表搜索的开销,它们的开销和数据块的读取和可能的并行执行有关,并将两者作对比。在一些情况下,一些不必要的全表搜
索的消除可以通过强制使用一个index来达到,只需要在SQL语句中加入一个索引的提示就可以了。
.
在全表搜索是一个最快的访问方法时,将小表的全表搜索放到缓存中,调优专家应该确保有一个专门的数据缓冲用作行缓冲。在Oracle7中,你可以使用
alter table xxx cache语句,在Oracle8或以上,小表可以被强制为放到KEEP池中缓冲。
. 确保最优的索引使用 :对于改善查询的速度,这是特别重要的。有时Oracle可以选择多个索引来进行查询,调优专家必须检查每个索引并且确保Oracle使用正确的索引。它还包括bitmap和基于函数的索引的使用。
. 确保最优的JOIN操作:有些查询使用NESTED LOOP join快一些,有些则是HASH join快一些,另外一些则是sort-merge join更快。
这些规则看来简单,不过它们占SQL调优任务的90%,并且它们也无需完全懂得Oracle SQL的内部运作。以下我们来简单概览以下Oracle SQL的优化。
我们首先简要查看Oracle的排序,并且看一看排序操作是如何影响性能的。
调整Oracle的排序操作
排序是SQL语法中一个小的方面,但很重要,在Oracle的调整中,它常常被忽略。当使用create index、ORDER BY或者GROUP BY的语句时,Oracle数据库将会自动执行排序的操作。通常,在以下的情况下Oracle会进行排序的操作:
使用Order by的SQL语句
使用Group by的SQL语句
在创建索引的时候
进行table join时,由于现有索引的不足而导致SQL优化器调用MERGE SORT
当与Oracle建立起一个session时,在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接
(dedicated
connection),那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个Program Global
Area (PGA)
。如果连接是通过多线程服务器建立的,那么排序的空间就在large_pool中分配。不幸的是,对于所有的session,用做排序的内存量都必须是一
样的,我们不能为需要更大排序的操作分配额外的排序区域。因此,设计者必须作出一个平衡,在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序
(disk
sorts)的同时,对于那些并不需要进行很大排序的任务,就会出现一些浪费。当然,当排序的空间需求超出了sort_area_size的大小时,这时
将会在TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14,000倍。
上面我们已经提到,私有排序区域的大小是有init.ora中的sort_area_size参数决定的。每个排序所占用的大小由init.ora中
的sort_area_retained_size参数决定。当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在Oracle实例中的临时表
空间中进行。
磁盘排序的开销是很大的,有几个方面的原因。首先,和内存排序相比较,它们特别慢;而且磁盘排序会消耗临时表空间中的资源。Oracle还必须分配缓
冲池块来保持临时表空间中的块。无论什么时候,内存排序都比磁盘排序好,磁盘排序将会令任务变慢,并且会影响Oracle实例的当前任务的执行。还有,过
多的磁盘排序将会令free buffer waits的值变高,从而令其它任务的数据块由缓冲中移走。
分享到:
相关推荐
oracle海量查询的数据优化! 建立“适当”的索引是实现查询优化的首要前提。
### Oracle优化方法与实践 #### 一、优化器模式选择 在进行Oracle数据库优化时,首先需要关注的是优化器模式的选择。Oracle提供了多种优化器模式,包括基于规则的优化器(RULE)、基于成本的优化器(COST)以及...
《基于成本的Oracle优化法则》是数据库专家Jonathan Lewis的一部经典著作,该书深入探讨了Oracle数据库的性能优化策略,特别是在成本基础优化方面。Oracle数据库是全球广泛使用的大型企业级数据库系统,其性能优化...
Oracle 优化器介绍 Oracle 优化器是 Oracle 数据库中的一种核心组件,负责生成执行计划,以提高 SQL 语句的执行效率。 Oracle 优化器可以分为两大类:基于规则的优化器(RBO)和基于成本的优化器(CBO)。 基于...
"基于成本的Oracle优化法则"是一个重要的概念,它涉及到Oracle数据库查询优化器的工作原理。在这个主题中,我们将深入探讨这一法则,理解其背后的机制,并学习如何利用这些知识来提升数据库性能。 1. **基于成本的...
Oracle 优化常用概念 Oracle 优化器是 Oracle 数据库管理系统中一个核心组件,负责根据用户提交的 SQL 语句生成最优的执行计划,以提高查询效率。以下是 Oracle 优化器中的一些常用概念: CBO/RBO Oracle 优化...
《Oracle优化日记——一个金牌DBA的故事》这本书深入浅出地揭示了Oracle数据库优化的精髓。作为一名金牌DBA,作者以其丰富的实战经验,通过一系列的实际案例,讲述了在Oracle数据库管理与优化过程中的种种挑战和解决...
Oracle优化设计方案旨在提升数据库性能,确保数据安全,便于管理和开发。设计Oracle数据库时,需要遵循一系列基本原则和步骤。 首先,逻辑建模是根据系统需求分析数据间的内在和外在关系,构建整个系统的数据结构。...
2. 性能优化:Oracle数据库的高效性能为PeopleSoft应用提供了强大的后台支持。 3. 扩展性:两者结合可以轻松应对企业规模扩大或业务复杂度增加的情况。 4. 高可用性:Oracle的RAC功能可以确保即使在硬件故障情况下,...
### Oracle优化重量级知识点解析 #### 一、Oracle优化的重要性 在数据库管理领域,Oracle数据库因其卓越的性能、稳定性和安全性而被广泛采用。然而,随着业务量的增长和技术的发展,即使是像Oracle这样的顶级...
Oracle是世界上最广泛使用的数据库管理系统之一,尤其在企业级应用中占据主导地位。这个"不错的Oracle课件!"显然是为了帮助初学者理解并掌握Oracle数据库的基础知识。以下将根据标题、描述以及压缩包中的文件名来...
优化笔记 sql性能的调整-总结 SQL代码性能优化 Oracle语句优化53个规则详解 ORACLE9i优化设计与系统调整 oracle9i优化器介绍 oracle9i的查询优化 ……
### Oracle优化教程知识点详解 #### 一、选用适合的Oracle优化器 在Oracle数据库中,优化器的选择对于SQL语句的执行效率至关重要。Oracle提供了三种优化器类型:基于规则(RULE)、基于成本(COST)和选择性...
Oracle SQL 优化 Oracle SQL 优化是数据库性能优化的关键部分。为了提高数据库的性能,我们需要从五个方面进行调整:去掉不必要的大型表的全表扫描、缓存小型表的全表扫描、检验优化索引的使用、检验优化的连接技术...
Oracle数据库性能优化是确保系统高效运行的关键环节,尤其是在大数据量和高并发的环境中。Oracle数据库因其先进、完整和集成的特性,在市场中占据主导地位,因此深入理解和掌握Oracle的优化技术至关重要。 首先,...
Oracle优化是数据库管理中的关键环节,它涉及到性能提升、资源有效利用和系统稳定性等多个方面。在给定的压缩包文件中,我们找到了四个与Oracle优化密切相关的文档:ArcSDE for Oracle优化配置和调整初步、oracle...
该培训资料中包括5大内容块: 1>Oracle分区技术及大数据量操作性能优化 2>Oracle性能问题一般解决思路 3>Oracle常用hint 4>Oracle动态图 5>Oracle执行计划和SQL调优