by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

동적 성능 뷰




개요

  • 내부 디스크 구조와 메모리 구조에 대한 데이터를 제공하며 데이터베이스가 오픈되어 사용중인 동안 계속 갱신된다
  • 성능 문제를 트러블슈팅하는데 있어 가장 기본적이며 유용한 스냅샷 데이터이다.
  • 일부 뷰들은 프로파일링 기법을 통해서 더욱 의미있는 정보를 만들어 낼 수 있다.

버전별 동적 성능 뷰 확인

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을 통해 읽음으로써 필요한 라이브러리 캐시 객체를 인덱스를 통해 빠르게 읽어 훨씬 효율적이다.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.