태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

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 :
Trackback 0 : Comment 0

Trackback Address :: http://ukja.tistory.com/trackback/257 관련글 쓰기

Write a comment