Oracle Awr

郑全

   SNAP_ID       DBID INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME
---------- ---------- --------------- ---------- -------------------------------
        97 1160732652               1     238420 06-10月-07 11.50.55.686 上午
        97 1160732652               1     238230 06-10月-07 11.47.45.687 上午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239130 06-10月-07 01.27.04.161 下午
        98 1160732652               1     239130 06-10月-07 01.27.04.161 下午
        98 1160732652               1     239130 06-10月-07 01.27.04.161 下午

已选择21行。

SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_2', cascade=>true);

PL/SQL 过程已成功完成。

SQL> select *from wrh$_active_session_history where snap_id in (92,93,94);
未选定行

SQL>

郑全

Using Oracle AWR

oracle10g awr取得快照

SQL> SQLPLUS / AS SYSDBA
SQL> exec dbms_workload_repository.create_snapshot
SQL> exec:snap_id:=dbms_workload_repository.create_snapshot
SQL> var snap_id number
SQL> print snap_id
SQL> @?/rdbms/admin/awrrpt.sql

郑全

查看当前的AWR保存策略

select * from dba_hist_wr_control;
DBID,SNAP_INTERVAL,RETENTION,TOPNSQL
860524039,+00 01:00:00.000000,+07 00:00:00.000000,DEFAULT

郑全

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

调整AWR配置

AWR配置都是通过dbms_workload_repository包进行配置

1调整AWR产生snapshot的频率和保留策略，如：如将收集间隔时间改为30 分钟一次。并且保留5天时间（注：单位都是为分钟）：

exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

2关闭AWR,把interval设为0则关闭自动捕捉快照

3手工创建一个快照

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

4 查看快照

select * from sys.wrh$_active_session_history

5手工删除指定范围的快照

exec WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);

6创建baseline

exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')

7删除baseline

exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => ' apply_interest_1', cascade => FALSE);

3.生产AWR报告

$ORACLE_HOME/rdbms/admin/awrrpt.sql

郑全

停止AWR的方法

Many Ways to Disable AWR:

Download Meta-Link script dbms_awr.plb, compile this package, then execute the PL/SQL package dbms_awr.disable_awr() [see metalink note 436386.1].
Set your init.ora parameter STATISTICS_LEVEL = BASIC
Execute the Oracle provided PL/SQL package: dbms_workload_repository.modify_snapshot_settings(interval=>0)
Execute the Oracle provided PL/SQL package: dbms_scheduler.disable('GATHER_STATS_JOB')
You can use Toad for #3: Main Menu->Database->Monitor->ADDM/AWR Reports screen, choose the Snapshot Management tab, set the interval to all zeroes, and then press the green checkmark in upper left corner to commit the change.
You can use Toad for #4: Main Menu->Schema Browser, choose the Sched. Job tab and disable the GATHER_STATS_JOB job.
You can use OEM for #4: Main Menu->Workload->Automatic Workload Repository, select the “Edit” button and then select the last radio group item labeled: Turn off Snapshot Collection, finally press OK
You can use OEM for #5: Main Menu->Scheduler->Jobs, select the data grid row for GATHER_STATS_JOB, choose the disable drop-down action, then finally press OK
Create your own database creation scripts (i.e. do not use DBCA) and make sure not to run the CATAWRTB.sql script [Note – Oracle upgrade process may undo this]
Run the CATNOAWR.sql script to drop the AWR Repository tables [Note – Oracle upgrade process may undo this]

郑全

ORACLE 10g/11g重建AWR

 

如果在日常工作中遇见了AWR数据量太大，可以通过如下方式做以清除：

SQL> select distinct dbid,db_name,instance_name from wrm$_database_instance;
DBID DB_NAME   INSTANCE_NAME                                           
---------- --------- ----------------                                 
2691876695 MYDB      mydb      
 
-- 运行如下脚本
SQL> @?\rdbms\admin\catnoawr.sql
 
-- 不重启的话，清空共享池
alter system flush shared_pool;
 
-- 运行重建AWR脚本：
SQL> @?\rdbms\admin\catawrtb.sql
 
-- recompile 
SQL> @?\rdbms\admin\utlrp.sql

ORACLE 11g需要需要运行如下脚本：

SQL> @?\rdbms\admin\execsvrm.sql

测试

SQL> exec dbms_workload_repository.create_snapshot;

如果不对存储过程进行重新编译，执行快照过程会提示如下问题：

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;
 
*
第 1 行出现错误: 
ORA-13516: AWR 操作失败: AWR Schema not initialized
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1 
在重新编译了存储过程后，存储过程运行正常
 
隔5分钟后，再次运行：
SQL> exec dbms_workload_repository.create_snapshot;
 
PL/SQL 过程已成功完成。
SQL> @?/rdbms/admin/awrrpt.sql

注意在RAC环境下的话，需要取消集群参数后，待执行完成后再次修改过来：

alter system set cluster_database = false scope = spfile;

当然在重建后我们需要进一步检查下相关对象是否存在异常，对于存在异常的对象需要重新编译：

spool objects.lst                                                                  
set pagesize500                                                                   
set linesize 100                                                                  
 
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version     
from dba_registry                                                                 
order by comp_name;                                                               
 
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type  
from dba_objects                                                                  
where status='INVALID' order by owner,object_type;                                
 
select owner,object_type,count(*)                                                 
from dba_objects                                                                  
where status='INVALID'                                                            
group by owner,object_type order by owner,object_type ;                           
 
spool off                                                                         
 
alter package <schema name>.<package_name> compile;                               
alter package <schema name>.<package_name> compile body;                          
alter view <schema name>.<view_name> compile;                                     
alter trigger <schema).<trigger_name> compile;