湖北电信Oracle数据库的优化浅探由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“oracle数据库的优化”。
湖北电信Oracle数据库优化浅探
叶小敏 湖北电信随州分公司 企业信息化部（441300）
摘要：在目前的湖北电信计费系统中，所有的账务稽查都是通过编写SQL语句向数据库提交需求的，面对全省同时提交的数百个数据需求，服务器接收的任务过多，往往会出现任务被吊起，长时间不响应的现象，甚至影响了账务结算工作。经过核查，造成死锁的原因很多时候是因为提交的语句存在各种语法或者逻辑问题，易造成死锁，极大的浪费了系统资源。
本文作者根据多年计费工作经验提出了一种优化Oracle数据库的方法。Oracle中SQL语句的执行过程可分为解析（Parse）、执行（Execute）和提取结果（Fetch）三步，该方法是通过对SQL语句在Oracle数据库中优化执行的三个过程来提高Oracle数据库的性能。减少系统资源被无效占用和损耗，这是提高湖北电信计费账务结算稽核效率、按期完成工作的有力保证。
关键词：数据库； 扫描； 多表联结； 子查询
1．如何优化Parse 1．1 SQL语句的Parse处理步骤：
1）计算语句值
2）共享池中有无与此语句值相同的语句？
3）共享池中有与此语句字符完全匹配的语句？ 4）准备要运行的SQL语句
5）为新语句在共享池中创建空间 6）将语句存放在共享池中
7）修改共享池图，标明语句的值和在共享池中的位置 8）执行准备好的SQL语句
最理想的是，语句只执行1、2、3和8步来进行处理。不经过2、3步来测试被传给Oracle的语句要使用1～8步进行处理。只经过1、2、3、8的SQL语句要比经过1～8步的语句更为有效。
1．2 在共享池中重用SQL语句
当SQL语句被传递给Oracle处理时，其秘诀是重复使用已经在共享池中的语句，而不是让Oracle在接受语句时去准备新的语句。
Oracle提供在数据库中存储代码的能力，当应用系统开始运行时，从数据库中读取代码传递到共享池中去处理。从数据库中取出的代码是编译过的并驻留在共享池中。当SQL语句被传递给Oracle处理是，如果Oracle接受了一个与共享池中语句一致的语句。就重用共享池中的语句。两条SQL语句必须是语法、文本完全相同，才能市委可以共享的SQL，利用Oracle共享区。
湖北电信账务稽核工作由全省各本地网人员共同参与，人员较多且语句编写思想均存在差异，但最终的需求是一致的，依据共享原则，由省公司账务处理部门按照稽核要求和内容，规定稽核点和稽核方法，下发经过整理的具有标准格式、大小写以及相同变量等遵循规定的稽核语句和过程，从而可以最大限度的重用共享池中的语句和过程，从而可以最大限度的重用共享池中的SQL语句。如何优化Execute和Fetch 2．1避免无计划的全表扫描
全表扫描连续从表读取所有数据，而不管数据是否与查询有关。避免不必要的全表扫描有两个充足理由：1全表扫描没有选择性。2通过全表扫描读取的数据很快从SGA的缓冲区移走（如果正在扫描的表不是“高速存储”的表）
在基于规则优化的情况下，如果下列任何条件在SGA语句出现，就要对一个表进行全表扫描。1）该表无索引
2）对返回的行无任何限定条件（如无Where语句）
3）对数据表与任何索引主列相对应的行无限定条件。例如，在City－State－Zip列上创建了三列复合索引，那么仅对State列有限定条件的查询不能使用这个索引，因为State不是索引的主列。
4）对索引主列的行有限定条件，但条件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情况下都不会使用索引。Where city is null Where city is not null Where city!= ‘jilin’
5）对索引主列的行有限定条件，但条件在表达式里使用。例如，如果在City列上索引，那么限定条件
Where City = ‘jilin’
可以使用索引。然而，如果限定条件是 Where UPPER(City)=’liaoning’
那么不会使用City列上的索引，因为City列在UPPER函数里。如果将City列与文本字符串联结在一起，也不会使用索引。例如，如果限定条件是 Where City||’x’ like ‘jilin%’
那么不会使用City列上的索引。
6）对索引主列的行有限定条件，但条件使用Like操作以及值以‘％’开始或者值是一个赋值变量。例如，在所有下列情况下都不会使用索引： Where City like ‘%aonin%’
Where City like :City_Bind_Variable 如果表小、索引列无选择性，基于开销的优化器可能决定使用全表扫描。2．2只使用选择性索引
索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。
如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。
索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。
2．3管理多表联结
Oracle提供了3个联结操作：NESTED LOOPS、HASH JOIN和MERGE JOIN。MERGE JOIN是一组操作，在所有行被处理完之前，它不返任何记录给下一操作。NESTED LOOPS和HASH JOIN是行操作，因此会很快将第一批记录返回给下一个操作。
在每个联结选项里，必须执行一些步骤以获取最好的联结性能。如果没有适当地优化联结操作，那么联结所需的时间也许随着表的增长而呈指数级地增长。
2．4管理包含视图的SQL语句
如果查询包含视图，优化器有两种执行查询的方法：首先解决视图然后执行查询，或者把视图文本集成到查询里去。如果首先执行视图，那么首先完成全部的结果集，然后用其余的查询条件做过滤器。
首先解决视图会导致查询性能下降的问题，这取决于所涉及表的相对大小。如果视图被集成到查询里，那么查询的条件也可以应用于视图里，并且可以使用一个小一些的结果集。然而在一些情况下，也许可以通过视图分离组操作提高查询性能。
如果一个视图包含集合的操作（如Group by、SUM、COUNT或者DISTINCT），那么视图不能被集成到查询里去。不使用组或者没有集合操作的视图的SQL语法可以被集成到大的查询里去。
2．5优化子查询
当使用自查询时，也许会碰到几个独特的问题。涉及子查询的查询潜在问题如下： a）也许在执行完查询的剩余部分前执行子查询（与执行分组功能的视图相似）。b）子查询也许要求特定的提示，但这些提示不直接与调用该子查询的查询有关。c)可以作为单个查询执行的子查询也许被代替写成几个不同的子查询。
d)也许在使用not in子句或者not exists子句时，不能在最有效的方式下进行子查询的存在查询。
1）当执行子查询时
如果一个查询包含子查询，那么优化器有两种完成查询的方法：首先完成子查询，然后完成查询（“视图的方法”），或者将子查询集成到查询里去（“联结”的方法）。如果首先解决子查询，那么整个子查询的结果集将首先被计算，并且用查询条件的剩余部分做过滤器。如果没有使用子查询去进行存在检查，那么“联结”方法将通常要比“视图”方法完成得好 2）如何组合子查询
一个查询可以包含多个子查询，使用的子查询越多，集成或者重写它们到大的联结里就越困难。既然有多个子查询使集成困难，就应该尽可能地组合多个子查询。3）怎样进行存在检查
有时子查询不返回行（记录），但可以进行数据正确性检查。在相关表里的记录或者存在或者不存在的逻辑检查，称为存在检查。可以使用exists和not exists子句提高存在检查的性能。
2．6管理对非常巨大的表的访问
随着表增长到比SGA的数据块高速缓冲区存储器的空间显著大时，需要从另一些角度优化对这个表的查询。
1）当表和它的索引小的时候，在SGA里可以有高度的数据共享。多用户读表或索引范围扫描可以反复使用同一个块。随着表的增长，表的索引也在增长。随着表和它的索引增长到比SGA里提供的空间大时，范围扫描需要的下一行将在SGA里找到的可能性变小，数据库的命中率将减小。最后，每一个逻辑读将要求一个单独的物理读。对使用非常大的表的优化方法着眼于特别的索引技术和有关索引的选择。
2）管理数据接近，在访问非常大的表期间，如果倾向于继续使用索引，那么应该关注数据接近，即逻辑相关记录的物理关系。为了使数据最大限度地接近，应该连续往表里插入记录。记录按通常在表的范围扫描里使用的列排序。
3）避免没有帮助的索引扫描，如果要对大表使用索引扫描，那么不能假定索引扫描将比全表扫描执行得更好。不紧跟表访问的索引唯一扫描或范围扫描执行得比较好，但紧跟通过RowID的表访问的索引范围扫描也许执行得差。随着表增长到比数据块高速缓冲存储器大得多，最终，索引扫描和全表扫描间的平衡点打破。
4）创建充分索引的表，如果表中的数据相当稳定，充分索引一个表是很有用的。创建一个复合索引，它包括所有在查询期间通常选择的列。在查询期间，查询要求的所有数据可以通过索引访问提供，不需要任何表访问。5）并行选项，可以把一个数据库任务，比如Select语句，分为多个单元的工作，由多个Oracle进程同时执行。这种能够允许数据库的单个查询活动由多个协调的进程透明地进行处理的能力，称为并行查询选项（PQO）。并行选项调用多个进程来利用空闲的系统资源，以减少完成任务所需要的时间。并行选项并不减少处理过程所要求的资源数量，而是把处理的任务分散给多个CPU。
2．7使用UNION ALL而不是UNION 在湖北电信账务稽核中，往往需要把符合条件的几部分数据汇总，形成一个新的结果表。
最常用的集操作是UNION操作，UNION操作使多个记录集联结成为单个集。UNION操作的数学定义是返回记录的单个集并且没有重复的行，所以在合并结果集里，Oracle只返回不同的记录。
当UNION操作用作SQL语句的一部分时，唯一性要求强迫Oracle移走重复的记录。Oracle的移走重复记录的功能是SORT UNIQUE操作，它与使用DISTINCT子句时执行的操作类似。
UNION ALL操作允许重复。UNION ALL不要求SORT UNIQUE操作，从而节省了开销。UNION ALL是一个行操作，所以当其变为有效就返回给用户。而UNION包括SORT UNIQUE集操作，在全部记录的集的排序结束前，不返回任何记录给用户。
当UNION ALL操作产生巨大的结果集时，不需要任何排序便返回记录给应用的事实意味着第一行检索的响应时间更快，并且在许多情况下，可以不用临时段完成操作。
在有些情况下，UNION ALL和UNION不返回同样的结果。如果在应用环境中，结果集并不包含任何重复的记录，则可以把UNION转换成UNION ALL。
经过笔者实际账务工作验证，采用UNION ALL操作往往能获得更快的响应速度。
2．8避免在SQL里使用PL/SQL功能调用
对于增加PL/SQL的使用，许多用户试图利PL/SQL功能的优势产生可重复使用的代码。其中一个强迫重复使用PL/SQL功能的方法是在SQL语句里使用。例如，可以创建一个将国际货币转换为US$的函数。这个函数称为US$。示例如下：
select transaction_type, US$(amount, currency)from international_transaction where US$(amount, currency)> 1000;执行前面的SQL语句没有所期望的那样好。在测试时，它的性能大约比下面得出相同结果的SQL语句慢大约几十倍。
select transaction_type,amount*exchange_rate US$ from exchange_rate er, international_transaction it where er.currency = it.currency and amount*exchange_rate > 1000;响应时间不同的原因是混合PL/SQL和SQL时，Oracle使用的机制不同。在SQL查询里嵌入PL/SQL功能时，在执行时，Oracle将调用分成两部分：用带有赋值变量的SQL语句代替功能调用以及对每一个函数调用的无名PL/SQL块。
select transaction_type, :a1 from international_transaction where :a1 > 1000 和 BEGIN :a1 := US$(:amount, :currency);END 对在international_transaction表里的每一行，将执行在前面示例里显示的无名块两次。无名块调用导致查询响应时间的剧增。应该避免在SQL语句里使用PL/SQL功能调用。
3.结语
本文依据作者在湖北电信计费系统多年工作经验，结合ORACLE数据库，简单论述了一些Oracle数据库优化的一些操作方法，为湖北电信计费工作同仁提供了一些Oracle数据库优化查询和运行的解决方案，希望籍此对湖北电信账务和计费处理工作带来具体的帮助。
参考文献
[1] 瓮正科，王新英著.Oracle8.X For Windows NT实用教程［M］.北京：清华大学出版社，1999.[2] 萨师萱，王珊著.数据库系统概论［M］.北京：高等教育出版社，2000.2.[3] Eyal Aronoff，Kevin Loney，Noorali Sonawalla著, 李逸波、王华驹、马赛红、曲宁等译.Oracle8性能优化和管理手册［M］.北京：电子工业出版社，2000.1.[4] Peter Koletzke,Dr.Paul Dorsey著, 刘晓霞、孙登峰、曲京、何粼等译.Oracle Designer信息系统开发［M］.北京：机械工业出版社，1999.8
Oracle DBA优化数据库性能心得体会很多的时侯，做Oracle DBA的我们，当应用管理员向我们通告现在应用很慢、数据库很慢的时侯，我们到数据库时做几个示例的Select也发现同样的问题......
ORACLE数据库结课论文 一个好的程序，必然联系着一个庞大的数据库网路...今年我们学习了 oracle数据库这门课程，起初的我，对这个字眼是要多陌生有多陌生，后来上课的时候听一会老......
学习Oracle数据库的心得体会对于学习Oracle数据库，应该先要了解Oracle的框架。它有物理结构（由控制文件、数据文件、重做日志文件、参数文件、归档文件、密码文件组成），逻辑结构......
2014-2015学年度第一学期ORACLE教学工作总结班级：13高软班数据库技术是计算机科学技术中发展最快的领域之一，也是应用最为广泛的技术之一，它已经成为计算机信息系统与应用的核......
创建表及命名规则？表名和列名:必须以字母开头 必须在1–30个字符之间必须只能包含A–Z, a–z, 0–9, _, $, 和# 必须不能和用户定义的其他对象重名 必须不能是Oracle 的保留......
