AWR Report

Список всех снапшотов:

SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1;

DBID:

select dbid from v$database

AWR DBtime script

select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like to_char(e.instance_number)
and stat_name = 'DB time'
)
where begin_snap between 0 and 99999999
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31


Создаются автоматически:

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => XXXX, - Сколько времени хранить (в минутах)
interval => XX); - С каким интервалом создавать (в минутах)
END;
/

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Дней)
interval => 30); -- Minutes
END;
/

STATISTICS_LEVEL = TYPICAL or ALL

Параметры изменяются так:

select snap_interval
, retention
from dba_hist_wr_control
/

exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60) //14 дней

execute dbms_workload_repository.modify_snapshot_settings(interval => 0);
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

Руками создаются так:

BEGIN DBMS_WORKLOAD_REPOSITORY.create_snapshot('ALL'); END;

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
begin
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (7);
end;

begin
DBMS_STATS.PURGE_STATS(to_timestamp_tz('01-09-2007 00:00:00 Europe/Moscow','DD-MM-YYYY HH24:MI:SS TZR'));
end;

Удаляются так:

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/

По умолчанию (TYPICAL)
EXECUTE DBMS_WORKLOAD_REPOSITORY.create_snapshot();

Отчеты формируются так:

SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_text
(Db_Id, Instance_num, Snap_begin, Snap_end)
);

Например так (в формате html):

SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_html
(4138903833,4,90743,90749)
);

План выполнения можно посмотреть так:

select * from TABLE(dbms_xplan.display_awr('8s9npcpzjkzst'));


Отключить сбор снапшотов AWR можно так:
execute dbms_workload_repository.modify_snapshot_settings(interval => 0);

Включить сбор снапшотов AWR с интервалом в 1 час можно так:
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

Установить политику хранения снапшотов можно так:
exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60) //14 дней

Посмотреть текущие значения настроек можно так:
select dbid, snap_interval, retention  from sys.wrm$_wr_control

(собирать каждые 15 мин. и хранить один день):
execute dbms_workload_repository.modify_snapshot_settings(interval => 15);
exec dbms_workload_repository.modify_snapshot_settings (retention => 1*24*60)


Many Ways to Disable AWR:

1. 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].
2. Set your init.ora parameter STATISTICS_LEVEL = BASIC
3. Execute the Oracle provided PL/SQL package: dbms_workload_repository.modify_snapshot_settings(interval=>0)
4. Execute the Oracle provided PL/SQL package: dbms_scheduler.disable('GATHER_STATS_JOB')
5. 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.
6. You can use Toad for #4: Main Menu->Schema Browser, choose the Sched. Job tab and disable the GATHER_STATS_JOB job.
7. 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
8. 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
9. 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]
10. Run the CATNOAWR.sql script to drop the AWR Repository tables [Note – Oracle upgrade process may undo this]


select * from nls_session_parameters
alter session set nls_date_format = 'DD-MM-YYYY';
alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;

 ASH - report

select
output
from
table
(dbms_workload_repository.ash_report_html
(4138903833,1,to_date('11-04-2017 08:10:00','DD-MM-YYYY HH24:MI:SS'), to_date('11-04-2017 09:10:00','DD-MM-YYYY HH24:MI:SS')));

ADDM - report


SET LINESIZE 120

COLUMN begin_interval_time FORMAT A30
COLUMN end_interval_time FORMAT A30
COLUMN startup_time FORMAT A30

SELECT snap_id, begin_interval_time, end_interval_time, startup_time
FROM   dba_hist_snapshot
WHERE  begin_interval_time > TRUNC(SYSTIMESTAMP)
ORDER BY snap_id;

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              STARTUP_TIME
---------- ------------------------------ ------------------------------ ------------------------------
      1770 06-JUL-2015 00:00:54.031       06-JUL-2015 01:00:58.011       03-JUL-2015 11:13:51.000
      1771 06-JUL-2015 01:00:58.011       06-JUL-2015 02:00:02.167       03-JUL-2015 11:13:51.000
      1772 06-JUL-2015 02:00:02.167       06-JUL-2015 03:00:06.332       03-JUL-2015 11:13:51.000
      1773 06-JUL-2015 03:00:06.332       06-JUL-2015 04:00:10.573       03-JUL-2015 11:13:51.000
      1774 06-JUL-2015 04:00:10.573       06-JUL-2015 05:00:14.648       03-JUL-2015 11:13:51.000
      1775 06-JUL-2015 05:00:14.648       06-JUL-2015 06:00:18.690       03-JUL-2015 11:13:51.000
      1776 06-JUL-2015 06:00:18.690       06-JUL-2015 07:00:22.717       03-JUL-2015 11:13:51.000
      1777 06-JUL-2015 07:00:22.717       06-JUL-2015 08:00:27.209       03-JUL-2015 11:13:51.000
      1778 06-JUL-2015 08:00:27.209       06-JUL-2015 09:00:31.694       03-JUL-2015 11:13:51.000
      1779 06-JUL-2015 09:00:31.694       06-JUL-2015 10:00:36.238       03-JUL-2015 11:13:51.000
      1780 06-JUL-2015 10:00:36.238       06-JUL-2015 11:00:40.915       03-JUL-2015 11:13:51.000
      1781 06-JUL-2015 11:00:40.915       06-JUL-2015 12:00:45.594       03-JUL-2015 11:13:51.000
      1782 06-JUL-2015 12:00:45.594       06-JUL-2015 13:00:49.954       03-JUL-2015 11:13:51.000
      1783 06-JUL-2015 13:00:49.954       06-JUL-2015 14:00:54.322       03-JUL-2015 11:13:51.000
      1784 06-JUL-2015 14:00:54.322       06-JUL-2015 15:00:58.984       03-JUL-2015 11:13:51.000
      1785 06-JUL-2015 15:00:58.984       06-JUL-2015 16:00:03.464       03-JUL-2015 11:13:51.000

SQL>


ANALYZE_DB

CONN sys@cdb1 AS SYSDBA

DECLARE
  l_task_name VARCHAR2(30) := '1783_1785_addm_db';
BEGIN
  DBMS_ADDM.analyze_db (
    task_name      => l_task_name,
    begin_snapshot => 1783,
    end_snapshot   => 1785);
END;
/



ANALYZE_INST

CONN sys@cdb1 AS SYSDBA

DECLARE
  l_task_name VARCHAR2(30) := '1783_1785_addm_inst';
BEGIN
  DBMS_ADDM.analyze_inst (
    task_name       => l_task_name,
    begin_snapshot  => 1783,
    end_snapshot    => 1785,
    instance_number => 1);
END;
/


GET_REPORT

SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF

SELECT DBMS_ADDM.get_report('1783_1785_addm_db') FROM dual;


DELETE

BEGIN
  DBMS_ADDM.delete('1783_1785_addm_db');
END;
/



Miscellaneous

    INSERT_FINDING_DIRECTIVE : Limit reporting for a specific finding.
    INSERT_PARAMETER_DIRECTIVE : Prevent ADDM from creating actions to alter a specific parameter.
    INSERT_SEGMENT_DIRECTIVE : Prevent ADDM from creating actions to run the Segment Advisor for specific segments.
    INSERT_SQL_DIRECTIVE : Limit reporting of actions on specific SQL.
    DELETE_FINDING_DIRECTIVE : Delete a finding directive.
    DELETE_PARAMETER_DIRECTIVE : Delete a parameter directive.
    DELETE_SEGMENT_DIRECTIVE : Delete a segment directive.
    DELETE_SQL_DIRECTIVE : Delete an SQL directive.