oracle AWR深入研究分析,如何使用
oracle AWR深入研究分析,如何使用
AWR的前身是statspack,当然现在还在,只不过大家都在使用AWR,因为它方便,简单,直观,形象。
AWR是oracle内置工具,安装oracle时已经自动安装完毕,无需额外安装了。
SELECT * FROM DBA_HIST_WR_CONTROL;
这里主要查看AWR策略的制定信息,多久抓一次快照,保留多长时间。
DECLARE BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings ( interval => 30, retention => 10 * 24 * 60); END;
修改策略信息
如何生成AWR报表信息:
依赖于awrrpt.sql脚本,该脚本的路径是$oracle_home/RDBMS/ADMIN
该脚本的内容如下:
[sql] Rem $Header: awrrpt.sql 24-oct-2003.12:04:53 pbelknap Exp $ Rem Rem awrrpt.sql Rem Rem Copyright (c) 1999, 2003, Oracle Corporation. All rights reserved. Rem Rem NAME Rem awrrpt.sql Rem Rem DESCRIPTION Rem This script defaults the dbid and instance number to that of the Rem current instance connected-to, then calls awrrpti.sql to produce Rem the Workload Repository report. Rem Rem NOTES Rem Run as select_catalog privileges. Rem This report is based on the Statspack report. Rem Rem If you want to use this script in an non-interactive fashion, Rem see the 'customer-customizable report settings' section in Rem awrrpti.sql Rem Rem MODIFIED (MM/DD/YY) Rem pbelknap 10/24/03 - swrfrpt to awrrpt Rem pbelknap 10/14/03 - moving params to rpti Rem pbelknap 10/02/03 - adding non-interactive mode cmnts Rem mlfeng 09/10/03 - heading on Rem aime 04/25/03 - aime_going_to_main Rem mlfeng 01/27/03 - mlfeng_swrf_reporting Rem mlfeng 01/13/03 - Update comments Rem mlfeng 07/08/02 - swrf flushing Rem mlfeng 06/12/02 - Created Rem -- -- Get the current database/instance information - this will be used -- later in the report along with bid, eid to lookup snapshots set echo off heading on underline on; column inst_num heading "Inst Num" new_value inst_num format 99999; column inst_name heading "Instance" new_value inst_name format a12; column db_name heading "DB Name" new_value db_name format a12; column dbid heading "DB Id" new_value dbid format 9999999999 just c; prompt prompt Current Instance prompt ~~~~~~~~~~~~~~~~ select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i; @@awrrpti undefine num_days; undefine report_type; undefine report_name; undefine begin_snap; undefine end_snap; -- -- End of file
那么我们就通过调用该脚本产生报表信息:
后面就是基本简单操作。
关键产生报表后如何分析呢?
1、观察CACHE SIZE,观察主机内存情况,判断SGA的内存分批额是否合理
2、观察load profile的事务情况,判断系统繁忙程度
3、观察load profile的hard parses和Parses值,观察是否存在过多硬解析,从而定位是否存在绑定变量的问题
4、观察top 5 timed events,查看系统的瓶颈所在,一般而言,CPU TIME排名第一并且占用了绝大部分的时间比例,说明系统没有什么瓶颈
5、关注如下6个ordered by的sql语句,
elapsed_time,cpu time,gets,reads,executions,parse calls