태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Fixed Table'에 해당되는 글 2건

  1. 2010.07.01 V$SQL 관련 뷰 조회하기 (1)
  2. 2009.09.29 Fixed Table에 대한 Query 튜닝하기

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를 많이 사용하며 래치나 뮤텍스와 같은 동기화 객체에서 심각한 경합이 발생할 수 있습니다.

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

Trackbacks 0 : Comments 1
  1. 스누피 2010.07.10 11:43 Modify/Delete Reply

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

Write a comment


Fixed Table에 대한 Query 튜닝하기

오라클 2009.09.29 13:50
Oracle 성능 모니터링을 수행하다 보면 거의 반드시라고 할 만큼 Fixed Table(X$ 테이블이라고도 함)을 조회할 일이 생깁니다. Fixed Table을 읽는다는 것은 SGA의 특정 메모리 영역을 읽는다는 것과 같은 의미죠. 그만큼 조회가 빠릅니다.

하지만 그렇다고 해서 아무 생각없이 Query를 작성해도 된다는 말은 아닙니다. 메모리를 읽는 것이라고 해도 그 크기가 수백MB에 달한다면 엄청난 성능 문제가 생길 것이라고 예상할 수 있습니다. 이런 이유 때문에 많은 Fixed Table들이 인덱스를 가지고 있습니다.

UKJA@ukja1021> desc v$indexed_fixed_column
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      TABLE_NAME                               VARCHAR2(30)
    2      INDEX_NUMBER                             NUMBER
    3      COLUMN_NAME                              VARCHAR2(30)
    4      COLUMN_POSITION                          NUMBER
가령 LCO(Library Cache Object)의 목록을 나타내는 X$KGLOB 테이블은 다음과 같이 두 개의 컬럼에 대해 인덱스를 가지고 있습니다.
UKJA@ukja1021> exec print_table('select * from v$indexed_fixed_column -
> 		  where table_name = ''X$KGLOB''');
TABLE_NAME                    : X$KGLOB
INDEX_NUMBER                  : 1
COLUMN_NAME                   : KGLNAHSH
COLUMN_POSITION               : 0
-----------------
TABLE_NAME                    : X$KGLOB
INDEX_NUMBER                  : 2
COLUMN_NAME                   : KGLOBT03
COLUMN_POSITION               : 0
-----------------
SQL Hash Value(KGLNAHSH)와 SQL ID(KGLOBT03)에 대해 인덱스가 있죠. 즉, X$KGLOB 테이블을 조회할 때는 가능한 한 이 두 개의 컬럼을 사용하도록 해야 합니다.

간단한 테스트를 통해서 성능 문제를 논의해보겠습니다.

1. 다음과 같이 간단한 SQL문을 수행하고 SQL ID를 추출합니다.

UKJA@ukja1021> select * from t1;

no rows selected

Elapsed: 00:00:00.00
UKJA@ukja1021> col sql_id new_value sql_id
UKJA@ukja1021> select regexp_replace(plan_table_output,
  2  				     'SQL_ID[[:blank:]]+([[:alnum:]]+),.*', '\1') as sql_id
  3  from table(dbms_xplan.display_cursor)
  4  where plan_table_output like 'SQL_ID%';

SQL_ID
--------------------------------------------------------------------------------
27uhu2q2xuu7r
2. KGNAOBJ(인덱소 없음)컬럼과 KGLOBT03(인덱스 있음) 컬럼을 통해 X$KGLOB 테이블을 조회하는 두 개의 SQL 문장을 실행합니다.
UKJA@ukja1021> select count(*) from sys.xm$kglob
  2  where kglnaobj = 'select * from t1';

  COUNT(*)
----------
         2

Elapsed: 00:00:00.06
UKJA@ukja1021> 
UKJA@ukja1021> select count(*) from sys.xm$kglob
  2  where kglobt03 = '&sql_id';
old   2: where kglobt03 = '&sql_id'
new   2: where kglobt03 = '27uhu2q2xuu7r'

  COUNT(*)
----------
         2

Elapsed: 00:00:00.01
3. 두 SQL 문장에 대한 TKPROF 결과입니다.
select count(*) from sys.xm$kglob
where kglnaobj = 'select * from t1'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.06       0.05          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.05          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=59402 us)
      2   FIXED TABLE FULL X$KGLOB (cr=0 pr=0 pw=0 time=43810 us)

select count(*) from sys.xm$kglob
where kglobt03 = '27uhu2q2xuu7r'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=167 us)
      2   FIXED TABLE FIXED INDEX X$KGLOB (ind:2) (cr=0 pr=0 pw=0 time=120 us)
다음과 같은 사실을 알 수 있습니다.
  • Logical Reads만으로는 전혀 차이를 알 수 없습니다. Fixed Table에 대한 질의는 블록(Block)을 읽는 것이 아니라 메모리를 직접 읽는 것이기 때문에 우리가 일반적으로 일량을 측정하기 위해 사용하는 Logical Reads로는 관찰되지 않습니다.
  • 실행 계획상으로는 FIXED TABLE FULLFIXED TABLE FIXED INDEX (ind:2)로부터 첫번째 SQL은 Full Scan을 두번째 SQL은 Index Scan(정확하게 말하면 두번째 인덱스)을 하는 것을 알 수 있습니다.
  • 때문에 첫번째 SQL은 0.05초가, 두번째 SQL은 0초가 걸린 것으로 보고됩니다.
우리가 전통적으로 SQL 문장의 성능을 비교할 때 사용하는 Logical Reads가 전혀 무용지물이라는 것에 주의할 필요가 있습니다.

4. 개인적으로 테스트할 때 사용하는 Monitor 스크립트를 이용해서 두 경우의 성능을 비교해보겠습니다.

@mon_on userenv('sid')

select count(*) from sys.xm$kglob
where kglnaobj = 'select * from t1';

@mon_off

select count(*) from sys.xm$kglob
where kglobt03 = '&sql_id';

@mon_off2
@mon_show2
...
02. time model

STAT_NAME                                   VALUE1         VALUE2           DIFF
----------------------------------- -------------- -------------- --------------
DB time                                    166,458        115,196        -51,262
sql execute elapsed time                   164,356        113,308        -51,048
DB CPU                                     134,972        103,749        -31,223
...

03. latch

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
library cache                       -4186          0          0          0
row cache objects                     -45          0          0          0
enqueues                              -19          0          0          0
enqueue hash chains                   -18          0          0          0
...
가장 큰 차이는 Library Cache Latch의 획득에 있습니다. 메모리를 Full Scan해야 하는 첫번째 SQL 문장이 인덱스를 사용하는 두번째 SQL 문장에 비해 Latch 획득 수가 훨씬 많습니다. 그만큼 많은 메모리 영역을 스캔한다는 의미이며 성능도 느리고 Latch 경합에 의한 동시성 문제도 발생할 수 있습니다.

이런 원리를 모르고 모니터링 스크립트를 작성하면 자칫 성능에 치명적인 결과를 가져올 수 있습니다. 성능을 모니터링하기 위한 작업이 성능을 저하시키는 결과가 되는 것이죠. 우리가 흔히 사용하는 모니터링 툴들에서도 이런 실수들이 빈번하게 발생합니다. 성능을 해결하기 위해 사용하는 Query 자체가 튜닝이 안되어 있는 웃지못할 현상이 생깁니다.

Dictionary View에 대해서도 비슷한 원리가 적용될 수 있습니다. 아래 포스트에서는 Dictionary View에 대한 Query 성능에 대해 재미있는 관찰을 볼 수 있습니다. 참조하세요.

tags : Fixed Table
Trackbacks 0 : Comments 0

Write a comment