20171229V$DB_OBJECT_CACHE type='INDEX'

[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语句要重新分析建立新执行计划等等.那位能这个问题讲清楚....^_^.

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

转载于:http://blog.itpub.net/267265/viewspace-2149479/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值