> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Discover Insights From System Catalog Tables export const Ocient = "Ocient®"; You can query the {Ocient} System catalog tables to discover important information on system objects such as databases, segments, tables, and indexes. These statistics can be important for monitoring your system to ensure it operates as intended. However, querying these tables can be challenging because generating insights about your system can involve writing complex queries that involve merging, grouping, and filtering multiple system catalog tables. Here, you can find examples of useful queries that you can use as templates for discovering insights about your system. Most of these example queries use the `data_type_coverage` table available with the Ocient Simulator. For details, see [Ocient Simulator](/ocient-simulator). ## Segment System Catalog Tables For specific column definitions of the system catalog tables referenced in these examples, see the [Storage](/system-catalog#storage) section of the System Catalog page. ### Segment Size Check the size of segments and return insights about the segment storage allocation and the number of table rows contained in each segment. ```sql SQL theme={null} SELECT current_timestamp, sg.segment_type, t.name, (COUNT(*)) AS num_segments, (SUM(s.row_count)) AS total_rows, (AVG(s.row_count)) AS avg_rows_per_segment, (DOUBLE(SUM(s.segment_size)) / 1000000000) AS total_size_gb, (MAX(s.segment_size / 1000000)) AS max_size, (MIN(s.segment_size / 1000000)) AS min_size, (AVG(s.segment_size) / 1000000) AS avg_size_mb, (STDEV(DOUBLE(s.segment_size) / 1000000)) AS stdev_size_mb FROM sys.segment_groups AS sg JOIN sys.tables t ON t.id = sg.table_id JOIN sys.segments s ON s.segment_group_id = sg.id GROUP BY sg.segment_type, t.name ORDER BY total_size_gb DESC, total_rows DESC, sg.segment_type, t.name; ``` *Output* ```none Text theme={null} | "current_timestamp()" | "segment_type" | "name" | "num_segments" | "total_rows" | "avg_rows_per_segment" | "total_size_gb" | "max_size" | "min_size" | "avg_size_mb" | "stdev_size_mb" | |-------------------------|----------------|--------------------|----------------|--------------|------------------------|-----------------|------------|------------|--------------------|-------------------| | 2024-07-31 16:23:22.863 | TKT_SEGMENT | data_type_coverage | 3 | 100000 | 33333.333333333336 | 0.189542692 | 113 | 37 | 63.180897333333334 | 43.73338035824336 | ``` For information on the system catalog tables queried in this example, see [sys.segments](/system-catalog#sys-segments) and [sys.tables](/system-catalog#sys-tables). ### Page and Segment Information Check the details for pages and segments in the system. ```sql SQL theme={null} WITH cte AS ( SELECT COALESCE(v.schema, t.schema) AS schema, COALESCE(v.name, t.name) AS name, stored_segments.status, stored_segments.kind, SUM(CASE WHEN segments.segment_type = 'PAGE' THEN 1 ELSE 0 END) AS pagecount, SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN 1 ELSE 0 END) AS segcount, SUM(CASE WHEN segments.segment_type = 'PAGE' THEN segments.row_count ELSE 0 END) AS page_rows, SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN segments.row_count ELSE 0 END) AS seg_rows, MIN(CASE WHEN segments.segment_type = 'PAGE' THEN sg.begin_time ELSE NULL END) AS minpagetime, MAX(CASE WHEN segments.segment_type = 'PAGE' THEN sg.end_time ELSE NULL END) AS maxpagetime, MIN(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.begin_time ELSE NULL END) AS minsegtime, MAX(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.end_time ELSE NULL END) AS maxsegtime FROM sys.segments JOIN sys.stored_segments ON segments.segment_group_id = stored_segments.segment_group_id AND segments.ida_offset = stored_segments.ida_offset JOIN sys.segment_groups sg ON sg.id = segments.segment_group_id JOIN sys.tables AS t ON t.id = sg.table_id LEFT JOIN sys.views AS v ON v.global_dictionary_compression_table_id = t.id WHERE stored_segments.end_osn = 'OSN_INFINITY' GROUP BY 1, 2, stored_segments.status, stored_segments.kind ) SELECT schema, name, status, kind, pagecount, segcount, page_rows, seg_rows, page_rows + seg_rows AS tot_rows, CEILING(page_rows /(pagecount +.000001)) AS rows_per_page, CEILING(seg_rows /(segcount +.000001)) AS rows_per_seg, to_timestamp(minpagetime / 1000000000) AS minpagetime, to_timestamp(maxpagetime / 1000000000) AS maxpagetime, to_timestamp(minsegtime / 1000000000) AS minsegtime, to_timestamp(maxsegtime / 1000000000) AS maxsegtime, NOW() FROM cte ORDER BY schema, name; ``` *Output* ```none Text theme={null} | "schema" | "name" | "status" | "kind" | "pagecount" | "segcount" | "page_rows" | "seg_rows" | "tot_rows" | "rows_per_page" | "rows_per_seg" | "minpagetime" | "maxpagetime" | "minsegtime" | "maxsegtime" | "current_timestamp()" | |----------|--------------------|----------|--------|-------------|------------|-------------|------------|------------|-----------------|----------------|---------------|---------------|-------------------------|-------------------------|-------------------------| | loading | data_type_coverage | INTACT | DISK | 0 | 3 | 0 | 100000 | 100000 | 0.0 | 33334.0 | | | 1970-01-01 00:00:00.000 | 1970-01-01 00:00:00.000 | 2024-08-07 21:41:36.800 | ``` ### Storage Spaces on a System Check all storage spaces on your system. ```sql SQL theme={null} SELECT * FROM sys.storage_spaces; ``` *Output* ```none Text theme={null} | "id" | "name" | "is_system_storage_space" | "block_size" | "total_width" | "parity_width" | "parity_type" | "parity_cycles" | "page_replication" | |----------------------------------------|--------------------|---------------------------|--------------|---------------|----------------|---------------|-----------------|--------------------| | ce9f9c2e-d3fe-4101-a2b2-6f7f2580327b | ss0 | false | 4096 | 3 | 1 | XOR | 1 | 2 | | "7277656e-6465-6c20-7761-732068657265" | systemstoragespace | true | 4096 | 3 | 2 | REPLICATION | 1 | 3 | ``` ## Query Management System Catalog Tables For specific column definitions of the system catalog tables referenced in these examples, see the [Monitoring](/system-catalog#monitoring) section of the System Catalog page. ### Running Queries Check all queries currently running on the system. The output of the `sys.queries` table depends on what database you are logged into and your system role. For details, see [Query Visibility](/data-control-language-dcl-statement-reference#query-visibility). ```sql SQL theme={null} SELECT queries.query_id, queries.initial_priority, queries.effective_priority, queries.user, queries.total_time, queries.status, queries.rows_returned, queries.bytes_returned FROM sys.queries WHERE queries.sql NOT LIKE '%sys.queries%'; SELECT q.query_id, q.user, q.sql, q.status, "database_name", q.bytes_returned FROM sys.queries q; ``` *Output* ```none Text theme={null} | "query_id" | "user" | "sql" | "status" | "database_name" | "bytes_returned" | |----------------------------------------|--------------|------------------|----------|-----------------|------------------| | "66b27dde-17df-403a-82d7-65e34b6fd07f" | admin@system | "example_query" | RUNNING | test | 0 | ``` ### Recent Queries Check the most recent queries completed by the system. The output of the `sys.completed_queries` table depends on what database you are logged into and your system role. For details, see [Query Visibility](/data-control-language-dcl-statement-reference). ```sql SQL theme={null} SELECT user, timestamp_start, total_time, state, reason, sql FROM sys.completed_queries ORDER BY timestamp_start DESC LIMIT 10; ``` *Output* ```none Text theme={null} | "user" | "time_start" | "total_time" | "state" | "reason" | "sql" | | | | | | | | | | | | | | |--------------|---------------------------|--------------|---------|-----------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------|-------------|---------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|------------|---------|------------------------------------|----------|------------|---------|--------------------------------------------------------------------| | admin@system | "2024-08-06T21:51:29.284" | 84 | "00000" | The operation completed successfully | "with the_user_query_to_add_a_limit_to as (select user | time_start | total_time | state | reason | sql from sys.completed_queries order by time_start desc limit 100) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | admin@system | "2024-08-06T21:45:33.952" | 58 | "00000" | The operation completed successfully | "with the_user_query_to_add_a_limit_to as (select q.query_id | q.user | q.sql | q.status | ""database_name"" | q.bytes_returned from sys.queries q) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | admin@system | "2024-08-06T21:41:04.521" | 205 | "00000" | The operation completed successfully | "with the_user_query_to_add_a_limit_to as (select sg.segment_type | ss.kind | ss.status | t.name | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type | ss.kind | ss.status | t.name | active order by num_segments desc | ss.kind | ss.status | t.name | active) select * from the_user_query_to_add_a_limit_to limit 200" | | admin@system | "2024-08-06T21:38:19.740" | 90 | "00000" | The operation completed successfully | with the_user_query_to_add_a_limit_to as (select * from sys.stored_segments) select * from the_user_query_to_add_a_limit_to limit 200 | | | | | | | | | | | | | | | admin@system | "2024-08-06T21:37:35.181" | 5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind | ss.status | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | | | admin@system | "2024-08-06T21:37:33.710" | 5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind | ss.status | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | | | admin@system | "2024-08-06T21:36:03.692" | 11 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind | ss.status | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | | | admin@system | "2024-08-06T21:35:51.301" | 4 | "42703" | The reference to column 't.name' is not valid | "with the_user_query_to_add_a_limit_to as (select ss.kind | ss.status | t.name | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | | admin@system | "2024-08-06T21:35:10.331" | 5 | "42703" | The reference to column 'sg.segment_type' is not valid | "with the_user_query_to_add_a_limit_to as (select sg.segment_type | ss.kind | ss.status | t.name | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" | | | | | | | | | | admin@system | "2024-08-06T21:34:42.970" | 208 | "00000" | The operation completed successfully | "with the_user_query_to_add_a_limit_to as (select sg.segment_type | ss.kind | ss.status | t.name | (ss.end_osn = 'OSN_INFINITY') active | count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type | ss.kind | ss.status | t.name | active order by num_segments desc | ss.kind | ss.status | t.name | active) select * from the_user_query_to_add_a_limit_to limit 200" | ``` ## Database and Table System Catalog Tables For specific column definitions of the system catalog tables used in these examples, see the [Databases](/system-catalog#databases) section of the System Catalog page. ### Database Sizes Check the total size of all databases on your system. ```sql SQL theme={null} SELECT TO_CHAR(SUM(sp.size), '999,999,999,999,999') AS totsize, d.name FROM sys.segment_parts AS sp JOIN sys.segment_groups AS sg ON sp.segment_group_id = sg.id JOIN sys.tables AS t ON sg.table_id = t.id JOIN sys.databases AS d ON t.database_id = d.id GROUP BY d.name ORDER BY 1 DESC; ``` *Output* ```none Text theme={null} | "totsize" | "name" | |-----------|--------| | 204276004 | test | ``` ### Table Sizes Check the total size of all tables on the system. ```sql SQL theme={null} WITH cte AS ( SELECT sg.id AS segment_group_id, sp.name AS segment_name, sp.part_type AS partition_type, sp.size AS segment_size, sg.segment_type, sg.status, t.schema, t.name FROM sys.segment_parts AS sp INNER JOIN sys.segment_groups AS sg ON sp.segment_group_id = sg.id INNER JOIN sys.tables AS t ON sg.table_id = t.id ), ctefinal AS ( SELECT schema, name, SUM(segment_size) AS total_table_size FROM cte GROUP BY schema, name ) SELECT SCHEMA, name, total_table_size, SUM(total_table_size) OVER( ORDER BY total_table_size DESC ) AS running_total FROM ctefinal ORDER BY total_table_size DESC; ``` *Output* ```none Text theme={null} | "schema" | "name" | "total_table_size" | "running_total" | |----------|--------------------|--------------------|-----------------| | loading | data_type_coverage | 204276004 | 204276004 | ``` ### Table Statistics This query provides a breakdown of table statistics and partition information. ```sql SQL theme={null} WITH cte1 AS ( SELECT sg.id AS segment_group_id, sp.name AS segment_name, sp.part_type AS partition_type, sp.size AS segment_size, sg.segment_type, sg.status, t.schema, t.name FROM sys.segment_parts AS sp INNER JOIN sys.segment_groups AS sg ON sp.segment_group_id = sg.id INNER JOIN sys.tables AS t ON sg.table_id = t.id ), cte AS ( SELECT COUNT(*) AS num_segments, SUM(segment_size) AS partition_size, partition_type, SUM(SUM(segment_size)) OVER(PARTITION BY schema, name) AS total_table_size, SUM(COUNT(*)) OVER(PARTITION BY schema, name) AS total_table_segments, schema, name FROM cte1 GROUP BY partition_type, schema, name ) SELECT name, num_segments, partition_size, partition_type, CAST(FLOAT(partition_size)/(total_table_size + 1) * 100 AS DECIMAL(6,3)) AS percent_of_total, total_table_size, total_table_segments FROM cte ORDER BY name, partition_size DESC; ``` *Output* ```none Text theme={null} | "name" | "num_segments" | "partition_size" | "partition_type" | "percent_of_total" | "total_table_size" | "total_table_segments" | |--------------------|----------------|------------------|------------------------|--------------------|--------------------|------------------------| | data_type_coverage | 3 | 113397760 | parity_data | 59.827 | 189542692 | 42 | | data_type_coverage | 3 | 75599872 | data | 39.885 | 189542692 | 42 | | data_type_coverage | 3 | 172032 | skip_lists | 0.091 | 189542692 | 42 | | data_type_coverage | 3 | 172032 | copy_skip_lists | 0.091 | 189542692 | 42 | | data_type_coverage | 3 | 90112 | parity_stats | 0.048 | 189542692 | 42 | | data_type_coverage | 3 | 59210 | stats | 0.031 | 189542692 | 42 | | data_type_coverage | 3 | 12288 | table_of_contents | 0.006 | 189542692 | 42 | | data_type_coverage | 3 | 12288 | parity_index | 0.006 | 189542692 | 42 | | data_type_coverage | 3 | 12288 | copy_table_of_contents | 0.006 | 189542692 | 42 | | data_type_coverage | 3 | 8192 | index | 0.004 | 189542692 | 42 | | data_type_coverage | 3 | 4096 | parity_summary_stats | 0.002 | 189542692 | 42 | | data_type_coverage | 3 | 906 | column_metadata | 0.000 | 189542692 | 42 | | data_type_coverage | 3 | 906 | copy_column_metadata | 0.000 | 189542692 | 42 | | data_type_coverage | 3 | 710 | summary_stats | 0.000 | 189542692 | 42 | ``` For information on the system catalog tables queried in this example, see [sys.segment\_groups](/system-catalog#sys-segment_groups) and [sys.segment\_parts](/system-catalog#sys-segment_parts). ### Compression Check the information on the compression for each column in the specified table. ```sql SQL theme={null} SELECT r."table", c.NAME, r.is_rle_enabled, r.is_ne_enabled, r.raw_size, r.compressed_size, r.is_deltadelta_enabled, r.num_deltadelta_blocks, r.num_rle_blocks, r.num_nerle_blocks, r.num_uncompressed_blocks, r.num_total_blocks FROM sys.columns c, ( SELECT t.name AS "table", t.id, cci.is_deltadelta_enabled, cci.is_rle_enabled, cci.is_ne_enabled, cci.raw_size, cci.compressed_size, cci.num_deltadelta_blocks, cci.num_rle_blocks, cci.num_nerle_blocks, cci.num_uncompressed_blocks, cci.num_total_blocks, cci.ordinal FROM sys.tables AS t, sys.columns_compression_info AS cci WHERE t.id = cci.table_id AND t.name = 'my_table' ) AS r WHERE r.id = c.table_id AND r.ordinal = c.ordinal; ``` *Output* ```none Text theme={null} | "table" | "name" | "is_rle_enabled" | "is_ne_enabled" | "raw_size" | "compressed_size" | "is_deltadelta_enabled" | "num_deltadelta_blocks" | "num_rle_blocks" | "num_nerle_blocks" | "num_uncompressed_blocks" | "num_total_blocks" | |--------------------|---------------|------------------|-----------------|------------|-------------------|-------------------------|-------------------------|------------------|--------------------|---------------------------|--------------------| | data_type_coverage | col_double | true | true | 900000 | 905216 | false | 0 | 0 | 0 | 220 | 220 | | data_type_coverage | col_int | true | true | 500000 | 507904 | true | 0 | 0 | 0 | 123 | 123 | | data_type_coverage | col_tinyint | true | true | 200000 | 204800 | true | 0 | 0 | 0 | 49 | 49 | | data_type_coverage | col_date | true | true | 500000 | 294912 | true | 71 | 0 | 0 | 0 | 71 | | data_type_coverage | col_decimal | true | true | 1100000 | 1105920 | false | 0 | 0 | 0 | 269 | 269 | | data_type_coverage | col_ip | true | true | 1700000 | 1712128 | false | 0 | 0 | 0 | 417 | 417 | | data_type_coverage | col_point | true | true | 1700000 | 1712128 | false | 0 | 0 | 0 | 417 | 417 | | data_type_coverage | col_timestamp | true | true | 900000 | 905216 | true | 0 | 0 | 0 | 220 | 220 | | data_type_coverage | col_bigint | true | true | 900000 | 905216 | true | 0 | 0 | 0 | 220 | 220 | | data_type_coverage | col_binary | true | true | 300000 | 307200 | false | 0 | 0 | 0 | 74 | 74 | | data_type_coverage | col_boolean | true | true | 200000 | 106496 | true | 19 | 0 | 0 | 6 | 25 | | data_type_coverage | col_ipv4 | true | true | 500000 | 507904 | true | 0 | 0 | 0 | 123 | 123 | | data_type_coverage | col_time | true | true | 900000 | 905216 | true | 0 | 0 | 0 | 220 | 220 | | data_type_coverage | col_float | true | true | 500000 | 507904 | false | 0 | 0 | 0 | 123 | 123 | | data_type_coverage | col_uuid | true | true | 1700000 | 1712128 | false | 0 | 0 | 0 | 417 | 417 | | data_type_coverage | col_smallint | true | true | 300000 | 307200 | true | 0 | 0 | 0 | 74 | 74 | ``` For information on the system catalog table queried in this example, see [sys.columns\_compression\_info](/system-catalog#sys-columns_compression_info). ### Compression for the Entire Table This query summarizes the total compression of the specified table. In the example, replace the table name and schema name accordingly. ```sql SQL theme={null} SELECT SUM(raw_size), SUM(compressed_size) FROM ( SELECT t.name, c.name AS colname, co.raw_size, co.compressed_size, SUM(co.compressed_size) OVER(PARTITION BY t.schema, t.name) AS table_size, 100 -((100 * co.compressed_size) / co.raw_size) AS col_compress_percent, DECIMAL( FLOAT(co.compressed_size) * 100 / SUM(co.compressed_size) OVER(PARTITION BY t.schema, t.name), 3, 1 ) AS percent_of_table FROM ( SELECT table_id, ordinal, raw_size, compressed_size FROM sys.columns_compression_info UNION ALL SELECT table_id, ordinal, raw_size, compressed_size FROM sys.vl_columns_compression_info ) AS co JOIN sys.tables AS t ON co.table_id = t.id JOIN sys.columns AS c ON c.table_id = t.id AND co.ordinal = c.ordinal WHERE t.name = 'data_type_coverage' AND t.schema = 'loading' ORDER BY co.ordinal ) AS abc; ``` *Output* ```none Text theme={null} | "sum(raw_size)" | "sum(compressed_size)" | |-----------------|------------------------| | 73788914 | 73592306 | ``` For information on the system catalog table queried in this example, see [sys.columns\_compression\_info](/system-catalog). ### Index Size Check the total size of each index across the different segment groups. In this example, the table has three secondary indexes added before loading data. ```sql SQL theme={null} SELECT p.name AS uuid, p.part_type, i.name AS index_name, SUM(p.size) AS sum_size FROM ( SELECT * FROM sys.segment_parts WHERE part_type LIKE 'index' ) AS p INNER JOIN sys.indexes i ON p.name = i.id GROUP BY 1,2,3 ORDER BY 1,2; ``` *Output* ```none Text theme={null} | "uuid" | "part_type" | "index_name" | "sum_size" | |----------------------------------------|--------------|--------------|------------| | "669cbcdf-772f-4c97-b250-0cc9e573cd7b" | index | idx01 | 2527232 | | cccf6f5e-96a8-4621-b947-f318d120f6a0 | index | idx03 | 180224 | | d75ae71a-207e-4723-a86c-ecffc798c7d0 | index | idx02 | 3190784 | ``` For information on the system catalog tables queried in this example, see [sys.indexes](/system-catalog#sys-indexes) and [sys.segment\_parts](/system-catalog). ### Column Sizes This query finds the size of all columns for the specified table. Before running this query on your system, replace `table_name` in the example. ```sql SQL theme={null} WITH cte AS ( SELECT table_id, ordinal, compressed_size FROM sys.columns_compression_info UNION ALL SELECT table_id, ordinal, compressed_size FROM sys.vl_columns_compression_info ) SELECT ( SELECT t.name FROM sys.tables AS t WHERE t.id = cte.table_id ) AS table_name, ordinal, compressed_size, ( SELECT name FROM sys.columns AS c WHERE c.table_id = cte.table_id AND c.ordinal = cte.ordinal ) AS name, DOUBLE(compressed_size) / SUM(compressed_size) OVER() AS percent_of_total FROM cte WHERE table_id IN ( SELECT id FROM sys.tables WHERE name LIKE 'table_name' ) ORDER BY compressed_size; ``` *Output* ```none Text theme={null} | "table_name" | "ordinal" | "compressed_size" | "name" | "percent_of_total" | |--------------------|-----------|-------------------|----------------|-----------------------| | data_type_coverage | 25 | 8192 | | 0.0001082748327179 | | data_type_coverage | 21 | 8192 | | 0.0001082748327179 | | data_type_coverage | 2 | 102400 | col_boolean | 0.0013534354089736837 | | data_type_coverage | 18 | 204800 | col_tinyint | 0.0027068708179473675 | | data_type_coverage | 19 | 266240 | | 0.003518932063331578 | | data_type_coverage | 4 | 294912 | col_date | 0.0038978939778442096 | | data_type_coverage | 12 | 307200 | col_smallint | 0.004060306226921052 | | data_type_coverage | 1 | 307200 | col_binary | 0.004060306226921052 | | data_type_coverage | 10 | 507904 | col_ipv4 | 0.0067130396285094715 | | data_type_coverage | 7 | 507904 | col_float | 0.0067130396285094715 | | data_type_coverage | 8 | 507904 | col_int | 0.0067130396285094715 | | data_type_coverage | 3 | 699370 | col_char | 0.009243673066151615 | | data_type_coverage | 23 | 783147 | col_varbinary | 0.010350965627260875 | | data_type_coverage | 6 | 905216 | col_double | 0.011964369015327365 | | data_type_coverage | 17 | 905216 | col_timestamp | 0.011964369015327365 | | data_type_coverage | 16 | 905216 | col_time | 0.011964369015327365 | | data_type_coverage | 0 | 905216 | col_bigint | 0.011964369015327365 | | data_type_coverage | 5 | 1105920 | col_decimal | 0.014617102416915785 | | data_type_coverage | 13 | 1712128 | col_point | 0.022629440038039995 | | data_type_coverage | 22 | 1712128 | col_uuid | 0.022629440038039995 | | data_type_coverage | 11 | 1712128 | col_ip | 0.022629440038039995 | | data_type_coverage | 20 | 1784388 | | 0.02358451076706771 | | data_type_coverage | 24 | 1788957 | col_varchar | 0.023644899891907562 | | data_type_coverage | 14 | 8500000 | col_linestring | 0.11234571265894837 | | data_type_coverage | 9 | 21477958 | col_int_array | 0.28387723505517193 | | data_type_coverage | 15 | 27739482 | col_polygon | 0.3666366910682436 | ``` This output is based on a query using the table `data_type_coverage`. ### Column Cardinality This query finds the cardinality of each column in the specified table. Knowing the cardinality is useful for determining which compression scheme to use for a column. ```sql SQL theme={null} SELECT t.schema, t.name, c.name, c.ordinal, cc.cardinality FROM sys.column_cardinalities AS cc LEFT JOIN sys.tables AS t ON cc.table_id = t.id LEFT JOIN sys.columns AS c ON cc.column_id = c.id WHERE t.name LIKE 'data_type_coverage' ORDER BY c.ordinal; ``` *Output* ```none Text theme={null} | "schema" | "name" | "name_1" | "ordinal" | "cardinality" | |----------|--------------------|----------------|-----------|---------------| | loading | data_type_coverage | col_bigint | 0 | 98551 | | loading | data_type_coverage | col_binary | 1 | 48048 | | loading | data_type_coverage | col_boolean | 2 | 2 | | loading | data_type_coverage | col_char | 3 | 95206 | | loading | data_type_coverage | col_date | 4 | 29634 | | loading | data_type_coverage | col_decimal | 5 | 20059 | | loading | data_type_coverage | col_double | 6 | 53546 | | loading | data_type_coverage | col_float | 7 | 75680 | | loading | data_type_coverage | col_int | 8 | 99319 | | loading | data_type_coverage | col_int_array | 9 | 5000855 | | loading | data_type_coverage | col_int_array | 9 | 99899 | | loading | data_type_coverage | col_ipv4 | 10 | 99908 | | loading | data_type_coverage | col_ip | 11 | 99875 | | loading | data_type_coverage | col_smallint | 12 | 51227 | | loading | data_type_coverage | col_point | 13 | 100000 | | loading | data_type_coverage | col_linestring | 14 | 100000 | | loading | data_type_coverage | col_polygon | 15 | 99153 | | loading | data_type_coverage | col_time | 16 | 99902 | | loading | data_type_coverage | col_timestamp | 17 | 99829 | | loading | data_type_coverage | col_tinyint | 18 | 256 | | loading | data_type_coverage | col_uuid | 22 | 99892 | | loading | data_type_coverage | col_varbinary | 23 | 68834 | | loading | data_type_coverage | col_varchar | 24 | 93258 | | loading | data_type_coverage | | | 10410 | | loading | data_type_coverage | | | 2 | | loading | data_type_coverage | | | 1 | | loading | data_type_coverage | | | 93333 | | loading | data_type_coverage | | | 1 | ``` For information on the system catalog table queried in this example, see [sys.column\_cardinalities](/system-catalog#sys-column_cardinalities). ## Cluster and Node System Catalog Tables For specific column definitions of the system catalog tables referenced in these examples, see the [System](/system-catalog#system) section of the System Catalog page. ### Nodes Present on a System Check the status of all nodes in the system. ```sql SQL theme={null} SELECT name, status FROM sys.nodes; ``` *Output* ```none Text theme={null} | "name" | "status" | |-------------|----------| | foundation2 | ACCEPTED | | sql | ACCEPTED | | loader | ACCEPTED | | foundation1 | ACCEPTED | | foundation0 | ACCEPTED | ``` ### Nodes Roles on a System Check the roles of each node in the system. ```sql SQL theme={null} SELECT n.name, n.status, STRING_AGG(DISTINCT c.name, ', ' ORDER BY c.name) in_clusters, STRING_AGG(DISTINCT sr.service_role_type, ', ' ORDER BY sr.service_role_type) with_roles FROM sys.nodes n LEFT JOIN sys.node_clusters nc ON nc.node_id = n.id LEFT JOIN sys.clusters c ON c.id = nc.cluster_id LEFT JOIN sys.service_roles sr ON sr.node_id = n.id GROUP BY n.name, n.status ORDER BY n.name; ``` *Output* ```none Text theme={null} | "name" | "status" | "in_clusters" | "with_roles" | | | | |-------------|----------|---------------------|-------------------------|----------------|-------------|--------------| | foundation0 | ACCEPTED | "foundation_cluster | foundation_cluster-vm" | "health | lts | operatorvm" | | foundation1 | ACCEPTED | "foundation_cluster | foundation_cluster-vm" | "health | lts | operatorvm" | | foundation2 | ACCEPTED | "foundation_cluster | foundation_cluster-vm" | "health | lts | operatorvm" | | loader | ACCEPTED | | "health | streamloader" | | | | sql | ACCEPTED | initial-vm | "admin | health | operatorvm | sql" | ``` ### Node Status Check the operational status, software versions, and assigned roles of your nodes. Possible status values include `ACTIVE`, `STARTING`, `STOPPING`, `ERROR`, `UNKNOWN`, or `UNREACHABLE`. ```sql SQL theme={null} SELECT name, operational_status, software_version, ARRAY_AGG(service_role_type) FROM sys.node_status AS ns LEFT JOIN sys.nodes AS n ON ns.node_id = id LEFT JOIN sys.service_roles AS sr ON sr.node_id = n.id GROUP BY name, operational_status, software_version; ``` *Output* ```none Text theme={null} | name | operational_status | software_version | array_agg(service_role_type:) | |-------------|--------------------|------------------|-------------------------------| | loader | Active | 24.0.0 | ['health','streamloader'] | | foundation2 | Active | 24.0.0 | ['health','lts','operatorvm'] | ``` ### Foundation Nodes on a Specific Cluster Check for all Foundation Nodes on your system clusters. ```sql SQL theme={null} SELECT n.name, r.name FROM sys.nodes AS n, ( SELECT * FROM sys.clusters AS c, sys.node_clusters AS nc WHERE c.cluster_type = 'Foundation' AND c.id = nc.cluster_id ) AS r WHERE r.node_id = n.id; ``` *Output* ```none Text theme={null} | foundation2 | foundation_cluster | |-------------|--------------------| | foundation0 | foundation_cluster | | foundation1 | foundation_cluster | ``` ### Drive Status Check the status of all drives assigned to nodes in the system. ```sql SQL theme={null} SELECT n.name AS node_name, s.node_id, s.id AS serial_number, s.pci_address, s.device_status, s.device_model FROM sys.nodes AS n JOIN sys.storage_device_status AS s ON n.id = s.node_id; ``` *Output* ```none Text theme={null} | "node_name" | "node_id" | "serial_number" | "pci_address" | "device_status" | "device_model" | |-------------|----------------------------------------|----------------------------------------|----------------------------------------------------------|-----------------|----------------| | foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | "72062649-396c-4424-9da5-c90cf0589e00" | /var/opt/ocient/72062649-396c-4424-9da5-c90cf0589e00.dat | "" | NON-NVME DRIVE | | foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | ee2f0013-c7b7-4750-833e-3d717af85001 | /var/opt/ocient/ee2f0013-c7b7-4750-833e-3d717af85001.dat | "" | NON-NVME DRIVE | | loader | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | ff3186fb-502f-417c-bcab-8b7aa3de8800 | /var/opt/ocient/ff3186fb-502f-417c-bcab-8b7aa3de8800.dat | "" | NON-NVME DRIVE | | loader | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | "11ff09e2-a6c1-4f76-ae96-3a97eaf02a01" | /var/opt/ocient/11ff09e2-a6c1-4f76-ae96-3a97eaf02a01.dat | "" | NON-NVME DRIVE | | foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | "6156962f-fcf6-4299-bbd7-2618fc6f1d00" | /var/opt/ocient/6156962f-fcf6-4299-bbd7-2618fc6f1d00.dat | "" | NON-NVME DRIVE | | foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | e6a4b24f-0d49-4704-b7ce-18af163c0701 | /var/opt/ocient/e6a4b24f-0d49-4704-b7ce-18af163c0701.dat | "" | NON-NVME DRIVE | | sql | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "37203a6c-9deb-47cf-8194-95c4eecd7300" | /var/opt/ocient/37203a6c-9deb-47cf-8194-95c4eecd7300.dat | "" | NON-NVME DRIVE | | sql | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "0921d62f-a31c-4681-9a56-82cb1450a401" | /var/opt/ocient/0921d62f-a31c-4681-9a56-82cb1450a401.dat | "" | NON-NVME DRIVE | | foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544 | "842881df-b71c-4cfc-847b-19517e8b2800" | /var/opt/ocient/842881df-b71c-4cfc-847b-19517e8b2800.dat | "" | NON-NVME DRIVE | | foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544 | fc0e490a-a295-491d-b6a6-8b3c153a7301 | /var/opt/ocient/fc0e490a-a295-491d-b6a6-8b3c153a7301.dat | "" | NON-NVME DRIVE | ``` ## Roles, Privileges, and Service Class System Catalog Tables For specific column definitions of the system catalog tables referenced in these examples, see the [User Management](/system-catalog#user-management) section of the System Catalog page. ### Group Assignment for Users Check for all users on the system and their corresponding assigned group. ```sql SQL theme={null} SELECT g.name AS group_name, r.user_name FROM sys.groups AS g, ( SELECT u.user_name, ug.group_id FROM sys.users AS u, sys.user_groups AS ug WHERE u.id = ug.user_id ) AS r ORDER BY g.name ASC; ``` *Output* ```none Text theme={null} | group_name | user_name | |------------|-----------| | DBA | Humphrey | ``` ### User Roles Check for all users on the system and their assigned role. ```sql SQL theme={null} SELECT rl.name AS role_name, r.user_name FROM sys.roles AS rl, ( SELECT u.user_name, ur.role_id FROM sys.users AS u, sys.user_roles AS ur WHERE u.id = ur.user_id ) AS r ORDER BY rl.name ASC; ``` *Output* ```none Text theme={null} | role_name | user_name | |------------------------|-----------| | system administrator | Humphrey | | system analyst | Grimey | | database administrator | Charles | | database analyst | Edna | ``` ### User Privileges Check the privileges for the specific table name. Before running on your system, replace `username` in the example. ```sql SQL theme={null} SELECT t.schema AS db_name, t.name AS table_name, r.privilege FROM sys.tables AS t, ( SELECT object_type, object_id, privilege FROM sys.PRIVILEGES WHERE grantee = 'username' ) AS r WHERE r.object_id = t.id ORDER BY t.schema, t.name ASC; ``` *Output* ```none Text theme={null} | db_name | table_name | privilege | |----------|--------------------|-------------| | loading | data_type_coverage | VIEW | | loading | data_type_coverage | SELECT | ``` ### Privileges for the Specific Table Check the granted privileges across all database objects of the specific type, such as `GROUP`, `TABLE`, or `DATABASE`. In this example, the query checks for `TABLE` privileges. ```sql SQL theme={null} SELECT p.grantee, t.schema, t.name, p.privilege FROM sys.tables AS t, sys.privileges AS p WHERE object_type = 'TABLE' AND p.object_id = t.id; ``` *Output* ```none Text theme={null} | admin@system | admin@system | example_table | DELETE | |--------------|--------------|--------------------|---------| | admin@system | admin@system | example_table | DROP | | admin@system | admin@system | example_table | SYSAUTH | | admin@system | admin@system | example_table | LOAD | | admin@system | admin@system | example_table | INSERT | | admin@system | admin@system | example_table | ALTER | | admin@system | admin@system | example_table | SELECT | | admin@system | admin@system | example_table | VIEW | ``` ### Groups Assigned to Service Classes Check for the service class assignment for all groups. ```sql SQL theme={null} SELECT g.name, sc.name FROM sys.groups AS g, sys.service_classes AS sc WHERE g.service_class_id = sc.id; ``` *Output* ```none Text theme={null} | group | sc_name | |---------|---------------| | analyst | high_priority | | dbadmin | low_priority | ``` ### Service Class Settings Check for the settings for the specified service class. Before running on your system, replace the `high priority` service class name in the example. For details about service class settings, see [Workload Management and Service Classes](/workload-management-and-service-classes). ```sql SQL theme={null} SELECT * FROM sys.groups AS g, sys.service_classes AS sc WHERE g.service_class_id = sc.id AND sc.name = 'high_priority'; ``` *Output* ```none Text theme={null} | id | name | database_id | service_class_id | id_1 | database_id_1 | name_1 | max_temp_disk_usage | max_elapsed_time | max_concurrent_queries | max_rows_returned | scheduling_priority | cache_max_bytes | cache_max_time | max_elapsed_time_for_caching | max_columns_in_result_set | priority_adjustment_factor | priority_adjustment_time | min_priority | max_priority | statement_text | statement_text_matcher_type | half_parallelism | load_balance_shuffle | parallelism | memory_optimal_strategy | |--------------------------------------|---------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|---------------|---------------------|------------------|------------------------|-------------------|---------------------|-----------------|----------------|------------------------------|---------------------------|----------------------------|--------------------------|--------------|--------------|----------------|-----------------------------|------------------|----------------------|-------------|-------------------------| | e9c92a1b-32a8-4f88-ab72-485ff8b24f53 | analyst | e80010d7-6f26-438c-8461-11af309ed8a3 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | e80010d7-6f26-438c-8461-11af309ed8a3 | high_priority | 80 | 100 | 10 | 100 | 5 | 1,000 | 25 | 50 | -1 | 0 | 0 | 0 | -1 | | | | | | false | ``` ### Service Classes for the Specific User Check for the service class of the specific user. Before running on your system, replace the `jmack@test` username in this example with the specific username that you want to query. ```sql SQL theme={null} SELECT r2.name, sc.name, r2.user_name FROM sys.service_classes AS sc, ( SELECT g.name, g.service_class_id, r.user_name FROM sys.groups AS g, ( SELECT u.user_name, ug.group_id FROM sys.users AS u, sys.user_groups AS ug WHERE u.id = ug.user_id AND u.user_name = 'jmack@test' ) AS r ) AS r2; ``` *Output* ```none Text theme={null} | group | service_class | user_name | |---------|-----------------|-------------| | analyst | Default | jmack@test | | analyst | high_priority | jmack@test | ``` ## Data Pipelines ### Pipeline Events While your pipeline is running, the pipeline generates events in the `sys.pipeline_events` system catalog table to mark significant checkpoints when something has occurred. In this example, you execute the `CREATE PIPELINE` and `START PIPELINE` SQL statements on the pipeline named `my_pipeline` and let the pipeline complete. As expected, the `CREATED`, `STARTED`, and `COMPLETED` events appear. For details about the lists of files, see the `sys.pipeline_files` system catalog table. Pipeline events include messages from many different tasks. These events are the background processes that execute across different Loader Nodes during pipeline operation. For details about tasks, you can query the `sys.tasks` and `sys.subtasks` system catalog tables. ```sql SQL theme={null} SELECT * FROM sys.pipeline_events ORDER BY event_timestamp; ``` *Output* ```sql SQL theme={null} +--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+ | pipeline_id | task_id | user_id | event_type | event_message | event_timestamp | |--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------| | d8848467-9262-4bd6-84a7-1380c95f8b8b | | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | CREATED | Created pipeline my_pipeline | 2024-02-07 18:04:32.822690 | | d8848467-9262-4bd6-84a7-1380c95f8b8b | | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | STARTED | Started processing pipeline my_pipeline | 2024-02-07 18:39:52.682276 | | d8848467-9262-4bd6-84a7-1380c95f8b8b | | | FILE_LISTING_STARTED | File listing started for pipeline my_pipeline. This may take a while. | 2024-02-07 18:39:52.848000 | | d8848467-9262-4bd6-84a7-1380c95f8b8b | | | FILE_LISTING_COMPLETED | File listing completed for pipeline my_pipeline. 1 files were listed. | 2024-02-07 18:39:52.863000 | | d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 | | EXTRACTION_STARTED | Extraction started | 2024-02-07 18:39:58.367000 | | d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 | | EXTRACTION_COMPLETED | Extraction completed | 2024-02-07 18:39:59.417000 | | d8848467-9262-4bd6-84a7-1380c95f8b8b | 49c17f79-0108-4b05-aae5-9ae9a13abaca | | COMPLETED | Completed processing pipeline my_pipeline | 2024-02-07 18:40:03.808543 | +--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+ Fetched 7 rows ``` ### Pipeline Metrics Find metrics that measure the performance and activity of a pipeline in the `sys.pipeline_metrics` system catalog table. This table contains one row for each metric at a specified point in time. A common use of this data is to plot metric values over time to examine the behavior of a pipeline. **Metric Types** Metrics are either instantaneous or incremental: * Instantaneous metrics reflect the current value of a counter at the time of the metric collection. These values can increase or decrease based on the current state or rate of the metric. * Incremental metrics increment over time and reflect the cumulative value for a specified counter. **Example** The first snapshot of a metric appears when it is initialized. Afterward, the metric is updated every 10 seconds. The `updated_at` column shows the metric collection time. For example, if you are interested in the number of transformed record bytes, you can execute this query. The value increases over time as the pipeline runs. ```sql SQL theme={null} SELECT * FROM sys.pipeline_metrics WHERE name = 'count.record.bytes.transformed' ORDER BY updated_at; ``` *Output* ```sql SQL theme={null} +--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+ | pipeline_id | extractor_task_id | partition_id | sink_index | name | value | updated_at | |--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------| | 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e | | | count.record.bytes.transformed | 7774214710 | 2024-02-07 22:35:27.546000 | | 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e | | | count.record.bytes.transformed | 16529202619 | 2024-02-07 22:35:37.546000 | | 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e | | | count.record.bytes.transformed | 25077088214 | 2024-02-07 22:35:47.546000 | | 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e | | | count.record.bytes.transformed | 33425410618 | 2024-02-07 22:35:57.546000 | | 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e | | | count.record.bytes.transformed | 41818859671 | 2024-02-07 22:36:07.546000 | +--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+ Fetched 5 rows ``` **Metric Scope** Each metric has a scope that defines its uniqueness. Be careful not to aggregate metrics across different scopes. The scope columns are `pipeline_id`, `extractor_task_id`, `partition_id`, and `sink_index`. If the value of any of these columns is NULL, the scope applies to all values in that dimension. For example, because the `partition_id` and `sink_index` columns are NULL for the metric `count.record.bytes.transformed`, its scope applies to the pipeline indicated by the `pipeline_id` and `extractor_task_id` across all sinks and partitions. An `extractor_task_id` is the internal identifier used when the Ocient System executes a pipeline process on a Loader Node. A pipeline might have many of these processes across time and loaders. **Example** Retrieve the most recent snapshot for each metric and aggregate the values to produce a single value for a pipeline. ```sql SQL theme={null} SELECT pipeline_id, name, SUM(most_recent_value) AS total_value FROM ( SELECT DISTINCT * FROM ( SELECT pipeline_id, extractor_task_id, partition_id, name, first_value (value) OVER (PARTITION BY pipeline_id, extractor_task_id, partition_id, name ORDER BY updated_at DESC) AS most_recent_value FROM sys.pipeline_metrics WHERE name LIKE 'count%' ) ) GROUP BY pipeline_id, name; ``` *Output* ```sql SQL theme={null} +--------------------------------------+--------------------------------+---------------+ | pipeline_id | name | total_value | |--------------------------------------+--------------------------------+---------------| | 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.total | 64 | | 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.bytes.transformed | 33644961 | | 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.processed | 64 | | 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.sent | 100000 | | 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.durable | 100000 | +--------------------------------------+--------------------------------+---------------+ Fetched 5 rows ``` This output contains a single row for each metric for each pipeline. If you run a pipeline with 64 files and 100,000 records, the output might look like this. This table explains each column in the output. ## Related Links [System Catalog](/system-catalog) [Data Integrity and Storage](/data-integrity-and-storage) [Query Analysis](/query-analysis) [Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference) [Cluster and Node Management](/cluster-and-node-management) [Users, Groups, and Service Classes](/users-groups-and-service-classes)