A little more on the new book
As the print date draws close for the new book, I thought I would share the details of what is in it. The price is right now on Amazon by the way. I think everyone should know what is 'inside' before they commit to buying.
Feel free to ask any question you would like about the book here and I'll be glad to address them.
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 're-inventing 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 lead 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 also take a quick look at the SGA (Shared Global Area) and the processes behind the Oracle instance, and describe 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 you use them.
Chapter 4, The Memory Structures. This chapter covers how Oracle uses memory – both in the individual processes (PGA or Process Global Area memory) and shared memory (SGA). We explore the differences between manual and automatic PGA and, in Oracle 10g, SGA memory management and see when each would be 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 between 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 and SMON you’ll see when you start an Oracle instance and explain 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 discussed Oracle's basic approach to these issues, the types of locks that can be applied (DML, DDL, latches...) and the problems that can arise if locking is not implemented carefully (deadlocking, blocking and escalation).
Chapter 7, Concurrency and Multi-versioning. In this chapter, we’ll explore my favorite Oracle feature – multi-versioning – and how it affects concurrency controls and the very design of your application. Here you will learn that all databases are not created equal and that their very implementation can have an impact on the design of your 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 multi-versioning, the feature that allows Oracle to provide non-blocking reads in the database, might have for you.
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 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, ROLLBACK), integrity constraints, distributed transactions (the two-phase commit) and finally autonomous transactions.
Chapter 9, Redo and Undo. It can be said that the developer does not need to understand the detail of redo and undo as much as the DBA, but developers do need to know the roles they play in the database. After first defining redo, we examine what exactly a COMMIT does. We discus how to find out how much redo is being generated, turning off logging and also analyzing redo.
In the undo section of the chapter 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 types of table. This chapter looks at each different type – heap organized (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, how it will be used. Too often, indexes are treated as an afterthought in application development, and performance suffers as a consequence.
This chapter we look in detail at the different types of indexes, including B*Tree, bitmap, function-based, and application domain indexes, and discuss where they should and should not be used. We'll also answer some of those common queries in the Frequently Answered Questions section, such as 'Do indexes work on views?' and 'Why isn't my index getting used?
Chapter 12, Datatypes. Datatypes – there are a lot 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 NLS (National Language Support), 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 and look at the new Oracle 10g options for storage of numbers in the database. The LONG and LONG RAW types are covered, but mostly from an 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, 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 is the LOB datatype, covering how they are stored and what each of the many settings such as IN ROW, CHUNK, RETENTION, CACHE, and so on mean to you. 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 up with the ROWID and UROWID types. These are special types, proprietary to Oracle, that represent the address of a row. We’ll describe 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. 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 start by describing when parallel processing is useful and should be considered, as well as when it should not be considered. After gaining that understanding, we move into 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 into 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 the DBA 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 if done serially.
The chapter closes on procedural parallelism, the means by which you can execute your application code in parallel. We cover two techniques here. The first is parallel pipelined functions – the ability of Oracle to execute your stored function in parallel dynamically. The second is do-it-yourself parallelism, whereby you design your application to run concurrently.
Chapter 15, Data Loading and Unloading. This first half of this chapter focuses on SQLLDR and covers the various ways in which we can use this tool to load and modify data in the database. Issues covered include loading delimited data, updating existing rows and inserting new ones, unloading data, and how to call SQLLDR from a stored procedure. Again, SQLLDR is a well-established and crucial tool but is the source of many questions with regard to its practical use. The second half focuses on external tables – an alternative and highly efficient means by which to bulk load and unload data.