--- title: Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的相关的常见问题和使用技巧，帮助您快速解决 Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的 更新时间：2024-01-22 01:56 适用版本： V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的。 以下例子以 OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本和 OceanBase Connector/J V2.4.1 版本为例进行说明。 1. 创建测试方法 testPs()。 ```shell @Test public void testPs(){ Connection conn; Connection conn1; Statement stmt; try { // 打开链接 conn = DriverManager.getConnection("jdbc:oceanbase://xx.xx.xx.xx:xxxx/test?useServerPrepStmts=true", "test@xxxx", "test"); conn1 = DriverManager.getConnection("jdbc:oceanbase://xx.xx.xx.xx:xxxx/oceanbase?useServerPrepStmts=true", "root@sys", ""); stmt = conn.createStatement(); String r = ""; RowId i = null; // 创建测试表 System.out.println("创建测试表"); stmt.executeUpdate("set autocommit=1;"); stmt.executeUpdate("create table test1(c1 int, c2 int)partition by hash(c1) partitions 3;"); conn1.createStatement().executeUpdate("alter proxyconfig set enable_cached_server='false';"); stmt.executeUpdate("insert into test1 values(911);"); stmt.executeUpdate("insert into test1 values(918);"); ResultSet rs = stmt.executeQuery("select * from test1 where 0;"); while(rs.next()){ r = rs.getString(1); i = rs.getRowId(1); } PreparedStatement ps = conn.prepareStatement("select * from test1 where a = :1;"); ps.setString(1, r); ResultSet rs1 = ps.executeQuery(); while(rs1.next()){ Assert.assertEquals(1, rs1.getInt(1)); } ResultSet rs2 = conn1.createStatement().executeQuery("select query_sql,plan_type,PARAMS_VALUE from oceanbase.GV$SQL_AUDIT where query_sql like 'select * from test1 where%' order by request_time desc limit 1;"); while(rs2.next()){ // Assert.assertEquals(1, rs2.getInt(2)); System.out.println(rs2.getString(1)+" "+rs2.getInt(2)+" "+rs2.getString(3)); } ps.setRowId(1, i); ResultSet rs3 = ps.executeQuery(); while(rs3.next()){ Assert.assertEquals(1, rs3.getInt(1)); } ResultSet rs4 = conn1.createStatement().executeQuery("select query_sql,plan_type,PARAMS_VALUE from oceanbase.GV$SQL_AUDIT where query_sql like 'select * from test1 where%' order by request_time desc limit 1;"); while(rs4.next()){ // Assert.assertEquals(1, rs4.getInt(2)); System.out.println(rs4.getString(1)+" "+rs4.getInt(2)+" "+rs4.getString(3)); } stmt.executeUpdate("drop table test1;"); stmt.close(); conn.close(); conn1.close(); } catch (Exception se) { // 处理 JDBC 错误 se.printStackTrace(); } // 处理 Class.forName 错误 System.out.println("Goodbye!"); } ``` 2. 每次 SQL 语句请求的 PS 协议绑定变量的值。 ```shell obclient > select tenant_id,query_sql , trace_id , REQUEST_TYPE,ps_stmt_id,PARAMS_VALUE from oceanbase.gv$sql_audit where query_sql like 'select * from test1 where%'; +-----------+----------------------------------+-----------------------------------+--------------+------------+--------------+ | tenant_id | query_sql | trace_id | REQUEST_TYPE | ps_stmt_id | PARAMS_VALUE | +-----------+----------------------------------+-----------------------------------+--------------+------------+--------------+ | 1002 | select * from test1 where 0 | YB420BA65670-00060057FDE01DFC-0-0 | 2 | 0 | | | 1 | select * from test1 where a = ?; | YB420BA6566F-0006004751CE7B96-0-0 | 5 | 1 | | | 1 | select * from test1 where a = ?; | YB420BA6566F-0006004751CE7B97-0-0 | 6 | 1 | 1 | | 1002 | select * from test1 where 0 | YB420BA6566F-000600474E4BF895-0-0 | 2 | 0 | | | 1002 | select * from test1 where a = ?; | YB420BA6566F-000600474E4BF8A3-0-0 | 5 | 1 | | | 1002 | select * from test1 where a = ?; | YB420BA6566F-000600474E4BF8A4-0-0 | 6 | 1 | 1 | | 1003 | select * from test1 where a = ?; | YB420BA6566F-00060047500BF7CD-0-0 | 5 | 1 | | | 1003 | select * from test1 where a = ?; | YB420BA6566F-00060047500BF7CE-0-0 | 1 | 1 | | +-----------+----------------------------------+-----------------------------------+--------------+------------+--------------+ 8 rows in set (0.27 sec) ``` 3. 集群中所有的租户以及租户的分布信息。 ```shell obclient > select * from oceanbase.__all_tenant; +----------------------------+----------------------------+-----------+-------------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+ | gmt_create | gmt_modified | tenant_id | tenant_name | replica_num | zone_list | primary_zone | locked | collation_type | info | read_only | rewrite_merge_version | locality | logonly_replica_num | previous_locality | storage_format_version | storage_format_work_version | default_tablegroup_id | compatibility_mode | drop_tenant_time | status | in_recyclebin | +----------------------------+----------------------------+-----------+-------------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+ | xxxx-xx-xx xx:xx:xx.xxxxx | xxxx-xx-xx xx:xx:xx.xxxxx | 1 | sys | -1 | zone1;zone2;zone3 | zone1;zone2,zone3 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 | | xxxx-xx-xx xx:xx:xx.xxxxx | xxxx-xx-xx xx:xx:xx.xxxxx | 1001 | chongzheng_oracle | -1 | zone1;zone2;zone3 | zone1,zone2,zone3 | 0 | 0 | | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | | 0 | 0 | -1 | 1 | -1 | TENANT_STATUS_NORMAL | 0 | | xxxx-xx-xx xx:xx:xx.xxxxx | xxxx-xx-xx xx:xx:xx.xxxxx | 1002 | L3 | -1 | zone1;zone2;zone3 | RANDOM | 0 | 0 | | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 | | xxxx-xx-xx xx:xx:xx.xxxxx | xxxx-xx-xx xx:xx:xx.xxxxx | 1003 | L3_oracle | -1 | zone1;zone2;zone3 | zone1;zone2,zone3 | 0 | 0 | | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | | 0 | 0 | -1 | 1 | -1 | TENANT_STATUS_NORMAL | 0 | +----------------------------+----------------------------+-----------+-------------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+ 4 rows in set (0.00 sec) ``` ## 适用版本 OceanBase 数据库 V3.x 版本。 ## 问题原因 设置了 `is_execute_ps_cursor` 参数导致走 PL 路径，而 PL 路径走的是 inner sql 执行。由于记录进 SQL Audit 时记录的类型是 inner sql，所以导致了 request type 错误。 ## 解决方法 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.4 BP4 (oceanbase-3.2.4.4-104000052023062021) 及之后版本。 Previous [使用 SQL_ID 和 FORMAT_SQL_ID 来绑定执行计划的说明](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005438226) Next [explain SQL 报错 4000](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000245506) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线