ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Bind Graduation 测试

Oracle Bind Graduation 测试

原创 Linux操作系统 作者:aaqwsh 时间:2012-11-30 10:04:14 0 删除 编辑

最近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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    261340