
Example:
dbadmin=> SELECT *
dbadmin-> FROM (SELECT "HOUR", "Total IO (GB)", "Read IO (GB)", "Write IO (GB)"
dbadmin(> FROM (SELECT MIN(TRUNC(start_time)) start_time,
dbadmin(> DATE_TRUNC('hour', start_time) as "HOUR",
dbadmin(> (SUM(read_kbytes_per_sec) + SUM(written_kbytes_per_sec)) * 60/1024/1024 as "Total IO (GB)",
dbadmin(> SUM(read_kbytes_per_sec) * 60/1024/1024 as "Read IO (GB)",
dbadmin(> SUM(written_kbytes_per_sec) * 60/1024/1024 as "Write IO (GB)"
dbadmin(> FROM v_monitor.io_usage
dbadmin(> GROUP BY 2) foo
dbadmin(> WHERE start_time >= TRUNC(SYSDATE) - 5 -- LAST 5 DAYS!
dbadmin(> LIMIT 1 OVER (PARTITION BY start_time ORDER BY start_time, "Total IO (GB)" DESC)) foo2
dbadmin-> ORDER BY "HOUR" DESC;
HOUR | Total IO (GB) | Read IO (GB) | Write IO (GB)
---------------------+------------------+---------------------+------------------
2019-08-02 10:00:00 | 7.31542510986328 | 1.28464279174805 | 6.03078231811523
2019-08-01 23:00:00 | 13.6195484161377 | 10.5460218429565 | 3.07352657318115
2019-07-31 23:00:00 | 13.6010026931763 | 10.5195688247681 | 3.0814338684082
2019-07-30 12:00:00 | 104.669023132324 | 6.8077428817749 | 97.8612802505493
2019-07-29 14:00:00 | 1.92557315826416 | 0.00724811553955078 | 1.91832504272461
(5 rows)
Helpful Link:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/IO_USAGE.htmHave fun!