따라올수없는성능 Oracle 11g 의새기능 류점수책임컨설턴트 (jumsu.ryu@oracle.com) Technical Solution Consulting 한국오라클주식회사
Agenda Introduction Manage Change High Availability Automatic System Management Data management New Technologies for Performance Etc <Insert Picture Here> 2
Real Application Testing 30 Years of Active Data Guard Innovation Database Vault/Audit Vault Application Integration Architecture 1970 s Flashback Database Self-Managing Database Grid Computing Oracle Data Guard Real Application Clusters First Comprehensive CRM Suite First Internet Applications Built in Java VM Partitioning Support 1980 s Full Applications Implementation Methodology Industry-Specific Business Applications Object Relational Support Multimedia Support Data Warehousing Optimizations First Unix-Based Applications Parallel Operations Distributed SQL & Transaction Support Cluster and MPP Support Multi-version Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation 1990 s 2000 s 3
Oracle: The Database Market Leader Overall database market share leader Leader OLTP Leader Data Warehousing Leader on Linux Leader on UNIX Leader TPC-C OLTP Performance Leader TPC-H DW Performance Leader TPC Price/Performance - Linux and Windows 4
Top Customer s Concerns Increasing Rate of Change and Pressure to Move Faster Rate of Change Cost 24/7 Availability Complexity Business Value Security Compliance Alignment Process integration Outsource Biggest Pressure Second Biggest Pressure 0 10 20 30 40 50 60 Source : Gartner (December 2006) 응답비율 5
<Insert Picture Here> Manage Change * The New Paradigm ; Oracle Database 11g Manage Change * Real Application Testing The Best Solution for Managing Change! * 6
<Insert Picture Here> The Big Challenge ; Change!! 시스템변경의어려움 복잡해져가는 Enterprise 환경과시스템환경변화 Oracle Database 11g will focus on helping you preserve order amid change 그러나, 재현하기어려운실제 Workload 운영전에는알수없는 Real 환경테스트 높은변경비용 7
Lifecycle of Change Management Test Make Change Diagnose & Resolve Problems Set Up Test Environments Provision for Production Identify Patches & Workarounds Diagnose & Manage Problems 8
What is Real Application Testing 실제운영 DB 의부하를테스트환경에서재생함으로써, 변화된상황 ( 테스트환경 ) 을미리분석 진단하고보완하는솔루션 Enterprise Edition Option 2 Feature - Database Replay (DB Replay) - SQL Performance Analyzer (SPA) 9
Database Replay Application 독립적인실제테스트환경구축 Replay Driver clients Middle Tier Replay에는응용프로그램환경이필요없음 캡처재생변경된 부하로드파일 테스트환경 PRODUCTION TEST 10
Supported Changes Client Client Client Changes Unsupported Middle Tier Changes Supported Database Upgrades, Patches Schema, Parameters RAC nodes, Interconnect OS Platforms, OS Upgrades CPU, Memory Storage Etc. Storage Recording of External Client Requests 11
Database Replay Summary Report 12
Why DB Replay? From: Artificial workloads Partial workflows Months of development Manual intensive To: Production workloads Complete workflows Days of development Automated 150 High risk Low risk 10 Days Days 13
SQL Performance Analyzer Optimzer 통계및 Parameter 변화 Application Upgrade : 테이블디자인변경, Index 변화, SQL 튜닝등 OS, H/W, DB 환경변화 Client Client Client Production Test Middle Tier Re-execute SQL Queries Oracle DB Capture SQL Use SQL Tuning Advisor to tune regression Storage 14
SPA Report 15
<Insert Picture Here> High Availability * Active Data Guard Leading Maximum Availability & used to Managing Change * 16
What is Data Guard? Data Guard Apply just Transport ServicesRedo Network I/O Updates Production DBMS Control Files fil Online Logs Archive Logs Flashback Logs Data Files SYSTEM USER TEMP UNDO 7X less data volume* 27X fewer network I/Os* Standby DBMS Control Files A fil Standby Redo Logs Archive Logs Apply Flashback Logs Data Files SYSTEM USER TEMP UNDO 17
Why Called Active Data Guard in 11g? Physical Standby 의실시간질의 Continuous Redo Shipment and Apply ppy Concurrent Real-Time Query Primary Database Physical Standby Database Redo 적용과동시에 Physical Standby 에서 Read-Only Query 가가능 -Query 결과의정합성보장 - 모든데이터형식적용가능하나, Logical Standby와같은DML작업은불가 18
Snapshot Standby 를이용한테스트환경구축 Physical Standby Apply Logs Physical Standby Snapshot Standby - 테스트를위해쓰기가능하게 Open - alter database convert to snapshot standby; Open Database Back out Changes Physical Standby 로복귀용이 - 테스트자료자동제거 - alter database convert to physical standby; Snapshot Standby Perform Testing Data 유실없는테스트유지 기타 - 본래의 DR 기능외에도과부하가걸릴수있는 Continuous Redo Shipping Reporting 용도로사용할수있음. - Index 구조변경, SQL 튜닝등 Application 테스트 수행활용 19
Use With Real Application Testing 1. Convert to Snapshot Standby 2. Use Snapshot Standby 3. Convert to Physical Standby Replay Driver Replay Driver 1 2 Apply Redo Standby Read/Write Read/Write Read/Write Time 3 Apply Archive Logs Standby Standby Standby Time 20
<Insert Picture Here> Automatic System Management * Automatic ti performance Tunning & Database Management * 21
Self-Managing Database Auto-Tuning Advisory Instrumentation 22
Automatic Memory Tuning 10g &1 1g 11g Untunable PGA Untunable PGA Memory Target Untunable PGA Free PGA Target Free PGA Target SQL Areas SQL Areas Buffer cache Large pool SGA Target SQL Areas Buffer cache Large pool SGA Target Buffer cache Large pool Shared pool Shared pool Shared pool Java pool Streams pool Java pool Streams pool Java pool Streams pool Other SGA Other SGA Other SGA OLTP BATCH BATCH 23
Automatic SQL Tunning Nightly Packaged Apps High-Load SQL AWR Customizable Apps Automatic ti SQL Tuning SQL Profiling Index, Stats, Structure Analysis 응답시간, 처리량, 처리주기등을기준으로과부하 SQL 을자동선별 SQL Profile생성으로 SQL 자동튜닝 튜닝한 plan이성능향상되었는지검증하기위한자동테스트실행 SQL Profiles Test Execute Implement 자동구현및실행 잘못된구조, 오래된통계, 빈약한구조의 SQL 자동리포팅 Automatic Well-tuned SQL Advisor Report Manual 24
ADDM for RAC Database-Level ADDM RAC 전반에걸친진단 Self-Diagnostic Engine 매시간자동기동 Instance-Level ADDMs Cluster Level 의분석 - Global Cache Interconnect 이슈 - Lock kmanager 폭주이슈 - Global Resource 경합 (IO bandwidth, hot block 등 ) - high-load SQL in Global - Instance Response time 저하문제등 Node 1 Node 2 Node 3 25
Easy Partition Management Packaged Apps Index, MV Analysis Index, MV Advice Customizable Apps SQL Workload Access Advisor Partition Analysis Partition Advice Well-Designed Schema Access Advisor 를통한 Partition Advise - Parititioning 기법권고 (Range/Range Key, Interval/Interval Key, Hash/Hash Key) - Tbl Table, Index, MView 에대한 Partition advise - 전체 query와 DML workload에서 query 성능이증가하도록고려 Automatic Interval Partition 생성 - 첫번째 Insert 시 -Date나Number 타입의일정한 Interval - Range 파티션테이블을 Interval로전환가능 - 하나의테이블에서 Range와 Interval 공존가능 26
Automatic Diagnostic Repository Critical Error Automatic Diagnostic Repository DBA 1 2 Auto Incident Creation Alert DBA First-Failure Capture Targeted Health Checks No Duplicate Bug? DBA 4 EM Support Workbench: Package Incident & Configuration Information Repair Advisors DBA 3 Yes EM Support Workbench: Apply Patch or Workaround Repair Advisors Reduce Time to Problem Resolution 27
<Insert Picture Here> Data Management * Partitioning i * Flashback Archive Data * Advanced Compress * 28
Partitioning Technologies Core functionality Oracle8 Range partitions, i global l range index Oracle8i Oracle9i List partitioning i Oracle9i R2 Hash and composite range-hash partitioning Composite range-list partitioning Oracle 10g Global l hash h indexes Oracle 10g R2 1M partitions per table Partitioning by reference Virtual column partitioning Automatic interval partitioning New composite partitioning: i i range-range, list-range, list-list, list-hash Partition Advisor 29
Necessities for Historical Data Management Data 이력관리의필요성증대 - 감사및규제준수의필수요구사항 SOX, HIPAA, Internal Audit, etc - 업무효율성증대및다변화 특정시점의데이터분석과거와현재시점의정보를동시에분석 기존이력관시솔루션들의한계 - Oracle 10g Flashback Query Undo 에저장된내용만활용가능 -3 rd Party Solution Vendor 전용의저장공간및관리 변경 감사 관리효율성저하, 보안문제발생 Trigger 등의사용에따른성능저하 테이블 변경이력정보 30
Error Correction with Flashback Customer Order Database Correct Errors at any Level Flashback Database - 특정시간대로데이터베이스를돌림 Flashback Drop - Recycle cle bin 을이용, drop 된테이블복구 Flashback Table - Undo 이용, 특정시간대로테이블을되돌림 Flashback Query - Undo 이용, 과거특정시점의정보조회 Flashback Transaction - 트린잭션과모든수반된대치되는트랜잭션철회 Flashback Data Archive - 트랜잭션의변경사항을별도로보관하여영구적으로이용 31
Flashback Data Archive Total Recall Option Select * from orders AS OF Midnight 31 Dec 2004 ORDERS Midnight 31-Dec-2004 자동으로저장 Archive Tables 설정된테이블의모든변경사항을 -Archive Data는수정불가 - 유지정책에따른과거 Data purge Flashback Query를이용, 원하는시점의모든정보조회가능 User Tablespaces Oracle Database Flashback Data Archive CREATE FLASHBACK ARCHIVE fda1 TABLESPACE tbs1 RETENTION 5 YEAR; 사용례 - Change tracking/long term history -ILM - Auditing - Compliance ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE fda1; 32
Compression for Mainstream Advanced Compress 이전버전의 Compress - 단지 bulk(direct-path) load 동안 table 압축 : D/W, ILM 모든 Application 에가능한 Table Compression - 자유로운 update 동안에도가능한 Compress - 종래의 Insert/update/delete를포함한모든 DML 종전보다 2 배 ~3 배높은압축율 Decompression overhead를피한압축데이터직접읽기가능 33
<Insert Picture Here> New Technologies for Performance * Secure File * Server/Client Result Cache * Optimized Fusion Cache Protocol * 34
Oracle SecureFiles 데이터의통합된보안관리 Database Files + 일반 file - 많은 Application 은 DB data 뿐만아니라일반파일 (CAD, images 등 ) 들을갖는다 - DW application 만큼좋은 OLTP 보다빠르고보다많이저장할수있는 LOBs - Transaprent encryption, compression 등지원 - 데이터베이스의보안성, 신뢰성, 확장성지원 - LOB 의보다쉬운 Migration Benefits - 단일의보안모델 - 데이터의단일관리 - 높은성능 - Compression : 보다효율적인공간사용으로비용감소 - 복제방지 35
Server Result Cache SGA 에 Query 혹은부분 Query 결과 Caching 특징 -Memory 양이많을때더빠른 Query 성능 - 캐쉬된결과는 execution plan을공유하는다른 Query에서도공유가능 - 바인드변수, PL/SQL, RAC, PQ, Partitioning 등의기술과함께쓰임 - End user에게완벽하게 Transparent 한솔루션 - 다중 Control - 문장 Level : hint 이용 - 테이블 Level : caching DDL 사용 - Session Level : Parameter 사용 ( force/auto/manual) - Cache size는 DBA에의해setting가능 -AS가 OCI client로 DB를사용할경우 Client Server간 Cache Fusion 같은기능 36
OCI Consistent Client Cache 서버와클라이언트사이의 Cache Fusion 과같은효과 Application Server Consistent Caching Database Query 결과를 Client 에 Cache 읽기위주테이블에대한성능향상 - Network Round Trip 제거에따른응답속도의향상 -Server의 CPU 사용율절약 Server Client 간의데이터일관성유지 - Result Set 이변경되면 Cache 는능동적으로갱신 37
Optimized Cache Fusion Protocol Database 차세대 Cache Fusion 프로토콜 - Reader Optimized Fusion Protocol - Long Query Optimized Fusion Protocol - Update Optimized Fusion Protocol 일반적인동작을핸들링하는데최적의프로토콜 Cluster 환경에서의 50%-90% 의 Cluster Messing 처리감소 38
Summary Oracle Database 11g 는 손쉬운변경관리를지원합니다 : Real Application Testing 여러분을 안전하고도실용적인 DR Solution 을제공합니다 : Active Data Guard g 보다자동화되고, 발전된성능관리로관리비용을절감해드립니다. Oracle Database 11g 의세계로 * 초대합니다. 안전하고도환경변화에손쉽게대응할수있는 Data 관리를 지원합니다. Partitioning, Total Recall, Advanced Compress 최적의관리, Performance를유지할수있는여러기술들을제공합니다. * 39
Q U E S T I O N S A N S W E R S 40