AWR(从ORACLE 10G后预安装到ORACLE中)
自动工作负载资料库(automatic workload repository)
查看当前的AWR保存策略:
col
SNAP_INTERVAL format a20
col RETENTION format a20
SELECT * FROM DBA_HIST_WR_CONTROL;
1、手工创建一个快照:
EXEC
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
2、查看AWR的快照ID
SELECT
SNAP_ID,
TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss')
BEGIN_INTERVAL_TIME,
TO_CHAR(STARTUP_TIME,'yyyy-mm-dd hh24:mi:ss')
STARTUP_TIME
FROM DBA_HIST_SNAPSHOT ORDER BY 1;
3、获取AWR报告:
@?/rdbms/admin/awrrpt
4、如果用 sys 之外的用户创建 AWR
报告则需要授予相应的权限:
CONNECT / AS SYSDBA
GRANT ADVISOR TO SCOTT;
GRANT
SELECT_CATALOG_ROLE TO SCOTT;
GRANT EXECUTE ON sys.dbms_workload_repository
TO SCOTT;
AWR报告内容非常全面,主要包括:WAIT EVENT, SQL, IO, MTTR ADVIRSORY, PGA MEMORY ADVISORY, SHARED POOL ADVISORY, SGA ADVISORY, UNDO, LATCH, SEGMENT。