Oracle/Tuning2018. 12. 13. 09:01

DB 링크 튜닝 기법

DB 링크는 유용한 만큼 성능 이슈가 많은 기능이다. DB 링크 튜닝 기법을 살펴보자.


테스트를 위해 아래와 같이 테이블을 생성하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 1-1
DROP DATABASE LINK dl1 ;
CREATE DATABASE LINK dl1 CONNECT TO tuna IDENTIFIED BY "tuna" USING 'ora11gr2';
 
-- 1-2
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 DATE);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 DATE);
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);
CREATE INDEX t2_x3 ON t2 (c3);
 
CREATE OR REPLACE FUNCTION f1 RETURN NUMBER IS BEGIN RETURN 1; END f1;
/

아래 쿼리는 리모트 테이블만 조회했다. 쿼리가 리모트에서 수행되며, 수행 결과가 로컬로 전송된다. 이런 경우 Note에 fully remote statement가 표시된다. 데이터 전송량을 최소화하기 위해 불필요한 열을 기술하지 않는 편이 바람직하다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 2
SELECT * FROM t1@dl1;
 
------------------------------------------------
| Id  | Operation              | Name | Inst   |
------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |        |
|   1 |  TABLE ACCESS FULL     | T1   | ORA11~ |
------------------------------------------------
 
Note
-----
   - fully remote statement

로컬 테이블과 리모트 테이블을 조인한 경우 쿼리가 로컬에서 수행된다. 아래 쿼리는 로컬 테이블(t1)과 리모트 테이블(t2@dl1)을 NL 조인한다. Remote SQL Information에서 조인 조건(b1.c1 = a.c1)이 일반 조건("C1"=:1)으로 변경된 것을 확인할 수 있다. NL 조인의 경우 로컬 테이블의 건수만큼 리모트 쿼리가 수행된다. DB Call에 의한 부하가 발생할 수 있으므로 로컬 테이블의 건수가 작은 경우에만 사용해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 3-1
SELECT /*+ LEADING(A) USE_NL(B) INDEX(B) */
       *
  FROM t1 a, t2@dl1 b
 WHERE b.c1 = a.c1;
 
---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|   1 |  NESTED LOOPS      |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T1   |    DL1 | R->S |
---------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   3 - SELECT /*+ INDEX ("B") USE_NL ("B") */ "C1","C2","C3" FROM "T2" "B" WHERE "C1"=:1 (accessing 'DL1' )


아래 쿼리는 로컬 테이블(t1)과 리모트 테이블(t1@dl1)을 해시 조인한다. 리모트 테이블의 전체 데이터를 수신하여, 로컬에서 조인을 수행한다. 데이터 전송량에 의해 쿼리의 성능이 저하될 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 3-2
SELECT /*+ LEADING(A) USE_HASH(B) */
       *
  FROM t1 a, t1@dl1 b
 WHERE b.c1 = a.c1;
 
---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T1   |    DL1 | R->S |
---------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   3 - SELECT /*+ USE_HASH ("B") */ "C1","C2","C3" FROM "T1" "B" (accessing 'DL1' )

쿼리 3-2에서 리모트 테이블의 크기가 크고, 조인 결과가 작다면, DRIVING_SITE 힌트로 쿼리의 성능을 개선할 수 있다. 아래 쿼리는 로컬 테이블을 리모트로 전송하고, 리모트에서 조인을 수행한 후, 조인 결과를 전송받는다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 3-3
SELECT /*+ LEADING(A) USE_HASH(B) DRIVING_SITE(B) */
       *
  FROM t1 a, t1@dl1 b
 WHERE b.c1 = a.c1;
 
-------------------------------------------------------
| Id  | Operation              | Name | Inst   |IN-OUT|
-------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |        |      |
|*  1 |  HASH JOIN             |      |        |      |
|   2 |   REMOTE               | T1   |      ! | R->S |
|   3 |   TABLE ACCESS FULL    | T1   | ORA11~ |      |
-------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   2 - SELECT "C1","C2","C3" FROM "T1" "A2" (accessing '!' )
 
Note
-----
   - fully remote statement

아래 쿼리는 3개의 테이블을 조인했다. 2개의 리모트 테이블을 전송받아, 로컬에서 조인을 수행한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 3-4
SELECT /*+ LEADING(A) USE_HASH(B C) */
       *
  FROM t1 a, t1@dl1 b, t2@dl1 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1;
 
----------------------------------------------------
| Id  | Operation           | Name | Inst   |IN-OUT|
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |      |
|*  1 |  HASH JOIN          |      |        |      |
|*  2 |   HASH JOIN         |      |        |      |
|   3 |    TABLE ACCESS FULL| T1   |        |      |
|   4 |    REMOTE           | T1   |    DL1 | R->S |
|   5 |   REMOTE            | T2   |    DL1 | R->S |
----------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   4 - SELECT /*+ USE_HASH ("B") */ "C1","C2","C3" FROM "T1" "B" (accessing 'DL1' )
   5 - SELECT /*+ USE_HASH ("C") */ "C1","C2","C3" FROM "T2" "C" (accessing 'DL1' )

쿼리 3-4에서 리모트 테이블의 조인 건수가 작다면, 인라인 뷰를 통해 쿼리의 성능을 개선할 수 있다. 아래 쿼리는 리모트 테이블의 조인을 리모트에서 수행하고, 조인 결과를 전송받은 후, 로컬에서 최종 조인을 수행한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 3-5
SELECT /*+ LEADING(A) USE_HASH(B) NO_MERGE(B) */
       *
  FROM t1 a
     , (SELECT /*+ LEADING(A) USE_HASH(B) */
               a.c1, b.c2
          FROM t1@dl1 a, t2@dl1 b
         WHERE b.c1 = a.c1) b
 WHERE b.c1 = a.c1;
 
---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   VIEW             |      |        |      |
|   4 |    REMOTE          |      |    DL1 | R->S |
---------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   4 - SELECT /*+ LEADING ("A2") USE_HASH ("A1") */
              "A2"."C1","A1"."C2"
         FROM "T1" "A2","T2" "A1"
        WHERE "A1"."C1"="A2"."C1" (accessing 'DL1' )

아래 쿼리는 f1 리모트 함수(f1@dl1)를 사용했다. 쿼리가 리모트에서 수행되었지만, 함수가 필터 조건으로 사용되어 쿼리의 성능이 저하될 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 4-1
SELECT * FROM t1@dl1 WHERE c1 = f1@dl1;
 
------------------------------------------------
| Id  | Operation              | Name | Inst   |
------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |        |
|*  1 |  TABLE ACCESS FULL     | T1   | ORA11~ |
------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A1"."C1"="F1"())
 
Note
-----
   - fully remote statement

아래 쿼리는 함수에 서브 쿼리를 사용했다. 리모트 테이블 전체를 로컬로 전송받은 후, 함수 결과로 필터링하는 것을 확인할 수 있다. 함수는 1번만 수행되었지만, 데이터 전송량 증가로 인해 쿼리의 성능이 저하될 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 4-2
SELECT * FROM t1@dl1 WHERE c1 = (SELECT f1@dl1 FROM DUAL);
 
-------------------------------------------------
| Id  | Operation        | Name | Inst   |IN-OUT|
-------------------------------------------------
|   0 | SELECT STATEMENT |      |        |      |
|*  1 |  FILTER          |      |        |      |
|   2 |   REMOTE         | T1   |    DL1 | R->S |
|   3 |   FAST DUAL      |      |        |      |
-------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"= (SELECT "F1"() FROM "SYS"."DUAL" "DUAL"))
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   2 - SELECT "C1","C2","C3" FROM "T1" "T1" (accessing 'DL1' )

아래 쿼리처럼 DUAL 테이블에 DB 링크를 기술하면 리모트에서 서브 쿼리가 수행된다. 함수 수행 횟수의 감소, 데이터 전송량의 최소화를 통해 쿼리의 성능을 개선할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 4-3
SELECT * FROM t1@dl1 WHERE c1 = (SELECT f1@dl1 FROM DUAL@dl1);
 
------------------------------------------------
| Id  | Operation              | Name | Inst   |
------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |        |
|*  1 |  TABLE ACCESS FULL     | T1   | ORA11~ |
|   2 |   FAST DUAL            |      |        |
------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A1"."C1"= (SELECT "F1"() FROM "SYS"."DUAL" "A2"))
 
Note
-----
   - fully remote statement

아래 쿼리는 SYSDATE 함수를 사용했다. 리모트 서버의 날짜를 기준으로 데이터가 조회된다.[각주:1]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 5-1
SELECT * FROM t2@dl1 WHERE c3 >= SYSDATE - 1;
 
------------------------------------------------------
| Id  | Operation                   | Name  | Inst   |
------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE     |       |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    | ORA11~ |
|*  2 |   INDEX RANGE SCAN          | T2_X3 | ORA11~ |
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A1"."C3">=SYSDATE@!-1)
 
Note
-----
   - fully remote STATEMENT

아래 쿼리는 조인에 의해 로컬에서 수행되고, 리모트 테이블에 SYSDATE 함수를 사용했다. Remote SQL Information에서 SYSDATE 함수가 바인드 변수로 치환된 것을 확인할 수 있다. 로컬 기준 날짜 값으로 조회하므로, 로컬과 리모트의 날짜가 불일치하는 경우 데이터 일관성에 문제가 발생할 수 있다. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 5-2
SELECT /*+ LEADING(A) USE_HASH(B) */
       *
  FROM t1 a, t2@dl1 b
 WHERE b.c1 = a.c1
   AND b.c3 >= SYSDATE - 1;
 
---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   REMOTE           | T2   |    DL1 | R->S |
---------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   3 - SELECT /*+ USE_HASH ("B") */ "C1","C2","C3" FROM "T2" "B" WHERE "C3">=:1-:2 (accessing 'DL1' )

아래 쿼리는 SYSDATE 함수에 서브 쿼리를 사용했지만, 데이터 일관성 문제를 해결할 수 없고, 데이터 전송량이 증가하여 쿼리의 성능이 저하된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 5-3
SELECT /*+ LEADING(A) USE_HASH(B) */
       *
  FROM t1 a, t2@dl1 b
 WHERE b.c1 = a.c1
   AND b.c3 >= (SELECT SYSDATE - 1 FROM DUAL@dl1);
 
----------------------------------------------------
| Id  | Operation           | Name | Inst   |IN-OUT|
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |      |
|*  1 |  FILTER             |      |        |      |
|*  2 |   HASH JOIN         |      |        |      |
|   3 |    TABLE ACCESS FULL| T1   |        |      |
|   4 |    REMOTE           | T2   |    DL1 | R->S |
|   5 |   REMOTE            | DUAL |    DL1 | R->S |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("B"."C3">= (SELECT SYSDATE@!-1 FROM  "DUAL"))
   2 - access("B"."C1"="A"."C1")
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   4 - SELECT /*+ USE_HASH ("B") */ "C1","C2","C3" FROM "T2" "B" (accessing 'DL1' )
   5 - SELECT 0 FROM "DUAL" "DUAL" (accessing 'DL1' )

아래 쿼리는 인라인 뷰를 사용했지만, 쿼리 5-2와 동일하게 수행된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 5-4
SELECT /*+ LEADING(A) USE_HASH(B) NO_MERGE(B) */
       *
  FROM t1 a
     , (SELECT *
          FROM t2@dl1
         WHERE c3 >= SYSDATE - 1) b
 WHERE b.c1 = a.c1;
 
---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   VIEW             |      |        |      |
|   4 |    REMOTE          | T2   |    DL1 | R->S |
---------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   4 - SELECT "C1","C2","C3" FROM "T2" "T2" WHERE "C3">=:1-:2 (accessing 'DL1' )

아래 쿼리처럼 인라인 뷰와 서브 쿼리를 함께 사용해야, 데이터 일관성을 확보하고, 데이터 전송량을 최소화할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 5-5
SELECT /*+ LEADING(A) USE_HASH(B) NO_MERGE(B) */
       *
  FROM t1 a
     , (SELECT *
          FROM t2@dl1
         WHERE c3 >= (SELECT SYSDATE - 1 FROM DUAL@dl1)) b
 WHERE b.c1 = a.c1;
 
---------------------------------------------------
| Id  | Operation          | Name | Inst   |IN-OUT|
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |      |
|*  1 |  HASH JOIN         |      |        |      |
|   2 |   TABLE ACCESS FULL| T1   |        |      |
|   3 |   VIEW             |      |        |      |
|   4 |    REMOTE          |      |    DL1 | R->S |
---------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   4 - SELECT "A1"."C1","A1"."C2","A1"."C3"
         FROM "T2" "A1"
        WHERE "A1"."C3">= (SELECT SYSDATE@!-1 FROM "DUAL" "A2") (accessing 'DL1' )


아래 쿼리는 INSERT 문의 SELECT 문에 DB 링크를 사용했다. 이런 경우 DRIVING_SITE 힌트를 사용할 수 없으며, 무조건 로컬에서 쿼리가 수행된다.[각주:2]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 6-1
INSERT INTO t1 SELECT * FROM t2@dl1;
 
---------------------------------------------------------
| Id  | Operation                | Name | Inst   |IN-OUT|
---------------------------------------------------------
|   0 | INSERT STATEMENT         |      |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |        |      |
|   2 |   REMOTE                 | T2   |    DL1 | R->S |
---------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   2 - SELECT /*+ OPAQUE_TRANSFORM */ "C1","C2" FROM "T2" "T2" (accessing 'DL1' )

아래 쿼리는 최적화되지 않은 DB 링크 사용을 보여준다. t1@dl1 테이블 전체가 전송되며, f1@dl1 함수가 과도하게 수행될 수 있고, 데이터 일관성에 문제가 발생할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 6-2
INSERT
  INTO t1
SELECT /*+ LEADING(A) USE_HASH(B C) */
       a.c1, b.c2, c.c3
  FROM t1 a, t1@dl1 b, t2@dl1 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1
   AND c.c2 = f1@dl1
   AND c.c3 >= SYSDATE - 1;
 
---------------------------------------------------------
| Id  | Operation                | Name | Inst   |IN-OUT|
---------------------------------------------------------
|   0 | INSERT STATEMENT         |      |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |        |      |
|*  2 |   HASH JOIN              |      |        |      |
|*  3 |    HASH JOIN             |      |        |      |
|   4 |     TABLE ACCESS FULL    | T1   |        |      |
|   5 |     REMOTE               | T1   |    DL1 | R->S |
|   6 |    REMOTE                | T2   |    DL1 | R->S |
---------------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   5 - SELECT /*+ OPAQUE_TRANSFORM USE_HASH ("B") */ "C1","C2" FROM "T1" "B" (accessing 'DL1' )
   6 - SELECT /*+ OPAQUE_TRANSFORM USE_HASH ("C") */
              "C1","C2","C3"
         FROM "T2" "C"
        WHERE "C3">=:1-:2
          AND "C2"="F1"() (accessing 'DL1' )

아래 쿼리는 지금까지 살펴본 튜닝 기법을 적용한 쿼리다. 최적의 쿼리 성능을 보장할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 6-3
INSERT
  INTO t1
SELECT /*+ LEADING(A) USE_HASH(B) NO_MERGE(B) */
       a.c1, a.c2, b.c3
  FROM t1 a
     , (SELECT a.c1, a.c2, b.c3
          FROM t1@dl1 a, t2@dl1 b
         WHERE b.c1 = a.c1
           AND b.c2 = (SELECT f1@dl1 FROM DUAL@dl1)
           AND b.c3 >= (SELECT SYSDATE - 1 FROM DUAL@dl1)) b
 WHERE b.c1 = a.c1;
 
--------------------------------------------------
| Id  | Operation                | Inst   |IN-OUT|
--------------------------------------------------
|   0 | INSERT STATEMENT         |        |      |
|   1 |  LOAD TABLE CONVENTIONAL |        |      |
|*  2 |   HASH JOIN              |        |      |
|   3 |    TABLE ACCESS FULL     |        |      |
|   4 |    VIEW                  |        |      |
|   5 |     REMOTE               |    DL1 | R->S |
--------------------------------------------------
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   5 - SELECT "A2"."C1","A2"."C2","A1"."C3"
         FROM "T1" "A2","T2" "A1"
        WHERE "A1"."C1"="A2"."C1"
          AND "A1"."C2"= (SELECT "F1"() FROM "DUAL" "A4")
          AND "A1"."C3">= (SELECT SYSDATE@!-1 FROM "DUAL" "A3") (accessing 'DL1' )


  1. Predicate Information의 SYSDATE@! 함수는 쿼리가 수행되는 서버의 날짜를 조회한다. [본문으로]
  2. Remote SQL Information의 OPAQUE_TRANSFORM 힌트는 리모트 INSERT SELECT 문의 SELECT 문에 기술되며, 리모트 서버 버전에 맞게 쿼리를 변환하는 역할을 수행한다. [본문으로]
저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

PX SELECTOR 오퍼레이션  (0) 2018.12.19
PRECOMPUTE_SUBQUERY 힌트  (0) 2018.12.14
JPPD와 서브 쿼리 팩토링  (0) 2018.12.11
JPPD와 ROLLUP  (0) 2018.12.10
FK로 인한 DML 성능 저하  (0) 2018.12.08
Posted by 정희락_

티스토리툴바