Re: Why dba_hist_waitclassmet_history has 0 line?

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