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

oracle数据库级别优化分析工具介绍当我们对数据库优化诊断时，需要收集相应的信息以供参考，从个人的使用经验来说，这种统计数据分为两大类一类是数据库级别的统计信息二类是os级别的统计信息下面就分别介绍在不同的级别下，常用什么工具来收集信息帮助优化诊断首先是oracle数据库级别优化分析工具介绍目录：1.statspack2.ASH3.AWR4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划)a.autotraceb.explain的使用1.statspacka。安装sql sqlplus / as sysdbaSQL select file_name from dba_data_files;SQL create tablespace perfstat datafile e:oracleoradataskateperfstat.dbf size 2000m;sql ORACLE_HOMErdbmsadminspcreate.sqlb。使用SQL conn perfstat/passwd收集统计信息sql execute statspack.snap或SQL exec statspack.SNAP(i_snap_level =5);生成报告sql ORACLE_HOMErdbmsadminspreport.sql定时收集信息有两种方式，一种是oracle job，一种是os的crontab，我比较习惯用os级别的crontab设定其每个小时自动收集一次采样的jobdeclareVariable job number ;begindbms_job.submit(:job, statspack.snap; ,trunc( sysdate + 1/24 , hh24 ), trunc(sysdate+1/24,hh24) );commit ;end ;/查看job使用情况SQL select job,schema_user,next_date,interval,what from user_jobs自动停止采样jobdeclareVariable job number ;begindbms_job.submit(:job, dbms_job.broken(44,true); ,trunc( sysdate + 1 ), null );commit ;end ;/清空所有stats统计信息表里的数据sql ORACLE_HOMErdbmsadminsptrunc.sqlsnapshot的level，这可以通过EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=N)来修改，N可以为0，5，6，7，10，缺 省为5。 0 仅提供一般性能统计5 增加了对SQL语句总体分析6 增加了SQL计划和使用7 增加了分段（Segments）级的统计10增加了对闩锁（Latches）的分析其中文档建议对10要慎重，因为代价较高。eg：SQL exec statspack.SNAP(i_snap_level =6);oracle不仅提供生成数据库报告的脚本spreport.sql，还提供另一个statspack 报告脚本sprepsql.sql来生成SQL的报告sql ORACLE_HOMErdbmsadminsprepsql.sql参考文档：利用statspack来获取生成环境中top SQL及其执行计划/Doc/statspack_report_sql.htm2.AWRawr是建库是自动配置和启用的，他对性能数据的收集默认是一小时，awr对历史数据的分析生成报告脚本在目录下生成报告使用$ORACLE_HOME/rdbms/admin/，如下：awrrpt.sql ：生成指定快照区间的统计报表；awrrpti.sql ：生成指定数据库实例，并且指定快照区间的统计报表；awrsqlrpt.sql ：生成指定快照区间，指定SQL语句(实际指定的是该语句的SQLID)的统计报表；awrsqrpi.sql ：生成指定数据库实例，指定快照区间的指定SQL语句的统计报表；awrddrpt.sql ：指定两个不同的时间周期，生成这两个周期的统计对比报表；awrddrpi.sql ：指定数据库实例，并指定两个的不同时间周期，生成这两个周期的统计对比报表；修改Snapshots设置通过MODIFY_SNAPSHOT_SETTINGS过 程，DBA可以调整包括快照收集频率、快照保存时间、以及捕获的SQL数量三个方面的设置。 分别对应MODIFY_SNAPSHOT_SETTINGS 的三个参数：Retention ：设置快照保存的时间，单位是分钟。可设置的值最小为1天，最大为100年。设置该参数值为0的话，就表示永久保留收集的快照信息。Interval ：设置快照收集的频率，以分钟为单位。可设置的值最小为10分钟，最大为1年。如果设置该参数值为0，就表示禁用AWR特性。Topnsql ：指定收集的比较占用资源的SQL数量，可设置的值最小为30，最大不超过100000000。AWR相关几个视图：DBA_HIST_WR_CONTROL：查看当前快照收集的相关设置v$active_session_history:由ASH自动在 内存中维护，以每秒一次的频率收集当前系统中活动session的信息dba_hist_active_sess_history:是视图 v$active_session_history的历史数据，保存在硬盘上dba_hist_database_instance:显示数据库 是实例的信息dba_hist_snapshot:当前数据库收集到的快照信息3.ASHash和awr不是完全分离的两个功能，ash以秒为单位从v$session中收集信息并保存在内存中，这块内存可以重用，内存满时，ASH数据 交给AWR，最后写入系统视图ash包括两部分内容，一部分是SGA中的，这部分反映是本次系统启动以来的数据，并且ASH尽量保留1小时的内容，这部分内容保存在 v$active_session_history另一部分保存在系统字典表里dba_hist_active_sess_history,是永久的数据ASH也有生成报告的脚本，在目录下$ORACLE_HOME/rdbms/admin/ashrpt.sql: 生成数据库级别的ASH统计报表ashrpti.sql: 生成数据库实例级别的ASH统计报表，常用于RAC单实例4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划)a.autotrace安装用sys用户运行脚本ultxplan.sql建立这个表的脚本是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%rdbmsadmin）ultxplan.sql。SQL connect sys/syscolm2 as sysdba;SQL C:oracleora92rdbmsadminutlxplan.sql;SQL create public synonym plan_table for plan_table;-建立同义词SQL grant all on plan_table to public;-授权所有用户要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目录 （UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%sqlplusadmin）中；SQL C:oracleora92sqlplusadminplustrce.sql;然后将角色plustrace授予需要autotrace的用户；SQLgrant plustrace to public;经过以上步骤的设置，就可以在sql*plus中使用autotrace了，autotrace功能只能在SQL*PLUS里使用AUTOTRACE Statistics常用列解释 db block gets :从buffer cache中读取的block的数量consistent gets:从buffer cache中读取的undo数据的block的数量physical reads: 从磁盘读取的block的数量redo size: DML生成的redo的大小sorts (memory):在内存执行的排序量sorts (disk):在磁盘上执行的排序量eg:SYSdbset autotrace Usage: SET AUTOTRACE OFF | ON | TRACEONLY EXPLAIN STATISTICSSYSdbset timing onSYSdbset autot trace exp statSYSdbselect * from tab;3809 rows selected.Elapsed: 00:00:00.06Execution Plan-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-| 0 | SELECT STATEMENT | | 1059 | 46596 | 216 (2)| 1 | NESTED LOOPS OUTER | | 1059 | 46596 | 216 (2)| 2 | TABLE ACCESS FULL | OBJ$ | 1059 | 39183 | 158 (2)| 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 7 | 1 (0)| 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|-Note- PLAN_TABLE is old versionStatistics-0 recursive calls0 db block gets9077 consistent gets0 physical reads0 redo size133502 bytes sent via SQL*Net to client3252 bytes received via SQL*Net from client255 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)3809 rows processedb。EXPLAIN的使用 Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。1.1 、安 装 要使用EXPLAIN首先要执行相应的脚本，创建出Explain_plan表。具体脚本执行如下： $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) 该脚本后会生成一个表这个程序会创建一个名为plan_table的表。1.2 、使用常规使用语法：explain PLAN SET STATEMENT_ID = INTO FOR 其中：STATEMENT_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。TABLE_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。SQL_STATEMENT：是真正的SQL语句。 比 如： SQLexplain plan set statement_id=T_TEST for select * from t_test; SQLExplained执行下面