View tenant sessions|V4.2.1| docs|Distributed Database

View tenant sessions

Last Updated：2023-12-25 08:49:40 Updated

You can view sessions of a tenant by using views or SQL statements.

View sessions of a tenant by using a view

Log on to a MySQL or Oracle tenant of the cluster.

Execute the following statement to view tenant sessions.

A tenant administrator can view all sessions of the tenant. A regular user can view only its own sessions. If you have the PROCESS privilege, you can view all sessions.

MySQL mode

SELECT * FROM oceanbase.GV$OB_PROCESSLIST;

The query result is as follows:

+----------------+----------+----------+------------+------+----------------------+-----------+----------+---------+-------+------------+--------+-------------------------------------------+--------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+-------+-------------------+-----------------------+--------+--------+----------+----------+-----------+
| SVR_IP         | SVR_PORT | SQL_PORT | ID         | USER | HOST                 | DB        | TENANT   | COMMAND | TIME  | TOTAL_TIME | STATE  | INFO                                      | PROXY_SESSID | MASTER_SESSID | USER_CLIENT_IP | USER_HOST | RETRY_CNT | RETRY_INFO | SQL_ID                           | TRANS_ID | THREAD_ID | SSL_CIPHER | TRACE_ID                          | TRANS_STATE | ACTION | MODULE | CLIENT_INFO | LEVEL | SAMPLE_PERCENTAGE | RECORD_POLICY         | LB_VID | LB_VIP | LB_VPORT | IN_BYTES | OUT_BYTES |
+----------------+----------+----------+------------+------+----------------------+-----------+----------+---------+-------+------------+--------+-------------------------------------------+--------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+-------+-------------------+-----------------------+--------+--------+----------+----------+-----------+
| xx.xx.xx.xx    |     2882 |     2881 | 3221487669 | root | xx.xx.xx.xx:43325    | NULL      | mysql001 | Query   |     0 |          0 | ACTIVE | SELECT * FROM oceanbase.GV$OB_PROCESSLIST |         NULL |          NULL | xx.xx.xx.xx    | %         |         0 |          0 | 2E175335D36600B7A8EC72C5094888DD |        0 |     67793 | NULL       | YB42AC1E87C0-000604A74DB36453-0-0 |             |        |        |             |     1 |                10 | SAMPLE_AND_SLOW_QUERY |   NULL | NULL   |     NULL |      472 |       734 |
| xx.xx.xx.xx    |     2882 |     2881 | 3221487666 | root | xx.xx.xx.xx:19326    | oceanbase | mysql001 | Sleep   | 18421 |      18421 | SLEEP  | NULL                                      |         NULL |          NULL | xx.xx.xx.xx    | %         |         0 |          0 |                                  |        0 |         0 | NULL       | NULL                              |             |        |        |             |     1 |                10 | SAMPLE_AND_SLOW_QUERY |   NULL | NULL   |     NULL |      573 |     10349 |
+----------------+----------+----------+------------+------+----------------------+-----------+----------+---------+-------+------------+--------+-------------------------------------------+--------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+-------+-------------------+-----------------------+--------+--------+----------+----------+-----------+
2 rows in set

Oracle mode

SELECT * FROM SYS.GV$OB_PROCESSLIST;

The query result is as follows:

+----------------+----------+----------+------------+------+----------------------+------+-----------+---------+-------+------------+--------+---------------------------------+--------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+-------+-------------------+-----------------------+--------+--------+----------+----------+-----------+
| SVR_IP         | SVR_PORT | SQL_PORT | ID         | USER | HOST                 | DB   | TENANT    | COMMAND | TIME  | TOTAL_TIME | STATE  | INFO                            | PROXY_SESSID | MASTER_SESSID | USER_CLIENT_IP | USER_HOST | RETRY_CNT | RETRY_INFO | SQL_ID                           | TRANS_ID | THREAD_ID | SSL_CIPHER | TRACE_ID                          | TRANS_STATE | ACTION | MODULE | CLIENT_INFO | LEVEL | SAMPLE_PERCENTAGE | RECORD_POLICY         | LB_VID | LB_VIP | LB_VPORT | IN_BYTES | OUT_BYTES |
+----------------+----------+----------+------------+------+----------------------+------+-----------+---------+-------+------------+--------+---------------------------------+--------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+-------+-------------------+-----------------------+--------+--------+----------+----------+-----------+
| xx.xx.xx.xx    |     2882 |     2881 | 3221487673 | SYS  | xx.xx.xx.xx:30390    | SYS  | oracle001 | Query   |     0 |          0 | ACTIVE | SELECT * FROM GV$OB_PROCESSLIST |         NULL |          NULL | xx.xx.xx.xx    | %         |         0 |          0 | D90D2671435AFEEDC9C0E4A1F9BB7895 |        0 |     68178 | NULL       | YB42AC1E87C0-000604A74D5364E0-0-0 | NULL        | NULL   | NULL   | NULL        |     1 |                10 | SAMPLE_AND_SLOW_QUERY |   NULL | NULL   |     NULL |      570 |       867 |
| xx.xx.xx.xx    |     2882 |     2881 | 3221487667 | SYS  | xx.xx.xx.xx:19819    | SYS  | oracle001 | Sleep   | 17758 |      17758 | SLEEP  | NULL                            |         NULL |          NULL | xx.xx.xx.xx    | %         |         0 |          0 | NULL                             |        0 |         0 | NULL       | NULL                              | NULL        | NULL   | NULL   | NULL        |     1 |                10 | SAMPLE_AND_SLOW_QUERY |   NULL | NULL   |     NULL |      752 |     13098 |
+----------------+----------+----------+------------+------+----------------------+------+-----------+---------+-------+------------+--------+---------------------------------+--------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+-------+-------------------+-----------------------+--------+--------+----------+----------+-----------+
2 rows in set

The fields in the query results are described as follows:

SVR_IP: the IP address of the server to which the session belongs, which is the IP address of the OBServer node.
SVR_PORT: the RPC port number of the server to which the session belongs, which is the RPC port number of the OBServer node.
SQL_PORT: the SQL port number of the server to which the session belongs, which is the SQL port number of the OBServer node.
ID: the ID of the session.
USER: the user to which the session belongs.
HOST: the IP address and port number of the client that initiates the session when the client is directly connected to the database, or the IP address and port number of the OceanBase Database Proxy (ODP) host when the client is connected to the database by using ODP.
DB: the name of the database to which the session is connected. In Oracle mode, the schema name that is the same as the username is displayed.
TENANT: the name of the tenant that is accessed in the session.
COMMAND: the type of the command being executed in the session.
TIME: the duration, in seconds, that the current command has been executed. If the command is retried, the system resets and recalculates the execution duration.
TOTAL_TIME: the total execution duration, in seconds, of the current command. If the command is retried, the system does not clear the total execution duration.
STATE: the status of the session.
INFO: the statements being executed in the session.
PROXY_SESSID: If the database is connected by using ODP, the value is the session ID on the ODP node. Otherwise, the value is NULL.
MASTER_SESSID: If the database is connected by using ODP, the value is the primary session ID on the ODP node, which is provided to associate the child sessions of the same SQL statement. Otherwise, the value is NULL.
USER_CLIENT_IP: the IP address of the client that initiates the session.
USER_HOST: the host name of the client that initiates the session.
RETRY_CNT: the number of retries of the current command.
RETRY_INFO: the retry information of the current command, which is usually the error code for the last retry.
SQL_ID: the SQL ID corresponding to the current command.
TRANS_ID: the ID of the transaction.
THREAD_ID: the ID of the thread. If the database is connected by using ODP, the value also indicates the session ID on the ODP node.
SSL_CIPHER: the name of the SSL cipher.
TRACE_ID: the trace ID.
TRANS_STATE: the status of the transaction.
ACTION: the name of the operation being executed that is set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure.
MODULE: the name of the module that is set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure.
CLIENT_INFO: the client information set by calling the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.
LEVEL: the monitoring level of end-to-end tracing for the session. For example, the value 1 indicates the diagnostics information at level 1.
SAMPLE_PERCENTAGE: the sampling frequency of end-to-end tracing for the session. For example, the value 10 indicates that the diagnostics information is sampled at the frequency of 10%.
RECORD_POLICY: the recording strategy of end-to-end tracing for the session. Valid values:
- ALL: All sampling-enabled traces that are hit are recorded in the log file immediately after each span ends.
- ONLY_SLOW_QUERY: Among sampling-enabled traces that are hit, only traces of slow queries are recorded in the log file, and other traces are discarded.
  
  For this strategy, trace logs are recorded after a request ends and is determined as a slow query. In the proxy, the root span (transaction-level span) is immediately recorded when a slow query is detected.
- SAMPLE_AND_SLOW_QUERY: Among sampling-enabled traces that are hit, those meeting any of the following conditions are recorded in the log file:
  1. The hidden parameter _print_sample_ppm is used for sampling at a frequency of 0.01% and the sampling result is hit.
  2. The condition specified in the ONLY_SLOW_QUERY strategy is met.
  In this mode, the logging time varies based on the situation. Specifically, if Condition 1 is met, it is marked on the client that the trace log will be forcibly recorded and this information will be transferred to subsequent components on the link. If Condition 2 is met, the logging time is the same as that in the ONLY_SLOW_QUERY strategy.
  
  For more information about end-to-end tracing, see Overview.
LB_VID: the VPC ID of the load balancing service if you directly connect to the database in a public cloud. The value is always NULL in other scenarios.
LB_VIP: the IP address used by the client to connect to the load balancing service if you directly connect to the database in a public cloud. The value is always NULL in other scenarios.
LB_VPORT: the port used by the client to connect to the load balancing service if you directly connect to the database in a public cloud. The value is always NULL in other scenarios.
IN_BYTES: the inbound traffic of the session.
OUT_BYTES: the outbound traffic of the session.

View tenant sessions by using the SHOW PROCESSLIST or SHOW FULL PROCESSLIST statement

The execution result of the SHOW PROCESSLIST statement depends on the database connection method. If the database is connected by using ODP, the session information on the corresponding ODP node is displayed. If the database is directly connected to the client, the session information on the corresponding OBServer node of the tenant is displayed.

The administrator of a tenant can view all sessions within the tenant. A regular user can view sessions of only the user. If you have the PROCESS privilege, you can view all sessions in the tenant.

Note

For more information about how to view your privileges in Oracle mode, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Modify user privileges.
For more information about how to view your privileges in MySQL mode, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Modify user privileges.

Log on to a MySQL or Oracle tenant of the cluster.
Execute the following statement to view tenant sessions:
- If the client directly connects to the database, use the SHOW PROCESSLIST statement.
```
SHOW PROCESSLIST;
```
  The query result is as follows:
```
+------------+------+----------------------+-----------+---------+------+--------+------------------+
| Id         | User | Host                 | db        | Command | Time | State  | Info             |
+------------+------+----------------------+-----------+---------+------+--------+------------------+
| 3221487631 | root | xx.xx.xx.xx:44615    | oceanbase | Query   |    0 | ACTIVE | SHOW PROCESSLIST |
+------------+------+----------------------+-----------+---------+------+--------+------------------+
1 row in set
```
  The fields are described as follows:
  - Id: the ID of the session.
  - User: the user to which the session belongs.
  - Host: the IP address and port number of the client that initiates the session.
  - db: the name of the database to which the session is connected. In Oracle mode, the schema name that is the same as the username is displayed.
  - Command: the type of the command being executed in the session.
  - Time: the duration, in seconds, that the current command has been executed. If the command is retried, the system resets and recalculates the execution duration.
  - State: the status of the session.
  - Info: the statements being executed in the session.
- If the client connects to the database directly or by using ODP, use the SHOW FULL PROCESSLIST statement.
```
SHOW FULL PROCESSLIST;
```
  The query result is as follows:
```
+------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+
| ID         | USER | TENANT    | HOST                 | DB   | COMMAND | TIME | STATE  | INFO                  | IP             | PORT |
+------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+
| 3221926974 | SYS  | oracle001 | xx.xx.xx.xx:36950    | SYS  | Sleep   | 9154 | SLEEP  | NULL                  | xx.xx.xx.197   | 2881 |
| 3221549774 | SYS  | oracle001 | xx.xx.xx.xx:16924    | SYS  | Query   |    0 | ACTIVE | SHOW FULL PROCESSLIST | xx.xx.xx.198   | 2881 |
+------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+
2 rows in set
```
  The fields are described as follows:
  - ID: the ID of the session.
  - USER: the user to which the session belongs.
  - TENANT: the name of the tenant that is accessed in the session.
  - HOST: the IP address and port number of the client that initiates the session. If the client connects to the database by using ODP, this parameter indicates the IP address and port number of the ODP host.
  - DB: the name of the database to which the session is connected. In Oracle mode, the schema name that is the same as the username is displayed.
  - COMMAND: the type of the command being executed in the session.
  - TIME: the duration, in seconds, that the current command has been executed. If the command is retried, the system resets and recalculates the execution duration.
  - STATE: the status of the session.
  - INFO: the statements being executed in the session.
  - IP: the IP address of the server to which the session belongs, which is the IP address of the OBServer node.
  - PORT: the SQL port number of the server to which the session belongs, which is the SQL port number of the OBServer node.