oracle数据库级别优化分析工具介绍

1、oraclle数据库库级别优化化分析工具具介绍当我们对数数据库优化化诊断时，需需要收集相相应的信息息以供参考考，从个人人的使用经经验来说，这这种统计数数据分为两两大类一类类是数据库库级别的统统计信息二二类是oss级别的统统计信息下面就分别别介绍在不不同的级别别下，常用用什么工具具来收集信信息帮助优优化诊断首先是orraclee数据库级级别优化分分析工具介介绍目录：1.staatspaack2.ASH33.AWRR4.ORRACLEE EXPPLAINN PLAAN的总结结(查询ssql的执执行计划)a.auutotrracebb.expplainn的使用1.staatspaacka。安装sql

2、 sqlpplus / aas syysdbaaSQL seleect ffile_namee froom dbba_daata_ffiless;SQL creaate ttableespacce peerfsttat ddataffile e:oraccleooradaatasskateeperrfstaat.dbbf ssize 20000m;sql ORAACLE_HOMEErdbbmsaadminnspccreatte.sqqlb。使用SQL connn perrfstaat/paasswdd收集统计信信息sqll exxecutte sttatsppack.snapp或SQL exe

3、ec sttatsppack.SNAPP(i_ssnap_leveel =5);生成报告ssql ORAACLE_HOMEErdbbmsaadminnsprreporrt.sqql定时收集信信息有两种种方式，一一种是orraclee jobb，一种是是os的ccronttab，我我比较习惯惯用os级级别的crrontaab设定其每个个小时自动动收集一次次采样的jjobdeclaareVaariabble job nummber ;beggindbbms_jjob.ssubmiit(:jjob, staatspaack.ssnap; ,ttruncc( syysdatte + 1/244 , h

4、h244 ), trrunc(sysddate+1/244,hhh24) );commmit ;end ;/查看jobb使用情况况SQL seleect jjob,sschemma_usser,nnext_datee,inttervaal,whhat ffrom userr_jobbs自动停止采采样jobbdeclaareVaariabble job nummber ;beggindbbms_jjob.ssubmiit(:jjob, dbmms_joob.brrokenn(44,truee); ,truunc( sysddate + 1 ), nulll );commmit ;end ;/清空

5、所有sstatss统计信息息表里的数数据sql ORAACLE_HOMEErdbbmsaadminnspttruncc.sqllsnapsshot的的leveel，这可可以通过EEXEC STATTSPACCK.MOODIFYY_STAATSPAACK_PPARAMMETERR(i_ssnap_leveel=N)来修改，NN可以为00，5，66，7，110，缺 省为5。 0 仅提供一一般性能统统计5 增增加了对SSQL语句句总体分析析6 增加加了SQLL计划和使使用7 增增加了分段段（Seggmentts）级的的统计100增加了对对闩锁（LLatchhes）的的分析其中文档建建议对100要慎重

6、，因因为代价较较高。eg：SQQL eexec stattspacck.SNNAP(ii_snaap_leevel =6);oraclle不仅提提供生成数数据库报告告的脚本ssprepport.sql，还还提供另一一个staatspaack 报报告脚本sspreppsql.sql来来生成SQQL的报告告sql ORAACLE_HOMEErdbbmsaadminnsprrepsqql.sqql参考文档：利用sttatsppack来来获取生成成环境中ttop SSQL及其其执行计划划 HYPERLINK /Doc/statspack_report_sql.htm httpp:/Doc/statts

7、pacck_reeportt_sqll.htmm2.AWRRawr是建建库是自动动配置和启启用的，他他对性能数数据的收集集默认是一一小时，aawr对历历史数据的的分析生成报告脚脚本在目录录下生成报报告使用$ORACCLE_HHOME/rdbmms/addmin/，如下：awrrppt.sqql ：生生成指定快快照区间的的统计报表表；awrrrptii.sqll ：生成成指定数据据库实例，并并且指定快快照区间的的统计报表表；awrrsqlrrpt.ssql ：生成指定定快照区间间，指定SSQL语句句(实际指指定的是该该语句的SSQLIDD)的统计计报表；aawrsqqrpi.sql ：生成指指定

8、数据库库实例，指指定快照区区间的指定定SQL语语句的统计计报表；aawrdddrpt.sql ：指定两两个不同的的时间周期期，生成这这两个周期期的统计对对比报表；awrdddrpii.sqll ：指定定数据库实实例，并指指定两个的的不同时间间周期，生生成这两个个周期的统统计对比报报表；修改Snaapshoots设置置通过过MODIIFY_SSNAPSSHOT_SETTTINGSS过 程，DDBA可以以调整包括括快照收集集频率、快快照保存时时间、以及及捕获的SSQL数量量三个方面面的设置。 分别对对应MODDIFY_SNAPPSHOTT_SETTTINGGS 的三三个参数：Retenntionn

9、 ：设置置快照保存存的时间，单单位是分钟钟。可设置置的值最小小为1天，最最大为1000年。设设置该参数数值为0的的话，就表表示永久保保留收集的的快照信息息。Interrval ：设置快快照收集的的频率，以以分钟为单单位。可设设置的值最最小为100分钟，最最大为1年年。如果设设置该参数数值为0，就就表示禁用用AWR特特性。Topnssql ：指定收集集的比较占占用资源的的SQL数数量，可设设置的值最最小为300，最大不不超过10000000000。AWR相关关几个视图图：DBA_HHIST_WR_CCONTRROL：查查看当前快快照收集的的相关设置置v$acctivee_sesssionn_hi

10、sstoryy:由ASSH自动在在 内存中中维护，以以每秒一次次的频率收收集当前系系统中活动动sesssion的的信息dbba_hiist_aactivve_seess_hhistoory:是是视图 vv$acttive_sesssion_histtory的的历史数据据，保存在在硬盘上ddba_hhist_dataabasee_insstancce:显示示数据库 是实例的的信息dbba_hiist_ssnapsshot:当前数据据库收集到到的快照信信息3.ASHHash和aawr不是是完全分离离的两个功功能，assh以秒为为单位从vv$sesssionn中收集信信息并保存存在内存中中，这块内内

11、存可以重重用，内存存满时，AASH数据据 交给AAWR，最最后写入系系统视图ash包括括两部分内内容，一部部分是SGGA中的，这这部分反映映是本次系系统启动以以来的数据据，并且AASH尽量量保留1小小时的内容容，这部分分内容保存存在 v$actiive_ssessiion_hhistoory另一一部分保存存在系统字字典表里ddba_hhist_actiive_ssess_histtory,是永久的的数据ASH也有有生成报告告的脚本，在在目录下$ORACCLE_HHOME/rdbmms/addmin/ashrppt.sqql: 生生成数据库库级别的AASH统计计报表asshrptti.sqql:

12、 生生成数据库库实例级别别的ASHH统计报表表，常用于于RAC单单实例4.ORAACLE EXPLLAIN PLANN的总结(查询sqql的执行行计划)a.auttotraace安装用sys用用户运行脚脚本ulttxplaan.sqql建立这个表表的脚本是是：（UNNIX:$ORACCLE_HHOME/rdbmms/addmin, Winndowss:%ORRACLEE_HOMME%rrdbmssadmmin）uultxpplan.sql。SQL connnect HYPERLINK mailto:sys/syscolm2 sys/syscolmm2 ass syssdba;SQL C:ora

13、ccleoora922rdbbmsaadminnutllxplaan.sqql;SQL creaate ppubliic syynonyym pllan_ttablee forr plaan_taable;-建立立同义词SQL grannt alll onn plaan_taable to ppubliic;-授权所有有用户要在数据库库中建立一一个角色pplusttracee,用syys用户运运行脚本pplusttrce.sql来来创建这个个角色，这这个脚本在在目录 （UUNIX:$ORAACLE_HOMEE/sqllpluss/admmin, Winddows:%ORAACLE_HOMEE%

14、sqqlpluusaddmin）中中；SQL C:oraccleoora922sqllplussadmminpplusttrce.sql;然后将角色色plusstracce授予需需要auttotraace的用用户；SQLggrantt pluustraace tto puublicc;经过以上步步骤的设置置，就可以以在sqll*pluus中使用用autootracce了，aautottracee功能只能能在SQLL*PLUUS里使用用AUTOTTRACEE Staatisttics常常用列解释释 db bblockk getts :从从bufffer ccachee中读取的的blocck的数量

15、量conssisteent ggets:从bufffer cachhe中读取取的unddo数据的的blocck的数量量physsicall reaads: 从磁盘读读取的bllock的的数量reedo ssize: DMLL生成的rredo的的大小soorts (memmory):在内存存执行的排排序量soorts (dissk):在在磁盘上执执行的排序序量eg: HYPERLINK mailto:SYSdbset SYSddbseet auutotrrace Usaage: SET AUTOOTRAACE OFFF | OON | TRACCEONNLY EXXPLAAIN STTATIIS

16、TICCS HYPERLINK mailto:SYSdbset SYSddbseet tiimingg on HYPERLINK mailto:SYSdbset SYSddbseet auutot tracce exxp sttat HYPERLINK mailto:SYSdbselect SYSddbseelectt * ffrom tab;3809 rowss sellecteed.Elapssed: 00:000:000.06Execuutionn Plaan-| Idd | Operratioon | Namee | Rowws | Byttes | Cosst (%CPU)|-| 0

17、 | SEELECTT STAATEMEENT | | 10599 | 4465966 | 2166 (2)| 11 | NESTTED LLOOPSS OUTTER | | 10559 | 465996 | 2116 (2)| 2 | TAABLE ACCEESS FFULL | OBJ$ | 10059 | 391183 | 1158 (2)| 3 | TTABLEE ACCCESS CLUSSTER| TABB$ | 1 | 7 | 1 (00)| 4 | INDDEX UUNIQUUE SCCAN | I_OBJ# | 11 | | 00 (0)|-Note- PPLAN_TABLLE

18、 iis olld veersioonStatiisticcs-00 reecurssive callls0 db bblockk getts90777 cconsiistennt geets0 phyysicaal reeads00 reedo ssize11335002 bbytess sennt viia SQQL*Neet too cliient33252 byttes rreceiived via SQL*Net fromm cliient2255 SQL*Net rounndtriips tto/frrom ccliennt0 sortts (mmemorry)0 sorrts (

19、diskk)38009 rrows proccesseedb。EXPPLAINN的使用 OOraclle RDDBMS执执行每一条条SQL语语句，都必必须经过OOraclle优化器器的评估。所以，了了解优化器器是如何选选择(搜索索)路径以以及索引是是如何被使使用的，对对优化SQQL语句有有很大的帮帮助。Exxplaiin可以用用来迅速方方便地查出出对于给定定SQL语语句中的查查询数据是是如何得到到的即搜索索路径(我我们通常称称为Acccess Pathh)。从而而使我们选选择最优的的查询方式式达到最大大的优化效效果。1.1 、安 装 要使用EEXPLAAIN首先先要执行相相应的脚本本，创建出出Expllain_plann表。具体脚本执执行如下： $OORACLLE_HOOME/rrdbmss/ad