ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
CD9C5E45FE03F6AEE05362FB09053404
DBA Container Data
Y
select 'All' value,'Return data from both, the PDB and the CDB' description from v$parameter vp where vp.name='container_data' and vp.value='ALL' union all
select 'Current PDB' value, 'Return data only from the current PDB' description from v$parameter vp where vp.name='container_data' union all
select 'N/A' value,'Not applicable for non CDB databases' from dual where not exists (select null from v$parameter vp where vp.name='container_data')
US
A7DFBD038AC2042AE0530100007F6181
DBA Diagnostic Pack Enabled
Y
select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from v$parameter vp where vp.name='control_management_pack_access' and vp.value like 'DIAGNOSTIC%'
US
4CAC4BF674A105E2E0530100007FBC52Y
select
sys_context('userenv','db_name') database,
dhdi.host_name,
dhdi.instance_name,
case when extract(day from dhwc.retention)>0 then extract(day from dhwc.retention)||'d ' end||
case when extract(hour from dhwc.retention)>0 then extract(hour from dhwc.retention)||'h ' end||
case when extract(minute from dhwc.retention)>0 then extract(minute from dhwc.retention)||'m ' end||
case when extract(second from dhwc.retention)>0 then extract(second from dhwc.retention)||'s' end retention,
case when extract(day from dhwc.snap_interval)>0 then extract(day from dhwc.snap_interval)||'d ' end||
case when extract(hour from dhwc.snap_interval)>0 then extract(hour from dhwc.snap_interval)||'h ' end||
case when extract(minute from dhwc.snap_interval)>0 then extract(minute from dhwc.snap_interval)||'m ' end||
case when extract(second from dhwc.snap_interval)>0 then extract(second from dhwc.snap_interval)||'s' end snap_interval,
dhwc.topnsql,
dhs.count snapshot_count,
(select count(*) from dba_hist_sqlstat dhss where dhdi.dbid=dhss.dbid and dhdi.instance_number=dhss.instance_number) sqlstat_count,
(select count(*) from dba_hist_active_sess_history dhash where (dhash.dbid, dhash.instance_number, dhash.snap_id) in (select dhs.dbid, dhs.instance_number, dhs.snap_id from dba_hist_snapshot dhs where dhdi.dbid=dhs.dbid and dhdi.instance_number=dhs.instance_number)) active_sess_history_count,
(select count(*) from sys.wrh$_active_session_history wash where (wash.dbid, wash.instance_number, wash.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from sys.wrm$_snapshot ws where dhdi.dbid=ws.dbid)) orphan_sess_history_count,
(select count(*) from sys.wrh$_sysmetric_history wsh where (wsh.dbid, wsh.instance_number, wsh.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from sys.wrm$_snapshot ws where dhdi.dbid=ws.dbid)) orphan_sysmetric_history_count,
xxen_util.client_time(dhs.startup_time) startup_time,
xxen_util.client_time(dhs.most_recent_snap_time) most_recent_snap_time,
dhdi.dbid
from
dba_hist_wr_control dhwc,
v$database vd,
(select x.* from (select max(dhdi.startup_time) over (partition by dhdi.dbid, dhdi.instance_number) max_startup_time, dhdi.* from dba_hist_database_instance dhdi) x where x.startup_time=x.max_startup_time) dhdi,
(
select distinct
dhs.dbid,
dhs.instance_number,
count(*) over (partition by dhs.dbid, dhs.instance_number) count,
max(dhs.startup_time) over (partition by dhs.dbid, dhs.instance_number) startup_time,
max(dhs.end_interval_time) over (partition by dhs.dbid, dhs.instance_number) most_recent_snap_time
from
dba_hist_snapshot dhs
) dhs
where
1=1 and
dhwc.dbid=dhdi.dbid(+) and
dhdi.dbid=dhs.dbid(+) and
dhdi.instance_number=dhs.instance_number(+)
order by
dhdi.startup_time desc
US
DBA AWR Settings
Automatic workload repository settings such as retention period, snapshot interval and number of top SQLs to capture (from table underlying the view dba_hist_wr_control).
Note that for executing Blitz Report queries on AWR data, you require a Diagnostic pack license as explained in Oracle's note KB136730:
https://support.oracle.com/support/?kmContentId=1490798
topnsql=DEFAULT means the database captures the top 30 SQLs from 5 different categories (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count) for each snapshot interval.
So the default setting would capture a maximum of 150 different SQLs per snapshot, depending on system load.
Settings are modified by package dbms_workload_repository where interval parameters passed are specified in minutes.
Example: A 35 days retention with snapshot intervals of 30 minutes and 50 top SQLs captured is set as follows:
exec dbms_workload_repository.modify_snapshot_settings (retention=>35*1440, interval=>30, topnsql=>50);
A common problem is that AWR records are not getting purged, see Oracle note 1292724.1.
If column 'Orphan Sess History Count' is bigger than zero, then orphan records not belonging to the current DB's snapshots should get purged either 'manually' table by table:
delete /*+ parallel(x 4) */ from wrh$_active_session_history wash where (wash.dbid, wash.instance_number, wash.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from wrm$_snapshot ws)
alter table wrh$_active_session_history shrink space cascade;
or by a generic script:
begin
for c in (
select x.* from (
select distinct
dt.num_rows,
dt.row_movement,
dtc.table_name,
listagg(dtc.column_name,', ') within group (order by dtc.column_name) over (partition by dtc.table_name) table_columns,
count(*) over (partition by dtc.table_name) column_count
from
dba_tables dt,
dba_tab_columns dtc
where
dt.owner=dtc.owner and
dt.table_name=dtc.table_name and
dtc.owner='SYS' and
dtc.table_name like 'WRH$\_%' escape '\' and
dtc.column_name in ('DBID','INSTANCE_NUMBER','SNAP_ID')
) x
where
x.table_columns in ('DBID, INSTANCE_NUMBER, SNAP_ID','DBID, SNAP_ID')
order by
x.num_rows desc
) loop
if c.table_columns='DBID, INSTANCE_NUMBER, SNAP_ID' then
execute immediate '
delete /*+ parallel(x 4) */
'||c.table_name||' x
where
x.dbid is not null and
x.instance_number is not null and
x.snap_id is not null and
(x.dbid, x.instance_number, x.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from wrm$_snapshot ws)';
dbms_output.put_line(sql%rowcount||' records deleted from '||c.table_name);
elsif c.table_columns='DBID, SNAP_ID' then
execute immediate '
delete /*+ parallel(x 4) */
'||c.table_name||' x
where
x.dbid is not null and
x.snap_id is not null and
(x.dbid, x.snap_id) not in (select ws.dbid, ws.snap_id from wrm$_snapshot ws)';
dbms_output.put_line(sql%rowcount||' records deleted from '||c.table_name);
end if;
if c.row_movement='ENABLED' then
execute immediate 'alter table '||c.table_name||' shrink space cascade';
dbms_output.put_line(c.table_name||' shrinked');
end if;
commit;
end loop;
end;
To purge obsolete data from old DBIDs:
http://www.strategicdbs.com/p/removing-old-dbid-data.html
begin
for c in (select distinct dfus.dbid from dba_feature_usage_statistics dfus where dfus.dbid not in (select vd.dbid from v$database vd)) loop
dbms_swrf_internal.unregister_database(c.dbid);
end loop;
delete wri$_dbu_usage_sample wdus where wdus.dbid not in (select vd.dbid from v$database vd);
delete wri$_dbu_feature_usage wdfu where wdfu.dbid not in (select vd.dbid from v$database vd);
delete wri$_dbu_high_water_mark wdhwm where wdhwm.dbid not in (select vd.dbid from v$database vd);
delete wri$_dbu_cpu_usage wdcu where wdcu.dbid not in (select vd.dbid from v$database vd);
delete wri$_dbu_cpu_usage_sample wdcus where wdcus.dbid not in (select vd.dbid from v$database vd);
commit;
end;
ZHS
DBA AWR Settings
自动工作负载存储库设置，如保留期、快照间隔和要捕获的顶级SQL的数量（来自视图dba_hist_wr_control的基础表）。
请注意，如果要在 AWR 数据上执行 Blitz Report 查询，您需要诊断包许可证，如 Oracle 的注释 1490798.1 所述。
https://support.oracle.com/support/?kmContentId=1490798.1
topnsql=DEFAULT是指数据库在每个快照时间间隔内从5个不同的类别（耗时、CPU时间、解析调用、可共享内存、版本计数）中捕获前30个SQL。
所以默认设置下，根据系统负载情况，每个快照最多可以捕获150个不同的SQL。
设置由包dbms_workload_repository修改，其中传递的间隔参数以分钟为单位指定。
举个例子。保留35天，快照间隔为30分钟，捕获50个顶级SQL，设置如下。
exec dbms_workload_repository.modify_snapshot_settings (retention=>35*1440, interval=>30, topnsql=>50)。
一个常见的问题是AWR记录没有被清除，参见Oracle注释1292724.1。
如果列'Orphan Sess History Count'大于零，那么不属于当前DB的快照的孤儿记录应该被 "手动 "逐表清除。
删除 /*+ parallel(x 4) */ from wrh$_active_session_history wash where (wash.dbid, wash.instance_number, wash.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from wrm$_snapshot ws)
修改表wrh$_active_session_history收缩空间级联。
或由通用脚本。
开始
对于c在(
选择x.*从(
挑明
dt.num_rows。
dt.row_movement。
dtc.table_name。
listagg(dtc.column_name,', ') within group (order by dtc.column_name) over (partition by dtc.table_name) table_columns,
count(*) over (partition by dtc.table_name) column_count
从
dba_tables dt,
dba_tab_columns dtc
哪儿
dt.owner=dtc.owner and
dt.table_name=dtc.table_name和
dtc.owner='SYS'且
dtc.table_name like 'WRH$/%' escape '\' and
dtc.column_name in ('DBID','INSTANCE_NUMBER','SNAP_ID')
) x
哪儿
x.table_columns in ('DBID, INSTANCE_NUMBER, SNAP_ID', 'DBID, SNAP_ID')
按顺序
x.num_rows desc
) 循环
如果c.table_columns='DBID, INSTANCE_NUMBER, SNAP_ID'那么
立即执行'
删除 /*+ parallel(x 4) */
'||c.table_name||' x
哪儿
x.dbid不为空，并且
x.instance_number不为空，并且
x.snap_id不为空且
(x.dbid, x.instance_number, x.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from wrm$_snapshot ws)'。
dbms_output.put_line(sql%rowcount||' records deleted from '||c.table_name);
elsif c.table_columns='DBID, SNAP_ID' then
立即执行'
删除 /*+ parallel(x 4) */
'||c.table_name||' x
哪儿
x.dbid不为空，并且
x.snap_id不为空且
(x.dbid, x.snap_id) not in (select ws.dbid, ws.snap_id from wrm$_snapshot ws)';
dbms_output.put_line(sql%rowcount||' records deleted from '||c.table_name);
end if;
如果c.row_movement='ENABLED'，那么
立即执行'改变表'||c.table_name||'收缩空间级联'。
dbms_output.put_line(c.table_name||'shrinked')。
end if;
提交。
结束循环。
结束。
要清除旧DBID中的过时数据。
http://www.strategicdbs.com/p/removing-old-dbid-data.html
开始
for c in (select distinct dfus.dbid from dba_feature_usage_statistics dfus where dfus.dbid not in (select vd.dbid from v$database vd)) loop
dbms_swrf_internal.unregister_database(c.dbid)。
结束循环。
删除 wri$_dbu_usage_sample wdus where wdus.dbid not in (select vd.dbid from v$database vd);
删除 wri$_dbu_feature_usage wdfu 其中 wdfu.dbid 不在 (select vd.dbid from v$database vd)。
删除 wri$_dbu_high_water_mark wdhwm，其中 wdhwm.dbid 不在 (select vd.dbid from v$database vd)。
删除 wri$_dbu_cpu_usage wdcu 其中 wdcu.dbid 不在 (select vd.dbid from v$database vd)。
删除 wri$_dbu_cpu_usage_sample wdcus 其中 wdcus.dbid 不在 (select vd.dbid from v$database vd)。
提交。
结束。
Enginatics
1=1
1
200
1=1
1=1
LOV
DBA Diagnostic Pack Enabled
A7DFBD038AC2042AE0530100007F6181
Y
select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from v$parameter vp where vp.name='control_management_pack_access' and vp.value like 'DIAGNOSTIC%'
Y
Y
F
Pack de diagnostic activé
US
Diagnostic Pack enabled
2
210
1=1
dhwc.dbid=vd.dbid
LOV
DBA Container Data
CD9C5E45FE03F6AEE05362FB09053404
Y
select 'All' value,'Return data from both, the PDB and the CDB' description from v$parameter vp where vp.name='container_data' and vp.value='ALL' union all
select 'Current PDB' value, 'Return data only from the current PDB' description from v$parameter vp where vp.name='container_data' union all
select 'N/A' value,'Not applicable for non CDB databases' from dual where not exists (select null from v$parameter vp where vp.name='container_data')
%
coalesce((select 'Current PDB' from v$parameter vp where vp.name='awr_pdb_autoflush_enabled' and vp.value='TRUE'),(select 'All' from v$parameter vp where vp.name='container_data'),'N/A')
Y
F
Données sur les conteneurs
US
Container Data
3
1=1
dhwc.dbid=vd.con_dbid
Current PDB
F
Données sur les conteneurs
US
Container Data