Monitoring the database

This page includes a collection of general-purpose SQL statements useful for monitoring your database.

This page includes a collection of general-purpose SQL statements useful for monitoring your database.

Check disk space

Check disk space used by tables.

=> SELECT projection_schema, anchor_table_name, to_char(sum(used_bytes)/1024/1024/1024,'999,999.99')
as disk_space_used_gb FROM
GROUP by projection_schema, anchor_table_name ORDER by
disk_space_used_gb desc limit 50;

Check total disk space used.

=> SELECT to_char(sum(used_bytes)/1024/1024/1024,'999,999.99') AS gb FROM projection_storage;

Check the amount of free disk space.

=> SELECT to_char(sum(disk_space_free_mb)/1024,'999,999,999') AS
disk_space_free_gb, to_char(sum(disk_space_used_mb)/1024,'999,999,999') AS
disk_space_used_gb FROM disk_storage;

Adjust data types

Change the Zip and Population columns from VARCHAR to INT.

=> UPDATE cities_flex_keys set data_type_guess='int' WHERE key_name='Zip';
=> UPDATE cities_flex_keys set data_type_guess='int' WHERE key_name='Population';

Refresh the cities_flex_view with the new data types

=> SELECT build_flextable_view('cities_flex');

Materialize the flex table

Materialize the flex table and all columns into a persistent Vertica table.

=> CREATE TABLE cities AS SELECT * from cities_flex_view;

View user and role information

View user information.

=> SELECT user_name, is_super_user, resource_pool, memory_cap_kb, temp_space_cap_kb, run_time_cap FROM users;

Identify users.

=> SELECT * FROM user_sessions;

View queries by user.

=> SELECT * FROM query_profiles WHERE user_name ILIKE '%dbadmin%';

View roles.

=> SELECT * FROM roles;

View database information

View resource pool assignments.

=> SELECT user_name, resource_pool FROM users;

View table information.

=> SELECT table_name, is_flextable, is_temp_table, is_system_table, count(*) FROM tables GROUP by 1,2,3,4;

View projection information.

=> SELECT is_segmented, is_aggregate_projection, has_statistics, is_super_projection, count(*) FROM projections GROUP by 1,2,3,4,5;

View update information.

=> SELECT substr(query, 0, instr(query, '')+1) count(*) FROM (SELECT transaction_id, statement_id, upper(query::varchar(30000)) as query FROM query_profiles
WHERE regexp_like(query,'^\s*update\s','i')) sq GROUP BY 1 ORDER BY 1;

View active events.

=> SELECT * FROM active_events WHERE event_problem_description NOT ILIKE '%state to UP';

View backups.

=> SELECT * FROM database_backups;

View disk storage.

=> SELECT node_name, storage_path, storage_usage, storage_status, disk_space_free_percent FROM disk_storage;

View long-running queries

=> SELECT query_duration_us/1000000/60 AS query_duration_mins, table_name, user_name, processed_row_count AS rows_processed, substr(query,0,70) FROM query_profiles
ORDER BY query_duration_us DESCLIMIT 250;

View sizes and counts of Read Optimized Store (ROS) containers.

=> SELECT node_name, projection_name, sum(ros_count), sum(ros_used_bytes) FROM projection_storage GROUP BY 1,2 HAVING sum(ros_count) >= 50

View license information

View license consumption.


View how the database complies with your license.


Audit the database to check if it complies with raw storage allowance of your license.


Compare storage size of database the database and your license.

=> SELECT /*+(license_utilization)*/
database_size_bytes / (1024^3) AS database_size_gb,
license_size_bytes / (1024^3) AS license_size_gb, usage_percent
FROM v_catalog.license_audits ORDER BYaudit_start_timestamp DESC LIMIT 30;