이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... ·...

30
1 1 1 이펙티브 오라클 성능 향상을 위한 데이터베이스 설 비법 이 문서는 Oracle Club 데이터베이스 스터디 모임에서 작성하였습니다. 1. 수정 DML의 시작 끝 2. DDL 처리 3. 바인드 변수의 사용 4. 가능한 한 적 파싱하기 5. 요약 이펙티브 오라클 5장 문 처리 강정식 ( [email protected] )

Transcript of 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... ·...

Page 1: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

1111이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

이 문서는 Oracle Club 데이터베이스 스터디 모임에서 작성하였습니다.

1. 수정 DML의 시작과 끝

2. DDL 처리

3. 바인드 변수의 사용

4. 가능한 한 적게 파싱하기

5. 요약

이펙티브 오라클제5장 문 처리

강정식 ( [email protected] )

Page 2: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

2222이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

1. 수정 DML의 시작과 끝 Page 369 ~ 371Page 369 ~ 371

1.1 수정 DML 문(INSERT, DELETE, UPDATE, MERGE)은 일반적으로 쿼리 구성 요소를 갖는다.

▶ DELETE, UPDATE, MERGE 문은 테이블에서 수정 대상 행을 찾아 이들을 수정한다.

▶ INSERT 문은 이를 포함 할 수도 있고 아닐 수도 있다.

UPDATE EMP

SET SAL = SAL * 1.1;

INSERT INTO EMP (EMPNO, DEPTNO)

VALUES (9999, 10);

INSERT INTO TAB

SELECT EMPNO

FROM EMP;

쿼리 구성 요소 포함

쿼리 구성 요소 포함 안 함

UPDATE EMP

SET SAL = SAL * 1.1;

� 14행이갱신되었습니다.

call count cpu elapsed disk query current rows

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 1 0.00 0.03 0 0 0 0

Execute 1 0.00 0.00 0 3 15 14

Fetch 0 0.00 0.00 0 0 0 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

total 2 0.00 0.03 0 3 15 14

1.2 수정 문은 일관된 읽기 메커니즘을 이용하여 갱신 대상 데이터를 찾은 다음 이를 수정하기 위하여 현재 모드로 바꾼다.

▶ 인덱스가 없는 컬럼을 변경할 경우

오른쪽에서 보는 것처럼 current와 rows의 수가 거의 동일하다.

이는 수정을 하기 위해 현재 모드(current)로 전환하고 수정한 다음에(rows) 이를 반환함을 의미한다.

Page 3: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

3333이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

1. 수정 DML의 시작과 끝

CREATE INDEX SAL_IDX ON EMP(SAL);

� 인덱스가 생성되었습니다.

ALTER SESSION SET SQL_TRACE = TRUE;

� 세션이 변경되었습니다.

UPDATE EMP SET SAL = SAL * 1.1;

� 14행이 갱신되었습니다.

call count cpu elapsed disk query current rows

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 3 73 14

Fetch 0 0.00 0.00 0 0 0 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

total 2 0.00 0.00 0 3 73 14

1.2 수정 문은 일관된 읽기 메커니즘을 이용하여 갱신 대상 데이터를 찾은 다음 이를 수정하기 위하여 현재 모드로 바꾼다.

▶ 인덱스가 있는 컬럼을 변경 할 경우

보는 것처럼 인덱스를 추가한 경우도 마찬가지로 현재 모드로 바꾸기 위한 작업이 증가한 것을 볼 수 있다.

다만 인덱스를 추가하기 전보다 훨씬 증가하였는데 이 이유는 다음과 같다.

▣ 이전과 같이 현재 데이터 값을 현재 모드로 돌리기 위한 작업한다.

▣ 인덱스 유지를 위해 인덱스 값 또한 수정작업을 하기 위해 현재 모드로 바꾸는 작업을 병행한다.

이 외에도 DML은 다음과 같은 작업을 수행한다.

▣ 내부적으로 실패할 경우 수정이 복구될 수 있도록 하기 위한 리두 생성

▣ 사용자에 의해 트랜잭션이 롤백되는 경우에 수정을 원상태로 돌리기 위한 언두 생성

Page 369 ~ 371Page 369 ~ 371

Page 4: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

4444이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

2. DDL 처리

2.1 DDL문은 DML과 달리 최적화 작업을 하지 않고 파싱을 한다.

2.2 DDL문을 실행하기 위해서는 내부적으로 이 구문을 검증하기 위해 대략 50개의 SQL문이 필요하다.

▶ CREATE TABLE T (X INT) 이 구문이 제출될 경우

▣ 오라클은 사용자가 테이블을 생성할 수 있는 권한을 갖고 있는지 체크한다.

▣ 어느 테이블 영역에 생성되어야 하는지 체크한다.

▣ 테이블 영역의 쿼터를 초과하지는 않았는지 체크한다.

▣ T라고 명명된 객체가 존재하는지 않는지 체크한다.

▣ 대상 테이블이 실제로 존재하도록 하기 위해 SYS.OBJ$, SYS.COL$ 등에 행을 삽입하는 문을 실행 할 수 있다.

Page 371 ~ 373Page 371 ~ 373

ALTER SYSTEM FLUSH SHARED_POOL; -- 공유 풀 지움

� 시스템이 변경되었습니다.

ALTER SESSION SET SQL_TRACE = TRUE;

� 세션이 변경되었습니다.

CREATE TABLE T (X INT);

�테이블이생성되었습니다.

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.02 0 0 1 0

Execute 1 0.02 0.07 661 1693 8 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.02 0.09 661 1693 9 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 123 0.21 0.66 8 222 0 0

Execute 347 0.08 0.41 24 183 96 23

Fetch 752 0.07 1.20 693 2120 0 536

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

total 1222 0.37 2.28 725 2525 96 559

Misses in library cache during parse: 52

RECURSIVE STATEMENTS에서 보는 것처럼 DDL문으로 인해 오라클은 123개의

SQL 문을 파싱했으며 이들을 347번이나 실행하였다. 이들 중 일부는(몇 번의 Fetch로

인한) SELECT이고 일부는 수정이다.

2.3 테스트

Page 5: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

5555이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

2. DDL 처리

2.4 DDL문은 DDL문이 실행되기 직전과 COMMIT 또는 ROLLBACK 직후에 묵시적인 COMMIT이 수행된다.

DDL 개념은 다음과 같다.

▣ DDL은 이처럼 시작 전에 미 해결된 작업이 있으면 항상 이들의 커밋을 완료한다.

▣ 이는 DDL 명령이 실패하더라도 대부분의 트랜잭션의 커밋은 완료된다는 것을 의미한다.

Page 371 ~ 373Page 371 ~ 373

BEGINCOMMIT;

PARSE THE DDL –권한과구문검사

BEGIN

DO_THE_DDL; COMMIT;

EXCEPTIONWHEN OTHERS THEN

ROLLBACK;

END;

END;

Page 6: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

6666이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

⊙ 바인드 변수란?

▶ 호스트 환경에서 선언한 변수로 런타임 값을 하나 이상의 PL/SQL 프로그램과 주고 받는데 사용한다.

▶ 이 변수는 SQL*PLUS 환경에서 변수를 사용하기 위해 다음과 같은 구문을 사용한다.

VAR[IABLE] 변수명 [ NUMBER | CHAR(n) | VARCHAR2(n)]

▶ 또한 바인드 변수 값을 나타내기 위해서는 PRINT문을 사용한다.

▶ 바인드 변수를 사용하면 오라클은 한 번만 하드 파싱을 수행하고 이후에는 계속해서 소프트 파싱을 수행할 수 있다. 즉 실행 시간에 값이

공급될 수 있는 개체 틀로 대체됨을 의미한다.

Page 373 ~ 402Page 373 ~ 402

⊙ 사용 예제

SELECT ENAME FROM EMP WHERE EMPNO = 7369;

� SMITH

SELECT ENAME FROM EMP WHERE EMPNO = 7521;

� WARD

▶ 바인드 변수 없는 쿼리

VARIABLE EMPNO NUMBER

EXEC :EMPNO := 7369;

� PL/SQL 처리가 정상적으로 완료되었습니다.

SELECT ENAME FROM EMP WHERE EMPNO = :EMPNO;

� SMITH

EXEC :EMPNO = 7521;

� PL/SQL 처리가 정상적으로 완료되었습니다.

SELECT ENAME FROM EMP WHERE EMPNO = :EMPNO;

� WARD

▶ 바인드 변수 있는 쿼리

Page 7: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

7777이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

1) 1) 1) 1) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 성능이성능이성능이성능이 낮다낮다낮다낮다....1) 1) 1) 1) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 성능이성능이성능이성능이 낮다낮다낮다낮다....

⊙ 쿼리문을 제출하면 오라클은 SGA 영역의 공유 풀을 먼저 찾아본다.

▶ 쿼리문이 제출되면 파싱에서 이 쿼리가 이미 파싱되었으며 최적화되었는지를 파악하기 위해 SGA영역의 Shared Pool을 살펴본다.

▣ 소프트 파싱 : 만약 공유 풀에 이 쿼리가 존재한다면 최적화 절차를 건너뛰고 바로 문을 실행한다.

▣ 하드 파싱 : 만약 공유 풀에 이 쿼리가 존재하지 않는다면 오라클은 CPU 중심의 작업인 하드 파싱을 하고 최적화 과정을 거친다.

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 사용 예제 : 속도 비교

CREATE TABLE T (X VARCHAR2(5));

� 테이블이 생성되었습니다.

▶ 테스트를 하기 위한 T 테이블 생성

DECLAREDECLAREDECLAREDECLARETYPETYPETYPETYPE rc ISISISIS REFREFREFREF CURSORCURSORCURSORCURSOR;l_cursor RC;l_dummy ALL_OBJECTS.OBJECT_NAME%TYPETYPETYPETYPE;l_start NUMBERNUMBERNUMBERNUMBER DEFAULTDEFAULTDEFAULTDEFAULT DBMS_UTILITY.GET_TIME;

BEGINBEGINBEGINBEGINFORFORFORFOR I ININININ 1..5000 LOOPLOOPLOOPLOOP

OPENOPENOPENOPEN l_cursor FORFORFORFOR 'SELECT X'SELECT X'SELECT X'SELECT XFROM TFROM TFROM TFROM TWHERE X = ' WHERE X = ' WHERE X = ' WHERE X = ' |||||||| TO_CHAR(iTO_CHAR(iTO_CHAR(iTO_CHAR(i););););

FETCHFETCHFETCHFETCH l_cursorINTOINTOINTOINTO l_dummy;CLOSECLOSECLOSECLOSE l_cursor;

ENDENDENDEND LOOPLOOPLOOPLOOP;

DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - L_START) / 100, 2) || 'sec');ENDENDENDEND;

▶ 하드 파싱 (3.58 sec)

DECLAREDECLAREDECLAREDECLARETYPETYPETYPETYPE rc ISISISIS REFREFREFREF CURSORCURSORCURSORCURSOR;l_cursor RC;l_dummy ALL_OBJECTS.OBJECT_NAME%TYPETYPETYPETYPE;l_start NUMBERNUMBERNUMBERNUMBER DEFAULTDEFAULTDEFAULTDEFAULT DBMS_UTILITY.GET_TIME;

BEGINBEGINBEGINBEGINFORFORFORFOR I ININININ 1..5000 LOOPLOOPLOOPLOOP

OPENOPENOPENOPEN l_cursor FORFORFORFOR 'SELECT X'SELECT X'SELECT X'SELECT XFROM TFROM TFROM TFROM TWHERE X =WHERE X =WHERE X =WHERE X = :x' USING i;:x' USING i;:x' USING i;:x' USING i;

FETCHFETCHFETCHFETCH l_cursorINTOINTOINTOINTO l_dummy;CLOSECLOSECLOSECLOSE l_cursor;

ENDENDENDEND LOOPLOOPLOOPLOOP;

DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - L_START) / 100, 2) || 'sec');ENDENDENDEND;

▶ 소프트 파싱 (1.14 sec)

Page 8: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

8888이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

1) 1) 1) 1) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 성능이성능이성능이성능이 낮다낮다낮다낮다....1) 1) 1) 1) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 성능이성능이성능이성능이 낮다낮다낮다낮다....

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 사용 예제 : 내부 처리 절차 비교

DECLAREDECLAREDECLAREDECLARETYPETYPETYPETYPE rc ISISISIS REFREFREFREF CURSORCURSORCURSORCURSOR;l_cursor RC;

BEGINBEGINBEGINBEGINFORFORFORFOR i ININININ 1..5000 LOOPLOOPLOOPLOOP

OPENOPENOPENOPEN l_cursor FORFORFORFOR 'SELECT XFROM TWHERE X = ' || TO_CHAR(i);

CLOSECLOSECLOSECLOSE l_cursor;ENDENDENDEND LOOPLOOPLOOPLOOP;

ENDENDENDEND;

▶ 하드 파싱

▶ 내부 처리 절차

SELECT X

FROM T

WHERE X = 1

SELECT X

FROM T

WHERE X = 2

SELECT X

FROM T

WHERE X = 3

SELECT X

FROM T

WHERE X = 5000

▪▪▪

5000천번을 서로 다른 쿼리로 인식하고 하드 파싱을 한다.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

cal l count cpu elapsed disk query current rows

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 5 0 .00 0 .02 0 0 0 0

Execute 6 1 .95 2 .07 0 0 0 1

Fetch 7 0 .00 0 .00 0 118 0 3

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tota l 18 1 .96 2 .10 0 118 0 4

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

cal l count cpu elapsed disk query current rows

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 5001 2 .50 2 .71 0 0 0 0

Execute 5001 0 .12 0 .12 0 0 0 0

Fetch 1 0 .00 0 .00 0 2 0 1

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tota l 10003 2 .62 2 .83 0 2 0 1

Misses in library cache during parse: 5000

5006 user SQL statements in session.

1 internal SQL statements in session.

5007 SQL statements in session.

0 statements EXPLAINed in this session.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Page 9: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

9999이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

1) 1) 1) 1) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 성능이성능이성능이성능이 낮다낮다낮다낮다....1) 1) 1) 1) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 성능이성능이성능이성능이 낮다낮다낮다낮다....

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 사용 예제 : 내부 처리 절차 비교

DECLAREDECLAREDECLAREDECLARETYPETYPETYPETYPE rc ISISISIS REFREFREFREF CURSORCURSORCURSORCURSOR;l_cursor RC;

BEGINBEGINBEGINBEGINFORFORFORFOR i ININININ 1..5000 LOOPLOOPLOOPLOOP

OPENOPENOPENOPEN l_cursor FORFORFORFOR 'SELECT XFROM EMP_3WHERE X = :x' USINGUSINGUSINGUSING i;

CLOSECLOSECLOSECLOSE l_cursor;ENDENDENDEND LOOPLOOPLOOPLOOP;

ENDENDENDEND;

▶ 소프트 파싱

▶ 내부 처리 절차

SELECT X

FROM EMP_3

WHERE X = :x

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 5000 0.10 0.09 0 0 0 0

Execute 5000 0.14 0.13 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 10000 0.24 0.23 0 0 0 0

바인드 변수를 사용하여 공유 풀에 있는 것을 확인 한 후 소프트 파싱을 한다.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

cal l count cpu elapsed disk query current rows

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 5 0 .00 0 .00 0 0 0 0

Execute 6 1 .73 1 .85 0 0 0 1

Fetch 7 0 .00 0 .00 0 118 0 3

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tota l 18 1 .74 1 .86 0 118 0 4

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

cal l count cpu elapsed disk query current rows

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Parse 5001 0 .10 0 .09 0 0 0 0

Execute 5001 0 .14 0 .13 0 0 0 0

Fetch 1 0 .00 0 .00 0 2 0 1

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tota l 10003 0 .24 0 .23 0 2 0 1

Misses in library cache during parse: 0

5006 user SQL statements in session.

1 internal SQL statements in session.

5007 SQL statements in session.

0 statements EXPLAINed in this session.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

이 값이 0이라는 얘기는

이 쿼리가 소프트 파싱을 하였음을 의미한다.

Page 10: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

10101010이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

2) 2) 2) 2) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 확장성이확장성이확장성이확장성이 낮다낮다낮다낮다....2) 2) 2) 2) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 확장성이확장성이확장성이확장성이 낮다낮다낮다낮다....

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 하드 파싱은 CPU 중심의 작업일 뿐만 아니라 확장성이 낮은 작업이다.

▶ 쿼리의 파싱과 최적화는 여러 개가 동시에 수행될 수 있는 작업이 아니다. 오라클은 반드시 SGA의 공유 데이터 구조에 대한 액세스를

제어하여야 한다. 만약 이런 제어가 안 된다면 종국에는 시스템이 깨지고 만다. 그러므로 오라클은 래치(경렬 직렬화 장치)를 사용하여 공유 구조

를 보호한다.

▶ 이런 구조 때문에 파싱이 많으면 많을수록 공유 풀을 보호하는 이들 래치에 대한 경함이 늘어나고 동시 작업은 줄어들 수 밖에 없다.

▶ 래치(직렬화 장치) : 많은 세션이 공유 데이터 구조를 안전하게 액세스하고 수정할 수 있도록 해주는 기능

⊙ 이런 문제를 해결할 수 있는 유일한 방법은 처음부터 바인드 변수를 사용해야 한다.

▶ 하드 파싱은 확정성에 취약하다.

▶ 바인드 변수를 사용하지 않고 초당 수백 또는 수천 개의 쿼리를 실행해야 하는 경우 시스템은 멈추고 말 것이다.

▶ 이를 해결하기 위해 CPU를 추가하거나 클러스터를 사용한다고 하더라도 문제가 해결되지는 않는다. 이유는 하드웨어가 불충분하여 생긴 것이

아니라 다른 세션이 사용 대상 데이터 구조를 점유하고 있어서 불가피하게 기다릴 수밖에 없어서 생긴 문제이기 때문이다.

Page 11: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

11111111이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

3) 3) 3) 3) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 코드코드코드코드 작성이작성이작성이작성이 어렵다어렵다어렵다어렵다....3) 3) 3) 3) 바인드바인드바인드바인드 변수를변수를변수를변수를 사용하지사용하지사용하지사용하지 않으면않으면않으면않으면 코드코드코드코드 작성이작성이작성이작성이 어렵다어렵다어렵다어렵다....

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 인용 부호(작은 따옴표, 큰 따옴표 등)가 포함된 문자열을 쿼리에 대입하는 경우 실패하는 사례가 증가한다.

▶ 실패 예제 : 검색 창에 [Ben and Jerry’s] 라고 값을 입력하고 조회를 할 경우

FIND 버튼 로직 ‘SELECT * FROM TAB WHERE Field_1 = ’’’ || v_value || ’’’’;-- v_value변수는검색값

검색 창 FIND

Field_1 Field_2 Field_3 Field_4 Field_5 Field_6

조회 화면

Ben and Jerry’s

FIND 버튼 클릭할 때 반환되는 쿼리 ‘SELECT * FROM TAB WHERE Field_1 = ‘Ben and Jerry’s’

만약WHERE 조건에 ‘Ben and Jerry’s’ 이런 값으로 조회한다면 조회를 실패할 것이다.

그러므로 REPLACE() 함수를 이용하여 한 개의 작은 따옴표를 두 개로 만들어야 한다.

FIND 버튼 수정된 로직 ‘SELECT * FROM TAB WHERE Field_1 ‘’’ || REPLACE(v_value, ‘’’’, ‘’’’’’) || ‘’’’;

FIND 버튼 수정된 로직으로 반환되는 쿼리 ‘SELECT * FROM TAB WHERE Field_1 = ‘Ben and Jerry’’s’

이처럼 쿼리에 문자열을 포함할 경우 바인드 변수를 사용해야 만 다양한 값에 대응을 할 수 있다.

Page 12: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

12121212이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

4) 4) 4) 4) 바인드바인드바인드바인드 변수가변수가변수가변수가 사용되지사용되지사용되지사용되지 않은않은않은않은 코드의코드의코드의코드의 안전도는안전도는안전도는안전도는 낮다낮다낮다낮다....4) 4) 4) 4) 바인드바인드바인드바인드 변수가변수가변수가변수가 사용되지사용되지사용되지사용되지 않은않은않은않은 코드의코드의코드의코드의 안전도는안전도는안전도는안전도는 낮다낮다낮다낮다....

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 바인드 변수를 사용하지 않을 경우 약간의 편법을 이용하여WHERE 조건을 임의로 변경할 수 있다.

oracleclub

babo’ or ‘1’ = ‘1

아이디

비밀번호 FIND

SQL Injection

SELECT ‘CHECK’

FROM TAB

WHERE USERNAME = ‘oracleclub’

AND PASSWORD = ‘babo’ or ‘1’ = ‘1’

▣ 만약 사용자가 다음과 같은 값을 넣을 경우…

▣ 이럴 경우 비밀번호를 잘 못 입력하여도 ‘1’ = ‘1’ 구문이 항상 참이기 때문에 로그인이 된다.

oracleclub

0909

아이디

비밀번호 FIND

정상 동작

SELECT ‘CHECK’

FROM TAB

WHERE USERNAME = ‘oracleclub’

AND PASSWORD = ‘0909’

▣ 사용자 테이블에 아이디와 패스워드가 ‘oracleclub’ / ‘0909’ 와 같이 있을 경우…

▶ 이런 대표적인 사례가 SQL Injection 해킹 기법이다.

▶ SQL Injection 해킹 기법 : SQL Injection은 웹 페이지를 통해 입력된 파라미터 값을 이용하여 쿼리를 재구성하는 방법이다.

Page 13: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

13131313이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용

4) 4) 4) 4) 바인드바인드바인드바인드 변수가변수가변수가변수가 사용되지사용되지사용되지사용되지 않은않은않은않은 코드의코드의코드의코드의 안전도는안전도는안전도는안전도는 낮다낮다낮다낮다....4) 4) 4) 4) 바인드바인드바인드바인드 변수가변수가변수가변수가 사용되지사용되지사용되지사용되지 않은않은않은않은 코드의코드의코드의코드의 안전도는안전도는안전도는안전도는 낮다낮다낮다낮다....

Page 373 ~ 402Page 373 ~ 402

3.1 바인드 변수를 사용하면 무엇이 좋은가?

⊙ 실제 예제

-------- 2. 2. 2. 2. 쿼리쿼리쿼리쿼리

SELECT COUNT(*)SELECT COUNT(*)SELECT COUNT(*)SELECT COUNT(*)

FROM TABFROM TABFROM TABFROM TAB

WHERE USERNAME = WHERE USERNAME = WHERE USERNAME = WHERE USERNAME = ‘‘‘‘&&&&unameunameunameuname’’’’

AND PASSWORD = AND PASSWORD = AND PASSWORD = AND PASSWORD = ‘‘‘‘&&&&pwordpwordpwordpword’’’’

���� 1111

Where username = Where username = Where username = Where username = ‘‘‘‘&&&&unameunameunameuname’’’’

Where username = Where username = Where username = Where username = ‘‘‘‘oraclecluboraclecluboraclecluboracleclub’’’’

Where Where Where Where pwordpwordpwordpword = = = = ‘‘‘‘&&&&pwordpwordpwordpword’’’’

Where Where Where Where pwordpwordpwordpword = = = = ‘‘‘‘babobabobabobabo’’’’ or or or or ‘‘‘‘1111’’’’ = = = = ‘‘‘‘1111’’’’

-------- 2. 2. 2. 2. 쿼리쿼리쿼리쿼리

SELECT COUNT(*)SELECT COUNT(*)SELECT COUNT(*)SELECT COUNT(*)

FROM TABFROM TABFROM TABFROM TAB

WHERE USERNAME = WHERE USERNAME = WHERE USERNAME = WHERE USERNAME = ‘‘‘‘&&&&unameunameunameuname’’’’

AND PASSWORD = AND PASSWORD = AND PASSWORD = AND PASSWORD = ‘‘‘‘&&&&pwordpwordpwordpword’’’’

���� 0000

▶ 사용자 테이블 생성하고 유저 생성

CREATE TABLE EMP_2(USERNAME VARCHAR2(30), PASSWORD VARCHAR2(30));

INSERT INTO EMP_2 VALUES(‘oracleclub', ‘0909');

COMMIT;

-------- 1. 1. 1. 1. 변수변수변수변수 선언선언선언선언

ACCEPT ACCEPT ACCEPT ACCEPT unameunameunameuname PROMPT PROMPT PROMPT PROMPT ““““Enter username : Enter username : Enter username : Enter username : ““““

���� Enter username : Enter username : Enter username : Enter username : oraclecluboraclecluboraclecluboracleclub

ACCEPT ACCEPT ACCEPT ACCEPT pwordpwordpwordpword PROMPT PROMPT PROMPT PROMPT ““““Enter password : Enter password : Enter password : Enter password : ““““

���� Enter password : Enter password : Enter password : Enter password : babobabobabobabo’’’’ or or or or ‘‘‘‘1111’’’’ = = = = ‘‘‘‘1111

▶ 문자열을 결합한 형태

-------- 1. 1. 1. 1. 변수변수변수변수 선언선언선언선언

VARIABLE VARIABLE VARIABLE VARIABLE unameunameunameuname VARCHAR2(30);VARCHAR2(30);VARCHAR2(30);VARCHAR2(30);

VARIABLE VARIABLE VARIABLE VARIABLE pwordpwordpwordpword VARCHAR2(30);VARCHAR2(30);VARCHAR2(30);VARCHAR2(30);

Exec :Exec :Exec :Exec :unameunameunameuname := := := := ‘‘‘‘oraclecluboraclecluboraclecluboracleclub’’’’;;;;

Exec :Exec :Exec :Exec :pwordpwordpwordpword := := := := ‘‘‘‘babobabobabobabo’’’’’’’’ or or or or ‘’‘’‘’‘’1111’’’’’’’’ = = = = ‘’‘’‘’‘’1111’’’’

▶ 문자열을 바인딩 변수를 사용하여 접근한 형태

Page 14: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

14141414이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.2 자바와 VB에서 바인드 변수 사용하기

⊙ JDBC 프로그램에서 Statement와 PreparedStatement 비교

▶일반적으로 성능이 좋다는 이유만으로 바인드 변수가 허용되는 PreparedStatement보다는 바인드 변수가 허용되지 않는 Statement를 사용하

도록 강권하고 있다. 그러나 단일 사용자 환경이나 다중 사용자 환경에서도 Statement보다는 PreparedStatement가 훨씬 성능이 좋다.

▶ 이유는 앞에서도 보았다시피 하드파싱을 하게 되는 Statement는 수많은 래치를 유발하고 이는 확장성을 저해한다. 결국 사용자가 늘어나면

시스템은 실패할 수 밖에 없다.

▶ 그러므로 가급적 Statement를 통해 “값의 결합”을 시도하지 말고 바인드 변수를 사용할 수 있는 PreparedStatement를 사용하라.

Page 15: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

15151515이펙티브 오라클 성능 향상을 데이터베이스 설계 비법

변수의 사용 Page 373 ~ 402

3.3 모든 규칙에는 예외가 있다.

⊙ 바인드 변수를 사용할 때의 장점

▶ 바인드 변수는 쿼리를 파싱할 때 애플리케이션이 사용하는 래치의 수를 줄이기 위한 훌륭한 방법이다.

▶ 소프트 파싱은 하드 파싱보다 CPU 시간을 약간 적게 사용하며 바인드 변수는 소프트 파싱을 성취하기 위한 수단이다.

▶ SQL 문에 바인드 변수를 사용하는 대신 문자열 리터럴을 사용하면 시스템이 SQL 삽입에 노출된다.

▶ SQL 문에 문자열 리터럴을 사용하면 사용자가 인용 부호와 같은 예기치 못한 문자를 입력할 경우 문에 실행되지 않게 될 수 있다.

▶ 사용중인 환경(PL/SQL, 자바와 JDBC, 또는 몇 가지 다른 언어)에 상관없이 바인드 변수를 사용하는 방안이 바인드 변수를 사용하지 않는

것보다 좀더 빠를 뿐만 아니라 쉽게 작성한다.

⊙ 바인드 변수를 사용할 때의 의문점

▶ 바인드 변수를 사용하면 하드 파싱이 아니라 SGA 공유 풀을 사용하는 소프트 파싱을 하기 때문에 유리하다.

▶ 하지만 바인드 변수는 리터럴 값을 공급하지 못하고 변수를 공급하기 때문에 옵티마이저는 이 값을 받아 통계정보를 이용하지 못한다.

다음과 같은 예를 통해 살펴보자.

▣ 통계정보가 생성된 TAB 테이블의 Field_1 컬럼에 A, B, C, D, E 5개 값이 들어가있고 인덱스가 설정되어 있다.

▣ A, B, C, D, E의 분포도는 각각 1%, 2%, 3%, 4%, 90%를 차지하고 있다.

▣ 만약 이럴 경우, 리터럴 값이 들어간다면 A, B, C, D값일 경우 옵티마이저는 인덱스를 E값일 경우는 Full Scan을 할 것이다.

▣ 하지만 바인드 변수를 사용하면 1/N 로 분포도를 나눠 25%가 되므로 값에 상관없이 무조건 Full Scan을 할 것이다.

⊙ 책에서 제시한 바인드 변수 사용 사례

▶ 책에서는 정적 SQL에 들어가는 값에는 리터럴 값을 사용하고 동적 SQL에 들어가는 값에는 바인드 변수를 사용하라고 한다.

▶ 하지만 대부분 바인드 변수가 들어가는 컬럼에 최상의 효율을 끌어내기 위해 인덱스나 클러스터링 작업을 하기 때문에 이 솔루션은 의미가 없다.

▶ 그래서 책 저자는 이를 해결하기 위해 Oracle 9i부터 제공하는 Bind Variable Peeking을 소개하고 있다.

Page 16: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

16161616이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Bind Variable Peeking 이란?

▶ 바인딩 변수를 사용한 SQL은 파싱과 최적화가 이루어진 후에 바인드 변수의 바인딩이 이루어진다. 따라서 최적화가 이루어지는 시점에서 변수로

제공되는 컬럼의 분포도가 균일하다는 가정을 세우고 최적화를 수행한다. 이렇기 때문에 최적화를 수행하고 분포도가 균일하지 못한 컬럼에 바인드

변수를 사용하게 되면 최악의 실행계획을 생성할 수도 있다.

▶ 그래서 하드 파싱의 부하를 줄이기 위해 바인드 변수를 사용하는 것이 좋다는 것을 알면서도 분포도가 좋지 않기 때문에 현실적으로 사용하기 어려

운 경우가 종종 발생한다.

▶ 이러한 한계를 극복하기 위해 Oracle 9i부터 ‘Peeking’ 기능을 제공한다.

▶ ‘Peeking’을 사전에서 찾아보면 ‘몰래 엿보다’라고 되어있는데 이 기능은 다음과 같다.

▣ 바인드 변수를 사용한 SQL이 첫 번째 실행될 때 옵티마이저는 사용자가 지정한 바인드 변수를 살짝 엿본다.

▣ 이 살짝 엿본다는 의미는 맨 처음 들어온 상수값으로 실행계획을 생성하고 그 이후부터 들어온 SQL은 무조건 이 실행계획을 사용한다.

▣ 앞서 본 예를 가지고 설명하면, 만약 SQL의 바인딩 컬럼 값이 맨 처음 A로 들어온다면 당연히 인덱스 실행계획을 생성할 것이고 그 이후부터

같은 SQL이 들어온다면 무조건 인덱스 실행계획을 생성한다. 그러므로 B, C, D, E 값이 들어와도 무조건 인덱스 스캔이다.

▣ 정리하면, 하드 파싱이 일어날 때 단 한번만 바인드 변수를 Peeking 한다는 뜻이고 첫 번째 바인딩되는 값에 따라 실행계획이 고정된다.

Page 17: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

17171717이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

학점

# 학점

* 시작점수

* 종료점수

학생

# 학번

* 이름

* 학점(FK)

▣ ERD

▣ 데이터 구조

600F

7061D

8071C

9081B

10091A

E_POINTS_POINTGRADE

학점 테이블

Boracleclub100010000

Boracleclub990

Boracleclub880

Boracleclub770

Boracleclub660

Boracleclub550

Foracleclub440

Doracleclub330

Coracleclub220

Aoracleclub110

GRADESTUNAMESTUNO

학생 테이블

� 1000개의 로우가 존재하고 4명을 제외한 나머지는 모두 학점이 ‘B’

Page 18: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

18181818이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ Script

CREATE TABLE GRADE_TAB AS -- 학점 테이블 생성 스크립트

SELECT 'A' GRADE, 91 S_POINT, 100 E_POINT FROM DUAL UNION ALL

SELECT 'B' GRADE, 81 S_POINT, 90 E_POINT FROM DUAL UNION ALL

SELECT 'C' GRADE, 71 S_POINT, 80 E_POINT FROM DUAL UNION ALL

SELECT 'D' GRADE, 61 S_POINT, 70 E_POINT FROM DUAL UNION ALL

SELECT 'F' GRADE, 0 S_POINT, 60 E_POINT FROM DUAL;

CREATE TABLE STU_TAB AS -- 학생 테이블 생성 스크립트

SELECT CNT * 10 STUNO

, 'oracleclub' || CNT STUNAME

, DECODE(CNT, 1, 'A'

, 2, 'B'

, 3, 'C'

, 4, 'F', 'B') GRADE

FROM (SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 10000);

ALTERALTERALTERALTER TABLETABLETABLETABLE STU_TAB ADDADDADDADD CONSTRAINTCONSTRAINTCONSTRAINTCONSTRAINT STU_PK_STUNO PRIMARYPRIMARYPRIMARYPRIMARY KEYKEYKEYKEY(STUNO); -- 학생번호에 PK 부여

� 테이블이 변경되었습니다.

CREATECREATECREATECREATE INDEXINDEXINDEXINDEX STU_GRADE_INDX ONONONON STU_TAB(GRADE); -- 학점에 인덱스 생성

� 인덱스가 생성되었습니다.

ANALYZEANALYZEANALYZEANALYZE TABLETABLETABLETABLE STU_TAB COMPUTECOMPUTECOMPUTECOMPUTE STATISTICSSTATISTICSSTATISTICSSTATISTICS -- 학생 테이블 통계정보 생성FORFORFORFOR TABLETABLETABLETABLE FORFORFORFOR ALLALLALLALL INDEXESINDEXESINDEXESINDEXES FORFORFORFOR ALLALLALLALL INDEXEDINDEXEDINDEXEDINDEXED COLUMNSCOLUMNSCOLUMNSCOLUMNS;� 테이블이 분석되었습니다.

-- 공유 풀 초기화

ALTER SYSTEM FLUSH SHARED_POOL;� 시스템이 변경되었습니다.

-- TRACE 생성

ALTER SESSION SET SQL_TRACE=TRUE;� 세션이 변경되었습니다.

Page 19: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

19191919이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ Script : 리터럴 값에 따른 실행계획

-- 분포도 좋은 데이터 들어옴

SELECTSELECTSELECTSELECT COUNTCOUNTCOUNTCOUNT(*), MINMINMINMIN(STUNO)FROMFROMFROMFROM STU_TABWHEREWHEREWHEREWHERE GRADE = 'A';

-- 분포도 나쁜 데이터 들어옴

SELECTSELECTSELECTSELECT COUNTCOUNTCOUNTCOUNT(*), MINMINMINMIN(STUNO)FROMFROMFROMFROM STU_TABWHEREWHEREWHEREWHERE GRADE = ‘B';

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

1 TABLE ACCESS BY INDEX ROWID STU_TAB

1 INDEX RANGE SCAN STU_GRADE_INDX(object id 1365117)

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

9996 TABLE ACCESS FULL STU_TAB

Page 20: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

20202020이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ Script : 바인드 변수 사용하여 ‘A’ � ‘B’ 값 순서로 실행

-- 분포도 좋은 데이터 먼저 실행

VARIABLE x VARCHAR2(1)

EXEC :x := 'A';

DECLARE

v_count NUMBER := 0; -- 학생수

v_stuno NUMBER := 0; -- 학번

BEGIN

SELECT COUNT(*), MIN(STUNO)

INTO v_count, v_stuno

FROM STU_TAB

WHERE GRADE = :x;

END;

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

1 TABLE ACCESS BY INDEX ROWID STU_TAB

1 INDEX RANGE SCAN STU_GRADE_INDX(object id 1365117)

-- 분포도 나쁜 데이터 나중에 실행

VARIABLE x VARCHAR2(1)

EXEC :x := ‘B';

DECLARE

v_count NUMBER := 0; -- 학생수

v_stuno NUMBER := 0; -- 학번

BEGIN

SELECT COUNT(*), MIN(STUNO)

INTO v_count, v_stuno

FROM STU_TAB

WHERE GRADE = :x;

END;

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

9996 TABLE ACCESS BY INDEX ROWID STU_TAB

9996 INDEX RANGE SCAN STU_GRADE_INDX(object id 1365117)

Page 21: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

21212121이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ Script : 바인드 변수 사용하여 ‘B’ � ‘A’ 값 순서로 실행

-- 분포도 나쁜 데이터 먼저 실행

VARIABLE x VARCHAR2(1)

EXEC :x := ‘B';

DECLARE

v_count NUMBER := 0; -- 학생수

v_stuno NUMBER := 0; -- 학번

BEGIN

SELECT COUNT(*), MIN(STUNO)

INTO v_count, v_stuno

FROM STU_TAB

WHERE GRADE = :x;

END;

-- 분포도 좋은 데이터 나중에 실행

VARIABLE x VARCHAR2(1)

EXEC :x := ‘A';

DECLARE

v_count NUMBER := 0; -- 학생수

v_stuno NUMBER := 0; -- 학번

BEGIN

SELECT COUNT(*), MIN(STUNO)

INTO v_count, v_stuno

FROM STU_TAB

WHERE GRADE = :x;

END;

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

9996 TABLE ACCESS FULL STU_TAB

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

1 TABLE ACCESS FULL STU_TAB

Page 22: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

22222222이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ 지금까지 살펴본 것처럼 처음 들어오는 값의 실행계획을 가지고 그 이후의 바인드 변수는 모두 같은 실행계획인 것을 볼 수 있었다.

▣ 이 Peeking을 통해 이전에는 바인드 변수를 사용할 때 분포도를 고려하지 않고 평균값을 이용하는 기존의 최적화 방법의 한계를 극복한걸로

볼 수 있으나 여전히 비효율의 기능을 가지고 있다.

▣ 지금까지 살펴본 것처럼 가장 이상적인 방법은 항상 소프트 파싱을 하고 또한 분포도를 고려한 실행계획을 세울 수 있는 전략이 되겠으나

바인드 변수(소프트 파싱)와 분포도를 고려한 실행계획은 Peeking 이전에는 언제나 상극이었다. 그렇다고 Peeking이 도입되었으나 이

또한 맨 처음의 리터럴 값만 살펴보고 그 이후에는 맨 처음 세운 실행계획을 따라가므로 완벽한 것이 아니다.

▣ 그렇다면 현재까지 나와있는 자원을 가지고 위에서 살펴본 가장 이상적인 접근법을 구현할 수는 없을까?

완벽하진 않지만 부분적으로 이를 극복할 수 있다.

▣ 그 방법은 가장 분포도가 나쁜 값이 들어올 경우와 분포도가 좋은 값이 들어올 경우로 나누어 쿼리를 맨 처음 생성하면 그 이후에는

항상 최적화 방법을 가지고 실행계획을 작성 할 수 있다.

Page 23: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

23232323이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ Script : 분포도에 따라 최적의 실행계획 유도

-- 값의 분포도에 따라 최적의 실행계획 유도하는 저장형 함수 생성

CREATE OR REPLACE FUNCTION GET_DATA(p_grade VARCHAR2) RETURN VARCHAR2

AS

v_count NUMBER := 0; -- 학생수

v_stuno NUMBER := 0; -- 학번

BEGIN

IF(p_grade = 'B') THEN

SELECT COUNT(*), MIN(STUNO)

INTO v_count, v_stuno

FROM STU_TAB B_TYPE

WHERE GRADE = p_grade;

ELSE

SELECT COUNT(*), MIN(STUNO)

INTO v_count, v_stuno

FROM STU_TAB OTHER_TYPE

WHERE GRADE = p_grade;

END IF;

RETURN v_count || '명 ', '최소학번 ' || v_stuno;

END;

-- 저장형 함수 이용하여 쿼리 실행

BEGIN

FOR rec_stu IN (SELECT GRADE FROM STU_TAB) LOOP

DBMS_OUTPUT.PUT_LINE(rec_stu.GRADE || ' : ' ||

GET_DATA(rec_stu.GRADE));

END LOOP;

END;

A : 1 명, 최소학번 10

B : 9996 명, 최소학번 50

C : 1 명, 최소학번 20

D : 1 명, 최소학번 30

F : 1 명, 최소학번 40

Page 24: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

24242424이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

3. 바인드 변수의 사용 Page 373 ~ 402Page 373 ~ 402

3.4 바인드 변수 조사(Bind Variable Peeking)

⊙ Peeking 예제

▶ 학점과 학생 테이블을 1:M관계로 테이블을 생성하고 이를 활용하여 Peeking을 살펴보자.

▣ Script : 분포도에 따라 최적의 실행계획 유도

-- 분포도 좋은 컬럼이므로 인덱스 실행계획

SELECT COUNT(*), MIN(STUNO)

FROM STU_TAB OTHER_TYPE

WHERE GRADE = :b1;

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

4 TABLE ACCESS BY INDEX ROWID STU_TAB

4 INDEX RANGE SCAN STU_GRADE_INDX(object id 1365117)

-- 분포도 나쁜 컬럼이므로 Full Scan

SELECT COUNT(*), MIN(STUNO)

FROM STU_TAB B_TYPE

WHERE GRADE = :b1;

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE

9996 TABLE ACCESS FULL STU_TAB

Page 25: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

25252525이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

4. 가능한 한 적게 파싱하기 Page 402 ~ 421Page 402 ~ 421

4.1 파싱 비용

⊙ 일반적으로 커서를 사용할 때 개발자들은 CLOSE를 신속하게 하는 경향이 있다.

▶ 커서를 제대로 사용하는 애플리케이션은 세션별로 데이터베이스에 대한 커서를 한 번만 열고 수 차례에 걸쳐 이것을 실행한다.

▶ 커서를 제대로 사용하지 못하는 애플리케이션은 실행 시마다 커서를 열어, 즉 엄청난 비용을 들여 데이터베이스를 처리하는 경향을 보인다.

⊙ 하지만 오라클은 또 다시 OPEN 될 것에 대비하여 캐시에 저장한다.

▶ PL/SQL에서는 Close Cursor를 하면 닫는것처럼 보이지만 실제로는 커서가 열린 상태로 유지된다. 그 이유는 이 커서가 다시 사용될 것이라고

생각하고 이 문을 캐시에 저장하는데 이것이 바로 정적 커서이다. 즉, 참조 커서는 커서를 바로 닫지만 정적 커서는 열린 상태로 캐시에 저장된다.

⊙ 이를 이용하여 느슨한 소프트 파싱보다 무 파싱을 하도록 유도해야 한다.

▶ 이를 하기 위해서는 가급적 참조 커서보단 정적 커서를 사용해서 파싱을 한번만 하도록 유도한다.

▶ 즉 하드파싱 보다는 느슨한 소프트파싱을, 느슨한 소프트파싱 보다는 무파싱을 하도록 유도하자.

▶ 하드 파싱 : 리터럴(실행시마다 변경이 될 수 있는) 값을 이용한 쿼리

▶ 느슨한 소프트 파싱 : 바인드 변수를 사용한 쿼리

▶ 무 파싱 : 바인드 변수 역할을 선언부에 커서로 선언하고 이를 변수로 받아 처리하는 쿼리

⊙ 참조 커서 & 정적 커서

▶ 참조 커서 : 커서의 내용을 실행부(BEGIN END) 안에서 사용하는 구문

Ex) FOR rec_val IN (SELECT * FROM EMP) LOOP

▶ 정적 커서 : 커서의 내용을 선언부(DECLARE) 안에서 사용하는 구문

Ex) DECLARE

CURSOR cur_val IS SELECT * FROM EMP

BEGIN

FOR rec_val IN cur_val LOOP …… [로직] …… END LOOP

END;

Page 26: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

26262626이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

4. 가능한 한 적게 파싱하기 Page 402 ~ 421Page 402 ~ 421

4.1 파싱 비용

하드 파싱

DECLARE

TYPE rc IS REF CURSOR;

l_cursor RC;

BEGIN

FOR i IN 1..5000 LOOP

OPEN l_cursor FOR 'SELECT X

FROM EMP_3

WHERE X = ' || TO_CHAR(iTO_CHAR(iTO_CHAR(iTO_CHAR(i););););

CLOSE l_cursor;

END LOOP;

END;

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTSOVERALL TOTALS FOR ALL RECURSIVE STATEMENTSOVERALL TOTALS FOR ALL RECURSIVE STATEMENTSOVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ----------- - - - - - - - - -

Parse 5001Parse 5001Parse 5001Parse 5001 2.50 2.71 0 0 0 0

Execute 5001 0.12 0.12 0 0 0 0

Fetch 1 0.00 0.00 0 2 0 1

------- ------ -------- ---------- ---------- ---------- ----------- - - - - - - - - -

total 10003 2.62 2.83 0 2 0 1

Misses in library cache during parse: 5000Misses in library cache during parse: 5000Misses in library cache during parse: 5000Misses in library cache during parse: 5000

참조 커서

(느슨한 소프트 파싱)

DECLARE

TYPE rc IS REF CURSOR;

l_cursor RC;

BEGIN

FOR i IN 1..5000 LOOP

OPEN l_cursor FOR 'SELECT X

FROM EMP_3

WHERE X = :x' USING i;:x' USING i;:x' USING i;:x' USING i;

CLOSE l_cursor;

END LOOP;

END;

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - -

Parse 5000Parse 5000Parse 5000Parse 5000 0.10 0.09 0 0 0 0

Execute 5000 0.14 0.13 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - -

total 10000 0.24 0.23 0 0 0 0

쿼리 5,000 번 반복

5,000번 소프트 파싱

정적 커서

(무 파싱)

DECLARE

CURSOR c(p_input IN VARCHAR2) IS

SELECT XSELECT XSELECT XSELECT X

FROM EMP_3FROM EMP_3FROM EMP_3FROM EMP_3

WHERE X = WHERE X = WHERE X = WHERE X = p_inputp_inputp_inputp_input;;;;

BEGIN

FOR i IN 1..5000 LOOP

OPEN OPEN OPEN OPEN c(ic(ic(ic(i););););

CLOSE c;

END LOOP;

END;

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - -

Parse 1Parse 1Parse 1Parse 1 0.00 0.00 0 0 0 0

Execute 5000 0.11 0.11 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - -

total 5001 0.11 0.11 0 0 0 0

1번 파싱

Page 27: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

27272727이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

4. 가능한 한 적게 파싱하기 Page 402 ~ 421Page 402 ~ 421

4.2 PL/SQL을 사용한 파스 줄이기

⊙ 지금까지 본 것처럼 캐시에 저장할 수 없는 방법은 지양하고 캐시에 저장 가능한 방법을 사용해야 한다.

▶ PL/SQL은 오라클의 EXECUTE IMMEDIATE 또는 REF_CURSOR FOR 문을 캐시에 저장하지 못한다.

▶ 가능한 한 암묵적이거나 명시적인 커서 형태로 정적 SQL을 사용하라.

▶ 만약 같은 동적 문을 여러 차례에 걸쳐 반복적으로 수행해야 하는 경우는 EXECUTE IMMEDIATE 보다는 DBMS_SQL을 사용하라.

▶ DBMS_SQL 대신에 배열을 사용하여, 즉 다수의 입력으로 하나의 동적 문을 실행하는 방안을 고려한다. 이렇게 하면 실행 시간 실행에 극적인

차이가 생기며 확장성이 엄청나게 제고된다. 왜냐하면, 필요한 리소스의 양이 감소하고 필요한 래치의 수가 눈에 띄게 줄어들기 때문이다.

⊙ 참조 커서가 캐시에 저장될 수 없는 이유

▶ PL/SQL이 보통의 커서를 저장할 수 있는 것처럼 참조 커서를 캐시에 저장할 수 있을 것처럼 보이지만 실제로는 그렇지 않다.

▶ 이유는 참조 커서가 동적으로 열릴 수 있을 뿐만 아니라 닫히기 전에 같은 참조 커서가 여러 차례 열릴 수 있기 때문이다.

▶ 그러므로 커서에 대한 포인터 역할을 하는 참조 커서는 캐시에 저장될 수 없다.

Page 28: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

28282828이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

4. 가능한 한 적게 파싱하기 Page 402 ~ 421Page 402 ~ 421

4.2 PL/SQL을 사용한 파스 줄이기

⊙ DBMS_SQL 사용 예제

▶ EXECUTE IMMEDIATE 은 동적 쿼리를 실행할 때 한 건마다 파싱을 하지만 DBMS_SQL은 한번만 파싱을 한다.

▶ DBMS_SQL 을 사용한 프로시저와 EXECUTE IMMEDIATE 를 사용한 프로시저를 비교하면서 파싱을 살펴본다.

-------- 1. 1. 1. 1. 패키지패키지패키지패키지 선언선언선언선언

CREATE OR REPLACE PACKAGE DYN_INSERT AS

PROCEDURE DBMS_SQL_METHOD(p_tname IN VARCHAR2,

p_value IN VARCHAR2);

PROCEDURE EXEC_IMD_METHOD(p_tname IN VARCHAR2,

p_value IN VARCHAR2);

END;

-------- 2. 2. 2. 2. 패키지패키지패키지패키지 Body Body Body Body 선언선언선언선언

CREATE OR REPLACE PACKAGE BODY DYN_INSERT AS

g_last_tname VARCHAR2(30); -------- 마지막마지막마지막마지막 테이블을테이블을테이블을테이블을 기억하기기억하기기억하기기억하기 위한위한위한위한 변수변수변수변수

g_cursor NUMBER := DBMS_SQL.OPEN_CURSOR;

-------- DBMS_SQL_METHODDBMS_SQL_METHODDBMS_SQL_METHODDBMS_SQL_METHOD

PROCEDURE DBMS_SQL_METHOD(p_tname IN VARCHAR2,

p_value IN VARCHAR2) IS

l_rows NUMBER;

BEGIN

IF(g_last_tname <> p_tname OR g_last_tname IS NULL) THEN

DBMS_SQL.PARSE(g_cursor,

'INSERT INTO ' || p_tname || ' (x) values (:x)',

DBMS_SQL.NATIVE);

g_last_tname := p_tname;

END IF;

DBMS_SQL.BIND_VARIABLE(g_cursor, ':x', p_value);

l_rows := DBMS_SQL.EXECUTE(g_cursor);

END DBMS_SQL_METHOD;

-------- EXEC_IMD_METHODEXEC_IMD_METHODEXEC_IMD_METHODEXEC_IMD_METHOD

PROCEDURE EXEC_IMD_METHOD(p_tname IN VARCHAR2,

p_value IN VARCHAR2) IS

BEGIN

EXECUTE IMMEDIATE

'INSERT INTO ' || p_tname || '(x) values (:x)' USING p_value;

END EXEC_IMD_METHOD;

END;

-------- 3. 3. 3. 3. 프로시저프로시저프로시저프로시저 실행실행실행실행(DBMS_SQL_METHOD)(DBMS_SQL_METHOD)(DBMS_SQL_METHOD)(DBMS_SQL_METHOD)

DECLARE

BEGIN

FOR i IN 1..5000 LOOP

DYN_INSERT.DBMS_SQL_METHOD('EMP_3', TO_CHAR(i));

END LOOP;

END;

-------- 4. 4. 4. 4. 프로시저프로시저프로시저프로시저 실행실행실행실행((((EXEC_IMDEXEC_IMDEXEC_IMDEXEC_IMD_METHOD)_METHOD)_METHOD)_METHOD)

DECLARE

BEGIN

FOR i IN 1..5000 LOOP

DYN_INSERT.EXEC_IMD_METHOD('EMP_3', TO_CHAR(i));

END LOOP;

END;

Page 29: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

29292929이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

4. 가능한 한 적게 파싱하기 Page 402 ~ 421Page 402 ~ 421

4.2 PL/SQL을 사용한 파스 줄이기

⊙ DBMS_SQL 사용 예제

▶ TKPROF 보고서 비교

-------- 3. 3. 3. 3. 프로시저 실행(DBMS_SQL_METHOD)

DECLARE

BEGIN

FOR i IN 1..5000 LOOP

DYN_INSERT.DBMS_SQL_METHOD('EMP_3', TO_CHAR(i));

END LOOP;

END;

-------- 4. 4. 4. 4. 프로시저 실행(EXEC_IMD_METHOD)

DECLARE

BEGIN

FOR i IN 1..5000 LOOP

DYN_INSERT.EXEC_IMD_METHOD('EMP_3', TO_CHAR(i));

END LOOP;

END;

INSERT INTO EMP_3 (x) INSERT INTO EMP_3 (x) INSERT INTO EMP_3 (x) INSERT INTO EMP_3 (x)

valuesvaluesvaluesvalues

(:x)(:x)(:x)(:x)

call count call count call count call count cpucpucpucpu elapsed disk query current rowselapsed disk query current rowselapsed disk query current rowselapsed disk query current rows

---------------------------- ------------------------ -------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------- - - - - - - - - -- - - - - - - - - -- - - - - - - - - -- - - - - - - - - -

Parse 1Parse 1Parse 1Parse 1 0.00 0.00 0.00 0.00 0.000.000.000.00 0 0 0 00 0 0 00 0 0 00 0 0 0

Execute 5000 0.34 0.35 0 8 Execute 5000 0.34 0.35 0 8 Execute 5000 0.34 0.35 0 8 Execute 5000 0.34 0.35 0 8 5105 50005105 50005105 50005105 5000

Fetch 0 0.00 Fetch 0 0.00 Fetch 0 0.00 Fetch 0 0.00 0.000.000.000.00 0 0 0 00 0 0 00 0 0 00 0 0 0

---------------------------- ------------------------ -------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------- - - - - - - - - -- - - - - - - - - -- - - - - - - - - -- - - - - - - - - -

total 5001 0.35 total 5001 0.35 total 5001 0.35 total 5001 0.35 0.350.350.350.35 0 8 5105 50000 8 5105 50000 8 5105 50000 8 5105 5000

INSERT INTO EMP_3(x) INSERT INTO EMP_3(x) INSERT INTO EMP_3(x) INSERT INTO EMP_3(x)

valuesvaluesvaluesvalues

(:x)(:x)(:x)(:x)

call count call count call count call count cpucpucpucpu elapsed disk query current rowselapsed disk query current rowselapsed disk query current rowselapsed disk query current rows

---------------------------- ------------------------ -------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------------------------------------------------------------------------

Parse 5000Parse 5000Parse 5000Parse 5000 0.09 0.09 0.09 0.09 0.090.090.090.09 0 0 0 00 0 0 00 0 0 00 0 0 0

Execute 5000 0.38 Execute 5000 0.38 Execute 5000 0.38 Execute 5000 0.38 0.380.380.380.38 0 5009 5110 50000 5009 5110 50000 5009 5110 50000 5009 5110 5000

Fetch 0 0.00 Fetch 0 0.00 Fetch 0 0.00 Fetch 0 0.00 0.000.000.000.00 0 0 0 00 0 0 00 0 0 00 0 0 0

---------------------------- ------------------------ -------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------------------------------------------------------------------------

total 10000 0.47 0.48 0 5009 total 10000 0.47 0.48 0 5009 total 10000 0.47 0.48 0 5009 total 10000 0.47 0.48 0 5009 5110 50005110 50005110 50005110 5000

Page 30: 이펙티브오라클cfs7.tistory.com/upload_control/download.blog?fhandle=YmxvZzIzNzA5... · 이펙티브오라클성능향상을위한데이터 ... (insert , delete, update ...

30303030이펙티브 오라클 성능 향상을 위한 데이터베이스 설계 비법

5. 요약 Page 402 ~ 421Page 402 ~ 421

⊙ 오라클은 DML 구문에서는 파싱을 시도하지만 DDL에서는 파싱을 시도하지 않는다.

▶ DML에서는 일관된 읽기 메커니즘을 위해 현재 모드로 바꾸고 작업을 수행한다.

▶ DDL구문이 제출되면 내부적으로 대략 50개 정도의 SQL 작업이 일어난다.

⊙ 바인드 변수를 사용하는 목적

▶ 바인드 변수를 사용하지 않으면 최적화하는데 많은 시간을 허비함으로써 시스템이 제대로 동작하지 않는다.

▶ 인용 부호를 제대로 표현하기 수월하다.

▶ SQL Injection 해킹 공격에 대응할 수 있다.

⊙ 바인드 변수를 효율적으로 사용하는 방법

▶ 바인드 변수를 사용하게 되면 분포도를 고려한 실행계획을 작성할 수 없다. 그러므로 Peeking 기법을 활용하라.

▶ 파싱을 줄이기 위해 캐시에 저장할 수 없는 참조 커서를 사용하지 말고 정적 커서를 사용하라.

▶ EXECUTE IMMEDIATE 보다는 DBMS_SQL 을 사용하라.