table of contents
- preface
- discovering empty dba_hist_ tables
- collecting empty dba_hist_ table numbers
- visualizing empty dba_hist_ table numbers
- reference stuff
preface
perhaps anybody else is wondering why there has no "in last 24 hours", "in last 7 days", "in last 31 days" and "custom time period" in my previous two blog notes (the former is "how to visualize active sessions per activity class" in chinese and the latter is "how to visualize active sessions per wait class" in english) have been published on m6米乐安卓版下载-米乐app官网下载 recently. here's a general solution that i often learn something new from experts in the virtual world.
a couple of days ago i respectively posted a same thread about "why dba_hist_waitclassmet_history has 0 line?" on and for solving my confusion from here and here. in the meantime i'm extremely glad to receive awesome replies from several oracle experts (such as, , and ). hence quoted their fantastic response as below (based on the order of their names mentioned previously):
it must be a deliberate coding decision, and there may be a hidden parameter (or call to an internal package) that changes the way oracle access the relevant x$, but if you trace the snapshot code you can see the sql statement that ought to populate the relevant wrh$ from the x$ table, and then see that nothing arrives there.
yeah, i recall empty dba_hist_waitclassmet_history
being confusing. i believe dba_hist_waitclassmet_
history is just for alerting entries when some limit has been breached.
it's been a long time since i've looked at this stuff.
- statistics
- dba_hist_sysmetric_summary – max, min, avg standard deviation
- dba_hist_sysstat (cumulative)
- dba_hist_sysmetric_history (alerts)
- waits
- waitclassmetric_history (alerts)
- dba_hist_system_event (cumulative)
- file io
- dba_hist_filemetric_history (alerts)
- dba_hist_filestatxs (cumulative)
digging into the view definition, you'll see it sources its data from wrh$_waitclassmetric_history.
during an awr capture, wrh$_waitclassmetric_history is populated from x$kewmevmv
if we then dig into v$fixed_view_definition, you can see that this x$ table is the source for v$eventmetric and if we check the docs for that:
"v$eventmetric displays values of wait event metrics for the most recent 60-second interval."
so i'd imagine unless you're running hard *right now*, the rows will most likely be zero.
of course, the following two threads are my response on oracle-l with freelists and asktom separately.
-- i called dbms_metadata.get_ddl() to check the view defined.
set verify off
set long 1000000000
set linesize 200
set pagesize 200
prompt =======================
prompt running on sys schema
prompt =======================
select dbms_metadata.get_ddl(upper('&object_type'), upper('&object_name'), upper('&owner_name')) from dual
/
enter value for object_type: view
enter value for object_name: dba_hist_waitclassmet_history
enter value for owner_name: sys
dbms_metadata.get_ddl(upper('view'),upper('dba_hist_waitclassmet_history'),upper
--------------------------------------------------------------------------------
create or replace force view "sys"."dba_hist_waitclassmet_history" ("snap_id", "dbid", "instance_number", "wait_class
_id", "wait_class", "begin_time", "end_time", "intsize", "group_id", "average_wa
iter_count", "dbtime_in_wait", "time_waited", "wait_count", "time_waited_fg", "w
ait_count_fg") as
select em.snap_id, em.dbid, em.instance_number,
em.wait_class_id, wn.wait_class, begin_time, end_time, intsize,
group_id, average_waiter_count, dbtime_in_wait,
time_waited, wait_count, time_waited_fg, wait_count_fg
from wrm$_snapshot sn, wrh$_waitclassmetric_history em,
(select wait_class_id, wait_class from wrh$_event_name
group by wait_class_id, wait_class) wn
where em.wait_class_id = wn.wait_class_id
and sn.snap_id = em.snap_id
and sn.dbid = em.dbid
and sn.instance_number = em.instance_number
and sn.status = 0
sql> select count(*) from wrh$_waitclassmetric_history;
count(*)
----------
0-- thank you, connor!
set linesize 200
column view_name format a30
column view_definition format a120
select view_name
, view_definition
from v$fixed_view_definition
where view_definition like '%x$kewmevmv%'
;
view_name view_definition
------------------------------ ------------------------------------------------------------------------------------------------------------------------
gv$eventmetric select inst_id, begtime, endtime, intsize_csec, wait#, wait_id, nsess_wait, time_waited, wait_count,
time_waited_fg, wait_count_fg from x$kewmevmv where flag1 = 1 and groupid = 0
gv$waitclassmetric select inst_id, begtime, endtime, intsize_csec, wait#, wait_id, average_waiter_count, dbtime_in_wa
it, time_waited, wait_count, time_waited_fg, wait_count_fg from x$kewmevmv where flag1 =
1 and groupid = 1
gv$waitclassmetric_history select inst_id, begtime, endtime, intsize_csec, wait#, wait_id, average_waiter_count, dbtime_in_wa
it, time_waited, wait_count, time_waited_fg, wait_count_fg from x$kewmevmv where groupid
= 1at this moment i assume that we've known the real reason why dba_hist_waitclassmet_history has 0 line. so we can't stop here and consider finding out how many empty "dba_hist_" tables in oracle database. now we'll use the following two type of approach to exactly discover all of empty dba_hist_ tables in different oracle versions.
discovering empty dba_hist_ tables
using explicit cursor and dynamic sql to discover
set serveroutput on;
declare
v_sql varchar2(200);
tab_num number;
cursor cur_dba_hist is
select table_name
from dict
where table_name like '�a_hist_%'
order by 1;
begin
for v_dba_hist in cur_dba_hist
loop
v_sql := 'select count(*) into tab_num from ' || v_dba_hist.table_name;
execute immediate v_sql;
if tab_num = 0 then
dbms_output.put_line('the lines of ' || v_dba_hist.table_name || ' has been returned 0.');
end if;
end loop;
end;
/
declare
*
error at line 1:
ora-00905: missing keyword
ora-06512: at line 15as you can see from the above line 15 (execute immediate v_sql;) oracle prompts missing keyword, i've got confused and taken a bit of time troubleshooting the weird error but fortunately pointed out the possible syntax error between line 14 and 15 for assignment to v_sql and execute immediate v_sql on my . here's the correct syntax code like this (due to a little longer, so omitted the rest of pl/sql code):
......
v_sql := 'select count(*) from ' || v_dba_hist.table_name;
execute immediate v_sql into tab_num;
......its executing outcome is as follows.
the lines of dba_hist_baseline_template has been returned 0.
the lines of dba_hist_cluster_intercon has been returned 0.
the lines of dba_hist_colored_sql has been returned 0.
the lines of dba_hist_cr_block_server has been returned 0.
the lines of dba_hist_current_block_server has been returned 0.
the lines of dba_hist_dlm_misc has been returned 0.
the lines of dba_hist_dyn_remaster_stats has been returned 0.
the lines of dba_hist_filemetric_history has been returned 0.
the lines of dba_hist_ic_client_stats has been returned 0.
the lines of dba_hist_ic_device_stats has been returned 0.
the lines of dba_hist_inst_cache_transfer has been returned 0.
the lines of dba_hist_interconnect_pings has been returned 0.
the lines of dba_hist_latch_children has been returned 0.
the lines of dba_hist_latch_parent has been returned 0.
the lines of dba_hist_memory_resize_ops has been returned 0.
the lines of dba_hist_memory_target_advice has been returned 0.
the lines of dba_hist_mttr_target_advice has been returned 0.
the lines of dba_hist_persistent_queues has been returned 0.
the lines of dba_hist_persistent_subs has been returned 0.
the lines of dba_hist_sessmetric_history has been returned 0.
the lines of dba_hist_snap_error has been returned 0.
the lines of dba_hist_streams_apply_sum has been returned 0.
the lines of dba_hist_streams_capture has been returned 0.
the lines of dba_hist_waitclassmet_history has been returned 0.
pl/sql procedure successfully completed.it looks like to show an ugly/unformatted output in the prior content. let's slightly adjust pl/sql code snippet. taking a look at the following new section (adding the extra output).
set serveroutput on;
set feedback off;
declare
v_sql varchar2(200);
tab_rows number;
tab_nums number;
cursor cur_dba_hist is
select table_name
from dict
where table_name like '�a_hist_%'
order by 1;
begin
dbms_output.put_line(chr(13));
dbms_output.put_line('all of the "dba_hist_" tables that returned 0 line are as follows:');
dbms_output.put_line(chr(13));
tab_nums := 0;
for v_dba_hist in cur_dba_hist
loop
v_sql := 'select count(*) from ' || v_dba_hist.table_name;
execute immediate v_sql into tab_rows;
if tab_rows = 0 then
tab_nums := tab_nums 1;
dbms_output.put_line(v_dba_hist.table_name);
end if;
end loop;
dbms_output.put_line(chr(13));
dbms_output.put_line('total number of "dba_hist_" tables that returned 0 line is: ' || tab_nums || '.');
dbms_output.put_line(chr(13));
end;
/
all of the "dba_hist_" tables that returned 0 line are as follows:
dba_hist_baseline_template
dba_hist_cluster_intercon
dba_hist_colored_sql
dba_hist_cr_block_server
dba_hist_current_block_server
dba_hist_dlm_misc
dba_hist_dyn_remaster_stats
dba_hist_filemetric_history
dba_hist_ic_client_stats
dba_hist_ic_device_stats
dba_hist_inst_cache_transfer
dba_hist_interconnect_pings
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_memory_resize_ops
dba_hist_memory_target_advice
dba_hist_mttr_target_advice
dba_hist_persistent_queues
dba_hist_persistent_subs
dba_hist_sessmetric_history
dba_hist_snap_error
dba_hist_streams_apply_sum
dba_hist_streams_capture
dba_hist_waitclassmet_history
total number of "dba_hist_" tables that returned 0 line is: 24.incredible! apart from the tidy dba_hist_ table names we also added the friendly prompt message before showing table names as well as outputting total numbers of all of empty dba_hist_ table.
using ref cursor and dynamic sql to discover
it's similar to use explicit cursor and dynamic sql to discover empty dba_hist_ tables we'll use ref cursor in this section. here's my another anonymous pl/sql code blocks and executing result as follows:
set serveroutput on;
declare
v_sql varchar2(200);
v_tablename varchar2(50);
tab_num number;
v_ref_cursor sys_refcursor;
begin
open v_ref_cursor for
select table_name
from dict
where table_name like '�a_hist_%'
order by 1;
loop
fetch v_ref_cursor into v_tablename;
v_sql := 'select count(*) from ' || v_tablename;
execute immediate v_sql into tab_num;
if tab_num = 0 then
dbms_output.put_line('the lines of ' || v_tablename || ' has been returned 0.');
end if;
exit when v_ref_cursor%notfound;
end loop;
close v_ref_cursor;
end;
/
the lines of dba_hist_baseline_template has been returned 0.
the lines of dba_hist_cluster_intercon has been returned 0.
the lines of dba_hist_colored_sql has been returned 0.
the lines of dba_hist_cr_block_server has been returned 0.
the lines of dba_hist_current_block_server has been returned 0.
the lines of dba_hist_dlm_misc has been returned 0.
the lines of dba_hist_dyn_remaster_stats has been returned 0.
the lines of dba_hist_filemetric_history has been returned 0.
the lines of dba_hist_ic_client_stats has been returned 0.
the lines of dba_hist_ic_device_stats has been returned 0.
the lines of dba_hist_inst_cache_transfer has been returned 0.
the lines of dba_hist_interconnect_pings has been returned 0.
the lines of dba_hist_latch_children has been returned 0.
the lines of dba_hist_latch_parent has been returned 0.
the lines of dba_hist_memory_resize_ops has been returned 0.
the lines of dba_hist_memory_target_advice has been returned 0.
the lines of dba_hist_mttr_target_advice has been returned 0.
the lines of dba_hist_persistent_queues has been returned 0.
the lines of dba_hist_persistent_subs has been returned 0.
the lines of dba_hist_sessmetric_history has been returned 0.
the lines of dba_hist_snap_error has been returned 0.
the lines of dba_hist_streams_apply_sum has been returned 0.
the lines of dba_hist_streams_capture has been returned 0.
the lines of dba_hist_waitclassmet_history has been returned 0.
pl/sql procedure successfully completed.next we'll adjust it based on the same idea in the previous section.
set serveroutput on;
set feedback off;
declare
v_sql varchar2(200);
v_tablename varchar2(50);
tab_rows number;
tab_nums number;
v_ref_cursor sys_refcursor;
begin
open v_ref_cursor for
select table_name
from dict
where table_name like '�a_hist_%'
order by 1;
dbms_output.put_line(chr(13));
dbms_output.put_line('all of the "dba_hist_" tables that returned 0 line are as follows:');
dbms_output.put_line(chr(13));
tab_nums := 0;
loop
fetch v_ref_cursor into v_tablename;
v_sql := 'select count(*) from ' || v_tablename;
execute immediate v_sql into tab_rows;
if tab_rows = 0 then
tab_nums := tab_nums 1;
dbms_output.put_line(v_tablename);
end if;
exit when v_ref_cursor%notfound;
end loop;
close v_ref_cursor;
dbms_output.put_line(chr(13));
dbms_output.put_line('total number of "dba_hist_" tables that returned 0 line is: ' || tab_nums || '.');
dbms_output.put_line(chr(13));
end;
/
all of the "dba_hist_" tables that returned 0 line are as follows:
dba_hist_baseline_template
dba_hist_cluster_intercon
dba_hist_colored_sql
dba_hist_cr_block_server
dba_hist_current_block_server
dba_hist_dlm_misc
dba_hist_dyn_remaster_stats
dba_hist_filemetric_history
dba_hist_ic_client_stats
dba_hist_ic_device_stats
dba_hist_inst_cache_transfer
dba_hist_interconnect_pings
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_memory_resize_ops
dba_hist_memory_target_advice
dba_hist_mttr_target_advice
dba_hist_persistent_queues
dba_hist_persistent_subs
dba_hist_sessmetric_history
dba_hist_snap_error
dba_hist_streams_apply_sum
dba_hist_streams_capture
dba_hist_waitclassmet_history
total number of "dba_hist_" tables that returned 0 line is: 24collecting empty dba_hist_ table numbers
sorry, i've forgotten mentioning oracle 11.2.0.4 where i ran pl/sql code in the preceding topic. now let's separately notice how many empty dba_hist_ tables in oracle 11.2.0.4, 19.3.0.0, 19.12.0.0 and 21.3.0.0. the following is output result when executing pl/sql code snippet via the prior topic.
-- running on 11.2.0.4, shown 24 number of "dba_hist_" tables.
all of the "dba_hist_" tables that returned 0 line are as follows:
dba_hist_baseline_template
dba_hist_cluster_intercon
dba_hist_colored_sql
dba_hist_cr_block_server
dba_hist_current_block_server
dba_hist_dlm_misc
dba_hist_dyn_remaster_stats
dba_hist_filemetric_history
dba_hist_ic_client_stats
dba_hist_ic_device_stats
dba_hist_inst_cache_transfer
dba_hist_interconnect_pings
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_memory_resize_ops
dba_hist_memory_target_advice
dba_hist_mttr_target_advice
dba_hist_persistent_queues
dba_hist_persistent_subs
dba_hist_sessmetric_history
dba_hist_snap_error
dba_hist_streams_apply_sum
dba_hist_streams_capture
dba_hist_waitclassmet_history
total number of "dba_hist_" tables that returned 0 line is: 24-- running on 19.3, shown 53 number of "dba_hist_" tables.
all of the "dba_hist_" tables that returned 0 line are as follows:
dba_hist_apply_summary
dba_hist_asm_bad_disk
dba_hist_asm_diskgroup
dba_hist_asm_diskgroup_stat
dba_hist_asm_disk_stat_summary
dba_hist_baseline_template
dba_hist_buffered_queues
dba_hist_buffered_subscribers
dba_hist_capture
dba_hist_cell_config
dba_hist_cell_config_detail
dba_hist_cell_db
dba_hist_cell_disktype
dba_hist_cell_disk_name
dba_hist_cell_disk_summary
dba_hist_cell_global
dba_hist_cell_global_summary
dba_hist_cell_ioreason
dba_hist_cell_name
dba_hist_cell_open_alerts
dba_hist_cluster_intercon
dba_hist_colored_sql
dba_hist_comp_iostat
dba_hist_con_sysmetric_hist
dba_hist_cr_block_server
dba_hist_current_block_server
dba_hist_dlm_misc
dba_hist_dyn_remaster_stats
dba_hist_filemetric_history
dba_hist_filestatxs
dba_hist_ic_client_stats
dba_hist_ic_device_stats
dba_hist_im_seg_stat
dba_hist_inst_cache_transfer
dba_hist_interconnect_pings
dba_hist_java_pool_advice
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_lms_stats
dba_hist_memory_resize_ops
dba_hist_memory_target_advice
dba_hist_mttr_target_advice
dba_hist_recovery_progress
dba_hist_replication_tbl_stats
dba_hist_replication_txn_stats
dba_hist_sessmetric_history
dba_hist_sess_sga_stats
dba_hist_snap_error
dba_hist_streams_apply_sum
dba_hist_streams_capture
dba_hist_tempfile
dba_hist_tempstatxs
dba_hist_waitclassmet_history
total number of "dba_hist_" tables that returned 0 line is: 53.-- running on 19.12, shown 58 number of "dba_hist_" tables.
all of the "dba_hist_" tables that returned 0 line are as follows:
dba_hist_apply_summary
dba_hist_asm_bad_disk
dba_hist_asm_diskgroup
dba_hist_asm_diskgroup_stat
dba_hist_asm_disk_stat_summary
dba_hist_baseline_template
dba_hist_buffered_queues
dba_hist_buffered_subscribers
dba_hist_capture
dba_hist_cell_config
dba_hist_cell_config_detail
dba_hist_cell_db
dba_hist_cell_disktype
dba_hist_cell_disk_name
dba_hist_cell_disk_summary
dba_hist_cell_global
dba_hist_cell_global_summary
dba_hist_cell_ioreason
dba_hist_cell_name
dba_hist_cell_open_alerts
dba_hist_cluster_intercon
dba_hist_colored_sql
dba_hist_comp_iostat
dba_hist_con_sysmetric_hist
dba_hist_cr_block_server
dba_hist_current_block_server
dba_hist_dlm_misc
dba_hist_dyn_remaster_stats
dba_hist_filemetric_history
dba_hist_filestatxs
dba_hist_ic_client_stats
dba_hist_ic_device_stats
dba_hist_im_seg_stat
dba_hist_inst_cache_transfer
dba_hist_interconnect_pings
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_lms_stats
dba_hist_memory_resize_ops
dba_hist_memory_target_advice
dba_hist_mttr_target_advice
dba_hist_persistent_qmn_cache
dba_hist_persistent_queues
dba_hist_persistent_subs
dba_hist_recovery_progress
dba_hist_replication_tbl_stats
dba_hist_replication_txn_stats
dba_hist_rule_set
dba_hist_sessmetric_history
dba_hist_sess_sga_stats
dba_hist_sess_time_stats
dba_hist_snap_error
dba_hist_streams_apply_sum
dba_hist_streams_capture
dba_hist_streams_pool_advice
dba_hist_tempfile
dba_hist_tempstatxs
dba_hist_waitclassmet_history
total number of "dba_hist_" tables that returned 0 line is: 58.-- running on 21.3, shown 60 number of "dba_hist_" tables.
all of the "dba_hist_" tables that returned 0 line are as follows:
dba_hist_apply_summary
dba_hist_asm_bad_disk
dba_hist_asm_diskgroup
dba_hist_asm_diskgroup_stat
dba_hist_asm_disk_stat_summary
dba_hist_baseline_template
dba_hist_buffered_queues
dba_hist_buffered_subscribers
dba_hist_capture
dba_hist_cell_config
dba_hist_cell_config_detail
dba_hist_cell_db
dba_hist_cell_disktype
dba_hist_cell_disk_name
dba_hist_cell_disk_summary
dba_hist_cell_global
dba_hist_cell_global_summary
dba_hist_cell_ioreason
dba_hist_cell_name
dba_hist_cell_open_alerts
dba_hist_cluster_intercon
dba_hist_colored_sql
dba_hist_comp_iostat
dba_hist_con_sysmetric_hist
dba_hist_cr_block_server
dba_hist_current_block_server
dba_hist_dlm_misc
dba_hist_dyn_remaster_stats
dba_hist_filemetric_history
dba_hist_filestatxs
dba_hist_ic_client_stats
dba_hist_ic_device_stats
dba_hist_im_seg_stat
dba_hist_inst_cache_transfer
dba_hist_interconnect_pings
dba_hist_java_pool_advice
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_lms_stats
dba_hist_memory_resize_ops
dba_hist_memory_target_advice
dba_hist_mttr_target_advice
dba_hist_persistent_qmn_cache
dba_hist_persistent_queues
dba_hist_persistent_subs
dba_hist_recovery_progress
dba_hist_replication_tbl_stats
dba_hist_replication_txn_stats
dba_hist_rule_set
dba_hist_sessmetric_history
dba_hist_sess_network
dba_hist_sess_sga_stats
dba_hist_sess_time_stats
dba_hist_snap_error
dba_hist_streams_apply_sum
dba_hist_streams_capture
dba_hist_streams_pool_advice
dba_hist_tempfile
dba_hist_tempstatxs
dba_hist_waitclassmet_history
total number of "dba_hist_" tables that returned 0 line is: 60.visualizing empty dba_hist_ table numbers
now we're able to get a statistic table collecting empty dba_hist_ table numbers in different oracle versions, such as:
oracle_versions empty_dba_hist_table_nums
--------------- -------------------------
11.2.0.4 24
19.3.0.0 53
19.12.0.0 58
21.3.0.0 60next we use microsoft office excel to visualize it. here's a relevant screenshot.
that's all of stuff in this blog note about discovering empty dba_hist_ tables in different oracle versions. if you have any good advice or suggestion please leave your message in the bottom of comment area. meanwhile i'll read every message and reply to you asap. really hope to give me some feedback, thanks for reading spending your expensive time!
reference stuff
updated on dec 11, 2021 at night:
- adding an entry about the sql source code (mentioned previously in this blog note) that has been published on my github in the reference stuff;