SQL код:
col STAT_NAME for a20
col VALUE_DIFF for 9999,999,999
col STAT_PER_MIN for 9999,999,999
set lines 200 pages 1500 long 99999999
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
set pagesize 40
set pause on
select hsys.SNAP_ID,
hsnap.BEGIN_INTERVAL_TIME,
hsnap.END_INTERVAL_TIME,
hsys.STAT_NAME,
hsys.VALUE,
hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS "VALUE_DIFF",
round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID)) /
round(abs(extract(hour from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))*60 +
extract(minute from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME)) +
extract(second from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))/60),1)) "STAT_PER_MIN"
from dba_hist_sysstat hsys, dba_hist_snapshot hsnap
where hsys.snap_id = hsnap.snap_id
and hsnap.instance_number in (select instance_number from v$instance)
and hsnap.instance_number = hsys.instance_number
and hsys.STAT_NAME='user commits'
order by 1;
вывод приблизительно такой
Код:
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME STAT_NAME VALUE VALUE_DIFF STAT_PER_MIN
---------- ------------------------------ ------------------------------ -------------------- ---------- ------------- -------------
6626 11-NOV-17 05.00.13.272 PM 11-NOV-17 06.00.29.527 PM user commits 350001525 1,147,017 19,022
6627 11-NOV-17 06.00.29.527 PM 11-NOV-17 07.00.14.759 PM user commits 351130223 1,128,698 18,875
6628 11-NOV-17 07.00.14.759 PM 11-NOV-17 08.00.02.845 PM user commits 351987886 857,663 14,342
6629 11-NOV-17 08.00.02.845 PM 11-NOV-17 09.00.22.109 PM user commits 352829839 841,953 13,963
6630 11-NOV-17 09.00.22.109 PM 11-NOV-17 10.00.07.076 PM user commits 353478483 648,644 10,865
6631 11-NOV-17 10.00.07.076 PM 11-NOV-17 11.00.24.303 PM user commits 353939928 461,445 7,652
6632 11-NOV-17 11.00.24.303 PM 12-NOV-17 12.00.11.904 AM user commits 354335275 395,347 6,611
6633 12-NOV-17 12.00.11.904 AM 12-NOV-17 01.00.29.406 AM user commits 354604745 269,470 4,469
6634 12-NOV-17 01.00.29.406 AM 12-NOV-17 02.00.17.332 AM user commits 354955934 351,189 5,873
6635 12-NOV-17 02.00.17.332 AM 12-NOV-17 03.00.03.228 AM user commits 356918293 1,962,359 32,815
6636 12-NOV-17 03.00.03.228 AM 12-NOV-17 04.00.20.577 AM user commits 357821672 903,379 14,981
6637 12-NOV-17 04.00.20.577 AM 12-NOV-17 05.00.09.204 AM user commits 358154880 333,208 5,572
6638 12-NOV-17 05.00.09.204 AM 12-NOV-17 06.00.25.507 AM user commits 358296694 141,814 2,352
6639 12-NOV-17 06.00.25.507 AM 12-NOV-17 07.00.09.734 AM user commits 358692156 395,462 6,624
6640 12-NOV-17 07.00.09.734 AM 12-NOV-17 08.00.01.047 AM user commits 359373748 681,592 11,379
6641 12-NOV-17 08.00.01.047 AM 12-NOV-17 09.00.17.981 AM user commits 360418586 1,044,838 17,327
6642 12-NOV-17 09.00.17.981 AM 12-NOV-17 10.00.04.542 AM user commits 362476024 2,057,438 34,405
6643 12-NOV-17 10.00.04.542 AM 12-NOV-17 11.00.22.732 AM user commits 364469092 1,993,068 33,053
6644 12-NOV-17 11.00.22.732 AM 12-NOV-17 12.00.09.693 PM user commits 365611444 1,142,352 19,103
6645 12-NOV-17 12.00.09.693 PM 12-NOV-17 01.00.27.672 PM user commits 366866479 1,255,035 20,813
6646 12-NOV-17 01.00.27.672 PM 12-NOV-17 02.00.14.537 PM user commits 368466462 1,599,983 26,756 STAT_PER_MIN - количество коммитов в минуту в течение времени снапшота.
Скрипт не мой, просто попался в записках, боюсь потерять.