얼마전에
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 함수를 호출해보면 알 수 있다.
앞으로 분산 쿼리를 테스트할 때 위와 같은 방법의 분석이 도움이 되기를 바랍니다.