You can view sessions of a tenant by using views or SQL statements.
Considerations
In this topic, OceanBase Database V4.3.0 and OceanBase Database Proxy (ODP) V4.2.3 are used, and the client_session_id_version = 2 setting is specified for ODP. The return result varies with the version and setting. For more information about how to view and set the parameters of ODP, see View and modify parameters.
View sessions of a tenant by using a view
Log in to a MySQL or Oracle-compatible 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
PROCESSprivilege, you can view all sessions.MySQL-compatible mode
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PROCESSLIST\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 SQL_PORT: 2881 ID: 3221487622 USER: root HOST: 100.xx.xxx.xxx:49451 DB: test TENANT: mysql001 COMMAND: Sleep TIME: 631.462197 TOTAL_TIME: 631.462197 STATE: SLEEP INFO: NULL PROXY_SESSID: NULL MASTER_SESSID: NULL USER_CLIENT_IP: 100.xx.xxx.xxx USER_HOST: % RETRY_CNT: 0 RETRY_INFO: 0 SQL_ID: TRANS_ID: 3413 THREAD_ID: 0 SSL_CIPHER: NULL TRACE_ID: NULL TRANS_STATE: ACTIVE ACTION: MODULE: CLIENT_INFO: LEVEL: 1 SAMPLE_PERCENTAGE: 10 RECORD_POLICY: SAMPLE_AND_SLOW_QUERY LB_VID: NULL LB_VIP: NULL LB_VPORT: NULL IN_BYTES: 448 OUT_BYTES: 0 USER_CLIENT_PORT: 49451 TOTAL_CPU_TIME: 0 PROXY_USER: SERVICE_NAME: NULL TOTAL_CPU_TIME: 0 TOP_INFO: NULL MEMORY_USAGE: NULL *************************** 2. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 SQL_PORT: 2881 ID: 3221487623 USER: root HOST: 100.xx.xxx.xxx:14422 DB: NULL TENANT: mysql001 COMMAND: Query TIME: 0.005717 TOTAL_TIME: 0.005774 STATE: ACTIVE INFO: SELECT * FROM oceanbase.GV$OB_PROCESSLIST PROXY_SESSID: NULL MASTER_SESSID: NULL USER_CLIENT_IP: 100.xx.xxx.xxx USER_HOST: % RETRY_CNT: 0 RETRY_INFO: 0 SQL_ID: 2E175335D36600B7A8EC72C5094888DD TRANS_ID: 0 THREAD_ID: 33799 SSL_CIPHER: NULL TRACE_ID: YB42A************** TRANS_STATE: ACTION: MODULE: CLIENT_INFO: LEVEL: 1 SAMPLE_PERCENTAGE: 10 RECORD_POLICY: SAMPLE_AND_SLOW_QUERY LB_VID: NULL LB_VIP: NULL LB_VPORT: NULL IN_BYTES: 449 OUT_BYTES: 0 USER_CLIENT_PORT: 14422 TOTAL_CPU_TIME: 0 PROXY_USER: SERVICE_NAME: NULL TOTAL_CPU_TIME: 0 TOP_INFO: NULL MEMORY_USAGE: NULL 2 rows in setOracle-compatible mode
obclient [SYS]> SELECT * FROM SYS.GV$OB_PROCESSLIST\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 SQL_PORT: 2881 ID: 3221487626 USER: SYS HOST: 100.xx.xxx.xxx:25945 DB: SYS TENANT: oracle001 COMMAND: Sleep TIME: 14 TOTAL_TIME: 14 STATE: SLEEP INFO: NULL PROXY_SESSID: NULL MASTER_SESSID: NULL USER_CLIENT_IP: 100.xx.xxx.xxx USER_HOST: % RETRY_CNT: 0 RETRY_INFO: 0 SQL_ID: NULL TRANS_ID: 6343 THREAD_ID: 0 SSL_CIPHER: NULL TRACE_ID: NULL TRANS_STATE: ACTIVE ACTION: NULL MODULE: NULL CLIENT_INFO: NULL LEVEL: 1 SAMPLE_PERCENTAGE: 10 RECORD_POLICY: SAMPLE_AND_SLOW_QUERY LB_VID: NULL LB_VIP: NULL LB_VPORT: NULL IN_BYTES: 447 OUT_BYTES: 0 USER_CLIENT_PORT: 25945 TOTAL_CPU_TIME: 0 PROXY_USER: NULL SERVICE_NAME: NULL TOTAL_CPU_TIME: 0 TOP_INFO: NULL MEMORY_USAGE: NULL *************************** 2. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 SQL_PORT: 2881 ID: 3221487625 USER: SYS HOST: 100.xx.xxx.xxx:24782 DB: SYS TENANT: oracle001 COMMAND: Query TIME: 0 TOTAL_TIME: 0 STATE: ACTIVE INFO: SELECT * FROM SYS.GV$OB_PROCESSLIST PROXY_SESSID: NULL MASTER_SESSID: NULL USER_CLIENT_IP: 100.xx.xxx.xxx USER_HOST: % RETRY_CNT: 0 RETRY_INFO: 0 SQL_ID: 0A6CF0E2AB2C1A1917AB1FFDF2BE9CFF TRANS_ID: 0 THREAD_ID: 34190 SSL_CIPHER: NULL TRACE_ID: YB42A*************** TRANS_STATE: NULL ACTION: NULL MODULE: NULL CLIENT_INFO: NULL LEVEL: 1 SAMPLE_PERCENTAGE: 10 RECORD_POLICY: SAMPLE_AND_SLOW_QUERY LB_VID: NULL LB_VIP: NULL LB_VPORT: NULL IN_BYTES: 449 OUT_BYTES: 0 USER_CLIENT_PORT: 24782 TOTAL_CPU_TIME: 0 PROXY_USER: NULL SERVICE_NAME: NULL TOTAL_CPU_TIME: 0 TOP_INFO: NULL MEMORY_USAGE: NULL 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 remote procedure call (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.Note
In this topic, OceanBase Database V4.3.0 and ODP V4.2.3 are used, and the
client_session_id_version =2setting is specified for ODP. When you use OceanBase Database earlier than V4.3.0 or ODP earlier than V4.2.3, or you use ODP V4.2.3 but do not specifyclient_session_id_version =2, if you directly connect to the database, this field indicates the IP address and port number of the client that initiates the session. If you connect to the database by using ODP, this field indicates the IP address and port number of the ODP node.DB: the name of the database to which the session is connected. In Oracle-compatible 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 amount of time that has elapsed since execution of the command started in the session, or the duration of the current state of the session if no command is being executed, in seconds.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 isNULL.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 isNULL.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 transaction ID.THREAD_ID: the thread ID. 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 Secure Sockets Layer (SSL) cipher.TRACE_ID: the trace ID.TRANS_STATE: the status of the transaction.IDLE: The transaction is in the idle state, indicating that the transaction is not started.ACTIVE: The transaction is explicitly started by a statement such asSTART TRANSACTIONandBEGIN.IMPLICIT_ACTIVE: The transaction is implicitly started. This means that withautocommit = onspecified, the transaction is started after a DML statement or another data modification statement is executed.ROLLBACK_SAVEPOINT: The transaction is being rolled back to a savepoint.IN_TERMINATE: The transaction is being terminated due to an internal cause or a statement initiated by the user, such asCOMMITorROLLBACK.ABORTED: The transaction is internally aborted or rolled back due to an exception.ROLLED_BACK: The transaction is rolled back.COMMIT_TIMEOUT: The transaction commit timed out.COMMIT_UNKNOWN: The result of the transaction commit is unknown.COMMITTED: The transaction is successfully committed.
ACTION: the name of the operation being executed that is set by calling theDBMS_APPLICATION_INFO.SET_ACTIONprocedure.MODULE: the name of the module that is set by calling theDBMS_APPLICATION_INFO.SET_MODULEprocedure.CLIENT_INFO: the client information that is set by calling theDBMS_APPLICATION_INFO.SET_CLIENT_INFOprocedure.LEVEL: the monitoring level of end-to-end tracing for the session. For example, the value1indicates the diagnostics information at level 1.SAMPLE_PERCENTAGE: the sampling percentage of end-to-end tracing for the session. For example, the value10indicates that the diagnostics information is sampled at a percentage 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:- The hidden parameter
_print_sample_ppmis used for sampling at a percentage of 0.01% and the sampling result is hit. - The condition specified in the
ONLY_SLOW_QUERYstrategy 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_QUERYstrategy.For more information about end-to-end tracing, see Overview of end-to-end tracing.
- The hidden parameter
LB_VID: the virtual private cloud (VPC) ID of the load balancing service if you directly connect to the database in a public cloud. The value is alwaysNULLin 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 alwaysNULLin 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 alwaysNULLin other scenarios.IN_BYTES: the inbound traffic of the session.OUT_BYTES: the outbound traffic of the session.USER_CLIENT_PORT: the port number of the client that initiates the session.PROXY_USER: the username of the proxy user when a proxy user is used for login. The value of this column is empty when a non-proxy user is used for login.SERVICE_NAME: Indicates which service name created the session. A value ofNULLmeans the session was not created by a service name.TOTAL_CPU_TIME: the CPU time consumed by the current command, in seconds. If the command is retried, the system does not clear the CPU time.TOP_INFO: information about the top-level PL statement where the executing SQL resides.MEMORY_USAGE: the memory occupied by a single SQL statement, in bytes.
View tenant sessions by using the SHOW PROCESSLIST or SHOW FULL PROCESSLIST statement
You can execute the SHOW PROCESSLIST or SHOW FULL PROCESSLIST statement to view tenant sessions.
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 all tenants. For more information about how to view your privileges, see View user privileges (Oracle-compatible mode) and View user privileges (MySQL-compatible mode). If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges (Oracle-compatible mode) and Grant direct privileges (MySQL-compatible mode).
Log in to a MySQL or Oracle-compatible tenant of the cluster.
Execute the following statement to view tenant sessions:
Use the SHOW PROCESSLIST statement
Notice
In this topic, OceanBase Database V4.3.0 and ODP V4.2.3 are used, and the
client_session_id_version =2setting is specified for ODP. When you use OceanBase Database earlier than V4.3.0 or ODP earlier than V4.2.3, or you use ODP V4.2.3 but do not specifyclient_session_id_version =2, if the database is connected by using ODP, the session information on the corresponding ODP node is displayed after you execute theSHOW PROCESSLISTstatement. If the database is directly connected to the client, the session information on the corresponding OBServer node of the tenant is displayed after you execute theSHOW PROCESSLISTstatement.obclient [SYS]> SHOW PROCESSLIST;The query result is as follows:
+------------+------+----------------------+------+---------+------+--------+------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +------------+------+----------------------+------+---------+------+--------+------------------+ | 3221488068 | SYS | 100.xx.xxx.xxx:18985 | SYS | Sleep | 298 | SLEEP | NULL | | 3221488078 | SYS | 100.xx.xxx.xxx:32489 | SYS | Query | 0 | ACTIVE | SHOW PROCESSLIST | +------------+------+----------------------+------+---------+------+--------+------------------+ 2 rows in setThe 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-compatible 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.
Use the
SHOW FULL PROCESSLISTstatementobclient [SYS]> SHOW FULL PROCESSLIST;The query result is as follows:
+------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+ | ID | USER | TENANT | HOST | DB | COMMAND | TIME | STATE | INFO | IP | PORT | +------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+ | 3221488068 | SYS | oracle001 | 100.xx.xxx.xxx:18985 | SYS | Sleep | 548 | SLEEP | NULL | 172.xx.xxx.xxx | 2881 | | 3221488078 | SYS | oracle001 | 100.xx.xxx.xxx:32489 | SYS | Query | 0 | ACTIVE | SHOW FULL PROCESSLIST | 172.xx.xxx.xxx | 2881 | +------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+ 2 rows in setThe 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 node.DB: the name of the database to which the session is connected. In Oracle-compatible 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.