没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
[转贴]大型 ORACLE 数据库优化设计方案
大型 ORACLE 数据库优化设计方案
本文主要从大型数据库 ORACLE 环境四个不同级别的调整分析入手,分析 ORACLE 的系统结构和工作机
理,从九个不同方面较全面地总结了 ORACLE 数据库的优化调整方案。
关键词 ORACLE 数据库 环境调整 优化设计 方案
对于 ORACLE 数据库的数据存取,主要有四个不同的调整级别,第一级调整是操作系统级包括硬件平
台,
第二级调整是 ORACLE RDBMS 级的调整, 第三级是数据库设计级的调整, 最后一个调整级是 SQL 级。
通常依此四级调整级别对数据库进行调整、优化,数据库的整体性能会得到很大的改善。下面从九个不同
方面介绍 ORACLE 数据库优化设计方案。
一、数据库优化自由结构 OFA(Optimal flexible Architecture)
数据库的逻辑配置对数据库性能有很大的影响,为此,ORACLE 公司对表空间设计提出了一种优化结构
OFA。使用这种结构进行设计会大大简化物理设计中的数据管理。优化自由结构 OFA,简单地讲就是在数
据库中可以高效自由地分布逻辑数据对象,因此首先要对数据库中的逻辑对象根据他们的使用方式和物理
结构对数据库的影响来进行分类,这种分类包括将系统数据和用户数据分开、一般数据和索引数据分开、
低活动表和高活动表分开等等。
数据库逻辑设计的结果应当符合下面的准则:(1)把以同样方式使用的段类型存储在一起;(2)按
照标准使用来设计系统;(3)存在用于例外的分离区域;(4)最小化表空间冲突;(5)将数据字典分
离。
二、充分利用系统全局区域 SGA(SYSTEM GLOBAL AREA)
SGA 是 oracle 数据库的心脏。用户的进程对这个内存区发送事务,并且以这里作为高速缓存读取命中的
数据,以实现加速的目的。正确的 SGA 大小对数据库的性能至关重要。SGA 包括以下几个部分:
1、数据块缓冲区(data block buffer cache)是 SGA 中的一块高速缓存,占整个数据库大小的 1%-2%,
用来存储从数据库重读取的数据块(表、索引、簇等),因此采用 least recently used (LRU,最近最少使
用)的方法进行空间管理。
2、字典缓冲区。该缓冲区内的信息包括用户账号数据、数据文件名、段名、盘区位置、表说明和权限,
它也采用 LRU 方式管理。
3、重做日志缓冲区。该缓冲区保存为数据库恢复过程中用于前滚操作。
4、SQL 共享池。保存执行计划和运行数据库的 SQL 语句的语法分析树。也采用 LRU 算法管理。如果设
置过小,语句将被连续不断地再装入到库缓存,影响系统性能。
另外,SGA 还包括大池、JAVA 池、多缓冲池。但是主要是由上面 4 种缓冲区构成。对这些内存缓冲区
的合理设置,可以大大加快数据查询速度,一个足够大的内存区可以把绝大多数数据存储在内存中,只有
那些不怎么频繁使用的数据,才从磁盘读取,这样就可以大大提高内存区的命中率。
三、规范与反规范设计数据库
1、规范化
范式是符合某一级别的关系模式的集合,根据约束条件的不同,一般有 1NF、2NF、3NF 三种范式。
规范化理论是围绕这些范式而建立的。规范化的基本思想是逐步消除数据依赖中不合适的部分,使模式中
的各关系模式达到某种程度的“分离”,即采用“一事一地”的模式设计原则,因此,所谓规范化实质上就是
概念的单一化。数据库中数据规范化的优点是减少了数据冗余,节约了存储空间,相应逻辑和物理的 I/O
次数减少,同时加快了增、删、改的速度。但是一个完全规范化的设计并不总能生成最优的性能,因为对
数据库查询通常需要更多的连接操作,从而影响到查询的速度。故有时为了提高某些查询或应用的性能而
有意破坏规范规则,即反规范化。
2、反规范化
⑴ 反规范的必要性
是否规范化的程度越高越好呢?答案是否定的,应根据实际需要来决定,因为“分离”越深,产生的关系
越多,结构越复杂。关系越多,连接操作越频繁,而连接操作是最费时间的,在数据库设计中特别对以查
询为主的数据库设计来说,频繁的连接会严重影响查询速度。所以,在数据库的设计过程中有时故意保留
非规范化约束,或者规范化以后又反规范,这样做通常是为了改进数据库的查询性能,加快数据库系统的
响应速度。
⑵ 反规范技术
在进行反规范设计之前,要充分考虑数据的存取需求,常用表的大小、特殊的计算、数据的物理存储等。
常用的反规范技术有合理增加冗余列、派生列,或重新组表几种。反规范化的好处是降低连接操作的需求、
降低外码和索引数目,减少表的个数,从而提高查询速度,这对于性能要求相对较高的数据库系统来说,
能有效地改善系统的性能,但相应的问题是可能影响数据的完整性,加快查询速度的同时降低修改速度。
3、数据库设计中的优化策略
数据应当按两种类别进行组织:频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数
据,内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据,内部设计应当物理规范化。比较
复杂的方法是将规范化的表作为逻辑数据库设计的基础,然后再根据整个应用系统的需要,物理地非规范
化数据。规范与反规范都是建立在实际的操作基础之上的约束,脱离了实际两者都没有意义。只有把两者
合理地结合在一起,才能相互补充,发挥各自的优点。
四、合理设计和管理表
1、利用表分区
分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当
对这个表进行查询时,只需要在表分区中进行扫描,而不必进行 FTS(Full Table Scan,全表扫描),明显
缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O,一个精心设
置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。
2、避免出现行连接和行迁移
在建立表时,由于参数 pctfree 和 pctused 不正确的设置,数据块中的数据会出现行链接和行迁移,也就
是同一行的数据不保存在同一的数据块中。如果在进行数据查询时遇到了这些数据,那么为了读出这些数
据,磁头必须重新定位,这样势必会大大降低数据库执行的速度。因此,在创建表时,就应该充分估计到
将来可能出现的数据变化,正确地设置这两个参数,尽量减少数据库中出现行链接和行迁移。
3、控制碎片
碎片(fragmentation)是对一组非邻接的数据库对象的描述。碎片意味着在执行数据库的功能时要耗费额
外的资源(磁盘 I/O,磁盘驱动的循环延迟,动态扩展,链接的块等),并浪费大量磁盘空间。当两个或
多个数据对象在相同的表空间中,会发生区间交叉。在动态增长中,对象的区间之间不再相互邻接。为了
消除区间交叉将静态的或只有小增长的表放置在一个表空间中,而把动态增长的对象分别放在各自的表空
间中。在 create table、、create index、create tablespace、create cluster 时,在 storage 子句中的参数
的合理设置,可以减少碎片的产生。
4、别名的使用
别名是大型数据库的应用技巧,就是表名、列名在查询中以一个字母为别名,查询速度要比建连接表快
1.5 倍。
5、回滚段的交替使用
由于数据库配置对应用表具有相对静止的数据字典和极高的事务率特点。而且数据库的系统索引段、数
据段也具有相对静止,并发现在应用中最高的负荷是回滚段表空间。把回滚段定义为交替引用,这样就达
到了循环分配事务对应的回滚段,可以使磁盘负荷很均匀地分布。
五、索引 Index 的优化设计
1、管理组织索引
索引可以大大加快数据库的查询速度,索引把表中的逻辑值映射到安全的 RowID,因此索引能进
行快速定位数据的物理地址。但是有些 DBA 发现,对一个大型表建立的索引,并不能改善数据查询速度,
反而会影响整个数据库的性能。这主要是和 SGA 的数据管理方式有关。ORACLE 在进行数据块高速缓存
管理时,索引数据比普通数据具有更高的驻留权限,在进行空间竞争时,ORACLE 会先移出普通数据。
对一个建有索引的大型表的查询时,索引数据可能会用完所有的数据块缓存空间,ORACLE 不得不频繁
地进行磁盘读写来获取数据,因此在对一个大型表进行分区之后,可以根据相应的分区建立分区索引。如
果对这样大型表的数据查询比较频繁,或者干脆不建索引。另外,DBA 创建索引时,应尽量保证该索引
最可能地被用于 where 子句中,如果对查询只简单地制定一个索引,并不一定会加快速度,因为索引必须
指定一个适合所需的访问路径。2、聚簇的使用
Oracle 提供了另一种方法来提高查询速度,就是聚簇(Cluster)。所谓聚簇,简单地说就是把几个表放
在一起,按一定公共属性混合存放。聚簇根据共同码值将多个表的数据存储在同一个 Oracle 块中,这时
检索一组 Oracle 块就同时得到两个表的数据,这样就可以减少需要存储的 Oracle 块,从而提高应用程序
的性能。
3、优化设置的索引,就必须充分利用才能加快数据库访问速度。ORACLE 要使用一个索引,有一些最基
本的条件:1)、where 子名中的这个字段,必须是复合索引的第一个字段;2)、where 子名中的这个字
段,不应该参与任何形式的计算。Û Sal*(2*90/100)
六、多 CPU 和并行查询 PQO(Parallel Query Option)方式的利用
1、尽量利用多个 CPU 处理器来执行事务处理和查询
CPU 的快速发展使得 ORACLE 越来越重视对多 CPU 的并行技术的应用,一个数据库的访问工作可以用
多个 CPU 相互配合来完成,加上分布式计算已经相当普遍,只要可能,应该将数据库服务器和应用程序
的 CPU 请求分开,或将 CPU 请求从一个服务器移到另一个服务器。对于多 CPU 系统尽量采用 Parallel
Query Option(PQO,并行查询选项)方式进行数据库操作。
2、使用 Parallel Query Option(PQO,并行查询选择)方式进行数据查询
使用 PQO 方式不仅可以在多个 CPU 间分配 SQL 语句的请求处理,当所查询的数据处于不同的磁盘时,
一个个独立的进程可以同时进行数据读取。
3、使用 SQL*Loader Direct Path 选项进行大量数据装载
使用该方法进行数据装载时,程序创建格式化数据块直接写入数据文件中,不要求数据库内核的其他
I/O。
七、实施系统资源管理分配计划
ORACLE 提供了 Database Resource Manager(DRM,数据库资源管理器)来控制用户的资源分配,
DBA 可以用它分配用户类和作业类的系统资源百分比。在一个 OLDP 系统中,可给联机用户分配 75%的
CPU 资源,剩下的 25%留给批用户。另外,还可以进行 CPU 的多级分配。除了进行 CPU 资源分配外,
DRM 还可以对资源用户组执行并行操作的限制。
八、使用最和 SQL 优化方优的数据库连接案
1、使用直接的 OLE DB 数据库连接方式。
通过 ADO 可以使用两种方式连接数据库,一种是传统的 ODBC 方式,一种是 OLE DB 方式。ADO 是建
立在 OLE DB 技术上的,为了支持 ODBC,必须建立相应的 OLE DB 到 ODBC 的调用转换,而使用直接
的 OLE DB 方式则不需转换,从而提高处理速度。
2、使用 Connection Pool 机制
在数据库处理中,资源花销最大的是建立数据库连接,而且用户还会有一个较长的连接等待时间。解决
的办法就是复用现有的 Connection,也就是使用 Connection Pool 对象机制。
Connection Pool 的原理是:IIS+ASP 体系中维持了一个连接缓冲池,这样,当下一个用户访问时,直接
在连接缓冲池中取得一个数据库连接,而不需重新连接数据库,因此可以大大地提高系统的响应速度。
3、高效地进行 SQL 语句设计
通常情况下,可以采用下面的方法优化 SQL 对数据操作的表现:
(1)减少对数据库的查询次数,即减少对系统资源的请求,使用快照和显形图等分布式数据库对象可
以减少对数据库的查询次数。
(2)尽量使用相同的或非常类似的 SQL 语句进行查询,这样不仅充分利用 SQL 共享池中的已经分析
的语法树,要查询的数据在 SGA 中命中的可能性也会大大增加。
(3)限制动态 SQL 的使用,虽然动态 SQL 很好用,但是即使在 SQL 共享池中有一个完全相同的查询
值,动态 SQL 也会重新进行语法分析。
(4)避免不带任何条件的 SQL 语句的执行。没有任何条件的 SQL 语句在执行时,通常要进行 FTS,
数据库先定位一个数据块,然后按顺序依次查找其它数据,对于大型表这将是一个漫长的过程。
(5)如果对有些表中的数据有约束,最好在建表的 SQL 语句用描述完整性来实现,而不是用 SQL 程
序中实现。
(6)可以通过取消自动提交模式,将 SQL 语句汇集一组执行后集中提交,程序还可以通过显式地用
COMMIT 和 ROLLBACL 进行提交和回滚该事务。
(7)检索大量数据时费时很长,设置行预取数则能改善系统的工作表现,设置一个最大值,当 SQL 语
句返回行超过该值,数值库暂时停止执行,除非用户发出新的指令,开始组织并显示数据,而不是让用户
继续等待。
九、充分利用数据的后台处理方案减少网络流量
1、合理创建临时表或视图
所谓创建临时表或视图,就是根据需要在数据库基础上创建新表或视图,对于多表关联后再查询信息的
可建新表,对于单表查询的可创建视图,这样可充分利用数据库的容量大、可扩充性强等特点,所有条件
的判断、数值计算统计均可在数据库服务器后台统一处理后追加到临时表中,形成数据结果的过程可用数
据库的过程或函数来实现。
2、数据库打包技术的充分利用
利用数据库描述语言编写数据库的过程或函数,然后把过程或函数打成包在数据库后台统一运行包即可。
3、数据复制、快照、视图,远程过程调用技术的运用
数据复制,即将数据一次复制到本地,这样以后的查询就使用本地数据,但是只适合那些变化不大的数
据。使用快照也可以在分布式数据库之间动态复制数据,定义快照的自动刷新时间或手工刷新,以保证数
据的引用参照完整性。调用远程过程也会大大减少因频繁的 SQL 语句调用而带来的网络拥挤。
总之,对所有的性能问题,没有一个统一的解决方法,但 ORACLE 提供了丰富的选择环境,可以从
ORACLE 数据库的体系结构、软件结构、模式对象以及具体的业务和技术实现出发,进行统筹考虑。提
高系统性能需要一种系统的整体的方法,在对数据库进行优化时,应对应用程序、I/O 子系统和操作系统
(OS)进行相应的优化。优化是有目的地更改系统的一个或多个组件,使其满足一个或多个目标的过程。
对 Oracle 来说,优化是进行有目的的调整组件级以改善性能,即增加吞吐量,减少响应时间。如果 DBA
能从上述九个方面综合考虑优化方案,相信多数 ORACLE 应用可以做到按最优的方式来存取数据。
我们要做到不但会写 SQL,还要做到写出性能优良的 SQL,以下为笔者学习、摘录、并汇总部分资料与大家
分享!
(1)Û Û Û 选择最有效率的表名顺序(只在基于规则的优化器中有效):
ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名,FROM 子句中写在最后的表(基础表
driving table)将被最先处理,在 FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基
础表。如果有 3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那
剩余55页未读,继续阅读
资源评论
老帽爬新坡
- 粉丝: 79
- 资源: 2万+
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功