今天问了ORACLE ACS们9i 和 10gV$SQL在功能上的不同之处

最新推荐文章于 2021-10-28 16:35:21 发布

netcaster

最新推荐文章于 2021-10-28 16:35:21 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： sql oracle parsing module action schema

本文链接：https://blog.youkuaiyun.com/netcaster/article/details/4521218

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文讨论了Oracle数据库9i与10g版本中V$SQL视图的变化，指出Oracle未明确文档化这些变化导致的影响，并对比了两个版本的V$SQL和GV$SQL视图的查询结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

大家都知道v$SQL在9i和10g其实执行结果是不同的，但ORACLE在这功能上区别

的描述，没有在任何文档中出现。其实对于我们客户是十分不对的。

今天我问了ORACLE ACS支持（每年可是花了不少钱在那里），他们说X$表是不公开的，

我和他们说V$SQL是DBA常用的VIEW，很多产品已经监测SCRIPT都将会因为这

一变化而显示不正确的结果，变化没有说ORACLE不对，但不和客户说这一变

话是ORACLE的责任，我们也不需要了解X$表发生了什么变化，但至少你要

告诉我9i的V$SQL和10g的V$SQL在功能上有哪些不同，不要让我们自己瞎猜。

不然我们只有认为这是BUG了。总算他们同意去调查（还要调查？？？），让

我们RAISE新的SR，他们将会在SR里回答。希望过些日子能有答复。

REFERNCE：

<<<<<<<<<< 9i >>>>>>>>>>>>>>>>>>>>
V$SQL
select SQL_TEXT , SHARABLE_MEM , PERSISTENT_MEM , RUNTIME_MEM , SORTS ,
LOADED_VERSIONS , OPEN_VERSIONS , USERS_OPENING , FETCHES , EXECUTIONS ,
USERS_EXECUTING , LOADS , FIRST_LOAD_TIME , INVALIDATIONS , PARSE_CALLS , DISK_READS ,
BUFFER_GETS , ROWS_PROCESSED , COMMAND_TYPE , OPTIMIZER_MODE , OPTIMIZER_COST ,
PARSING_USER_ID , PARSING_SCHEMA_ID , KEPT_VERSIONS , ADDRESS , TYPE_CHK_HEAP ,
HASH_VALUE , PLAN_HASH_VALUE, CHILD_NUMBER, MODULE, MODULE_HASH , ACTION ,
ACTION_HASH, SERIALIZABLE_ABORTS , OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME, OUTLINE_SID,
CHILD_ADDRESS, SQLTYPE, REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE,
LAST_LOAD_TIME, IS_OBSOLETE, CHILD_LATCH
from GV$SQL
where inst_id = USERENV('Instance')

GV$SQL
select inst_id,kglnaobj,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16, kglobt08+kglobt11, kglobt10, kglobt01,
decode(kglobhs6,0,0,1), decode(kglhdlmd,0,0,1), kglhdlkc, kglobt04, kglobt05, kglobpc6, kglhdldc,
substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), kglhdivc, kglobt12, kglobt13,
kglobt14, kglobt15, kglobt02, decode(kglobt32,        0, 'NONE',        1, 'ALL_ROWS',
2, 'FIRST_ROWS',        3, 'RULE',        4, 'CHOOSE','UNKNOWN'),
kglobtn0, kglobt17, kglobt18, kglhdkmk, kglhdpar, kglobtp0,
kglnahsh, kglobt30, kglobt09, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21,
kglobts2, kglobt06, kglobt07, kglobt28, kglhdadr, kglobt29,
decode(bitand(kglobt00,64),64, 'Y', 'N'),
decode(kglobsta,        1, 'VALID',        2, 'VALID_AUTH_ERROR',
       3, 'VALID_COMPILE_ERROR',        4, 'VALID_UNAUTH',        5,
       'INVALID_UNAUTH',        6, 'INVALID'), kglobt31,
substr(to_char(kglobtt0,'YYYY-MM-DD/HH24:MI:SS'),1,19), decode(kglobt33, 1, 'Y', 'N'), kglhdclt
from x$kglcursor
where
kglhdadr != kglhdpar
and   kglobt02 != 0
<<<<<<<<<< 10g >>>>>>>>>>>>>>>>>>>>

V$SQL
select SQL_TEXT , SQL_FULLTEXT , SQL_ID, SHARABLE_MEM , PERSISTENT_MEM ,
RUNTIME_MEM , SORTS , LOADED_VERSIONS , OPEN_VERSIONS , USERS_OPENING , FETCHES ,
EXECUTIONS , PX_SERVERS_EXECUTIONS , END_OF_FETCH_COUNT, USERS_EXECUTING , LOADS ,
FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS , DISK_READS , DIRECT_WRITES ,
BUFFER_GETS , APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME,
USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, ROWS_PROCESSED , COMMAND_TYPE ,
OPTIMIZER_MODE , OPTIMIZER_COST, OPTIMIZER_ENV, OPTIMIZER_ENV_HASH_VALUE,
PARSING_USER_ID , PARSING_SCHEMA_ID , PARSING_SCHEMA_NAME, KEPT_VERSIONS , ADDRESS ,
TYPE_CHK_HEAP , HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, SERVICE,
SERVICE_HASH, MODULE, MODULE_HASH , ACTION , ACTION_HASH ,
SERIALIZABLE_ABORTS , OUTLINE_CATEGORY, CPU_TIME, ELAPSED_TIME, OUTLINE_SID, CHILD_ADDRESS,
SQLTYPE, REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE, LAST_LOAD_TIME, IS_OBSOLETE,
CHILD_LATCH, SQL_PROFILE, PROGRAM_ID, PROGRAM_LINE#, EXACT_MATCHING_SIGNATURE,
FORCE_MATCHING_SIGNATURE, LAST_ACTIVE_TIME, BIND_DATA
from GV$SQL
where inst_id = USERENV('Instance')

GV$SQL
select inst_id,kglnaobj,kglfnobj,kglobt03,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16, kglobt08+kglobt11, kglobt10, kglobt01,
decode(kglobhs6,0,0,1), decode(kglhdlmd,0,0,1), kglhdlkc, kglobt04, kglobt05,
kglobt48, kglobt35, kglobpc6, kglhdldc,
substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), kglhdivc, kglobt12, kglobt13, kglobwdw, kglobt14, kglobwap, kglobwcc,
kglobwcl, kglobwui, kglobt42, kglobt43, kglobt15, kglobt02,
decode(kglobt32,   0, 'NONE',        1, 'ALL_ROWS',        2, 'FIRST_ROWS',        3,
   'RULE',     4, 'CHOOSE',           'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17,
kglobt18, kglobts4, kglhdkmk, kglhdpar, kglobtp0, kglnahsh, kglobt46, kglobt30,
kglobt09, kglobts5, kglobt48, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21,
kglobts2, kglobt06, kglobt07, decode(kglobt28, 0, to_number(NULL), kglobt28),
kglhdadr, kglobt29, decode(bitand(kglobt00,64),64, 'Y', 'N'),
decode(kglobsta,1, 'VALID',        2, 'VALID_AUTH_ERROR',        3, 'VALID_COMPILE_ERROR',
       4, 'VALID_UNAUTH',        5, 'INVALID_UNAUTH',        6, 'INVALID'),
        kglobt31, substr(to_char(kglobtt0,'YYYY-MM-DD/HH24:MI:SS'),1,19),
decode(kglobt33, 1, 'Y', 'N'), kglhdclt, kglobts3, kglobt44, kglobt45, kglobt47, kglobt49,
kglobcla, kglobcbca
from x$kglcursor_child