- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=20021294&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
개요
- 내부 디스크 구조와 메모리 구조에 대한 데이터를 제공하며 데이터베이스가 오픈되어 사용중인 동안 계속 갱신된다
- 성능 문제를 트러블슈팅하는데 있어 가장 기본적이며 유용한 스냅샷 데이터이다.
- 일부 뷰들은 프로파일링 기법을 통해서 더욱 의미있는 정보를 만들어 낼 수 있다.
버전별 동적 성능 뷰 확인
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production SQL> select * from v$fixed_table; 1771 rows selected. SQL> select * from v$fixed_table where name like 'V$%' NAME OBJECT_ID TYPE TABLE_NUM ------------------------------ ---------- ----- ---------- V$SECUREFILE_TIMER 4294953028 VIEW 65537 V$DNFS_CHANNELS 4294953030 VIEW 65537 V$CELL_STATE 4294953118 VIEW 65537 491 rows selected. SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> select * from v$fixed_table; 1393 rows selected. SQL> select * from v$fixed_table where name like 'V$%' NAME OBJECT_ID TYPE TABLE_NUM ------------------------------ ---------- ----- ---------- V$SYSTEM_FIX_CONTROL 4294952716 VIEW 65537 V$SESSION_FIX_CONTROL 4294952718 VIEW 65537 V$ENCRYPTION_WALLET 4294952721 VIEW 65537 399 rows selected.
교재에서 다루는 동적 성능 뷰
| 뷰 | 설명 |
|---|---|
| V$SESSION | 세션에 대한 정보 |
| V$SESSION_WAIT | 세션의 현재 대기 상태에 대한 정보 |
| V$SESSION_EVENT | 세션의 현재 시점까지의 대기 이벤트별 누적 정보 |
| V$SESSTAT | 세션의 현재 시점까지의 통계별 누적 정보 |
| V$SESS_TIME_MODEL | 세션의 현재 시점까지의 타임 모델 정보 |
| V$SES_OPTIMIZER_ENV | 세션의 옵티마이저 환경 변수 |
| V$SYSSTAT | 시스템의 현재 시점까지의 통계별 누적 정보 |
| V$SYSTEM_EVENT | 시스템의 현재 시점까지의 대기 이벤트별 누적 정보 |
| V$SYS_TIME_MODEL | 시스템의 현재 시점까지의 타임 모델 정보 |
| V$SYS_OPTIMIZER_ENV | 시스템의 옵티마이저 환경 변수 |
| V$PROCESS | 서버 프로세스 정보 |
| V$SQLAREA | 부모 커서 정보 |
| V$SQL | 자식 커서 정보 |
| V$SQL_OPTIMIZER_ENV | SQL의 옵티마이저 환경 변수 |
| V$SQL_PLAN | 커서별 실행 계획 정보 |
| V$SQL_PLAN_STATISTICS | 각 실행 계획의 Plan Statistics 정보 |
| V$SQL_SHARED_CURSOR | 각 차일드 커서의 분기 이유 정보 |
| V$SQL_BIND_CAPTURE | 각 커서의 바인드 캡처 정보 |
| V$OBJECT_DEPENDENCY | LCO간 의존 관계 정보 |
| V$EVENT_NAME | 대기 이벤트에 대한 정보 |
| V$LATCH | 래치별 누적 획득 정보 |
| V$LATCH_CHIDRLEN | 각 차일드 래치별 누적 획득 정보 |
| V$LATCHHOLDER | 현재 시점의 래치 홀더 정보 |
| V$LATCH_MISSES | 래치 획득 실패에 대한 누적 정보 |
| V$LOCK | 현재 시점의 락(Enqueue) 획득과 대기 정보 |
| V$LOCK_TYPE | 락(Enqueue)에 대한 정보 |
| V$MUTEX_SLEEP | 각 뮤텍스별 누적 대기 정보 |
| V$MUTEX_SLEEP_HISTORY | 각 뮤텍스별 대기에 대한 시계열 데이터 |
| V$ROWCACHE | 딕셔너리 캐시에 대한 누적 통계 정보 |
| V$ROWCACHE_PARENT | 현재 시점의 딕셔너리에 대한 획득과 대기 정보 |
| V$LIBRARYCACHE | 라이브러리 캐시에 대한 누적 통계 정보 |
| V$SGA_RESIZE_OPS | SGA 자동 크기 변경에 대한 누적 통계 정보 |
| V$SGA_CURRENT_RESIZE_OPS | 현재 자동 크기 변경에 의해 크기가 변하고 있는 SGA 컴포넌트 정보 |
| V$OSSTAT | OS 지표별 누적 통계 정보 |
| V$BH | 버퍼 헤더 정보 |
교재에서 다루는 동적 성능 뷰의 베이스 테이블
| 베이스테이블 | 설명 |
|---|---|
| X$KSPPI, X$KSPPCV | 파라메터 정보, V$PARAMETER 뷰의 베이스 테이블 |
| X$KQRST | 딕셔너리 캐시 정보, V$ROWCACHE 뷰의 베이스 테이블 |
| X$KGLLK | Library Cache Lock 정보 |
| X$KGLPN | Library Cache Pin 정보 |
| X$KGLOB | LCO(Library Cache Object) 정보 |
| X$BH | 버퍼 헤더 정보, V$BH 뷰의 베이스 테이블 |
| X$KCBSW, X$KCBWH, X$KCBUWHY, X$KCBWH | 버퍼 획득(Buffer Get) 정보 |
| X$KSLLW | 래치 획득 장소 (Location) 정보 |
| X$KSUPRLAT | 래치 획득 정보, V$LATCHHOLDER 뷰의 베이스 테이블 |
| X$KSUSE | 세션 정보, V$SESSION 뷰의 베이스 테이블 |
| X$KSMSS, X$KSMLS, X$KSMJS, X$KSMSTRS | 공유 풀 정보, V$SGASTAT 뷰의 베이스 테이블 |
동적 성능 뷰의 정의 알아내기
- V$FIXED_VIEW_DEFINITION 뷰 조회하여 베이스 테이블을 확인
- GV$XXX 와 같은 형태의 검색 조건을 사용하여 조회
SQL> desc v$fixed_view_definition; Name Null? Type ----------------------------------------- -------- ---------------------------- VIEW_NAME VARCHAR2(30) VIEW_DEFINITION VARCHAR2(4000) -- EX) V$BH 뷰의 정의 SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$BH'; VIEW_DEFINITION ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------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,'securefile', 14,'siop',15,'recckpt'), 0, 0, 0, bh.le_addr, name,le_class, decode(bitand(flag,1), 0, 'N', 'Y'), decode(bitand(flag,16), 0, 'N', 'Y'), decode(bitand(flag,1536), 0, 'N', 'Y'), decode(bitand(flag,16384), 0, 'N', 'Y'), decode(bitand(flag,65536), 0, 'N', 'Y'), 'N', obj, ts#, lobid from x$bh bh, x$le le where bh.le_addr = le.le_addr (+)
V$뷰 대신 X$테이블을 사용해야 하는 경우
- 성능 면에서 더 효과적인 경우
- V$뷰가 제공하지 않는 정보를 제공하는 경우
성능 면에서 더 효과적인 경우
-- 버퍼 캐시에 존재하는 버퍼 헤더의 목록을 보여주는 V$BH뷰와 베이스테이블인 X$BH 테이블에서 특정 블록의 버퍼 헤더 정보를 검색하는 쿼리의 실행 계획을 비교 SQL> explain plan for 2 select * from v$bh 3 where file#=:b1 and block#=:b2 4 ; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 798134341 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 142 | 1 (100)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 1 | 142 | 1 (100)| 00:00:01 | |* 2 | FIXED TABLE FULL| X$BH | 1 | 110 | 0 (0)| 00:00:01 | | 3 | FIXED TABLE FULL| X$LE | 100 | 3200 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------- SQL> explain plan for 2 select * from sys.x$bh 3 where dbarfil=:b1 and dbablk=:b2 4 ; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1818235433 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 648 | 0 (0)| 00:00:01 | |* 1 | FIXED TABLE FULL| X$BH | 1 | 648 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------- * X$BH 테이블을 직접 조회하는 것이 더 간단하고 효율적인 실행 계획을 보인다. * V$뷰는 여러개의 X$테이블을 조인하는 쿼리로 정의되는 경우가 많아, 비효율적인 실행 계획을 가질 수도 있다. 이런 경우 X$테이블을 직접 이용함으로써 쿼리의 성능을 개선할 수 있다.
V$뷰가 제공하지 않는 정보를 제공하는 경우
-- V$SGASTAT 뷰가 제공하는 정보는 풀이름, 컴포넌트명, 각 컴포넌트가 자치하는 크기이다. 공유 풀의 크기가 큰 경우에는 경합을 줄이기 위해 여러개의 서브 풀로 나누어 관리한다. 하지만 V$SGASTAT뷰에는 서브 풀 번호는 확인할 수 없다. SQL> desc v$sgastat Name Null? Type ----------------------------------------- -------- ---------------------------- POOL VARCHAR2(12) NAME VARCHAR2(26) BYTES NUMBER -- V$SGASTAT뷰 정의를 조회해본다. SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SGASTAT'; VIEW_DEFINITION -------------------------------------------------------------------------------- select inst_id,'',ksmssnam,ksmsslen from x$ksmfs where ksmsslen>1 union all select inst_id,'shared pool',ksmssnam, sum(ksmsslen) from x$ksmss where ksmsslen>1 group by inst_id, 'shared pool', ksmssnam --공유풀 정보 union all select inst_id,'largepool',ksmssnam, sum(ksmsslen) from x$ksmls where ksmsslen>1 group by inst_id, 'large pool', ksmssnam union all select inst_id,'java pool',ksmssnam, sum(ksmsslen) from x$ksmjs where ksmsslen>1 group by inst_id, 'java pool', ksmssnam union all select inst_id,'streams pool',ksmssnam, sum(ksmsslen) from x$ksmstrs where ksmsslen>1 group by inst_id, 'streams pool', ksmssnam -- 공유 풀 영역의 정보는 X$KSMSS 테이블에 해당된다. 서브 풀과 관련된 정보는 X$KSMSS테이블의 KSMDSIDX컬럼의 데이터를 확인한다. SQL> desc sys.x$ksmss Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER KSMSSLEN NUMBER KSMSSNAM VARCHAR2(26) KSMDSIDX NUMBER
동적 성능 뷰 검색시 성능 고려
- 동적 성능 뷰가 제공하는 데이터는 공유풀에 존재하는 것으로 메모리 구조를 탐색하기 때문에 성능면에서 효과적이다.
- 그러나 메모리 영역이라고 하더라도 비효율적으로 많은 데이터를 읽는 경우는 CPU를 과도하게 사용하면서 성능에 악영향을 준다
- 특히 오라클의 경우 공유 메모리 영역을 읽을때는 래치나 뮤텍스를 획득하도록 되어 있기 때문에 비효율적으로 많은 메모리영역을 읽으면 치명적인 래치 경합이나 뮤텍스 경합을 유발할 수 있다
동적 성능 뷰 검색조건에 따른 성능 비교 예제
-- 1) SQL_TEXT 컬럼을 검색조건으로 V$SQLAREA 뷰 조회 SQL> explain plan for 2 select * from v$sqlarea 3 where sql_text like 'SELECT /* cafe.conf (TStyleConfigItemHandler_SELECT_ALL_ITEMCOUNT) */%' 4 ; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 232555890 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5574 | 0 (0)| 00:00:01 | |* 1 | FIXED TABLE FULL| X$KGLCURSOR_CHILD_SQLID | 1 | 5574 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- -- SQL_TEXT컬럼을 검색조건으로 V$SQLAREA 뷰를 조회하면 X$KGLCURSOR_CHILD_SQLID 테이블을 TABLE FULL SCAN 한다. 즉 공유풀의 라이브러리 캐시 영역을 전부 액세스 한다. -- 2) SQL_ID 컬럼을 검색조건으로 V$SQLAREA 뷰 조회 SQL> explain plan for 2 select * from v$sqlarea 3 where sql_id = '99t3dq7h4h0gx'; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- Plan hash value: 4100860936 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5574 | 0 (0)| 00:00:01 | |* 1 | FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD_SQLID (ind:2 | 1 | 5574 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- -- SQL_ID 컬럼을 검색조건으로 V$SQLAREA 뷰를 조회하면 X$KGLCURSOR_CHILD_SQLID 테이블을 TABLE FULL SCAN이 아닌 INDEX RANGE SCAN을 통해 읽음으로써 필요한 라이브러리 캐시 객체를 인덱스를 통해 빠르게 읽어 훨씬 효율적이다.
 | Oracle Dynamic Performance Views Reference URL : http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1.htm#g1540635 |
문서에 대하여
- 최초작성일 : 2012년 03월 03일
- 이 문서는 오라클클럽 오라클 성능 트러블슈팅의 기초 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 엑셈(EXEM) 에서 출간한 '오라클 성능 트러블슈팅의 기초'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=20021294&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.