cursor_sharing與v$sqlarea_v$sql_v$sql_shared_cursor

---查詢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的個數