oracle11g v$sql_v$sqlarea_version_count測試

--測試version_count相關原因

--業務會話
SQL> conn scott/system
Connected.
SQL> create table t_version(a int);

Table created.

SQL> select count(1) from t_version;

  COUNT(1)
----------
         0
        
--另一個業務會話        
SQL> conn wisdomone/system
Connected.
SQL> create table t_version(a int);

Table created.

SQL> select count(1) from t_version;

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

 

 

 

--管理會話,上述不同使用者執行相同的sql，在v$sqlarea僅一條記錄
select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like '%select count(1) from t_version%'

SQL_TEXT                                 SQL_ID        ADDRESS  HASH_VALUE
---------------------------------------- ------------- -------- ----------
select sql_text,sql_id,address,hash_valu b5ab8brs7888k 28EE62AC 4034142482
e from v$sqlarea where sql_text like '%s
elect count(1) from t_version%'

select count(1) from t_version           9prcth1x0rx0j 28F355CC 2047603729

--執行業務會話1後的情況
SQL> select * from v$sql_shared_cursor where address='28F355CC';

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
- - - - -
9prcth1x0rx0j 28F355CC 28F35348            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

--執行業務會話2後的情況
SQL> select * from v$sql_shared_cursor where address='28F355CC';

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
- - - - -
9prcth1x0rx0j 28F355CC 28F35348            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

9prcth1x0rx0j 28F355CC 28E8FC7C            1 N N N N N N N N N N N N N Y N N N Y
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

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
- - - - -

--執行業務會話2後的情況
select sql_text,sql_id,address,child_address,child_number from v$sql where sql_id='9prcth1x0rx0j'

SQL_TEXT                       SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------------------------ ------------- -------- -------- ------------
select count(1) from t_version 9prcth1x0rx0j 28F355CC 28F35348            0
select count(1) from t_version 9prcth1x0rx0j 28F355CC 28E8FC7C            1

SQL>

--經查閱文件
https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=48219702870355&id=296377.1&_afrWindowMode=0&_adf.ctrl-state=qbci1jsq5_100

--原因為兩個物件所屬使用者不同導致二物件不同，即object_id不同，所以轉化不匹配
TRANSLATION_MISMATCH VARCHAR2(1) (Y|N) The base objects of the existing child cursor do not match

--最佳化器模式變化也會導致version_count加大

--當最佳化器模式為all_rows
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SQL> conn scott/system
Connected.
SQL> select count(1) from t_version;

  COUNT(1)
----------
         0
        
        
SQL> select sql_text,sql_id,address,hash_value,optimizer_mode from v$sqlarea where sql_text like '%select count(1) from t_version%';
SQL_TEXT                       SQL_ID        ADDRESS  HASH_VALUE OPTIMIZER_
------------------------------ ------------- -------- ---------- ----------

select count(1) from t_version 9prcth1x0rx0j 28F355CC 2047603729 ALL_ROWS

--變更最佳化器模式
SQL> alter session set optimizer_mode=first_rows;

Session altered.

SQL> select count(1) from t_version;

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

--變更最佳化器模式後情況        
SQL> select sql_text,sql_id,address,child_address,child_number,optimizer_mode,parsing_schema_name  from v$sql where sql_id='9prcth1x0rx0j';

SQL_TEXT                       SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------------------------ ------------- -------- -------- ------------
OPTIMIZER_ PARSING_SCHEMA_NAME
---------- ------------------------------
select count(1) from t_version 9prcth1x0rx0j 28F355CC 28F35348            0
ALL_ROWS   SCOTT

select count(1) from t_version 9prcth1x0rx0j 28F355CC 28E8FC7C            1
ALL_ROWS   WISDOMONE

select count(1) from t_version 9prcth1x0rx0j 28F355CC 28FC02A0            2
FIRST_ROWS SCOTT      

---更改最佳化器模式後情況
SQL> select * from v$sql_shared_cursor where address='28F355CC';

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
- - - - -
9prcth1x0rx0j 28F355CC 28F35348            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

9prcth1x0rx0j 28F355CC 28E8FC7C            1 N N N N N N N N N N N N N Y N N N Y
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

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
- - - - -

9prcth1x0rx0j 28F355CC 28FC02A0            2 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 N N N N N N N
N N N N N

--上述的最佳化器模式變化導致不匹配
OPTIMIZER_MODE_MISMATCH VARCHAR2(1) (Y|N) Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)

SQL> select sql_text,sql_id,address,hash_value,version_count from v$sqlarea where sql_text like '%select count(1) from t_version%';
SQL_TEXT                       SQL_ID        ADDRESS  HASH_VALUE VERSION_COUNT
------------------------------ ------------- -------- ---------- -------------
select count(1) from t_version 9prcth1x0rx0j 28F355CC 2047603729             3

6 rows selected.

小結：
     1,v$sqlarea僅儲存sql的文字
     2,v$sqlarea對應sql的父遊標
     3,v$sql對應sql父遊標的子游標；每個子游標由sql的引用物件的使用者及會話最佳化器模式及其它相關屬性決定子游標的個數；及是否可以共享；
     4,每個子游標是否共享及不能共享原因可以查詢v$sql_shared_cursor
     5,子游標個數即version_count