最近team 讨论 Bind Graduation 比较激烈,详细可以见上一篇文章,基于Bind Graduation 做了以下测试：
主要目的：
测试基于OCI JDBC 等接口的 Bind Graduation行为.针对目前Bind Graduation的行为,以及11.2.0.3出现的purge问题,由于bind graduation导致的child cursor过多问题,暂时没有好的solution(_cursor_obsolete_threshold ?).
建议对问题语句涉及到的表做水平拆分。
测试版本11.2.0.3
[oracle@testdb ~]$ ora si SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 16:39:30 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> show user USER is "SYS" SQL> alter system flush shared_pool; System altered.
1. sqlplus OCI
SQL> VARIABLE n NUMBER
VARIABLE v VARCHAR2(32)
EXECUTE :n := 1; :v := 'Helicon';
INSERT INTO t (n, v) VALUES (:n, :v);SQL> SQL> SQL>
PL/SQL procedure successfully completed.
SQL> SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)'; 2 3
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj 0 1
SQL> VARIABLE v VARCHAR2(33)
EXECUTE :n := 4; :v := 'Terminus';
INSERT INTO t (n, v) VALUES (:n, :v);SQL> SQL>
PL/SQL procedure successfully completed.
SQL> SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> VARIABLE v VARCHAR2(129)
EXECUTE :n := 4; :v := 'Terminus';
INSERT INTO t (n, v) VALUES (:n, :v);SQL> SQL>
PL/SQL procedure successfully completed.
SQL> SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> /
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj 0 2
6cvmu7dwnvxwj 1 1 -----------------129 产生第一个child cursor
SQL> SELECT s.child_number, m.position, m.max_length,
decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2; 2 3 4 5 6
Enter value for sql_id: 6cvmu7dwnvxwj
old 4: WHERE s.sql_id = '&sql_id'
new 4: WHERE s.sql_id = '6cvmu7dwnvxwj'
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
0 1 22 NUMBER
0 2 128 VARCHAR2
1 1 22 NUMBER
1 2 2000 VARCHAR2
丢失了 32这个区间
2 OCI pl/sqldeveloper 操作
SQL> VARIABLE n NUMBER
SQL> VARIABLE v VARCHAR2(32)
SQL> EXECUTE :n := 1; :v := 'Helicon';
PL/SQL procedure successfully completed
n
---------
1
v
---------
Helicon
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
1 row inserted
n
---------
1
v
---------
Helicon
SQL> commit;
Commit complete
SQL> SELECT sql_id,child_number, executions
FROM v$sql
WHERE sql_text = ' INSERT INTO t (n, v) VALUES (:n, :v) '; 2 3
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fp1vwg5jfpk4b 0 1
SQL> VARIABLE v VARCHAR2(33)
SQL> EXECUTE :n := 4; :v := 'Terminus';
PL/SQL procedure successfully completed
n
---------
4
v
---------
Terminus
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
1 row inserted
n
---------
4
v
---------
Terminus
SQL> commit;
Commit complete
SQL> /
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fp1vwg5jfpk4b 0 2
SQL> VARIABLE v VARCHAR2(129)
SQL> EXECUTE :n := 4; :v := 'Terminus';
PL/SQL procedure successfully completed
n
---------
4
v
---------
Terminus
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
1 row inserted
n
---------
4
v
---------
Terminus
SQL>
SQL> commit;
Commit complete
SQL> /
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fp1vwg5jfpk4b 0 3 -------------------没有产生child cursor
SQL> SELECT s.child_number, m.position, m.max_length,
decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2; 2 3 4 5 6
Enter value for sql_id: fp1vwg5jfpk4b
old 4: WHERE s.sql_id = '&sql_id'
new 4: WHERE s.sql_id = 'fp1vwg5jfpk4b'
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
0 1 22 NUMBER
0 2 4000 VARCHAR2
默认产生了4000的max值区间。
这个测试不具备任何意义,经过确认pl/sql developer经过了封装,导致oracle 端默认为4000的max区间。
3. OCI JAVA -(模拟真实环境)
代码如下：
oracle_conn = DriverManager.getConnection("jdbc:oracle:oci:@xxx", "xxx", "xxx");
oracle_stmt = oracle_conn.prepareStatement("insert into t values(?, ?)");
oracle_stmt.setInt(1, 1);
oracle_stmt.setString(2, "Helicon");
oracle_stmt.execute();
oracle_stmt.setInt(1, 2);
oracle_stmt.setString(2, "Helicon33333333333333333333333333333");
oracle_stmt.execute();
oracle_stmt.setInt(1, 3);
oracle_stmt.setString(2, "Helicon3333333333333333333333333333323sssssssssssssssssssssssssssssssssssssssssssssssssssssssddddddddddddddddddddddddddddddddddssssssssssssssssssssssss");
oracle_stmt.execute();
SQL> SELECT s.child_number, m.position, m.max_length,
2 decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
3 FROM v$sql s, v$sql_bind_metadata m
4 WHERE s.sql_id = 'dw481sdb5fkkt'
5 AND s.child_address = m.address
6 ORDER BY 1, 2;
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
0 1 22 NUMBER
0 2 32 VARCHAR2
1 1 22 NUMBER
1 2 2000 VARCHAR2
SQL_TEXT SQL_ID EXECUTIONS LOADS FIRST_LOAD_TIME
insert into t values(:1, :2) dw481sdb5fkkt 1 1 2012-11-28/20:30:05
insert into t values(:1, :2) dw481sdb5fkkt 2 1 2012-11-28/20:30:05
产生了32,2000的区间, 但是缺少了128的区间。
4 JDBC JAVA (目前使用的场景)
代码如下：
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.*;
public class test {
public static void main(String[] args) {
Connection oracle_conn = null;
PreparedStatement oracle_stmt = null;
ResultSet oracle_rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
oracle.jdbc.driver.OracleDriver a;
oracle_conn = DriverManager.getConnection("jdbc:oracle:thin:@xx.xx.xx.xx:xx:yy", "xx", "xx");
oracle_stmt = oracle_conn.prepareStatement("insert into t values(?, ?)");
oracle_stmt.setInt(1, 1);
oracle_stmt.setString(2, "Helicon");
oracle_stmt.execute();
oracle_stmt.setInt(1, 2);
oracle_stmt.setString(2, "Helicon33333333333333333333333333333");
oracle_stmt.execute();
oracle_stmt.setInt(1, 3);
oracle_stmt.setString(2, "Helicon3333333333333333333333333333323sssssssssssssssssssssssssssssssssssssssssssssssssssssssddddddddddddddddddddddddddddddddddssssssssssssssssssssssss");
oracle_stmt.execute();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(oracle_rs != null) {
oracle_rs.close();
oracle_rs = null;
}
if(oracle_stmt != null) {
oracle_stmt.close();
oracle_stmt = null;
}
if(oracle_conn != null) {
oracle_conn.close();
oracle_conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
SQL> SELECT s.child_number, m.position, m.max_length,
2 decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
3 FROM v$sql s, v$sql_bind_metadata m
4 WHERE s.sql_id = 'fw60v89km14c9'
5 AND s.child_address = m.address
6 ORDER BY 1, 2;
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
0 1 22 NUMBER
0 2 32 VARCHAR2
1 1 22 NUMBER
1 2 128 VARCHAR2
2 1 22 NUMBER
2 2 2000 VARCHAR2
SQL_TEXT SQL_ID EXECUTIONS LOADS FIRST_LOAD_TIME
insert into t values(:1, :2) fw60v89km14c9 1 1 2012-11-28/16:16:46
insert into t values(:1, :2) fw60v89km14c9 2 1 2012-11-28/16:16:46
insert into t values(:1, :2) fw60v89km14c9 3 1 2012-11-28/16:16:46
JDBC 行为正常 32 128 2000的区间符合默认行为。
总结：
1. pl/sql developer 测试不具备任何价值。(各位同学也不要基于这个去测试了)
2. OCI sqlplus 缺少32区间, JAVA缺少 128区间 这个问题比较疑惑。
3. JDBC 目前正常
Bind Graduation oracle的本意是为了更详细的区分cursor,多次peeking 达到最佳的执行计划。但是对于一些设计很烂的表,将会出现child cursor暴增的可能
Reference:Oracle Call Interface