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

1、oracle数据库级别优化分析工具介绍当我们对数据库优化诊断时，需要收集相应的信息以供参考，从个人的使用经验来说，这种统计数据分为两大类一类是数据库级别的统计信息二类是os级别的统计信息下面就分别介绍绍在不同的级级别下，常用用什么工具来来收集信息帮帮助优化诊断断首先是oraccle数据库库级别优化分分析工具介绍绍目录：1.statsspack22.ASH33.AWR44.ORACCLE EXXPLAINN PLANN的总结(查查询sql的的执行计划)a.auttotracceb.exxplainn的使用1.statsspacka。安装sql sqqlpluss / aas syssdbaSQL

2、 seelect file_name from dba_ddata_ffiles;SQL crreate tableespacee perffstat dataffile e:orracleoradaataskkatepperfsttat.dbbf siize 20000m;sql OORACLEE_HOMEErdbmmsadmminsppcreatte.sqllb。使用SQL coonn peerfstaat/passswd收集统计信息ssql eexecutte staatspacck.snaap或SQL exec statsspack.SNAP(i_snaap_levvel =5);生成

3、报告sqll ORRACLE_HOMErdbmssadmiinsprreportt.sql定时收集信息有有两种方式，一一种是oraacle jjob，一种种是os的ccrontaab，我比较较习惯用oss级别的crrontabb设定其每个小时时自动收集一一次采样的jjobdeclareeVariaable job numbeer ;beegindbbms_joob.subbmit(:job, stattspackk.snapp; ,ttrunc( sysddate + 1/244 , hhh24 ), ttrunc(sysdaate+1/24,hhh24) );ccommitt ;endd ;

4、/查看job使用用情况SQL seelect job,sschemaa_userr,nextt_datee,inteerval,what from user_jobs自动停止采样jjobdeclareeVariaable job numbeer ;beegindbbms_joob.subbmit(:job, dbmss_job.brokeen(44,true); ,ttrunc( sysddate + 1 ), nulll );commiit ;ennd ;/清空所有staats统计信信息表里的数数据sql OORACLEE_HOMEErdbmmsadmminspptruncc.sqlsnap

5、shoot的levvel，这可可以通过EXXEC STTATSPAACK.MOODIFY_STATSSPACK_PARAMMETER(i_snaap_levvel=N)来修改，NN可以为0，55，6，7，110，缺 省省为5。 0 仅提提供一般性能能统计5 增增加了对SQQL语句总体体分析6 增增加了SQLL计划和使用用7 增加了了分段（Seegmentts）级的统统计10增加加了对闩锁（LLatchees）的分析析其中文档建议对对10要慎重重，因为代价价较高。eg：SQL execc stattspackk.SNAPP(i_snnap_leevel =6);oracle不不仅提供生成成数据库

6、报告告的脚本sppreporrt.sqll，还提供另另一个staatspacck 报告脚脚本spreepsql.sql来生生成SQL的的报告sql OORACLEE_HOMEErdbmmsadmminspprepsqql.sqll参考文档：利用用statsspack来来获取生成环环境中topp SQL及及其执行计划划 HYPERLINK /Doc/statspack_report_sql.htm http:/DDoc/sttatspaack_reeport_sql.hhtm2.AWRawr是建库是是自动配置和和启用的，他他对性能数据据的收集默认认是一小时，aawr对历史史数据的分析析生成报告脚

7、本在在目录下生成成报告使用$ORACLLE_HOMME/rdbbms/addmin/，如如下：awrrpt.sql ：生成指定快快照区间的统统计报表；aawrrptti.sqll ：生成指指定数据库实实例，并且指指定快照区间间的统计报表表；awrssqlrptt.sql ：生成指定定快照区间，指指定SQL语语句(实际指指定的是该语语句的SQLLID)的统统计报表；aawrsqrrpi.sqql ：生成成指定数据库库实例，指定定快照区间的的指定SQLL语句的统计计报表；awwrddrppt.sqll ：指定两两个不同的时时间周期，生生成这两个周周期的统计对对比报表；aawrddrrpi.sqql

8、 ：指定定数据库实例例，并指定两两个的不同时时间周期，生生成这两个周周期的统计对对比报表；修改Snapsshots设设置通过过MODIFFY_SNAAPSHOTT_SETTTINGS过过 程，DBBA可以调整整包括快照收收集频率、快快照保存时间间、以及捕获获的SQL数数量三个方面面的设置。 分别对应MMODIFYY_SNAPPSHOT_SETTIINGS 的的三个参数：Retentiion ：设设置快照保存存的时间，单单位是分钟。可可设置的值最最小为1天，最最大为1000年。设置该该参数值为00的话，就表表示永久保留留收集的快照照信息。Intervaal ：设置置快照收集的的频率，以分分钟为单

9、位。可可设置的值最最小为10分分钟，最大为为1年。如果果设置该参数数值为0，就就表示禁用AAWR特性。Topnsqll ：指定收收集的比较占占用资源的SSQL数量，可可设置的值最最小为30，最最大不超过11000000000。AWR相关几个个视图：DBA_HISST_WR_CONTRROL：查看看当前快照收收集的相关设设置v$acctive_sessiion_hiistoryy:由ASHH自动在 内内存中维护，以以每秒一次的的频率收集当当前系统中活活动sesssion的信信息dba_hist_activve_sesss_hisstory:是视图 vv$actiive_seessionn_his

10、ttory的历历史数据，保保存在硬盘上上dba_hhist_ddatabaase_innstancce:显示数数据库 是实实例的信息ddba_hiist_snnapshoot:当前数数据库收集到到的快照信息息3.ASHash和awrr不是完全分分离的两个功功能，ashh以秒为单位位从v$seessionn中收集信息息并保存在内内存中，这块块内存可以重重用，内存满满时，ASHH数据 交给给AWR，最最后写入系统统视图ash包括两部部分内容，一一部分是SGGA中的，这这部分反映是是本次系统启启动以来的数数据，并且AASH尽量保保留1小时的的内容，这部部分内容保存存在 v$aactivee_sess

11、sion_hhistorry另一部分分保存在系统统字典表里ddba_hiist_acctive_sess_histoory,是永永久的数据ASH也有生成成报告的脚本本，在目录下下$ORACCLE_HOOME/rddbms/aadmin/ashrpt.sql: 生成数据库库级别的ASSH统计报表表ashrppti.sqql: 生成成数据库实例例级别的ASSH统计报表表，常用于RRAC单实例例4.ORACLLE EXPPLAIN PLAN的的总结(查询询sql的执执行计划)a.autottrace安装用sys用户运运行脚本ulltxplaan.sqll建立这个表的脚脚本是：（UUNIX:$ORAC

12、LLE_HOMME/rdbbms/addmin, Windoows:%OORACLEE_HOMEE%rdbbmsaddmin）uultxpllan.sqql。SQL coonnectt HYPERLINK mailto:sys/syscolm2 sys/sysccolm2 as syysdba;SQL CC:oraacleoora92rdbmssadmiinutllxplann.sql;SQL crreate publiic synnonym plan_tablee for plan_tablee;-建立立同义词SQL grrant aall onn plann_tablle to publi

13、ic;-授授权所有用户户要在数据库中建建立一个角色色plusttrace,用sys用用户运行脚本本plusttrce.ssql来创建建这个角色，这这个脚本在目目录 （UNNIX:$OORACLEE_HOMEE/sqlpplus/aadmin, Winddows:%ORACLLE_HOMME%sqqlplussadmiin）中；SQL CC:oraacleoora92sqlpllusaddminpplustrrce.sqql;然后将角色pllustraace授予需需要autootracee的用户；SQLgraant pllustraace too publlic;经过以上步骤的的设置，就可可以在

14、sqll*pluss中使用auutotraace了，aautotrrace功能能只能在SQQL*PLUUS里使用AUTOTRAACE Sttatisttics常用用列解释 db bloock geets :从从buffeer cacche中读取取的blocck的数量cconsisstent gets:从bufffer caache中读读取的unddo数据的bblock的的数量phyysicall readds: 从磁磁盘读取的bblock的的数量reddo sizze: DMML生成的rredo的大大小sortts (meemory):在内存执执行的排序量量sortss (dissk):在磁磁

15、盘上执行的的排序量eg: HYPERLINK mailto:SYSdbset SYSdbset aautotrrace Ussage: SET AAUTOTRACE OFFF | ONN | TRRACEOONLY EXPPLAINN SSTATIISTICSS HYPERLINK mailto:SYSdbset SYSdbset ttimingg on HYPERLINK mailto:SYSdbset SYSdbset aautot tracee exp stat HYPERLINK mailto:SYSdbselect SYSdbselecct * ffrom ttab;3809 roo

16、ws seelecteed.Elapsedd: 00:00:000.06Executiion Pllan-| IId | Operaation | NName | Roows | Bytees | CCost (%CPU)|-| 00 | SEELECT STATEEMENT | | 10559 | 446596 | 2216 (2)| 1 | NEESTED LOOPSS OUTEER | | 10599 | 466596 | 2116 (2)| 2 | TAABLE AACCESSS FULLL | OBJ$ | 1059 | 391183 | 1588 (22)| 3 | TABBLE

17、ACCCESS CLUSTTER| TTAB$ | 1 | 7 | 1 (0)| 4 | INDDEX UNNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|-Note- PLAN_TABLEE is old vversioonStatisttics-0 rrecurssive ccalls00 db blockk getss9077 conssistennt getts0 pphysiccal reeads0 redoo sizee1335002 byytes ssent vvia SQQL*Nett to cclientt3252 bytees recceivedd vi

18、a SQL*NNet frrom cllient2255 SSQL*Neet rouundtriips too/fromm clieent0 sortss (memmory)00 sorrts (ddisk)33809 rows proceessedb。EXPLAAIN的使用用 Oraacle RRDBMS执执行每一条SSQL语句，都都必须经过OOraclee优化器的评评估。所以，了了解优化器是是如何选择(搜索)路径径以及索引是是如何被使用用的，对优化化SQL语句句有很大的帮帮助。Expplain可可以用来迅速速方便地查出出对于给定SSQL语句中中的查询数据据是如何得到到的即搜索路路径(我们通通常称为Acccess Path)。从而使我我们选择最优优的查询方式式达到最大的的优化效果。1.1 、安 装 要使用EXPPLAIN首首先要执行相相应的脚本，创创建出Expplain_plan表表。具体脚本执行如如下： $ORAACLE_HHOME/rrdbms/adm