AWR 功能笔记

================================addm

配置awr report
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
                 interval => 30, topnsql => 100);
END;
/

select * from DBA_HIST_WR_CONTROL ;

手工创建snapshot
--Creating Snapshots
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

生成报表
@?/rdbms/admin/awrrpt.sql

创建addm任务

使用addmrpt.sql
@?/rdbms/admin/addmrpt.sql

--set parameter
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
                     'ADDM', 'DBIO_EXPECTED', 8000);

使用dbms_advisor
CREATE OR REPLACE FUNCTION run_addm(start_time IN DATE, end_time IN DATE )
  RETURN VARCHAR2
IS
  begin_snap          NUMBER;
  end_snap            NUMBER;
  tid                 NUMBER;          -- Task ID
  tname               VARCHAR2(30);    -- Task Name
  tdesc               VARCHAR2(256);   -- Task Description
BEGIN
  -- Find the snapshot IDs corresponding to the given input parameters.
  SELECT max(snap_id)INTO begin_snap
    FROM DBA_HIST_SNAPSHOT
   WHERE trunc(end_interval_time, 'MI') <= start_time;
  SELECT min(snap_id) INTO end_snap
    FROM DBA_HIST_SNAPSHOT
   WHERE  end_interval_time >= end_time;
  --
  -- set Task Name (tname) to NULL and let create_task return a
  -- unique name for the task.
  tname := '';
  tdesc := 'run_addm( ' || begin_snap || ', ' || end_snap || ' )';
  --
  -- Create a task, set task parameters and execute it
  DBMS_ADVISOR.CREATE_TASK( 'ADDM', tid, tname, tdesc );
  DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'START_SNAPSHOT', begin_snap );
  DBMS_ADVISOR.SET_TASK_PARAMETER( tname, 'END_SNAPSHOT' , end_snap );
  DBMS_ADVISOR.EXECUTE_TASK( tname );
  RETURN tname;
END;
/

SET PAGESIZE 0 LONG 1000000 LONGCHUNKSIZE 1000;
COLUMN get_clob FORMAT a80;
-- execute run_addm() with 7pm and 9pm as input
VARIABLE task_name VARCHAR2(30);
BEGIN
  :task_name := run_addm( to_date('2010-09-09 10:00:00','yyyy-mm-dd hh24:mi:ss'),
                          to_date('2010-09-09 11:00:00','yyyy-mm-dd hh24:mi:ss') );
END;
/

查看report
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:task_name)
  FROM DBA_ADVISOR_TASKS t
 WHERE t.task_name = :task_name
  AND t.owner = SYS_CONTEXT( 'userenv', 'session_user' );

DBA_ADVISOR_TASKS
DBA_ADVISOR_LOG
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_FINDINGS

ASH report
查看ash report
@?/rdbms/admin/ashrpt.sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27143/viewspace-673242/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-673242/