Description
How This Book Is Structured
To help you use this book, most chapters are organized into four general sections (described in the list that follows). These aren’t rigid divisions, but they will help you navigate quickly to the area you need more information on. This book has 15 chapters, and each is like a “minibook”—a virtually stand-alone component. Occasionally, I refer to examples or features in other chapters, but you could pretty much pick a chapter out of the book and read it on its own. For example, you don’t have to read Chapter 10 on database tables to understand or make use of Chapter 14 on parallelism.
The format and style of many of the chapters is virtually identical:
• An introduction to the feature or capability.
• Why you might want to use the feature or capability (or not). I outline when you would consider using this feature and when you would not want to use it.
• How to use this feature. The information here isn’t just a copy of the material in the SQL reference; rather, it’s presented in step-by-step manner: here is what you need, here is what you have to do, and these are the switches you need to go through to get started. Topics covered in this section will include:
• How to implement the feature
• Examples, examples, examples
• How to debug this feature
• Caveats of using this feature
• How to handle errors (proactively)
• A summary to bring it all together
There will be lots of examples and lots of code, all of which is available for download from the Source Code area of www.apress.com. The following sections present a detailed breakdown of the content of each chapter.
Chapter 1: Developing Successful Oracle Applications
This chapter sets out my essential approach to database programming. All databases are not created equal, and in order to develop database-driven applications successfully and on time, you need to understand exactly what your particular database can do and how it does it. If you do not know what your database can do, you run the risk of continually reinventing the wheel—developing functionality that the database already provides. If you do not know how your database works, you are likely to develop applications that perform poorly and do not behave in a predictable manner.
The chapter takes an empirical look at some applications where a lack of basic understanding of the database has led to project failure. With this example-driven approach, the chapter discusses the basic features and functions of the database that you, the developer, need to understand. The bottom line is that you cannot afford to treat the database as a black box that will simply churn out the answers and take care of scalability and performance by itself.
Chapter 2: Architecture Overview
This chapter covers the basics of Oracle architecture. We start with some clear definitions of two terms that are
very misunderstood by many in the Oracle world, namely instance and database. We then cover two new types of databases introduced in Oracle 12c, namely container database and pluggable database. We also take a quick look
at the System Global Area (SGA) and the processes behind the Oracle instance, and examine how the simple act of
“connecting to Oracle” takes place.
Chapter 3: Files
This chapter covers in depth the eight types of files that make up an Oracle database and instance. From the simple parameter file to the data and redo log files, we explore what they are, why they are there, and how we use them.
Chapter 4: Memory Structures
This chapter covers how Oracle uses memory, both in the individual processes (Process Global Area, or PGA, memory) and shared memory (SGA). We explore the differences between manual and automatic PGA and, in Oracle 10g, automatic shared memory management, and in Oracle 11g, automatic memory management, and see when each is appropriate. After reading this chapter, you will have an understanding of exactly how Oracle uses and manages memory.
Chapter 5: Oracle Processes
This chapter offers an overview of the types of Oracle processes (server processes versus background processes). It also goes into much more depth on the differences in connecting to the database via a shared server or dedicated server process. We’ll also take a look, process by process, at most of the background processes (such as LGWR, DBWR, PMON, SMON, and LREG) that we’ll see when starting an Oracle instance and discuss the functions of each.
Chapter 6: Locking and Latching
Different databases have different ways of doing things (what works well in SQL Server may not work as well in Oracle), and understanding how Oracle implements locking and concurrency control is absolutely vital to the success of your application. This chapter discusses Oracle’s basic approach to these issues, the types of locks that can be applied (DML, DDL, and latches), and the problems that can arise if locking is not implemented carefully
(deadlocking, blocking, and escalation).
Chapter 7: Concurrency and Multiversioning
In this chapter, we’ll explore my favorite Oracle feature, multiversioning, and how it affects concurrency controls and the very design of an application. Here we will see that all databases are not created equal and that their very implementation can have an impact on the design of our applications. We’ll start by reviewing the various transaction isolation levels as defined by the ANSI SQL standard and see how they map to the Oracle implementation (as well
as how the other databases map to this standard). Then we’ll take a look at what implications multiversioning, the feature that allows Oracle to provide nonblocking reads in the database, might have for us.
Chapter 8: Transactions
Transactions are a fundamental feature of all databases—they are part of what distinguishes a database from a file system. And yet, they are often misunderstood and many developers do not even know that they are accidentally not using them. This chapter examines how transactions should be used in Oracle and also exposes some bad habits that may have been picked up when developing with other databases. In particular, we look at the implications
of atomicity and how it affects statements in Oracle. We also discuss transaction control statements (COMMIT, SAVEPOINT, and ROLLBACK), integrity constraints, distributed transactions (the two-phase commit, or 2PC), and finally autonomous transactions.
Chapter 9: Redo and Undo
It can be said that developers do not need to understand the detail of redo and undo as much as DBAs, but developers do need to know the role they play in the database. After first defining redo, we examine what exactly a COMMIT does. We discuss how to find out how much redo is being generated and how to significantly reduce the amount of redo generated by certain operations using the NOLOGGING clause. We also investigate redo generation in relation to issues such as block cleanout and log contention.
In the undo section of the chapter, we examine the role of undo data and the operations that generate the
most/least undo. Finally, we investigate the infamous ORA-01555: snapshot too old error, its possible causes, and how to avoid it.
Chapter 10: Database Tables
Oracle now supports numerous table types. This chapter looks at each different type—heap organized (i.e., the default, “normal” table), index organized, index clustered, hash clustered, nested, temporary, and object—and discusses when, how, and why you should use them. Most of time, the heap organized table is sufficient, but this chapter will help you recognize when one of the other types might be more appropriate.
Chapter 11: Indexes
Indexes are a crucial aspect of your application design. Correct implementation requires an in-depth knowledge of the data, how it is distributed, and how it will be used. Too often, indexes are treated as an afterthought in application development, and performance suffers as a consequence.
This chapter examines in detail the different types of indexes, including B*Tree, bitmap, function-based, and application domain indexes, and discusses where they should and should not be used. I’ll also answer some common queries in the “Frequently Asked Questions and Myths About Indexes” section, such as “Do indexes work on views?” and “Why isn’t my index getting used?”
Chapter 12: Datatypes
There are a lot of datatypes to choose from. This chapter explores each of the 22 built-in datatypes, explaining how they are implemented, and how and when to use each one. First up is a brief overview of National Language Support (NLS), a basic knowledge of which is necessary to fully understand the simple string types in Oracle. We then move on to the ubiquitous NUMBER type. Next the LONG and LONG RAW types are covered, mostly from a historical perspective. The main objective here is to show how to deal with legacy LONG columns in applications and migrate them to the LOB type. Next, we delve into the various datatypes for storing dates and time, and investigating how to manipulate the various datatypes to get what we need from them. The ins and outs of time zone support are also covered.
Next up are the LOB datatypes. We’ll cover how they are stored and what each of the many settings such as
IN ROW, CHUNK, RETENTION, CACHE, and so on mean to us. When dealing with LOBs, it is important to understand how they are implemented and how they are stored by default—especially when it comes to tuning their retrieval and storage. We close the chapter by looking at the ROWID and UROWID types. These are special types, proprietary to Oracle, that represent the address of a row. We’ll cover when to use them as a column datatype in a table (which is almost never).
Chapter 13: Partitioning
Partitioning is designed to facilitate the management of very large tables and indexes by implementing a divide and conquer logic—basically breaking up a table or index into many smaller and more manageable pieces. It is an area where the DBA and developer must work together to maximize application availability and performance. Features introduced in Oracle 11g and Oracle 12c are also covered in detail.
This chapter covers both table and index partitioning. We look at partitioning using local indexes (common in data warehouses) and global indexes (common in OLTP systems).
Chapter 14: Parallel Execution
This chapter introduces the concept of and uses for parallel execution in Oracle. We’ll start by looking at when parallel processing is useful and should be considered, as well as when it should not be considered. After gaining that understanding, we move on to the mechanics of parallel query, the feature most people associate with parallel execution. Next, we cover parallel DML (PDML), which allows us to perform modifications using parallel execution. We’ll see how PDML is physically implemented and why that implementation leads to a series of restrictions regarding PDML.
We then move on to parallel DDL. This, in my opinion, is where parallel execution really shines. Typically, DBAs have small maintenance windows in which to perform large operations. Parallel DDL gives DBAs the ability to fully exploit the machine resources they have available, permitting them to finish large, complex operations in a fraction of the time it would take to do them serially.
The chapter closes on procedural parallelism, the means by which we can execute application code in parallel. We cover two techniques here. The first is parallel pipelined functions, or the ability of Oracle to execute stored functions in parallel dynamically. The second is “do it yourself” (DIY) parallelism, whereby we design the application to run concurrently.
Chapter 15: Data Loading and Unloading
The first half of the chapter focuses on external tables, a highly efficient means by which to bulk load and unload data. If you perform a lot of data loading, you should strongly consider using external tables. Also discussed in detail is the external table preprocessing feature that allows for operating system commands to be executed automatically as part of selecting from an external table.
The second half of this chapter focuses on SQL*Loader (SQLLDR) and covers the various ways in which we can use this tool to load and modify data in the database. Issues discussed include loading delimited data, updating existing rows and inserting new ones, unloading data, and calling SQLLDR from a stored procedure. Again, SQLLDR is a well-established and crucial tool, but it is the source of many questions with regard to its practical use.