oracle11g v$sql_v$sqlarea_version_count测试

最新推荐文章于 2022-11-16 19:00:00 发布

clg10051

最新推荐文章于 2022-11-16 19:00:00 发布

阅读量273

点赞数

文章标签：数据库 ui

--测试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';

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

--运行业务会话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';

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-763828/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-763828/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle11g v$sql_v$sqlarea_version_count测试

--测试version_count相关原因--业务会话SQL> conn scott/systemConnected.SQL> create table t_version(a int);Table crea...
复制链接

扫一扫