Oracle Database Performance and Scalability E-Book

Henry H. Liu

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Quantitative Software Engineering Series
• Sprache: Englisch

Seitenzahl: 870

Veröffentlichungsjahr: 2011

Beschreibung

The innovative performance and scalability features with each newer edition of the Oracle database system can present challenges for users. This book teaches software developers and students how to effectively deal with Oracle performance and scalability issues throughout the entire life cycle of developing Oracle-based applications. Using real-world case studies to deliver key theories and concepts, the book introduces highly dependable and ready-to-apply performance and scalability optimization techniques, augmented with Top 10 Oracle Performance and Scalability Features as well as a supplementary support website.

Leseprobe

Quantitative Software Engineering Series

The Quantitative Engineering Series focuses on the convergence of systems engineering with emphasis on quantitative engineering trade-off analysis. Each title brings the principles and theory of programming in-the-large and industrial strength software into focus.

This practical series helps software developers, software engineers, systems engineers, and graduate students understand and benefit from this convergence through the unique weaving of software engineering case histories, quantitative analysis, and technology into the project effort. You will find each publication reinforces the series goal of assisting the reader with producing useful, well-engineered software systems.

Series Editor: Lawrence Bernstein

Professor Bernstein is currently an Industry Research Professor at the Stevens Institute of Technology. He previously pursued a distinguished executive career at Bell Laboratories. He is a fellow of the IEEE and ACM

Trustworthy Systems for Quantitative Software Engineering / Larry Bernstein and C.M. Yuhas

Software Measurement and Estimation: A Practical Approach / Linda M. Laird and M. Carol Brennan

World Wide Web Application Engineering and Implementation / Steven A. Gabarro

Software Performance and Scalability / Henry H. Liu

Managing the Development of Software-Intensive Systems / James McDonald

Trustworthy Compilers / Vladimir O. Safonov

Oracle Database Performance and Scalability: A Quantitative Approach / Henry H. Liu

Enterprise Software Architecture and Design: Entities, Services and Resources / Dominic Duggan

Copyright © 2012 by John Wiley & Sons, Inc. All rights reserved

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

Nopart of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on theweb at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permission.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Cataloging-in-Publication Data:

Liu, Henry H.

Oracle database performance and scalability : a quantitative approach /Henry H. Liu.

p. cm.

ISBN 978-1-118-05699-8 (cloth)

1. Oracle (Computer file) 2. Database management. I. Title.

QA76.9.D3L5945 2012

005.75'65–dc23

2011017552

oBook ISBN: 978-1-118-13553-2

ePDF ISBN: 978-1-118-13549-5

ePub ISBN: 978-1-118-13551-8

eMobi ISBN: 978-1-118-13550-1

To My Family

Preface

God created the integers, all else is the work of man.

—Leopold Kronecker

Why This Book

This book stemmed from the author's other book—Software Performance and Scalability: a Quantitative Approach, published by Wiley in 2009. That book helps readers grasp the basic concepts, principles, methodologies, best practices, queueing theories, and profiling tools associated with optimizing software performance and scalability in general. Many quantitative, real world case studies have been used to support the notions and theories presented therein. The book has been positively received around the world. Some readers suggested I apply the same style and approach adopted in that book, namely, basing all concepts and theories on quantitative, real world case studies, to explore systematically the art and science of optimizing the performance and scalability of some common foundational software platforms, such as database and virtualization platforms, upon which various software applications are built and run.

After some deliberation on the suggestions described above, I decided to give it a try. It occurred naturally to me that I should do it with Oracle first for a few reasons. One reason is that I have been working in the trenches on Oracle-based enterprise application performance and scalability for more than a decade. I have studied, used, optimized and tuned Oracle a lot; and most importantly, I had the foresight to accumulate many good quantitative case studies based on my own first-hand real experiences. I felt compelled to share all of my Oracle endeavors with a broader audience.

My second reason for writing this text is to offer an alternative, more effective and more efficient approach to learning Oracle and its performance and scalability features. One can certainly learn from the product documentations accompanying every release of Oracle. Yet, as of Oracle 11g, those documentations total over 10,000 pages! This apparently is not an effective approach to learning Oracle systematically in a reasonable timeframe. Then, what about so many other Oracle texts published over the years? Certainly, there are very excellent texts like Tom Kyte's, which contain comprehensive, reliable information; but there are also many Oracle texts that are full of opinions rather than facts supported with quality, quantitative, real world case studies. It is critical to distinguish between facts and opinions. Facts are supported by measured data, which is repeatable, whereas opinions are personal and not necessarily based on facts in general. Because of my physics research background, I always prefer the technical texts that are based on facts rather than opinions, and this text just falls into that category.

Along the way, I felt more and more obligated to make this text a concise, rigorous, and quantitative textbook so that university/college CS professors and their students could use it to supplement their database courses. I hope it will be useful not only in classrooms but also in the field for those professionals who strive to develop highly-performing, scalable enterprise software products based on Oracle. Incidentally, I am not on commission from Oracle. This has been totally my own choice that I feel is worth my time based on the intrinsic high performance and scalability of Oracle that I like and I know of.

Who This Book is For

One of the primary objectives of this text is to provide college professors who teach database courses at advanced undergraduate or post-graduate level with a much-needed, supplementary textbook. I took a database course at a U.S. college more than ten years ago when I was preparing for a career transition from physics research to computers. Retrospectively, I strongly feel that a database course should teach students not only database concepts and theories but also practical implementations in a real product like Oracle. It would be more ideal and beneficial if the concepts and theories could be corroborated with a real product like Oracle that is proven to be intrinsically high performing and scalable. Students could potentially have a much more rewarding future career if they were given a chance to have their classroom exercises enhanced with a solid, real database product.

The other equally weighted objective is to provide enterprise software professionals with a clearly-structured, reliable text that teaches how to build highly performing, scalable enterprise applications based on the world's most robust database product—Oracle. Although Oracle has been proven to be intrinsically high performing and scalable, software practitioners need to learn how to leverage the performance and scalability features engineered into Oracle to achieve the end result of meeting performance and scalability requirements with their products as demanded by their customers.

This book has the right style and context both for college professors who teach database concepts and for enterprise software professionals who develop Oracle-based enterprise applications. It has been written carefully with the following considerations:

practicality-based selection of all basic database concepts and architectural features designed specifically with Oracle from performance and scalability perspectives,precise step-by-step instructions about how to perform various Oracle specific tasks in the context of optimizing and tuning Oracle performance and scalability as convenient timesavers for all audiences,a full-scale secure online banking application (SOBA) built with the latest technologies such as Spring Framework, Hibernate, and RESTful Web services to demonstrate how an Oracle-based application can be developed with performance and scalability taken into account,quantitative case studies demonstrating Oracle meeting performance and scalability challenges in the real world.

These considerations are reflected in how this book is organized as discussed next.

How This Book is Organized

This book is divided into the following four parts logically, in order to meet the main objectives described previously:

Part 1,“Getting Started with Oracle,” consists of four chapters demonstrating how to set up a working Oracle environment with some of the major performance and scalability factors taken into account in the first place. A quick tour is provided to help illustrate all major database concepts in Oracle's context. In summary, this part helps a reader get up to speed quickly with getting around an Oracle server. Based on my own experience, the best way to learn about a software product starts with learning how to install and set it up. This would serve as an effective stepping stone to learning more advanced concepts and features.Part 2, “Oracle Architecture from Performance and Scalability Perspectives,” covers all major database concepts and architectural features related to optimizing Oracle performance and scalability. The following subjects are covered:

overall Oracle architecture

memory management

storage structure

Oracle wait interface (OWI)

Oracle data consistency and concurrency

Oracle automatic workload repository (AWR)

Oracle advanced features and options

Top 10 Oracle performance and scalability features

Oracle-based application performance and scalability by design

Project: SOBA—A Secure Online Banking Application on Oracle

Part 3, “Optimizing Oracle Performance and Scalability,” teaches all about how to optimize and tune Oracle performance and scalability. The following subjects are selected:

Oracle cost-based optimizer (CBO)

Oracle SQL tuning

Oracle indexing

Oracle auto-tune features

Part 4, “Case Studies: Oracle Meeting Real World Performance and Scalability Challenges,” provides quantitative case studies out of my own first-hand, real product based experiences to demonstrate how one can achieve high performance and scalability by applying various Oracle performance and scalability best practices. It sets a high standard on teaching Oracle performance and scalability by using quantitative, real world case studies rather than over-simplified, classroom-setting oriented, Scottish examples. Students and enterprise software professionals will be equipped with ready-to-apply techniques that can easily result in multifold or even orders-of-magnitude improvements on the performance and scalability of real products.

In addition to the main text, a few appendices are provided at the end of the book as handy references for performing various routine tasks in dealing with Oracle performance and scalability challenges.

Software and Hardware

To help make the most of this text, a hands-on approach is recommended. One can have an Oracle setting with the latest version of Oracle (11g release 2 as of this writing) installed on the following hardware systems (note that Oracle runs on a wide range of hardware and OS platforms).

For college students, a typical Oracle setting might just be a laptop with the latest version of Oracle installed. For writing this text, I used two PCs:

Oracle Server PC with the following specs (HP Pavilion desktop p6620f):

OS: Windows 7 Home Premium 64-bit

Processor: AMD Phenom II X4 quad-core 830 @ 2.8 GHz (2 MB L2 + 4 MB L3 Cache, 4 GHz System Bus)

Memory: 6 GB DDR3 SDRAM (3 × 2 GB)

Disk: SATA 1 TB (7200 RPM, 64 MB Cache)

Network: 10 /100 Ethernet LAN; Wireless LAN 802.11b/g/n

Dedicated Oracle Client PC with the following specs (Toshiba Satellite laptop L655 – S5103):

OS: Windows 7 Home Premium 64-bit

Processor: Intel Core i3-370M 2 Cores/4 Hyper Threads @ 2.4 GHz (512 KB L2 + 3 MB L3 Cache, 2500 MHz Front Side Bus)

Memory: 4 GB DDR3 SDRAM

Disk: HDD 500 GB (5400 RPM, 8 MB Cache)

Network: 10 /100 Ethernet LAN; Wireless LAN 802.11 b/g/n

Note that computer performance is not all about CPUs. A larger amount of memory and a faster disk are as equally important as fast CPUs.

For enterprise software professionals, an Oracle setting could range from a laptop or a desktop comparable to the PCs as described above at a minimum or readily to an enterprise-class server system with much larger computing capacities.

After you have decided on a computer on which you are going to experiment with Oracle, next, I am going to suggest how you can use this book most effectively and efficiently to achieve the maximum benefits along the way.

How to Use This Book

Depending on the background and interests of a reader, this text can be used in a variety of ways. Instead of suggesting what path a reader should take, I'd like to recommend a few learning elements from which a reader can build a learning path to suit his/her own interests. These learning elements include:

Setting up an Oracle Server Environment. It is strongly recommended to have at least a one-time hands-on experience with getting an Oracle Server up and running by following the procedure given in this text. This kind of experience is more than just installing and creating an Oracle database. You will get a real feel for what components an Oracle Server has, and thus understand the architecture of a robust database server product better. Besides, you will get an early exposure to most of the major Oracle performance and scalability settings as well as initialization parameters that can help ease your subsequent consumption of the material presented in this text significantly. This is also the proven, most effective and efficient approach to learning the architecture of a software product. By going through the process of setting up and configuring a product, combined with examining the relevant configuration files and navigating around on the product's admin console and user console if applicable, within a matter of hours, one could get a very solid feel about how things work together with the product. Note that learning by reading is merely a visual, brain exercise. Only actually getting your hands dirty can give you a real feel.Understanding the Concepts First before Getting Your Hands Dirty. An intellectual learning process always starts with grasping a basic set of concepts. It is always important to understand the concepts first, since they are the basic elements for associative and creative thinking. I have tried my best to explain various database concepts in Oracle's text as clearly as possible while meeting the goal of limiting the entire text to about 650 pages (in my opinion, wasting a reader's time with too verbose a text is kind of a soft sin). However, you may still feel that certain concepts might have not been explained in detail to the level that you can understand. If this turnsout to be the case for you, please email me your comments and I'll try my best to address your questions. Or, you can refer to some other sources, for example:

Oracle's own documentation accompanying each release, which is not only freely available online but also as authentic as they can be. For the latest version of Oracle 11g R2 as of this writing, all documentation is accessible at http://www.oracle.com/pls/db112/homepage. I strongly recommend three texts here: (1) the Concepts document, which explains various concepts in depth; (2) the Administrator's Guide, which starts out by explaining clearly the Oracle architecture first before describing how to carry out various administrative; tasks, and (3) the Performance Tuning Guide, which contains all Oracle performance tuning tips.

Tom Kyte's text, Expert Oracle Database Architecture: 9i, 10g and 11g Programming Techniques and Solutions, 2nd Edition, APress, New York, 2010. Tom knows Oracle inside out, and his data-based, scientific approach to exploring every Oracle issue has made him an asset to the Oracle community.

Experimenting with the Secure Online Banking Application (SOBA). This is an Oracle-based sample application intended for demonstrating all major Oracle performance and scalability features. I developed it with an end-to-end, piece-by-piece approach. This sample application will not only help demonstrate the full development life cycle of an Oracle-based application but also serve as a valuable educational and experimental tool for exploring Oracle performance and scalability features. I have decided to take this sample application—SOBA—much further than just another Oracle sample schema or a standalone backend tier: the application tier and Web tier were coded with one of the most widely used Java development platforms, Spring Source (version 3.0), in conjunction with some of the standard Web technologies available today. It is a very exciting and highly challenging project, and I will even bring you into some of the very hot new software technologies such as RESTful Web services. This is a fully fledged project, and I hope it will open a new path to learning about Oracle performance and scalability from a developer's perspective.Quantitative Case Studies. About one third of this text is dedicated to quantitative case studies out of my own first-hand, real product based experiences since Oracle 8i. This is one of the aspects that this text possesses, which differentiates itself from many other Oracle texts, outdated or new, on the market. Because of my background as a physicist, whenever I pursue a software performance and scalability issue, I always strive to set everything down to a firm ground as if it were a scientific experiment. I have a strong belief in data, and I hope those quantitative case studies can help open up horizons for you to learn how Oracle meets real world performance and scalability challenges.

Finally, to be fair and responsible, I have to make it clear that this is not a how-to text for full-time Oracle DBAs, although the Oracle performance and scalability troubleshooting methodologies and those quantitative case studies based on real products and customer engagements might be useful for such a group of Oracle professionals. For the same reason, I only cover those administrative tasks that are necessary for carrying out Oracle performance and scalability work in a broad sense.

How to Reach The Author

All errors in the text are the author's responsibility. You are more than welcome to email your questions and comments to me at [email protected]. Your valuable feedback will be taken seriously and acknowledged in the next version of this text. For downloads and updates, please visit the book's Web site at http://www.perfmath.com.

Henry H. Liu, PH.D.

Folsom, CaliforniaSpring, 2011

Acknowledgments

First, I would like to thank both my current employer, BMC Software, Inc., and my previous employers of Amdocs and Intel for all Oracle related opportunities through my employment during the past ten years, as without such great places to work, I could have not accumulated so much first-hand experience in Oracle and written this book. I would also like to thank Dr. Larry Bernstein for his great vision of quantitative computing with Wiley as well as his continuous support throughout the entire process of getting this text published. I am very grateful to Simone Taylor, the director of the editorial development at Wiley, for her making this publishing experience as smooth as possible. Many thanks to those anonymous reviewers for their professional opinions and generous comments, which greatly influenced my decisions on how this book should be organized and written to better serve the potential audiences of college students and enterprise developers. At last, my gratitude extends to both my wife Sarah and our son William for their invaluable support and patience. Hopefully I won't do it more than once again.

I have been deeply impressed by the splendid endeavor of NASA's launching of the rover Spirit to Mars. The cover design of this book is a best annotation of what performance and scalability really mean—how fast and how far one can go! Thanks for NASA's permission for using the Spirit illustration on the cover design to express the theme of this book in an indirect context.

Introduction

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.

—A Chinese Proverb

As one of the top few largest software companies in the world, Oracle Corporation offers many products in the categories of enterprise servers and applications. Although Oracle has expanded enormously during the past decade with aggressive acquisitions, Oracle database platform remains its strongest flagship product out of its entire portfolios. Rigorously speaking, we should always be specific about which Oracle server we are referring exactly, for example, Oracle database server, Oracle application server, and so on. For convenience, however, in this text, we use the term Oracle server (or simply Oracle) to mean Oracle database server implicitly, without always carrying the term database or database server explicitly when it's contextually obvious.

The Oracle database management system (DBMS) is one of the earliest, most widely used, and most robust database management systems for managing enterprise data. It has numerous features both in terms of functionality and in terms of performance and scalability, from most basic to very advanced ones. The magnitude and complexity of all those Oracle features can be evidenced with over 10,000 page documents accompanying every release of the product. Not every customer uses every feature, though. In reality, most customers use only a subset of all those features.

Next, let's explore some of the main features Oracle has to offer.

Features of Oracle