This topic describes how to query the detailed internal status of a specified client session on OceanBase Database Proxy (ODP).
Procedure
You can use the SHOW PROXYSESSION ATTRIBUTE statement in the sys tenant or a user tenant to query the detailed internal status of a specified client session on ODP, including the server session involved in the client session.
Connect to OceanBase Database by using ODP.
Here is an example:
obclient -h10.xx.xx.xx -uusername@obtenant#obdemo -P2883 -p****** -c -A oceanbaseFor more information about how to connect to OceanBase Database by using ODP, see Connect to a MySQL-compatible tenant of OceanBase Database by using OBClient and Connect to an Oracle-compatible tenant of OceanBase Database by using OBClient.
Query the detailed internal status of the specified client session.
The SQL syntax is as follows:
SHOW PROXYSESSION ATTRIBUTE [id [LIKE 'xxx']]The parameters are described as follows:
If
idis not specified, the details of the current session are returned. This query method is supported for ODP V1.1.0 and later. You can also query the value of a specified attribute of the current session in fuzzy match mode. This query method is supported for ODP V1.1.2 and later.If
idis specified, you can query the value of a specified attribute of a specified session in fuzzy match mode. This query method is supported for ODP V1.1.0 and later.In the statement,
idcan be replaced withcs_idorconnection_id. These parameters are equivalentcs_idindicates the ID of a client session in ODP.connection_idindicates the ID of a client session in OceanBase Database.You can obtain the
connection_idvalue by using theSELECT CONNECTION_ID();statement in MySQL-compatible mode and theSHOW FULL PROCESSLIST;statement in Oracle-compatible mode.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
MySQL-compatible modeOracle-compatible modeQuery the details of the specified client session.
SHOW PROXYSESSION ATTRIBUTE;The query result is as follows:
+----------------------------------+----------------------+----------------+ | attribute_name | value | info | +----------------------------------+----------------------+----------------+ | proxy_sessid | -6044239443189891054 | cs common | | cs_id | 65160 | cs common | | cluster | test420 | cs common | | tenant | mysql001 | cs common | | user | root | cs common | | host_ip | 100.xx.xx.xx | cs common | | host_port | 48056 | cs common | | db | NULL | cs common | | total_trans_cnt | 0 | cs common | | svr_session_cnt | 1 | cs common | | active | true | cs common | | read_state | MCS_ACTIVE_READER | cs common | | tid | 76286 | cs common | | pid | 76286 | cs common | | idc_name | | cs common | | modified_time | 0 | cs stat | | reported_time | 0 | cs stat | | hot_sys_var_version | 0 | cs var version | | sys_var_version | 2 | cs var version | | user_var_version | 0 | cs var version | | last_insert_id_version | 0 | cs var version | | db_name_version | 0 | cs var version | | server_ip | 172.xx.xx.xx | last used ss | | server_port | 2881 | last used ss | | server_sessid | 3221597019 | last used ss | | ss_id | 22 | last used ss | | state | MSS_KA_CLIENT_SLAVE | last used ss | | transact_count | 4 | last used ss | | server_trans_stat | 0 | last used ss | | hot_sys_var_version | 0 | last used ss | | sys_var_version | 2 | last used ss | | user_var_version | 0 | last used ss | | last_insert_id_version | 0 | last used ss | | db_name_version | 0 | last used ss | | is_checksum_supported | 1 | last used ss | | is_safe_read_weak_supported | 0 | last used ss | | is_checksum_switch_supported | 1 | last used ss | | checksum_switch | 1 | last used ss | | enable_extra_ok_packet_for_stats | 1 | last used ss | +----------------------------------+----------------------+----------------+ 39 rows in setQuery details of specified attributes through fuzzy search.
SHOW PROXYSESSION ATTRIBUTE 65160 like '%id%';The query result is as follows:
+------------------------+----------------------+----------------+ | attribute_name | value | info | +------------------------+----------------------+----------------+ | proxy_sessid | -6044239443189891054 | cs common | | cs_id | 65160 | cs common | | tid | 76286 | cs common | | pid | 76286 | cs common | | idc_name | | cs common | | last_insert_id_version | 0 | cs var version | | server_sessid | 3221597019 | last used ss | | ss_id | 22 | last used ss | | last_insert_id_version | 0 | last used ss | +------------------------+----------------------+----------------+ 9 rows in set
Query the details of the specified client session.
SHOW PROXYSESSION ATTRIBUTE;The query result is as follows:
+----------------------------------+----------------------+----------------+ | attribute_name | value | info | +----------------------------------+----------------------+----------------+ | proxy_sessid | -6044239443189891058 | cs common | | cs_id | 65141 | cs common | | cluster | test420 | cs common | | tenant | oracle001 | cs common | | user | sys | cs common | | host_ip | 100.xx.xx.xx | cs common | | host_port | 59648 | cs common | | db | SYS | cs common | | total_trans_cnt | 0 | cs common | | svr_session_cnt | 1 | cs common | | active | true | cs common | | read_state | MCS_ACTIVE_READER | cs common | | tid | 76286 | cs common | | pid | 76286 | cs common | | idc_name | | cs common | | modified_time | 0 | cs stat | | reported_time | 0 | cs stat | | hot_sys_var_version | 1 | cs var version | | sys_var_version | 10 | cs var version | | user_var_version | 0 | cs var version | | last_insert_id_version | 0 | cs var version | | db_name_version | 1 | cs var version | | server_ip | xx.xx.xx.xx | last used ss | | server_port | 2881 | last used ss | | server_sessid | 3221583071 | last used ss | | ss_id | 18 | last used ss | | state | MSS_KA_CLIENT_SLAVE | last used ss | | transact_count | 4 | last used ss | | server_trans_stat | 0 | last used ss | | hot_sys_var_version | 1 | last used ss | | sys_var_version | 10 | last used ss | | user_var_version | 0 | last used ss | | last_insert_id_version | 0 | last used ss | | db_name_version | 1 | last used ss | | is_checksum_supported | 1 | last used ss | | is_safe_read_weak_supported | 0 | last used ss | | is_checksum_switch_supported | 1 | last used ss | | checksum_switch | 1 | last used ss | | enable_extra_ok_packet_for_stats | 1 | last used ss | +----------------------------------+----------------------+----------------+ 39 rows in setQuery details of specified attributes through fuzzy search.
SHOW PROXYSESSION ATTRIBUTE 65141 LIKE '%id%';The query result is as follows:
+------------------------+----------------------+----------------+ | attribute_name | value | info | +------------------------+----------------------+----------------+ | proxy_sessid | -6044239443189891058 | cs common | | cs_id | 65141 | cs common | | tid | 76286 | cs common | | pid | 76286 | cs common | | idc_name | | cs common | | last_insert_id_version | 0 | cs var version | | server_sessid | 3221583071 | last used ss | | ss_id | 18 | last used ss | | last_insert_id_version | 0 | last used ss | +------------------------+----------------------+----------------+ 9 rows in set
The following table describes the fields in the returned result.
Field Description attribute_name The attribute name. value The attribute value. info The basic information. The following table describes the general attributes.
Field Description proxy_sessid The session ID of the ODP. cs_id The ID used by ODP to identify a client. cluster The name of the cluster to which the client session belongs. tenant The tenant. user The user. host_ip The IP address of the user. host_port The port number of the user. db The database. total_trans_cnt The total number of transmitted transactions. svr_session_cnt The total number of sessions. active Indicates whether the session is active. read_state The status of the client session. tid The thread ID. pid The process ID. modified_time The time when the session was modified. reported_time The time when the session was reported. hot_sys_var_version The version of the system variable that is updated in real time. sys_var_version The system variable version. user_var_version The user variable version. last_insert_id_version The last inserted ID version. db_name_version The database name version. server_ip The IP address of the OBServer node. server_port The port number of the OBServer node. server_sessid The session ID of the OBServer node. ss_id The ID used by ODP to identify a server session. state The network connectivity. transact_count The number of transactions executed by the current server session. server_trans_stat The execution status of the current server session, which indicates whether requests are accurately executed in the current server session. hot_sys_var_version The version of general system variables saved by ODP. sys_var_version The version of system variables saved by ODP. user_var_version The version of user variables saved by ODP. last_insert_id_version The version of the last_insert_idvariable saved by ODP.db_name_version The version of the current database variables saved by ODP. is_checksum_supported Indicates whether the checksum is enabled for the current server session. is_safe_read_weak_supported Indicates whether security weak-consistency read is supported. is_checksum_switch_supported Indicates whether the checksum can be enabled or disabled in real time. checksum_switch The enabling status of the checksum. enable_extra_ok_packet_for_stats Indicates whether an extra packet is supported for confirming the return status.