V$、V_$、G$、G_$、X$彻底剖析

一、V_$

1.V_$是sys用户下的视图，

scott@ZILING> select * from v$mystat where rownum<5;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        18          0          0
        18          1          1
        18          2          1
        18          3         28

已用时间:  00: 00: 00.01
scott@ZILING> select * from v_$mystat where rownum<5;
select * from v_$mystat where rownum<5
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在

已用时间:  00: 00: 00.03
scott@ZILING> select * from sys.v_$mystat where rownum<5;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        18          0          0
        18          1          1
        18          2          1
        18          3         37

2.V_$视图由G_$视图加where inst_id=USEREN('INSTANCE')组成

scott@ZILING>   select * from v$fixed_view_definition where instr(view_name,'$')=2 and rownum<5;

VIEW_NAME
------------------------------------------------------------
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------
V$WAITSTAT
select class,count,time from gv$waitstat where inst_id = USERENV('Instance')

V$BH
select file#, block#, class#, status, xnc, forced_reads, forced_writes, lock_element_addr, lock_elem
ent_name, lock_element_class, dirty, temp, ping, stale, direct, new, objd, ts#, lobid, cachehint  fr
om gv$bh where inst_id = USERENV('Instance')

二、V$是V_$视图的同义词

sys@ZILING> select * from DBA_SYNONYMS where rownum<5;

OWNER                SYNONYM_NAME         TABLE_OWNER          TABLE_NAME                     DB_LINK
-------------------- -------------------- -------------------- ------------------------------ --------------------
PUBLIC               V$PARALLEL_DEGREE_LI SYS                  V_$PARALLEL_DEGREE_LIMIT_MTH
                     MIT_MTH

PUBLIC               V$PARAMETER          SYS                  V_$PARAMETER
PUBLIC               V$PARAMETER2         SYS                  V_$PARAMETER2
PUBLIC               V$PARAMETER_VALID_VA SYS                  V_$PARAMETER_VALID_VALUES
                     LUES

三、G_$是全局视图，多由x$表得来

scott@ZILING>   select * from v$fixed_view_definition where instr(view_name,'$')=3 and rownum<3;

VIEW_NAME
------------------------------------------------------------
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------
GV$WAITSTAT
select inst_id,decode(indx,1,'data block',2,'sort block',3,'save undo block', 4,'segment header',5,'
save undo header',6,'free list',7,'extent map', 8,'1st level bmb',9,'2nd level bmb',10,'3rd level bm
b', 11,'bitmap block',12,'bitmap index block',13,'file header block',14,'unused', 15,'system undo he
ader',16,'system undo block', 17,'undo header',18,'undo block'), count,time from x$kcbwait where ind
x!=0

GV$BH
select bh.inst_id, file#, dbablk, class, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,
'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefi
le', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna'), 0, 0, 0, bh.le_addr,
le_id1, le_id2, decode(bitand(flag,1), 0, 'N', 'Y'), decode(bitand(flag,16), 0, 'N', 'Y'), decode(bi
tand(flag,1536), 0, 'N', 'Y'), decode(bitand(flag,16384), 0, 'N', 'Y'), decode(bitand(flag,65536), 0
, 'N', 'Y'), 'N', obj, ts#, lobid,  bitand(OBJ_FLAG, 240)/16 from x$bh bh, x$le le where bh.le_addr
= le.le_addr (+)

已用时间:  00: 00: 00.06

四、G$是G_$视图的同义词

scott@ZILING> select * from DBA_SYNONYMS where synonym_name like 'GV%' and rownum<5;

OWNER                SYNONYM_NAME         TABLE_OWNER          TABLE_NAME                     DB_LINK
-------------------- -------------------- -------------------- ------------------------------ --------------------
PUBLIC               GV_OLAPI_SESSION_HIS SYS                  GV_OLAPI_SESSION_HISTORY
                     TORY

PUBLIC               GV_OLAPI_MEMORY_OP_H SYS                  GV_OLAPI_MEMORY_OP_HISTORY
                     ISTORY

PUBLIC               GV_OLAPI_IFACE_OP_HI SYS                  GV_OLAPI_IFACE_OP_HISTORY
                     STORY

PUBLIC               GV_OLAPI_IFACE_OBJEC SYS                  GV_OLAPI_IFACE_OBJECT_HISTORY
                     T_HISTORY

已用时间:  00: 00: 00.42

五、X$是表

scott@ZILING>    select * from v$fixed_table where rownum<5;

NAME                            OBJECT_ID TYPE        TABLE_NUM
------------------------------ ---------- ---------- ----------
X$KQFTA                        4294950912 TABLE               0
X$KQFVI                        4294950913 TABLE               1
X$KQFVT                        4294951149 TABLE               2
X$KQFDT                        4294950914 TABLE               3

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

转载于:http://blog.itpub.net/25989950/viewspace-715243/