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語句要重新分析建立新執行計劃等等.那位能這個問題講清楚....^_^.