Oracle V$ 와 X$ 뷰의 구조
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 Oracle V$ 와 X$ 뷰의 구조[편집]
- 맨 하단의 SYS.x$ 테이블을 기반으로 view 나 시노님들이 만들어짐
- 딕셔너리 뷰 및 V$ 뷰는 x$ 테이블의 데이타를 참고하는 view나 시노님 임.
- V$ VIEW는 뷰가 아닌 V$ 시노님이 더 맞지 않을까?
1.1 V$PARAMETER 뷰의 원본 테이블[편집]
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.1.1 DBA_SYNONYMS 에서 확인[편집]
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
- v$parameter는 sys소유의 v_$parameter 뷰를 public 시노님으로 생성 함.
1.1.2 DBA_VIEWS 에서 찾기[편집]
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
- SYS.V$PARAMETER 로 생성됨.
1.1.3 SYS.V$PARAMETER FIXED VIEW[편집]
SELECT view_definition FORMAT
FROM V$FIXED_VIEW_DEFINITION
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')
1.1.4 V$FIXED_VIEW_DEFINITION 뷰 조회[편집]
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)))
약어 | 의미(추정) |
---|---|
K | Kernel |
KC | Kernel Cache |
KCB | Kernel Cache buffer |
KCBW | Kernel Cache buffer wait |
KCC | Kernel Cache Control file |
KCCB | Kernel Cache Control Backup |
KCCF | Kernel Cache Copy Flash Recovery Area |
KCCDC | Kernel Cache Control file Data file Copy |
KCP | Kernel Cache transPortable tablespace |
KCR | Kernel Cache Redo |
KCT | Kernel Cache insTance |
KG | Kernel Generic |
KGL | Kernel Generic Library Cache |
KCLJ | Kernel Generic Library Cache java |
KS | Kernel Service |
KSB | Kernel Service Background |
KSM | Kernel Service Memory |
KSU | Kernel Service User |
KSUSE | Kernel Service User SEssion |
KSUSECON | Kernel Service User SEssion COnnection |
KSUSEH | Kernel Service User SEssion History |
KT | Kernel Transaction |
KTU | Kernel Transaction Undo |
KX | Kernel eXecution |
KXS | Kernel eXecution Pool |
- v$filestat가 참조하는 x$ 테이블인 x$kcfio ,x$kccfe 는 kernel cache의 file i/o에 관련된 테이블과 Kernel Cache Control file에 관련된 구조정보를 조인하는 문구 정도로 추측할 수 있음.