Oracle SQL多版本VERSION_COUNT产生的可能性

Oracle数据库中执行的SQL，很多时候会因为种种原因产生多个不同的执行版本，一个游标的版本过多很容易引起数据库的性能问题，甚至故障。

有时候一个SQL的版本数量可能多达数万个，以下是我之前在恩墨讲堂分享过的一个案例。

产生SQL多版本的原因很多，通过如下的一些测试我们可以稍微来看看如何分析和找到可能的原因。

以下作为一个基础测试数据，一条基本的SQL查询：

create table t1(c1 int, c2 nvarchar2(100));
alter system flush shared_pool;
var b1 number;
var b2 varchar2(10);
exec :b1 := 1;
exec :b2 := '0';
select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2;

如果我们修改了优化器参数、环境变量、绑定变量等，都可能使得SQL发生重新解析，产生不同的子游标，也就是不同的VERSION。

SQL> set serveroutput on
SQL> set echo on
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1(c1 int, c2 nvarchar2(100));
Table created.
SQL> alter system flush shared_pool;
System altered.
SQL> var b1 number;
SQL> var b2 varchar2(10);
SQL> exec :b1 := 1;
PL/SQL procedure successfully completed.
SQL> exec :b2 := '0';
PL/SQL procedure successfully completed.
SQL> select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2;
no rows selected
Rem 这里我们修改了NLS_SORT参数，再来查看SQL的游标数。

SQL> alter session set nls_sort = 'SCHINESE_RADICAL_M';
Session altered.
SQL> select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2;
no rows selected
SQL> exec shared_cursor('3yr4fwqux2buz')
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000088F64328
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000087AE2D38
CHILD_NUMBER                   = 1
LANGUAGE_MISMATCH              = Y
--------------------------------------------------
REM 注意，以上第一个Child就是因为语言不匹配产生的（LANGUAGE_MISMATCH）
REM 以下修改了优化器模式，又一个新的子游标将会因此而产生。

SQL> alter session set optimizer_mode = first_rows;
Session altered.
SQL> select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2;
no rows selected
SQL> exec shared_cursor('3yr4fwqux2buz')
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000088F64328
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000087AE2D38
CHILD_NUMBER                   = 1
LANGUAGE_MISMATCH              = Y
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 000000008A1B2678
CHILD_NUMBER                   = 2
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
REM 可以看到，第二个子游标是因为优化器模式不匹配产生的，OPTIMIZER_MODE_MISMATCH.

REM 以下步骤，我们绑定了不同长度的绑定变量，由此又可能产生新的SQL版本。
SQL> var b2 varchar2(2000);
SQL> exec :b2 := '0';
PL/SQL procedure successfully completed.
SQL> select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2;
no rows selected
SQL> exec shared_cursor('3yr4fwqux2buz')
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000088F64328
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000087AE2D38
CHILD_NUMBER                   = 1
LANGUAGE_MISMATCH              = Y
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 000000008A1B2678
CHILD_NUMBER                   = 2
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 00000000876C6E38
CHILD_NUMBER                   = 3
BIND_LENGTH_UPGRADEABLE        = Y
--------------------------------------------------
REM 我们看到的第三个子游标就是因为绑定变量长度不同而产生的。

复制

从Oracle 9i开始，Oracle对中文语言方式（Simplified Chinese和Traditional Chinese）提供了多种排序方式。主要由以下四种，大家可以进行修改尝试：

SCHINESE_RADICAL_M   针对简体中文，按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M    针对简体中文，按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M       针对简体中文，按照拼音排序
TCHINESE_RADICAL_M    针对繁体中文，按照部首（第一顺序）、笔划（第二顺序）排序
TCHINESE_STROKE_M     针对繁体中文，按照笔划（第一顺序）、部首（第二顺序）排序
复制

当然可以逐一尝试：

SQL> alter session set nls_sort = 'TCHINESE_RADICAL_M';
Session altered.
SQL> select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2;
no rows selected
SQL> exec shared_cursor('3yr4fwqux2buz')
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000088F64328
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000087AE2D38
CHILD_NUMBER                   = 1
LANGUAGE_MISMATCH              = Y
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 000000008A1B2678
CHILD_NUMBER                   = 2
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 00000000876C6E38
CHILD_NUMBER                   = 3
BIND_LENGTH_UPGRADEABLE        = Y
--------------------------------------------------
SQL_TEXT                       = select /* test */ * from t1 where c1 = :b1 and c2 = :b2 order by c2
SQL_ID                         = 3yr4fwqux2buz
ADDRESS                        = 0000000088361678
CHILD_ADDRESS                  = 0000000087AD8AB0
CHILD_NUMBER                   = 4
LANGUAGE_MISMATCH              = Y
--------------------------------------------------
复制

以上测试是基于11.2.0.3版本，在Oracle 12c中，相关的可能因素已经多达64个，参考官方手册可以了解 V$SQL_SHARED_CURSOR 的更详细信息。

文中用到的shared_cursor存储过程脚本列举如下：

create or replace PROCEDURE shared_cursor(
p_sqlid IN VARCHAR2 ) AUTHID CURRENT_USER
IS
l_theCursor NUMBER;
col_cnt NUMBER;
col_rec dbms_sql.desc_tab;
col_value VARCHAR2(4000);
ret_val NUMBER;
BEGIN
l_theCursor := dbms_sql.open_cursor;
dbms_sql.parse(l_theCursor, 'select sql.sql_text, cur.*
from v$sql_shared_cursor cur, v$sql sql
where cur.sql_id = sql.sql_id
and cur.child_number = sql.child_number
and sql.sql_id = :p_sqlid', dbms_sql.native);
dbms_sql.bind_variable( l_theCursor, ':p_sqlid', p_sqlid );
dbms_sql.describe_columns(l_theCursor, col_cnt, col_rec);
FOR idx IN 1 .. col_cnt
LOOP
dbms_sql.define_column(l_theCursor, idx, col_value, 4000);
END LOOP;
ret_val := dbms_sql.execute(l_theCursor);
WHILE(dbms_sql.fetch_rows(l_theCursor) > 0)
LOOP
FOR idx IN 1 .. col_cnt
LOOP
dbms_sql.column_value(l_theCursor, idx, col_value);
IF col_rec(idx).col_name IN ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'SQL_TEXT') THEN
dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
elsif col_value = 'Y' THEN
dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
END IF;
END LOOP;
dbms_output.put_line('--------------------------------------------------');
END LOOP;
dbms_sql.close_cursor(l_theCursor);
END;