每个SQL语句在解析时,会在Library cache中查找同一是否存在同样的SQL,如果没有就创建parent cursor and child cursor,如果有那还要看相关变量类型、环境参数等是否一致,如果一致就重用该sql,如果不一致那就要创建child cursor:

SQL> create table test(c1 int);

Table created.

SQL> var v1 number;
SQL> exec :v1 := 111;

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from test where c1 = :v1;

  COUNT(*)
----------
         0

SQL> select sql_id,address,child_number,child_address
  2    from v$sql
  3   where sql_text ='select count(*) from test where c1 = :v1';

SQL_ID        ADDRESS  CHILD_NUMBER CHILD_AD
------------- -------- ------------ --------
16gd1thg2rw9w 5625BBF0            0 4EF7A00C

CHILD_NUMBER=0表示首次创建child_cursor,也就是本身自己了;

SQL> select sql_text,sql_id,address from v$sqlarea where sql_id = '16gd1thg2rw9w';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID        ADDRESS
------------- --------
select count(*) from test where c1 = :v1
16gd1thg2rw9w 5625BBF0

SQL> select * from v$sql_bind_metadata where address = '4EF7A00C';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN
-------- ---------- ---------- ---------- ----------
BIND_NAME
------------------------------
4EF7A00C          1          2         22          0
V1

SQL> var v1 varchar2(20);
SQL> exec :v1 := '111';

PL/SQL procedure successfully completed.

SQL> select count(*) from test where c1 = :v1;

  COUNT(*)
----------
         0

SQL> select sql_id,address,child_number,child_address
  2    from v$sql
  3   where sql_text ='select count(*) from test where c1 = :v1';

SQL_ID        ADDRESS  CHILD_NUMBER CHILD_AD
------------- -------- ------------ --------
16gd1thg2rw9w 5625BBF0            0 4EF7A00C
16gd1thg2rw9w 5625BBF0            1 506A7AE8

虽然SQL相同,但变量类型发生了变化,还是要创建新的child_cursor,

CHILD_NUMBER=1表示parent_cursot 的第二个child_cursor;

SQL> select sql_text,sql_id,address from v$sqlarea where sql_id = '16gd1thg2rw9w';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID        ADDRESS
------------- --------
select count(*) from test where c1 = :v1
16gd1thg2rw9w 5625BBF0

SQL> select * from v$sql_bind_metadata where address = '506A7AE8';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN
-------- ---------- ---------- ---------- ----------
BIND_NAME
------------------------------
506A7AE8          1          1        128          0
V1

修改环境变量,也会导致sql不会重用:
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS
SQL> alter session set optimizer_mode='ALL_ROWS';

Session altered.

SQL> var v1 number;
SQL> exec :v1 :=222;

PL/SQL procedure successfully completed.

SQL> select count(*) from test where c1 = :v1;

  COUNT(*)
----------
         0

SQL> select sql_id,address,child_number,child_address
  2    from v$sql
  3   where sql_text ='select count(*) from test where c1 = :v1';

SQL_ID        ADDRESS  CHILD_NUMBER CHILD_AD
------------- -------- ------------ --------
16gd1thg2rw9w 5625BBF0            0 4EF7A00C
16gd1thg2rw9w 5625BBF0            1 506A7AE8
16gd1thg2rw9w 5625BBF0            2 50636A2C

SQL>  select sql_id,address,child_address
  2    from v$sql_shared_cursor where sql_id = '16gd1thg2rw9w';

SQL_ID        ADDRESS  CHILD_AD
------------- -------- --------
16gd1thg2rw9w 5625BBF0 4EF7A00C
16gd1thg2rw9w 5625BBF0 506A7AE8
16gd1thg2rw9w 5625BBF0 50636A2C

SQL>

同一SQL有多个child_cursor;