성능 면에서 더 효과적인 경우

-- 버퍼 캐시에 존재하는 버퍼 헤더의 목록을 보여주는 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

동적 성능 뷰 검색조건에 따른 성능 비교 예제

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