View SQL Monitoring Information

DBCC provides monitoring information for in-progress and history SQL statements in the database. On the Query Monitor and Query History pages, you can see the execution status and details of each SQL statement, and the status of each database session.

Note

DBCC currently does not support displaying information of the Prepare statements.

Access the page

To access the Query Monitor page, you need to:

Access the DBCC dashboard in your browser via http://<cluster_node_IP>:8080/.
Click Query Monitor in the left navigation menu to enter the page.

View SQL execution status

DBCC provides real-time monitoring for SQL queries and allows you to look up historical SQL information.

On the Query Status tab of the Query Monitor page, you can view queries that are Running, Cancelling, and Unknown.
On the Query Monitor page, you can cancel queries that are in progress.
The Query History page lists all queries that have exceeded a specific execution time. You can click a SQL query to view its details.

View historical SQL information

To view SQL statements that have completed execution, go to the Query History page.

In the search area, you can filter queries by the following conditions:

Query ID: Enter the full query ID for a precise search.
User: Select one or more users from the drop-down list.
Database: Select one or more databases from the drop-down list.
Submitted Time: Select a time range from the drop-down list, such as “1 Hour”, “4 Hours”, “1 Day”, or “Custom”.
Blocking Time: Enter a minimum and maximum blocking time range (in seconds) to filter queries that were queued or blocked for a specific duration.
Execution Time: Enter a minimum and maximum execution time range (in seconds) to filter queries by how long they took to execute.

Once you have set the filter conditions, click Query, and a list of matching SQL query history will be displayed below. You can click a query ID in the list to view its execution details.

View in-progress SQL

To check the execution status of an ongoing SQL statement, enter the Query Monitor page and click the Query Status section.

Note

You can pause or resume monitoring of in-progress query status by using the following commands:

psql gpperfmon -- Connects to the "gpperfmon" data warehouse.
select query_state_pause(); -- Pauses monitoring of ongoing queries.
select query_state_resume(); -- Resumes monitoring of ongoing queries.

In the search area, you can filter by the execution status of the SQL statement, user, and database. Click Query to search. The User filter supports multiple selections.

The options in the Status dropdown are described as follows:

Option name	Description
Running	SQL statement is executing.
Cancelling	SQL statement is being cancelled.
Unknown	SQL statement execution status is unknown.

After clicking Query, a list of SQL statements will be displayed in the area below.

Tip

This panel section dynamically updates the query status and related information. Specific details of the query, such as its status and execution time, are automatically updated based on the actual situation.

You should be able to find the target SQL statement in the list. The fields in this list are described as follows:

Field name	Description
Query ID	The unique ID that identifies the SQL statement being executed in the database.
Status	The execution status of the SQL statement.
User	The user executing the SQL statement.
Database	The database where the SQL statement is being executed.
PID	The process ID.
Submitted Time	The time when the SQL statement was submitted.
Queued Time	The waiting time before the SQL statement was executed.
Run Time	The execution time of the SQL statement.
Operation	For a running SQL statement, you can click Cancel Query to cancel its execution.

View SQL Details

To view the details of a specific SQL statement, click on the query ID of that SQL statement to enter the details page.

The details page displays information about the SQL execution, and you can click on different tabs to view the query plan diagram, SQL text, and query plan text. In the query plan diagram section, ongoing queries are dynamic, and the animation effect disappears once the query ends.

You can click on a module to view specific information, which updates automatically as the query progresses.

In the Query Plan tab, you can view the query plan information in text format. The Query Plan (JSON) tab provides the query plan in JSON format.

Note

In the Query History page, you can see SQL queries that took longer than a specific duration to execute. By default, the system records queries that take longer than 20 seconds. You can modify this value by changing the min_query_time parameter in the gpperfmon.conf configuration file on the Coordinator node. You can print the directory of this configuration file using echo $COORDINATOR_DATA_DIRECTORY.

Cancel SQL execution

To cancel one or more SQL statements, locate the Operation column of the corresponding SQL statement in the SQL list, and then click Cancel Query.

View session status

To view session status in the database, click the Session Status tab on the Query Monitor page.

A list of real-time sessions running in the database is displayed, including session ID, execution status, the user who operates, the database where the session is running, the start time, the application, and idle time.

To view the details of a session, in the search area, fill in the corresponding drop-down option box according to the execution status, user, database, and application name. Then click Query to search. The User box supports multiple selections.

The options in the Status drop-down box are described as follows:

Option name	Description
Active	The backend is running the session.
Idle	The backend is waiting for new client commands.
Idle in transaction (aborted)	The backend is in a transaction, but currently, no query is running.
Fastpath function call	The backend is executing the fast path function.
Disabled	The status is reported when `track_activities` is disabled in the backend.
Unknown	The session status is unknown.

After clicking Query, a list of sessions is displayed in the area below, and you should be able to find the target session from the list.

By default, the session list is sorted by Start Time in descending order. You can click Start Time to sort in ascending order, or sort by Idle Time. The description of the fields in the list is as follows:

Option name	Description
Session ID	Identifies the unique ID of the session being executed in the database.
Status	The status of the session.
User	The user who performs the session operation.
Database	The database where the session is running.
PID	The process ID.
Start Time	The start time of the session.
Application	The client application for executing the session.
Idle time	The idle time of the session.
Operation	For running sessions, you can click Cancel Query to cancel the session.