이펙티브오라클 제 5 장문처리 1. 수정 DML의시작과끝 2. DDL 처리 3. 바인드변수의사용 4. 가능한한적게파싱하기 5. 요약 강정식 ( xsofter@empal.com ) 이문서는 Oracle Club 데이터베이스스터디모임에서작성하였습니다. 1
1. 수정 DML의시작과끝 Page 369 ~ 371 1.1 수정 DML 문 (INSERT, DELETE, UPDATE, MERGE) 은일반적으로쿼리구성요소를갖는다. DELETE, UPDATE, MERGE 문은테이블에서수정대상행을찾아이들을수정한다. UPDATE EMP SET SAL = SAL * 1.1; INSERT 문은이를포함할수도있고아닐수도있다. 쿼리구성요소포함안함 INSERT INTO EMP (EMPNO, DEPTNO) VALUES (9999, 10); 쿼리구성요소포함 INSERT INTO TAB SELECT EMPNO FROM EMP; 1.2 수정문은일관된읽기메커니즘을이용하여갱신대상데이터를찾은다음이를수정하기위하여현재모드로바꾼다. 인덱스가없는컬럼을변경할경우 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 오른쪽에서보는것처럼 current 와 rows 의수가거의동일하다. 이는수정을하기위해현재모드 (current) 로전환하고수정한다음에 (rows) 이를반환함을의미한다. 2
1. 수정 DML 의시작과끝 Page 369 ~ 371 1.2 수정문은일관된읽기메커니즘을이용하여갱신대상데이터를찾은다음이를수정하기위하여현재모드로바꾼다. 인덱스가있는컬럼을변경할경우 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 보는것처럼인덱스를추가한경우도마찬가지로현재모드로바꾸기위한작업이증가한것을볼수있다. 다만인덱스를추가하기전보다훨씬증가하였는데이이유는다음과같다. 이전과같이현재데이터값을현재모드로돌리기위한작업한다. 인덱스유지를위해인덱스값또한수정작업을하기위해현재모드로바꾸는작업을병행한다. 이외에도 DML 은다음과같은작업을수행한다. 내부적으로실패할경우수정이복구될수있도록하기위한리두생성 사용자에의해트랜잭션이롤백되는경우에수정을원상태로돌리기위한언두생성 3
2. DDL 처리 Page 371 ~ 373 2.1 DDL문은 DML과달리최적화작업을하지않고파싱을한다. 2.2 DDL문을실행하기위해서는내부적으로이구문을검증하기위해대략 50개의 SQL문이필요하다. CREATE TABLE T (X INT) 이구문이제출될경우 오라클은사용자가테이블을생성할수있는권한을갖고있는지체크한다. 어느테이블영역에생성되어야하는지체크한다. 테이블영역의쿼터를초과하지는않았는지체크한다. T라고명명된객체가존재하는지않는지체크한다. 대상테이블이실제로존재하도록하기위해 SYS.OBJ$, SYS.COL$ 등에행을삽입하는문을실행할수있다. 2.3 테스트 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이고일부는수정이다. 4
2. DDL 처리 Page 371 ~ 373 2.4 DDL 문은 DDL 문이실행되기직전과 COMMIT 또는 ROLLBACK 직후에묵시적인 COMMIT 이수행된다. DDL 개념은다음과같다. COMMIT; PARSE THE DDL 권한과구문검사 DO_THE_DDL; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DDL 은이처럼시작전에미해결된작업이있으면항상이들의커밋을완료한다. 이는 DDL 명령이실패하더라도대부분의트랜잭션의커밋은완료된다는것을의미한다. 5
3. 바인드변수의사용 Page 373 ~ 402 바인드변수란? 호스트환경에서선언한변수로런타임값을하나이상의 PL/SQL 프로그램과주고받는데사용한다. 이변수는 SQL*PLUS 환경에서변수를사용하기위해다음과같은구문을사용한다. VAR[IABLE] 변수명 [ NUMBER CHAR(n) VARCHAR2(n)] 또한바인드변수값을나타내기위해서는 PRINT문을사용한다. 바인드변수를사용하면오라클은한번만하드파싱을수행하고이후에는계속해서소프트파싱을수행할수있다. 즉실행시간에값이공급될수있는개체틀로대체됨을의미한다. 사용예제 바인드변수없는쿼리 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 6
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 1) 1) 바인드변수를사용하지않으면성능이낮다. 쿼리문을제출하면오라클은 SGA 영역의공유풀을먼저찾아본다. 쿼리문이제출되면파싱에서이쿼리가이미파싱되었으며최적화되었는지를파악하기위해 SGA영역의 Shared Pool을살펴본다. 소프트파싱 : 만약공유풀에이쿼리가존재한다면최적화절차를건너뛰고바로문을실행한다. 하드파싱 : 만약공유풀에이쿼리가존재하지않는다면오라클은 CPU 중심의작업인하드파싱을하고최적화과정을거친다. 사용예제 : 속도비교 하드파싱 (3.58 sec) 테스트를하기위한 T 테이블생성 DECLARE CREATE TABLE T (X VARCHAR2(5)); TYPE rc IS REF CURSOR; l_cursor RC; 테이블이생성되었습니다. l_dummy ALL_OBJECTS.OBJECT_NAME%TYPE TYPE; l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME; FOR I IN 1..5000 LOOP OPEN l_cursor FOR 'SELECT X FROM T WHERE X = ' TO_CHAR(i); FETCH l_cursor INTO l_dummy; CLOSE l_cursor; END LOOP; DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - L_START) / 100, 2) 'sec'); 소프트파싱 (1.14 sec) DECLARE TYPE rc IS REF CURSOR; l_cursor RC; l_dummy ALL_OBJECTS.OBJECT_NAME%TYPE TYPE; l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME; FOR I IN 1..5000 LOOP OPEN l_cursor FOR 'SELECT X FROM T WHERE X = :x' USING i; FETCH l_cursor INTO l_dummy; CLOSE l_cursor; END LOOP; DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - L_START) / 100, 2) 'sec'); 7
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 1) 1) 바인드변수를사용하지않으면성능이낮다. 사용예제 : 내부처리절차비교 하드파싱 DECLARE TYPE rc IS REF CURSOR; l_cursor RC; FOR i IN 1..5000 LOOP OPEN l_cursor FOR 'SELECT X FROM T WHERE X = ' TO_CHAR(i); CLOSE l_cursor; END LOOP; 내부처리절차 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - total 18 1.96 2.10 0 118 0 4 Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS SELECT X FROM T WHERE X = 1 SELECT X FROM T WHERE X = 2 call 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - total 10003 2.62 2.83 0 2 0 1 Misses in library cache during parse: 5000 SELECT X 5006 user SQL statements in session. FROM T 1 internal SQL statements in session. WHERE X = 3 5007 SQL statements in session. 0 statements EXPLAINed in this session. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * SELECT X FROM T 5000천번을서로다른쿼리로인식하고하드파싱을한다. WHERE X = 5000 8
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 1) 1) 바인드변수를사용하지않으면성능이낮다. 사용예제 : 내부처리절차비교 소프트파싱 DECLARE TYPE rc IS REF CURSOR; l_cursor RC; FOR i IN 1..5000 LOOP OPEN l_cursor FOR 'SELECT X FROM EMP_3 WHERE X = :x' USING i; CLOSE l_cursor; END LOOP; * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - total 18 1.74 1.86 0 118 0 4 Misses in library cache during parse: 0 내부처리절차 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 RECURSIVE STATEMENTS call 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - total 10003 0.24 0.23 0 2 0 1 Misses in library cache during parse: 0 이값이 0이라는얘기는 이쿼리가소프트파싱을하였음을의미한다. 5006 user SQL statements in session. 1 internal SQL statements in session. 5007 SQL statements in session. 0 statements EXPLAINed in this session. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 바인드변수를사용하여공유풀에있는것을확인한후소프트파싱을한다. 9
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 2) 2) 바인드변수를사용하지않으면확장성이낮다. 하드파싱은 CPU 중심의작업일뿐만아니라확장성이낮은작업이다. 쿼리의파싱과최적화는여러개가동시에수행될수있는작업이아니다. 오라클은반드시 SGA의공유데이터구조에대한액세스를제어하여야한다. 만약이런제어가안된다면종국에는시스템이깨지고만다. 그러므로오라클은래치 ( 경렬직렬화장치 ) 를사용하여공유구조를보호한다. 이런구조때문에파싱이많으면많을수록공유풀을보호하는이들래치에대한경함이늘어나고동시작업은줄어들수밖에없다. 래치 ( 직렬화장치 ) : 많은세션이공유데이터구조를안전하게액세스하고수정할수있도록해주는기능 이런문제를해결할수있는유일한방법은처음부터바인드변수를사용해야한다. 하드파싱은확정성에취약하다. 바인드변수를사용하지않고초당수백또는수천개의쿼리를실행해야하는경우시스템은멈추고말것이다. 이를해결하기위해 CPU를추가하거나클러스터를사용한다고하더라도문제가해결되지는않는다. 이유는하드웨어가불충분하여생긴것이아니라다른세션이사용대상데이터구조를점유하고있어서불가피하게기다릴수밖에없어서생긴문제이기때문이다. 10
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 3) 3) 바인드변수를사용하지않으면코드작성이어렵다. 인용부호 ( 작은따옴표, 큰따옴표등 ) 가포함된문자열을쿼리에대입하는경우실패하는사례가증가한다. 실패예제 : 검색창에 [Ben and Jerry s] 라고값을입력하고조회를할경우 조회화면 검색창 Ben and Jerry s FIND Field_1 Field_2 Field_3 Field_4 Field_5 Field_6 FIND 버튼로직 SELECT * FROM TAB WHERE Field_1 = v_value ;-- v_value 변수는검색값 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 이처럼쿼리에문자열을포함할경우바인드변수를사용해야만다양한값에대응을할수있다. 11
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 4) 4) 바인드변수가사용되지않은코드의안전도는낮다. 바인드변수를사용하지않을경우약간의편법을이용하여 WHERE 조건을임의로변경할수있다. 이런대표적인사례가 SQL Injection 해킹기법이다. SQL Injection 해킹기법 : SQL Injection 은웹페이지를통해입력된파라미터값을이용하여쿼리를재구성하는방법이다. 사용자테이블에아이디와패스워드가 oracleclub / 0909 와같이있을경우 정상동작 아이디 비밀번호 oracleclub 0909 FIND SELECT CHECK FROM TAB WHERE USERNAME = oracleclub AND PASSWORD = 0909 만약사용자가다음과같은값을넣을경우 SQL Injection 아이디 비밀번호 oracleclub babo or 1 = 1 FIND SELECT CHECK FROM TAB WHERE USERNAME = oracleclub AND PASSWORD = babo or 1 = 1 이럴경우비밀번호를잘못입력하여도 1 = 1 구문이항상참이기때문에로그인이된다. 12
3. 바인드변수의사용 Page 373 ~ 402 3.1 바인드변수를사용하면무엇이좋은가? 4) 4) 바인드변수가사용되지않은코드의안전도는낮다. 실제예제 사용자테이블생성하고유저생성 CREATE TABLE EMP_2(USERNAME VARCHAR2(30), PASSWORD VARCHAR2(30)); INSERT INTO EMP_2 VALUES( oracleclub', 0909'); COMMIT; 문자열을결합한형태 -- 1. 변수선언 ACCEPT uname PROMPT Enter username : Enter username : oracleclub ACCEPT pword PROMPT Enter password : Enter password : babo or 1 = 1 문자열을바인딩변수를사용하여접근한형태 -- 1. 변수선언 VARIABLE uname VARCHAR2(30); VARIABLE pword VARCHAR2(30); Exec :uname : := oracleclub oracleclub ; Exec :pword : := babo babo or 1 = 1 -- 2. 쿼리 SELECT COUNT(*) FROM TAB WHERE USERNAME = &uname uname AND PASSWORD = &pword pword 1 -- 2. 쿼리 SELECT COUNT(*) FROM TAB WHERE USERNAME = &uname uname AND PASSWORD = &pword pword 0 Where username = &uname uname Where username = oracleclub oracleclub Where pword = &pword pword Where pword = babo babo or 1 = 1 13
3. 바인드변수의사용 Page 373 ~ 402 3.2 자바와 VB 에서바인드변수사용하기 JDBC 프로그램에서 Statement와 PreparedStatement 비교 일반적으로성능이좋다는이유만으로바인드변수가허용되는 PreparedStatement보다는바인드변수가허용되지않는 Statement를사용하도록강권하고있다. 그러나단일사용자환경이나다중사용자환경에서도 Statement보다는 PreparedStatement가훨씬성능이좋다. 이유는앞에서도보았다시피하드파싱을하게되는 Statement는수많은래치를유발하고이는확장성을저해한다. 결국사용자가늘어나면시스템은실패할수밖에없다. 그러므로가급적 Statement를통해 값의결합 을시도하지말고바인드변수를사용할수있는 PreparedStatement를사용하라. 14
변수의사용 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을소개하고있다. 이펙티브오라클성능향상을 데이터베이스설계비법 15
3. 바인드변수의사용 Page 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 한다는뜻이고첫번째바인딩되는값에따라실행계획이고정된다. 16
3. 바인드변수의사용 Page 373 ~ 402 3.4 바인드변수조사 (Bind Variable Peeking) Peeking 예제 학점과학생테이블을 1:M 관계로테이블을생성하고이를활용하여 Peeking 을살펴보자. ERD 학점 # 학점 * 시작점수 * 종료점수 학생 # 학번 * 이름 * 학점 (FK) 학생테이블 1000개의로우가존재하고 4명을제외한나머지는모두학점이 B STUNO STUNAME GRADE 10 oracleclub1 A 20 oracleclub2 C 30 oracleclub3 D 데이터구조 40 50 oracleclub4 oracleclub5 F B 학점테이블 60 oracleclub6 B GRADE S_POINT E_POINT 70 oracleclub7 B A 91 100 80 oracleclub8 B B 81 90 90 oracleclub9 B C 71 80 D 61 70 F 0 60 10000 oracleclub1000 B 17
3. 바인드변수의사용 Page 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); -- 공유풀초기화 ALTER SYSTEM FLUSH SHARED_POOL; 시스템이변경되었습니다. -- TRACE 생성 ALTER SESSION SET SQL_TRACE=TRUE; 세션이변경되었습니다. ALTER TABLE STU_TAB ADD CONSTRAINT STU_PK_STUNO PRIMARY KEY(STUNO); -- 학생번호에 PK 부여 테이블이변경되었습니다. CREATE INDEX STU_GRADE_INDX ON STU_TAB(GRADE); -- 학점에인덱스생성 인덱스가생성되었습니다. ANALYZE TABLE STU_TAB COMPUTE STATISTICS -- 학생테이블통계정보생성 FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS; 테이블이분석되었습니다. 18
3. 바인드변수의사용 Page 373 ~ 402 3.4 바인드변수조사 (Bind Variable Peeking) Peeking 예제 학점과학생테이블을 1:M 관계로테이블을생성하고이를활용하여 Peeking 을살펴보자. Script : 리터럴값에따른실행계획 -- 분포도좋은데이터들어옴 SELECT COUNT(*), MIN(STUNO) FROM STU_TAB WHERE GRADE = 'A'; 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) -- 분포도나쁜데이터들어옴 SELECT COUNT(*), MIN(STUNO) FROM STU_TAB WHERE GRADE = B'; Rows Row Source Operation ------- ----------------------------------------------- ---- 1 SORT AGGREGATE 9996 TABLE ACCESS FULL STU_TAB 19
3. 바인드변수의사용 Page 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; -- 학번 SELECT COUNT(*), MIN(STUNO) INTO v_count, v_stuno FROM STU_TAB WHERE GRADE = :x; Rows Row Source Operation Rows Row Source Operation ------- ------------------------------------------------- -- 1 SORT AGGREGATE ------- ------------------------------------------------- -- 1 SORT AGGREGATE 9996 TABLE ACCESS BY INDEX ROWID STU_TAB 9996 INDEX RANGE SCAN STU_GRADE_INDX(object id 1365117) 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; -- 학번 SELECT COUNT(*), MIN(STUNO) INTO v_count, v_stuno FROM STU_TAB WHERE GRADE = :x; 20
3. 바인드변수의사용 Page 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; -- 학번 SELECT COUNT(*), MIN(STUNO) INTO v_count, v_stuno FROM STU_TAB WHERE GRADE = :x; Rows Row Source Operation ------- ----------------------------------------------- ---- 1 SORT AGGREGATE 1 TABLE ACCESS FULL STU_TAB Rows Row Source Operation ------- ----------------------------------------------- ---- 1 SORT AGGREGATE 9996 TABLE ACCESS FULL STU_TAB -- 분포도좋은데이터나중에실행 VARIABLE x VARCHAR2(1) EXEC :x := A'; DECLARE v_count NUMBER := 0; -- 학생수 v_stuno NUMBER := 0; -- 학번 SELECT COUNT(*), MIN(STUNO) INTO v_count, v_stuno FROM STU_TAB WHERE GRADE = :x; 21
3. 바인드변수의사용 Page 373 ~ 402 3.4 바인드변수조사 (Bind Variable Peeking) Peeking 예제 학점과학생테이블을 1:M관계로테이블을생성하고이를활용하여 Peeking을살펴보자. 지금까지살펴본것처럼처음들어오는값의실행계획을가지고그이후의바인드변수는모두같은실행계획인것을볼수있었다. 이 Peeking을통해이전에는바인드변수를사용할때분포도를고려하지않고평균값을이용하는기존의최적화방법의한계를극복한걸로볼수있으나여전히비효율의기능을가지고있다. 지금까지살펴본것처럼가장이상적인방법은항상소프트파싱을하고또한분포도를고려한실행계획을세울수있는전략이되겠으나바인드변수 ( 소프트파싱 ) 와분포도를고려한실행계획은 Peeking 이전에는언제나상극이었다. 그렇다고 Peeking이도입되었으나이또한맨처음의리터럴값만살펴보고그이후에는맨처음세운실행계획을따라가므로완벽한것이아니다. 그렇다면현재까지나와있는자원을가지고위에서살펴본가장이상적인접근법을구현할수는없을까? 완벽하진않지만부분적으로이를극복할수있다. 그방법은가장분포도가나쁜값이들어올경우와분포도가좋은값이들어올경우로나누어쿼리를맨처음생성하면그이후에는항상최적화방법을가지고실행계획을작성할수있다. 22
3. 바인드변수의사용 Page 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; -- 학번 IF(p_grade = 'B') THEN ELSE SELECT COUNT(*), MIN(STUNO) INTO v_count, v_stuno FROM STU_TAB B_TYPE WHERE GRADE = p_grade; SELECT COUNT(*), MIN(STUNO) INTO v_count, v_stuno FROM STU_TAB OTHER_TYPE WHERE GRADE = p_grade; END IF; -- 저장형함수이용하여쿼리실행 FOR rec_stu IN (SELECT GRADE FROM STU_TAB) LOOP DBMS_OUTPUT.PUT_LINE(rec_stu.GRADE ' : ' GET_DATA(rec_stu.GRADE)); END LOOP; A : 1 명, 최소학번 10 B : 9996 명, 최소학번 50 C : 1 명, 최소학번 20 D : 1 명, 최소학번 30 F : 1 명, 최소학번 40 RETURN v_count ' 명 ', ' 최소학번 ' v_stuno; 23
3. 바인드변수의사용 Page 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 24
4. 가능한한적게파싱하기 Page 402 ~ 421 4.1 파싱비용 일반적으로커서를사용할때개발자들은 CLOSE를신속하게하는경향이있다. 커서를제대로사용하는애플리케이션은세션별로데이터베이스에대한커서를한번만열고수차례에걸쳐이것을실행한다. 커서를제대로사용하지못하는애플리케이션은실행시마다커서를열어, 즉엄청난비용을들여데이터베이스를처리하는경향을보인다. 하지만오라클은또다시 OPEN 될것에대비하여캐시에저장한다. PL/SQL에서는 Close Cursor를하면닫는것처럼보이지만실제로는커서가열린상태로유지된다. 그이유는이커서가다시사용될것이라고생각하고이문을캐시에저장하는데이것이바로정적커서이다. 즉, 참조커서는커서를바로닫지만정적커서는열린상태로캐시에저장된다. 참조커서 & 정적커서 참조커서 : 커서의내용을실행부 ( END) 안에서사용하는구문 Ex) FOR rec_val IN (SELECT * FROM EMP) LOOP 정적커서 : 커서의내용을선언부 (DECLARE) 안에서사용하는구문 Ex) DECLARE CURSOR cur_val IS SELECT * FROM EMP FOR rec_val IN cur_val LOOP [ 로직 ] END LOOP 이를이용하여느슨한소프트파싱보다무파싱을하도록유도해야한다. 이를하기위해서는가급적참조커서보단정적커서를사용해서파싱을한번만하도록유도한다. 즉하드파싱보다는느슨한소프트파싱을, 느슨한소프트파싱보다는무파싱을하도록유도하자. 하드파싱 : 리터럴 ( 실행시마다변경이될수있는 ) 값을이용한쿼리 느슨한소프트파싱 : 바인드변수를사용한쿼리 무파싱 : 바인드변수역할을선언부에커서로선언하고이를변수로받아처리하는쿼리 25
4. 가능한한적게파싱하기 Page 402 ~ 421 4.1 파싱비용 DECLARE TYPE rc IS REF CURSOR; 하드파싱 l_cursor RC; FOR i IN 1..5000 LOOP OPEN l_cursor FOR 'SELECT X FROM EMP_3 WHERE X = ' TO_CHAR(i); CLOSE l_cursor; END LOOP; DECLARE TYPE rc IS REF CURSOR; 참조커서 l_cursor RC; ( 느슨한소프트파싱 ) FOR i IN 1..5000 LOOP OPEN l_cursor FOR 'SELECT X FROM EMP_3 WHERE X = :x' USING i; CLOSE l_cursor; END LOOP; 쿼리 5,000 번반복 5,000 번소프트파싱 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call 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 ------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - - total 10003 2.62 2.83 0 2 0 1 Misses in library cache during parse: 5000 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 DECLARE CURSOR c(p_input IN VARCHAR2) IS SELECT X FROM EMP_3 WHERE X = p_input; FOR i IN 1..5000 LOOP OPEN c(i); CLOSE c; END LOOP; 정적커서 ( 무파싱 ) 1 번파싱 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- --------- - - - - - - - - - - - Parse 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 26
4. 가능한한적게파싱하기 Page 402 ~ 421 4.2 PL/SQL 을사용한파스줄이기 지금까지본것처럼캐시에저장할수없는방법은지양하고캐시에저장가능한방법을사용해야한다. PL/SQL은오라클의 EXECUTE IMMEDIATE 또는 REF_CURSOR FOR 문을캐시에저장하지못한다. 가능한한암묵적이거나명시적인커서형태로정적 SQL을사용하라. 만약같은동적문을여러차례에걸쳐반복적으로수행해야하는경우는 EXECUTE IMMEDIATE 보다는 DBMS_SQL을사용하라. DBMS_SQL 대신에배열을사용하여, 즉다수의입력으로하나의동적문을실행하는방안을고려한다. 이렇게하면실행시간실행에극적인차이가생기며확장성이엄청나게제고된다. 왜냐하면, 필요한리소스의양이감소하고필요한래치의수가눈에띄게줄어들기때문이다. 참조커서가캐시에저장될수없는이유 PL/SQL이보통의커서를저장할수있는것처럼참조커서를캐시에저장할수있을것처럼보이지만실제로는그렇지않다. 이유는참조커서가동적으로열릴수있을뿐만아니라닫히기전에같은참조커서가여러차례열릴수있기때문이다. 그러므로커서에대한포인터역할을하는참조커서는캐시에저장될수없다. 27
4. 가능한한적게파싱하기 Page 402 ~ 421 4.2 PL/SQL 을사용한파스줄이기 DBMS_SQL 사용예제 EXECUTE IMMEDIATE 은동적쿼리를실행할때한건마다파싱을하지만 DBMS_SQL 은한번만파싱을한다. DBMS_SQL 을사용한프로시저와 EXECUTE IMMEDIATE 를사용한프로시저를비교하면서파싱을살펴본다. -- 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); -- 3. 프로시저실행 (DBMS_SQL_METHOD) DECLARE FOR i IN 1..5000 LOOP DYN_INSERT.DBMS_SQL_METHOD('EMP_3', TO_CHAR(i)); END LOOP; -- 4. 프로시저실행 (EXEC_IMD EXEC_IMD_METHOD) _METHOD) DECLARE FOR i IN 1..5000 LOOP DYN_INSERT.EXEC_IMD_METHOD('EMP_3', TO_CHAR(i)); END LOOP; -- 2. 패키지 Body 선언 CREATE OR REPLACE PACKAGE BODY DYN_INSERT AS g_last_tname VARCHAR2(30); -- 마지막테이블을기억하기위한변수 g_cursor NUMBER := DBMS_SQL.OPEN_CURSOR; -- DBMS_SQL_METHOD PROCEDURE DBMS_SQL_METHOD(p_tname IN VARCHAR2, p_value IN VARCHAR2) IS l_rows NUMBER; 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_METHOD PROCEDURE EXEC_IMD_METHOD(p_tname IN VARCHAR2, p_value IN VARCHAR2) IS EXECUTE IMMEDIATE 'INSERT INTO ' p_tname '(x) values (:x)' USING p_value; END EXEC_IMD_METHOD; 28
4. 가능한한적게파싱하기 Page 402 ~ 421 4.2 PL/SQL 을사용한파스줄이기 DBMS_SQL 사용예제 TKPROF 보고서비교 -- 3. 프로시저실행 (DBMS_SQL_METHOD) DECLARE FOR i IN 1..5000 LOOP DYN_INSERT.DBMS_SQL_METHOD('EMP_3', TO_CHAR(i)); END LOOP; -- 4. 프로시저실행 (EXEC_IMD_METHOD) DECLARE FOR i IN 1..5000 LOOP DYN_INSERT.EXEC_IMD_METHOD('EMP_3', TO_CHAR(i)); END LOOP; INSERT INTO EMP_3 (x) values (:x) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - - Parse 1 0.00 0.00 0 0 0 0 Execute 5000 0.34 0.35 0 8 5105 5000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- - - - - - - - - - - total 5001 0.35 0.35 0 8 5105 5000 INSERT INTO EMP_3(x) values (:x) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5000 0.09 0.09 0 0 0 0 Execute 5000 0.38 0.38 0 5009 5110 5000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10000 0.47 0.48 0 5009 5110 5000 29
5. 요약 Page 402 ~ 421 오라클은 DML 구문에서는파싱을시도하지만 DDL 에서는파싱을시도하지않는다. DML 에서는일관된읽기메커니즘을위해현재모드로바꾸고작업을수행한다. DDL 구문이제출되면내부적으로대략 50 개정도의 SQL 작업이일어난다. 바인드변수를사용하는목적 바인드변수를사용하지않으면최적화하는데많은시간을허비함으로써시스템이제대로동작하지않는다. 인용부호를제대로표현하기수월하다. SQL Injection 해킹공격에대응할수있다. 바인드변수를효율적으로사용하는방법 바인드변수를사용하게되면분포도를고려한실행계획을작성할수없다. 그러므로 Peeking 기법을활용하라. 파싱을줄이기위해캐시에저장할수없는참조커서를사용하지말고정적커서를사용하라. EXECUTE IMMEDIATE 보다는 DBMS_SQL 을사용하라. 30