---
title: Discover Insights From System Catalog Tables
slug: fGuC-checking-k
description: This page describes various useful queries used on the system catalog tables to track important metrics such as storage use, node status, running queries, and segment sizes. 
docTags: 
createdAt: Tue Jul 30 2024 21:36:08 GMT+0000 (Coordinated Universal Time)
updatedAt: Wed Apr 22 2026 21:18:18 GMT+0000 (Coordinated Universal Time)
---

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.&#x20;

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.&#x20;

Here, you can find examples of useful queries that you can use as templates for discovering insights about your system.&#x20;

:::hint{type="info"}
Most of these example queries use the `data_type_coverage` table available with the Ocient Simulator. For details, see [Ocient Simulator](<./././Ocient Simulator.md>).&#x20;
:::

## Segment System Catalog Tables

For specific column definitions of the system catalog tables referenced in these examples, see the [Storage](<./System Catalog.md>) section of the System Catalog page.  &#x20;

### 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.&#x20;

```sql
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
|  "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](https://docs.ocient.com/system-catalog#iIIaY) and [sys.tables](https://docs.ocient.com/system-catalog#yQ93k).

### Page and Segment Information

Check the details for pages and segments in the system.&#x20;

```sql
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
| "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.&#x20;

```sql
SELECT * 
FROM sys.storage_spaces;
```

*Output*

```none
|                  "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.md>) section of the System Catalog page.  &#x20;

### Running Queries

Check all queries currently running on the system.&#x20;

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.md>).

```sql
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
|               "query_id"               |    "user"    |      "sql"       | "status" | "database_name" | "bytes_returned" |
|----------------------------------------|--------------|------------------|----------|-----------------|------------------|
| "66b27dde-17df-403a-82d7-65e34b6fd07f" | admin@system |  "example_query" | RUNNING  | test            |                0 |

```

### Recent Queries&#x20;

Check the most recent queries completed by the system.&#x20;

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.md>).

```sql
SELECT user,
    timestamp_start,
    total_time,
    state,
    reason,
    sql
FROM sys.completed_queries
ORDER BY timestamp_start DESC
LIMIT 10;
```

*Output*

```none
|    "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.md>) section of the System Catalog page.  &#x20;

### Database Sizes

Check the total size of all databases on your system.&#x20;

```sql
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
| "totsize" | "name" |
|-----------|--------|
| 204276004 | test   |
```

### Table Sizes

Check the total size of all tables on the system.&#x20;

```sql
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
| "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.&#x20;

```sql
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
|       "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](https://docs.ocient.com/system-catalog#YSjFd) and [sys.segment\_parts](https://docs.ocient.com/system-catalog#X1xtD).

### Compression

Check the information on the compression for each column in the specified table.&#x20;

```sql
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
|      "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](https://docs.ocient.com/system-catalog#EyT3q).

### Compression for the Entire Table&#x20;

This query summarizes the total compression of the specified table. In the example, replace the table name and schema name accordingly.&#x20;

```sql
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
| "sum(raw_size)" | "sum(compressed_size)" |
|-----------------|------------------------|
|        73788914 |               73592306 |   
```

For information on the system catalog table queried in this example, see [sys.columns\_compression\_info](https://docs.ocient.com/system-catalog#EyT3q).

### Index Size

Check the total size of each index across the different segment groups.&#x20;

In this example, the table has three secondary indexes added before loading data.&#x20;

```sql
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
|                 "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](https://docs.ocient.com/system-catalog#EyrdY) and [sys.segment\_parts](https://docs.ocient.com/system-catalog#X1xtD).

### 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.&#x20;

```sql
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
|    "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`.&#x20;

### 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.&#x20;

```sql
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

| "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](https://docs.ocient.com/system-catalog#lEmk1).

## Cluster and Node System Catalog Tables

For specific column definitions of the system catalog tables referenced in these examples, see the [System](https://docs.ocient.com/system-catalog#dzHsR) section of the System Catalog page.  &#x20;

### Nodes Present on a System

Check the status of all nodes in the system.

```sql
SELECT name,
    status
FROM sys.nodes; 
```

*Output*

```none
|   "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
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
|   "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
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
|    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. &#x20;

```sql
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
| foundation2 | foundation_cluster |
|-------------|--------------------|
| foundation0 | foundation_cluster |
| foundation1 | foundation_cluster |
```

### Drive Status

Check the status of all drives assigned to nodes in the system.&#x20;

```sql
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
| "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](https://docs.ocient.com/system-catalog#aYSC6) section of the System Catalog page.  &#x20;

### Group Assignment for Users

Check for all users on the system and their corresponding assigned group.&#x20;

```sql
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
| group_name | user_name |
|------------|-----------|
| DBA        | Humphrey  |
```

### User Roles

Check for all users on the system and their assigned role.&#x20;

```sql
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
|       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.&#x20;

```sql
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
|  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.&#x20;

```sql
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

| 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.&#x20;

```sql
SELECT g.name,
    sc.name
FROM sys.groups AS g,
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id;
```

*Output*

```none
|  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.&#x20;

For details about service class settings, see [Workload Management and Service Classes](<./././Database Administration/Manage Users_ Groups_ and Roles/Workload Management and Service Classes.md>).

```sql
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
| 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.&#x20;

```sql
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
|  group  |  service_class  |  user_name  |
|---------|-----------------|-------------|
| analyst | Default         | jmack@test  |
| analyst | high_priority   | jmack@test  |
```

## Data Pipelines

### Pipeline Events&#x20;

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
SELECT * 
FROM sys.pipeline_events 
ORDER BY event_timestamp;
```

*Output*

```sql
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
| 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
SELECT * 
FROM sys.pipeline_metrics 
WHERE name = 'count.record.bytes.transformed' 
ORDER BY updated_at;
```

*Output*

```sql
+--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+
| 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
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
+--------------------------------------+--------------------------------+---------------+
| 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.md>)&#x20;

[Data Integrity and Storage](<././Data Definition Language _DDL_ Statement Reference/Data Integrity and Storage.md>)&#x20;

[Query Analysis](<././Data Definition Language _DDL_ Statement Reference/Query Analysis.md>)&#x20;

[Data Definition Language (DDL) Statement Reference](<././Data Definition Language _DDL_ Statement Reference.md>)&#x20;

[Cluster and Node Management](<././Data Definition Language _DDL_ Statement Reference/Cluster and Node Management.md>)&#x20;

[Users, Groups, and Service Classes](<././Data Definition Language _DDL_ Statement Reference/Users_ Groups_ and Service Classes.md>)&#x20;



