Re: Why dba_hist_waitclassmet_history has 0 line?

Very Sorry, Jonathan and Kyle 😊.

I'm misunderstanding the apparent difference between Wait Class Metric and ASH. Yes, some views (v$waitclassmetric, v$waitclassmetric_history and dba_hist_waitclassmet_history) for Wait Class Metric has just checked those data in last 1 minute and 1 hour. But ASH is able to check some data in entire retention days (31 days, like my oracle database), such as, in last 1 hour, in last 24 hours, in last 7 days etc.

Meanwhile I've found this blog post (
*http://dboptimizer.com/2011/07/20/wait-event-and-wait-class-metrics-vs-vsystem_event/* <http://dboptimizer.com/2011/07/20/wait-event-and-wait-class-metrics-vs-vsystem_event/>) from Google, hence quoted the key sentence as below:

Remember DBA_HIST_WAITCLASSMETRIC_HISTORY is used for alerts and or
> baselines not everyday stats.

 Thank you so much, Kyle.

Best Regards
Quanwen Zhao

Quanwen Zhao <quanwenzhao_at_gmail.com> 于2021年12月3日周五 17:37写道：

> Sorry, *DBA_HIST_FILEMETRIC_HISTORY* has also been returned *0* line,
> Kyle! 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
>>
>> --------------------------------------------------------------------------------
>>
>>
>> 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
>>
>
> 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_filemetric_history*
>> Enter value for owner_name: sys
>>
>>
>> DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('DBA_HIST_FILEMETRIC_HISTORY'),UPPER('
>>
>> --------------------------------------------------------------------------------
>>
>> CREATE OR REPLACE FORCE VIEW "SYS"."DBA_HIST_FILEMETRIC_HISTORY"
>> ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "FILEID", "CR
>> EATIONTIME", "BEGIN_TIME", "END_TIME", "INTSIZE", "GROUP_ID",
>> "AVGREADTIME", "AV
>> GWRITETIME", "PHYSICALREAD", "PHYSICALWRITE", "PHYBLKREAD",
>> "PHYBLKWRITE") AS
>> select fm.snap_id, fm.dbid, fm.instance_number,
>> fileid, creationtime, begin_time,
>> end_time, intsize, group_id, avgreadtime, avgwritetime,
>> physicalread, physicalwrite, phyblkread, phyblkwrite
>> from wrm$_snapshot sn, *WRH$_FILEMETRIC_HISTORY* fm
>> where sn.snap_id = fm.snap_id
>> and sn.dbid = fm.dbid
>> and sn.instance_number = fm.instance_number
>> and sn.status = 0
>>
>
> SQL> SELECT COUNT(*) FROM WRH$_FILEMETRIC_HISTORY;
>>
>> COUNT(*)
>> ----------
>> 0
>>
>
> Best Regards
> Quanwen Zhao
>
> Quanwen Zhao <quanwenzhao_at_gmail.com> 于2021年12月3日周五 13:50写道：
>
>> Thanks for exploring those views you mentioned previously, Kyle 😊.
>>
>> But they're not empty data for those views marking ALERT by you. Maybe
>> really breached some limit (it doesn't seem like to exceed how much lines).
>>
>> Best Regards
>> Quanwen Zhao
>>
>> 在 2021年12月3日星期五，kyle Hailey <kylelf_at_gmail.com> 写道：
>> >
>> > 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)
>> >
>> > On Sun, Nov 28, 2021 at 12:49 AM Quanwen Zhao <quanwenzhao_at_gmail.com>
>> wrote:
>> >>
>> >> Hello Listeners 😊,
>> >> Recently I've found three important views about "v$waitclassmetric",
>> "v$waitclassmetric_history", and "dba_hist_waitclassmet_history". Here're
>> the corresponding links from oracle online docs:
>> >>
>> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-WAITCLASSMETRIC.html
>> >>
>> >>
>> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-WAITCLASSMETRIC_HISTORY.html#GUID-854BB495-19FC-4EB4-A81C-4D0EEA13B83C
>> >>
>> >>
>> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_WAITCLASSMET_HISTORY.html#GUID-A931DFBF-4023-41F0-A333-E56741DE589D
>> >>
>> >> Quoted the brief introduction for themselves:
>> >>>
>> >>> 9.131 V$WAITCLASSMETRIC
>> >>>
>> >>> V$WAITCLASSMETRIC displays metric values of wait classes for the most
>> recent 60-second interval. A history of the last one hour will be kept in
>> the system.
>> >>
>> >>
>> >>>
>> >>> 9.132 V$WAITCLASSMETRIC_HISTORY
>> >>>
>> >>> V$WAITCLASSMETRIC_HISTORY displays metric values of wait classes for
>> all intervals in the last one hour.
>> >>>
>> >>> The columns for V$WAITCLASSMETRIC_HISTORY are the same as those
>> for V$WAITCLASSMETRIC.
>> >>>
>> >>> 5.84 DBA_HIST_WAITCLASSMET_HISTORY
>> >>>
>> >>> DBA_HIST_WAITCLASSMET_HISTORY displays the history of the wait event
>> class metric data kept by the Workload Repository.
>> >>
>> >> But why dba_hist_waitclassmet_history has 0 line? Here's my query from
>> oracle 21.3 (test db) and found all the same situation from 19.3 and
>> 11.2.0.4:
>> >>>
>> >>> 16:43:23 SYS_at_ORACDB> select count(*) from v$waitclassmetric;
>> >>>
>> >>> COUNT(*)
>> >>> ----------
>> >>> 13
>> >>>
>> >>> 16:43:29 SYS_at_ORACDB> select count(*) from v$waitclassmetric_history;
>> >>>
>> >>> COUNT(*)
>> >>> ----------
>> >>> 793
>> >>>
>> >>> 16:43:33 SYS_at_ORACDB> select count(*) from
>> dba_hist_waitclassmet_history;
>> >>>
>> >>> COUNT(*)
>> >>> ----------
>> >>> 0
>> >>
>> >> Has anybody else encountered it? Oracle hasn't saved the historical
>> wait class metric to view dba_hist_waitclassmet_history or is it a bug?
>> >> Best Regards
>> >> Quanwen Zhao
>
>

--
http://www.freelists.org/webpage/oracle-l