[20171229]V$DB_OBJECT_CACHE type='INDEX'.txt
--//前几天一直在使用V$DB_OBJECT_CACHE视图查询FULL_HASH_VALUE,验证自己推断FULL_HASH_VALUE如何计算.
--//我无意中发现一个奇怪的现象,就是查询这个视图type='INDEX',除了owner='SYS'外,其它owner几乎无法查询到.
--//自己今天做一些探究.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select * from V$DB_OBJECT_CACHE where type='INDEX' and owner<>'SYS';
no rows selected
--//我的测试环境type='INDEX' and owner<>'SYS'一个都没有.
SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where TYPE='INDEX' and owner='SYS' and rownum<=5;
OWNER NAME NAMESPACE TYPE HASH_VALUE FULL_HASH_VALUE STATUS
------ -------------------- ---------- ----- ---------- -------------------------------- ------
SYS I_OBJ#_INTCOL# INDEX INDEX 3598591747 434a51e7d9b780fb00a6906ed67e2703 VALID
SYS I_H_OBJ#_COL# INDEX INDEX 1338804478 31fb0006138102d78beac8f44fcc88fe VALID
SYS I_HH_OBJ#_INTCOL# INDEX INDEX 2773272724 19a7b13c095ea769459763c8a54cc894 VALID
SYS I_HH_OBJ#_COL# INDEX INDEX 1377242932 ad9ade920cb128a84a880e5e52170f34 VALID
SYS I_TAB_STATS$_OBJ# INDEX INDEX 1950176892 55daa9210606e2e2020b4252743d567c VALID
--//我仔细检查一个索引发现一个规律,就是这些索引对应的表都是分区表或者是cluster表,非分区仅仅
--//IND_STATS$,TAB_STATS$,HIST_HEAD$,FIXED_OBJ$,HISTGRM$(cluster表).
--//我也查询生产系统,普通用户的索引在V$DB_OBJECT_CACHE视图中也不存在,因为我们开发不用分区表.
select owner,table_name,cluster_name,PARTITIONED from dba_tables where (owner,table_name) in
(SELECT table_owner, table_name
FROM dba_indexes
WHERE (owner, index_name) IN (SELECT owner, name
FROM V$DB_OBJECT_CACHE
WHERE TYPE = 'INDEX' AND owner = 'SYS'));
OWNER TABLE_NAME CLUSTER_NAME PAR
------ ------------------------------ ------------------------------ ---
SYS WRH$_SERVICE_STAT YES
SYS WRH$_ACTIVE_SESSION_HISTORY YES
SYS WRH$_SERVICE_WAIT_CLASS YES
SYS WRH$_WAITSTAT YES
SYS WRH$_DB_CACHE_ADVICE YES
SYS IND_STATS$ NO
SYS WRH$_SQLSTAT YES
SYS WRH$_TABLESPACE_STAT YES
SYS WRH$_ROWCACHE_SUMMARY YES
SYS WRH$_PARAMETER YES
SYS TAB_STATS$ NO
SYS WRH$_MVPARAMETER YES
SYS HISTGRM$ C_OBJ#_INTCOL# NO
SYS HIST_HEAD$ NO
SYS WRH$_SYS_TIME_MODEL YES
SYS WRH$_SYSSTAT YES
SYS WRH$_SYSTEM_EVENT YES
SYS WRH$_EVENT_HISTOGRAM YES
SYS WRH$_FILESTATXS YES
SYS WRH$_SGASTAT YES
SYS FIXED_OBJ$ NO
SYS WRH$_LATCH YES
SYS WRH$_LATCH_MISSES_SUMMARY YES
SYS WRH$_OSSTAT YES
SYS WRH$_SEG_STAT YES
25 rows selected.
--//难道仅仅分区表的索引才会在 V$DB_OBJECT_CACHE存在吗?而且其对应的基表是sys.x$kglob,仅仅加一个条件where kglnaobj IS NOT NULL.
2.测试在普通用户建立分区表以及索引看看.
create table users
(region_code varchar2(3),
username varchar2(30),
account_status varchar2(32),
created date,
profile varchar2(128))
partition by range (region_code)
(partition a_m values less than ('N'),
partition n_r values less than ('S'),
partition s_z values less than (MAXVALUE));
insert into users select substr(username,1,3), username, account_status, created, profile from dba_users;
exec dbms_stats.gather_table_stats('','USERS');
3.建立索引看看:
SCOTT@book> create unique index users_username_u1 on users(username) global;
Index created.
SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where TYPE='INDEX' and owner='SCOTT' and NAME='USERS_USERNAME_U1' and rownum<=5;
OWNER NAME NAMESPACE TYPE HASH_VALUE FULL_HASH_VALUE STATUS
------ -------------------- ---------- ------ ---------- -------------------------------- ------
SCOTT USERS_USERNAME_U1 INDEX INDEX 2235571155 eaad7ced006dd3d2a5f20cc085401bd3 VALID
SCOTT@book> drop index users_username_u1;
Index dropped.
SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where TYPE='INDEX' and owner='SCOTT' and NAME='USERS_USERNAME_U1' and rownum<=5;
OWNER NAME NAMESPACE TYPE HASH_VALUE FULL_HASH_VALUE STATUS
------ -------------------- ---------- ----- ---------- -------------------------------- ------
SCOTT USERS_USERNAME_U1 INDEX INDEX 2235571155 eaad7ced006dd3d2a5f20cc085401bd3 UNKOWN
--//status=UNKOWN
SCOTT@book> create index users_username_u1 on users(username) global;
Index created.
SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where TYPE='INDEX' and owner='SCOTT' and NAME='USERS_USERNAME_U1' and rownum<=5;
OWNER NAME NAMESPACE TYPE HASH_VALUE FULL_HASH_VALUE STATUS
------ -------------------- ---------- ----- ---------- -------------------------------- -------
SCOTT USERS_USERNAME_U1 INDEX INDEX 2235571155 eaad7ced006dd3d2a5f20cc085401bd3 VALID
4.继续测试,建立本地索引看看:
SCOTT@book> drop index users_username_u1;
Index dropped.
SCOTT@book> create index users_username_l1 on users(username) local;
Index created.
SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value,status from V$DB_OBJECT_CACHE where TYPE='INDEX' and owner='SCOTT' and NAME='USERS_USERNAME_L1' and rownum<=5;
OWNER NAME NAMESPACE TYPE HASH_VALUE FULL_HASH_VALUE STATUS
------ -------------------- ---------- ----- ---------- -------------------------------- ------
SCOTT USERS_USERNAME_L1 INDEX INDEX 2934347769 f6834aac7908d9d4184ee11daee697f9 VALID
--//似乎仅仅分区表出现该视图中,而剩下的表非常特殊:
select owner,table_name,cluster_name,PARTITIONED from dba_tables where PARTITIONED<>'YES' and (owner,table_name) in
(SELECT table_owner, table_name
FROM dba_indexes
WHERE (owner, index_name) IN (SELECT owner, name
FROM V$DB_OBJECT_CACHE
WHERE TYPE = 'INDEX' ));
OWNER TABLE_NAME CLUSTER_NAME PAR
------ ---------- ------------------------------ ---
SYS IND_STATS$ NO
SYS TAB_STATS$ NO
SYS HISTGRM$ C_OBJ#_INTCOL# NO
SYS HIST_HEAD$ NO
SYS FIXED_OBJ$ NO
--//也许这些表非常特殊吧,视乎都是表和索引直方图的统计表.因为你分析表,一些对象sql语句要重新分析建立新执行计划等等.那位能这个问题讲清楚....^_^.
