태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'2010/09'에 해당되는 글 6건

  1. 2010.09.28 대기이벤트가 포함된 10046 진단이벤트 실행하기 - Oracle 11g (4)
  2. 2010.09.16 9월의 엑셈 오라클 퀴즈에 도전해보세요. (3)
  3. 2010.09.15 Library Cache Pin에 대한 Self Deadlock 만들기
  4. 2010.09.15 ASH Viewer (3)
  5. 2010.09.14 V$ACTIVE_SESSION_HISTORY (2)
  6. 2010.09.07 Remote SQL (4)

대기이벤트가 포함된 10046 진단이벤트 실행하기 - Oracle 11g

오라클/대기이벤트 2010.09.28 15:22
Oracle 11g에서 대기 이벤트가 포함된 10046 진단 이벤트를 실행하는 방법을 정리해봤습니다.

1. 전통적인 10046 진단 이벤트 사용법은 다음과 같습니다. 레벨 8 이상이면 대기 이벤트가 기록됩니다.

alter session set events '10046 trace name context forever, level 8';

select /* 10046 */ count(*) from user_objects;

alter session set events '10046 trace name context off';
2. 11g에서는 다음과 같이 sql_trace라는 "인식 가능한" 이름의 진단 이벤트를 사용할 수 있습니다.
-- sql_trace + level 8
alter session set events 'sql_trace level 8';

select /* sql_trace_1 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true
alter session set events 'sql_trace wait=true';

select /* sql_trace_2 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true, bind=true
alter session set events 'sql_trace wait=true, bind=true';

select /* sql_trace_3 */ count(*) from user_objects;

alter session set events 'sql_trace off';
11g에서 추가된 새로운 진단 이베트 기능은 매우 강력해서 아래와 같이 SQL을 지정할 수도 있습니다. 너무나 유용한 기능이죠.
-- SQL_ID 얻어오고...
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;

col sql_id new_value sql_id1
select sql_id 
from v$sqlarea 
where sql_text = 'select /* sql_trace_4 */ count(*) from user_objects';

col sql_id new_value sql_id2
select sql_id 
from v$sqlarea 
where sql_text = 'select /* sql_trace_5 */ count(*) from user_objects';

-- 하나의 sql에 대해서
alter session set events 'sql_trace [sql: &sql_id1] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';

-- 여러 개의 sql에 대해서
alter session set events 'sql_trace [sql: &sql_id1 | &sql_id2] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';
3. DBMS_MONITOR 패키지가 오라클이 공식적으로 추천하는 방법입니다. DBMS_MONITOR 패키지에서도 SQL을 특정할 수 있는 기능이 제공된다면 좋겠지만 개발자들이 아직 거기까지는 고려하지 못한 것 같습니다.
exec dbms_monitor.session_trace_enable(waits=>true);

select /* dbms_monitor */ count(*) from user_objects;

exec dbms_monitor.session_trace_disable;
4. DBMS_SYSTEM 패키지나 DBMS_SUPPORT 패키지같은 히든 패키지에서도 비슷한 기능을 제공합니다만, DBMS_MONITOR 패키지가 등장한 이상 필요가 없어졌죠.
col sid new_value sid
col serial# new_value se
select sid, serial# 
from v$session 
where sid = userenv('sid');

exec sys.dbms_system.set_ev(&sid, &se, 10046, 8, null);

select /* dbms_system */ count(*) from user_objects;

exec sys.dbms_system.set_ev(&sid, &se, 10046, 0, null);
SQL_ID 값을 이용해서 특정 SQL에 대해서만 진단 이벤트를 수행할 수 있는 기능이 특히 유용해보입니다. 오라클 11g의 확장된 진단 이벤트 기능은 아래 문서에 자세히 소개되어 있습니다.
저작자 표시
신고
Trackback 0 : Comments 4
  1. park1q 2010.09.30 12:33 신고 Modify/Delete Reply

    10G 이상에서는 v$session 보면 어떤 세션이 trace 중인가 하는 칼럼이 sql_trace sql_trace_binds, sql_trace_waits 등이 있어 알기 쉬운에..혹시 9i 에도 이걸 알수 있는 뭔가가 있을까요?

    • 욱짜 2010.09.30 13:42 신고 Modify/Delete

      저도 이 문제를 고민한 적이 있는데 뚜렷한 방법을 찾지 못해서 포기했었더랬습니다. 이후로도 아직 도움이 될만한 정보를 얻지 못하고 있어요.

  2. 물곰 2010.10.06 17:08 신고 Modify/Delete Reply

    해당 명령어로 Trace를 잡을 시 Trace파일의 Prefix를 지정할수 있는 방법은 없을까요?

    • 욱짜 2010.10.06 18:27 신고 Modify/Delete

      아마 질문의 의도가 SQL 트레이스가 동작하는 시점에 트레이스 파일의 Identifier(tracefile_identifier)를 바꾸는 것을 의미하시는 것 같습니다.

      제가 알기로는 아직 그런 기능은 없는 것 같습니다.

Write a comment


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

오라클/기타 2010.09.16 09:34
이런, 이런. 한달이 너무 빨리 돌아오는군요.

이번 달 퀴즈도 역시 쉽습니다. 재빨리 도전해보시고 추석 선물로 받으시면 어떨까요!

저작자 표시
신고
Trackback 0 : Comments 3
  1. 욱짜 2010.09.27 09:56 신고 Modify/Delete Reply

    아아... 이번 퀴즈는 대실패입니다. 문제를 잘못 골랐어요.

    그래도 퀴즈는 쭉~

  2. 라튜니 2010.10.01 08:47 신고 Modify/Delete Reply

    퀴즈 정답 발표는 안 하나요~? 발표기간이 넘은거 같아서요.

    • 욱짜 2010.10.01 15:49 신고 Modify/Delete

      추석이 끼어있어서 좀 지연되었습니다. 다음 주 화요일에 발표 예정입니다.

      이번에는 문제가 조금 잘못 출제되어서 좀 불안합니다. ㅠㅠ

Write a comment


Library Cache Pin에 대한 Self Deadlock 만들기

오라클/대기이벤트 2010.09.15 14:19
Library Cache Pin에 대한 Self Deadlock을 아주 간단한게 만들어보겠습니다. 우선 다음과 같이 텅빈 프로시저 TEST_PROC1을 만듭니다.
create or replace procedure test_proc1 
is
begin
	null;
end;
/
그리고 아래의 PL/SQL 블록에서 TEST_PROC1을 실행하고, 곧이어 TEST_PROC1 프로시저를 컴파일합니다. 그러면 행(Hang) 상태에 빠져버립니다.
TPACK@ukja1120> begin
  2     test_proc1;
  3
  4     execute immediate 'alter procedure test_proc1 compile';
  5
  6  end;
  7  /

...
(Hang)
Active Session History를 통해 행 상태에 빠진 세션이 어떤 대기 상태인지 확인해보면 아래와 같이 library cache pin 이벤트를 무한 대기(는 아니고 타임아웃이 발생할 때까지)하고 있는 것을 알 수 있습니다. 그리고 Blocker와 SID가 동일한 것으로부터 Self Deadlock 상태에 빠진 것을 알 수 있습니다.
select * 
from (
	select
		h.session_id as sid,
		to_char(h.sample_time,'mi:ss') as sample_time,
		h.sql_id,
		(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
		event,
		blocking_session as blocker
	from
		v$active_session_history h
	where
		h.session_id = &sid
	order by h.sample_time desc			
) where rownum <= 20	
;					

 SID SAMPL SQL_ID        SQL_TEXT             EVENT         BLOCKER
---- ----- ------------- -------------------- ---------- ----------
 136 49:10                                    library ca        136
                                              che pin

 136 49:09                                    library ca        136
                                              che pin

 136 49:08                                    library ca        136
                                              che pin

 136 49:07                                    library ca        136
                                              che pin

 136 49:06                                    library ca        136
                                              che pin

 136 49:05                                    library ca        136
                                              che pin

 136 49:04                                    library ca        136
                                              che pin

 136 49:03                                    library ca        136
                                              che pin

 136 49:02                                    library ca        136
                                              che pin

 136 49:01                                    library ca        136
                                              che pin

 136 49:00                                    library ca        136
                                              che pin

 136 48:59                                    library ca        136
                                              che pin

 136 48:58                                    library ca        136
                                              che pin

 136 48:57                                    library ca        136
                                              che pin

 136 48:56                                    library ca        136
                                              che pin

 136 48:55                                    library ca        136
                                              che pin

 136 48:54                                    library ca        136
                                              che pin

 136 48:53                                    library ca        136
                                              che pin

 136 48:52                                    library ca        136
                                              che pin

 136 48:51                                    library ca        136
                                              che pin


20 rows selected.
티팩을 통해서 해당 세션 정보를 확인해보면 더욱 명확한 정보를 얻을 수 있습니다.
TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))

NAME                           VALUE
------------------------------ --------------------
SID                            136
Serial#                        2797
SPID                           5148
Program                        sqlplus.exe
Process                        5404:672
Module                         SQL*Plus
SQL ID                         9pbva4bn2m25b
Child No                       0
SQL Text                       alter procedure test
                               _proc1 compile

Status                         ACTIVE
Blocking Instance              1
Blocking Session               136
SQL Exec Start                 2010/09/15 13:45:34
Event                          library cache pin
Seq#                           130
P1(P1raw)                      384372376(0000000016
                               E90E98)

P2(P2raw)                      384372376(0000000016
                               DAB608)

P3(P3raw)                      384372376(00014F8500
                               010003)

Seconds in wait                40
State                          WAITING
Wait Event                     library cache pin
Holder SID                     136
Namespace                      TABLE/PROCEDURE
Object                         TEST_PROC1
Holding Mode                   2(S)
위의 결과를 잘 해석해보면 TEST_RPOC1 프로시저를 실행한 후 Library Cache Pin을 해제하지 않고 Shared Mode로 계속해서 점유하고 있는 것이 이유라는 것을 알 수 있습니다. TEST_PROC1을 컴파일하려면 Library Cache Pin을 Exclusive Mode로 획득해야하는데, Shared Mode의 Library Cache Pin이 아직 해제되지 않았기 때문에 대기하게 되는 것입니다.

왜 Shared Mode의 Library Cache Pin을 곧바로 해제하지 않는가가? 그 이유는 MOS 노트 264476.1에서 설명되어 있습니다.

When a stored procedure is called from another stored procedure/function/PLSQL block, 'LIBRARY CACHE PIN' held on called stored procedures is released only at the end of the calling stored procedure or PL/SQL block (not immediately after execution of called procedure ). So, any DDL operation on the called stored procedures will wait on 'LIBRARY CACHE PIN' until full pl/sql block is completed. However, you can execute called the procedure.

이 현상을 좀 더 확장해보면 최악의 경우 아래와 같이 불필요한 Library Cache Pin 대기를 유발할 수 있습니다.

-- Session #1은 실행 중
TPACK@ukja1120> begin
  2     test_proc1;
  3
  4     dbms_lock.sleep(120);
  5
  6  end;
  7  /
... (실행 중)

-- Session #2는 Session #1 때문에 컴파일을 못해서 대기
TPACK@ukja1120> alter procedure test_proc1 compile;
... (대기 중) 

-- Session #3은 Session #2 때문에 실행을 못하고 대기
TPACK@ukja1120> exec test_proc1;
... (대기 중)
저작자 표시
신고
Trackback 0 : Comment 0

Write a comment


ASH Viewer

오라클 2010.09.15 11:33
Active Session History의 활용을 극대화하고 싶지만, 비싼 툴을 살 여유가 없다면 ASH Viewer를 활용해보시면 어떨까요? 액티브 세션에 기반한 모니터링 툴로 써볼만한 것 같습니다.

저작자 표시
신고

'오라클' 카테고리의 다른 글

ASH Viewer  (3) 2010.09.15
V$ACTIVE_SESSION_HISTORY  (2) 2010.09.14
Remote SQL  (4) 2010.09.07
엑셈 퀴즈에 도전해보세요.  (6) 2010.08.25
Deferred Segment Creation의 재미있는 두가지 버그  (0) 2010.08.23
tags : ASH Viewer
Trackback 0 : Comments 3
  1. park1q 2010.09.29 16:23 신고 Modify/Delete Reply

    비슷한 프로그램 개발중인데..정말 유용하네요.
    처음으로 글남기는데..많이 도움 주셔서 감사합니다.

    • 욱짜 2010.09.29 17:30 신고 Modify/Delete

      트라이얼 버전 나오면 사용할 수 있도록 알려주세요~

      공유할 수 있는 아이디어는 서로 공유하면 좋죠.

  2. park1q 2010.09.30 12:27 신고 Modify/Delete Reply

    네..알겠습니다.
    트라이얼 나오면 가장 먼저 보내 드리겠습니다.

    ASH Viewer 9i 에서도 테스트 해 봤는데..10g 만큼은 아니더라도 꽤 만족스런 결과가 나오더군요..근데..실시간 수행되는 sql 이 매번 소프트 파싱은 하네요..
    버클리 DB 도 쓰는사람이 있네요..ㅋㅋ
    formatter 하고 parser 에 관심이 있었는데..Blanco Sql Formatter 라는것도 한번 보게 되었구요..
    일본 사람이더군요..
    --------------------------------

    Used libraries:

    Oracle Berkeley DB Java Edition
    Version 3.3.75
    http://www.oracle.com/database/berkeley-db

    JFreeChart
    Version 1.0.12
    http://www.jfree.org

    E-Gantt
    Version 0.5.3
    http://egantt.wikispaces.com

    SwingLabs Swing Component Extensions
    Version 0.9.5
    http://swinglabs.java.sun.com

    Joda Time - Java date and time API
    Version 1.6
    http://joda-time.sourceforge.net

    Blanco Sql Formatter
    Version 0.1.1
    http://www.igapyon.jp/blanco/blanco.ja.html

    jEdit Syntax Package
    Version 2.2.2
    http://sourceforge.net/projects/jedit-syntax

Write a comment


V$ACTIVE_SESSION_HISTORY

오라클 2010.09.14 15:19
오라클이 제공하는 Active Session History가 버전에 따라 얼마나 많은 개선이 이루어지는지 눈으로 확인해보시기 바랍니다.

Oracle 10.2.0.1

TPACK@ukja1021> desc v$active_session_history
           Name                            Null?    Type
           ------------------------------- -------- --------------

    1      SAMPLE_ID                                NUMBER
    2      SAMPLE_TIME                              TIMESTAMP(3)
    3      SESSION_ID                               NUMBER
    4      SESSION_SERIAL#                          NUMBER
    5      USER_ID                                  NUMBER
    6      SQL_ID                                   VARCHAR2(13)
    7      SQL_CHILD_NUMBER                         NUMBER
    8      SQL_PLAN_HASH_VALUE                      NUMBER
    9      FORCE_MATCHING_SIGNATURE                 NUMBER
   10      SQL_OPCODE                               NUMBER
   11      SERVICE_HASH                             NUMBER
   12      SESSION_TYPE                             VARCHAR2(10)
   13      SESSION_STATE                            VARCHAR2(7)
   14      QC_SESSION_ID                            NUMBER
   15      QC_INSTANCE_ID                           NUMBER
   16      BLOCKING_SESSION                         NUMBER
   17      BLOCKING_SESSION_STATUS                  VARCHAR2(11)
   18      BLOCKING_SESSION_SERIAL#                 NUMBER
   19      EVENT                                    VARCHAR2(64)
   20      EVENT_ID                                 NUMBER
   21      EVENT#                                   NUMBER
   22      SEQ#                                     NUMBER
   23      P1TEXT                                   VARCHAR2(64)
   24      P1                                       NUMBER
   25      P2TEXT                                   VARCHAR2(64)
   26      P2                                       NUMBER
   27      P3TEXT                                   VARCHAR2(64)
   28      P3                                       NUMBER
   29      WAIT_CLASS                               VARCHAR2(64)
   30      WAIT_CLASS_ID                            NUMBER
   31      WAIT_TIME                                NUMBER
   32      TIME_WAITED                              NUMBER
   33      XID                                      RAW(8)
   34      CURRENT_OBJ#                             NUMBER
   35      CURRENT_FILE#                            NUMBER
   36      CURRENT_BLOCK#                           NUMBER
   37      PROGRAM                                  VARCHAR2(64)
   38      MODULE                                   VARCHAR2(48)
   39      ACTION                                   VARCHAR2(32)
   40      CLIENT_ID                                VARCHAR2(64)
Oracle 11.2.0.1
TPACK@ukja1120> desc v$active_session_history
           Name                            Null?    Type
           ------------------------------- -------- ---------------

    1      SAMPLE_ID                                NUMBER
    2      SAMPLE_TIME                              TIMESTAMP(3)
    3      IS_AWR_SAMPLE                            VARCHAR2(1)
    4      SESSION_ID                               NUMBER
    5      SESSION_SERIAL#                          NUMBER
    6      SESSION_TYPE                             VARCHAR2(10)
    7      FLAGS                                    NUMBER
    8      USER_ID                                  NUMBER
    9      SQL_ID                                   VARCHAR2(13)
   10      IS_SQLID_CURRENT                         VARCHAR2(1)
   11      SQL_CHILD_NUMBER                         NUMBER
   12      SQL_OPCODE                               NUMBER
   13      SQL_OPNAME                               VARCHAR2(64)
   14      FORCE_MATCHING_SIGNATURE                 NUMBER
   15      TOP_LEVEL_SQL_ID                         VARCHAR2(13)
   16      TOP_LEVEL_SQL_OPCODE                     NUMBER
   17      SQL_PLAN_HASH_VALUE                      NUMBER
   18      SQL_PLAN_LINE_ID                         NUMBER
   19      SQL_PLAN_OPERATION                       VARCHAR2(30)
   20      SQL_PLAN_OPTIONS                         VARCHAR2(30)
   21      SQL_EXEC_ID                              NUMBER
   22      SQL_EXEC_START                           DATE
   23      PLSQL_ENTRY_OBJECT_ID                    NUMBER
   24      PLSQL_ENTRY_SUBPROGRAM_ID                NUMBER
   25      PLSQL_OBJECT_ID                          NUMBER
   26      PLSQL_SUBPROGRAM_ID                      NUMBER
   27      QC_INSTANCE_ID                           NUMBER
   28      QC_SESSION_ID                            NUMBER
   29      QC_SESSION_SERIAL#                       NUMBER
   30      EVENT                                    VARCHAR2(64)
   31      EVENT_ID                                 NUMBER
   32      EVENT#                                   NUMBER
   33      SEQ#                                     NUMBER
   34      P1TEXT                                   VARCHAR2(64)
   35      P1                                       NUMBER
   36      P2TEXT                                   VARCHAR2(64)
   37      P2                                       NUMBER
   38      P3TEXT                                   VARCHAR2(64)
   39      P3                                       NUMBER
   40      WAIT_CLASS                               VARCHAR2(64)
   41      WAIT_CLASS_ID                            NUMBER
   42      WAIT_TIME                                NUMBER
   43      SESSION_STATE                            VARCHAR2(7)
   44      TIME_WAITED                              NUMBER
   45      BLOCKING_SESSION_STATUS                  VARCHAR2(11)
   46      BLOCKING_SESSION                         NUMBER
   47      BLOCKING_SESSION_SERIAL#                 NUMBER
   48      BLOCKING_INST_ID                         NUMBER
   49      BLOCKING_HANGCHAIN_INFO                  VARCHAR2(1)
   50      CURRENT_OBJ#                             NUMBER
   51      CURRENT_FILE#                            NUMBER
   52      CURRENT_BLOCK#                           NUMBER
   53      CURRENT_ROW#                             NUMBER
   54      TOP_LEVEL_CALL#                          NUMBER
   55      TOP_LEVEL_CALL_NAME                      VARCHAR2(64)
   56      CONSUMER_GROUP_ID                        NUMBER
   57      XID                                      RAW(8)
   58      REMOTE_INSTANCE#                         NUMBER
   59      TIME_MODEL                               NUMBER
   60      IN_CONNECTION_MGMT                       VARCHAR2(1)
   61      IN_PARSE                                 VARCHAR2(1)
   62      IN_HARD_PARSE                            VARCHAR2(1)
   63      IN_SQL_EXECUTION                         VARCHAR2(1)
   64      IN_PLSQL_EXECUTION                       VARCHAR2(1)
   65      IN_PLSQL_RPC                             VARCHAR2(1)
   66      IN_PLSQL_COMPILATION                     VARCHAR2(1)
   67      IN_JAVA_EXECUTION                        VARCHAR2(1)
   68      IN_BIND                                  VARCHAR2(1)
   69      IN_CURSOR_CLOSE                          VARCHAR2(1)
   70      IN_SEQUENCE_LOAD                         VARCHAR2(1)
   71      CAPTURE_OVERHEAD                         VARCHAR2(1)
   72      REPLAY_OVERHEAD                          VARCHAR2(1)
   73      IS_CAPTURED                              VARCHAR2(1)
   74      IS_REPLAYED                              VARCHAR2(1)
   75      SERVICE_HASH                             NUMBER
   76      PROGRAM                                  VARCHAR2(64)
   77      MODULE                                   VARCHAR2(48)
   78      ACTION                                   VARCHAR2(32)
   79      CLIENT_ID                                VARCHAR2(64)
   80      MACHINE                                  VARCHAR2(64)
   81      PORT                                     NUMBER
   82      ECID                                     VARCHAR2(64)
   83      TM_DELTA_TIME                            NUMBER
   84      TM_DELTA_CPU_TIME                        NUMBER
   85      TM_DELTA_DB_TIME                         NUMBER
   86      DELTA_TIME                               NUMBER
   87      DELTA_READ_IO_REQUESTS                   NUMBER
   88      DELTA_WRITE_IO_REQUESTS                  NUMBER
   89      DELTA_READ_IO_BYTES                      NUMBER
   90      DELTA_WRITE_IO_BYTES                     NUMBER
   91      DELTA_INTERCONNECT_IO_BYTES              NUMBER
   92      PGA_ALLOCATED                            NUMBER
   93      TEMP_SPACE_ALLOCATED                     NUMBER
위의 데이터들을 보면 기대하지 않았던 다양한 값들까지 제공되는 것을 알 수 있습니다. 풍부한 Active Session History 데이터는 전통적으로 3rd Party 툴들이 장점을 보이던 분야였지만, Oracle 11g부터는 데이터가 크게 보완되어 그런 장점들이 의미가 점점 희석되고 있습니다.

Active Session History 기능이 Oracle 10g에 와서야 오라클에 추가된 것은 너무 늦은 감이 있지만, 버전이 올라갈 수록 풍부한 데이터를 제공하는 것을 보면 역시 오라클이라는 생각을 하게 만듭니다. 단, 여전히 부족한 점들이 보이는데요, 버전이 올라가면서 더 많은 데이터가 제공되기를 기대해봅니다.

저작자 표시
신고

'오라클' 카테고리의 다른 글

ASH Viewer  (3) 2010.09.15
V$ACTIVE_SESSION_HISTORY  (2) 2010.09.14
Remote SQL  (4) 2010.09.07
엑셈 퀴즈에 도전해보세요.  (6) 2010.08.25
Deferred Segment Creation의 재미있는 두가지 버그  (0) 2010.08.23
Trackback 0 : Comments 2
  1. tohappy 2010.11.22 11:31 신고 Modify/Delete Reply

    같은맥락으로 9i 와 8i 등에서는 어떤 view가 있을까요?

    • 욱짜 2010.11.22 14:48 신고 Modify/Delete

      8i와 9i에서 10g와 비슷한 효과를 내려면 V$SESSION + V$SESSINO_WAIT 뷰 등을 주기적으로 샘플링하면서 데이터를 저장하고, 저장된 데이터를 읽는 수 밖에 없습니다.

      비슷한 시도들을 하는 경우도 있는데요.
      http://sourceforge.net/projects/orasash/

      위와 같은 아이디어를 이용해서 직접 구현해보셔도 좋습니다. 아주 간단하게는 SPOOL 명령을 이용해서 5초에 한번씩 세션 리스트 정보를 텍스트 파일에 저장하고, 나중에 필요할 때 해당 테스트 파일을 External Table 기능을 이용해서 읽도록 하는 것도 가능합니다.

      하지만 어떤 방법을 사용하더라도 10g에서 제공하는 ASH를 능가할 수는 없습니다. 10g의 ASH는 DMA 방식으로 데이터를 읽기 때문에 가장 가볍습니다.

Write a comment


Remote SQL

오라클 2010.09.07 10:22
얼마전에 Database Link를 사용하는 분산 쿼리(Distributed Query)의 동작 원리에 대한 질문을 받았습니다. 그에 대한 답을 간단한 테스트로 하려고 합니다.

테스트 환경은 Oracle 11.1.0.6입니다.

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

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
분산 쿼리(Distributed Query)를 위해 Loopback Database Link를 만듭니다. Loopback Database Link란 자기 자신이 원격 데이터베이스가 되는 Database Link를 의미합니다.
TPACK@ukja1106> -- create loopback database link
TPACK@ukja1106> create public database link loopback
  2  connect to {user}
  3  identified by {password}
  4  using '{service_name}';

Database link created.
테이블 T1, T2를 만듭니다.
TPACK@ukja1106> -- create table
TPACK@ukja1106> create table t1(c1, c2)
  2  as
  3  select level, level
  4  from dual
  5  connect by level <= 1000
  6  ;

Table created.

TPACK@ukja1106> 
TPACK@ukja1106> create table t2(c1, c2)
  2  as
  3  select level, level
  4  from dual
  5  connect by level <= 1000
  6  ;

Table created.

TPACK@ukja1106> 
TPACK@ukja1106> create index t2_n1 on t2(c1);

Index created.

TPACK@ukja1106> 
TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.
테이블 T1(로컬)이 선행 테이블이 되고 테이블 T2(원격)가 조인 대상이 되는 분산 쿼리(Distributed Query)의 실행 계획입니다. Remote SQL Information 부분에 주목해주시기 바랍니다.
TPACK@ukja1106> -- explain plan
TPACK@ukja1106> -- nested loops join
TPACK@ukja1106> 
TPACK@ukja1106> -- execute it, but 0 row
TPACK@ukja1106> explain plan for
  2  select /*+ leading(t1) use_nl(d) */
  3  	*
  4  from t1, t2@loopback d
  5  where t1.c1 = d.c1
  6  	and t1.c1 < 0
  7  ;

TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|   1 |  NESTED LOOPS      |      |        |      |
|*  2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   | LOOPB~ | R->S |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."C1"<0)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"
       (accessing 'LOOPBACK' )
Remote SQL이란 분산 쿼리를 실행할 때 원격 데이터베이스로부터 데이터를 전송받기 위해 원격에서 실행하게 될 SQL을 말합니다. 즉 로컬 데이터베이스는 Remote SQL을 원격 데이터베이스로 요청해서 원하는 데이터를 받아옵니다.

로컬 데이터베이스는 원격 데이터베이스로부터 원격 테이블과 인덱스의 기본 통계 정보를 전송받습니다. 그리고 실행 계획을 만듭니다. 이 과정에서 Remote SQL을 만듭니다. Remote SQL은 실제로 데이터를 전송받기 위해서 실행하기 전까지는 원격 데이터베이스에 보내지지 않습니다.

이를 증명하기 위해 쿼리를 실행한 후 원격 데이터베이스에서 Remote SQL이 실행되었는지 확인해보겠습니다. 아래 쿼리를 실행하면, 선행 테이블에서 한건의 로우도 나오지 않기 때문에(t1.c1 <0 조건 때문에) 원격 데이터베이스로의 데이터 요청이 없을 것입니다. 아래 결과를 보면 이 가정이 맞다는 것을 알 수 있습니다.

TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */
  2  	*
  3  from t1, t2@loopback d
  4  where t1.c1 = d.c1
  5  	and t1.c1 < 0
  6  ;

no rows selected

TPACK@ukja1106> 
TPACK@ukja1106> select sql_id, executions
  2  from v$sqlarea
  3  where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"';

no rows selected
이번에는 동일한 테스트를 1,000 번의 데이터 요청을 원격 데이터베이스로 보내는 분산 쿼리에 대해 수행해봅니다.
TPACK@ukja1106> -- execute it, for 1000 rows
TPACK@ukja1106> explain plan for
  2  select /*+ leading(t1) use_nl(d) */
  3  	*
  4  from t1, t2@loopback d
  5  where t1.c1 = d.c1
  6  ;

Explained.

TPACK@ukja1106> 
TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|   1 |  NESTED LOOPS      |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   | LOOPB~ | R->S |
---------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1" (accessing
       'LOOPBACK' )
1,000 번의 데이터 요청을 보낸 결과, Remote SQL이 원격 데이터베이스에서 1,000번 수행되었습니다. 이것은 마치 SQL*Plus에서 해당 쿼리를 1,000번 수행한 것과 동일합니다.
TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */
  2  	*
  3  from t1, t2@loopback d
  4  where t1.c1 = d.c1
  5  ;

        C1         C2         C1         C2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
...
       999        999        999        999
      1000       1000       1000       1000

1000 rows selected.

TPACK@ukja1106> 
TPACK@ukja1106> col sql_id new_value sql_id
TPACK@ukja1106> select sql_id, executions
  2  from v$sqlarea
  3  where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1"';

SQL_ID        EXECUTIONS
------------- ----------
6skxmvb24s6v4       1000
원격 데이터베이스에서의 Remote SQL의 실행 계획은 DBMS_XPLAN.DISPLAY_CURSOR 함수를 이용하면 알 수 있습니다. 아래와 같이 Index Range Scan이 사용되었습니다.
TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=:1)
이번에는 Nested Loops Join이 아닌 Hash Join에 대해서 동일한 테스트를 수행해보겠습니다. Remote SQL은 다음과 같습니다.
TPACK@ukja1106> explain plan for
  2  select /*+ leading(t1) use_hash(d) */
  3  	*
  4  from t1, t2@loopback d
  5  where t1.c1 = d.c1
  6  ;

Explained.

TPACK@ukja1106> 
TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   | LOOPB~ | R->S |
---------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="D"."C1")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D" (accessing 'LOOPBACK' )
비록 1,000건을 페치하는 것은 동일하지만, Hash Join의 특성상 원격 데이터베이스로 1,000번을 수행 요청하는 것이 아니라 1번의 요청으로 원하는 데이터를 얻어옵니다. 따라서 실행 회수(EXECUTIONS)는 "1"입니다.
TPACK@ukja1106> select /*+ leading(t1) use_hash(d) */
  2  	*
  3  from t1, t2@loopback d
  4  where t1.c1 = d.c1
  5  ;

        C1         C2         C1         C2
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          3          3          3
...
       999        999        999        999
      1000       1000       1000       1000

1000 rows selected.

TPACK@ukja1106> col sql_id new_value sql_id
TPACK@ukja1106> select sql_id, executions
  2  from v$sqlarea
  3  where sql_text = 'SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"';

SQL_ID        EXECUTIONS
------------- ----------
0uksumbhuswyx          1

1 row selected.
그리고 Remote SQL은 Table Full SCan의 실행 계획을 가집니다.
TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));

SQL_ID  0uksumbhuswyx, child number 0
-------------------------------------
SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| T2   |  1000 |  7000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
위의 테스트 결과를 보면 다음과 같은 결론을 내릴 수 있습니다.
  • 분산 쿼리의 동작 방식은 로컬 쿼리의 동작 방식과 거의 동일하다.
  • 단, 원격 데이터베이스로부터 데이터를 가져오기 위해 Remote SQL을 원격 데이터베이스로 보낸다. 어떤 Remote SQL을 사용하는지는 DBMS_XPLAN 패키지를 이용해 확인할 수 있다.
  • Remote SQL이 원격 데이터베이스에서 어떤 실행 계획을 가지는지는 원격 데이터베이스에 접속하여 DBMS_XPLAN.DISLAY 함수를 호출해보면 알 수 있다.
앞으로 분산 쿼리를 테스트할 때 위와 같은 방법의 분석이 도움이 되기를 바랍니다.
저작자 표시
신고

'오라클' 카테고리의 다른 글

ASH Viewer  (3) 2010.09.15
V$ACTIVE_SESSION_HISTORY  (2) 2010.09.14
Remote SQL  (4) 2010.09.07
엑셈 퀴즈에 도전해보세요.  (6) 2010.08.25
Deferred Segment Creation의 재미있는 두가지 버그  (0) 2010.08.23
Trackback 0 : Comments 4
  1. IFO 2010.09.10 12:41 신고 Modify/Delete Reply

    앞으로는 11gR2에서 테스트 해주시면 더욱 좋을거 같습니다. ^^
    좀 이르지만 즐거운 추석연휴 되세요~

  2. park1q 2010.10.01 12:50 신고 Modify/Delete Reply

    아~~요즘 자주 들르.네요..
    다름이 아니라 로컬세션과 리모트 세션과의 연결고리를 확인할수 있는 방법이 있을까요.?
    진행중인 액티브 세션및 액티브 트랜잭션이라면 X$K2GTE 의 K2GTITID_ORA 로 확인이 가능할텐데..
    단순 조회후 남아있는 인엑티브 세션과의 고리 말이죠..
    혹시 아시면..부탁 드립니다

    • 욱짜 2010.10.01 15:51 신고 Modify/Delete

      분산 쿼리를 실행한 세션에서 커밋을 아직 수행하지 않았다면 단순 조회 쿼리라고 하더라도 k2gtitid_ora 컬럼으로 확인할 수 있을 겁니다.

Write a comment

티스토리 툴바