Docstoc

Oracle Database 10g on Windows Architecture for Performance - PowerPoint

Document Sample
Oracle Database 10g on Windows Architecture for Performance - PowerPoint Powered By Docstoc
					Session id: 40171

Oracle Database 10g on Windows: Architecture for Performance
David Colello Consulting Member of Technical Staff Oracle Corporation Steve Fluge Senior Technical Consultant Intel Corporation

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Database Architecture Agenda
 Oracle Database 10g on Windows architecture  64-bit development landscape  Migration & compatibility (32-bit to 64-bit)  Future directions & conclusion  Customer case study: Intel

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Thread Model
 Unlike UNIX ports of Oracle Database 10g, Oracle Database 10g on Windows is implemented as a single operating system process.  Typical “processes” such as PMON and LGWR have been converted to native Windows threads running in the single large Oracle process.  One process exists per instance on a Windows machine.
Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Thread Model
Oracle process SGA 3GB or 8TB total
SGA contains db buffers, log buffers shared pool, other memory allocations
Each thread consists of PGA, stack, other memory allocations

Background and foreground threads

Code
Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation..

*Other names and brands may be claimed as the property of others.

Architecture: Thread Model
 Internally, relatively few code changes are required to implement the thread model. The SGA and PGAs are allocated differently and threads are used instead of processes, but very little else needs to change.  As a result, it’s easy to maintain and port the database while at the same time preserving it’s inherent stability, features, and performance.
Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Thread Model
 Thread model benefits:
– – – –
–

Faster context switches Decreased memory usage Quicker connection times Perceived as a more “native” implementation since it uses threads versus processes No changes required for client applications

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: File I/O
 Full support for 64-bit file I/O enables very large files to be used by Oracle Database 10g on Windows
– –

There are no 2GB or 4GB limitations on database file sizes. The maximum size for an entire database on Windows is 4 petabytes (64K files x 16K bytes/block x 4M blocks/file).

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: File I/O
 Async file I/O is built into Windows and Oracle Database 10g supports async I/O to all types of files, assuming that the portable code layers utilize async I/O.  No limits on the number of files in a single database except those already imposed by Oracle portable code (around 64K files).  Raw files are fully supported. They are better performing than file system files, but are harder to manage.
Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Tuning
 Both CPU affinities and thread priorities can be set and manipulated via registry parameters on a thread-by-thread basis.  With the introduction of Windows-based NUMA platforms, Oracle Database 10g has been enhanced to recognize NUMA systems and adjust memory and scheduling operations accordingly.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Tuning
 Single production instances of Oracle on Windows support more than 2000 concurrent connections.  With the use of technologies such as multiplexing, connection pooling, and clusters, the number of users supported by a single physical database grows much higher.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Tuning
 New for Oracle Database 10g: – Fiber model support
 An extension of the thread support already in place since Oracle7. Users may now run the database in fiber mode, which employs Oraclescheduled fibers instead of O/S scheduled threads.  For CPU intensive apps, this will provide a performance boost and reduce CPU utilization.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Architecture: Tuning
 New for Oracle Database 10g: – Large Page support
 Using new APIs in Windows Server 2003, Oracle can now allocate database buffers in fewer larger memory pages. This reduces the amount of CPU overhead required to access large amounts of RAM.  For instances with large memory requirements, large page support on both 32 and 64-bit Windows can improve performance.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Database Architecture Agenda
 Oracle Database 10g on Windows architecture  64-bit development landscape  Migration & compatibility (32-bit to 64-bit)  Future directions & conclusion  Customer case study: Intel

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

64-bit development details
 Working with Intel
–

Company Relations
 Mutual alliances teams  Regular Executive Meetings

–

Development Cooperation
    Intel engineering team on-site at Oracle Optimizing support on IA specifics Early access to new Processors/Systems Early access to Intel’s SW tools (Compilers & Tuning Tools)

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

64-bit development details
 Working with Microsoft
– –

–

Oracle is a beta site for Microsoft O/S releases Frequent communication occurs at many levels from executives to relationship teams to developers. Conference calls, briefings, etc. occur as needed between appropriate groups

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

64-bit development details
 64-bit database architecture based on 32-bit database
–
–

Allows database to be built upon a stable, proven base with few architecture changes On Windows, thread architecture is retained

 Oracle not bound by specific memory amount or number of processors
–

Will support as much as the OS can support

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

64-bit development details
 Easy to port code base to 64-bit (change a few #defines and recompile)  Simultaneous porting to three different 64-bit Itanium platforms == knowledge sharing  Most time is spent testing and on performance work  All products in RDBMS bundle have been moved to native 64-bit

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

64-bit development details
 Data access methods supported natively on 64-bit (initially):
– – –
– –

OLE DB ODBC Oracle Call Interface (OCI) JDBC SQLJ

 When 64-bit .NET Framework and interfaces are released, then ODBC.NET, OLE DB.NET, and ODP.NET will be supported
Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Itanium benefits for Oracle
 Larger cache size
– – – –
–

Itanium L3 cache (on-die) is 3 MB Madison L3 cache up to 6 MB Less cache latency (fewer clock cycles needed) Oracle benefits greatly with larger caches closer to the processor Allows for “bigger” systems

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Itanium benefits for Oracle
 Better Parallelism
– – –

Better Instruction level parallelism More registers (328, 128 GP, 128 FP) Improved Scalability of System Bus allowing for bigger systems Predication removes delays caused by mispredicted branches

 Improved Micro Architecture
–

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Itanium benefits for Oracle
 Large memory addressability
– – – –

No 4 GB linear memory limitation for SGA Up to 8 TB of memory supported Page size can be up to 4GB Capacity, concurrent users, and performance of Oracle database increases dramatically with more memory

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Oracle 64-bit Performance Enhancements
 Intel Electron compiler
–

Provides profile-guided optimization (PGO)
    Better scheduling More parallelism through larger instruction groups Lower number of branch mispredictions Better use of instruction paging and cache memory

–

Expect 15%-25% performance improvement from PGO

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Oracle10g Support on 64-bit
 Itanium is supported by
– –

64-bit Oracle DB server 64-bit Oracle DB client 32-bit Oracle Database server 32-bit Oracle Database client

 Itanium is not supported by
– –

 Machines with 32-bit clients are interoperable with 64-bit Oracle on Itanium

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Database Architecture Agenda
 Oracle Database 10g on Windows architecture  64-bit development landscape  Migration & compatibility (32-bit to 64-bit)  Future directions & conclusion  Customer case study: Intel

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Migration
 Upgrade process (32-bit to 64-bit)
–

32-bit data files are compatible with 64-bit DB

 No need to recreate the database  A full export and import is not required  For end-user applications, migrating to Oracle on Itanium is transparent
–

No changes required to 32-bit client applications

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Migration
 Simple migration steps:
– –
– – – –

Shutdown database on 32-bit machine Copy database files to 64-bit machine once 64bit Oracle Database 10g has been installed Re-create control file (a single SQL statement) Startup database Re-compile PL/SQL (a single SQL script) Done!

 Or use the Database Upgrade Assistant
Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Compatibility
 Independent software vendors (ISVs) porting to 64-bit Oracle
–
–

No Oracle-specific code changes required when moving from 32-bit Just make 64-bit-specific changes and recompile

 Client programs completely unaffected by new back-end. No changes, re-compiles or relinks needed.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Agenda
 Oracle Database 10g on Windows architecture  64-bit development landscape  Migration & compatibility (32-bit to 64-bit)  Future directions & conclusion  Customer case study: Intel

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Future directions
 Further Windows exploitation where it makes sense  Architectural changes always being explored for capacity/performance improvements. Some examples are fibers, completion ports, and NUMA-awareness.  64-bit scale-up as hardware permits

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Useful links
 Oracle on Windows home page
–

http://www.oracle.com/windows

 Oracle Technology Network’s Windows Development Center
–

http://otn.oracle.com/windows http://www.oracle.com/go/?&Src=1407080&Act=6

 Oracle Data Provider for .NET download
–

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Conclusion
 Oracle is committed to Windows  64-bit Windows Server 2003 on Itanium enables excellent performance and scalability for Oracle Database 10g

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Database Architecture Agenda
 Oracle Database 10g on Windows architecture  64-bit development landscape  Migration & compatibility (32-bit to 64-bit)  Future directions & conclusion  Customer case study: Intel

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Customer case study: Intel Agenda
     Background Configuration Test strategy Key indicators and results Conclusion

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Background
 Business needs driving migration to Oracle9i and Windows Server 2003 on IA64
–

Affordable Scalability
 High performance computing at right price

–

Prepares for next generation Grid Computing
 Can be part of a cluster of Itanium2 servers to consolidate databases

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Background: Application
 Call Center application
–

24x7 availability requirement
 Call centers located worldwide in follow the sun model  High availability demand (5 9’s)

–

Scalability/Performance
 Customer base growing 30% annually  Need to handle high volume surge traffic

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Background: Application
 Call Center application (cont.)
–

Current usage
 Nominal load is 400 concurrent users.  Peak loads can reach 500+ concurrent users.  Ancillary operations such as email, fax and batch data load processing take up to 20% of CPU processing.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Customer case study: Intel Agenda
 Configuration

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Configuration: 32-bit Architecture
 Hardware
–

Single database server with an application server
 4 PIII Zeon processors  4GB RAM  200GB SAN volume

 Operating System
–

Windows 2000 Oracle9i Release 2 (32-bit)

 Database
–

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Configuration: 32-bit Database
 SGA size and general configuration
– – – –

Total size = 1G (approximately) db_cache_size = 600M shared_pool = 400M log_buffer = 1M

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Configuration: Application
 Application characteristics
– – –
– –

5k stored procedures Thick client Connection pooling managed by application server E-mail text messages stored as BLOBs Total memory footprint including PGA is 1.5GB at 400 connections

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Configuration: 64-bit Architecture
 Server hardware
– –

16GB memory 4x800 MHz Itanium2 processors Windows Server 2003 64-bit PowerPath Emulex drivers using HBA connectivity to SAN 64-bit file format set in the O/S

 Operating System and software
– –
–

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Configuration: 64-bit Database
 Oracle9i configuration
– – – – –

Larger SGA (2G) shared_pool = 400M buffer_cache = 1200M log_buffer = 2M pga_aggregate_target = 1G
2x4M record tables Pinned several large PL/SQL packages

 Objects cached in SGA
–

–

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Customer case study: Intel Agenda

 Test strategy

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Test Strategy
 The strategy for the comparison between a 32-bit XEON® Oracle9i system and a 64-bit Itanium2® Oracle9i system was to generate an identical load on each system and compare both resource usage and performance under that load.  Performance Monitor was used for resource usage capture.  Application Characterization Tool – Application Expert by Compuware was used for capturing performance.  Visual Test Scripts were used to generate the user load against a thick client, connecting through an application server, which then connected to the 32-bit or 64-bit database backend, depending on the test.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Customer case study: Intel Agenda

 Key indicators and results

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Results: 32-bit XEON Load @ 600 Users

CPU Avg. load 45%

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Results: 64-bit Itanium2 Load @ 600 Users

CPU Avg. load 20%

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Results: Response Time with 600 Users
 32-bit XEON®
–

Average response time under load: 2.27 seconds

 64-bit Itanium2®
– –

Average response time under load: 1.16 seconds Note: Large queries that would not resolve under the 32-bit environment would resolve while utilizing the 64-bit Itanium2® system with Oracle9i.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Sample of indicators recorded
 System performance
–

Change in load distribution between the database server and the application server Case creation Simple search function Complex search function

 Application performance
– –
–

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Key indicators
 System Performance
–
–

–

Change in load distribution between the database server and the application server CPU load in database server changed from 45% to 20%, while application server CPU load increased from 30% to 100% Network traffic monitoring indicated bottleneck at the application server, as requests to the database were handled at a higher rate than the app server could generate

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Key indicators
 Application performance  Login/logout
–

–

Account lookup and profile access, followed by fetch of objects viewable in the application database 64-bit averages 20% faster Involves search for duplicate cases in several 1M record tables 64-bit averages 25% faster

 Case creation
– –

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Key indicators
 Search operations
–

Simple search
 Individual record lookup based on case number involves 2 table join  64-bit averages 40% faster

–

Heavy search
 Search of multiple instances of case information related to single customer profile. Multi-table join of 4M to 8M record tables

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Key indicators
 Search operations: Special Search case test
–

Disabled search
 Version of heavy search with single criteria on common customer name, company name, geographic location, resulting in outer joins  Not enabled in 32-bit version due to impact on system under normal load conditions  64-bit version returns result sets in less than 2 minutes on average

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Customer case study: Intel Agenda

 Comparative analysis

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Conclusion
 System characteristics improvements
–

Scalability - Itanium2 server can drive multiple application servers
 Application server became the bottleneck in the 64-bit architecture, which can be remedied by adding more 32-bit servers.

 Application characteristic improvements
– – –

Faster response times on all key indicators Ability to more efficiently handle heavy queries Disabled query operations possible

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

Reminder – please complete the OracleWorld online session survey Thank you.

Text and slide content Copyright © 2003 Intel Corporation.
Slide background Copyright © 2003 Oracle Corporation.. *Other names and brands may be claimed as the property of others.

QUESTIONS ANSWERS


				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:315
posted:8/12/2009
language:English
pages:58