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 tenant of OceanBase Database by using OBClient and Connect to an Oracle 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 mode and theSHOW FULL PROCESSLIST;statement in Oracle mode.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
MySQL modeOracle 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.
FieldDescriptionattribute_name The attribute name. value The attribute value. info The basic information. The following table describes the general attributes.
FieldDescriptionproxy_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.