Scaling Oracle8i™: Building Highly Scalable OLTP System Architectures

As open systems continue to replace traditional mainframe systems, system scalability is becoming an increasingly important topic. Although far more flexible than mainframe systems, open systems applications tend to be less reliable and more difficult to scale. There is no cookbook approach to solving this challenge: A thorough understanding of the technologies involved is essential for designing scalable solutions that meet long-term business needs.

Scaling Oracle8i™ offers valuable insights and techniques for designing reliable and scalable online transaction processing (OLTP) applications using Oracle. This book focuses on providing the in-depth information about Oracle and the underlying hardware required to build systems that scale effectively.

You will find coverage of relevant hardware and I/O operation; benchmark and database monitoring systems; Oracle internals, operation, and implementation; and UNIX operating system issues that impact Oracle performance and scalability. Essential topics covered include:

• Critical scalability concepts, including latches and locks, lists, hashing, and caching
• A briefing on various hardware architectures, including symmetric multiprocessor, clustered SMP, ccNUMA, and massive parallel processors
• I/O technology, including the physical attributes of I/O systems and RAID technology
• Producing application benchmarks using Oracle trace files
• An introduction to the Tcl-based Oracle scripting tool dbaman, for easy, general purpose database programming
• Interrogating the Oracle fixed tables to derive operational data
• Oracle Parallel Server
• The inner workings of the UNIX kernel and virtual memory system
• Oracle's Virtual Operating System abstraction interface, including hardware vendor co-engineering
• Writing scalable SQL
• Using TP monitors
• Performance tuning
• Scaling large Oracle applications

In addition, a real-life case study of a large-scale Oracle system illustrates concepts, approaches, and experienced-based pointers for implementation. Scaling Oracle8i™ concludes with a look at Oracle's future, including its role in the emerging field of Internet OLTP development.

0201325748B04062001

Preface

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
This book is primarily aimed at the following audiences.

• System architects
• Database engineers wanting to expand their knowledge
• System administrators working on UNIX/Oracle systems
• Senior application developers

In addition, this book will be of value to the following audiences.

• Technology managers
• Computer science students

0201325748P04062001

Introduction

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:

• Online transaction processing (OLTP) system
• Decision support systems (DSS) such as data warehouses, data mining, reporting and so on

Both types of systems present unique challenges in implementing systems of large scale. The challenge of large transactional systems involves the management of many small operations occurring at once, while DSS systems need to process vast amounts of data. Consequently, transactional systems need low latencies, and DSS systems need high throughput.

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:

0. Unscalable applications are the default product.
1. Reliable systems are difficult to obtain.

Both of these costs bear down hard on a business. Although the business has been able to develop the application and implement the hardware for a fraction of the cost of a comparable mainframe system, this advantage is overshadowed by potentially long, unscheduled downtime and by difficulties in scaling the system in line with business growth.

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:

0. A return to some of the ground rules introduced by the mainframe, particularly multitier architectures
1. A much greater level of technical knowledge within the teams

The first change involves taking the "good" aspects of the mainframe development sandbox and adopting them in the open systems arena. Multitier application architectures are prime among these aspects, moving away from the single points of failure, poor scalability, low reusability, and often proprietary, two-tier solutions.

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
What is scaling? Why do I need to be concerned with scalability? What are the common concepts used to provide scalability? This chapter presents the basic concepts of computer science that are required in order to understand some of the later chapters.

Chapter 2: Hardware Architectures and I/O Subsystems
This chapter describes the many different hardware architectures on the market today, all of which have significantly different operational profiles. Understanding the differences among the platforms and how those differences relate to the operation of Oracle are critical during platform selection and subsequent configuration and tuning.

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
Large systems cannot be simply rolled into production. At least some element of initial testing must be performed in order to certify both the performance and the stability of the platform. This chapter shows how a simple benchmark can be produced using simple Oracle trace files. Also presented is the Oracle-based scripting tool, dbaman.

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
This chapter concentrates on the physical attributes of Oracle, including the initialization file, the different types of objects, the internals of those objects, and how consistent read is implemented.

Chapter 6: Oracle8 in Operation
The other side of Oracle is the "living" side of it, the Oracle instance. This chapter describes the various caches used by Oracle and the measures that have been taken within the product to allow it to scale effectively. An introduction to Oracle Parallel Server is included, along with some more details on the V$ views.

Part IV: How UNIX Works

Chapter 7: Introduction to UNIX
A knowledge of the UNIX kernel often becomes important when one is trying to determine why a database server behaves the way it does. This chapter describes the UNIX kernel and the virtual memory system, and how they relate to an Oracle database server.

Chapter 8: Oracle User's Guide to UNIX
Oracle relies heavily on the underlying operating system. This chapter describes how Oracle interfaces with the operating system, using the virtual operating system abstraction. An introduction to a selection of the invaluable UNIX tools is provided.

Part V: Implementing Oracle

Chapter 9: Scalable Transaction Processing
This chapter provides guidelines on how to develop applications that scale, and how to tune the database to execute the requests most effectively. Included in this chapter are sections on writing scalable SQL, the purpose of a transaction processing (TP) monitor, and an approach to tuning for the desired result.

Chapter 10: Pulling It All Together: A Case Study
The proof of the pudding is in the eating. This chapter gives an overview of a real-life, large-scale Oracle system, along with pointers to the lessons learned during implementation.

Part VI: Further Considerations

Chapter 11: Building a Successful Team
This small but important chapter introduces some techniques for building a good team to do this kind of work.

Chapter 12: Pitfalls
This chapter concentrates on software problems--bugs. It is inevitable that large systems will be impacted in some way by bugs, and dealing with them effectively is important in maintaining a stable system.

Chapter 13: Internet Applications and the Future
The final chapter looks at the direction of application engineering and the future direction of Oracle.

Table of Contents

Figure List.


Preface.


Introduction.

I. CONCEPTS AND ARCHITECTURE.

II. BUILDING SUPPORT SOFTWARE.

III. HOW ORACLE WORKS.

IV. HOW UNIX WORKS.

V. IMPLEMENTING ORACLE.