Slide 1

Size: px

Start display at page:

Download "Slide 1"

동훈 손
5 years ago
Views:

1 <Insert Picture Here> Oracle Database 11gR2 의장점과 Real Application Testing 을활용한업그레이드베스트프랙티스 권희용 Principal Database Sales Consultant Database Technology, Technology Sales Consulting, Oracle Korea

2 Oracle Database 11g 2

3 Grid, High Availability, OLTP 3

4 Active Data Guard Physical Standby 의실시간질의 Primary Database Continuous Redo Shipment and Apply Concurrent Real-Time Query Physical Standby Database 리두적용과동시에 physical standby 에서 Read-only 질의가가능 디스크복제솔루션에비해네트웍량감소, 재해시스템활용가능, 비용절감 4

5 TPS Active Data Guard Standby Database 의실시간활용 Better Performance and Increased Scalability Before ADG After ADG Read/Write Transactions Read/Only Transactions 5

6 Online Application Upgrade 대용량, 미션크리티컬애플리케이션을사용하는경우, 데이터베이스의특정애플리케이션콤포넌트에대한패치나업그레이드작업은보통수시간에걸친다운타임을필요로함 11gR2 에서는온라인중에애플리케이션을업그레이드할수있는 Edition-based Redefinition 이라는혁신적인기능을제공 Pre-upgrade 애플리케이션과 post-upgrade 애플리케이션을동시에사용할수있음 6

7 Online Application Upgrade 과정 1. 모든세션은 pre-upgrade 애플리케이션사용중 2. 업그레이드동안 pre-upgrade 애플리케이션과 postupgrade 애플리케이션모두사용가능 a. 기존세션은세션종료시까지 pre-upgrade 애플리케이션사용 b. 새로운세션은 post-upgrade 애플리케이션사용시작 3. Pre-upgrade 애플리케이션을더이상사용하는세션이없으면, 해당애플리케이션제거가능 4. Post-upgrade 애플리케이션만존재 7

8 Adaptive Cursor Sharing Business Requirement Optimizer 는초기 bind variable 값에따라 plan 을결정 향후 bind 값이변화되더라도동일한 execution plan 사용 Solution Bind 값이동일한 selectivity 를가질때 plan 공유 Bind 값이다른 selectivity 를가질때새로운 plan 생성 8

9 Adaptive Cursor Sharing SELECT FROM.. WHERE Job = :B1 Value of B1 = CLERK Ename Empno Job SMITH 6973 CLERK ALLEN WARD SCOTT CLARK CLERK CLERK CLERK CLERK Ename SMITH ALLEN WARD Empno Job CLERK CLERK CLERK KING 8739 VP SELECT FROM.. WHERE Job = :B1 Value of B1 = VP SCOTT CLARK CLERK CLERK Ename Empno Job KING 8739 VP 9

10 Business Intelligence and Data Warehousing 10

11 Oracle Database 11g Release 2 Partitioning 기능향상 모든조합의 composite partitioning 8i : RANGE-HASH -> 9i,10g : + RANGE-LIST -> 11g : + RANGE-RANGE/LIST-RANGE/LIST-LIST/LIST-HASH Automated interval partitioning CREATE TABLE ORDERS PARTITION BY RANGE (time_id) INTERVAL('MONTH') Automated reference partitioning ORDERS TABLE (7 years) PARTITION BY REFERENCE (FK) Zero-Size Unusable Index / Index Partition Intelligent Multi Branch Execution Partition Scan Large Zero Size Unusable Index Index Lookup Small Usable Index 11

12 Oracle Database 11g Release 2 Partitioning 기능향상 Virtual Column Based Partitioning Virtual Column Virtual Column 을 partitioning key 로사용가능 CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null,... acc_branch number(2) generated always as ((( to_number(substr(to_char(acc_no),1,2 ) partition by list (acc_branch) Adams Blake King Smith Jones Clark Hurd Phillips 32 12

13 Advanced OLTP Compression 모든유형의업무에적용 OLTP, DW 등 모든유형의데이터에적용 정형데이터 / 비정형데이터 성능향상 Physical read 의감소 백업, 재해복구센터등압축효과의전파 최대 4X 압축 13

14 OLTP Table Compression Overhead Free Space Uncompressed Compressed 신규레코드는압축되지않은상태로저장 블록사용률이 PCTFREE 에도달하면압축자동실행 신규레코드는다시압축하지않음 블록사용률이 PCTFREE 에다시도달하면압축자동실행 일반 OLTP INSERT 에대해평상시엔압축하지않음 일반 OLTP INSERT 성능유지 블록사용률이 PCTFREE 에도달하면자동으로압축 공간절약 14

15 Information Management 15

16 기존 LOB 대체 - Oracle SecureFiles SecureFiles 는비정형데이터에대한고성능입출력을보장하는 11g 의신개념데이터베이스저장형식 LOB 과유사한개념이지만새로설계 / 구현된구조 - 훨씬빠르고, 다양한신규기능들제공 투명한암호화기능 (Transparent Encryption) 자동압축기능 (Advanced Compression) 중복데이터제거기능 (De-duplication) 저장데이터에대한보안성과신뢰성및확장성보장 손쉬운마이그레이션 : 기존 LOB 인터페이스확장제공 Speed (MB/sec) File Read Performance SecureFiles Linux Files LOBs File Size (MB) File Write Performance SecureFiles Linux Files LOBs File Size (MB) 16

17 Security 17

18 Total Recall Flashback Data Archive 투명한데이터변경이력관리 전용테이블스페이스에변경이력정보저장및관리 정책기반의이력정보관리 압축된형식으로저장 이력정보에대한변조금지 내부커널최적화를통한캡쳐부하최소화 유연한이력데이터조회 필요한때에원하는시점의모든정보조회가능 AS OF SQL 명령어 DDL 이가해져도이전데이터유지 Select * from orders AS OF Midnight 31-Dec-2004 ORDERS User Table 자동삭제 X Archive Tables Flashback Data Archive Oracle Database CREATE FLASHBACK ARCHIVE fda1 TABLESPACE tbs1 RETENTION 5 YEAR; ALTER TABLE ORDERS FLASHBACK ARCHIVE fda1; 18

19 Management 19

20 자동메모리관리의확장 O/S Memory SGA O/S Memory SGA SGA 와 PGA 메모리관리의통합 모든데이터베이스메모리에대한단일동적파라미터 MEMORY_TARGET 업무부하변화에따른자동조절 메모리활용률극대화 PGA PGA 20

21 점진적통계정보관리 Business Requirement 예를들어 bulk loading 한특정 partition 에대해통계정보를수집하는경우 global statistics 를갱신하기위해모든 partition 에대한 full scan 이필요 이작업은오랜시간과자원을필요로함 Solution 단지필요한 partition 들에대해서만통계정보수집 그외 partition 들에대해서는 scanning 작업없이테이블전체통계정보갱신 21

22 신규통계정보관리 Business Requirement 통계정보를수집하는순간해당정보를사용하게됨 => 예기치못한실행계획의변경이야기될수있음 이를방지하기위해중요 plan과통계정보를 freeze 시키고있음 Solution 통계정보를수집하되 pending 상태로남김 새로운통계정보에대해검증작업을수행 검증된통계정보를 public하게 open함 22

23 11g 의자동 SQL 튜닝과정 자동튜닝후이를검증하여자동적용하며, 실행계획변화에대한이력관리기능을제공 자체검증을통해 3 배이상의성능향상요소가있는경우자동적용 환경변화및자동튜닝에의한실행계획변화이력관리 AWR Workload 2 1 SQL 자동진단 2 SQL 자동튜닝 3 SQL 자동검증및적용 SQL 검증보고서 5 SQL 실행계획관리 자동 SQL 튜닝 4 5 SQL Plan Management DBA 23

24 SQL Plan Management Plan Baseline 에없는 plan 은검증되기전엔사용되지않음 DBA 가언제든지검증할수있음 GB NL NL Statement log Plan history Plan baseline GB HJ DBA Plan 검증작업 Optimizer 는새로운 plan 이기존 plan 보다같거나좋은지점검 HJ 기존 plan 보다좋지않으면 plan history 에만남게되고 unaccepted 상태 NL GB NL NL Statement log Plan history Plan baseline GB HJ HJ GB NL NL 기존 plan 보다같거나좋으면 plan baseline 에추가 24

25 Oracle Database 11g 25

26 데이터베이스업그레이드경로

27 Real Application Testing Database Replay client s Middle Tier PRODUCTION 캡쳐 Replay 에는 응용프로그램환경이필요없음 부하로드파일 재생 Replay Driver TEST 변경된테스트환경 27

28 Real Application Testing SQL Performance Analyzer PRODUCTION Clients Mid-Tier TEST Storage Storage Capture SQL (STS) Transport STS Execute SQL Pre-change Execute SQL Post-change Compare Perf 28

29 RAT 활용가능버전 DB Replay SPA (x) 9.x x g (x) g R1 (x) 29

30 Real Application Testing(SQL Performance Analyzer) 를이용한 g Upgrade Case Study 30

31 g DB Upgrade Production Database Test Database Prod DB (10.2) Upgrade Test DB (11g) Upgrade Test DB (10.2) 1. Capture SQL workload to STS Send SQL for Remote Execution Collect execution stats 2. Transport STS 11g SPA System 3. Establish 10.2 and 11g Trials 4. Compare performance and generate SPA report 5. Deploy Tuning and Change to Prod 31

32 g DB Upgrade (1) Capture Workload to STS Steps 1: Capture workload into STS through Incremental Capture Workload into STS: Preferred method Other sources for STS also possible: Top SQL in AWR / AWR Baseline 32

33 g DB Upgrade (1) Capture Workload to STS 33

34 g DB Upgrade 1. Capture SQL workload to STS 2. Transport STS 3. Establish 10.2 and 11g Trials 4. Compare performance and Generate SPA report 5. Deploy Tuning and Change to Prod 34

35 g DB Upgrade (2) Transport STS Step 2: Copy STS to SPA system Setup Test DB (Copy of Prod) 10.2 Upgrade Test DB from 10.2 to 11g 35

36 g DB Upgrade 1. Capture SQL workload to STS 2. Transport STS 3. Establish 10.2 and 11g Trials 4. Compare performance and Generate SPA report 5. Deploy Tuning and Change to Prod 36

37 g DB Upgrade (3) Establish 10.2 and 11g Trials 37

38 g DB Upgrade 1. Capture SQL workload to STS 2. Transport STS 3. Establish 10.2 and 11g Trials 4. Compare Performance, Generate SPA Report 5. Tune Regressions, Deploy Tuning and Change to Prod 38

39 g DB Upgrade (4) Compare Performance and Generate Report

40 g DB Upgrade (4) Compare Performance and Generate Report 40

41 g DB Upgrade 1. Capture SQL workload to STS 2. Transport STS 3. Establish 10.2 and 11g Trials 4. Compare performance and Generate SPA report 5. Tune Regressions, Deploy Tuning and Change to Prod 41

42 g DB Upgrade (5) Regression Remediation 5 * 42

43 g DB Upgrade (5) Deploy Tuning and Change in Production 43

44 g DB Upgrade 1. Capture SQL workload to STS 2. Transport STS 3. Establish 10.2 and 11g Trials 4. Compare performance and Generate SPA report 5. Tune Regressions, Deploy Tuning and Change to Prod 44

45 45

Slide 1

Oracle 11g R2 Overview & New Feature 이진호 Principal Database Sales Consultant Database Technology, Technology Sales Consulting, Oracle Korea 지속적인기술혁신 - 90 년대중반 - OLTP/DW 중심 - 데이터 /APP 분리 - GUI - Tool 전성시대