Post on 23-Jul-2018
따라올 수 없는 성능 – Oracle 11g의 새 기능
류점수 책임 컨설턴트 (jumsu.ryu@oracle.com)
T h i l S l ti C ltiTechnical Solution Consulting
한국 오라클 주식회사
Agenda
Introduction<I t Pi t H >Manage ‘Change’
High Availability
<Insert Picture Here>
Automatic System ManagementData management New Technologies for PerformanceEtc
2Copyright© 2008, Oracle. All rights reserved.
30 Years of Active Data GuardReal Application Testing
Application Integration Architecture
Database Vault/Audit VaultInnovation
O l D t G dGrid Computing
Self-Managing DatabaseFlashback Database
First Internet ApplicationsFirst Comprehensive CRM Suite
Real Application ClustersOracle Data Guard
2000’s
I d t S ifi B i A li ti
Partitioning SupportBuilt in Java VM
pp
1980’s Full Applications Implementation Methodology
Data Warehousing OptimizationsMultimedia Support
Object Relational SupportIndustry-Specific Business Applications
1990’s1970’s
Cluster and MPP SupportDistributed SQL & Transaction Support
Parallel Operations
g pFirst Unix-Based Applications
3Copyright© 2008, Oracle. All rights reserved.Commercial SQL ImplementationPlatform Portability
Client/Server SupportMulti-version Read Consistency
pp
Oracle: The Database Market Leader
Overall database market share leaderLeader OLTPLeader Data WarehousinggLeader on LinuxLeader on UNIXLeader TPC-C OLTP PerformanceLeader TPC-H DW PerformanceLeader TPC Price/Performance
- Linux and Windows
4Copyright© 2008, Oracle. All rights reserved.
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
0 10 20 30 40 50 60
Biggest Pressure
Second Biggest Pressure
0 10 20 30 40 50 60
응답비율
Source : Gartner (December 2006)
5Copyright© 2008, Oracle. All rights reserved.
<Insert Picture Here> Manage ‘Change’Manage Change
* The New Paradigm ;
Oracle Database 11g Manage Change
** Real Application Testing
Th B t S l ti f M i Ch !The Best Solution for Managing Change !
6Copyright© 2008, Oracle. All rights reserved.
<Insert Picture Here>
Ch !!The Big Challenge ;
Change !!시스템변경의어려움
g복잡해져가는 Enterprise 환경과시스템환경변화그러나, 재현하기어려운실제Workload운영전에는알수없는 R l환경테스트“Oracle Database 11g will focus on helping
you preserve order amid change”운영전에는알수없는 Real 환경테스트높은변경비용
you preserve order amid change
7Copyright© 2008, Oracle. All rights reserved.
Lifecycle of Change Management
Test
Make Change Diagnose & Resolve Problems
Set Up TestEnvironments
Pro ision forProvision for Production
Identify Patches & Workarounds
Diagnose & Manage ProblemsWorkarounds
8Copyright© 2008, Oracle. All rights reserved.
What is Real Application Testing
실제운영 DB의부하를테스트환경에서재생함으로써, 변화된상황(테스트환경)을미리분석 진단하고보완하는솔루션변화된상황(테스트환경)을미리분석·진단하고보완하는솔루션
Enterprise Edition OptionEnterprise Edition Option
2 Feature- Database Replay (DB Replay)- SQL Performance Analyzer (SPA)
9Copyright© 2008, Oracle. All rights reserved.
Database Replay
Application 독립적인실제테스트환경구축
Replay Driver
Replay에는
clients
Middle Tier
응용프로그램환경이필요없음
부하로드
캡처 재생 변경된테스트환경
PRODUCTION TEST
파일
10Copyright© 2008, Oracle. All rights reserved.
Supported ChangesClientClient
…Client
Changes Unsupported
Middle Tier
Changes Supportedg pp•Database Upgrades, Patches
•Schema, Parameters
•RAC nodes InterconnectRecording of
•RAC nodes, Interconnect
•OS Platforms, OS Upgrades
•CPU, Memory Storage
External Client Requests
•Storage
•Etc.
11Copyright© 2008, Oracle. All rights reserved.
Database Replay Summary Report
12Copyright© 2008, Oracle. All rights reserved.
Why DB Replay?
To:From:
Complete workflowsPartial workflows
Production workloadsArtificial workloads
Complete workflowsPartial workflows
Days of developmentMonths of development
AutomatedManual intensive
150 10Low riskHigh risk150Days
10Days
Copyright© 2008, Oracle. All rights reserved. 13
SQL Performance Analyzer
Optimzer 통계및 Parameter 변화A li ti U d 테이블디자인변경 I d 변화 SQL튜닝등Application Upgrade : 테이블디자인변경, Index변화, SQL튜닝등OS, H/W, DB 환경변화
ClientClient
…Client
Production Test
……Capture SQL
Middle Tier Re-execute SQL Queries
Use SQL Tuning Oracle DB
gAdvisor to tune regression
Storage
14Copyright© 2008, Oracle. All rights reserved.
SPA Report
15Copyright© 2008, Oracle. All rights reserved.
<Insert Picture Here> High AvailabilityHigh Availability
* Active Data Guard
Leading Maximum AvailabilityLeading Maximum Availability
& used to Managing Change *& used to Managing Change
16Copyright© 2008, Oracle. All rights reserved.
What is Data Guard ?
N t k I/OData Guard just Transport Redo
Network I/OData Guard Apply Services
Control
Production DBMS Standby DBMSNetwork I/O
Control7X less
dataControl
Production DBMS Standby DBMSNetwork I/O
A l
7X less data
Files fil
OnlineLogs
Archive
Files fil
Standby Redo LogsArchive
volume*
27X fewer
Files fil
OnlineLogs
Archive
Applyvolume*
27X fewer Archive Logs
Flashback LogsUpdates
Archive LogsFlashback Logs
network I/Os*
Archive Logs
Flashback LogsUpdates
network I/Os*
DataFiles
SYSTEM
DataFilesSYSTEM
DataFiles
SYSTEM USERTEMPUNDO
USERTEMPUNDO
USERTEMPUNDO
17Copyright© 2008, Oracle. All rights reserved.
Why Called ‘Active’ Data Guard in 11g?
Physical Standby의실시간질의
Concurrent
Continuous Redo Shipment and Apply
Real-Time Query
Physical Standby Database
Primary Database
p pp y
Redo적용과동시에 Physical Standby에서 Read-Only Query가가능Redo 적용과동시에 Physical Standby 에서 Read-Only Query가가능- Query 결과의정합성보장-모든데이터형식적용가능하나, Logical Standby와같은 DML작업은불가
18Copyright© 2008, Oracle. All rights reserved.
Snapshot Standby를이용한테스트환경구축
Physical Standby ApplyPhysical Standby Snapshot Standby테스트를위해쓰기가능하게 OPhysical Standby Apply
Logs-테스트를위해쓰기가능하게 Open- alter database convert to snapshot standby;
Back out Changes
Open Database
Physical Standby로복귀용이-테스트자료자동제거- alter database convert to physical standby;
Snapshot Standby Perform
p y y;
Data 유실없는테스트유지p y
Testing기타
-본래의 DR기능외에도과부하가걸릴수있는
Continuous Redo Shipping Reporting 용도로사용할수있음.- Index구조변경, SQL 튜닝등Application테스트수행활용
19Copyright© 2008, Oracle. All rights reserved.
Use With Real Application Testing1. Convert to Snapshot Standby
2. Use Snapshot Standby
1 2
3. Convert to Physical Standby …Replay Driver
Replay Driver
Time
1 2Apply Redo
StandbyTime
Read/Write Read/Write Read/Write
3
Apply Archive Logs
StandbyStandby TimeStandby
20Copyright© 2008, Oracle. All rights reserved.
<Insert Picture Here> Automatic SystemAutomatic System Management
* A t ti f T i &* Automatic performance Tunning &
Database ManagementDatabase Management
*
21Copyright© 2008, Oracle. All rights reserved.
Self-Managing Database
Auto-TuningTuning
Advisory
Instrumentation
22Copyright© 2008, Oracle. All rights reserved.
Automatic Memory Tuning
UntunableUntunable Untunable
10g &1 1g 11g
Memory TargetPGA
FreePGA Target
PGA
Free SQL Areas
PGA
PGA Target
Buffer cache
SQL Areas
SGA Target Buffer cache
SQL Areas
Buffer cacheSGA Target
Buffer cache
Large poolLarge pool
Large pool
Shared pool
Java poolStreams pool
Shared pool
Java poolStreams pool
Shared pool
Java poolStreams poolStreams pool
Other SGA
Streams pool
Other SGA
OLTP BATCH BATCH
Streams pool
Other SGA
23Copyright© 2008, Oracle. All rights reserved.
Automatic SQL Tunning
PackagedApps
Customizable Apps
응답시간, 처리량, 처리주기등을기준으로과부하 SQL을자동선별Apps Apps
A t ti SQL T i
High-Load SQL
AWRNightly
과부하 SQL을자동선별
SQL Profile생성으로 SQL 자동튜닝
Automatic SQL Tuning Index, Stats,
Structure Analysis
Nightly
SQL Profiling
튜닝한 plan이성능향상되었는지검증하기위한자동테스트실행
Test Execute
SQL Profiles자동구현및실행
잘못된구조오래된통계 빈약한구조의 SQL
Advisor Report
Implement
Automatic
잘못된구조,오래된통계, 빈약한구조의 SQL자동리포팅
Well-tuned SQL
Advisor ReportManual
24Copyright© 2008, Oracle. All rights reserved.
ADDM for RACDatabase-Level ADDM RAC 전반에걸친진단
Self-Diagnostic Engine매시간자동기동
Cluster Level의분석- Global Cache Interconnect 이슈
L k M 폭주이슈
Instance-Level ADDMs- Lock Manager 폭주이슈- Global Resource 경합(IO bandwidth, hot block 등)- high-Load SQL in Global
저하문제등
Node 1 Node 2 Node 3
- Instance Response time 저하문제등
25Copyright© 2008, Oracle. All rights reserved.
Easy Partition Management
Access Advisor를통한 Partition AdviseParititioning기법권고
SQL Workload
PackagedApps
Customizable Apps
- Parititioning 기법권고(Range/Range Key, Interval/Interval Key,Hash/Hash Key)
T bl I d MVi 에대한 P titi d i
Index MV
Access Advisor
Partition
- Table, Index, MView에대한 Partition advise-전체 query와 DML workload에서 query 성능이증가하도록고려
Index, MV Analysis
PartitionAnalysis
Automatic Interval Partition 생성-첫번째 Insert 시
Index, MV Advice
Partition Advice
- Date나 Number 타입의일정한 Interval- Range 파티션테이블을 Interval로전환가능-하나의테이블에서 Range와 Interval 공존가능
Well-Designed Schema
26Copyright© 2008, Oracle. All rights reserved.
Automatic Diagnostic Repository
C
Automatic Diagnostic Repository
Auto Incident Creation
Critical Error
Alert DBA1 2
DBA
Auto Incident CreationFirst-Failure Capture Targeted Health Checks
Duplicate Bug?
Yes
No
Yes
EM Support Workbench:Apply Patch or WorkaroundRepair Advisors
EM Support Workbench:Package Incident &
Configuration Information
34
DBADBARepair Advisors
Reduce Time to Problem Resolution
27Copyright© 2008, Oracle. All rights reserved.
<Insert Picture Here> Data ManagementData Management
* P titi i* Partitioning
* Flashback Archive DataFlashback Archive Data
* Advanced Compress *p
28Copyright© 2008, Oracle. All rights reserved.
Partitioning Technologies
Core functionalityO l 8 R i i l b l i dOracle8 Range partitions, global range index
Oracle8i Hash and composite range-hash partitioning
O l 9 Li t titi iOracle9i List partitioning
Oracle9i R2 Composite range-list partitioningO l 10 Gl b l h h i dOracle 10g Global hash indexes
Oracle 10g R2 1M partitions per tablePartitioning by referencePartitioning by referenceVirtual column partitioningAutomatic interval partitioningN i i i iNew composite partitioning:
range-range, list-range, list-list, list-hashPartition Advisor
29Copyright© 2008, Oracle. All rights reserved.
Necessities for Historical Data ManagementData 이력관리의필요성증대-감사및규제준수의필수요구사항사및규제 수의필수 구사항
SOX, HIPAA, Internal Audit, etc
-업무효율성증대및다변화특정시점의데이터분석
과거와현재시점의정보를동시에분석
기존이력관시솔루션들의한계
- Oracle 10g Flashback Query
변경 감사Undo에저장된내용만활용가능
- 3rd Party Solution전용의저장공간및관리
변경이력정보테이블
Vendor전용의저장공간및관리
관리효율성저하, 보안문제발생
Trigger등의사용에따른성능저하
30Copyright© 2008, Oracle. All rights reserved.
Trigger 등의사용에따른성능저하
Error Correction with Flashback
DatabaseCorrect Errors at any LevelFlashback Database
Customer-특정시간대로데이터베이스를돌림
Flashback DropRec cle bin을이용 drop된테이블복구- Recycle bin을이용, drop된테이블복구
Flashback Table- Undo이용, 특정시간대로테이블을되돌림
Order
Flashback Query- Undo 이용, 과거특정시점의정보조회
Flashback Transaction트린잭션과모든수반된대치되는트랜잭션철회-트린잭션과모든수반된대치되는트랜잭션철회
Flashback Data Archive-트랜잭션의변경사항을별도로보관하여영구적으로이용
31Copyright© 2008, Oracle. All rights reserved.
영구적으로이용
Flashback Data Archive Total Recall Optionp
Select * from ordersAS OF
‘Midnight 31 Dec 2004’
설정된테이블의모든변경사항을
자동으로저장
ORDERS
‘Midnight 31-Dec-2004’ 자동 저장- Archive Data는수정불가-유지정책에따른과거 Data purge
ORDERSArchiveTables
Flashback Query를이용, 원하는시점의모든정보조회가능
User Tablespaces
Flashback Data Archive
Oracle Database사용례Ch ki /l hiOracle Database
CREATE FLASHBACK ARCHIVE fda1 TABLESPACE tbs1 RETENTION 5 YEAR;
- Change tracking/long term history- ILM- Auditing
RETENTION 5 YEAR;
ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE fda1;
- Compliance
32Copyright© 2008, Oracle. All rights reserved.
Compression for MainstreamAdvanced CompressAdvanced Compress
이전버전의 Compress-단지 bulk(direct-path) load 동안 table 압축 : D/W, ILM
모든Application에가능한 Table Compression모든Application에가능한 Table Compression-자유로운 update 동안에도가능한 Compress-종래의 Insert/update/delete를포함한모든 DML
종전보다 2배~3배높은압축율
Decompression overhead를피한압축데이터직접읽기가능
33Copyright© 2008, Oracle. All rights reserved.
<Insert Picture Here> New TechnologiesNew Technologies for Performance
* Secure File
* Server/Client Result Cache
* Optimized Fusion Cache Protocol
*
34Copyright© 2008, Oracle. All rights reserved.
Oracle SecureFiles데이터의통합된보안관리데이터의통합된보안관리
Database Files + 일반 file많은 은 뿐만아니라일반파일 등 들을갖는다-많은Application은 DB data뿐만아니라일반파일(CAD, images 등)들을갖는다
- DW application만큼좋은 OLTP
보다빠르고보다많이저장할수있는 LOBs다빠 다많이저장할수있- Transaprent encryption, compression 등지원-데이터베이스의보안성, 신뢰성, 확장성지원- LOB의보다쉬운MigrationLOB의보다쉬운Migration
Benefits-단일의보안모델-데이터의단일관리-높은성능- Compression : 보다효율적인공간사용으로비용감소-복제방지
35Copyright© 2008, Oracle. All rights reserved.
Server Result Cache
SGA에 Query 혹은부분 Query 결과 Caching
특징- Memory양이많을때더빠른 Query 성능-캐쉬된결과는 execution plan을공유하는다른 Query에서도공유가능-바인드변수, PL/SQL, RAC, PQ, Partitioning 등의기술과함께쓰임- End user에게완벽하게 Transparent한솔루션p
-다중 Control-문장 Level : hint 이용문장 이용
-테이블 Level : caching DDL 사용- Session Level : Parameter 사용 ( force/auto/manual)
- Cache size는 DBA에의해 setting가능- AS가 OCI client로 DB를사용할경우 Client Server간 Cache Fusion 같은기능
36Copyright© 2008, Oracle. All rights reserved.
OCI Consistent Client Cache서버와클라이언트사이의 Cache Fusion과같은효과
Application Server Consistent CachingCaching
Database
Query 결과를 Client에 Cache읽기위주테이블에대한성능향상읽기위주테이블에대한성능향상
- Network Round Trip 제거에따른응답속도의향상- Server의 CPU 사용율절약
Server Client간의데이터일관성유지- Result Set이변경되면 Cache는능동적으로갱신
37Copyright© 2008, Oracle. All rights reserved.
Optimized Cache Fusion Protocol
Database
차세대 Cache Fusion 프로토콜- Reader Optimized Fusion Protocol- Long Query Optimized Fusion Protocol
Update Optimized Fusion Protocol- Update Optimized Fusion Protocol
일반적인동작을핸들링하는데최적의프로토콜
Cluster환경에서의 50%-90%의 Cluster Messing 처리감소
38Copyright© 2008, Oracle. All rights reserved.
Summary
Oracle Database 11g는
여러분을손쉬운변경관리를지원합니다 : Real Application Testing
*여러분을
Oracle Database 11g의세계로안전하고도실용적인 DR Solution을제공합니다 : Active Data Guard
보다자동화되고,발전된성능관리로관리비용을절감해드립니다.g초대합니다.
보다자동화되고, 발전된성능관리로관리비용을절감해드립니다.
안전하고도환경변화에손쉽게대응할수있는 Data 관리를지원합니다
*지원합니다.
Partitioning, Total Recall, Advanced Compress
*최적의관리, Performance를유지할수있는여러기술들을제공합니다.
39Copyright© 2008, Oracle. All rights reserved.
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S
40Copyright© 2008, Oracle. All rights reserved.