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

 

大家都知道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