Oracle Bind Graduation 測試

aaqwsh發表於2012-11-30
Oracle

最近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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章