Select a documentation version: <button class="button button-with-icon cloud-select-dropdown__button button-primary" aria-haspopup="true">16.14.0</button>

- [Current (18.4.0)](https://docs.cloud.google.com/alloydb/omni/containers/current/docs/reference/advanced-query-insights-reference.md.txt)
- [18.4.0](https://docs.cloud.google.com/alloydb/omni/containers/18.4.0/docs/reference/advanced-query-insights-reference.md.txt)
- [17.10.0](https://docs.cloud.google.com/alloydb/omni/containers/17.10.0/docs/reference/advanced-query-insights-reference.md.txt)
- [16.14.0](https://docs.cloud.google.com/alloydb/omni/containers/16.14.0/docs/reference/advanced-query-insights-reference.md.txt)
- [15.18.0](https://docs.cloud.google.com/alloydb/omni/containers/15.18.0/docs/reference/advanced-query-insights-reference.md.txt)

<br />


This document describes the configuration parameters, telemetry dimensions, and
observability views and functions for advanced query insights in
AlloyDB Omni.

For more information about enabling and querying advanced query
insights, see [Monitor query performance using advanced query
insights](https://docs.cloud.google.com/alloydb/omni/containers/16.14.0/docs/monitor-query-insights).

## Configuration parameters

To apply parameter changes that don't require a database restart, run
`ALTER SYSTEM SET` followed by `SELECT pg_reload_conf()`. The following table
describes the configuration parameters for advanced query insights:

| Parameter | Type | Default value | Restart required | Description |
|---|---|---|---|---|
| `google_insights_v2.enabled` | `boolean` | `off` | Yes for initial enablement with `shared_preload_libraries`. No for a subsequent reload with `pg_reload_conf()`. | Enables or disables advanced query insights telemetry collection across the instance. |
| `google_insights_v2.qi_max_query_string_length` | `integer` | `10240` | No (reload with `pg_reload_conf()`) | Sets the maximum byte length (`1024` to `102400`) recorded for normalized SQL templates, where literal values are masked as positional parameters such as `$1` and `$2`. |
| `google_insights_v2.qi_preserve_comments` | `boolean` | `off` | No (reload with `pg_reload_conf()`) | Controls whether SQL comments are preserved inside normalized query strings. |
| `google_insights_v2.qi_track_wait_event_types` | `boolean` | `on` | No (reload with `pg_reload_conf()`) | Records high-level `wait_class` bottleneck categories, such as `CPU`, `IO`, `Lock`, `Timeout`, and `IPC`. |
| `google_insights_v2.qi_track_wait_events` | `boolean` | `on` | No (reload with `pg_reload_conf()`) | Records granular PostgreSQL `wait_event` names, such as `DataFileExtend`, `transactionid`, and `WALWrite`. |
| `google_insights_v2.qi_track_client_address` | `boolean` | `off` | No (reload with `pg_reload_conf()`) | Records connecting client IP addresses in query and client address statistics views. |
| `google_insights_v2.qi_track_tag` | `boolean` | `off` | No (reload with `pg_reload_conf()`) | Enables tracking of tags, such as [SQLCommenter](https://google.github.io/sqlcommenter/spec/) tags, in SQL comments. |
| `google_insights_v2.qi_track_active_queries` | `boolean` | `off` | No (reload with `pg_reload_conf()`) | Enables real-time tracking of active queries and open transactions. |

## Observability views and metrics by dimension

The following sections list the observability views and metric columns
available in the `google_insights_v2` schema for each telemetry dimension.

### Queries

The queries dimension tracks execution statistics and normalized SQL text for
queries across the instance. To preserve SQL comments in normalized query text,
set `google_insights_v2.qi_preserve_comments` to `on`.

The following views and columns are available for this dimension:

- Observability views:
  - `observability_service_query_view`
  - `observability_service_query_coarse_1_view`
  - `observability_service_query_coarse_2_view`
- Related columns:
  - `query_id`: hash identifier of the normalized SQL query.
  - `query`: normalized SQL query text.
  - `database`: logical database where the query ran.
  - `username`: database role that executed the query.
  - `total_execution_time`: cumulative execution time in microseconds.
  - `total_wait_time`: cumulative wait time in microseconds.
  - `total_read_time`: cumulative disk read time in microseconds.
  - `total_write_time`: cumulative disk write time in microseconds.
  - `total_rows`: total number of rows returned or affected by the query.
  - `total_shared_blocks_hit`: total number of shared buffer hits.
  - `total_shared_blocks_read`: total number of shared blocks read from disk.
  - `count`: total number of times the query was executed.
  - `recorded_on`: timestamp when the telemetry interval was recorded.

### Wait event types

The wait event types dimension records high-level `wait_class` bottleneck
categories when `google_insights_v2.qi_track_wait_event_types` is set to `on`
(enabled by default).

The following views and columns are available for this dimension:

- Observability views:
  - `observability_service_wait_event_no_query_view`
  - `observability_service_wait_event_stats_by_query_view`
- Related columns:
  - `wait_class`: wait event category, such as `IO`, `Lock`, `LWLock`, `CPU`, or `Internal wait event`.
  - `wait_event`: granular PostgreSQL wait event name within the wait class.
  - `time_spent`: cumulative wait time for the wait event type in microseconds.
  - `count`: total number of times the wait event type occurred.
  - `database`: logical database where the wait event occurred.
  - `username`: database role associated with the wait event.
  - `tag_id`: numeric identifier of the associated SQLCommenter tag set.
  - `tag_string`: raw SQL comment string containing the application tags.
  - `recorded_on`: timestamp when the telemetry interval was recorded.
  - `query_id`: hash identifier of the query (in `observability_service_wait_event_stats_by_query_view`).

### Wait events

The wait events dimension records granular PostgreSQL `wait_event` names when
`google_insights_v2.qi_track_wait_events` is set to `on` (enabled by default).

The following views and columns are available for this dimension:

- Observability views:
  - `observability_service_wait_event_no_query_view`
  - `observability_service_wait_event_stats_by_query_view`
- Related columns:
  - `wait_event`: specific PostgreSQL wait event name, such as `DataFileExtend`, `DataFileRead`, `WALWrite`, or `Internal wait event`.
  - `wait_class`: wait event category that the wait event belongs to.
  - `time_spent`: cumulative wait time for the wait event in microseconds.
  - `count`: total number of times the wait event occurred.
  - `database`: logical database where the wait event occurred.
  - `username`: database role associated with the wait event.
  - `tag_id`: numeric identifier of the associated SQLCommenter tag set.
  - `tag_string`: raw SQL comment string containing the application tags.
  - `recorded_on`: timestamp when the telemetry interval was recorded.
  - `query_id`: hash identifier of the query (in `observability_service_wait_event_stats_by_query_view`).

### Databases

The databases dimension tracks resource consumption and query volume per
logical database when `google_insights_v2.enabled` is set to `on`.

The following views and columns are available for this dimension:

- Observability views:
  - `observability_service_db_load_conn_view`
  - `observability_service_query_view`
  - `observability_service_wait_event_no_query_view`
- Related columns:
  - `database`: logical database name.
  - `username`: database role that executed queries in the database.
  - `total_execution_time`: cumulative query execution time in the database in microseconds.
  - `total_wait_time`: cumulative wait time in the database in microseconds.
  - `count`: total number of query executions in the database.
  - `recorded_on`: timestamp when the telemetry interval was recorded.

### Users and client IP addresses

The users and client IP addresses dimension tracks load by database role
(`username`) by default. To also collect connecting client IP addresses
(`client_address`), you must set `google_insights_v2.qi_track_client_address`
to `on` (`off` by default).

The following views and columns are available for this dimension:

- Observability views:
  - `observability_service_query_view`
  - `observability_service_client_address_stats_by_query_view`
- Related columns:
  - `client_address`: connecting client IP address (when `google_insights_v2.qi_track_client_address` is `on`).
  - `username`: database role name.
  - `database`: logical database name.
  - `query_id`: hash identifier of the normalized SQL query.
  - `time_spent`: cumulative query execution time for the client address in microseconds.
  - `count`: total number of query executions for the user or client IP address.
  - `recorded_on`: timestamp when the telemetry interval was recorded.

### Tags

The tags dimension tracks query execution statistics by application tag
metadata. To collect tag metrics, you must set `google_insights_v2.qi_track_tag`
to `on` (`off` by default). When set to `off`, these views return no rows.

The following views and columns are available for this dimension:

- Observability views:
  - `observability_service_db_load_tag_view`
  - `observability_service_tag_view`
- Related columns:
  - `tag_id`: numeric identifier of the application tag combination.
  - `tag_string`: raw SQL comment string containing the application tags.
  - `application`: application name extracted from SQLCommenter tags.
  - `db_driver`: database driver extracted from SQLCommenter tags.
  - `framework_name`: application framework name extracted from SQLCommenter tags.
  - `framework_controller`: framework controller extracted from SQLCommenter tags.
  - `framework_action`: framework action extracted from SQLCommenter tags.
  - `framework_route`: framework route extracted from SQLCommenter tags.
  - `traceparent`: W3C trace context extracted from SQLCommenter tags.
  - `database`: logical database name where the tagged query ran.
  - `username`: database role that executed the tagged query.
  - `total_execution_time`: cumulative query execution time for the tag combination in microseconds.
  - `count`: total number of query executions for the tag combination.
  - `recorded_on`: timestamp when the telemetry interval was recorded.

### Active queries

The active queries dimension tracks running queries and open transactions in
real time. To query active sessions, you must set
`google_insights_v2.qi_track_active_queries` to `on`. When set to `off`,
querying these views returns an error.

The following views and columns are available for this dimension:

- Observability views:
  - `stat_activity`
  - `stat_activity_v3`
- Related columns:
  - `pid`: backend process ID. To terminate a session, pass the process ID to the PostgreSQL `pg_terminate_backend(pid)` system function.
  - `normalized_query_hash`: hash identifier of the normalized SQL query.
  - `normalized_query`: normalized SQL text of the active query.
  - `state`: current backend state, such as `active` or `idle in transaction`.
  - `backend_start`: timestamp when the current session started. To calculate session duration in seconds, compute `EXTRACT(EPOCH FROM (clock_timestamp() - backend_start))`.
  - `xact_start`: timestamp when the current transaction started. To calculate transaction duration in seconds, compute `EXTRACT(EPOCH FROM (clock_timestamp() - xact_start))`.
  - `query_start`: timestamp when the current query started. To calculate query duration in seconds, compute `EXTRACT(EPOCH FROM (clock_timestamp() - query_start))`.
  - `wait_event_type`: wait event category if the backend is waiting.
  - `wait_event`: wait event name if the backend is waiting.
  - `database`: logical database name for the active session.
  - `username`: database role name for the active session.
  - `application_name`: connecting application name.
  - `client_addr`: connecting client IP address.