设为首页收藏本站优领域

优领域

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
优领域 优领域 IT领域 数据库 Oracle 查看内容

Oracle数据库安全技术与策略

2014-5-21 10:23| 发布者: dzly| 查看: 675| 评论: 0|原作者: 王可君 王爱红

摘要: 本文系统分析了SQL 语句的执行过程,针对不同类型的应用系统对其SQL 语句的优化目标进行了 分析、阐述,并从三个不同的角度对SQL 语句的优化方法进行了探讨。

  1 引 言
  近年来,随着网络和电子商务的迅速发展,以数据库技术为主体的计算机应用系统已得到广泛应用,数据库的规模也不断扩大,且伴随着数据挖掘、数据分析的不断深入,数据处理也越来越复杂。庞大数据量加上复杂的数据处理过程,使数据库服务器经常超负荷运作,甚至出现死锁现象,严重影响使用效果。因此,数据库系统的性能优化问题也越来越受到关注。经统计,数据库80% 的性能问题都是由于不良的SQL 语句引起的,因此对SQL 进行优化是提高整个系统效率的重要途径。SQL 语句优化就是选择最有效的执行计划来执行SQL 语句的过程,这是在处理任何数据的语句( S E L E C T 、I N S E R T 、U P D A T E 或DELETE) 中的一个重要步骤。总体上讲,就是需要识别出最常用的SQL语句,通过仔细审查SQL 的执行计划以及使用Oracle 提示(HINTS) 调整执行计划来调整各个语句。本文对SQL 语句执行过程进行了分析,阐述了不同类型系统的优化目标,并从三个不同的角度探讨了如何设计最佳的SQL 语句,提高SQL 语句在数据中的解析和执行效率,提升Oracle 数据系统的性能。
  2 SQL 语句执行过程分析
  Oracle 数据库执行一条SQL 语句通常可分为四个阶段(如图1 所示): 解析(Parse)、绑定(Bind)、执行(Execute)和提取结果(Fetch)。


  2.1 解析阶段
  在语法解析期间,SQL 语句从用户进程传送到Oracle,SQL 语句经语法解析后,SQL 语句本身与解析的信息都被装入到共享池中。语法解析分别执行下列操作:(1)计算语句值;(2)查看共享存储池中有无与此语句值相同的语句,若有则执行,若没有则进行下一步;(3)准备要运行的SQL 语句,检查语义、语法和相关权限;(4)在共享池中为新语句创建空间;(5)将新语句放在共享池中;(6)修改共享池图,表明语句值和其在共享池中的位置;(7)确定执行计划。
  2.2 绑定阶段
  此时,Oracle 知道了SQL 语句的意思,但仍没有足够的信息用于执行该语句。Oracle 需要得到在语句中列出的所有变量的值。得到这个值的过程就叫绑定变量,因为指定了变量名,当再次执行之前无需重新捆绑变量,而只需改变绑定变量的值。而Oracle在每次执行时,仅仅使用内存地址来查找此值。
  2.3 执行阶段
  到了现在这个时候,Oracle 拥有所有需要的信息与资源,因此可以真正运行SQL 语句了。如果该语句为SELECT 查询或INSERT 语句, 则不需要锁定任何行,因为没有数据需要被改变。然而,如果语句为UPDATE或DELETE 语句,则该语句影响的所有行都被锁定,防止该用户提交或回滚之前,别的用户对这些数据进行修改,从而保证了数据的一致性。
  2.4 提取结果阶段
  在FETCH 阶段,行数据被取出来,每个后续的存取操作检索结果集中的下一行数据,直到最后一行被取出来。
  并要执行必要的I/O 和排序操作。
  产生低效率的SQL 语句,主要是因为开发人员在使用SQL 时往往只注重结果的正确性,而忽略了SQL 的性能,或者是开发人员只关注SQL 语句本身的效率,而对SQL 语句执行原理、影响SQL 执行效率的主要因素不清楚,从而导致SQL 的运行效率较低。因此要想写出高效的SQL 语句,开发者应熟练掌握学习SQL语法、各种内嵌函数、分析函数的用法,深入了解SQL 语句执行原理,并严格遵守开发规范。
  3 SQL 优化目标
  一般来说,Oracle 的应用分为两种类型: 联机事务处理(OLTP:Online Transaction Processing)和联机分析处理(OLAP:OnlineAnalytical Processing)。根据应用类型的不同,性能优化的目标有所不同。
  3.1 联机事务处理
  该类型的应用是高吞吐量,插入、更新、删除操作比较多的系统,这些系统以不断增长的大容量数据为特征。
  OLTP 的主要目标是可用性、速度、并发性和可恢复性。当设计这类系统时,必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与Cluster 表,因为这些结构会使插入和更新操作变慢。此种类型的应用程序把吞吐量定义为性能指标。
  3.2 联机分析处理
  该类型的应用将大量信息进行提取形成报告,协助决策者作出正确的判断, 是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果。OLAP的关键目标是速度、精确性和可用性。
  该种类型的设计往往与OLTP 设计的理念背道而驰,一般建议使用数据冗余、大量索引、Cluster 表、并行查询等。
  此种类型的应用程序把响应时间定义为性能指标。
  4 SQL 语句优化方法
  虽然构成SQL 语句的词汇和语法都是简单明了的,但是由它们组合出来的SQL 语句却是千变万化,没有固定模式。这也就造成了SQL 语句的优化没有可遵循的固定流程或操作方法,只能依靠一些基本的原则和思路再加上自己的经验和直觉进行判断。以下从三个不同的角度,对SQL 语句的优化方法进行了讨论。
  4.1 减少对磁盘的I/O 访问
  对磁盘过多的I/O 访问一直是影响系统效率的瓶颈,尽量减少对磁盘的I/O 访问是提高系统效率行之有效的方法。
  Oracle 提供了两种基本方式来访问磁盘中的记录:(1)全表扫描,就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(databaseblock)的方式优化全表扫描;(2)通过ROWID 访问表,ROWID 包含了表中记录的物理位置信息,Oracle 采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高。
  在表上面建立索引是提高对表查询更新速度有效的方法,但是并不是只要建立索引就可以提高查询速度,更不是索引建立越多系统性能就越高。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。所以索引的建立要根据实际表的大小与多少、实际需要来建立。另外在使用索引时要注意以下几个方面:(1)避免在索引列上使用NOT:通常,我们要避免在索引列上使用NOT, 当Oracle 遇到NOT, 它就会停止使用索引转而执行全表扫描。
  (2) 用>= 替代>: 如果DEPTNO 上有一个索引。
  高效: S E L E C T * F R O MCLASS WHERE DEPTNO>=4;低效: S E L E C T * F R O MCLASS WHERE DEPTNO>3。
  两者的区别在于,前者DBMS 将直接跳到第1 个DEPT 等于4 的记录,而后者将首先定位到DEPTNO=3 的记录并且向前扫描到第1 个DEPT 大于3 的记录。第2 个语句用的时间长。
  (3) 尽量去掉“IN” 或“OR”,严禁使用“NOT IN”:含有“IN”或“OR”的WHERE 子句常会令索引失效;在不产生大量重复值的情况下,可以考虑把子句拆开,拆开的子句中应该包含索引。
  (4) 避免在索引列上使用ISNULL 和IS NOT NULL: 避免在索引中使用任何可以为空的列,Oracle 将无法使用该索引。对于单列索引,如果列包含空值,索引中将不存在此记录。
  对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空,则记录存在于索引中。
  (5)总是使用索引的第1 个列:如果索引是建立在多个列上,只有在它的第1 列(leading column) 被WHERE 子句引用时,优化器才会选择使用该索引。
  (6)ORDER BY 子句只在两种严格的条件下才能够使用索引。
  ① ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。② ORDER BY 中所有的列必须定义为非空。
  (7)如果数据是枚举值,且取值范围固定,则尽量去掉<>,修改为OR,避免全表扫描。
  4.2 减少一切不必要的操作
  在执行每条SQL 语句时,Oracle会在内部执行很多工作,包括SQL 语句解析、估算索引的利用率、绑定变量、读数据块、对结果集进行排序等。因此,如果我们想办法尽量减少每一个环节所用的时间,就可以起到优化的作用。
  (1)共享SQL 语句,减少相同语句的重复解析。
  为了不重复解析相同的SQL 语句,在第一次解析之后,Oracle 将SQL 语句存放在内存中。这块位于系统全局区域SGA(System Global Area) 的共享池(Shared Buffer Pool)中的内存可以被所有的数据库用户共享。因此,当你执行一个SQL 语句时,如果它和之前执行过的语句完全相同,Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle 的这个功能大大地提高了SQL 的执行性能并节省了内存的使用。
  共享的语句必须满足三个条件:①当前被执行的语句与共享池中的语句必须完全相同,包括字符的大小写、空格、换行等;②两个语句所指向的对象必须完全相同;③两个语句中必须使用相同名字的绑定变量。
  (2)避免在SELECT 子句中使用“*”。
  在语句解析过程中,Oracle 会将“*”依次转换成所有的列名,这个工作是查询数据字典完成的,这将耗费更多的时间。
  (3) 当删除全表记录时, 用TRUNCATE 代替DELETE。
  当用DELETE 命令删除表中的记录时,Oracle 会用回滚段(rollbacksegments)存放可以被恢复的信息,如果没有COMMIT 事务,Oracle 会将数据恢复到删除之前的状态。而当运用TRUNCATE 时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。
  (4) 用WHERE 子句替代HAVING 子句。
  HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。如果能通过WHERE 子句限制记录的数目,那就能减少这方面的开销。
  (5)WHERE 子句中的连接顺序。
  Oracle 采用自下而上的顺序解析WHERE 子句,根据这个原理,表之间的连接必须写在其他WHERE 条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾。
  (6)尽量多使用COMMIT。
  在程序中尽量多的使用COMMIT, 这样不仅程序的性能得到提高, 而且可以释放很多资源。
  COMMIT 释放的资源包括: ① 回滚段用于恢复数据的信息;② Redo logbuffer 中的空间;③程序语句获得的锁;④ Oracle 管理上述3 种资源的内部花费。
  (7)使用显示的游标(Cursors)。
  使用隐式的游标将会执行两次操作:第1 次操作检索记录;第2 次操作检查TOO_MANY_ROWS 这个EXCEPTION。而显示的游标不执行第2 次的操作,效率将会有所提高。
  4.3 用空间换时间
  如果我们用尽上述所有方法,都无法满足系统对时间响应的需求,就可以考虑采用以空间换时间的方法,即将SQL 查询语句的“中间结果”或“最终结果”预先抽取到一张中间表中,SQL 语句运行时直接对中间表进行检索。通常中间表包含的记录数要比基础表少好几个数量级,因而可以极大地提高SQL 语句的执行效率。
  (1)建立中间临时表
  对于结果集比较固定的应用可以考虑建立中间临时表,将结果集或是经常要关联查询的中间结果集保存到中间临时表中。根据实际应用的情况,中间临时表可以是会话级、事务级或者是长久的。
  (2) 建立实体化视图
  (MATERIALIZED VIEW)对于结果集不固定、需要经常刷新的应用可考虑建立实体化视图,将查询的结果集保存为实体化视图后,系统会定时根据主表的变化刷新实体化视图中的数据。这对于那些需要在浏览器终端实时显示统计信息的应用来说非常有用。
  虽然上述两种方法增加了很多空间代价和管理开销,不过对于需要经常执行、对执行时间要求严格的一些大语句来说,它可能是最有效的办法。
  5 结束语
  SQL 语句优化对Oracle 数据库性能优化起到了主要的作用,高效的SQL 语句是Oracle 数据库高效运行的关键。SQL 优化的本质就是在结果正确的前提下,尽量减少系统不必要的操作、表扫描的I/O 次数、数据的转换、数据的传输量。当然,SQL 语句的优化是复杂的、无止境的,我们要适可而止,应该在语句优化对系统性能提升的影响以及我们所付出的代价之间进行权衡,在达到预定性能目标后就应该停止优化。
  
  

网站统计|优领域|优领域 ( 粤ICP备12011853号-1 )  

GMT+8, 2019-6-16 17:21 , Processed in 0.045528 second(s), 12 queries .

Copyright © 2008-2014 优领域

回顶部