oracle数据库优化

IhadT 贡献于2018-02-07

作者 Administrator  创建于2014-10-29 12:08:00   修改者IhadT  修改于2018-02-07 08:08:15字数4705

文档摘要:数据库的逻辑配置对数据库性能有很大的影响,为此,ORACLE公司对表空间设计提出了一种优化结构OFA。使用这种结构进行设计会大大简化物理设计中的数据管理。
关键词:

一、数据库优化自由结构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、利用表分区   分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行0扫描,而不必进行FTS(Full  Table  Scan,全表扫描),明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。   2、避免出现行连接和行迁移   在建立表时,由于参数pctfree和pctused不正确的设置,数据块中的数据会出现行链接和行迁移,也就是同一行的数据不保存在同一的数据块中。如果在进行数据查询时遇到了这些数据,那么为了读出这些数据,磁头必须重新定位,这样势必会大大降低数据库执行的速度。因此,在创建表时,就应该充分估计到将来可能出现的数据变化,正确地设置这两个参数,尽量减少数据库中出现行链接和行迁移。   3、控制碎片   碎片(fragmentation)是对一组非邻接的数据库对象的描述。碎片意味着在执行数据库的功能时要耗费额外的资源(磁盘I/O,磁盘驱动的循环延迟,动态扩展,链接的块等),并浪费大量磁盘空间。当两个或多个数据对象在相同的表空间中,会发生区间交叉。在动态增长中,对象的区间之间不再相互邻接。为了消除区间交叉将静态的或只有小增长的表放置在一个表空间中,而把动态增长的对象分别放在各自的表空间中。在create  table、create index、create tablespace时,在storage子句中的参数的合理设置,可以减少碎片的产生。   4、别名的使用   别名是大型数据库的应用技巧,就是表名、列名在查询中以一个字母为别名,查询速度要比建连接表快1.5倍。  五、索引Index和约束 constrain的优化设计     1、管理组织索引   索引可以大大加快数据库的查询速度,索引把表中的逻辑值映射到安全的RowID,因此索引能进行快速定位数据的物理地址。但是有些DBA发现,对一个大型表建立的索引,并不能改善数据查询速度,反而会影响整个数据库的性能。这主要是和SGA的数据管理方式有关。ORACLE在进行数据块高速缓存管理时,索引数据比普通数据具有更高的驻留权限,在进行空间竞争时,ORACLE会先移出普通数据。对一个建有索引的大型表的查询时,索引数据可能会用完所有的数据块缓存空间,ORACLE不得不频繁地进行磁盘读写来获取数据,因此在对一个大型表进行分区之后,可以根据相应的分区建立分区索引。如果对这样大型表的数据查询比较频繁,或者干脆不建索引。另外,DBA创建索引时,应尽量保证该索引最可能地被用于where子句中,如果对查询只简单地制定一个索引,并不一定会加快速度,因为索引必须指定一个适合所需的访问路径。 2、聚簇的使用   Oracle提供了另一种方法来提高查询速度,就是聚簇(Cluster)。所谓聚簇,简单地说就是把几个表放在一起,按一定公共属性混合存放。聚簇根据共同码值将多个表的数据存储在同一个Oracle块中,这时检索一组Oracle块就同时得到两个表的数据,这样就可以减少需要存储的Oracle块,从而提高应用程序的性能。   3、优化设置的索引 优化设置的索引,就必须充分利用才能加快数据库访问速度。ORACLE要使用一个索引,有一些最基本的条件:1)、where子名中的这个字段,必须是复合索引的第一个字段;2)、where子名中的这个字段,不应该参与任何形式的计算;3)、外键字段要创建索引;4)不会出现在WHERE子句中字段不该创建索引;5)、更新非常频繁的字段不适合创建索引。  4、 完整性约束的创建 必须给表的字段添加对应的约束满足其完整性要求:1)、除临时表外,物理表必须指定主键;2)、必须保证主键的无意义性,禁止给主键附加业务含义;3)、主键使用id字段标识,类型为number(10),可根据业务需要,调整长度;4)、主键id的取值禁止从1开始。通常应从1000000000开始,以保持主键长度的一致;5)、主键id的取值必须使用序列实现;6)、根据表的重要性设置好主从表的级联删除关系7)、除特殊说明的字段外,所有字段都可以使用Not null约束;8)、添加一定的检查约束、唯一约束。 六、使用最优的数据库连接和SQL优化方案     1、使用Connection Pool机制   在数据库处理中,资源花销最大的是建立数据库连接,而且用户还会有一个较长的连接等待时间。解决的办法就是复用现有的Connection,也就是使用Connection  Pool对象机制。  Connection  Pool的原理是:IIS+ASP体系中维持了一个连接缓冲池,这样,当下一个用户访问时,直接在连接缓冲池中取得一个数据库连接,而不需重新连接数据库,因此可以大大地提高系统的响应速度。   2、高效地进行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语句返回行超过该值,数值库暂时停止执行,除非用户发出新的指令,开始组织并显示数据,而不是让用户继续等待。 (8)对于count(*),count(1),count(primary key),count(column)尽量使用count(*),因为数据库对count(*)做了优化 (9)尽量少 用join,禁止3表以上的join (10)尽量避免在 where 子句中对字段进行表达式操作   七、充分利用数据的后台处理方案减少网络流量     1、合理创建临时表或视图   所谓创建临时表或视图,就是根据需要在数据库基础上创建新表或视图,对于多表关联后再查询信息的可建新表,对于单表查询的可创建视图,这样可充分利用数据库的容量大、可扩充性强等特点,所有条件的判断、数值计算统计均可在数据库服务器后台统一处理后追加到临时表中,形成数据结果的过程可用数据库的过程或函数来实现。   2、数据库打包技术的充分利用   利用数据库描述语言编写数据库的过程或函数,然后把过程或函数打成包在数据库后台统一运行包即可。  

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档