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

yesn

　　当我们对数据库优化诊断时，需要收集相应的信息以供参考，从个人的使用经验来说，这种统计数据分为两大类
　　一类是数据库级别的统计信息
　　二类是os级别的统计信息
　　下面就分别介绍在不同的级别下，常用什么工具来收集信息帮助优化诊断
　　首先是oracle数据库级别优化分析工具介绍
　　目录：
　　1.statspack
　　2.ASH
　　3.AWR
　　4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划)
　　a.autotrace
　　b.explain的使用
　　1.statspack
　　a。安装
　　sql> sqlplus "/ as sysdba"
　　SQL> select file_name from dba_data_files;

　　SQL> create tablespace perfstat datafile 'e:/oracle/oradata/skate/perfstat.dbf'>　　sql> @ORACLE_HOME/rdbms/admin/spcreate.sql
　　b。使用
　　SQL> conn perfstat/passwd
　　收集统计信息
　　sql> execute statspack.snap
　　或
　　SQL> exec statspack.SNAP(i_snap_level =>5);
　　生成报告
　　sql> @ORACLE_HOME/rdbms/admin/spreport.sql
　　定时收集信息有两种方式，一种是oracle job，一种是os的crontab，我比较习惯用os级别的crontab
　　设定其每个小时自动收集一次采样的job
　　declare
　　Variable  job  number ;
　　begin
　　dbms_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
　　自动停止采样job
　　declare
　　Variable  job  number ;
　　begin
　　dbms_job.submit(:job, "dbms_job.broken(44,true);" ,trunc( sysdate + 1 ), "null" );
　　commit ;
　　end ;
　　/
　　清空所有stats统计信息表里的数据
　　sql> @ORACLE_HOME/rdbms/admin/sptrunc.sql
　　snapshot的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_HOME/rdbms/admin/sprepsql.sql
　　参考文档：
　　利用statspack来获取生成环境中top SQL及其执行计划
　　http://www.hellodba.com/Doc/statspack_report_sql.htm
　　2.AWR
　　awr是建库是自动配置和启用的，他对性能数据的收集默认是一小时，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.ASH
　　ash和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%/rdbms/admin）ultxplan.sql。
　　SQL> connect sys/sys@colm2 as sysdba;
　　SQL> @C:/oracle/ora92/rdbms/admin/utlxplan.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%/sqlplus/admin）中；
　　SQL> @C:/oracle/ora92/sqlplus/admin/plustrce.sql;
　　然后将角色plustrace授予需要autotrace的用户；
　　SQL>grant 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>　　sorts (memory):在内存执行的排序量
　　sorts (disk):在磁盘上执行的排序量
　　eg:
　　SYS@db>set autotrace
　　Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
　　SYS@db>set timing on
　　SYS@db>set autot trace exp stat
　　SYS@db>select * from tab;
　　3809 rows selected.
　　Elapsed: 00:00:00.06
　　Execution Plan
　　----------------------------------------------------------
　　---------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------
　　|   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 version
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　9077  consistent gets
　　0  physical reads

　　0  redo>　　133502  bytes sent via SQL*Net to client
　　3252  bytes received via SQL*Net from client
　　255  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　3809  rows processed
　　b。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 [=] < string literal > ] [ INTO < table_name > ]
　　FOR < sql_statement >
　　其中：
　　STATEMENT_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。
　　TABLE_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。
　　SQL_STATEMENT：是真正的SQL语句。
　　比如：
　　SQL>explain plan set statement_id='T_TEST' for select * from t_test;
　　SQL>
　　Explained
　　执行下面语句可以查询到执行计划
　　SQL>SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
　　2  FROM PLAN_TABLE  a
　　3  WHERE STATEMENT_ID='T_TEST'

　　4  ORDER BY>　　也可以用这句话 select * from table(dbms_xplan.display); 可以把所有PLAN_TABLE里的数据罗列出来。