태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

V$SQL 관련 뷰 조회하기

오라클 2010.07.01 14:31
V$SQL, V$SQLAREA와 같은 뷰들을 조회할 때 주의할 점을 아직도 잘 모르시는 경우가 많은 것 같습니다. 이 뷰들들을 조회한다는 것은 Library Cache 영역을 탐색한다는 것과 같은 의미입니다. 따라서 잘못 조회하면 심각한 문제를 일으킬 수도 있습니다.

간단한 예로 설명하겠습니다.

1. 오라클 버전은 10.2.0.1 입니다.

TPACK@ukja1021> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
2. 간단한 SQL 문장을 수행하고 SQL ID를 얻습니다.
TPACK@ukja1021> col prev_sql_id new_value sql_id
TPACK@ukja1021> 
TPACK@ukja1021> select /* temp */ * from dual;

D
-
X

Elapsed: 00:00:00.00
TPACK@ukja1021> 
TPACK@ukja1021> select prev_sql_id from v$session where sid = userenv('sid');

PREV_SQL_ID
-------------
5xa7hydhm2dnc
3. SQL_TEXT 컬럼을 통해 검색할 경우의 실행 계획입니다. FIXED TABLE FULL로부터 Library Cache 영역을 Full Scan한다는 것을 알 수 있습니다. 즉, 단 하나의 SQL을 찾기 위해 모든 Library Cache 영역을 다 읽어봐야 합니다.
TPACK@ukja1021> explain plan for
  2  select * from v$sqlarea
  3  where sql_text like 'select /* temp */ %';

Explained.

-------------------------------------------------------------
| Id  | Operation        | Name                    | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT |                         |     1 |
|*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD_SQLID |     1 |
------------------------------------------------------------
4. SQL_ID 컬럼을 통해 검색한 경우의 실행 계획입니다. FIXED TABLE FIXED INDEX로부터 Library Cache 영역을 Index를 경유해서 탐색한다는 것을 알 수 있습니다. 따라서 Library Cache 영역을 불필요하게 읽을 필요가 없습니다. 즉, 가능한 한 Index를 경유하도록 작성해야 합니다.

TPACK@ukja1021> explain plan for
  2  select * from v$sqlarea
  3  where sql_id = '&sql_id';
old   3: where sql_id = '&sql_id'
new   3: where sql_id = '5xa7hydhm2dnc'

--------------------------------------------------------------------------
| Id  | Operation               | Name                           | Rows  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                                |     1 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD_SQLID (ind:2 |     1 |
--------------------------------------------------------------------------
V$SQLXXX 뷰에 대한 인덱스는 HASH_VALUE, SQL_ID 두 개의 컬럼에 대해서 각각 만들어져 있습니다.
TPACK@ukja1021> select table_name, index_number, column_name
  2  from v$indexed_fixed_column
  3  where table_name = 'X$KGLOB';

TABLE_NAME           INDEX_NUMBER COLUMN_NAME
-------------------- ------------ --------------------
X$KGLOB                         1 KGLNAHSH     -- HASH_VALUE
X$KGLOB                         2 KGLOBT03     -- SQL_ID
5. 티팩의 Session Snapshot Report를 이용해 Library Cache 영역을 Full Scan하는 경우와 Index Scan하는 경우 어느 정도의 성능 차이가 발생하는지 분석해보겠습니다.
-- Session Snapshot 시작
exec tpack.begin_session_snapshot(-1);

-- Full Scan on X$ view
begin
	for idx in 1 .. 1000 loop
		for r in (select * from v$sqlarea where sql_text like 'select /* temp */ %') loop
			null;
		end loop;
	end loop;
end;
/

-- Session Snapshot 추가
exec tpack.add_session_snapshot(-1);

-- Index Scan on X$ View
begin
	for idx in 1 .. 1000 loop
		for r in (select * from v$sqlarea where sql_id = '&sql_id') loop
			null;
		end loop;
	end loop;
end;
/

-- Session Snapshot 추가
exec tpack.add_session_snapshot(-1);

-- Session Snapshot Report 보기
set pages 10000
set lines 200
col item format a30
col deltas format a50

select * from table(tpack.session_snapshot_report(-1));
그 결과는 아래와 같습니다(너무 길어서 편집). 수행 시간은 168초:0.4초로 비교할 수가 없습니다. 결정적인 차이는 library cache 래치 획득 회수에서 알 수 있습니다. Library Cache 영역을 1000번 Full Scan한 경우 무려 8백만번의 획득이 필요합니다.
TYPE       ITEM                            START_VAL    END_VAL TOTAL_DELTA DELTAS
---------- ------------------------------ ---------- ---------- ----------- ------------------------
...
TIME       DB time                        1.5215E+10 1.5384E+10   168639689 168231521->408168
...

LATCH      library cache                    40199550   48532801     8333251 8330082->3169
...
V$ 뷰를 조회하는 것은 메모리 영역을 읽는다는 것이기 때문에 기본적으로 효율적이고 빠릅니다. 하지만 Library Cache나 Shared Pool과 같은 영역을 읽을 때는 대단히 주의해야 합니다. 큰 크기의 메모리 영역을 읽는 것은 기본적으로 CPU를 많이 사용하며 래치나 뮤텍스와 같은 동기화 객체에서 심각한 경합이 발생할 수 있습니다.

아무리 간단해 보이는 쿼리라도 언제나 성능의 최적화를 고려해야겠죠!

저작자 표시
신고
Trackback 0 : Comment 1
  1. 스누피 2010.07.10 11:43 신고 Modify/Delete Reply

    좋은 정보 감사합니다. 생각없이 날리던 쿼리들 조심해야겠네요

Write a comment

티스토리 툴바