This is the complete, expert guide to building enterprise-class UNIX-based Oracle OLTP systems that deliver maximum performance and scalability. In Scaling Oracle 8i, one of the world's leading Oracle consultants introduces today's best methods and technologies for building industrial-strength Oracle database systems on UNIX platforms. Understand exactly what scalability means in the enterprise; then discover how to deliver it, step-by-step, from the ground up, through design, testing, construction, maintenance, benchmarking, and ongoing management. Morle covers every component that impacts performance, including hashing, caching, hardware architecture and I/O subsystems, Oracle database objects, data storage, memory structures, and a detailed review of the Oracle Parallel Server. Readers will find comprehensive coverage of tuning the underlying UNIX platform to improve OLTP response times; including co-engineering the kernel; working with virtual memory, I/O, interprocess communication; and more. Scaling Oracle 8i contains a full chapter on the special issues associated with e-commerce, as well as a detailed case study drawn from one of the world's largest car rental reservations systems. For all enterprise system architects, database engineers, and application developers working with Oracle.
I. CONCEPTS AND ARCHITECTURE.1. Scaling Concepts.
What Is Scaling?
Latches and Locks.
Things That Need Locking/Latching.
Waiting on Latches and Locks.
Design Considerations to Reduce Lock Contention.
What Is a Linked List?
What Are Linked Lists Used For?
Optimizing Chain Lengths.
What Is Hashing?
Cache Reference Patterns.
CPU Cache Concepts.
Further Reading.2. Hardware Architectures and I/O Subsystems.
Introduction to Hardware Architectures.
Direct Memory Access (DMA).
Single Processor Architectures (Uniprocessors).
Oracle on Uniprocessors.
Symmetric Multiprocessors (SMPs).
Oracle on SMP Architectures.
Massively Parallel Processors (MPPs).
Oracle on MPP Systems.
Cache Coherent Nonuniform Memory Access (ccNUMA).
Sequent NUMA-Q 20.
SGI Origin 20.
Oracle on NUMA Systems.
Disk Drive Sizing.
Cached Disk Arrays: EMC Symmetrix.
II. BUILDING SUPPORT SOFTWARE.3. Benchmark Concepts and Design.
Why Develop a Benchmark?
What Are We Simulating?
Defining Application Areas.
Defining the Application Split.
Careful with Those Assumptions, Eugene.
Selecting a Development Approach.
Using Remote Terminal Emulation Software.
Custom Simulation Development.
Building a Simulator Using RTE Software.
Building a Custom Benchmark Suite.
When the Application Has Not Been Written.
If the Application Exists: Trap and Emulate All SQL Calls.
Using Tracefiles to Generate a Simulation: An Introduction to dbaman.
Validate Server-Side System Utilization.
Building a Multiuser Framework.
Scale the Simulator.
User Control Problems.
Simpler Methods for Use with dbaman.
Make It Easy to Run.
Automating Information Retrieval at Closedown.
Define Limitations in Advance.
A Benchmark Is Never Perfect.
Measure the Impact of the Inaccuracies.
Further Reading.4. System/Database Monitoring.
Proactive Fault Detection.
Rapid Fault Diagnosis.
Historical Performance Records.
Go Easy on the System.
Avoiding Single-Threaded Resources.
Introduction to V$ Views.
What Are the V$ Views ?
Accessing the V$ Views.
Structure of the V$ Views.
Overhead of Using V$ Views.
Using dbaman to Optimize Data Collection.
Defining the Cursors.
Parsing and Executing the SQL.
Process the Result Sets.
Pulling it Together with a Main Loop.
Defining the "Flight Envelope."
What Is a "Flight Envelope"?
How Do I Define a Flight Envelope for the System?
Using Excel Spreadsheets for Data Visualization.
III. HOW ORACLE WORKS.5. Physical Oracle.
Redo Log Information.
The INIT.ORA File.
Rules for init.ora Creation.
Overview: The Physical Picture.
Blocks: An Introduction.
Key Database Objects.
Temporary Tablespaces and Temporary Segments.
Other Database Objects.
The Data Dictionary.
Further Reading.6. Oracle8 in Operation.
The Oracle Kernel.
The SGA Components.
Operation of the Buffer Cache.
Flowchart Summary of Buffer Cache Operation.
Shared Pool Structure.
Shared Pool: The Library Cache.
The Parsing Mechanism.
Other Memory Structures.
Oracle Parallel Server Concepts
Distributed Lock Manager (DLM).
Parallel Cache Management.
Design Considerations for OPS.
Further Reading for OPS.
V$ Views Exposed.
IV. HOW UNIX WORKS.7. Introduction to UNIX.
What Is a Kernel?
Explicit Kernel Processing (Process Context Kernel Mode).
Implicit Kernel Processing (System Context Kernel Mode).
The Process "Table."
Memory Management: The Virtual Memory System.
Virtual Memory Introduction.
Virtual Memory Hierarchy.
The Memory/Disk Hierarchy.
Implementing the Hierarchy.
Implications of fork() and exec().
Everything Is a File.
Filesystems Versus Raw Disk.
Logical Volume Managers.
Interprocess Communication (IPC).
System V IPC.
More on System Calls.
Further Reading.8. Oracle User's Guide to UNIX.
Coengineering the Oracle Kernel.
Tcl and expect.
V. IMPLEMENTING ORACLE.9. Scalable Transaction Processing.
This is a book about getting the most out of Oracle8i on UNIX systems. While many people understand how to administrate Oracle and UNIX, far fewer understand the issues and workings of the software and hardware, thus limiting the scalability of the system. This book aims to open up this essential information, enabling the reader to build faster, larger, and more scalable systems than ever before.
The purpose of this book is to provide grounding in all the areas required for large systems implementation using UNIX and Oracle8i. Some of the information in this text is available elsewhere, scattered throughout a large number of specialized volumes, while other information, taken from experience in implementing such systems, is previously undocumented.
Unlike many Oracle books, this book avoids the "one size fits all," cookbook approach to improving the skillset of the reader. In my opinion, such books do little to improve foundation skills and serve only to confuse readers when their circumstances deviate from those of the author. Rather, the intent of this book is to communicate a distillation of many years of experience in building very large Oracle database systems. The information presented here allows the reader to make informed decisions, based on real facts, that directly apply to the actual case at hand.
Where appropriate, this book will make recommendations to the reader, mostly from an approach standpoint. These recommendations are intended to guide the reader past some of the common pitfalls often encountered during the building of large systems. In addition to technical information, the book also makes organizational and procedural recommendations intended to help the reader avoid dead ends and other sources of aggravation.
Although the focus of this book is on Oracle8i, the principles presented also apply to other database software. UNIX is the premier platform for very large Oracle systems and is therefore presented as the underlying operating system, although many of the hardware and theoretical discussions also apply to other operating systems, such as Windows NT. Large, custom-written applications are the main target of this book, but all of the concepts presented here also apply in varying degrees to smaller systems and packaged applications.
Who Should Read This Book
Database systems are growing at an enormous rate. Both connection volume and data volume have grown exponentially since the large-scale adoption of open systems and of commodity database server software such as Oracle. These systems are now matching and exceeding the capabilities previously demonstrated only by mainframe systems.
Database systems can be separated into two broad categories:
This book is focused mainly on transactional systems, with references to DSS systems where appropriate.
In the mainframe world, scaling and robustness are often heavily ingrained in the cultures of all involved; system programmers, DBAs, application programmers, and the vendors themselves conform to rigorous standards and methodologies that are practically set in stone. The net result of this enforced conformance is a greater probability that scalable, robust business systems will be produced.
In the open systems world, no such constraints are set on any of the personnel who build the system; any method can be used as long as it achieves the required result. This flexibility is the catalyst behind the proliferation of open systems, allowing very rapid development and inclusion of more powerful functionality within the application. Unfortunately, this flexibility results in the following costs:
In order to mitigate these disadvantages, it has become increasingly important for builders of open systems solutions to change the way these systems are built. This involves two fundamental changes in the default, anarchic method of open systems development:
The second change requires open systems teams to have a far greater understanding of how the systems work than they ever had before. Mainframe developers have historically had to deal with two contrasting levels of complexity during development. On one hand, the segmentation of function within mainframe systems meant that the developer did not need to be concerned about portions of system operation. On the other hand, the development of applications in low-level languages meant that application developers were forced to be concerned about performance and "doing the right thing." In open systems, applications are typically developed using high-level or object-based languages, which means that the separation between the application developer and the underlying systems is far greater than when procedural, third-generation languages are used. The effect of this is that application developers are often too far removed from the system, and the only individuals on the team who can see the whole picture are the database engineers. It is important, therefore, that the database engineer be able to understand all the issues, and that the application developer also be aware of the necessary considerations.
How To Read This Book
The book is divided into several parts, each of which can mostly be read independently of the others. It is recommended, however, that the book be read sequentially from front to back. The reason for this is that, although all the parts overlap somewhat, the book has been written from front to back. For this reason, some assumption of knowledge of prior chapters is made. The order of presentation (hardware before software) may initially appear to be exactly reversed, as the greatest impact can be made in the software. This is true, but it is my opinion that software cannot be understood or responsibly architected without prior knowledge of how it relates to the actual execution on the hardware. Therefore, we take the journey from the ground up.
Part I: Concepts and Architecture
Chapter 1: Scaling Concepts
Chapter 2: Hardware Architectures and I/O Subsystems
The chapter goes on to discuss I/O, a core part of any database system. A thorough understanding of the mechanics of the physical disk, and of the various RAID options, should be considered a prerequisite to building a database.
Part II: Building Support Software
Chapter 3: Benchmark Concepts and Design
Chapter 4: System/Database Monitoring This chapter explores how a large Oracle database server can be monitored using low-intrusion techniques. Included in this chapter is an introduction on how to interrogate the Oracle fixed tables to derive operational data, and how to present that data using standard PC tools.
Part III: How Oracle Works
Chapter 5: Physical Oracle
Chapter 6: Oracle8 in Operation
Part IV: How UNIX Works
Chapter 7: Introduction to UNIX
Chapter 8: Oracle User's Guide to UNIX
Part V: Implementing Oracle
Chapter 9: Scalable Transaction Processing
Chapter 10: Pulling It All Together: A Case Study
Part VI: Further Considerations
Chapter 11: Building a Successful Team
Chapter 12: Pitfalls
Chapter 13: Internet Applications and the Future