Transcript Oracle Database 10g on Windows: Architecture for Performance

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
Background and foreground threads
Code
SGA contains
db buffers,
log buffers
shared pool,
other memory
allocations
Each thread
consists of
PGA, stack,
other memory
allocations
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 32bit Windows support more than 2000
concurrent connections. On 64-bit Windows,
the number of connections can rise until the
operating system runs out of resources.
 With the use of technologies such as
multiplexing, connection pooling, and RAC,
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
 Improved Micro Architecture
–
Predication removes delays caused by
mispredicted branches
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
 Itanium is not supported by
–
–
32-bit Oracle Database server
32-bit Oracle Database client
 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
 Database Upgrade Assistant provide to
automate process
 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.
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 application-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
 Oracle Data Provider for .NET
–
http://otn.oracle.com/tech/windows/odpnet
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.
A N N O U N C E M E N T
World Record TPC-C Benchmark
Using an NEC Express5800/1320Xd with 32 Intel
Itanium2 processors on 64-bit Windows 2003, Oracle
Database 10g achieved 521,440.53 tpmC with a priceperformance ratio of $11.77/tpmC.
As of September 5, 2003: NEC Express 5800/1320Xd, 521,440.53 tpmC, $11.77 tpmC,
available February 15, 2004. Source: Transaction Processing Council (TPC),
http://www.tpc.org
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
 Database
–
Oracle9i Release 2 (32-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.
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
 Operating System and software
–
–
–
Windows Server 2003
64-bit PowerPath Emulex drivers using HBA
connectivity to SAN
64-bit file format set in the O/S
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
 Objects cached in SGA
–
–
2x4M record tables
Pinned several large PL/SQL packages
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
 Application performance
–
–
–
Case creation
Simple search function
Complex search function
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
 Case creation
–
–
Involves search for duplicate cases in several 1M
record tables
64-bit averages 25% faster
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
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.