Sys View-based Queries and Loads - Amazon Redshift
Connect to the database
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Sys View-based Queries and Loads

This document describes the Sys View-based Queries and Loads page in the Amazon Redshift Console, a tool for analyzing a query's components and performance.

The Queries and loads page has a functionality that displays information provided by SYS views. The console view includes the query profiler, which shows the graphical execution plan of a query. To switch to the SYS-based view, follow the steps here to grant the right access and permissions for the new Queries and loads page.

The SYS-based view feature of the Queries and loads page has the following functionality:

  • Increased security — You need elevated privileges to monitor queries for other users

  • Seven-day query history — Guaranteed access to seven days of query history

  • Query profiler — A graphical tool for monitoring query performance. For more information, see Query profiler

By default, you can only view your own queries. To view queries owned by other users, grant the SYS:MONITOR role to your account. To allow a user to end running queries, grant the user the SYS:OPERATOR privilege.

To grant the privilege to view queries owned by all users to a database user or role, run the following commands:

grant role sys:monitor to "IAMR:role-name";
grant role sys:monitor to "IAMR:user-name";

To automatically assign the sys:monitor role to an IAM user or role for Amazon Redshift Serverless or provisioned, run the following commands:

 create role monitor;
grant role sys:monitor to monitor;

To update the IAM role used for query monitoring, do the following:

  1. Choose the Tags tab.

  2. Choose Manage tags.

  3. Add a tag with key RedshiftDbRoles and value monitor.

  4. Save changes

To add database credentials to a user, run the following command:

grant role sys:monitor to <username>

Connect to the database

Before using the enhanced query monitoring feature, you must first connect to your database for access to SYS view-based information. To connect to the database, use one of the following credentials:

  • Username and password

  • Temporary credentials associated with your IAM role

  • A database user

Note the following about using enhanced query monitoring:

  • For provisioned clusters, you must connect to a database, because enhanced query monitoring uses SYS views. These views have increased security, and require elevated privileges to access data about queries owned by other users.

  • When using the SYS view-based queries and loads page, only your user_id is visible if your user account doesn't have the database superuser role. Usernames might be hidden from non-superusers until the classic query monitoring functionality is removed. In the future, non database super users will also be able to view the user names that ran the queries, if the role has the sys:monitor permission.

  • As part of the sys view-based queries and loads page experience, the query execution process ID (p_id) appears under the column heading session_id.