오라클 성능 문제에 대한 통찰

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 함수를 호출해보면 알 수 있다.

앞으로 분산 쿼리를 테스트할 때 위와 같은 방법의 분석이 도움이 되기를 바랍니다.

공감

sns신고

저작자표시

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

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 : DATABASE LINK, Remote SQL, 분산 쿼리

Trackback 0 : Comments 4

IFO 2010.09.10 12:41 신고 Modify/Delete Reply
앞으로는 11gR2에서 테스트 해주시면 더욱 좋을거 같습니다. ^^
좀 이르지만 즐거운 추석연휴 되세요~
- 욱짜 2010.09.13 13:26 신고 Modify/Delete
  바야흐로 11gR2의 시대군요.
  
  잘 알겠습니다~

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

: name

: password

: homepage secret mode

◀ PREV : [1] : [···] : [22] : [23] : [24] : [25] : [26] : [27] : [28] : [29] : [30] : [···] : [279] : NEXT ▶

태터데스크 관리자

태터데스크 메시지

욱짜의 블로그

Remote SQL

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

Write a comment

티스토리 툴바