자수성가한 부자

Oracle 의 뷰가 만들어지는 과정

DBA 생활을 하면서  우리가 단순히 사용하는 DBA_, USER_ , ALL_, V$ 등을 제대로 알고 어떻게 만들어지는지에 대한 정의를 한 문서를 본적이 없다.

따라서 최근에 읽기 시작한 "secret oracle"라는 책을 토대로해서 한번 정리해 보는 기회를 가졌다. 

아래는 오라클에서 만들어지는 V$ 뷰들의 layer architecture 를 표현한 그림이다.

맨 아래에 x$ 테이블을 토대로 위의 view나 시노님들이 만들어진다.

그렇다면 우리가 쉽게 조회하는 딕셔너리 뷰 및 v$ 뷰는 결국에는 x$ 테이블의 데이타를 참고하는 view나 시노님에 불과하다.

특히나 우리가 쉽게 조회하는 V$ 뷰는 결국에는 퍼블릭 시노님에 불과하다는 것을 알수 있다.

V$ VIEW라는 이름보다는 V$ 시노님이 더 어울리지 않을까?

TOP-DOWN방식으로 찾아본 X$ 테이블

그렇다면 여기서 우리가 초기화파라미터를 조회하기 위해서 흔히들 조회하는 

V$PARAMETER가 어떠한 x$ 테이블을 조회하는지 다이나믹 뷰의 원천 테이블을 찾아보도록 하자.

SQL> desc v$parameter
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(512)
 DISPLAY_VALUE                                      VARCHAR2(512)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER

1) V$PARAMETER SYNONYM

v$parameter는 sys소유의 v_$parameter 뷰를  public 시노님으로 생성되어 놓은 것에 불과하다는 것을 알수 있다.  

SQL> SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYMS

         WHERE SYNONYM_NAME ='V$PARAMETER';

OWNER    SYNONYM_NAME     TABLE_OWNER     TABLE_NAME
------------------------------------------------------------
PUBLIC    V$PARAMETER        SYS                     V_$PARAMETER

2) SYS.V_$PARAMETER 뷰

V_$PARAMETER 뷰의 정의를 찾아보자.

이 뷰는 SYS.V$PARAMETER 로 생성된 것을 알수 있다.

SQL> SELECT OWNER,TEXT FROM DBA_VIEWS WHERE VIEW_NAME ='V_$PARAMETER';

OWNER    TEXT

-------------------------------------------------
SYS         select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEFAULT","ISSES_MODIFIABLE","ISSYS_MODIFIABLE","

              ,"ISINSTANCE_MODIFIABLE","ISMODIFIED","ISADJUSTED","ISDEPRECATED","DESCRIPTION","UPDATE_COMMENT",

              "HASH"   from v$parameter

3) SYS.V$PARAMETER FIXED VIEW

그럼 FIXED VIEW 인 V$PARAMETER 의 구성을 찾아보자.

아래의 V$FIXED_VIEW_DEFINITION 뷰를 조회해보면 GV$PARAMETER를 가지고 있는것을 알수 있다.

SQL> SELECT view_definition FORMAT FROM V$FIXED_VIEW_DEFINITION
    2   WHERE view_name='V$PARAMETER';

FORMAT
--------------------------------------------------------------------------------
select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT
ED, DESCRIPTION, UPDATE_COMMENT, HASH  from GV$PARAMETER where inst_id = USERENV
('Instance')

GV$PARAMETER를 위의 1~ 3번과정을 동일하게 조회된다.

마지막에 SYS.GV$PARAMETER를 조회하면 해당되는 X$ 테이블을 알 수 있다.

SELECT * FROM V$FIXED_VIEW_DEFINITION
WHERE VIEW_NAME='GV$PARAMETER'

View_name 

--------------------

GV$PARAMETER 

View_definition

----------------------------------------------------------------------------------------------------

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,  decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),  decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',                                  3,'IMMEDIATE','FALSE'),  decode(bitand(ksppiflg,4),4,'FALSE',                                     decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),     decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash  from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and  ((translate(ksppinm,'_','#') not like '##%') and    ((translate(ksppinm,'_','#') not like '#%')      or (ksppstdf = 'FALSE') or      (bitand(ksppstvf,5) > 0)))

위의 과정들을 귀찮으니 한방에 어떠한 X$테이블인지 찾을려면

V$FILESTAT 의 예를 보면  먼저 GV$FILESTAT 로 변환해서 위의 V$FIXED_VIEW_DEFINITION 을 조회하면 되겠다.

View_name 

------------

View_definition

---------------------

GV$FILESTAT 

select k.inst_id, k.kcfiofno,k.kcfiopyr,k.kcfiopyw,k.kcfiopbr,k.kcfiopbw, k.kcfiosbr,k.kcfioprt,k.kcfiopwt,k.kcfiosbt,k.kcfioavg,k.kcfiolst,k.kcfiomin, k.kcfiormx,k.kcfiowmx from x$kcfio k,x$kccfe f where f.fedup <> 0 and  f.fenum=k.kcfiofno

X$ -> GV$ 뷰-> GV_$ 뷰-> GV$ 시노님 -> V$ 뷰 -> V_$ 뷰 -> V$ 시노님

과정을 거친다.

그렇다면 X$ 테이블의 정체는 무엇인가?

X$ 테이블은 오라클의 각종 뷰가 참조되는 원천 테이블이자 C 프로그램으로 작성된 Oracle Kernel 에 존재하는 2차원(2-dimension) array 형태의 데이타이다. 절대로 보호되어야 하는 데이타이고 따라서 당근 SYS유저만이 접근이 가능하다.  

거의 X$ 테이블에 대해서는 몇몇 테이블들(x$bh,x$ksppi, x$ksppsv 등)  일부 공개가 되어 있고 대부분 internal 그 의미와 용도가 숨겨져 있다.

다만 추측으로 어떠한 테이블인지에 대한 정보가 제공될 뿐이다.

위의 정보를 토대로 v$filestat가 참조하는 x$ 테이블인 x$kcfio ,x$kccfe 의 뜻을 대충 살펴보자면 kernel cache의 file i/o에 관련된 테이블과  Kernel Cache Control file에 관련된 구조정보를 조인하는 문구정도로 추측할 수 있을 것이다.

참고문헌: Secret Oracle (by Robert Debes)