You can view sessions of a tenant by using views or SQL statements.
Considerations
In this topic, OceanBase Database V4.3.0 and ODP V4.2.3 are used, and the client_session_id_version = 2 and proxy_id = 1 settings are specified for ODP. The return result varies for different versions and settings. 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 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
PROCESSprivilege, you can view all sessions.MySQL mode
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PROCESSLIST;The query result is as follows:
*************************** 1. row *************************** SVR_IP: 11.xxx.xxx.xxx SVR_PORT: 28826 SQL_PORT: 28816 ID: 3221491293 USER: root HOST: 100.xx.xxx.xxx:7005 DB: NULL TENANT: mysql001 COMMAND: Query TIME: 0 TOTAL_TIME: 0 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: ****** SSL_CIPHER: NULL TRACE_ID: Y709A0BA1CCFB-000614227831F518-0-0 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: 626 OUT_BYTES: 4432 USER_CLIENT_PORT: 7005 *************************** 2. row *************************** SVR_IP: 11.xxx.xxx.xxx SVR_PORT: 28826 SQL_PORT: 28816 ID: 3221491292 USER: root HOST: 100.xx.xxx.xxx:6476 DB: mwx TENANT: mysql001 COMMAND: Sleep TIME: 40 TOTAL_TIME: 40 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: ****** 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: 668 OUT_BYTES: 1891 USER_CLIENT_PORT: 6476 2 rows in setOracle mode
obclient [SYS]> SELECT * FROM SYS.GV$OB_PROCESSLIST\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 11.xxx.xxx.xxx SVR_PORT: 28826 SQL_PORT: 28816 ID: 3221491295 USER: SYS HOST: 100.xx.xxx.xxx:24534 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: ****** SSL_CIPHER: NULL TRACE_ID: Y709A0BA1CCFB-000614227891BB98-0-0 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: 722 OUT_BYTES: 4326 USER_CLIENT_PORT: 24534 *************************** 2. row *************************** SVR_IP: 11.xxx.xxx.xxx SVR_PORT: 28826 SQL_PORT: 28816 ID: 3221491299 USER: SYS HOST: 100.xx.xxx.xxx:30614 DB: SYS TENANT: oracle001 COMMAND: Sleep TIME: 15 TOTAL_TIME: 15 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: ****** 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: 664 OUT_BYTES: 964 USER_CLIENT_PORT: 30614 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.Note
In this topic, OceanBase Database V4.3.0 and ODP V4.2.3 are used, and the
client_session_id_version = 2andproxy_id = 1settings are 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 = 2orproxy_id = 1, the meaning of this field varies depending on the database connection method. 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 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.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 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 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.IDLE: The transaction is in the dle state, indicating the transaction is not started.ACTIVE: The transaction is explicitly started. It is explicitly begun by statements likeSTART TRANSACTIONorBEGIN.IMPLICIT_ACTIVE: The transaction is implicitly started. Inautocommit = onmode, the transaction is started after executing DML or other data modification statements.ROLLBACK_SAVEPOINT: The transaction is being rolled back to a savepoint.IN_TERMINATE: The transaction is ending, including endings caused by internal reasons and those initiated by users (for example, executingCOMMITorROLLBACKstatements).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 has timed out.COMMIT_UNKNOWN: The result of the transaction commit is unknown.COMMITTED: The transaction has been 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 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 level1.SAMPLE_PERCENTAGE: the sampling frequency of end-to-end tracing for the session. For example, the value10indicates 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:- The hidden parameter
_print_sample_ppmis used for sampling at a frequency 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.
- The hidden parameter
LB_VID: the 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 initiated the session.
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 mode) and View user privileges (MySQL mode). If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Modify user privileges (Oracle mode) and Modify user privileges (MySQL mode).
Log on to a MySQL or Oracle tenant of the cluster.
Execute the following statement to view tenant sessions:
Use the
SHOW PROCESSLISTstatementNotice
In this topic, OceanBase Database V4.3.0 and ODP V4.2.3 are used, and the
client_session_id_version = 2andproxy_id = 1settings are 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 = 2orproxy_id = 1, the result returned by theSHOW PROCESSLISTstatement varies depending on the database connection method. If you connect to the database by using ODP, the statement returns the session information on the corresponding ODP node. If you directly connect to the database, the statement returns the session information on the corresponding OBServer node of the tenant.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 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 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.