oracle数据库优化

  这是以前在程序员上发表的一篇文章

1         概述

       数据库是信息系统中核心的部分,数据库的高效性、安全性、稳定性、延展性,是项目成功的关键之一。一个好的数据库系统,设计当然是关键,但是就像显微镜的粗调和微调,当软件开发完成后,通过不断的对系统的跟踪监控,对数据库系统进行优化也是必不可少的。可以认为优化是数据库设计的一种顺延,和设计是相辅相成的。数据库的优化实际上从设计、数据库的安装就已经开始了,我们不应该就sql语句的效率孤立的谈论数据库的优化问题。实际上很多数据库优化的经验,在设计的时候就必须要考虑。

1.1      文章的组织结构

本文首先谈了一下和性能优化有关的基本概念,之后分六个方面详细讨论了数据库优化的方法、手段。在数据库的监控部分讲述了为什么?什么时候?如何监控数据库,并给出了一些可以直接使用的简单sql,用来直接分析某些方面的问题。从始至终,贯穿了一个实际的例子帮助读者能更好的理解有关理论。由于使用了多个工具参与优化工作,文中对每种工具的使用技巧做了简单提示。本文用到的例子是作者前不久遇到的一个项目,操作系统是DIGITAL ES20(True64UNIX),硬件配置为 520M内存,单cpu,单SCSI硬盘。这里只提一下大概的性能和功能要求:系统要求能在短时间内,导入56百万条的记录、部分数据需要手工录入。录入的记录进行处理后,被处理后产生各种报表,并且报表很可能发生变化。本系统在使用了一段时间后,发现性能严重下降,生成报表和数据导入都需要很长时间,通过优化将响应控制在了可以忍受的范围。

2         性能优化有关的基本概念

2.1      响应时间

数据库是否高效的一个重要指标就是响应时间,响应时间短,查询就可以使用更少的时间,所以响应时间短,反应速度快是高效率的一个表现,这里有一个统计的数据。

Ø         1/10秒是用户认为系统能够立即反应的极限

Ø         1 秒是用户觉得没有被中断的极限

Ø         10秒是用户能将注意力继续集中在与计算机的对话上的时间极限

引用自Jakob Nielsen  Usability Engineering 第五章(Morgan Kaufmann, San Francisco出版)

简单的说,数据库优化就是要缩短系统的响应时间。

2.2      数据库的增长

数据库不是静止不动的,应用程序的螺旋式展开,数据库的规模的不断增大,用户数目的不断增多,最终将会导致响应时间的增加。虽然期间经过硬件的升级和数据库的优化,可以降低响应时间,但是在整个产品周期内,响应时间是会周期性展开的,有一个总体的发展趋势,如图1所示:所以数据库的优化在整个产品的生命周期都是有意义的。

响应时间

数据库规模

 

 

 

 

 

 

 


                             优化       优化

1

       在数据库的设计阶段,就应该估算数据库的增长率,并作为系统硬件参数、内存参数、数据库空间配置参数、环境参数设置的依据。在数据库的优化阶段,可以通过对数据库的监控来验证和调整我们的估计,并适当改变参数和调整sql,来控制系统的响应时间。

       不同的业务特点增长率是不同的,根据在应用中的作用,数据库表基本上可以分为

²        静态参数表(应用相关表):相对稳定,并且比较小

²        动态参数表(事务相关表):于事务大小有关,缓慢增长

²        数据表(事务表):增长的主要因素,并且不稳定

²        临时/接口表:临时使用,之后被删除

3         数据库的具体优化方法

在数据库优化的时候,可以从5个方面调整,我们可以做为数据库优化的思路

Ø         网络

Ø         硬件

ü         I/O子系统

ü         Cpu子系统

ü         存储器

Ø         操作系统

ü         UNIX系统

ü         NT系统

ü         VMS

Ø         数据库

ü         存储参数调整

ü         环境参数调整

Ø         应用程序

批处理和OLTP应用程序具有不同特点,批处理要考虑整体时间,而OLTP更注重单个sql的性能,在考虑单条sql是重点是:

ü         棘手sql调整

ü         索引调整

3.1          网络

巧妇难为无米只炊,如果分布式应用的网络环境不稳定,那实在是没有办法了。搞好客户关系,花钱买设备吧。

3.2          硬件优化

硬件的调整常在应用程序展开的初期见到,并且能解决很多性能问题,随着应用程序的展开,数据库规模的增长,后期主要的调整手段要放到数据库和应用程序上去。它们往往是影响性能的主要因素,同时也比硬件调整要更加经济。当然,如果客户不计成本向里扔钱,不反对硬件升级。

3.3      操作系统的优化

对操作系统的优化要看具体的操作系统而言,下面有一些常用的经验可供参考。

1.         对于unix操作系统,我们经常需要调整如下参数:

Ø         共享内存(Shared Memory

Ø         信号灯(Semaphores)

对于共享内存主要关注下面四个核心参数:SHMMAX(单个共享内存段最大值)、SHMSEG(能够连到单个进程上共享内存段的最大值)、SHMALL(共享内存段总数目)。oracle建议将单个实例的SGA分配在一个共享内存段里。所以SHMMAX一般为物理内存的一半。具体的调整请看oracle的安装手册。最后,可以使用一些系统级工具进行监测:ipcs –b 可以获得当前共享内存段列表;tstshm 可以评估共享内存段设置。

对于信号灯:主要关注SSEMMNI(系统里信号灯集合的最大数目)、SEMMSL(每个集合里信号灯的最大数目)、SEMMNS(系统范围内可用的信号灯的最大数目)。每个oracle进程使用一个信号灯,信号灯数要比process多,否则数据不能启动,每个核心参数的最佳取值同上,在oracle的安装文档里有详细说明,此不赘述。

最后还要注意,要给操作系统留下足够的内存,不要因为SGA太大而造成过多的页交换,太多的page fault会消耗系统资源。

实战:可以利用操作系统的监视工具来分析系统的瓶颈,下面的示例都是本文实例使用sql*loader导入数据文件时的情况,这是系统资源使用的一个高峰。

ü         使用iostat如图2所示,bps表示每秒传输的kb数、tps表示每秒访问磁盘驱动的次数,关于每个参数的含义大家可以到google搜一下,就能找到。

 

2

可以看到硬盘平均每秒访问磁盘100次左右,平均每秒传输5M左右数据,这样每次访问磁盘可以访问到52K数据。

ü         使用vmstat如图3所示,大家要主要关心page faultpage pinpage pout,这些数据表明了系统的页交换情况,cpu id显示了cpu资源利用率,procs 表明队列的多少,具体参数的详细含义同样可以用google详细搜到。

 

3

3表明:有大量的页交换产生,空闲内存太少,存在着内存瓶颈。但是cpu有大量的空闲等待时间。这表明SGA太大,导致系统本身的缓存太小。

通过这两个工具的监测,就可以明白原来系统的瓶颈就在内存和硬盘io这两个子系统上,更深层次的原因还有待对oracle的监测来发现。

2.         对于WINDOWNT系列操作系统,可以使用如下手段优化操作系统:

Ø             使不必要的服务失效

Ø             取消屏幕保护

Ø             只将机器用做数据库服务器

Ø             如果是WINDOWSNT系统

²        控制面扳中Network tool –Properties下面四个选项中选择Maximize Throughput for Network Application

²        控制面扳中System tool—Performanceboost选择None,减少文件系统缓冲

Ø             利用性能监视器监视数据库服务器,分析性能瓶颈

Ø             利用微软资源工具箱(resources pake)中的工具(Process ViewerProcess ExplodeQuick SliceProcess Stat)监视数据库服务器,决定操作系统是否优化

3.4      数据库系统优化

       oracle基本的体系结构如图2所示,每个后台进程将占用5M大小的内存。通过合理的分配内存大小,合理的设置表空间体系和内部空间参数。可以提高磁盘空间的利用率、减少数据段碎片、并且在查询和向数据文件写入数据的时候使用较少的IO,较少的IO也会降低cpu的资源消耗。同时对环境参数的合理配置,可以使数据库中数据顺畅的流动,减少锁存器冲突和各种等待,充分的利用系统资源。

 

4

内存结构的优化主要是通过init.ora文件的环境参数来配置,要主要注意下面几个:

db_block_size:数据库中每个数据块的大小,默认是2048字节(2k),一般应该增大到4K8K,大型数据库也常使用16K32K,通常SGA也应该增加。

       Shared_pool_size:至于shared_pool_size大小是否合适,可以通过对数据库的监控得到,也可以通过一些sql语句实现。我们可以查询v$SGASTAT,查看SGA结构,其中free memory做为一个估计性的指标,如果大于20%可以将shared_pool_size给小些,给其他部分多分配些,如果小于8%,就可以开大些share_pool_size。有一些更精确的sql脚本可以查看其中library_cachecursor_cachepl/sql cache大小是否合适,如果数据库规模增长,还需要多少内存,所有这些都可以在附件中找到。

       Log_buffer:为了减少LGWRDBWR冲突,大型数据库的log_buffer一般都是要手工调大些,一般为2M3M。可以查询VSYSSTAT视图中redo log space requests,如果大于0就应该增加log_buffer的值。其他对oracle影响很大的参数有:db_writesdb_file_multiblock_readsort_direct_writessort_area_size讲起来就多了,由于和例子关系不大所以不再叙述。需要注意的是:对oracle9i来说sort_area_sizesort_area_retained_sizehash_area_sizebitmap_merge_area_size等参数都被放弃,由pga_aggregate_target统一动态分配内存区域大小,对于undo(回滚)操作也有些变化:undo_retention表示已提交数据在回滚表空间中保留时间,以秒为单位,缺省900。此选项可使新事务尽可能使用空闲的回滚表空间,这样就减少了查询过程因snapshot too old而失败的几率。undo_tablespace表示系统的回滚表空间。

       所有的环境参数,都可以通过系统的监控工具来分析是否适宜。

实战:对数据库系统可以使用toadoracle提供的脚本utlbstatutlestat监控:

ü         toad的监控结果如图5、图6,分别使用了dba 下的database monitorlog switch frequence maptoad还有很多工具大家都可以试试,还是很有特色的。

 

5

对于logical ioconsistent reads表示了逻辑获取,对全表扫描执行一次consistent reads增加1;对通过索引访问表consistent reads的值为索引高度+2*关键字。db_block_change是更新的数据块个数和数据更新的行数之和,表示被修改了的与无效列表连锁的数据块的数目。physical io 是物理读的统计。

Call ratemiss ratewaits也是应该关心的地方,可以看到通常在执行什么工作时系统出现等待。

       我们现在一起分析上面的图:在早上8:058:25分的监测结果表明Consistent_Get(逻辑读) 的峰值为每秒5000个数据块;phsical read(物理读)的峰值:600个数据块/每秒;主要发生在single block read上,那么应该是通过索引的方式访问表。这里single block read出现大量等待,可能存在没有充分索引的大表、或者使用了选择性低的索引。提醒我们在优化sql语句时要留意。注意,这里的块是oracle数据块,现在数据库块大小是2k,所以每秒传递的数据量为2k/数据块*600数据块/=1200k/秒。但是刚才我们在iostat里看到,不是每秒从磁盘传输5M左右的数据吗,为什么每秒向读如SGA的数据是1M呢?这是因为本操作系统的的块大小为8k,对来自oracle的每个读块要求,操作系统取一个8k的块放在系统缓冲区里,然后oracle只从中取2k的块存到SGA里,只使用了操作系统1/4io读取能力,所以监控结果会出现数据不一致。这正是造成硬盘io瓶颈的原因之一啊,看来必须要调整数据库块的大小,下面有一个章节介绍了如何修改数据库块大小。

再来看另外的部分:最多的call rate execute,最多的miss ratesql areamiss ratesql area存在峰值说明,部分时间段内sql的命中率较低,需要进一步查看是否是因为不良sql引起的,还是因为sql缓冲区太小。使用toadhealth check 可知db_block_buffers的命中率为97%,通过v$sgastat视图可查知shared_pool_sizefree mem有只4%左右,看来shared_pool_size太小了,结合上面存在大量页面交换的事实,得出结论:系统需要增加内存或者减少整体SGA,为系统留下更多的缓冲内存,同时还需要增加shared_pool_size的值。由于后面用utlestat脚本监控到sort disk始终为0,为排序分配的内存可以少留些,所以将sort area size75M降低到了32M,这部分内存就划分给了shared_pool_size。接下来看图6,图6监测的是(redo log file)重做日志文件的使用情况。

6

可以看到未优化前redo log 频繁发生,说明需要增加重写日志缓冲区和重写日志,看来LGWRDBWR的竞争也是硬盘io瓶颈的原因之一。优化的措施是Log buffer增加到2Mredo log file修改为100M,在以后的监测中,即使是事务高峰期也能控制日志文件的交换每小时最多12次。一般log buffer开到2M3M就可以了,如果太大会有负面影响,有可能在redo操作时会丢失数据。

当然也可以使用sql语句分析oracle,许多人也喜欢这样做。感兴趣的朋友可以在附录的文件中找到对自己有用的sql直接使用。还有许多人是自己开发软件来分析数据库的,据说这是最有效的方法。毕竟oracle已经提供了使用方便的许多视图,toad也是如此实现的。

ü         Linux下面也有个类似toad的工具tora,如果谁的工作环境是linux就可以用用看。

 

ü         Utlbstatutlestat是非常好的监控脚本,toad能发现的问题使用它同样也能发现,可以指定监控的时间段,是发现问题的一种更量化的方法,推荐使用。

这两个文件在oracle_home/rdbms/admin下,在运行脚本前注意更改tiemed_statistictrue,否则cpu used by this session将始终为0。运行完毕后在此目录下会产生report文件,就是监测结果。在文件中每段前都有简短的说明,可以帮助我们阅读。

ü         Statspack

oracle8.1.6开始提供,oracle提供了Statspack包,实际上它是对Utlbstat/Utlestat脚本的代替,相信许多人都使用过。

STATSPACK包的安装非常方便,以system/manger用户作为sysdba权限的用户登陆,运行$ORACLE_HOME/RDBMS/spcreate.sql语句将创建perfstat用户,同时在perfstatschema(模式)下将创建一些对象,大约有36个表、37个索引、1个序列和1个包。安装的关键是要注意在安装过程中要求指定perfstat用户的默认表空间和临时表空间,其默认表空间至少要大于100M

统计数据采集表以stats$开头,字段从我们常说的v$视图中提取,其实就是通过V$视图获得的基于时间点的统计数据集合。可以利用脚本创建的包STATSPACK中的方法SNAP填充数据采集表,具体的方法是首先调整数据库的timed_statistics参数为True,其次以PERFSTAT用户登陆,在pl/sql中执行execute statspack.snap生成快照。如果有2个以上的快照,运行$ORACLE_HOME/RDBMS/ADMIN/spreport.sql脚本,按照提示输入起始和结束SNAP(快照)ID,将产生sp_beginning_ending.lst输出文件。文件中将会显示I/O统计数据,SGA数据和初始化数据相关的诸多数据,意思和Utlbstat/Utlestat差不多,这里就不一一解释了。

使用时有些要注意的地方:第一要在运行spreport.sql前分析PERFSTAT这个schema

execute dbms_utility.analyze_schema(‘perfstat’,’compute’);第二要及时清除陈旧数据,清除陈旧数据可以使用rdbms/admin/spurge.sql;第三删除STATSPACK包要使用同目录下的spdrop.sql

ü         OEM(oracle enterprise manager)是大型数据库环境最理想的监控工具,可以在控制台上轻松管理所有安装了inteligent agent的数据库,如果没有安装就只能使用dba studio的有限功能了。Oem中有些工具还是非常好用的:分别是性能管理器、锁管理器、会话监视器、空间管理器、oracle Expertoracle trace。我个人觉的OEM是监控工具中最有效的一种工具。图7oem的界面;

 

7

使用OEM,基本的使用方法是:

1.         emca或者Enterprise Manager Configuration Assistant配置档案资料库。

2.         使用oemapp console登录,也可以使用图形界面登录,注意mangerment server中是能解析的主机名,用户也不是数据库用户,而是具有网络管理权限的用户。默认是sysman/oem_temp

3.         搜索可以管理的结点,可以使用ip地址或者可以解析的主机名,如果没有安装intelligent agent需要手工配置(注意SID)

4.         WINNT下只有http service agent service 服务都启动,才可以自动搜索到

5.         添加作业和事件进行管理

3.4.1          修改数据库块大小

根据上面分析我们需要改变数据库块大小,在安装完数据库后改变数据库块,只能导出所有数据,并重新重建整个数据库。如果您使用的是oracle8i9i那么恭喜您,使用dbassist(oracle database assistent)命令太方便了,在创建时选择custom选项,就可以调整db_block_size大小。如果是oracle7.3.4的用户,手工重建数库需要使用create database命令,重建前要修改init.ora,指定db_block_size8K。修改数据库块尺寸后,在每个数据块中将会有更多的行,所以发生块级竞争的可能性增大,需要创建表和索引时将freelist的值设为非默认值1,一般大于8k的数据库块,freelist大于4会得到更多的好处。是否存在freelist冲突,可以使用附录中的sql判断一下。

3.4.2          一些常用经验

oracle的内部空间管理和表空间需要遵守一些经验,这些都是设计数据库时要考虑的问题。比如表空间设计的时候应遵循oracle的最优化体系结构建议;db_block_bufferslibrary_cache都要保证合理的命中率;为了使全表扫描的效率最高,每个extent(数据区)都应该db_block_size*db_fiel_multiblock_read_count的倍数;如果是非常大的表,做sql*loader或者export操作时要使用direct选项,创建非常大的表和索引时要使用nologing选项。(oracle7上面是unrecoverable选项)

实战:本例中创建一个大索引开始没有使用nologing选项,使用后创建速度提高了3倍。

如果数据缓冲区太小,命中率较低,那么就需要经常从磁盘上访问数据。经常的io操作会严重影响效率,比从SGA中直接读取数据多8倍的时间并消耗更多的cpu资源。

在对表进行全表扫描时,数据库将采用多块读机制,每次读的块数由multi_block_read决定,如果块大小2k,每次读32个块,那么一次读出的量为64k。对于一个10M大小的表,假设storage中参数next extent256kpctincreasew0,那么每个extent(区间)256k segment(数据段)上将有会40extent,访问每个extent需要访问2次读64个数据块,全表扫描需要80次的数据块获取。如果设置next extent设为130K,每个extent130ksegment上将会存在79extent,这种情况下访问每个extent需要访问3次,前两次访问128k,最后一次2k,全表扫描需要237次数据块获取。所以在本例中,使每个extent的大小是64k的倍数,对提高全表扫描的速度意义重大。

技巧:SELECT Segment_Name,BYTES/1024/1024,BLOCKS,EXTENTS FROM USER_SEGMENTS where Segment_Name = '&table_name';可以查看表的大小,占用多少个数据块,在segment上的extent数目;select Segment_Name,extent_id,bytes/64,blocks from user_extents where Segment_Name = '&table_name'可以查看每个extents上有多少数据块,每个extent需要做多少次多块读取;

优化了数据库环境后,我们就可以考虑sql语句的优化了。

3.5      应用程序优化

在实际应用中批处理和OLTP应用程序具有不同特点,批处理要考虑整体时间,而OLTP更注重单个sql的性能,在报表系统和数据仓库中批处理的应用比较常见,往往需要一批sql语句完成数据的汇总、分类整理等工作,数据经过清洗和整理后,可以作为报表的基础数据。这些工作中的sql都是批量完成的。

3.5.1          批处理应用的优化

对批处理的应用的优化主要是在一些设计技巧上下工夫,有一些经验列举如下:使用存储过程比在开发工具中使用嵌入式sql更有优势;如果这批sql中经常检索一些小表,那么就可以把这些小表缓存起来,实现手段还是比较多的,可以在创建表的时候指明是可以缓存的,也可以在select中使用cache hint(提示)实现;如果许多sql都涉及对某个大表的查询,那么可以先对这个大对象进行整理,比如:如果where语句中日期条件最有优势,可以得到最少的记录,或者说日期字段最具有选择性,我们就可以先对表进行排序,使记录非常接近,非常接近的记录将使oracle使用较少的io,提高整体性能。

       OLTP等联机处理系统,单条sql语句是优化的重点,往往少量的几条sql语句会占用大部分资源。首先当然是找到这些sql,可能您能很清楚的知道是那些sql ,因为它们都很有特点,常常访问一些大表,也可能不很明确,这时可以用下面的sql 查一下:

select Executions,
       Disk_Reads,
       Buffer_Gets,
       Round((Buffer_Gets - Disk_Reads) / Buffer_Gets, 2) Hit_Radio,
       Round(Disk_Reads / Executions, 2) Reads_Per_Run,
       Sql_Text
 from V$sqlarea
 where Executions > 0
       and Buffer_Gets > 0
       and (Buffer_Gets - Disk_Reads) / Buffer_Gets < 0.8
 order by 4 desc;

如果找到了最影响性能的sql语句,我们就可以教育改造这些不良sql,让它们改过自新。

3.5.2          单条sql语句优化

请容许我再罗嗦一下,在优化sql语句之间要首先对oracle的环境先进行优化,只有优化了oracle数据库环境,谈论sql语句的优化才是有意义的。道生一、一生二、二生三,数据库设计就是道,sql语句的优化只是其上的衍生。内存的命中率、数据库锁、数据库内部空间参数设计对sql效率的影响是深远的。环境的优化我们已经提过,如果已经优化了环境,剩下的就要考虑是否对要访问的表是否使用了充分索引、是否使用了最优的执行计划。

得到表中的数据途径很多,可以全表扫描,可以通过索引访问,可以使用先使用索引再使用rowid…,因为sql4GL(第四代语言),描述的是应该做什么而不是怎么做,处理细节是被系统隐藏起来的,所以可能有多种途径来完成同一个sql。优化器的作用就是分析使用那条途径的成本更低。优化器的执行原理如图8所示:优化器将会产生多种执行计划,并且会选择cost值最低的sql执行。图中的执行计划就好比是一条条路径,要找的是两点之间最短的那条。Oracle的优化器分为三种选则:rulecostchoose,现在默认都是choose。如果表被analyze过就使用costCost-Based Optimizer)形式,否则就使用rule形式。在rule模式下表名顺序,where条件中的连接顺序都会影响执行计划计划,在choose模式下,优化器会自动识别。对oracle的优化器,我们是可以放心的。

 

8

创建了索引和没有创建索引的表的究竟谁的效率更高呢?如何创建索引能最好的优化sql的性能呢?这些都是在使用索引时要考虑的。因为通过索引扫描到的数据上不会立刻从SGAdb_block_buffers中清理掉,而全表扫描的数据块是不保存在DB_BLOCK_BUFFERS中的。从内存中直接获得数据自然比从磁盘读数据要快的多。但是如果一个表很大,它对应的索引也可能很大,假设扫描一个200M的索引,它会将数据缓冲区中所有的其它数据移走,会严重影响其它sql的命中率。所以可以的出结论:对经常访问的表和数据量不太大的一些参数表使用索引是有效的。而对大表的访问恰恰是提高性能的关键,对于大表是否使用索引呢?假设一个有1千万条记录的表,索引为1万条记录,索引的选择性为0.1%。对这个表而言1个数据块包含5条表记录,对这个索引而言1个数据块包含80条索引记录,(也可以通过USER_SEGMENT数据字典查到每个表和索引的大小及数据块的个数)。如果使用索引检索表,检索索引需要访问125个数据块,通过索引只能得到部分数据,其余的数据还需要通过rowid访问表,如果数据很接近的理想状态,需要访问200个数据块,最坏的情况下(1个块中只包含一条需要记录),需要访问1000个数据块,访问的范围是325个到10125个。而全表扫描需要访问2百万个数据块。在这里使用索引是效率最高的。但是如果索引的选择性为10%,数据接近的情况下,通过索引访问也要2012500个数据块。而且这个索引会占用全部的数据缓冲区,是不能采用的方法。可以再次得出结论:对大表使用索引,一定要使用选择性高的索引、同时要控制索引的大小,这样才能提高效率。或者使用分区对提高大表的查询效率也是不错的选择。

一般来说oracle的优化器是最优的,但是也有可能要使用hint(提示)强制改变执行计划。例如如果使用DB_LINK连接远程数据库,那么使用mergerhint会更有效果,可以用LECCO SQL expert for oracle分析执行计划。首先它能够用更多的时间更充分的分析执行计划,其次它可以帮助我们分析是否需要hintSQL expert的使用在去年csdn的开发高手里有介绍。

实战:

ü         使用LECCO SQL expert for oracle分析执行计划

LECCO SQL expert中非常好用的一个工具是sql formatter,可以先用它来格式化我们的sql。打开sql editor将格式化好的sql拷贝上去点击optimize按纽,expert会自动分析所有的执行计划,稍等一下就出现了窗口,告诉我们有多少个执行计划被分析,多少相同的执行计划被舍弃,点确定显示剩余执行计划。点击batch run,将按照cost有低到高的顺序执行sql如图9(这将是耗时的操作,需要一定时间等待):

 

9

运行完毕,就可以仔细分析每种执行计划,点sql comparer按钮,来比较每个执行计划的资源消耗情况图10

 

10

ü         plsql developer是我推荐使用的工具,用这个东东写存储过程什么的非常方便。写好sql,按一下F5,就可以显示执行计划如图11,太方便了。随手看一下sql是采用了什么执行计划,是否是自己希望的那样,对写出高水平的sql非常有用。

 

11

ü         Tkprof的是分析sql语句的另一个重要工具

使用思路是先利用sql_trace得到sqltrc文件,可以用下面的方法。在pl/sql中执行下面的两条sql语句:

alter session set sql_trace true;

alter session set timed_statistics = true;

之后运行需要分析的sql语句,就可以得到trc文件,就在oracle参数user_dump_dest设置的目录下面。但是trc文件是看不懂的,要使用TKPORF工具,语法是:tkprof   ‘trc文件的路径   转化后的文件名。下面是一个转化后的文件。

call     count       cpu    elapsed       disk      query    current        rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch       29      0.11       0.11         71        103          2        2900

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total       31      0.11        0.11         71        103          2        2900

 

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 14 

说明本条sql使用的cpu时间为11秒、物理读71次、逻辑读103次、共访问2900条记录、sql语句对象以前不存在于shared_pool_size中,被重新读入sql缓冲区,优化模式为CHOOSE。最后不要忘记了要关闭sql_trace

              alter session set sql_trace false

        alter session set timed_statistics = false

ü         评估索引的使用(只在oracle9i)

oracle9i中,可以启用索引监控确定索引是否被正确使用,如果索引没有使用,可以删除它,节约空间,同时在DML时减少不必要的开销。

Alter index ‘index_name’ monitoring usage;

启动索引监控后,可以查询v$object_usage视图,确定索引是否被使用,是否被监控制。

4         总结

优良的sql性能被许多相互制约、关联的因素影响,总的来说如果满足下面的条件就可以得到优良的sql性能

Ø         好的数据库设计

Ø         好的体系结构设计

Ø         良好的平台(硬件平台、网络平台)

Ø         合理的环境参数(操作系统环境参数、oracle环境参数)

Ø         合理充分的索引

Ø         好的dba来不断优化

本人才疏学浅,相信文中必有可以商榷的地方,大家可以放言评论指正,如果能共同进步那就不胜感谢了

posted on 2007-04-19 17:52  lodestar  阅读(8757)  评论(3编辑  收藏  举报

导航