SQL ordered by Version Count

来源：互联网发布：新东方网络课编辑：程序博客网时间：2024/05/20 09:25

v$sql v$sqlarea v$sql_shared_cursor及父游标子游标

1. v$sql和v$sqlarea的区别

v$sql和v$sqlarea从某种意义上具有父子关系。即v$sqlarea保存的是父游标的sql信息，而v$sql保存的是子游标的sql的信息。在v$sqlarea里面有一列VERSION_COUNT字段，其中代表的就是对于此父游标的子游标的数量，也就是在v$sql里面的子游标的sql记录的数量。在v$sql中有一列CHILD_NUMBER字段，表示该字游标的编号。可以说v$sqlarea和v$sql是一对多的父子关系。

2. 父游标和子游标
每种类型的dml语句都需要如下阶段：
Create a Cursor 创建游标

Parse the Statement     分析语句
Bind Any Variables      绑定变量
Run the Statement       运行语句
Close the Cursor        关闭游标

当数据库第一次对一条SQL语句进行硬解析的时候，会在库缓存中分配一些内存，并将新产生的父游标保存进去。与父游标有关的关键信息是这个SQL语句的文本，这个时候，会在v$sqlarea里面插入一条记录。那么，在什么情况下会产生子游标呢，当数据库又碰到一条完全相同SQL语句，但是语句的执行计划和执行环境发生了变化，比如由于绑定变量窥测而产生的不一致的执行计划，由于SQL的初始化参数optimizer_mode的不同以及绑定变量分级的情况都会产生子游标，当产生子游标的时候，会在v$sql里面插入一条记录。并且v$sqlarea里的VERSION_COUNT字段的值会加1。

SQL> select * from v$sqlarea where version_count > 1000;

查询出来只有一条记录。

只有CHILD_ADDRESS, CHILD_NUMBER, ADDRESS这三个字段的值不一样。

SQL> SELECT distinct
2 ACL_MISMATCH, ANYDATA_TRANSFORMATION,
3 AUTH_CHECK_MISMATCH, BIND_MISMATCH, BIND_PEEKED_PQ_MISMATCH,
4 BIND_UACS_DIFF, BUFFERED_DML_MISMATCH, --CHILD_ADDRESS, CHILD_NUMBER, ADDRESS,
5 CROSSEDITION_TRIGGER_MISMATCH, CURSOR_PARTS_MISMATCH,
6 DESCRIBE_MISMATCH, DIFFERENT_LONG_LENGTH, DIFF_CALL_DURN,
7 EDITION_MISMATCH, EXPLAIN_PLAN_CURSOR, FLASHBACK_ARCHIVE_MISMATCH,
8 FLASHBACK_CURSOR, FLASHBACK_TABLE_MISMATCH, FORCE_HARD_PARSE,
9 INCOMPLETE_CURSOR, INCOMP_LTRL_MISMATCH, INST_DRTLD_MISMATCH,
10 INSUFF_PRIVS, INSUFF_PRIVS_REM, LANGUAGE_MISMATCH,
11 LITERAL_MISMATCH, LITREP_COMP_MISMATCH, LOAD_OPTIMIZER_STATS,
12 LOAD_RUNTIME_HEAP_FAILED, LOCK_USER_SCHEMA_FAILED, LOGICAL_STANDBY_APPLY,
13 LOGMINER_SESSION_MISMATCH, MULTI_PX_MISMATCH, MV_QUERY_GEN_MISMATCH,
14 MV_REWRITE_MISMATCH, MV_STALEOBJ_MISMATCH, NO_TRIGGER_MISMATCH,
15 OPTIMIZER_MISMATCH, OPTIMIZER_MODE_MISMATCH, OUTLINE_MISMATCH,
16 OVERLAP_TIME_MISMATCH, PDML_ENV_MISMATCH, PLSQL_CMP_SWITCHS_DIFF,
17 PLSQL_DEBUG, PQ_SLAVE_MISMATCH, PX_MISMATCH,
18 REMOTE_MAPPING_MISMATCH, REMOTE_TRANS_MISMATCH, ROLL_INVALID_MISMATCH,
19 ROW_LEVEL_SEC_MISMATCH, SLAVE_QC_MISMATCH, SQL_ID,
20 SQL_TYPE_MISMATCH, STATS_ROW_MISMATCH, STB_OBJECT_MISMATCH,
21 TOP_LEVEL_DDL_MISMATCH, TOP_LEVEL_RPI_CURSOR, TRANSLATION_MISMATCH,
22 TYPCHK_DEP_MISMATCH, TYPECHECK_MISMATCH, UNBOUND_CURSOR,
23 USER_BIND_PEEK_MISMATCH
24 FROM v$sql_shared_cursor
25 WHERE sql_id IN ('ayhn8kx4p011p');

ACL_MISMATCH ANYDATA_TRANSFORMATION AUTH_CHECK_MISMATCH BIND_MISMATCH BIND_PEEKED_PQ_MISMATCH BIND_UACS_DIFF BUFFERED_DML_MISMATCH CROSSEDITION_TRIGGER_MISMATCH CURSOR_PARTS_MISMATCH DESCRIBE_MISMATCH DIFFERENT_LONG_LENGTH DIFF_CALL_DURN EDITION_MISMATCH EXPLAIN_PLAN_CURSOR FLASHBACK_ARCHIVE_MISMATCH FLASHBACK_CURSOR FLASHBACK_TABLE_MISMATCH FORCE_HARD_PARSE INCOMPLETE_CURSOR INCOMP_LTRL_MISMATCH INST_DRTLD_MISMATCH INSUFF_PRIVS INSUFF_PRIVS_REM LANGUAGE_MISMATCH LITERAL_MISMATCH LITREP_COMP_MISMATCH LOAD_OPTIMIZER_STATS LOAD_RUNTIME_HEAP_FAILED LOCK_USER_SCHEMA_FAILED LOGICAL_STANDBY_APPLY LOGMINER_SESSION_MISMATCH MULTI_PX_MISMATCH MV_QUERY_GEN_MISMATCH MV_REWRITE_MISMATCH MV_STALEOBJ_MISMATCH NO_TRIGGER_MISMATCH OPTIMIZER_MISMATCH OPTIMIZER_MODE_MISMATCH OUTLINE_MISMATCH OVERLAP_TIME_MISMATCH PDML_ENV_MISMATCH PLSQL_CMP_SWITCHS_DIFF PLSQL_DEBUG PQ_SLAVE_MISMATCH PX_MISMATCH REMOTE_MAPPING_MISMATCH REMOTE_TRANS_MISMATCH ROLL_INVALID_MISMATCH ROW_LEVEL_SEC_MISMATCH SLAVE_QC_MISMATCH SQL_ID        SQL_TYPE_MISMATCH STATS_ROW_MISMATCH STB_OBJECT_MISMATCH TOP_LEVEL_DDL_MISMATCH TOP_LEVEL_RPI_CURSOR TRANSLATION_MISMATCH TYPCHK_DEP_MISMATCH TYPECHECK_MISMATCH UNBOUND_CURSOR USER_BIND_PEEK_MISMATCH
------------ ---------------------- ------------------- ------------- ----------------------- -------------- --------------------- ----------------------------- --------------------- ----------------- --------------------- -------------- ---------------- ------------------- -------------------------- ---------------- ------------------------ ---------------- ----------------- -------------------- ------------------- ------------ ---------------- ----------------- ---------------- -------------------- -------------------- ------------------------ ----------------------- --------------------- ------------------------- ----------------- --------------------- ------------------- -------------------- ------------------- ------------------ ----------------------- ---------------- --------------------- ----------------- ---------------------- ----------- ----------------- ----------- ----------------------- --------------------- --------------------- ---------------------- ----------------- ------------- ----------------- ------------------ ------------------- ---------------------- -------------------- -------------------- ------------------- ------------------ -------------- -----------------------
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                      N                 ayhn8kx4p011p 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                N                 N                    N                   N            N                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                 ayhn8kx4p011p N                 N                  N                   N                      N                    N                    N                   N                  N              N
N            N                      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                 N                      N           N                 N           N                       N                     N                     N                      N                 ayhn8kx4p011p N                 N                  N                   N                      N                    N                    N                   N                  N              N

SQL>

查询出来只有三条记录。

SQL> SELECT sql_id, count(1)
2    FROM v$sql_shared_cursor
3   WHERE sql_id IN ('fndaxz4n1gdsg', 'ayhn8kx4p011p')
4   group by sql_id
5 ;

SQL_ID          COUNT(1)
------------- ----------
ayhn8kx4p011p        658
fndaxz4n1gdsg        201

SQL>

没有发现什么不同却产生了太多的Version Count。