<Insert Picture Here> Oracle Database 11gR2 의장점과 Real Application Testing 을활용한업그레이드베스트프랙티스 권희용 Principal Database Sales Consultant Database Technology, Technology Sales Consulting, Oracle Korea
Oracle Database 11g 2
Grid, High Availability, OLTP 3
Active Data Guard Physical Standby 의실시간질의 Primary Database Continuous Redo Shipment and Apply Concurrent Real-Time Query Physical Standby Database 리두적용과동시에 physical standby 에서 Read-only 질의가가능 디스크복제솔루션에비해네트웍량감소, 재해시스템활용가능, 비용절감 4
TPS Active Data Guard Standby Database 의실시간활용 Better Performance and Increased Scalability 3000 2500 2690 2000 1500 1000 1530 Before ADG After ADG 500 0 290 630 Read/Write Transactions Read/Only Transactions 5
Online Application Upgrade 대용량, 미션크리티컬애플리케이션을사용하는경우, 데이터베이스의특정애플리케이션콤포넌트에대한패치나업그레이드작업은보통수시간에걸친다운타임을필요로함 11gR2 에서는온라인중에애플리케이션을업그레이드할수있는 Edition-based Redefinition 이라는혁신적인기능을제공 Pre-upgrade 애플리케이션과 post-upgrade 애플리케이션을동시에사용할수있음 6
Online Application Upgrade 과정 1. 모든세션은 pre-upgrade 애플리케이션사용중 2. 업그레이드동안 pre-upgrade 애플리케이션과 postupgrade 애플리케이션모두사용가능 a. 기존세션은세션종료시까지 pre-upgrade 애플리케이션사용 b. 새로운세션은 post-upgrade 애플리케이션사용시작 3. Pre-upgrade 애플리케이션을더이상사용하는세션이없으면, 해당애플리케이션제거가능 4. Post-upgrade 애플리케이션만존재 7
Adaptive Cursor Sharing Business Requirement Optimizer 는초기 bind variable 값에따라 plan 을결정 향후 bind 값이변화되더라도동일한 execution plan 사용 Solution Bind 값이동일한 selectivity 를가질때 plan 공유 Bind 값이다른 selectivity 를가질때새로운 plan 생성 8
Adaptive Cursor Sharing SELECT FROM.. WHERE Job = :B1 Value of B1 = CLERK Ename Empno Job SMITH 6973 CLERK ALLEN WARD SCOTT CLARK 7499 7521 7788 7782 CLERK CLERK CLERK CLERK Ename SMITH ALLEN WARD Empno 6973 7499 7521 Job CLERK CLERK CLERK KING 8739 VP SELECT FROM.. WHERE Job = :B1 Value of B1 = VP SCOTT CLARK 7788 7782 CLERK CLERK Ename Empno Job KING 8739 VP 9
Business Intelligence and Data Warehousing 10
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 2003 2008 2009 Partition Scan Large Zero Size Unusable Index Index Lookup Small Usable Index 11
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)... 12500 Adams 12 12507 Blake 12 12666 King 12 12875 Smith 12... 32320 Jones 32 32407 Clark 32 32758 Hurd 32 32980 Phillips 32 12
Advanced OLTP Compression 모든유형의업무에적용 OLTP, DW 등 모든유형의데이터에적용 정형데이터 / 비정형데이터 성능향상 Physical read 의감소 백업, 재해복구센터등압축효과의전파 최대 4X 압축 13
OLTP Table Compression Overhead Free Space Uncompressed Compressed 신규레코드는압축되지않은상태로저장 블록사용률이 PCTFREE 에도달하면압축자동실행 신규레코드는다시압축하지않음 블록사용률이 PCTFREE 에다시도달하면압축자동실행 일반 OLTP INSERT 에대해평상시엔압축하지않음 일반 OLTP INSERT 성능유지 블록사용률이 PCTFREE 에도달하면자동으로압축 공간절약 14
Information Management 15
기존 LOB 대체 - Oracle SecureFiles SecureFiles 는비정형데이터에대한고성능입출력을보장하는 11g 의신개념데이터베이스저장형식 LOB 과유사한개념이지만새로설계 / 구현된구조 - 훨씬빠르고, 다양한신규기능들제공 투명한암호화기능 (Transparent Encryption) 자동압축기능 (Advanced Compression) 중복데이터제거기능 (De-duplication) 저장데이터에대한보안성과신뢰성및확장성보장 손쉬운마이그레이션 : 기존 LOB 인터페이스확장제공 Speed (MB/sec) 120 100 80 60 40 20 0 File Read Performance SecureFiles Linux Files LOBs 0.1 1 10 100 File Size (MB) 120 100 80 60 40 20 0 File Write Performance SecureFiles Linux Files LOBs 0.1 1 10 100 File Size (MB) 16
Security 17
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
Management 19
자동메모리관리의확장 O/S Memory SGA O/S Memory SGA SGA 와 PGA 메모리관리의통합 모든데이터베이스메모리에대한단일동적파라미터 MEMORY_TARGET 업무부하변화에따른자동조절 메모리활용률극대화 PGA PGA 20
점진적통계정보관리 Business Requirement 예를들어 bulk loading 한특정 partition 에대해통계정보를수집하는경우 global statistics 를갱신하기위해모든 partition 에대한 full scan 이필요 이작업은오랜시간과자원을필요로함 Solution 단지필요한 partition 들에대해서만통계정보수집 그외 partition 들에대해서는 scanning 작업없이테이블전체통계정보갱신 21
신규통계정보관리 Business Requirement 통계정보를수집하는순간해당정보를사용하게됨 => 예기치못한실행계획의변경이야기될수있음 이를방지하기위해중요 plan과통계정보를 freeze 시키고있음 Solution 통계정보를수집하되 pending 상태로남김 새로운통계정보에대해검증작업을수행 검증된통계정보를 public하게 open함 22
11g 의자동 SQL 튜닝과정 자동튜닝후이를검증하여자동적용하며, 실행계획변화에대한이력관리기능을제공 자체검증을통해 3 배이상의성능향상요소가있는경우자동적용 환경변화및자동튜닝에의한실행계획변화이력관리 AWR Workload 2 1 SQL 자동진단 2 SQL 자동튜닝 3 SQL 자동검증및적용 3 1 4 SQL 검증보고서 5 SQL 실행계획관리 자동 SQL 튜닝 4 5 SQL Plan Management DBA 23
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
Oracle Database 11g 25
데이터베이스업그레이드경로 11.1.0.6 26
Real Application Testing Database Replay client s Middle Tier PRODUCTION 캡쳐 Replay 에는 응용프로그램환경이필요없음 부하로드파일 재생 Replay Driver TEST 변경된테스트환경 27
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
RAT 활용가능버전 DB Replay SPA 9.2.0.8 9.2.0.7 (x) 9.x 10.1.0.5 10.1.0.x 10.2.0.2 11.1.0.6 10.2.0.2 11.1.0.6 11.2 11.2 10g (x) 10.2.0.2 11.1.0.6 11.1.0.6 10g R1 (x) 29
Real Application Testing(SQL Performance Analyzer) 를이용한 10.2 11g Upgrade Case Study 30
10.2 11g 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
10.2 11g 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
10.2 11g DB Upgrade (1) Capture Workload to STS 33
10.2 11g 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
10.2 11g 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
10.2 11g 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
10.2 11g DB Upgrade (3) Establish 10.2 and 11g Trials 37
10.2 11g 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
10.2 11g DB Upgrade (4) Compare Performance and Generate Report 2 3 1 4 39
10.2 11g DB Upgrade (4) Compare Performance and Generate Report 40
10.2 11g 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
10.2 11g DB Upgrade (5) Regression Remediation 5 * 42
10.2 11g DB Upgrade (5) Deploy Tuning and Change in Production 43
10.2 11g 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