Oracle数据库的优化

2010年第 35期SCTENCE & TECENOLOGY INFORMATIONOIT论坛O科技信息Oracle数据库的优化陈丽红'林常君2(1.延边广播电视大学吉林延边133000;2.交通银行延边分行 吉林延边133000)[摘要]本文提出了一种优化Oncle数据库的方法。Oracle中SQL语句的执行过程可分为解析(Parse). 执行(Execute) 和提取蛄果(ech)三步,此方法就是通过对SQL语句在Oncle敏据库中优化执行的三个过程来提高Oncle数据库的性能。[关键词]敏锯库;扫描;多表联结;于查询1如何优化 Parse6)对索引主列的行有限定条件.但条件使用1like操作以及值以‘%'开始或者值是一个赋值变量。例如,在所有下列情况下都不会使.1 SQLI.语句的Parse处理步骤:用索引:1)计算语句值Where City like '%aonin%'2)共享池中有无与此语句值相同的语旬?Where City like :Ciny _Bind. Variable3)共享池中有与此语句字符完全匹配的语句?如果找小、索引列无选择性.基于开销的优化器可能决定使用全4)准备婴运行的SQL语句表扫5)为新语句在共享池中创建空间2.2只使用选择性索引6)将语句存放在共享池中索引的选择性是指索引列里不同债的数a与表中记录数的比。7)修改共享池图,标明语句的值和在共享池中的位置如果表有1000个记录.表索引列有950个不同值，那么这个索引的选8)执行准备好的SQL语句最理想的是,语句只执行1.2.3和8步来进行处理。不经过2.3挥性就是950/1000或者0.95。最好的叮能性选择是1.0。依据非空值步来测试被传给Oracle 的语句要使用1-8步进行处理。只经过1.2.列的唯一索引,通常其选择性为1.0。如果使用善于开销的城优化,优化器不应该使用选择性不好的素3.8的SQL.语何要比经过1-8步的语句更为有效。1.2在共享池中重用 SQL语句索引的选择性是指索引列里不同值的數月与表中记录数的比。如当SOL.语句被传遵给Onele处理时，其秘诀是重复使用已经在果表有 1000个记录，表索引列有950↑个不同值,那么这个索引的选择共享池中的语句.而不是让Oracle在接受语句时去准备新的语句。前性就是950/1000或者0.95。最好的可能性选择是1.0依据非空值列面表明，如果Oracle接受了一-个与共享池中的语句相一致的语句,就的唯一-索引,通常其选择性为1.0。重用共享池中的语句。Oracle 提供在数据库中存储代码的能力。当应用系统开始运行2.3管理多表联结Oracle 提供了3个联结操作:NESTED LOOPS、HASH JOIN和时,从数据库中读取代码(可用PI/SQL语句编制)并像其它语句那样MERGE JOIN。MERGE JOIN是-组操作.在所有行被处理完之前,它传递到共享池中去处理。从数据库中取出的代码是编译过的并驻留在不返任何记求给下-操作。NESTED L00PS和HASH JOIN是行操作，共享池中。因此会很快将第-批记录返回给下一个操作。2如何优化Execute和Fetch在每个联结选项里。必须执行- 些步骤以获取最好的联结性能。如果没有适当地优化联结操作.耶么联结所而的时间也许随莉表的增2.1避免无计划的 全表打描全表打描连续从表读取所有数据，而不管数据是否与查询有关。长而呈指数级地增长。避免不必要的全表扫描有两个充足理由:1会表扫描设有选择性。2通2.4管理包含 视图的sQI.语句如果查询包含视图，优化器有两种执行套询的方法:首先解决视过全表打描读取的数据很快从SCA的缓冲区移走(如果正在扫描的图然后执行查面.或者把视图文本集 成到查询里去。如果首先执行视表不是“高速存储"的表)在基于规则优化的情况下.如果下列任何条件在SGA语句出现，图,那么首先完成全部的结果集.然后用其余的查询条件做过滤器。首先解决梲图公导致在淘性能卜降的问题.这取决f所步及表的就要对一个表进行全教扫描。相对大小。如果视围敏集成到查询电.耶么查削的条件也可以应用于1)该表无索引视图里,并H可以使用-个小一些的结果集。然而在-些情况下,也许2)对返回的行无任何限定条件(如无Where语句)可以通过视图分离维操作提呙查润性能。3)对敷据表与任何索引主列相对应的行无限定条件。例如,在.如果一个视图包含集合的操作( 如Croup by .SUM .COUNT或者City. Statee zZip 列上创建Y三列复合索引,那么仅对Sule列有限定条DISTINCT). 那么视图不能被集成到代询里去。不使用组或者没有集件的查询不能使用这个索引,因为Stuale不是索引的主列。合操作的视图的SQL语法可以被集成倒大的查询里去。4)对索引主列的行有限定条件，但条件或者是NULL或者是不25 优化子查询用等。例如,City列上存在索引. 在所有下列情况下都不会使用索引。当使用自查浏时.也许会碰到几个独特的问题。涉及子查淘的查Where city is null询潜在问题如下:Where eity is not mull1)也许在执行完查询的剩余部分前执行子查淘(与执行分组功能5)对索引主列的行有限定条件.但条件在表达式里使用。例如，Where city! = jilin'的视图相似)。2)f奁询也许要求特定的提示,但这些提示不直接与调用该子查如果在City列上索引,那么限定条件询的查询有关。Where Gity = *jlin'3)可以作为单个查询执行的子查询也许被代替写成几个不同的可以使用索引。然而.如果殿定条件是子查询。Where CPPER(City)= lianing'中国煤化工子句时,不能在最有效的那么不会使用City列上的索引，因为Cirty 列在UPPER函数里。如果将City列与文本字符串联结在一起.也不会使用索引。例如,如果限定条件是YHC N M H G有两种完成责询的方法:Where CityI'x' like ijlin%'首先完成子查询.然后完成查询(“视图的方法").或者将子查浏巢成那么不会使用City列上的紫引。到在询里去(“联结”的方法)。如果首先解决子查询,廓么整个f查询.104科技信息OIT论坛OSCTENCE & TECHNOLOGY INFORMATION2010年第35期的结果集将阶先被计算,并几用查询条件的剩余部分做过速器。如UNION ALI. 操作允许承复。UNION ALI.不要求SORT UNIQUE没有使用子查询去进行存在检盘.那么“联结" )i法将通常婴比“视图”操作,从而节省r开销。UNION ALI.是-.个行操作,所以当其变为有力法完成得好。效就返回给用户。面UNION包括SORT UNIQUE集操怍.在全都记录(2)如何组合子充询一个代削叮以包含多个子壳询.使用的子在询越多.集成或者電当UNION AIL操作产生巨大的结果集时，不需要任何排序便返写它们到大的联结电就越闲难。既然有多个子查询使巢成困难.就应回记域 给应用的事实意昧着第-行检索的响应时间史快,并且在许多该尽叮能地组介多个千食询。情况下,可以不用临时段完成操作。(3)怎样进行存在椅在在有此悄况下,UNION ALL和UNION不返回同样的结果。如果有时子在询不返同行(记录),但可以进行数据正确性检查。在相在应用环境中 ,结果集并不包含任何重复的记录.则叮以把UNION转关技里的i记水或者存在或者不存在的逻辑检代称为存在检查。可以换成UNIO使用exits和notexists子句提商仔在检查的性能。2.8避免在SQL.里使用PU/SQL功能调用2.6 管理对非常|i大的表的访问对于增加PI/SQL的使用,许多用户试用利PI/SQI.功能的优势产随符长增长到比SCA的数据块高速缓冲区存储器的空间显著大生可敢复使用的代码。其中一个强迫歌复使用PI/SQI.功能的方法是时,需要从另-些角度优化对这个表的在询。在SQL语句里使用。例如,可以创建一个将国际货币转换为US$的咱1)4表和它的索引小的时候.在SGA电可以有高度的数据共享。数。这个丽数称为US$。示例如下:多用户读长或索引范阂fI描叮以反复使用同一个块。随柞长的增长，seleet transaction. type, US$ (amount, curreney)表的求引也在增长。随料长和它的索引增长到比SGA电提供的空间from intermational _transaction where US $ (amount, currency) >大时.范阂打描需要的下一行将在SGA里找到的时能性变小，数据库1000;的命中率将减小。最后.每一个逻辑诙将婴求-.个单独的物理读。对使执行前面的sQI,诵句没有所期望的那样好。在测试时,它的性能用非常大的长的优化方法荷眼于特别的索引技术和有关索引的选择。大约比下面得出相间结果的SQL.语句慢大约几+倍。2)管理敷据接近.在访问非常大的衣期间.如果倾向于继续使用seleet tranaction, Jype,amount*exchange. rate US$索引.那么应该关注数据接近.即逻辑相关记录的物理关系。为了使数from exchange. rate er, intermational. transaction it据最大限度地接近.应该连续往表里插人记录。记承按通常在表的范围打描甲使用的列排序。响应时间不同的原因是混合PUJSQI.和SQL时,Oracle使用的机3)避免没A们帮助的索引扫描.如果要对大表使用索引扫描,那么制不同。在SQI.在询里嵌入PLSQL功能时.在执行时.Oracle将调用不能假定家打|描物比仑&扫描执行得更好。不紧跟表访阿的索引唯分成两部分:用带有赋值变鼂的SQL语旬代替功能调用以及对每一一打描或范例打I描执行得比较好，俱紧跟逋过RowID)的长访问的索个 晌数调用的无名PLISQL块。引范阂打|描也许执行得老。随着长增长到比数据块高速缓冲存储器大select transaction _ype, :al from intemational. _ransaction where :al得多,最终,索引打描和会衣I描间的平衡点打破。> 1000表是很有用的。创建-个复介索引,它包括所有在在湖期间通常选择BECIN的列。在代浏期间.查询要求的所有数据叮以通过索引访问提供,不需al = US$ (amount, :currency);要任何表访问。END5)并行选项.可以把一个数据库任务.比如Selet语句.分为多个对在intemational. _transaction 表中的每一行，将执行在前面示例单元的T.作.由多个Orarle进帮同时执行。这种能够允许数据库的单里显示的无名块两次。无名块调用导敛代削响应时间的剧增。应该避个t询话动由多个协调的进段透明地进行处理的能力.称为并行查询免在SQL语句电使用PI/SQL功能调用。(选项(PQ0)。并行选项调用多个进程来利用空闲的系统资源.以减少完成任务所霜要的时间。并行选项并不碱少处理过程所要求的资源敷[参考文献]量,而是把处理的任务分散给多个CPU。[1]竞正科,王新英Orcle8.X For Windows NT实用教程[M]北京:清华大学出版2.7使 用UNION AlJ.而不是UNIONt,1999最常用的集操作处UNION操作.UNION操作使多个记录集联结(2}萨师置，王建 数据库系烧氟论M.北京:高等教育出版社2000成为单个集。UNION 操作的数学定义是返网记项的单个集并且没有[3 lEyal Aoff.Kervin Lloney Noorali Snawl.Oracle8性能优化和管理手册[M],乍逸彼, E华驹.马赛红,曲宁,等,译北京:电子工业出版社2000.1.重复的行.所以在合并结梨集甲.Oracle只返回不同的记束。[4 ]Peter Koleike,Dr.Poul Dorsey Orecle Deigner 俯息系统开发[M].刘晓霞,孙登当UNION操作用作SOL. 语句的一部分时，唯-性要求强迫峰,自京，间藏等洋北京，机械上业出)19.8Oracle 移走敢复的记录。Oracle 的移走承复记项的功能是SORTUNIQUE操作,它与使用DISTINCT子句时执行的操作类似。[责任缡:王静](上接第77页)户信息表记录进行增加.以及进行客户信息的修改和后 利用sQL.语句来套询借的状态.生成临时表.把临时表做为数据原删除，食浏操作。进行显示出来。4)会员诈册 与交费用添加.首先标志婚加客户信息状态的会员，4总结对其人员进行借的跟踪，对其交费用和普通人员交费用进行区别对待。作为计算机应用的-部分，使用计算机对卉像租赁信息进行管首先是公员汴册问题.有管理员来注册该用户信息，设计代码,代理. 具有F I.管理所尤法比拟的优点。例如:检家迅連、查找方便、可靠码说明,定义敷组变比t仔放了段内容,利用SQL插入语创插人客户信性高 任储量大、都命长.等优点。这此优点能够级大地提高效事，也是总长,给出提小,清空原有所输入们息。科学化、正规化管理的歌要条件。5)齐像的在找j租借功能.该功能主要是新增加租借信息以及对信息代拽。6)音像 的归还与费用计算功能.该功能反映借阅的信息,以及借([1)谭桥强中国煤化工清华大学出版社。的状态[2]张彬集:MHCNMHG电出版社.7)对音箱借的信息进行在询打印以及定期打印音像的信息,了解[3]刘淳.Vi借的状态。粮据客户编号,可以在海出所有借的信息,接收客户编号参敷,然[贵任嫡辑:昌一然]105