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