Managing Historical Sessions

Scenarios

You can query historical sessions, historical wait events, and historical top SQL statements of your DB instance.

Procedure

1. Log in to the RDS console.
2. Click in the upper left corner and select a region.
3. On the Instances page, click the target instance name to go to the Summary page.
4. In the navigation pane, choose Sessions under DBA Assistant.
5. On the Historical Session Analysis tab page, select any time range within the last seven days and perform the following operations:
   • Viewing historical sessions

     In the session statistics area, you can filter sessions by database name, session ID, or username.

     Above the session list, you can click Export to export sessions on the current page. You can also view all export records.

     Figure 1 Historical Sessions
     
     Restrictions on historical session queries are as follows:

     • If there are only active sessions at a given collection time, a maximum of 50 sessions can be displayed.
     • If there are only sessions in idle in transaction state at a given collection time, a maximum of 50 sessions can be displayed.
     • If there are both active and idle-in-transaction sessions at a given collection time, the system will display at most the first 15 sessions based on the earliest session establishment time. For active sessions, the session establishment time means the query start time. For idle-in-transaction sessions, it means the transaction start time.

     Table 1 Historical session information (Some items are not displayed in the list by default.)

     Item	Description
     Sampled	Time when the record was collected.
     Blocked Process ID	ID array of the session that blocked the current session.
     Database OID	OID of the database associated with the current session.
     Database Name	Name of the database associated with the current session.
     Session ID	Current session ID.
     Concurrent Session ID	If the current session is a parallel query, the session ID of the main query of the parallel query is returned. If the current session is the main query of a parallel query or is not a parallel query, no value is returned.
     Backend User OID	OID of the user connected to the current session.
     Username	Name of the user connected to the current session.
     Application Name	Name of the application connected to the current session.
     Client Address	IP address of the client connected to the current session.
     Client Name	Host name of the client connected to the current session.
     Client Port	Port number of the client connected to the current session.
     Session Established	Time when the current session was established.
     Transaction Started	Time when the transaction was started.
     Transaction Duration (s)	Duration from the time when the transaction started to the time when the transaction was collected. The unit is second.
     Query Started	Time when the SQL query started. If the session status is not active, the value is the time when the previous SQL query started.
     State Changed	Time when the session status was last changed.
     Wait Event Type	Type of the query wait event. If no wait event exists, this parameter is left blank.
     Wait Event Name	Name of the query wait event. If no wait event exists, this parameter is left blank.
     Session Status	Status of the current session. active: The session is executing a query. idle in transaction: The session has opened a transaction, but is not executing a query.
     Backend XID	Top-level transaction ID.
     Backend Xmin	Xmin of the current session.
     Query ID	If the session status is active, the value is the identifier of the SQL query that is being executed. If the session status is other than active, the value is the identifier of the last SQL query.
     SQL Statement	If the session is active, the value is the SQL query that is being executed. If the session is in any other state, the value is the last SQL query.
     Process Type	Type of the current session. The possible values include autovacuum launcher, logical replication launcher, parallel worker, background writer, client backend, checkpointer, startup, walreceiver, walsender, and walwriter.
     Memory Usage (%)	Memory usage of the current session. This metric is collected every 5 minutes.
     Process Status	Status of the OS process associated with the current session. This metric is collected every 5 minutes. The statuses include sleeping, running, and disk-sleep.
     Average CPU Usage (%) in 3s	Average CPU usage of the current session in the last 3 seconds. This metric is collected every 5 minutes.
     I/O Wait Time (s)	Disk I/O wait time of the OS process associated with the current session. This metric is collected every 5 minutes.
     Disk Read Rate (MB/s)	Disk read throughput of the OS process associated with the current session. This metric is collected every 5 minutes.
     Disk Write Rate (MB/s)	Disk write throughput of the OS process associated with the current session. This metric is collected every 5 minutes.

   • Viewing historical wait events

     At a given collection time, a maximum of 20 records can be collected.

     Table 2 Historical wait event information

     Item	Description
     Sampled	Time when the record was collected.
     Quantity	Number of sessions with the same wait event type, wait event name, and session status at a given collection time.
     Wait Event Type	Type of the wait event.
     Wait Event Name	Name of the wait event.
     Session Status	The status is active, indicating that a query is being executed.

   • Viewing historical top SQL (instantaneous)

     At a given collection time, a maximum of 20 records can be collected.

     Table 3 Historical top SQL information

     Item	Description
     Sampled	Time when the record was collected.
     Quantity	Number of sessions that are executing the same SQL statement in the same database by the same user at the current time. For RDS for PostgreSQL 14 and later, it indicates the number of sessions that are executing the same query (with the same query ID) in the same database by the same user at the current time.
     Database Name	Database name.
     Username	Username.
     SQL Statement	SQL statement.
     Query ID	SQL query identifier. This parameter is only available in RDS for PostgreSQL 14 and later.