绑定变量及其相关特性分析

--绑定变量分析
一、理论分析
1.优势
绑定变量在性能方面的优势是他们允许共享库缓存中的父游标，这样就避免了硬解析以及相关的额外开销。
2.劣势
在某些条件下会对查询优化器隐藏重要的信息。使用字面值时，查询优化器总能够做出最接近的估算。
二、实验
1.绑定变量共享游标实验
2.绑定变量扫视（bind variable peeking）导致的劣势实验。

3.自适应游标共享（adaptive cursor sharing）的新特性实验。

--1.绑定变量共享游标实验
SCOTT@PROD1> CREATE TABLE t (n NUMBER, v VARCHAR2(4000));

Table created.

SCOTT@PROD1> 
SCOTT@PROD1> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SCOTT@PROD1> 
SCOTT@PROD1> COLUMN sql_id NEW_VALUE sql_id
SCOTT@PROD1> 
SCOTT@PROD1> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM This script only works if:
SCOTT@PROD1> REM - the database character set is a single-byte encoding (e.g. WE8MSWIN1252)
SCOTT@PROD1> REM - the database national character is a two-byte encoding (e.g. AL16UTF16)
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> SELECT parameter, value
  2  FROM nls_database_parameters
  3  WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER
------------------------------
VALUE
----------------------------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16


SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Execute three times the same SQL statement. Every time the value of the
SCOTT@PROD1> REM bind variable is changed. Note that the SQL statement uses two bind
SCOTT@PROD1> REM variables: a NUMBER and a VARCHAR2(32).
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE n NUMBER
SCOTT@PROD1> VARIABLE v VARCHAR2(32)  --此处为32字节长
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 1; :v := 'Helicon';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 2; :v := 'Trantor';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 3; :v := 'Kalgan';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Display information about the associated child cursors
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> SELECT sql_id, child_number, executions
  2  FROM v$sql
  3  WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj		 0	    3

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE
--没有创建子游标，父游标被执行了3次。

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Re-execute the SQL statement two times. Compared to the previous
SCOTT@PROD1> REM executions, the size of the VARCHAR2 bind variable is increased.
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE v VARCHAR2(33)  --此处更改为33字节长
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 4; :v := 'Terminus';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE v VARCHAR2(128)  --此处更改为128字节长
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 4; :v := 'Terminus';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Display information about the associated child cursors
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> SELECT sql_id, child_number, executions
  2  FROM v$sql
  3  WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj		 0	    4
6cvmu7dwnvxwj		 1	    1

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE
--0-128字长的包含在第一类中，不包括128。

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Re-execute the SQL statement two times. Compared to the previous
SCOTT@PROD1> REM executions, the size of the VARCHAR2 bind variable is increased.
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE v VARCHAR2(129)  --此处更改为129字节长
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 4; :v := 'Terminus';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE v VARCHAR2(2000)  --此处更改为2000字节长
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 4; :v := 'Terminus';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Display information about the associated child cursors
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> SELECT sql_id, child_number, executions
  2  FROM v$sql
  3  WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj		 0	    4
6cvmu7dwnvxwj		 1	    2
6cvmu7dwnvxwj		 2	    1

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE
--129-2000字长的包含在第一类中，不包括2000。

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Re-execute the SQL statement two times. Compared to the previous
SCOTT@PROD1> REM executions, the size of the VARCHAR2 bind variable is increased.
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE v VARCHAR2(2001)
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 4; :v := 'Terminus';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> VARIABLE v VARCHAR2(4000)
SCOTT@PROD1> 
SCOTT@PROD1> EXECUTE :n := 4; :v := 'Terminus';

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> INSERT INTO t (n, v) VALUES (:n, :v);

1 row created.

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM
SCOTT@PROD1> REM Display information about the associated child cursors
SCOTT@PROD1> REM
SCOTT@PROD1> 
SCOTT@PROD1> SELECT sql_id, child_number, executions
  2  FROM v$sql
  3  WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

SQL_ID	      CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj		 0	    4
6cvmu7dwnvxwj		 1	    2
6cvmu7dwnvxwj		 2	    3

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE
--2000字节以上都包括在第三类中。


SCOTT@PROD1> REM The following query works as of 11.2 only
SCOTT@PROD1> 
SCOTT@PROD1> SELECT child_number, bind_length_upgradeable
  2  FROM v$sql_shared_cursor
  3  WHERE sql_id = '&sql_id';
old   3: WHERE sql_id = '&sql_id'
new   3: WHERE sql_id = '6cvmu7dwnvxwj'

CHILD_NUMBER BIND_LENGTH_UPGRADEABLE
------------ -----------------------
	   0 N
	   1 Y
	   2 N

SCOTT@PROD1> 
SCOTT@PROD1> PAUSE

SCOTT@PROD1> 
SCOTT@PROD1> REM The following query works as of 11.2.0.2 only
SCOTT@PROD1> 
SCOTT@PROD1> SELECT x.child_number, x.reason, x.bind_position, x.original_oacmxl AS bind_size
  2  FROM v$sql_shared_cursor s,
  3  	  XMLTable('/Root'
  4  		   PASSING XMLType('<Root>'||reason||'</Root>')
  5  		   COLUMNS child_number NUMBER		 PATH '/Root/ChildNode[1]/ChildNumber',
  6  			   id NUMBER			 PATH '/Root/ChildNode[1]/ID',
  7  			   reason VARCHAR2(100) 	 PATH '/Root/ChildNode[1]/reason',
  8  			   bind_position NUMBER 	 PATH '/Root/ChildNode[1]/bind_position',
  9  			   original_oacflg NUMBER	 PATH '/Root/ChildNode[1]/original_oacflg',
 10  			   original_oacmxl NUMBER	 PATH '/Root/ChildNode[1]/original_oacmxl',
 11  			   upgradeable_new_oacmxl NUMBER PATH '/Root/ChildNode[1]/upgradeable_new_oacmxl'
 12  			   ) x
 13  WHERE s.sql_id = '&sql_id';
old  13: WHERE s.sql_id = '&sql_id'
new  13: WHERE s.sql_id = '6cvmu7dwnvxwj'

CHILD_NUMBER REASON		    BIND_POSITION  BIND_SIZE
------------ ---------------------- ------------- ----------
	   0 Bind mismatch(22)			1	 128
	   1 Bind mismatch(20)			1	2000
	   2 Bind mismatch(20)			1	4001

SCOTT@PROD1> 
SCOTT@PROD