오라클 성능 트러블슈팅의 기초 (2012년)
동적 성능 뷰 0 0 47,676

by 구루비스터디 동적 성능 뷰 X$ V$ [2018.09.27]


개요

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


버전별 동적 성능 뷰 확인


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_ENVSQL의 옵티마이저 환경 변수
V$SQL_PLAN커서별 실행 계획 정보
V$SQL_PLAN_STATISTICS각 실행 계획의 Plan Statistics 정보
V$SQL_SHARED_CURSOR각 차일드 커서의 분기 이유 정보
V$SQL_BIND_CAPTURE각 커서의 바인드 캡처 정보
V$OBJECT_DEPENDENCYLCO간 의존 관계 정보
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_OPSSGA 자동 크기 변경에 대한 누적 통계 정보
V$SGA_CURRENT_RESIZE_OPS현재 자동 크기 변경에 의해 크기가 변하고 있는 SGA 컴포넌트 정보
V$OSSTATOS 지표별 누적 통계 정보
V$BH버퍼 헤더 정보


교재에서 다루는 동적 성능 뷰의 베이스 테이블

베이스테이블설명
X$KSPPI, X$KSPPCV파라메터 정보, V$PARAMETER 뷰의 베이스 테이블
X$KQRST딕셔너리 캐시 정보, V$ROWCACHE 뷰의 베이스 테이블
X$KGLLKLibrary Cache Lock 정보
X$KGLPNLibrary Cache Pin 정보
X$KGLOBLCO(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을 통해 읽음으로써 필요한 라이브러리 캐시 객체를 인덱스를 통해 빠르게 읽어 훨씬 효율적이다.



"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4100

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입