oracle数据库优化
转自:http://www.cnblogs.com/lodestar/archive/2007/04/19/719933.html
(自己保留起来)
这是以前在程序员上发表的一篇文章
1 概述
数据库是信息系统中核心的部分,数据库的高效性、安全性、稳定性、延展性,是项目成功的关键之一。一个好的数据库系统,设计当然是关键,但是就像显微镜的粗调和微调,当软件开发完成后,通过不断的对系统的跟踪监控,对数据库系统进行优化也是必不可少的。可以认为优化是数据库设计的一种顺延,和设计是相辅相成的。数据库的优化实际上从设计、数据库的安装就已经开始了,我们不应该就sql语句的效率孤立的谈论数据库的优化问题。实际上很多数据库优化的经验,在设计的时候就必须要考虑。
1.1 文章的组织结构
本文首先谈了一下和性能优化有关的基本概念,之后分六个方面详细讨论了数据库优化的方法、手段。在数据库的监控部分讲述了为什么?什么时候?如何监控数据库,并给出了一些可以直接使用的简单sql,用来直接分析某些方面的问题。从始至终,贯穿了一个实际的例子帮助读者能更好的理解有关理论。由于使用了多个工具参与优化工作,文中对每种工具的使用技巧做了简单提示。本文用到的例子是作者前不久遇到的一个项目,操作系统是DIGITAL ES20(True64UNIX),硬件配置为 520M内存,单cpu,单SCSI硬盘。这里只提一下大概的性能和功能要求:系统要求能在短时间内,导入5、6百万条的记录、部分数据需要手工录入。录入的记录进行处理后,被处理后产生各种报表,并且报表很可能发生变化。本系统在使用了一段时间后,发现性能严重下降,生成报表和数据导入都需要很长时间,通过优化将响应控制在了可以忍受的范围。
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 fault、page pin、page 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—Performance中boost选择None,减少文件系统缓冲
Ø 利用性能监视器监视数据库服务器,分析性能瓶颈
Ø 利用微软资源工具箱(resources pake)中的工具(Process Viewer、Process Explode、Quick Slice、Process Stat)监视数据库服务器,决定操作系统是否优化
3.4 数据库系统优化
oracle基本的体系结构如图2所示,每个后台进程将占用5M大小的内存。通过合理的分配内存大小,合理的设置表空间体系和内部空间参数。可以提高磁盘空间的利用率、减少数据段碎片、并且在查询和向数据文件写入数据的时候使用较少的IO,较少的IO也会降低cpu的资源消耗。同时对环境参数的合理配置,可以使数据库中数据顺畅的流动,减少锁存器冲突和各种等待,充分的利用系统资源。
图4
内存结构的优化主要是通过init.ora文件的环境参数来配置,要主要注意下面几个:
db_block_size:数据库中每个数据块的大小,默认是2048字节(2k),一般应该增大到4K、8K,大型数据库也常使用16K和32K,通常SGA也应该增加。
Shared_pool_size:至于shared_pool_size大小是否合适,可以通过对数据库的监控得到,也可以通过一些sql语句实现。我们可以查询v$SGASTAT,查看SGA结构,其中free memory做为一个估计性的指标,如果大于20%可以将shared_pool_size给小些,给其他部分多分配些,如果小于8%,就可以开大些share_pool_size。有一些更精确的sql脚本可以查看其中library_cache,cursor_cache,pl/sql cache大小是否合适,如果数据库规模增长,还需要多少内存,所有这些都可以在附件中找到。
Log_buffer:为了减少LGWR和DBWR冲突,大型数据库的log_buffer一般都是要手工调大些,一般为2M到3M。可以查询V¥SYSSTAT视图中redo log space requests,如果大于0就应该增加log_buffer的值。其他对oracle影响很大的参数有:db_writes、db_file_multiblock_read、sort_direct_writes、sort_area_size讲起来就多了,由于和例子关系不大所以不再叙述。需要注意的是:对oracle9i来说sort_area_size,sort_area_retained_size,hash_area_size,bitmap_merge_area_size等参数都被放弃,由pga_aggregate_target统一动态分配内存区域大小,对于undo(回滚)操作也有些变化:undo_retention表示已提交数据在回滚表空间中保留时间,以秒为单位,缺省900。此选项可使新事务尽可能使用空闲的回滚表空间,这样就减少了查询过程因snapshot too old而失败的几率。undo_tablespace表示系统的回滚表空间。
所有的环境参数,都可以通过系统的监控工具来分析是否适宜。
实战:对数据库系统可以使用toad和oracle提供的脚本utlbstat、utlestat监控:
ü toad的监控结果如图5、图6,分别使用了dba 下的database monitor和log switch frequence map,toad还有很多工具大家都可以试试,还是很有特色的。
图5
对于logical io:consistent reads表示了逻辑获取,对全表扫描执行一次consistent reads增加1;对通过索引访问表consistent reads的值为索引高度+2*关键字。db_block_change是更新的数据块个数和数据更新的行数之和,表示被修改了的与无效列表连锁的数据块的数目。physical io 是物理读的统计。
Call rate和miss rate、waits也是应该关心的地方,可以看到通常在执行什么工作时系统出现等待。
我们现在一起分析上面的图:在早上8:05至8: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/4的io读取能力,所以监控结果会出现数据不一致。这正是造成硬盘io瓶颈的原因之一啊,看来必须要调整数据库块的大小,下面有一个章节介绍了如何修改数据库块大小。
再来看另外的部分:最多的call rate 是execute,最多的miss rate是sql area,miss rate中sql area存在峰值说明,部分时间段内sql的命中率较低,需要进一步查看是否是因为不良sql引起的,还是因为sql缓冲区太小。使用toad的health check 可知db_block_buffers的命中率为97%,通过v$sgastat视图可查知shared_pool_size中free mem有只4%左右,看来shared_pool_size太小了,结合上面存在大量页面交换的事实,得出结论:系统需要增加内存或者减少整体SGA,为系统留下更多的缓冲内存,同时还需要增加shared_pool_size的值。由于后面用utlestat脚本监控到sort disk始终为0,为排序分配的内存可以少留些,所以将sort area size从75M降低到了32M,这部分内存就划分给了shared_pool_size。接下来看图6,图6监测的是(redo log file)重做日志文件的使用情况。
图6
可以看到未优化前redo log 频繁发生,说明需要增加重写日志缓冲区和重写日志,看来LGWR和DBWR的竞争也是硬盘io瓶颈的原因之一。优化的措施是Log buffer增加到2M,redo log file修改为100M,在以后的监测中,即使是事务高峰期也能控制日志文件的交换每小时最多1到2次。一般log buffer开到2M—3M就可以了,如果太大会有负面影响,有可能在redo操作时会丢失数据。
当然也可以使用sql语句分析oracle,许多人也喜欢这样做。感兴趣的朋友可以在附录的文件中找到对自己有用的sql直接使用。还有许多人是自己开发软件来分析数据库的,据说这是最有效的方法。毕竟oracle已经提供了使用方便的许多视图,toad也是如此实现的。
ü Linux下面也有个类似toad的工具tora,如果谁的工作环境是linux就可以用用看。
ü Utlbstat和utlestat是非常好的监控脚本,toad能发现的问题使用它同样也能发现,可以指定监控的时间段,是发现问题的一种更量化的方法,推荐使用。
这两个文件在oracle_home/rdbms/admin下,在运行脚本前注意更改tiemed_statistic为true,否则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用户,同时在perfstat的schema(模式)下将创建一些对象,大约有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 Expert、oracle trace。我个人觉的OEM是监控工具中最有效的一种工具。图7为oem的界面;
图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 修改数据库块大小
根据上面分析我们需要改变数据库块大小,在安装完数据库后改变数据库块,只能导出所有数据,并重新重建整个数据库。如果您使用的是oracle8i、9i那么恭喜您,使用dbassist(oracle database assistent)命令太方便了,在创建时选择custom选项,就可以调整db_block_size大小。如果是oracle7.3.4的用户,手工重建数库需要使用create database命令,重建前要修改init.ora,指定db_block_size为8K。修改数据库块尺寸后,在每个数据块中将会有更多的行,所以发生块级竞争的可能性增大,需要创建表和索引时将freelist的值设为非默认值1,一般大于8k的数据库块,freelist大于4会得到更多的好处。是否存在freelist冲突,可以使用附录中的sql判断一下。
3.4.2 一些常用经验
oracle的内部空间管理和表空间需要遵守一些经验,这些都是设计数据库时要考虑的问题。比如表空间设计的时候应遵循oracle的最优化体系结构建议;db_block_buffers和library_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 extent为256k,pctincreasew为0,那么每个extent(区间)为256k, segment(数据段)上将有会40个extent,访问每个extent需要访问2次读64个数据块,全表扫描需要80次的数据块获取。如果设置next extent设为130K,每个extent为130k,segment上将会存在79个extent,这种情况下访问每个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…,因为sql是4GL(第四代语言),描述的是应该做什么而不是怎么做,处理细节是被系统隐藏起来的,所以可能有多种途径来完成同一个sql。优化器的作用就是分析使用那条途径的成本更低。优化器的执行原理如图8所示:优化器将会产生多种执行计划,并且会选择cost值最低的sql执行。图中的执行计划就好比是一条条路径,要找的是两点之间最短的那条。Oracle的优化器分为三种选则:rule、cost、choose,现在默认都是choose。如果表被analyze过就使用cost(Cost-Based Optimizer)形式,否则就使用rule形式。在rule模式下表名顺序,where条件中的连接顺序都会影响执行计划计划,在choose模式下,优化器会自动识别。对oracle的优化器,我们是可以放心的。
图8
创建了索引和没有创建索引的表的究竟谁的效率更高呢?如何创建索引能最好的优化sql的性能呢?这些都是在使用索引时要考虑的。因为通过索引扫描到的数据上不会立刻从SGA的db_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连接远程数据库,那么使用merger的hint会更有效果,可以用LECCO SQL expert for oracle分析执行计划。首先它能够用更多的时间更充分的分析执行计划,其次它可以帮助我们分析是否需要hint。SQL 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得到sql的trc文件,可以用下面的方法。在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来不断优化
本人才疏学浅,相信文中必有可以商榷的地方,大家可以放言评论指正,如果能共同进步那就不胜感谢了