DB 링크는 유용한 만큼 성능 이슈가 많은 기능이다. DB 링크 튜닝 기법을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 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가 표시된다. 데이터 전송량을 최소화하기 위해 불필요한 열을 기술하지 않는 편이 바람직하다.
-- 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에 의한 부하가 발생할 수 있으므로 로컬 테이블의 건수가 작은 경우에만 사용해야 한다.
-- 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)을 해시 조인한다. 리모트 테이블의 전체 데이터를 수신하여, 로컬에서 조인을 수행한다. 데이터 전송량에 의해 쿼리의 성능이 저하될 수 있다.
-- 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 힌트로 쿼리의 성능을 개선할 수 있다. 아래 쿼리는 로컬 테이블을 리모트로 전송하고, 리모트에서 조인을 수행한 후, 조인 결과를 전송받는다.
-- 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개의 리모트 테이블을 전송받아, 로컬에서 조인을 수행한다.
-- 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에서 리모트 테이블의 조인 건수가 작다면, 인라인 뷰를 통해 쿼리의 성능을 개선할 수 있다. 아래 쿼리는 리모트 테이블의 조인을 리모트에서 수행하고, 조인 결과를 전송받은 후, 로컬에서 최종 조인을 수행한다.
-- 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)를 사용했다. 쿼리가 리모트에서 수행되었지만, 함수가 필터 조건으로 사용되어 쿼리의 성능이 저하될 수 있다.
-- 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번만 수행되었지만, 데이터 전송량 증가로 인해 쿼리의 성능이 저하될 수 있다.
-- 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 링크를 기술하면 리모트에서 서브 쿼리가 수행된다. 함수 수행 횟수의 감소, 데이터 전송량의 최소화를 통해 쿼리의 성능을 개선할 수 있다.
-- 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
-- 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 함수가 바인드 변수로 치환된 것을 확인할 수 있다. 로컬 기준 날짜 값으로 조회하므로, 로컬과 리모트의 날짜가 불일치하는 경우 데이터 일관성에 문제가 발생할 수 있다.
-- 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 함수에 서브 쿼리를 사용했지만, 데이터 일관성 문제를 해결할 수 없고, 데이터 전송량이 증가하여 쿼리의 성능이 저하된다.
-- 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와 동일하게 수행된다.
-- 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' )
아래 쿼리처럼 인라인 뷰와 서브 쿼리를 함께 사용해야, 데이터 일관성을 확보하고, 데이터 전송량을 최소화할 수 있다.
-- 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
-- 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 함수가 과도하게 수행될 수 있고, 데이터 일관성에 문제가 발생할 수 있다.
-- 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' )
아래 쿼리는 지금까지 살펴본 튜닝 기법을 적용한 쿼리다. 최적의 쿼리 성능을 보장할 수 있다.
-- 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' )'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 |
