Oracle Database Performance and Scalability

A Quantitative Approach

Henry H. Liu

• Publisher: Wiley-IEEE Computer Society Pr
• Year: 2011
• Print ISBN: 9781118056998
• eBook ISBN: 9781118135518
• Edition: 1
• Topic: Computer Science
• Subtopic: Software Development
• Index: Computer Science

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.

Part One

Getting Started With Oracle

Rome was not built in one day.

—John Heywood, 1497–1580, English Playwright and Poet

Building and deploying business software systems across an enterprise requires a high-performance, scalable, and reliable database platform. Oracle as a powerful database platform has been fulfilling this role since decades ago. However, Oracle has more and more features built-in as a result of evolutions from generation to generation. In order to be able to cope with Oracle's complexity effectively, we need to learn its peculiarities and strengths in performance and scalability so that we would be able to make the most of it.

Based on my experiences, the best way to learn about a software product is to actually put it up and test-drive it. As soon as you become familiar with it, apply some load and increase load intensity gradually. Then watch how it behaves and even how it breaks. Then find out why it is broken. Figure out how you can make it undergo larger and larger loads by making adjustments to its various tunable parameters. After a few rounds of test drives like this, you could quickly become an expert.

But we need to take baby steps one at a time. In this part, I'll help you achieve the first goal of being able to get Oracle up and running and get around it freely.

This part consists of the following chapters:

Chapter 1, “Basic Concepts,” introduces some basic concepts so that we will all be on the same page when we move along to more advanced subjects.

Next, in Chapter 2, I'll show you how to install Oracle software and create an Oracle database without you having to comb through an installation guide and try it out on your own, repeatedly hitting and missing. It could become very frustrating some times when you think it should be a simple matter but actually it doesn't work no matter how hard you try. In addition, how well an Oracle Server eventually performs and scales is pre-determined by how it is configured at the installation time to some extent. Therefore, consider an Oracle installation experience a valuable opportunity to learn about how to set up an Oracle environment for a high potential of high performance and scalability down the road rather than merely a boring task of getting it up and running.

Chapter 3 gives you a complete overview of all the options you have to access your Oracle database server. Knowing about those options up-front can save you a lot of guessing work and frustrations.

Chapter 4 walks you through all major aspects of an Oracle server with a tour of an Oracle setup I installed on one of my systems.

Let's start with introducing some basic preparatory concepts in Chapter 1 next.

Chapter 1

Basic Concepts

Physical concepts are free creations of the human mind, and are not, however it may seem, uniquely determined by the external world.

—ALBERT EINSTEIN, The Evolution of Physics

Before we begin our exposition of Oracle performance and scalability, we need to consider a few preliminaries. This would include a brief introduction to what SQL is, and then a comparison between relational and object-oriented databases. We'll also clarify the differences between the concept of an Oracle instance and that of an Oracle database (these two concepts will come up frequently throughout this text). This is a necessary preparation before we take off on optimizing and tuning Oracle performance and scalability.

To be specific, this chapter consists of the following main sections:

• Standard versus Flavored SQLs
• Relational versus Object-Oriented Databases
• An Instance versus a Database

Let's start with a brief overview of standard SQLs versus flavored SQLs next.

1.1 Standard versus Flavored SQLs

SQL stands for Structured Query Language, which is pronounced as “sequel” or “ess cue ell.” It was said that at Oracle the former has been the norm. SQL was originally invented in 1974 by Donald Chamberlin and Raymond Boyce at IBM. The concept of SQL was further refined by Edgar F. Codd in his influential paper “A Relational Model of Data for Large Shared Data Banks,” published in 1970. SQL is a language for querying data, which is structured in relations, with both data and relations stored in a data store or database. SQL was later taken over and standardized by ANSI/ISO. The latest version of standard SQL is SQL: 2008, which is version 6 since the first version of SQL-86 released in 1986.

Most college students as well as computer and software professionals have had some exposure to SQL, and some of them are experts in this area. Our purpose here is not to delve deeply into SQL, but rather to review the components of SQLs divided into a series of subsets as described below:

• DML (Data Manipulation Language) SQLs. This subset of SQLs includes the SQL statements of SELECT, INSERT, UPDATE, DELETE, MERGE, and so on. Such SQLs allow users to query and manipulate data stored in a database.
• DDL (Data Definition Language) SQLs. This subset of SQLs includes the SQL statements of CREATE, ALTER, DROP, TRUNCATE, and so on. Such SQLs are used to create and modify tables, views, and indexes, and so on.
• DCL (Data Control Language) SQLs. This subset of SQLs includes GRANT, REVOKE, and so on. Such SQLs are used for controlling user access to data in a database, for example, granting/revoking certain privileges to/from certain users.
• TCL (Transaction Control Language). This subset of SQLs includes COMMIT, ROLLBACK, SET TRANSACTION, and so on. Such SQLs are useful for defining and manipulating database transactions.

Since a SQL standard is a common specification, it's subject to implementations by any interested parties. That leads to various flavors of SQL database products such as IBM's DB2, Microsoft's SQL Server, Oracle's Oracle, and the open source MySQL, PostgreSQL, and so on. MySQL was acquired by Oracle in 2009 as part of the Sun Microsystems acquisition.

Each of those products mentioned above has its own specific procedural language for writing programs and scripts that can run on its database server, for example:

• IBM's SQL is termed SQL PL
• Microsoft's SQL is termed T-SQL
• MySQL's SQL is termed MySQL
• Oracle's SQL is termed: PL/SQL
• PostgreSQL's SQL is termed PL/pgSQL

Mostly, those various flavors of SQLs differ in data types, especially in time and date, as well as in schemas and stored procedures. One needs to learn the proper SQL with a given database product.

Next, let's briefly discuss the subject of relational database management systems (RDBMS) versus object-oriented database management systems (ODBMS).

1.2 Relational versus Object-Oriented Databases

Real database-centric enterprise applications are rarely coded in SQL directly or entirely. Instead, they are coded in object-oriented programming languages such as C++, Java, or Microsoft C#, and so on. This has created a disparity between the language used for coding object-oriented application logic and SQL for operating on relational data in a relational database. Thus, the need for storing objects rather than relational tables in a database arose accordingly. It is believed that by supporting data as objects in a database, the overhead of converting between objects and relations can be avoided, resulting in higher development efficiency and better performance as well.

Most major database products, including Oracle, started supporting objects a few years ago. However, it's beyond the scope of this text at this time. We...

Table of contents

• Cover
• Quantitative Software Engineering Series
• Title Page
• Copyright
• Dedication
• Preface
• Acknowledgements
• Introduction
• Part 1: Getting Started with Oracle
• Part 2: Oracle Architecture from Performance and Scalability Perspectives
• Part 3: Optimizing Oracle Performance and Scalability
• Part 4: Case Studies: Oracle Meeting Real World Performance and Scalability Challenges
• Appendix A: Oracle Product Documentations
• Appendix B: Using Sql*Plus with Oracle
• Appendix C: A Complete List of all Wait Events in Oracle 11g
• Appendix D: A Complete List of all Metrics with the V$statname View
• Appendix E: A Complete List of all Statistics with the V$sysstat View
• Index