oracle10如何查出历史记录中执行效率低的SQL语句

nesta2001zhang

浏览: 70790 次
性别:
来自: 北京

最近访客更多访客>>

chenquanyi626

NO__ONE__

cmdjqxclb

911220wmx

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

解决oracle10g运行慢、无法确定瓶颈的问题，为了找到效率低的sql费劲不少，今天找到一个，

方法就是查看awr报告。

Oracle 在10g以前的使用的是 Statspack做性能故障诊断的。Oracle Database 10g 提供了一个显著改进的工具：自动工作负载信息库 (AWR)。
　　Oracle 建议用户用这个取代 Statspack。AWR 实质上是一个 Oracle 的内置工具，和数据库一起安装。
　　数据库装好后，快照由一个称为 MMON 的新的后台进程及其从进程自动地每小时采集一次（snap）
　　它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。

　　为了节省空间，采集的数据在 7 天后自动清除。快照频率和保留时间都可以由用户修改。它产生两种类型的输出：文本格式（类似于 Statspack 报表的文本格式但来自于 AWR 信息库）和默认的 HTML 格式（拥有到部分和子部分的所有超链接），从而提供了非常用户友好的报表。

　　AWR 使用几个表来存储采集的统计数据，所有的表都存储在新的名称为 SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$_* 和 WRH$_* 的格式命名，H 代表“历史数据 (historical)”而 M 代表“元数据 (metadata)”。
　　前一种类型(WRM$_*)存储元数据信息（如检查的数据库和采集的快照），
　　后一种类型(WRH$_*)保存实际采集的统计数据。

　　在这些表上构建了几种带前缀 DBA_HIST_ 的视图，这些视图可以用来编写您自己的性能诊断工具。
　　视图的名称直接与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上构建的。
　　select t.view_name from dba_views t where t.view_name like 'DBA_HIST%'

　　AWR 历史表采集的信息比 Statspack 多许多，这些信息包括表空间使用率、文件系统使用率、甚至操作系统统计数据。

具体操作方式：

登陆到服务器的操作系统；

进入到$ORACLE_HOME/rdbms/admin目录下；

然后sqlplus "/as sysdba"登陆到数据库，执行
@awrrpt.sql;

然后按照提示一步一步做，注意格式选html（这样方便你阅读）。

过程中需要设置以下值：
　　report_type=html或text，enter采用默认为html
　　num_days=1
　　begin_snap=输入开始快照ID
　　end_snap=输入结束快照ID
　　report_name=输入要保存的文件名，enter采用默认文件名

Oracle默认1个小时产生一次快照，可以查看、修改这个频率，可以手工创建、删除快照。

完了之后，你把那个文件拷贝到本地用IE打开就看到了。里面有很详细的，包括这段时间占CPU,IO,等等最严重的SQL排行。比如“SQL ordered by Elapsed Time”列出耗费时间最长的sql。

==============一些资料=======================

1.查看当前的AWR保存策略、设置：快照间隔、保存时间。

    SQL> col SNAP_INTERVAL format a20
    SQL> col RETENTION format a20
    SQL> select * from dba_hist_wr_control;
          DBID SNAP_INTERVAL        RETENTION            TOPNSQL
    ---------- -------------------- -------------------- ----------
    262089084 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

　　以上结果表示,每小时产生一个SNAPSHOT，保留7天。

2.调整AWR配置，修改快照间隔和保存时间等。
　　AWR配置都是通过dbms_workload_repository包进行配置。

　　2.1 调整AWR产生snapshot的频率和保留策略，
　　如将收集间隔时间改为30 分钟一次。并且保留5天时间（单位都是分钟）：
　　SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
　　设置快照时间间隔为 20 分钟，保留时间为两天 -- 您可以发出以下命令。参数以分钟为单位。
　　SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 20, retention => 2*24*60 );

　　2.2 关闭AWR，把interval设为0则关闭自动捕捉快照
　　SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

　　2.3 手工创建一个快照
　　SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
　　exec dbms_workload_repository.create_snapshot;

　　2.4 查看快照
　　SQL> select * from sys.wrh$_active_session_history
　　SQL> select count(*),max(snap_id) from wrh$_active_session_history;

　　2.5 手工删除指定范围的快照
　　SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);

　　2.6 创建baseline，保存这些数据用于将来分析和比较
　　SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');

　　2.7 删除baseline
　　SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'apply_interest_1', cascade => FALSE);

　　2.8 将AWR数据导出并迁移到其它数据库以便于以后分析
　　SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);

　　2.9 迁移AWR数据文件到其他数据库
　　SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');
　　把AWR数据转移到SYS模式中：
　　SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

　　3.AWR报告日常分析

　　3.1 SQL ordered by Elapsed Time
　　记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait Time)。

　　Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time
　　CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。
　　Executions: SQL语句在监控范围内的执行次数总计。
　　Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。
　　% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。
　　SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。
　　SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。
　　SQL Text: 简单的sql提示，详细的需要点击SQL ID。

　　3.2 SQL ordered by CPU Time:
　　记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。

　　3.3 SQL ordered by Gets:
　　记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets)。

　　3.4 SQL ordered by Reads:
　　记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。

　　3.5 SQL ordered by Executions:
　　记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。

　　3.6 SQL ordered by Parse Calls:
　　记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase)见：http://www.taoyoyo.net/ttt/post/305.html

　　3.7 SQL ordered by Sharable Memory:
　　记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小，单位是byte。

　　3.8 SQL ordered by Version Count:
　　记录了SQL的打开子游标的TOP SQL。

　　3.9 SQL ordered by Cluster Wait Time:
　　记录了集群的等待时间的TOP SQL