Re: Intermediate view between V$EVENTMETRIC and DBA_HIST_SYSTEM_EVENT

Thanks for the kindly reply, Jonathan.

I was aware of V$SYSMETRIC_HISTORY for metrics. Thanks anyway.

I'm pretty sure now that such view, as I have imagined, simply doesn't exists.

By the way here is a little contribution. Below is a small script that I use to get data from DBA_HIST_SYSMETRIC_HISTORY (the history *partner *table for V$SYSMETRIC_HISTORY)

*_at_save_sqlplus_settings*
>
>
> *-- metric_pivot.sql**-- Usage: _at_metric_pivot METRIC_NAME*
>
> *set numwidth 6*
>
> *col "'00'" heading 00h format 990D99 justify right*
> *col "'01'" heading 01h like "'00'"*
> *col "'02'" heading 02h like "'00'"*
> *col "'03'" heading 03h like "'00'"*
> *col "'04'" heading 04h like "'00'"*
> *col "'05'" heading 05h like "'00'"*
> *col "'06'" heading 06h like "'00'"*
> *col "'07'" heading 07h like "'00'"*
> *col "'08'" heading 08h like "'00'"*
> *col "'09'" heading 09h like "'00'"*
> *col "'10'" heading 10h like "'00'"*
> *col "'11'" heading 11h like "'00'"*
> *col "'12'" heading 12h like "'00'"*
> *col "'13'" heading 13h like "'00'"*
> *col "'14'" heading 14h like "'00'"*
> *col "'15'" heading 15h like "'00'"*
> *col "'16'" heading 16h like "'00'"*
> *col "'17'" heading 17h like "'00'"*
> *col "'18'" heading 18h like "'00'"*
> *col "'19'" heading 19h like "'00'"*
> *col "'20'" heading 20h like "'00'"*
> *col "'21'" heading 21h like "'00'"*
> *col "'22'" heading 22h like "'00'"**col "'23'" heading 23h like "'00'"*
>

> *with snaps as (*
> *select trunc(b.end_interval_time) dia,
> to_char(b.end_interval_time,'HH24') hora, a.value value*
> *from DBA_HIST_SYSMETRIC_HISTORY a, dba_hist_snapshot b, dba_hist_snapshot
> c*
> *where a.dbid=b.dbid*
> *and a.dbid=c.dbid *
> *and a.snap_id=b.snap_id *
> *and a.snap_id-1=c.snap_id*
> *and a.metric_name = '&1'*
> *and b.end_interval_time > sysdate -45*
> *order by b.end_interval_time*
> *)*
> *select **
> *from snaps*
> *pivot (*
> * avg (value)*
> * for hora*
> * in
> ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')*
> *)*
> *order by dia**/*
>
>
> *_at_restore_sqlplus_settings*
>

*--*
*Att*

*Luis Santos*

Em sex, 29 de jun de 2018 às 10:35, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> escreveu:

>
>
> It's not exactly what you want, but v$sysmetric_history (for group_id = 2)
> is a minute by minute summary of about 160 critical metrics which might
> give you what you need.
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Luis Santos <lsantos_at_pobox.com>
> Sent: 29 June 2018 13:18
> To: ORACLE-L
> Subject: Intermediate view between V$EVENTMETRIC and DBA_HIST_SYSTEM_EVENT
>
> Hi Oracle guys!
>
> In a nutshell: V$EVENTMETRIC has the average wait times for the last
> minute. And DBA_HIST_SYSTEM_EVENT has history average time. Not for the
> last minute, but for AWR snap retention (column RETENTION from
> DBA_HIST_WR_CONTROL).
>
> Is there a view that gives historical data for the V$EVENTMETRIC view? At
> least for minutes up to last AWR interval...
>
> In other words, if AWR snap interval is kept in the 1 hour default this
> view would have 60 rows (last 60 minutes). If AWR snapshot interval is
> reduced to, for example, 30 minutes, this view would store 30 rows.
>
> --
> Att
> Luis Santos
>
> [
> https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg
> ]
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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