最近team 讨论 Bind Graduation 比较激烈,主要是因为有个sql的子游标过多（我们的acs已经禁用掉）：
SQL> select count(*) from v$sql where sql_id='27svyp3s52cu0';
COUNT(*)
----------
1788
SQL> select count(*) from v$sql where sql_id='27svyp3s52cu0' and is_obsolete='Y';
COUNT(*)
----------
1700
手工去清理这些obsoleted游标时遇到这个bug（我们的数据库版本是Database Patch Set Update : 11.2.0.3.3 (13923374)）：
Bug 14127231 dbms_shared_pool.purge raised ora-6570 on obsoleted child cursors
于是基于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 ~]$
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 (目前使用的场景)
代码如下：
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();
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暴增的可能
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-750315/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/758322/viewspace-750315/
125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
