---查询cursor_sharing目前值及可选值
SQL> select name,value,isdefault from v$parameter_valid_values where name like '%cursor_sharing%'

NAME                 VALUE      ISDEFAULT
-------------------- ---------- ----------
cursor_sharing       FORCE      FALSE
cursor_sharing       EXACT      TRUE --exact为默认值
cursor_sharing       SIMILAR    FALSE

---测试目标：配置cursor_sharing不同值对于version_count的影响

---------1,cursor_sharing=exact其v$sqlarea,v$sql,v$sql_shared_cursor的信息
SQL> alter session set cursor_sharing=exact;

Session altered.    

---用户会话
SQL> select /*+ sex1 */ count(1) from t_version where a=1;

  COUNT(1)
----------
         0
        
--管理会话

-----父游标
SQL> select sql_text,sql_id,address,hash_value,optimizer_mode,version_count from v$sqlarea where sql_text like '%sex1%a=%' and parsing_schema_name='SCOTT';

SQL_TEXT                       SQL_ID        ADDRESS  HASH_VALUE OPTIMIZER_ VERSION_COUNT
------------------------------ ------------- -------- ---------- ---------- -------------
select /*+ sex1 */ count(1) fr 847hftv087zk8 2DE2C0A0 3229875784 ALL_ROWS               1
om t_version where a=1       

-----子游标
SQL> select sql_text,sql_id,address,child_address,child_number,optimizer_mode,parsing_schema_name  from v$sql where sql_text like '%sex1%count(1)%' and parsing_schema_name='SCOTT';

SQL_TEXT                       SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER OPTIMIZER_ PARSING_SCHEMA_NAME
------------------------------ ------------- -------- -------- ------------ ---------- ------------------------------
select /*+ sex1 */ count(1) fr 847hftv087zk8 2DE2C0A0 28D2C0AC            0 ALL_ROWS   SCOTT
om t_version where a=1

---用户会话运行另一列值的sql
SQL> select /*+ sex1 */ count(1) from t_version where a=2;

  COUNT(1)
----------
         0

-----父游标由原来1个变成2个，即cursor_sharing=exact是严格区分sql的列值，不同列值视为不同的sql
SQL> select sql_text,sql_id,address,hash_value,optimizer_mode,version_count from v$sqlarea where sql_text like '%sex1%a=%' and parsing_schema_name='SCOTT';

SQL_TEXT                       SQL_ID        ADDRESS  HASH_VALUE OPTIMIZER_ VERSION_COUNT
------------------------------ ------------- -------- ---------- ---------- -------------
select /*+ sex1 */ count(1) fr d9wz3afqxqbk7 2DE2842C 2916822599 ALL_ROWS               1
om t_version where a=2

select /*+ sex1 */ count(1) fr 847hftv087zk8 2DE2C0A0 3229875784 ALL_ROWS               1
om t_version where a=1

-----子游标由原来1个变成2个
SQL> select sql_text,sql_id,address,child_address,child_number,optimizer_mode,parsing_schema_name  from v$sql where sql_text like '%sex1%count(1)%' and parsing_schema_name='SCOTT';

SQL_TEXT                       SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER OPTIMIZER_ PARSING_SCHEMA_NAME
------------------------------ ------------- -------- -------- ------------ ---------- ------------------------------
select /*+ sex1 */ count(1) fr d9wz3afqxqbk7 2DE2842C 28CF0C98            0 ALL_ROWS   SCOTT
om t_version where a=2

select /*+ sex1 */ count(1) fr 847hftv087zk8 2DE2C0A0 28D2C0AC            0 ALL_ROWS   SCOTT
om t_version where a=1  

小结：1,cursor_sharing=exact时，严格区分sql的书写，不同列值视为不同的sql
     

---------为了方便测试清空共享池
SQL> alter system flush shared_pool;

System altered.    

---------2,cursor_sharing=similar其v$sqlarea,v$sql,v$sql_shared_cursor的信息
alter session set cursor_sharing=similar;

---用户会话
alter session set cursor_sharing=similar

SQL> select /*+ wv */ count(a) from t_version where a=1;

  COUNT(A)
----------
         0

SQL> select /*+ wv */ count(a) from t_version where a=2;

  COUNT(A)
----------
         0
        
        
        
---管理会话

---父游标,注意version_count为2,虽然只有一个父游标
SQL> select sql_text,sql_id,address,optimizer_mode,version_count from v$sqlarea where sql_text like '%wv%count(a)%' and sql_text not like '%v$sql%';

SQL_TEXT                                           SQL_ID        ADDRESS
-------------------------------------------------- ------------- --------
OPTIMIZER_ VERSION_COUNT
---------- -------------
select /*+ wv */ count(a) from t_version where a=: 0w2sqws87a711 2DCD1920
"SYS_B_0"
ALL_ROWS               2

---子游标,一个父游标有多个子游标
SQL>select sql_text,sql_id,address,child_address,child_number,optimizer_mode,parsing_schema_name  from v$sql where sql_text like '%wv%count(a)%' and sql_text not like '%v$sql%'

SQL_TEXT                                           SQL_ID        ADDRESS
-------------------------------------------------- ------------- --------
CHILD_AD CHILD_NUMBER OPTIMIZER_ PARSING_SCHEMA_NAME
-------- ------------ ---------- ------------------------------
select /*+ wv */ count(a) from t_version where a=: 0w2sqws87a711 2DCD1920
"SYS_B_0"
2DCD169C            0 ALL_ROWS   SCOTT

select /*+ wv */ count(a) from t_version where a=: 0w2sqws87a711 2DCD1920
"SYS_B_0"
2DCD1118            1 ALL_ROWS   SCOTT

---用户管理
SQL> select * from v$sql_shared_cursor where address='2DCD1920';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0w2sqws87a711 2DCD1920 2DCD169C            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0w2sqws87a711 2DCD1920 2DCD1118            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N        

---上述的h表示
HASH_MATCH_FAILED VARCHAR2(1) (Y|N) No existing child cursors have the unsafe literal bind hash values required by the current cursor

--3,cursor_sharing=force其v$sqlarea,v$sql,v$sql_shared_cursor的信息

----用户会话清空共享池并配置为force
SQL> alter system set cursor_sharing=force;

System altered.
SQL> alter system set cursor_sharing=force;

System altered.

---用户会话运行3个不同列值的sql
SQL> select /*+ force1 */ count(a) from t_version where a=1;

  COUNT(A)
----------
         0

SQL>
SQL> select /*+ force1 */ count(a) from t_version where a=2;

  COUNT(A)
----------
         0

SQL> select /*+ force1 */ count(a) from t_version where a=3;

  COUNT(A)
----------
         0
        

---管理会话

---父游标还是一个
SQL> select sql_text,sql_id,address,optimizer_mode,version_count from v$sqlarea where sql_text like '%force%count(a)%' and sql_text not like '%v$sql%';

SQL_TEXT                                           SQL_ID        ADDRESS  OPTIMIZER_ VERSION_COUNT
-------------------------------------------------- ------------- -------- ---------- -------------
select /*+ force1 */ count(a) from t_version where dxzum72xxhgk5 28FA2D94 ALL_ROWS               1
 a=:"SYS_B_0"    
 
 
---子游标还是一个
SQL> select sql_text,sql_id,address,child_address,child_number,optimizer_mode,parsing_schema_name  from v$sql where sql_text like '%force%count(a)%' and sql_text not like '%v$sql%';

SQL_TEXT                                           SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER OPTIMIZER_ PARSING_SCHEMA_NAME
-------------------------------------------------- ------------- -------- -------- ------------ ---------- ------------------------------
select /*+ force1 */ count(a) from t_version where dxzum72xxhgk5 28FA2D94 2DE1E7E0            0 ALL_ROWS   SCOTT
 a=:"SYS_B_0"
    
---用户管理
SQL> select * from v$sql_shared_cursor where address='28FA2D94';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
dxzum72xxhgk5 28FA2D94 2DE1E7E0            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

    
小结：
     1，最好采用cursor_sharing=force,这样父游标少，子游标也少；当然version_count就少了；会减少对应的library cache latch contention
      2,v$sqlarea.version_count对应子游标的个数即v$sql的个数