Note
This view was introduced in OceanBase Database V4.2.3.
Purpose
The V$OB_SESSION_PS_INFO view displays the information about prepared statements opened in all sessions on the current OBServer node.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node. |
| TENANT_ID | NUMBER(38) | NO | The ID of the tenant. |
| PROXY_SESSION_ID | NUMBER(38) | NO | The session ID in OceanBase Database Proxy (ODP). |
| SESSION_ID | NUMBER(38) | NO | For V4.2.x:
|
| PS_CLIENT_STMT_ID | NUMBER(38) | NO | The prepared statement ID returned to the client after the client sends a PREPARE request. |
| PS_INNER_STMT_ID | NUMBER(38) | NO | The internal prepared statement ID on the OBServer node, namely the prepared statement ID in the prepared statement cache. |
| STMT_TYPE | VARCHAR2(256) | NO | The type of the prepared statement. Here are some of the valid values: T_SELECT, T_INSERT, T_REPLACE, T_DELETE, T_UPDATE, T_MERGE, T_EXPLAIN, T_CREATE_TABLE, and T_DROP_TABLE. |
| PARAM_COUNT | NUMBER(38) | NO | The number of parameters in the prepared statement. |
| PARAM_TYPES | CLOB | NO | A list of parameter types. It contains the types of all parameters in the prepared statement. Each parameter type is represented by a number. Different parameter types are separated with commas (,). |
| REF_COUNT | NUMBER(38) | NO | The reference count of the ps_session_info_ attribute in the session. After a statement is prepared in a session, a ps_session_info_ attribute is generated in the session. |
| CHECKSUM | NUMBER(38) | NO | The string checksum of the parameterized SQL statement in the prepared statement cache. |
The following table lists the parameter types and numbers in the PARAM_TYPES column.
Parameter type |
Number |
|---|---|
| MYSQL_TYPE_DECIMAL | 0 |
| MYSQL_TYPE_TINY | 1 |
| MYSQL_TYPE_SHORT | 2 |
| MYSQL_TYPE_LONG | 3 |
| MYSQL_TYPE_FLOAT | 4 |
| MYSQL_TYPE_DOUBLE | 5 |
| MYSQL_TYPE_NULL | 6 |
| MYSQL_TYPE_TIMESTAMP | 7 |
| MYSQL_TYPE_LONGLONG | 8 |
| MYSQL_TYPE_INT24 | 9 |
| MYSQL_TYPE_DATE | 10 |
| MYSQL_TYPE_TIME | 11 |
| MYSQL_TYPE_DATETIME | 12 |
| MYSQL_TYPE_YEAR | 13 |
| MYSQL_TYPE_NEWDATE | 14 |
| MYSQL_TYPE_VARCHAR | 15 |
| MYSQL_TYPE_BIT | 16 |
| MYSQL_TYPE_COMPLEX | 160 |
| MYSQL_TYPE_ARRAY | 161 |
| MYSQL_TYPE_STRUCT | 162 |
| MYSQL_TYPE_CURSOR | 163 |
| MYSQL_TYPE_OB_TIMESTAMP_WITH_TIME_ZONE | 200 |
| MYSQL_TYPE_OB_TIMESTAMP_WITH_LOCAL_TIME_ZONE | 201 |
| MYSQL_TYPE_OB_TIMESTAMP_NANO | 202 |
| MYSQL_TYPE_OB_RAW | 203 |
| MYSQL_TYPE_OB_INTERVAL_YM | 204 |
| MYSQL_TYPE_OB_INTERVAL_DS | 205 |
| MYSQL_TYPE_OB_NUMBER_FLOAT | 206 |
| MYSQL_TYPE_OB_NVARCHAR2 | 207 |
| MYSQL_TYPE_OB_NCHAR | 208 |
| MYSQL_TYPE_OB_UROWID | 209 |
| MYSQL_TYPE_ORA_BLOB | 210 |
| MYSQL_TYPE_ORA_CLOB | 211 |
Sample query
Query the information about prepared statements opened in all sessions on the current OBServer node.
obclient [SYS]> SELECT * FROM SYS.V$OB_SESSION_PS_INFO;
The query result is as follows:
+---------------+----------+-----------+------------------+------------+-------------------+------------------+-----------+-------------+-------------+-----------+------------+
| SVR_IP | SVR_PORT | TENANT_ID | PROXY_SESSION_ID | SESSION_ID | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | STMT_TYPE | PARAM_COUNT | PARAM_TYPES | REF_COUNT | CHECKSUM |
+---------------+----------+-----------+------------------+------------+-------------------+------------------+-----------+-------------+-------------+-----------+------------+
| xx.xx.xx.xx | 17119 | 1002 | 0 | 3221643421 | 1 | 1 | T_SELECT | 3 | 3, 15, 202 | 1 | 1807504655 |
+---------------+----------+-----------+------------------+------------+-------------------+------------------+-----------+-------------+-------------+-----------+------------+