DB 성능고도화핵심원리 비투엔컨설팅 수석컨설턴트 조시형

원리를알아야답이보인다!!

SQL 개발자 (Developer) 데이터모델을통해업무를이해하고, SQL 을정확히구사하는능력 DB 성능고도화전문가양성 SQL 전문가 (Professional) 성능을고려한고급 SQL 작성능력

DB 성능고도화핵심원리실습문제 declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; end; 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER commit; end loop; end;

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; 보관주기 : 일주일 exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; end; 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER commit; end loop; end;

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; 보관주기 : 일주일 보관주기 : 10 년 exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; end; 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER commit; end loop; end;

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; 보관주기 : 일주일 보관주기 : 10 년 exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; end; PK : 입금일시 + 고객 ID+ 은행코드 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER PK : 수납일자 + 고객 ID commit; end loop; end;

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; 20 만건 execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; 10 만건 보관주기 : 일주일 보관주기 : 10 년 exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; end; PK : 입금일시 + 고객 ID+ 은행코드 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER PK : 수납일자 + 고객 ID commit; end loop; end;

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; end; commit; end loop; end; 20 만건 10 만건 보관주기 : 일주일 보관주기 : 10 년 PK : 입금일시 + 고객 ID+ 은행코드 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER PK : 수납일자 + 고객 ID 20 분소요 è 성능개선방안은?

DBMS 내부처리과정? select ename, job, sal from emp e, dept d where d.deptno = e.deptno and loc = NEW YORK 블랙박스

내부아키텍처와 SQL 수행원리를 알아야하는이유?

1) 알아야효과적으로일을지시할수있기때문

SQL 1 select lpad( 상품번호, 30) lpad( 상품명, 30) lpad( 고객ID, 10) lpad( 고객명, 20) to_char( 주문일시, 'yyyymmdd hh24:mi:ss') from 상품주문 where 주문일시 between :start and :end order by 상품번호 SQL 2 select lpad( 상품번호, 30) lpad( 상품명, 30) lpad( 고객ID, 10) lpad( 상품명, 20) to_char( 주문일시, 'yyyymmdd hh24:mi:ss') from ( select 상품번호, 상품명, 고객ID, 고객명, 주문일시 from 상품주문 where 주문일시 between :start and :end order by 상품번호 )

SQL 3 select min(a. 고객명 ), min(a. 연락처 ), min(a. 주소 ), sum( 주문금액 ) from 고객 a, 주문 b where b. 주문일자 = '20100929' and a. 연령 between 21 and 30 and a. 고객ID = b. 고객ID group by a. 고객ID SQL 4 select a. 고객명, a. 연락처, a. 주소, b. 주문금액 from 고객 a,(select 고객ID, sum( 주문금액 ) 주문금액 from 주문 where 주문일자 = '20100929' group by 고객ID) b where a. 고객ID = b. 고객ID and a. 연령 between 21 and 30

SQL 5 select * from 고객 where 고객 ID = : 고객 ID; select * from 고객 where 고객 ID = C18973 ; SQL 6 select * from 고객불만 where 처리상태 = : 처리상태 ; select * from 고객불만 where 처리상태 = 미처리 ;

1) 알아야효과적으로일을지시할수있기때문

2) 알아야문제를해결할수있기때문

3) 알아야전문가로성장할수있기때문

아키텍처와 SQL 수행원리 대주제프로세스메모리구조파일구조인덱스구조조인수행원리옵티마이저원리 소주제 서버프로세스 : 전용 (Dedicated) 서버, 공유 (Shared) 서버 백그라운드프로세스 : SMON, PMON, DBWn, LGWR, ARCn, CKPT 등 시스템공유메모리 : Buffer Cache, Shared Pool, Log Buffer 등 프로세스전용메모리 : PGA, UGA, CGA 등 데이터파일 : 블록, 익스텐트, 세그먼트, 테이블스페이스와의관계 임시파일 로그파일 : 트랜잭션로그, Online Redo 로그, Archived Redo 로그 인덱스탐색원리 다양한인덱스구조 다양한인덱스스캔방식 NL 조인 Sort Merge 조인 Hash 조인 옵티마이저종류및구성요소 비용계산원리와통계정보의중요성 쿼리변환

Oracle 아키텍처

SQL Server 아키텍처

부하지점 è 캐싱대상 시스템공유메모리의 3 대캐시영역 DB Buffer Cache : 데이터블록, 인덱스블록 Library Cache : SQL 과실행계획, 저장형함수 / 프로시저등 (=Procedure Cache) Log Buffer : Redo Log, Transaction Log 캐시를두는이유 : 해당영역의데이터를읽고쓸때부하가되기때문

DB 튜닝 핵심 원리 I/O를 최소화하고, 버퍼 캐시에 적재 된 데이터 블록을 오랫동안 재사용 하는 방안은? 무거운 파싱과정 을 거쳐 캐시에 적재된 SQL 실행 계획의 재사용성 을 높이는 방안은? Server Process Server Process Server Process 1. DB Buffer Cache è I/O 효율화 2. Library Cache è SQL 파싱 부하 해소 3. 작업요청 è 데이터베이스 Call 최소화 데이터베이스 Call 최소화 방안은?

DB 튜닝핵심원리 Log I/O 튜닝 Log Writer Online Redo Log Online Redo Log Online Redo Log Log Switch Server Process Server Process Server Process 커밋시점조절 비동기식커밋 배치커밋 Nologging 모드 Insert Minimal Logging 모드 Insert

DB 튜닝핵심원리 Lock 튜닝 DML Lock, DDL Lock, Library Cache Lock/Pin, Latch, Buffer Lock 등 공격이최상의수비 : Lock 자체튜닝보다는프로세스가작업을빨리마치도록하는것이최상의 Lock 튜닝

DB 튜닝핵심원리 - 1 1. DB Buffer Cache è I/O 효율화 I/O 를최소화하고, 버퍼캐시에적재된데이터블록을오랫동안재사용하는방안은? 2. Library Cache è SQL 파싱부하해소 3. 작업요청 è 데이터베이스 Call 최소화 Server Process Server Process Server Process

블록 (= 페이지 ) 단위 I/O 데이터저장구조 Tablespace Segment Extent Block Block Row

논리적인블록 I/O 물리적인블록 I/O Server Process 블록읽기요청 메모리 I/O 전기적신호 Buffer Cache 버퍼캐시 Miss 디스크 I/O 액세스 Arm DB File 버퍼캐시히트율 (BCHR) = 논리 I/O 물리 I/O 논리 I/O 100 필요한데이터블록을버퍼캐시에서찾은비율 논리적인블록 I/O 를줄여야물리적인블록 I/O 도감소

Table Access By Index Scan Full Table Scan 인덱스테이블테이블 인덱스효용성 스캔을통한테이블액세스 : 한레코드를읽기위해하나의블록 I/O(à Random I/O) 손익분기점이하소량데이터검색시에만효과적 소요시간 ( 초 ) 손익분기점 Table Access By Index Scan Full Table Scan 추출건수

I/O 효율화튜닝의첫번째원칙 : 인덱스를타야할때인덱스를타고, Full Table Scan 해야할때 Full Table Scan 하게하자.

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop 명시적 (Explicit) 형변환 begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; 보관주기 : 일주일 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; exception when no_data_found then execute immediate end; commit; end loop; end; 보관주기 : 10 년 'insert into 수납 ( 고객ID, 수납일자, 수납금액 ) values(' 묵시적 (Implicit) 형변환 : c. 고객ID ', 20100929, ' c. 입금액 to_number( ')'; 수납일자 ) = 20100929 to_number( 수납일자 ) = 20100929 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER

인덱스컬럼변형에주의!! select 수납금액 from 수납 where 고객ID = :cust_num and 수납일자 = 20100929; 은행입금내역 수납 (O) -------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------- 0 SELECT STATEMENT 1 15 43 (5) Full Table 00:00:01 Scan * 1 TABLE ACCESS FULL 수납 1 15 43 (5) 00:00:01 -------------------------------------------------------------------------- 한건을읽기위해매번 Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- 1 - filter(" 고객 ID"=TO_NUMBER(:CUST_NUM) AND TO_NUMBER(" 수납일자 ")=20100929)

인덱스컬럼변형에주의!! select 수납금액 from 수납 where 고객ID = :cust_num and 수납일자 = 20100929 ; 은행입금내역 수납 (O) -------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 15 2 (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID 수납 1 15 2 (0) 00:00:01 * 2 INDEX UNIQUE SCAN 수납 _PK 1 1 (0) 00:00:01 -------------------------------------------------------------------------------------- 인덱스스캔을통해한번씩만 Table Random Access Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ 2 - access(" 수납일자 "='20100929' AND " 고객 ID"=TO_NUMBER(:CUST_NUM))

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = '20100929'; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = ''20100929'''; exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', ''20100929'', ' c. 입금액 ')'; end; commit; end loop; end; 데이터타입일치화

DB 튜닝핵심원리 - 2 1. DB Buffer Cache è I/O 효율화 2. Library Cache è SQL 파싱부하해소 무거운파싱과정을거쳐캐시에적재된 SQL 실행계획의재사용성을높이는방안은? 3. 작업요청 è 데이터베이스 Call 최소화 Server Process Server Process Server Process

옵티마이저란? 옵티마이저 : SQL 이라는구조화된질의언어로사용자의작업요청을받아처리루틴 (Execution Plan) 을생성 옵티마이저에의한처리루틴을 Library Cache 에캐싱 저장형함수 / 프로시저처럼 Dictionary 에영구저장되지않으며, 캐시에서밀려나면처리루틴을다시생성 처리루틴의목표는비용최소화 - 비용? I/O 일량!! - 최근옵티마이저는 CPU 비용까지고려하지만여전히 I/O 비용이 90% 을이상

하드파싱 vs. 소프트파싱 User Parse Call SQL 과실행계획이캐시에존재? No Hard Parsing Yes Soft Parsing Execute Optimization 말그대로 무거운 처리과정 Row-Source Generation 하드파싱을줄이고, SQL 커서를공유할수있는형태로애플리케이션작성

커서공유 커서 (Cursor) 공유커서 세션커서 애플리케이션커서

커서공유 커서 (Cursor) 공유커서 세션커서 애플리케이션커서 공유커서란, SQL 과실행계획, 그밖에실행에필요한각종정보를담기위해라이브러리캐시에할당된메모리공간

커서공유 Library Cache 커서 1 select * from customer 커서 2 select * from Customer 커서식별자 à SQL 문장그자체 Server Process1 select * from customer Server Process2 select * from customer Server Process3 select * from Customer

커서공유 공유할수없는 SQL 1 SELECT * FROM CUSTOMER; SELECT * FROM CUSTOMER; 2 3 4 5 6 SELECT * FROM CUSTOMER; SELECT * FROM Customer; SELECT * FROM CUSTOMER; SELECT * FROM HR.CUSTOMER; SELECT * FROM CUSTOMER; SELECT /*+ all_rows */ * FROM CUSTOMER; SELECT * FROM CUSTOMER; SELECT /* 주석문 */ * FROM CUSTOMER; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'oraking'; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'javaking'; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy'; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'karajan';

Parameterization SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'oraking'; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'javaking'; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy'; SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'karajan ; procedure LOGIN_ORAKING( ) { } procedure LOGIN_JAVAKING( ) { } procedure LOGIN_TOMMY( ) { } procedure LOGIN_KARAJAN( ) { } SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID; procedure LOGIN(login_id in varchar2) { }

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = '20100929'; create procedure 1 번고객 _ 수납액을 _1000 으로 _ 갱신 ( ) { } ; create procedure 2 번고객 _ 수납액을 _4000 으로 _ 갱신 ( ) { } ; create procedure 3 번고객 _ 수납액을 _7000 으로 _ 갱신 ( ) { } ; create procedure 4 번고객 _ 수납액을 _2000 으로 _ 갱신 ( ) { } ; create procedure 5 번고객 _ 수납액을 _1000 으로 _ 갱신 ( ) { } ;.. execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = ''20100929'''; to_number( 수납일자 ) = 하드20100929 파싱반복 exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', ''20100929'', ' c. 입금액 ')'; end; commit; end loop; end;

declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = '20100929'; execute immediate 'update 수납 set 수납금액 = :amt where 고객 ID = :cust_id and 수납일자 = :dt' using c. 입금액, c. 고객 ID, '20100929' ; exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(:cust_id, :dt, :amt)' using c. 고객 ID, '20100929', c. 입금액 ; end; commit; end loop; end; create procedure 수납변경 ( 고객 ID, 수납금액 ) { } ; call 수납변경 (1, 1000); call 수납변경 (2, 4000); call 수납변경 (3, 7000); call 수납변경 (4, 2000); call 수납변경 (5, 1000);..

SQL> select sql_text, parse_calls, loads, executions, fetches 2 from v$sql 3 where sql_text like '% 수납 %' ; 바인드변수를사용하지않았을때 SQL_TEXT PARSE_CALLS LOADS EXECUTIONS FETCHES ---------------------------------------------- ------------ ----- ------------ -------- update 수납 set 수납금액 where 고객ID = 1 and 1 1 1 0 update 수납 set 수납금액 where 고객ID = 2 and 1 1 1 0 update 수납 set 수납금액 where 고객ID = 3 and 1 1 1 0 update 수납 set 수납금액 where 고객ID = 4 and 1 1 1 0 update 수납 set 수납금액 where 고객ID = 5 and 1 1 1 0...................... insert into 수납 ( 고객번호,, ) values(1,, ) 1 1 1 0 insert into 수납 ( 고객번호,, ) values(2,, ) 1 1 1 0 insert into 수납 ( 고객번호,, ) values(3,, ) 1 1 1 0 insert into 수납 ( 고객번호,, ) values(4,, ) 1 1 1 0 insert into 수납 ( 고객번호,, ) values(5,, ) 1 1 1 0...................... 바인드변수를사용했을때 SQL_TEXT PARSE_CALLS LOADS EXECUTIONS FETCHES ---------------------------------------------- ------------ ----- ------------ -------- update 수납 set 수납금액 where 고객ID = :cust_id 50000 1 50000 0 insert into 수납 (... ) values(:cust_id, :dt, :amt) 50000 1 50000 0

애플리케이션커서캐싱 캐시에서공유커서를찾는과정생략 à No Parse!! 커서 (Cursor) 공유커서 세션커서 애플리케이션커서

애플리케이션커서캐싱 JAVA - Method 1 PreparedStatement stmt = conn.preparestatement("select PWD FROM CUSTOMER WHERE LOGIN_ID =?"); for (int i = 1; i <= count; i++) { stmt.setstring(1, "id" + i); ResultSet rs=stmt.executequery(); // Do Anything rs.close(); } stmt.close();

애플리케이션커서캐싱 JAVA - Method 2 // Statement cache size 를 0 보다크게설정 ((OracleConnection)conn).setStatementCacheSize(3); // Implicit caching 을활성화 ((OracleConnection)conn).setImplicitCachingEnabled(true); for (int i = 1; i <= count; i++) { PreparedStatement stmt = conn.preparestatement("select PWD FROM CUSTOMER WHERE LOGIN_ID =?"); stmt.setstring(1, "id" + i); ResultSet rs=stmt.executequery(); // Do Anything } rs.close(); stmt.close();

애플리케이션커서캐싱 Pro*C for( ; ; ) { EXEC ORACLE OPTION (HOLD_CURSOR=YES); EXEC ORACLE OPTION (RELEASE_CURSOR=NO); EXEC SQL INSERT ; // SQL 수행 EXEC ORACLE OPTION (RELEASE_CURSOR=YES); }

애플리케이션커서캐싱 Oracle PL/SQL - Method 1 loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = '20100929'; Static SQL update 수납 set 수납금액 = c. 입금액 where 고객 ID = c. 고객 ID and 수납일자 = '20100929' ; exception when no_data_found then insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(c. 고객 ID, '20100929', c. 입금액 ); end; end loop;

애플리케이션커서캐싱 Oracle PL/SQL - Method 2 loop begin select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = '20100929'; session_cached_cursors > 0 (10g 이후 ) execute immediate 'update 수납 set 수납금액 = :amt where 고객 ID = :cust_id and 수납일자 = :dt' using c. 입금액, c. 고객 ID, '20100929' ; exception when no_data_found then execute immediate 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(:cust_id, :dt, :amt)' using c. 고객 ID, '20100929', c. 입금액 ; end; end loop;

애플리케이션커서캐싱 바인드변수를사용했을때 (+ 애플리케이션에서커서를캐싱하지않을때 ) SQL_TEXT PARSE_CALLS LOADS EXECUTIONS FETCHES ---------------------------------------------- ------------ ----- ------------ -------- update 수납 set 수납금액 where 고객ID = :cust_id 50000 1 50000 0 insert into 수납 (... ) values(:cust_id, :dt, :amt) 50000 1 50000 0 바인드변수를사용했을때 (+ 애플리케이션에서커서를캐싱할때 ) SQL_TEXT PARSE_CALLS LOADS EXECUTIONS FETCHES ---------------------------------------------- ------------ ----- ------------ -------- update 수납 set 수납금액 where 고객ID = :cust_id 1 1 50000 0 insert into 수납 (... ) values(:cust_id, :dt, :amt) 1 1 50000 0

DB 튜닝핵심원리 - 3 1. DB Buffer Cache è I/O 효율화 2. Library Cache è SQL 파싱부하해소 3. 작업요청 è 데이터베이스 Call 최소화 Server Process Server Process Server Process 데이터베이스 Call

public class ParseCall{ public static void insertreceipt( Connection con, String param1, long param2, long param3) throws Exception{ } String SQLStmt = "insert into 수납 ( 수납일자, 고객 ID, 수납금액 ) values(?,?,?)"; PreparedStatement st = con.preparestatement(sqlstmt); st.setstring(1, param1); st.setlong(2, param2); st.setlong(3, param3); st.execute(); st.close(); public static void updatereceipt( Connection con, String param1, long param2, long param3) throws Exception{ } String SQLStmt = "update 수납 set 수납금액 =? where 고객 ID =? and 수납일자 =?"; PreparedStatement st = con.preparestatement(sqlstmt); st.setlong(1, param3); st.setlong(2, param2); st.setstring(3, param1); st.execute(); st.close();

public static void execute(connection con, String input_date) throws Exception { String SQLStmt1 = "select 고객 ID, sum( 입금액 ) 입금액 " + "from 은행입금내역 " + "where to_char( 입금일시, 'yyyymmdd') = '" + input_date + "' " + "group by 고객 ID"; } String SQLStmt2 = "select 수납금액 " + "from 수납 " + "where 고객 ID =? " + "and 수납일자 = '" + input_date + "'"; PreparedStatement stmt1 = con.preparestatement(sqlstmt1); ResultSet rs1 = stmt1.executequery(); while(rs1.next()){ long 고객 ID = rs1.getlong(1); long 입금액 = rs1.getlong(2); PreparedStatement stmt2 = con.preparestatement(sqlstmt2); stmt2.setlong(1, 고객 ID); ResultSet rs2 = stmt2.executequery(); if(rs2.next()) updatereceipt(con, input_date, 고객 ID, 입금액 ); else insertreceipt(con, input_date, 고객 ID, 입금액 ); rs2.close(); stmt2.close(); } rs1.close(); stmt1.close();

User Call vs. Recursive Call Recursive Call 사용자정의함수 / 프로시저, 트리거내에서수행되는 SQL SQL 파싱과최적화과정에서 Dictionary 를조회하기위해내부적으로수행되는 SQL User Call 네트워크를경유해 DBMS 외부로부터인입되는 Call 성능부하 User Call > Recursive Call

데이터베이스 Call Parse Call : SQL 처리루틴생성요청 ( 캐싱돼있으면그것을사용 ) select 수납금액 from 수납 where 고객 ID = :1 and 수납일자 = '20100929 call count cpu elapsed disk query current rows ------- ------ ------ ---------- ------- -------- ---------- -------- Parse 100000 1.42 0.98 0 0 0 0 Execute Call : Execute 100000 2.18 2.21 0 0 0 0 Fetch 100000 2.32 SQL 3.08 실행요청 0 265658 0 50000 ------- ------ ------ ---------- ------- -------- ---------- -------- total 300000 5.93 6.29 0 265658 0 50000 Misses in library cache during parse: 1 Fetch Call : 데이터전송요청

데이터베이스 Call update 수납 set 수납금액 = :1 where 고객 ID = :2 and 수납일자 = :3 call count cpu elapsed disk query current rows ------- ------ ------ ---------- ------- -------- ---------- -------- Parse 50000 0.51 0.51 0 0 0 0 Execute 50000 3.18 2.91 0 106344 143448 50000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ ------ ---------- ------- -------- ---------- -------- total 100000 3.70 3.43 0 106344 143448 50000 Misses in library cache during parse: 1 insert into 수납 ( 수납일자, 고객 ID, 수납금액 ) values (:1, :2, :3) call count cpu elapsed disk query current rows ------- ------ ------ ---------- ------- -------- ---------- -------- Parse 50000 0.50 0.52 0 0 0 0 Execute 50000 1.93 2.22 0 50258 363256 50000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ ------ ---------- ------- -------- ---------- -------- total 100000 2.43 2.74 0 50258 363256 50000 Misses in library cache during parse: 1

update 수납 r set 수납금액 = (select sum( 입금액 ) from 은행입금내역 where 고객ID = r. 고객ID and 입금일시 between to_date('20100929', 'yyyymmdd') and to_date('20100930', 'yyyymmdd') - 1/24/60/60) where 수납일자 = '20100929 and exists (select 'x' from 은행입금내역 where 고객ID = r. 고객ID and 입금일시 between to_date('20100929', 'yyyymmdd') and to_date('20100930', 'yyyymmdd') - 1/24/60/60); insert into 수납 ( 고객ID, 수납일자, 수납금액 ) select 고객ID, '20100929', sum( 입금액 ) from 은행입금내역 r where 입금일시 between to_date('20100929', 'yyyymmdd') and to_date('20100930', 'yyyymmdd') - 1/24/60/60 and not exists ( select 'x' from 수납 where 고객ID = r. 고객ID and 수납일자 = '20100929') group by 고객ID; One-SQL 위주의처리

SELECT update 수납금액 r set FROM 수납금액 WHERE 고객ID = :B1 AND 수납일자 = '20100929 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 3 0 0 Execute 1 4.78 5.90 697 200547 102191 50000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 4.78 5.90 697 200550 102191 50000 insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 1 0 0 Execute 1 3.46 3.62 0 400211 113701 50000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 3.48 3.62 0 400212 113701 50000

merge into 수납 r using (select 고객 ID, sum( 입금액 ) 입금액 from 은행입금내역 where 입금일시 between to_date('20100929', 'yyyymmdd') and to_date('20100930', 'yyyymmdd') - 1/24/60/60 group by 고객ID) i on (r. 고객ID = i. 고객ID and r. 수납일자 = '20100929') when matched then update set r. 수납금액 = i. 입금액 One-SQL 로처리 (Merge 문활용 ) when not matched then insert ( 고객 ID, 수납일자, 수납금액 ) values (i. 고객 ID, '20100929', i. 입금액 );

SELECT merge into 수납금액 FROM r 수납 WHERE 고객ID = :B1 AND 수납일자 = '20100929 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.03 0 10 0 0 Execute 1 3.15 3.57 0 1941 219782 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 3.17 3.61 0 1951 219782 100000

한방 SQL 방 ( 放 ) à 데이터베이스 Call

declare type array_cust_id type array_amt type array_rid is table of 수납. 고객ID%type; is table of 수납. 수납금액 %type; is table of rowid; t_custid t_amt t_rid array_cust_id; array_amt; array_rid; l_fetch_size number default 1000; -- 1,000 건씩 Array 처리 cursor c is select i. 고객ID, i. 입금액, rowidtochar(r.rowid) as rid from (select 고객ID, sum( 입금액 ) 입금액 from 은행입금내역 where 입금일시 between to_date('20100929', 'yyyymmdd') and to_date('20100930', 'yyyymmdd') - 1/24/60/60 group by 고객ID) i, 수납 r where r. 고객ID(+) = i. 고객ID and r. 수납일자 (+) = '20100929'; begin open c; loop fetch c bulk collect into t_custid, t_amt, t_rid limit l_fetch_size; forall i in 1..t_custid.count update 수납 set 수납금액 = t_amt(i) where rowid = chartorowid( t_rid(i) ); Array Processing 활용 forall i in 1..t_custid.count insert into 수납 ( 고객ID, 수납일자, 수납금액 ) select t_custid(i), '20100929', t_amt(i) from dual where t_rid(i) is null; exit when c%notfound; end loop; close c; commit; end;

public static void insertdata(preparedstatement st, String param1,long param2,long param3 ) throws Exception { st.setstring(1, param1); st.setstring(2, param2); st.setstring(3, param3); st.addbatch(); } public void runbatch() throws Exception { PreparedStatement st = con.preparestatement( "insert into 수납 values(?,?,?)"); } while(rs.next()){ insertdata (st, 수납일자, 고객 ID, 수납금액 ); if(++rows%1000 == 0) st.executebatch(); }

select i. 고객 ID, i. 입금액, rowidtochar(r.rowid) as rid from call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 10 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 101 2.09 1.98 0 201634 0 100000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 103 2.09 1.99 0 201644 0 100000 update 수납 set 수납금액 = :b1 where rowid = chartorowid( :b2 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 2.81 2.85 0 50000 51310 50000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 2.81 2.85 0 50000 51310 50000 insert into 수납 select :b2, '20100929', :b1 from dual where :b3 is null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 4.59 4.71 0 1200 164525 50000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 4.59 4.71 0 1200 164525 50000

데이터베이스 Call 최소화튜닝 애플리케이션커서캐싱 à Parse Call 최소화 사용자정의함수 / 프로시저활용 à Parse Call 최소화 (Recursive Call 이므로 Call 부하자체도경감 ) Array Processing à Execute Call 최소화 부분범위처리원리활용 à Fetch Call 최소화 화면페이지처리 à Fetch Call 최소화 Array 단위 Fetch à Fetch Call 최소화 집합적사고를통해 One-SQL 로구현 à Parse Call, Execute Call, Fetch Call 최소화

DB 튜닝핵심원리 1. DB Buffer Cache è I/O 효율화 2. Library Cache è SQL 파싱부하해소 3. 작업요청 è 데이터베이스 Call 최소화

3 과목목차소개 1장. 아키텍처기반튜닝원리 1 절. 데이터베이스아키텍처 2 절. SQL 파싱부하 3 절. 데이터베이스 Call 과네트워크부하 4 절. 데이터베이스 I/O 원리 4장. 인덱스와조인 1 절. 인덱스기본원리 2 절. 인덱스튜닝 3 절. 조인기본원리 4 절. 고급조인기법 2장. Lock과트랜잭션동시성제어 1 절. Lock 2 절. 트랜잭션 3 절. 동시성제어 3장. 옵티마이저원리 1 절. 옵티마이저 2 절. 쿼리변환 5장. 고급 SQL 튜닝 1 절. 고급 SQL 활용 2 절. 소트튜닝 3 절. DML 튜닝 4 절. 파티션활용 5 절. 배치프로그램튜닝

예상문제 ü DB 성능을높이는방안중옳지않은것은? 1 모든 SQL 에바인드변수를사용해야한다. 2 OLTP 환경의모든 SQL 은반드시인덱스를 통해실행되도록튜닝해야한다. 3 사용자정의함수를절대사용해선안된다. 4 대용량데이터를처리할때는일반적으로 NL 조인보다해시조인이효과적이다.

ü 아래프로그램의문제점과성능개선방안을기술하시오. declare l_ 수납금액 number; begin for c in (select 고객ID, sum( 입금액 ) 입금액 loop begin from 은행입금내역 where to_char( 입금일시, 'yyyymmdd') = '20100929' group by 고객ID) select 수납금액 into l_ 수납금액 from 수납 where 고객ID = c. 고객ID and 수납일자 = 20100929; execute immediate ' update 수납 set 수납금액 = ' c. 입금액 ' where 고객ID = ' c. 고객ID ' and 수납일자 = 20100929'; exception when no_data_found then execute immediate end; 20 만건 10 만건 보관주기 : 일주일 보관주기 : 10 년 'insert into 수납 ( 고객 ID, 수납일자, 수납금액 ) values(' c. 고객 ID ', 20100929, ' c. 입금액 ')'; PK : 입금일시 + 고객 ID+ 은행코드 은행입금내역 # 입금일시 : DATE # 고객 ID : NUMBER # 은행코드 : VARCHAR2(2) * 입금액 : NUMBER 집계 수납 # 수납일자 : VARCHAR2(8) # 고객 ID : NUMBER * 수납금액 : NUMBER PK : 수납일자 + 고객 ID commit; end loop; end;

원리를알면답이보인다!!