Список всех снапшотов:
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.
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.
