태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'2010/07'에 해당되는 글 8건

  1. 2010.07.27 Long Running Query의 바인드 값 알아내기
  2. 2010.07.21 [티팩] Session Snapshot Report - Part 1
  3. 2010.07.21 엑셈의 오라클 퀴즈에 도전해보세요.
  4. 2010.07.20 [퀴즈] 성능 튜닝의 절대 원칙 - 오라클에서 가장 가벼운 작업은? - 해답편 (7)
  5. 2010.07.13 [티팩] 지능적인 대기 이벤트 분석 - Part3. (대기이벤트 프로파일링)
  6. 2010.07.12 [퀴즈] 인덱스를 만들 때 고려할 3대 요소는 - 해답편 (7)
  7. 2010.07.05 [티팩] 지능적인 대기 이벤트 분석 - Part2. (핫블록?) (6)
  8. 2010.07.01 V$SQL 관련 뷰 조회하기 (1)

Long Running Query의 바인드 값 알아내기

오라클 2010.07.27 13:53
이런 질문을 받았습니다.
  • 현재 장시간 수행되는 쿼리가 있습니다. 이 쿼리가 사용한 바인드 값을 알아낼 수 있나요?
종종 이런 요구사항이 생기게 됩니다. 아주 단순해 보이는 이 요구사항을 만족시키기 어려운 이유는 이것입니다.
  • 바인드 값은 프로세스의 값이다. 즉 PGA에 존재하는 값이다. 개별 프로세스의 메모리 영역에 존재하는 값이므로 이것을 직접 조회하는 것은 불가능하다.
즉, 오라클의 아키텍처로 인해 V$ 뷰를 조회하는 것과 같은 일반적인 방법으로는 불가능합니다. V$SQL_BIND_CAPTURE 뷰는 특정 SQL이 사용했던 바인드 정보를 보여주지만 1) 15분마다 캡처하며, 2) WHERE 절에 사용된 바인드 변수만 캡처하며, 3) 바인드 변수가 많을 경우 일부만 캡처하는 등의 문제가 있습니다. 무엇보다 현재의 값을 캡처하지 않는다는 제약이 걸림돌입니다.

레벨 4로 10046 이벤트를 수행하는 방법도 있지만, 현재 수행 중인 쿼리인 경우에는 바인드 값의 지정 단계가 이미 지났을 것이기 때문에 바인드 값이 기록되지 않는다는 문제가 있습니다.

이때 사용 가능한 방법이 Error Stack Dump입니다. 간단한 예를 볼까요?

1. Session #1에서 다음과 같이 장시간(30초) 수행되는 쿼리를 실행합니다. 바인드 변수 b1을 선언하고 값으로 'X'를 지정합니다.

-- session #1
create or replace function fsleep(p1 number) return number
is
begin
	dbms_lock.sleep(p1);
	return 1;
end;
/

exec dbms_application_info.set_client_info('SESSION1');

var b1 varchar2(1);
exec :b1 := 'X';

select fsleep(30) from dual where dummy =  :b1;
...
2. Session #1에서 Long Running Query를 수행하는 동안 Session #2에서 oradebug를 이용해 Error Stack Dump(Level 3)를 남깁니다.
conn sys/oracle@ukja1106 as sysdba

col pid new_value pid
select spid as pid from v$process 
where addr = (select paddr from v$session where client_info = 'SESSION1');

oradebug setospid &pid

oradebug dump errorstack 3
oradebug tracefile_name
3. 트레이스 파일을 열어보면 이런 부분이 있습니다. Cursor 번호 10번이 현재 실행중인 Cursor라는 의미입니다.
----- Session Cursor Dump -----
Current cursor: 10, pgadep=0
...
이 값을 이용해 트레이스 파일 내에서 Cursor#10을 찾으면 됩니다.
----------------------------------------
Cursor#10(0x0C731064) state=FETCH curiob=0x0D7809E4
 curflg=46 fl2=0 par=0x00000000 ses=0x345175D4
----- Dump Cursor sql_id=aanj0fskcmr4t xsc=0x0D7809E4 cur=0x0C731064 -----
Dump Parent Cursor sql_id=aanj0fskcmr4t phd=0x26D23F78 plk=0x3487F4A8
 sqltxt(0x26D23F78)=select fsleep(30) from dual where dummy =  :b1
  hash=227eaca42a9d50c9a552207624c9dc99
  parent=0x21F95C00 maxchild=02 plk=0x3487F4A8 ppn=n
cursor instantiation=0x0D7809E4 used=1279600018 exec_id=16777219 exec=1
 child#1(0x2C49E808) pcs=0x21F9594C
  clk=0x3487A930 ci=0x21F94710 pn=0x349254BC ctx=0x24DB5C80
 kgsccflg=0 llk[0x0D7809E8,0x0D7809E8] idx=0
 xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100
----- Bind Byte Code (IN) -----
  Opcode = 2   Bind Twotask Scalar Sql In (may be out) Copy 
  oacdef = 21F944CC   Offsi = 36, Offsi = 0
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=846 siz=32 off=0
  kxsbbbfp=0ceb0e3c  bln=32  avl=01  flg=05
  value="X"
4. 티팩을 사용하면 위의 과정을 어느 정도 자동화할 수 있습니다. 티팩에서 제공하는 SQL Detail Report에서 CURSOR_DUMP 옵션을 사용하면 위의 과정을 자동화해서 그 결과를 보여줍니다.

Session #2에서 다음과 같이 티팩의 SQL Detail Report를 생성합니다.

col sid new_value sid
select sid from v$session where client_info = 'SESSION1';

col name format a15
col value format a60
set pages 10000
set long 100000
select * from table(tpack.sql_detail(session_id=>&sid, options=>'cursor_dump'));
아래와 같이 현재 Cursor를 찾아서 출력해줍니다.
NAME            VALUE
--------------- ------------------------------------------------------------
SQL ID          aanj0fskcmr4t
Child Number    1
SQL Text        select fsleep(30) from dual where dummy =  :b1
Hash Value      617208985
Address         26D23F78
Plan Hash Value 272002086
Bind Sensitive  Y
Bind Aware      N
Cursor Dump     Cursor#7(0x0C730F44) state=FETCH curiob=0x0D7809E4
                 curflg=46 fl2=0 par=0x00000000 ses=0x345175D4
                ----- Dump Cursor sql_id=aanj0fskcmr4t xsc=0x0D7809E4 cur=0x
                0C730F44 -----
                Dump Parent Cursor sql_id=aanj0fskcmr4t phd=0x26D23F78 plk=0
                x3487F4A8
                 sqltxt(0x26D23F78)=select fsleep(30) from dual where dummy
                =  :b1
                  hash=227eaca42a9d50c9a552207624c9dc99
                  parent=0x21F95C00 maxchild=02 plk=0x3487F4A8 ppn=n
                cursor instantiation=0x0D7809E4 used=1279600616 exec_id=1677
                7221 exec=2
                 child#1(0x2C49E808) pcs=0x21F9594C
                  clk=0x3487A930 ci=0x21F94710 pn=0x349254BC ctx=0x24DB5C80
                 kgsccflg=1 llk[0x0D7809E8,0x0D7809E8] idx=9a
                 xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100
                ----- Bind Byte Code (IN) -----
                  Opcode = 2   Bind Twotask Scalar Sql In (may be out) Copy

                  oacdef = 21F944CC   Offsi = 36, Offsi = 0
                ----- Bind Info (kkscoacd) -----
                 Bind#0
                  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
                  oacflg=03 fl2=1000000 frm=01 csi=846 siz=32 off=0
                  kxsbbbfp=0d776cc4  bln=32  avl=01  flg=05
                  value="X"
                 Frames pfr 0x0DA46A30 siz=2904 efr 0x0DA46858 siz=2528
꼭 필요한 경우에 활용할 수 있으면 아주 효과적인 방법이 될 수 있겠습니다.

요즘은 Jennfier같은 Java 모니터링 툴이 보편화되면서 바인드 변수의 값을 보는 것이 가능해졌습니다. 하지만 이런 툴들도 JDBC를 통해 호출된 경우에만 모니터링 가능하기 때문에 여전히 제약이 있다고 하겠습니다.

저작자 표시
신고
tags : errorstack, 티팩
Trackback 0 : Comment 0

Write a comment


[티팩] Session Snapshot Report - Part 1

오라클 2010.07.21 15:14
일전에 이런 질문을 다룬 적이 있습니다.
  • 특정 세션의 성능 문제를 분석하는데 있어 가장 기본적인 뷰는 무엇일까요?
제가 이 질문을 받는다면 주저없이 V$SESSTAT이라고 대답할 것입니다.

오라클은 세션이 특정 작업을 수행할 때마다 항상 그 작업의 통계값(대부분의 경우 회수와 크기)을 V$SESSTAT 뷰에 기록합니다. 거꾸로 말하면, V$SESSTAT 뷰를 통하면 특정 세션이 어떤 작업을 수행하는지 알 수 있다는 말입니다.

이런 이유 때문에 성능 문제를 분석할 때 자연스럽게 V$SESSTAT 뷰를 많이 사용합니다. 이 뷰를 사용하다 보면 자연스럽게 다음과 같은 요구 사항이 생깁니다.

  1. 누적치를 가지고 있기 때문에 반드시 델타(Delta) 값을 계산해야 합니다.
  2. 여러 작업간의 차이를 비교할 수 있어야 합니다. 가령 작업A, 작업 B, 작업 C, 작업 D의 네 개의 작업간에 session logical reads 값이 얼마나 차이가 나는지 보고 싶은 경우들이 있습니다.
아마 성능 테스트나 트러블슈팅을 직접 해보신 경험이 있다면, 반드시 위와 유사한 요구 사항을 경험하셨을 겁니다.

특정 작업의 Snapshot을 구하고, Snapshot간의 차이를 비교하는 것. 이것이 티팩의 핵심 방법론 중 하나죠.

티팩이 제공하는 Session Snapshot Report는 위와 같은 작업을 자동화한 것입니다. 다음과 같은 방식으로 사용합니다.

exec tpack.begin_session_snapshot;

작업 A;

exec tpack.add_session_shapshot;

작업 B;

exec tpack.add_session_snapshot;

작업 C;

exec tpack.add_session_snapshot;

작업 D;

exec tpack.add_session_snapshot;

select * from table(tpack.session_snapshot_report);
Session Snapshot Report는 실제로는 V$SESSTAT 뷰 뿐만 아니라 더욱 다양한 뷰들로부터 Snapshot을 얻습니다. 각 뷰마다 중요도는 틀리지만, 상황에 따라 매우 중요한(그리고 다른 방법으로는 도저히 알 수 없는) 데이터를 제공해줍니다.
  • V$SESSTAT, V$SYSSTAT - 세션 혹은 시스템 레벨 STAT 정보
  • V$SESSION_EVENT, V$SYSTEM_EVENT - 세션 혹은 시스템 레벨 대기 정보
  • V$SESS_TIME_MODEL, V$SYS_TIME_MODEL - 세션 혹은 시스템 레벨 타임 모델 정보
  • V$LATCH - 시스템 레벨 래치 획득 정보
  • V$ROWCACHE - 시스템 레벨 로우 캐시 액세스 정보
  • V$MUTEX_SLEEP(_HISTORY) - 시스템 레벨 뮤텍스 획득 정보
  • Buffer Get(X$KCBSW, X$KCBWH, X$KCBUWHY) - 시스템 레벨 Buffer Get 정보
  • V$SGA_RESIZE_OPS - 시스템 레벨 SGA Resize 정보
  • V$LIBRARYCACHE - 시스템 레벨 라이브러리 캐시 액세스 정보
  • V$OSSTAT - 시스템 레벨 STAT 정보
결국 목적은 보다 다양한 성능 데이터를 체계적으로 분석할 수 있는 체계를 갖추는 것입니다.

간단한 예를 통해 Session Snapshot Report의 유용성을 살펴보겠습니다.

Oracle 10gR2(10.2.0.1 ~ 10.2.0.4)까지는 인덱스를 리빌드하면 Nested Loops Join 방식으로 해당 인덱스를 읽을 때 Logical Reads가 증가하는 버그가 있습니다. 이 버그를 Session Snapshot Report를 통해 살펴 보겠습니다.

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

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
2. 다음과 같이 테이블과 인덱스를 만듭니다.

SQL> create table t1(c1 int, c2 int);

Table created.

SQL> create table t2(c1 int, c2 int);

Table created.

SQL> create index t2_n1 on t2(c1);

Index created.

SQL> insert into t1
  2  select level, level
  3  from dual
  4  connect by level <= 200000;

200000 rows created.

SQL> insert into t2
  2  select level, level
  3  from dual
  4  connect by level <= 200000;

200000 rows created.

SQL> commit;

Commit complete.
3. 인덱스를 리빌드하기 전에는 INDEX RANGE SCAN(5번 단계)에서의 일량은 200,000 블록입니다. 선행 테이블 T1에서 200,000건의 로우가 추출되므로 한번의 인덱스 T2_N1에 대한 액세스마다 Logical Reads가 1씩 발생한다는 것을 알 수 있습니다.
SQL> -- before index rebuild
SQL> select /*+ gather_plan_statistics
  2  		 leading(t1) use_nl(t2) index(t2) */
  3    count(t2.c2)
  4  from
  5    t1, t2
  6  where t1.c1 = t2.c1
  7  	   and t1.c1 > 0
  8  ;

COUNT(T2.C2)
------------
      200000

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
--------------------------------------------------------
| Id  | Operation                    | Name  | Buffers |
--------------------------------------------------------
|   1 |  SORT AGGREGATE              |       |     201K|
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |     201K|
|   3 |    NESTED LOOPS              |       |     200K|
|*  4 |     TABLE ACCESS FULL        | T1    |     436 |
|*  5 |     INDEX RANGE SCAN         | T2_N1 |     200K|
--------------------------------------------------------
4. 인덱스 T2_N1을 리빌드합니다.
SQL> -- rebuild the index
SQL> alter index t2_n1 rebuild;

Index altered.
리빌드 후의 Logical Reads는 400,000 블록으로 정확하게 2배가 늘었습니다. 이 말은 인덱스 T2_N1을 한번 액세스할 때마다 Logical Reads가 2 발생한다는 것을 의미합니다.
SQL> -- after index rebuild
SQL> select /*+ gather_plan_statistics
  2  		 leading(t1) use_nl(t2) index(t2) */
  3    count(t2.c2)
  4  from
  5    t1, t2
  6  where t1.c1 = t2.c1
  7  	   and t1.c1 > 0
  8  ;

COUNT(T2.C2)
------------
      200000

--------------------------------------------------------
| Id  | Operation                    | Name  | Buffers |
--------------------------------------------------------
|   1 |  SORT AGGREGATE              |       |     401K|
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |     401K|
|   3 |    NESTED LOOPS              |       |     400K|
|*  4 |     TABLE ACCESS FULL        | T1    |     436 |
|*  5 |     INDEX RANGE SCAN         | T2_N1 |     400K|
--------------------------------------------------------
5. 이 현상을 Session Snapshot Report를 통해 분석해보겠습니다.
-- 인덱스를 재생성한 후(drop & create)

-- Session #1
exec tpack.begin_session_snapshot;

select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

-- Session #2
alter index t2_n1 rebuild;

-- Session #1
exec tpack.add_session_snapshot;

select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

exec tpack.add_session_snapshot;

col type format a10
break on type skip 1
col item format a20
col deltas format a20

select * from table(tpack.session_snapshot_report);
Session Snapshot Report의 결과 중 위의 현상과 관련 있는 것들을 추려보면 다음과 같습니다. { START_VAL = 최초의 작업이 시작하기 전의 값, END_VAL = 마지막 작업이 끝난 후의 값, TOTAL_DELTA = END_VAL - START_VAL, DELTAS = 각 단계별 Delta } 값입니다.
TYPE       ITEM                       START_VAL    END_VAL TOTAL_DELTA DELTAS
---------- ------------------------- ---------- ---------- ----------- --------------------
STAT       buffer is pinned count       1399292    2797644     1398352 799175->599177
           session logical reads         642598    1253969      611371 207133->404238
           buffer is not pinned coun     209197     410473      201276 863->200413
           t

LATCH      cache buffers chains        13161310   14396639     1235329 420897->814432

BUFF GET   kdiwh09: kdiixs               639636    1039734      400098 200098->200000
           kdiwh42: kdiixs               247335     447338      200003 4->199999

325 rows selected.
  • session logical reads가 200,000에서 400,000으로 증가한 것을 확인할 수 있습니다.
  • session logical reads가 증가한 만큼 buffer is pinned count는 줄고, buffer is not pinned count는 늘어났습니다.
  • kdiwh42: kdiixs 라는 오퍼레이션이 session logical reads가 증가한 만큼 증가한 것을 알 수 있습니다.
위의 지표를 분석해보건대, Buffer Pinning의 효과가 없어지면서 Logical Reads가 증가했다고 판단할 수 있습니다.

실제로 위의 현상은 인덱스를 리빌드할 경우 인덱스 루트 블록(Index Root Block)에 대한 Buffer Pinning 효과가 없어지면서 Logical Reads가 증가하는 버그입니다.(버그 번호 6455161에 해당)

아주 간단한 예같지만, V$SESSTAT 뷰에서 얼마나 중요한 데이터를 얻을 수 있는지 잘 알 수 있습니다.

티팩을 사용하는 이유가 이 Session Snapshot Report라고 할 정도로 저에게는 중요한 데이터입니다. 또는 제가 전문가라고 불리는 비결 중 하나?라고 할 수도 있겠네요. 전문가라는 것이 사실은 별 것 없고 남이 잘 보지 못하는 데이터를 추출해서 합리적으로 해석해서 문제를 해결하는 능력을 가진 자라고 본다면 말입니다.

이전 글 보기

  1. [티팩] 성능 문제를 트러블슈팅하는 두가지 틀(Frame)
  2. [티팩] oradebug
  3. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 1
  4. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 2 (핫 블록?)
  5. [티팩] 지능적인 대기 이벤트 분석 - Part3. (대기이벤트 프로파일링)
저작자 표시
신고
tags : 티팩
Trackback 0 : Comment 0

Write a comment


엑셈의 오라클 퀴즈에 도전해보세요.

오라클 2010.07.21 09:56
아주 간단한 퀴즈이고 가장 멋진 정답을 하신 분께는 무려 5만원 상당의 상품권이 수여됩니다. ㅎㅎ

퀴즈 도전은 여기서

화이팅!

저작자 표시
신고
Trackback 0 : Comment 0

Write a comment


[퀴즈] 성능 튜닝의 절대 원칙 - 오라클에서 가장 가벼운 작업은? - 해답편

오라클 2010.07.20 13:06
오라클 성능 문제에 관한 원조 중의 원조(구루?)로 평가받는 Carry Milsap은 성능을 개선시키는 절대적인 원칙 하나를 언급했습니다.
  • 어떤 작업의 성능을 개선시키는 최고의 방법은 그 작업을 하지 않는 것이다.
정말 명언 중의 명언입니다.

아래에 간단한 예가 있습니다.

1. 더 이상 튜닝할 것이 없는 완벽히 최적화된 문장입니다. 약 3.4초가 걸렸습니다.

TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		select trunc(idx) into v_value from dual;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.42
과연 그럴까요? 위에서 말한 절대 원칙을 적용하면? 불필요한 SELECT ... FROM DUAL 을 없앤다면? (작업을 하지 않는 것)
TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		v_value := trunc(idx);
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
불과 0.07초만에 수행이 끝납니다.

2. DECODE 함수는 SQL 문장내에서만 수행 가능합니다. 따라서 CASE ... 구문으로 변환하면 같은 효과를 누릴 수 있습니다.


TPACK@ukja1106> -- decode
TPACK@ukja1106> declare
  2  	v_value varchar2(1);
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		select decode(mod(idx,2),0,'A','B') into v_value from dual;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.59

TPACK@ukja1106> -- case
TPACK@ukja1106> declare
  2  	v_value varchar2(1);
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		v_value := case mod(idx,2) when 0 then 'A' else 'B' end;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
3. Oracle 11g부터는 Sequence의 값도 SELECT ... FROM DUAL을 통하지 않고 얻을 수 있게 되었습니다. 하지만 성능에 주는 영향은 거의 미미하군요.
TPACK@ukja1106> -- SELECT... FROM DUAL을 통해서 얻기
TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		select s1.nextval into v_value from dual;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.73

TPACK@ukja1106> -- 직접 얻기
TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		v_value := s1.nextval;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.64
음... 왜 그럴까요? 그에 대한 답은 SQL*Trace를 통해 얻을 수 있습니다. SQL*Trace를 수행해보면 { v_value := s1.nextval } 작업은 { Select S1.NEXTVAL from dual } 로 변환되는 것을 알 수 있습니다. 따라서 위의 두 작업은 사실은 동일한 일을 수행하고 있는 것입니다.

4. 이제 가벼운 문제 하나 나갑니다.

WAS 같은 시스템에서 오라클의 세션이 살아 있는지 죽었는지 확인하기 위해 특정 Query를 수행해봅니다. 이 작업을 수행하기 위한 최고의 방법은 무엇일까요? 즉 어떤 Query를 수행하면 될까요?


제가 생각한 정답은 이것입니다.
begin 
    null;
end;
/
되도록 아무런 일도 하지 않게 하는 것.

참고로 아무리 Logical Reads가 적은 Query를 구사해도 { select 1 from dual } 보다는 비효율적입니다. { select 1 from dual }은 Buffer Cache를 액세스하지 않기 때문입니다.

TPACK@ukja1106> explain plan for select 1 from dual;

Explained.

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------
단, {select * from dual } 과 갈은 쿼리를 수행하면 Buffer Cache에서 Buffer를 읽게 됩니다.
TPACK@ukja1106> explain plan for select * from dual;

Explained.

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DUAL |
----------------------------------
저작자 표시
신고
Trackback 0 : Comments 7
  1. 열심남 2010.07.16 10:58 신고 Modify/Delete Reply

    좋은 포스트입니다. ^^: RSS로 구독중인데 ukja님 블로그 full feed로 제공해주심 매우 감사할듯합니다. ^^:

  2. 열심남 2010.07.16 11:02 신고 Modify/Delete Reply

    저도 문제에 답변 응모를 해야죠.. 저희 was에서도 위에 나온것처러 select * from dual; 이 문장을 많이 씁니다. 해서 예전에 dual 테이블 자체를 organization index 로 만들어서 적용했던 적이 있습니다. ukja님은 더 좋은 해결책을 제시해주실듯 ^^: 기대됩니다.

  3. onsider 2010.07.16 19:32 신고 Modify/Delete Reply

    음... 제가 생각하기로는 가장 가벼운 Query는"Select * from dual"인고,
    이것을 더 가볍게 하기 위해 Procedure로 만들고 호출하는게 더 나은 방법 아닌지요 ?

  4. 박용석 2010.07.19 15:36 신고 Modify/Delete Reply

    select 1 from waschk ; 그리고 waschk 는 1블럭 짜리 IOT로 구성 합니다.

  5. 2010.07.20 14:13 Modify/Delete Reply

    비밀댓글입니다

    • 욱짜 2010.07.20 16:15 신고 Modify/Delete

      Oracle 10g부터는 dual 접속시 Logical Reads가 발생하지 않도록 보완되었습니다. (FAST DUAL)

      단 DUMMY 컬럼을 액세스할 때는 다시 이전의 DUAL 테이블 액세스로 바뀌게 됩니다. 즉 { select sysdate from dual } 같은 문장은 X$DUAL를 사용한 것과 동일한 효과가 있고, { select * from dual }과 같은 문장은 Logical Reads를 일으키게 됩니다.

  6. 김시연 2010.08.03 14:00 신고 Modify/Delete Reply

    select 1 from dual;방식보다 begin null; end; 방식이 O/S cpu 사용률 관점에서 98%개선되는군요.
    그리고 오라클 down시에 Error Code(3113,3114)도 리턴되므로 좋은 방법이 되겠네요~!


    $cat case1.sh
    sqlplus scott/tiger << EOF
    declare v1 number;
    begin
    for i in 1..100000 loop
    select 1 into v1 from dual;
    end loop;
    end;
    /
    exit
    EOF

    << $time case1.sh output >>
    real 0m4.017s
    user 0m0.017s
    sys 0m0.010s

    << 10046 trace output >>

    call count cpu elapsed
    ------- ------ -------- ----------
    Parse 1 0.00 0.00
    Execute 1 6.26 7.50
    Fetch 0 0.00 0.00
    ------- ------ -------- ----------
    total 2 6.26 7.50

    $cat case2.sh
    sqlplus scott/tiger << EOF
    begin
    for i in 1..100000 loop
    begin null; end;
    end loop;
    end;
    /
    exit
    EOF
    << $time case1.sh output >>
    real 0m0.091s
    user 0m0.020s
    sys 0m0.014s

    << 10046 trace output >>
    call count cpu elapsed
    ------- ------ -------- ----------
    Parse 1 0.00 0.00
    Execute 1 0.00 0.00
    Fetch 0 0.00 0.00
    ------- ------ -------- ----------
    total 2 0.00 0.00

Write a comment


[티팩] 지능적인 대기 이벤트 분석 - Part3. (대기이벤트 프로파일링)

오라클 2010.07.13 15:06
특정 세션이 어떤 이벤트를 주로 대기하는지 직관적으로 관찰하는 가장 좋은 방법은 무엇입니까?

아마 위의 문제에 대한 가장 정확한 대답은 Oracle 10g부터 제공되는 ASH(Active Session History)일 것입니다. ASH의 가치는 이루 말할 수가 없습니다. 좀 과장하면 오라클 행(Hang) 현상을 트러블슈팅하는 오라클의 핵심 지원 엔지니어들의 10년 소원이었을 것으로 봅니다.

이것이 단순히 과장이 아닌 것이, DMA(Direct Memory Access)에 의해 Active Session History를 수집하고 보여주는 솔루션이 고가로 시장에 팔리는 것을 보면 알 수 있습니다.

Oracle 10g부터는 DMA에 의한 ASH 데이터를 기본적으로 제공하니 당연히 이것을 적극 활용해야 합니다. 사용법은 아래와 같이 매우 간단합니다.

1. Session #1에서 ALL_OBJECTS를 읽습니다. 당연히 I/O 관련 대기 이벤트들(db file sequential read나 db file scattered read와 같은)이 나타날 것입니다.

-- session #1
exec dbms_application_info.set_client_info('SESSION1');

alter system flush buffer_cache;

select count(*) from all_objects;

2. ASH를 이용하면 Session #1이 어떤 이벤트들을 대기했는지를 초(Second)단위로 알 수 있습니다.

col sid new_value sid
select sid from v$session where client_info = 'SESSION1';

-- active session history
col event format a30
select * from (
	select to_char(sample_time,'hh24:mi:ss'), session_id, session_state, sql_id, event 
	from v$active_session_history
	where session_id = &sid
	order by sample_time desc
) where rownum <= 10;

TO_CHAR( SESSION_ID SESSION SQL_ID        EVENT
-------- ---------- ------- ------------- ------------------------------
11:29:31        143 WAITING 4c1xvq9ufwcjc db file sequential read
11:29:30        143 ON CPU  9tz4qu4rj9rdp
11:29:29        143 ON CPU  9tz4qu4rj9rdp
11:29:28        143 ON CPU  9tz4qu4rj9rdp
11:29:27        143 ON CPU  9tz4qu4rj9rdp
11:29:26        143 ON CPU  9tz4qu4rj9rdp
11:29:25        143 ON CPU  9tz4qu4rj9rdp
11:29:24        143 ON CPU  9tz4qu4rj9rdp
11:29:23        143 ON CPU  9tz4qu4rj9rdp
11:29:22        143 WAITING 9tz4qu4rj9rdp db file sequential read
우리가 일반적으로 활용하는 최소 시간 단위가 초단위이기 때문에 이 정도면 충분합니다. 오라클 행(Hang) 상황이라서 접속 자체가 안되는 경우에는 아래 방법을 사용하면 됩니다.

3. 만일 초단위보다 더 정밀하게 대기이벤트를 추적하고 싶다면 프로파일링 기법을 사용할 수 있습니다. 아래와 같이 100,000번 루프를 돌면서 V$SESSION_WAIT 뷰를 탐색한 뒤 { event, p1 }에 대해 그룹핑을 수행하면 특정 이벤트의 Parameter1 값의 발생 빈도를 알 수 있습니다.

select /*+ ordered use_nl(w) */ 
	sid, 
	event, 
	p1,
	count(*) as wait_hits
from 
	(select /*+ no_merge */ level as lvl from dual connect by level <= 100000) t1,
	(select /*+ no_merge */ 
		sid, 
		decode(state, 'WAITING', event, 'ON CPU') as event, 
		decode(state, 'WAITING', seq#, 0) as seq#, 
		decode(state, 'WAITING', p1, null) as p1, 
		decode(state, 'WAITING', p2, null) as p2, 
		decode(state, 'WAITING', p3, null) as p3 
		from v$session_wait 
		where
			sid = &sid		
	) w 
where 
 sid = &sid
 and (event like '%%' or event = 'ON CPU')
group by sid, event, p1
order by count(*) desc 
;

       SID EVENT                           P1  WAIT_HITS
---------- ------------------------------ --- ----------
       141 db file sequential read          1      51160
       141 ON CPU                                  45571
       141 db file scattered read           1       3269

4. 티팩에서는 위에서 사용한 쿼리를 좀 더 세련되게 구사해서 대기 이벤트 정보를 수집합니다. 아래와 같이 수행하면 특정 세션의 모든 대기 이벤트(null)에 대해 Parameter1 기준(p1)으로 10초간(10) 대기이벤트 정보를 V$SESSION_WAIT로부터 수집해서 요약해줍니다.

-- wait profiling
col event_name format a25
col p1 format 99
col p2 format 99
col p3 format 99
col wait_pct format 99.9
col avg_wait_time heading AVG_WAIT

select * from table(tpack.wait_profile_report(&sid, null, 'p1', 10));

 SID EVENT_NAME                 P1  P2  P3 WAIT_PCT  WAIT_TIME AVG_WAIT
---- ------------------------- --- --- --- -------- ---------- --------
 143 ON CPU                                    63.3     6334.3   6334.3
 143 db file sequential read     1             23.4     2347.6     14.3
 143 db file scattered read      1             13.1       1318     15.5
마치 10046 이벤트(또는 SQL*Trace)를 수행한 후 TKPROF를 통해 대기 이벤트 정보를 추출한 것과 거의 비슷한 효과가 있습니다. 10046을 수행하지 않고도 간편하게 특정 세션의 대기 현황을 알 수 있습니다. 10046과 비교해서 가장 큰 장점은 이벤트의 Parameter별로 집계할 수 있다는 것입니다.

가령 buffer busy waits 이벤트의 경우 P3 값이 Block Class 정보를 나타냅니다. 이 값을 정확하게 아는 것이 중요합니다. 하지만 이 정보가 TKPROF 리포트에는 나타나지 않습니다(물론 10046 트레이스 원본에는 있습니다). 하지만 위에서 사용한 프로파일링 기법을 이용하면 어떤 P3 값이 가장 많이 나타나는지를 바로 알 수 있습니다.

5. 10046 Trace의 원본 파일(레벨 8 이상)은 가장 상세한 대기 이벤트 정보를 제공합니다.

exec dbms_monitor.session_trace_enable(waits=>true);

select count(*) from all_objects;

exec dbms_monitor.session_trace_disable;
트레이스 파일에는 다음과 같은 대기 이벤트가 기록됩니다.
WAIT #3: nam='db file sequential read' ela= 16023 file#=1 block#=7754 blocks=1 obj#=380 tim=709352877372
WAIT #3: nam='db file sequential read' ela= 369 file#=1 block#=7753 blocks=1 obj#=380 tim=709352877875
Regular Expression(10g부터 제공)을 이용하면 위의 텍스트로부터 Event, P1, P2, P3, Wait Time 정보를 추출할 수 있습니다.
select
regexp_replace(
'WAIT #3: nam=''db file sequential read'' ela= 16023 file#=1 block#=7754 blocks=1 obj#=380 tim=709352877372',
'[[:print:]]+nam=''([[:print:]]+)''[[:space:]]+ela=[[:space:]]*([[:digit:]]+)[[:space:]][[:print:]]+=([[:digit:]]+)[[:space:]][[:print:]]+=([[:digit:]]+)[[:space:]][[:print:]]+=([[:digit:]]+)[[:space:]]obj#=[[:print:]]+',
'\1.\2.\3.\4.\5')
from dual;

REGEXP_REPLACE('WAIT#3:NAM=''DBFILESEQ
--------------------------------------
db file sequential read.16023.1.7754.1
티팩에서도 위와 비슷한 방법으로 트레이스 파일로부터 직접 읽어서 대기 이벤트 정보를 집계할 수 있습니다.
select * from table(tpack.wait_file_report(tpack.get_tracefile_name(&sid)));

   SID EVENT_NAME                 P1  P2  P3 WAIT_PCT  WAIT_TIME AVG_WAIT
------ ------------------------- --- --- --- -------- ---------- --------
     0 db file sequential read                   76.7   2473.378      2.7
     0 db file scattered read                    23.1    744.395      3.3
     0 SQL*Net message from clie                   .1      3.808      1.3
       nt
     0 SQL*Net message to client                   .0       .018       .0
상세한 대기 정보를 얻는데 생각보다 다양한 방법이 있다는 것을 알 수 있습니다. 어떤 상황에서도 대기 이벤트 정보를 추출할 수 있도록 자신만의 기법 또는 툴 사용 체계를 수립할 필요가 있습니다. 대기 이벤트 정보는 그만큼 중요하기 때문입니다.

이전 글 보기

  1. [티팩] 성능 문제를 트러블슈팅하는 두가지 틀(Frame)
  2. [티팩] oradebug
  3. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 1
  4. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 2 (핫 블록?)
저작자 표시
신고
tags : 티팩
Trackback 0 : Comment 0

Write a comment


[퀴즈] 인덱스를 만들 때 고려할 3대 요소는 - 해답편

오라클 2010.07.12 09:45
퀴즈 재미 붙였습니다. :)

이 문제는 제가 만든 것이 아니고 인덱스에 관한 최고의 저서 중 하나에 기록되어 있는 것입니다. 오늘 마침 인덱스에 관한 작업을 처리하다가 이 문제가 생각났습니다.

문제: 테이블에 인덱스를 만들려고 합니다. 어떤 컬럼에 대해 인덱스를 만들어야 할지를 결정할 때 사용할 수 있는 3대 기준이 있습니다. 이 세 가지 기준은 무엇일까요?

이미 답을 알고 계신 분들도 많을 것이고, 이 문제를 진지하게 고민해 본 분이라면 나름대로의 답을 이미 가지고 있는 분들도 있을 것 같습니다.

관심 있으신 분들은 심심풀이로 한번씩 달아보시면 좋겠습니다. 역시 답변이 안달리면 자삭하겠습니다. :)


퀴즈에 대한 답입니다. 원래의 정답은 아래와 같습니다.
  1. 랜덤 액세스의 비용이 테이블 Full Scan보다 비용이 저렴한가? - 선택도의 문제
  2. 테이블로 아예 가지 않도록 할 필요가 있느가? - Table lookup by ROWID의 성능 문제
  3. 정렬을 대신할 수 있는가? - Order By 성능 문제
그 외에 다양한 답들이 존재하겠지만 위의 세 가지가 가장 기본적인 고려 요소로 거론됩니다. Index Range Scan 만이 고려 대상이 되고 있죠? 그래서 가장 기본적이라는 것입니다. 다른 스캔 방식(Skip Scan, Fast Full Scan 등등)을 고려하면 답변이 훨씬 다양해지겠습니다.

인데스를 구성하는 컬럼을 고려할 때 DML은 일단 고려 대상이 아닙니다. DML의 성능에 대한 고려는 SELECT에 대한 고려와 검증이 끝난 후 별도의 튜닝 과정에서 필요하며, 이 경우에도 인덱스를 구성하는 컬럼을 변경하는 경우는 거의 없습니다.(또는 없어야 합니다)

PS) 제가 오라클 관련 이야기를 좀 더 공개적으로 해볼까 해서 트윗을 시작했는데 막상 들어가보니 오라클 관련 이야기를 할 공간이 없더군요. 트윗에서 오라클 관련 이야기를 주고 받을 수 있는 좋은 방법이나 아이디어 있으신 분 있나요? 트윗에서까지 오라클 이야기하는 것은 무리인 것 같기도 하고... 쿨럭

저작자 표시
신고
Trackback 0 : Comments 7
  1. 2010.07.02 23:43 Modify/Delete Reply

    비밀댓글입니다

  2. 2010.07.03 23:07 신고 Modify/Delete Reply

    대용량데이터베이스 솔루션에 몇가지가 나오는데요..설마 그건가요?
    1. 항상 사용하는가?
    2. =로 사용하는가?
    3. 분포도는 좋은가?
    기타:sort를 대체해서 사용할 수 있는가 등등....

    • 욱짜 2010.07.04 00:57 신고 Modify/Delete

      인덱스에 대해 다루는 책이라면 비슷한 이야기를 할 겁니다. 그 책은 아니지만 상당 부분 일치합니다. 그만큼 가장 기본적인 퀴즈라고 할 수 있겠네요.

      아주 많은 답의 조합이 가능하기도 할 거구요.

      지금 아르헨티나가 독일에 4:0으로 졌습니다. ㅎㅎ

  3. 새벽 2010.07.06 18:41 신고 Modify/Delete Reply

    인덱스 잡을때 개인적으로 고려하는 사항들입니다.
    물론 정답에서 많이 비껴날 수 있겠구요.. ^^;

    1.조건절에서 10% 이하의 검색범위로 빈번히 사용되는 컬럼
    2.1을 만족하면서 카디널러티가 좋은 컬럼 (동일한 값이 거의 없으면 Good!)
    3.1을 만족하면서 사이즈가 적은 컬럼
    4.1과 2를 만족하면서 Nullable인 컬럼도 개인적으로는 좋아합니다만 이건 별로인지 잘 모르겠네요..

    적고보니 위에 적으신 분 내용이랑 비슷하네요..

    내일부터는 4강전이 시작됩니다. ㅎㅎ

  4. rcho 2010.07.13 10:21 신고 Modify/Delete Reply

    인덱스에 대한 생각을 정리해볼 좋은 기회였습니다. 감사합니다.
    저희 회사분들중에도 정보공유를 위해 트윗을 해보시는 분들이 꽤 계시던데, 아직까지 성공적이진 않은것같습니다.
    우선 조동욱님의 트윗 아이디를 공유하시는게 첫 걸음이지않을까 싶습니다만...
    즐거운 마음으로 follow하겠습니다!

Write a comment


[티팩] 지능적인 대기 이벤트 분석 - Part2. (핫블록?)

오라클 2010.07.05 15:41
아주 지엽적인 문제이긴 하지만, 좀 더 깊이를 추구해보기 위해(제 블로그가 하드 코어 오라클 인터널 블로그로 불린다는 사실을 알고... 그걸 원하는 건 아닌데 말이죠) 핫블록이라는 주제를 다뤄보겠습니다. 이 주제 역시 많은 분들에게 하드 코어로 분류될 것 같습니다.

cache buffers chains래치 경합이 발생할 때 그 이유가 핫 블록 때문이지를 판단할 필요가 있습니다(이렇게 말은 하지만 실제로 적용해보신 분들은 거의 없을 것입니다). 많은 수의 세션이 동일한 블록을 액세스하게 되면 해당 블록에서 래치 경합이 발생하게 되죠. 그 악명높은 latch: cache buffers chains 대기 이벤트가 발생하게 됩니다.

제가 적은 빨간 책이나 제가 일하는 엑셈에서 번역 출간한 까만 책을 보면 핫블록(Hot Block)을 찾는 방법을 간단한게 기술하고 있습니다. 핵심만 말하자면 V$BH와 V$SESSION_WAIT을 적절히 결합하면 특정 세션이 가장 많이 액세스하는 뜨거운(Hot한) 블록을 찾을 수 있다는 것입니다. 터치 카운트(Touch Count)가 높으면 핫블록이라고 간주하는 것입니다.

select 
	  b.hladdr as latch_addr,
	  b.obj, 
	  (select object_name from dba_objects 
		where data_object_id = b.obj and rownum = 1) as obj_name,
	  b.dbarfil as file#,
	  b.dbablk as block#,
	  b.tch as touch_cnt
from x$bh b, v$session_wait s
where s.sid = &session_id
	  and s.p1raw = b.hladdr
order by b.tch desc
;
또는 X$BH와 V$LATCHHOLDER 뷰를 통해서도 같은 결과를 얻을 수 있습니다. 이전 포스트를 유심히 보셨다면 쉽게 이해가 될 것입니다.
select 
  b.hladdr as latch_addr,
  b.obj, 
  (select object_name from dba_objects
     where data_object_id = b.obj and rownum = 1) as obj_name,
  b.dbarfil as file#,
  b.dbablk as block#,
  b.tch as touch_cnt
from x$bh b, v$latchholder s
where s.sid = &session_id
  and s.laddr = b.hladdr
order by b.tch desc
;
여기서의 기준은 터치 카운트(TCH)가 됩니다. 터치 카운트가 높을 수록 많이 액세스한 블록일테고, 그것이 핫 블록일 가성능도 높다는 자연스러운 원리입니다.

실제로 간단한 테스트를 통해 핫블록이 어떻게 관찰되는지 보겠습니다.

1. 아래와 같이 인덱스 T2_N1을 과도하게 액세스하는 쿼리를 Session #1에서 수행합니다.

-- session #1
exec dbms_application_info.set_client_info('SESSION1');

begin
	for idx in 1 .. 1000 loop
		for r in (select /*+ ordered use_nl(t2) index(t2) */
						t1.c1, t2.c2
					from t1, t2
					where t1.c1 = t2.c1) loop
			null;		
		end loop;
	end loop;
end;
/
2. 위의 작업이 수행되는 동안 Session #2에서 V$BH, V$LATCHHOLDER 뷰를 캡쳐해본 결과입니다.
col sid new_value session_id
select sid from v$session where client_info = 'SESSION1';

col latch_addr format a10
col obj_name format a20

select * from (
select 
  b.hladdr as latch_addr,
  b.obj, 
  (select object_name from dba_objects where data_object_id = b.obj and rownum = 1) as obj_name,
  b.dbarfil as file#,
  b.dbablk as block#,
  b.tch as touch_cnt
from sys.tpack$bh b, v$latchholder s
where s.sid = &session_id
  and s.laddr = b.hladdr
order by b.tch desc
) where rownum <= 10;


LATCH_ADDR        OBJ OBJ_NAME                  FILE#     BLOCK#  TOUCH_CNT
---------- ---------- -------------------- ---------- ---------- ----------
3388F28C       102200 T2_N1                         4        757         15
3388F28C          213 PROCEDUREINFO$                1      69555         10
3388F28C          585 TABCOMPART$                   1       3786          5
3388F28C            2 ICOL$                         1      65217          3
3388F28C        64686 MGMT_METRICS_1HOUR_P          2      77066          2
                      K

3388F28C         5932 WRH$_SQLTEXT                  2      98989          2
3388F28C            8 UET$                          1      69322          1
3388F28C        64686 MGMT_METRICS_1HOUR_P          2      77066          1
                      K

3388F28C       102070 WRH$_TABLESPACE_STAT          2      90313          1
3388F28C          554 JAVAOBJ$                      1      43061          0

인덱스 T2_N1의 (4/757) 블록이 Touch Count가 가장 높군요. 이 블록을 핫 블록이라고 간주할 수도(!) 있겠습니다. 해당 블록이 정확하게 어떤 블록인지를 블록 덤프를 통해 확인해볼 수 있습니다.

3. 티팩이 제공하는 Extended Block Dump 기능을 이용하면 블록 덤프의 결과에서 오브젝트 정보를 해석해줍니다. 아래에 그 결과가 있습니다.

set pages 10000

TPACK@ukja1106> select * from table(tpack.block_dump(4, 757));

-- 또는

alter system dump datafile 4 block 757;
....
Block header dump:  0x010002f5
 Object id on Block? Y
*** data object id = 102200
*** object name = T2_N1
*** object type = INDEX
*** table object id = 102199
 seg/obj: 0x18f38  csc: 0x00.1ecfa33  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002f1 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.013.000076b5  0x00c011dc.14c1.01  CB--    0  scn 0x0000.01ecfa26
0x02   0x0004.021.0000551d  0x00c001fe.1113.04  C---    0  scn 0x0000.01ecfa28
Leaf block dump
===============
header address 534061156=0x1fd52064
kdxcolev 0
KDXCOLEV Flags = - - -
4. 하지만 Touch Count를 이용하는 방법은 단점이 있습니다.
  • 실제로 블록을 Touch한 회수가 그대로 Touch Count에 반영되지는 않습니다.
  • 누적값이므로 특정 시점에 핫블록인지 아닌지를 판단하기 어려울 수 있습니다.
그렇다면 좀 더 현실적으로 핫 블록을 찾을 수 있는 방법은 없을까?라는 고민이 들게 됩니다. 이때 사용할 수 있는 것이 래치 홀더를 프로파일링하는 것입니다(스냅샷과 프로파일이 티팩의 핵심 방법이라는 것은 이미 여러 차례 밝힌바 있습니다). 이것은 다음 몇 가지 사실에 기반한 것입니다.
  • V$LATCHHOLDER 뷰나 이 뷰의 베이스 뷰인 X$KSUPRLAT 뷰를 아주 짧은 주기로 방문해서 특정 세션이 특정 래치를 얼마나 점유하는지 알 수 있다.
  • X$KSUPRLAT.KSULAWHY 컬럼은 래치 획득 이유를 나타내는데 cache buffers chains 래치의 경우에는 DBA를 나타낸다.
  • 따라서 X$KSUPRLAT.KSULAHWHY 컬럼을 프로파일링 기법을 통해 값을 얻으면 특정 세션이 특정 시간동안 어떤 블록을 주로 방문했는지 알 수 있다.
즉, 다음과 같은 코드를 수행하면 됩니다. 138번 세션에 대해 X$KSUPRLAT 뷰를 100,000번 샘플링하면서 KSULAWHY 값을 얻어옵니다.

select /*+ ordered use_nl(l) */ 
	l.*
from 
	(select /*+ no_merge */ level as lvl from dual connect by level <= 100000) t1, 
	(select /*+ no_merge */ 
		ksuprsid as sid, 
		ksuprlat as latch_addr, 
		ksulagts as gets, 
		(select ksllwnam from sys.tpack$ksllw where indx = ksulawhr) as code_name, 
		(select ksusesqi from sys.tpack$ksuse where indx = ksuprsid) as sql_id, 
		ksuprlnm as latch_name,  
		to_char(ksulawhy,'XXXXXXXXXXXXXXXX') as obj_why 
	from sys.tpack$ksuprlat ) l 
	where 
	 sid = 138
	 and latch_name like '%cache buffers chains%' 
group by l.sid, latch_name, code_name, obj_why
order by count(*) desc 
);
5. 티팩에서는 위의 SQL 보다 조금 더 복잡하고 지능적인 쿼리를 사용해서 동일한 정보를 얻습니다. 이것을 Latch Profile Report라고 부릅니다. 아래와 같이 호출하면 cache buffers chains 래치에 대해 (래치명 + 래치 획득한 장소 + 래치 획득 이유 오브젝트) 정보를 5초(기본값) 동안 프로파일링한 결과를 리포트하게 됩니다. (일부 편집)
select * from table(tpack.latch_profile_report(&session_id, 'LATCH_NAME, CODE_NAME, OBJ_WHY', 'cache buffers chains'));

-- HELD_TIME, AVG_HELD_TIME은 millisecond(1/1000) 단위
SID LATCH_NAME           CODE_NAME   OBJ_WHY      HELD_PCT  HELD_TIME AVG_HELD_TIME
--- -------------------- ----------  ---------- ---------- ---------- -------------
170 cache buffers chains kcbgtcr: f  4/757              .2       13.2             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/748              .2       10.9             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/758              .1        9.4             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/749              .1        7.5             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/743               0         .1             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/742               0         .1             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/744               0         .1             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/1289              0          0             0
                         ast path ( 
                         cr pin) 
 
170 cache buffers chains kcbgtcr: f  4/741               0          0             0
                         ast path ( 
                         cr pin) 
 
터치 카운트에 의한 분석 결과와 동일하게 (4/757) 블록에 대한 점유시간이 가장 긴 것을 알 수 있습니다. 이 방법은 다음과 같은 장점이 있습니다.
  • 특정 시간 구간동안의 핫 블록을 추적할 수 있다. vs. 터치 카운트는 누적 값이다.
  • 특정 세션 레벨로 추적할 수 있다. vs. 터치 카운트는 세션 레벨이 아닌 시스템 레벨의 값이다.
  • 래치를 획득한 이유(CODE_NAME)까지 알 수 있다.
조금 더 수고를 들임으로써 터치 카운트에 기반한 평면적인 방법에 비해 훨씬 정확하고(물론 샘플링이라서 한계는 있지만) 다양한 데이터를 얻을 수 있습니다. 성능 분석에 필요한 데이터를 효과적으로 얻고 리포팅하는 것! 이것이 티팩의 핵심 사상이라고 할 수 있습니다.

포스트를 적다 보니 이상하게 점점 하드 코어로 변해간다는 생각이... ㅠㅠ

더욱 더 단순하면서도 실용적인 이야기들이 많이 나오도록 노력해야겠습니다.

이전 글 보기:

  1. [티팩] 성능 문제를 트러블슈팅하는 두가지 틀(Frame)
  2. [티팩] oradebug
  3. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 1
저작자 표시
신고
tags : 핫블록
Trackback 0 : Comments 6
  1. Park 2010.07.05 18:26 신고 Modify/Delete Reply

    질문 하나 드리겠습니다.
    운영시간 장 중에 latch: cache buffers chains 엄청 발생 했습니다.
    티팩을 이용하여 핫블록을 찾았습니다.
    이제 욱짜님은 어떻게 하실건가요?
    효용성에 의문이 들어서 질문을 드립니다.

    • 욱짜 2010.07.05 22:12 신고 Modify/Delete

      적절한 지적입니다. 유용성이 없는 지식은 무의미하죠.

      핫 블록 문제는 상당히 중요하면서도 지나치게 지엽적일 수 있는 문제입니다. 정말 간단하게 정리하면 아래 정도의 과정이 필요하겠습니다.

      1. 우선 latch: cache buffers chains 대기의 발생 원인이 핫블록에 의한 것인지 아닌지에 대한 판단이 필요합니다.

      2. 핫블록에 의한 것이라면 어떤 종류의 블록인지(인덱스 루트? 브랜치? 리프? 테이블 데이터? BMB? Segment Header?), 어떤 일을 하는 중인지 등에 대한 종합적인 정보가 필요합니다.

      3. 만일 정상적인 Read에 의해 발생하는 것이라면 동시성을 줄이는 것이 유일한 방법입니다.

      4. 정상적인 Read에 의한 것이 아니라면 버그입니다. 좀 더 추적을 해서 버그를 해결해야 합니다.

      이런 문제에 대한 저의 관점은 "우리는 지금 희귀병을 다루고 있다"는 것입니다. 99%의 기본적인 지식으로는 해결안되는 1%의 문제를 다루고 있다. 이말은 거꾸로 99% 경우에는 써먹을 데도 없는 지식일수도 있다는 것입니다. 하지만 이 1%를 해결하는 것이 때론 더 중요할 때도 있습니다.

      아래 URL에 Latch Profile Report가 어느 정도의 의미있는 데이터를 추출할 수 있는지를 보여주는 예가 있습니다. 도움이 될 것으로 봅니다.
      http://sites.google.com/site/otpack/guide/examples#TOC-Logical-Reads-

      PS) 티팩은 핫블록을 찾아주지 않습니다. 아마 다른 어떤 방법도 마찬가지일 것입니다. 핫블록이 도대체 무엇인지부터 명확한 정의가 필요하겠습니다. 요는 철저하게 사람의 몫이라는 것입니다.

  2. Park 2010.07.06 08:31 신고 Modify/Delete Reply

    감사합니다. 99% 경우에는 써먹을 데도 없는 지식일수도 있다는 것입니다. 하지만 이 1%를 해결하는 것이 때론 더 중요할 때도 있다. 이부분이 와닿습니다. ^^

  3. Eddy 2010.07.06 17:07 신고 Modify/Delete Reply

    파레토 법칙을 적용해보면 그 1%가 결코 1%가 아닐꺼라고 봅니다. ㅋㅋ

    또한 인터널한 것을 알려고 노력하다 보면
    늘 보던 것에서도 새로운 것을 볼수 있게 되고,
    이러한 경험은 지식의 창조적 재조합을 가능하게 하며,
    결국 새로운 경지로 나아갈 수 있는 힘을 갖게 된다고 봅니다.

    늘 같은 자리를 맴돌며 안전함을 즐기는 사람이 아주 좋은 사람인지는 모르겠습니다.
    하지만 뭔가 새로운 것을 찾아 깊이 탐구하는 사람은 대개 좋은 사람이라고 봅니다.

    저는 이 블로그의 주인은 매우 어려운 것을 단순하고 쉽게 풀어내는데
    탁월한 능력이 있는 분이라고 생각해 왔습니다.

    앞으로도 잘 부탁드리겠습니다. ^^

  4. clipper 2015.03.22 12:13 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


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

티스토리 툴바