Preface

---

This book is about tuning Oracle databases. Three areas of Oracle Database tuning are data model tuning, SQL code tuning plus physical, and configuration tuning. The author began his career as an applications developer and not a systems or network administrator. As a result this book is written from an applications rather than an operating system perspective.

The objective of this book is to cover all three areas of Oracle Database tuning. Currently there is no title on the market completely covering all of these. This book will cover all three by explaining both problem detection and resolution.

The approach in this book is to present something which appears to be immensely complex in a simplistic and easy-to-understand manner. Both reference material and examples are utilized appropriately in order to expedite understanding for the reader.

Reference material is not overused. Oracle software has been in general use commercially for many years and is intended to provide for a very large and diverse customer base. Features are often not removed from Oracle software between versions and new features are continuously being added. The result is that Oracle software contains a plethora of available options and features. Using only reference information to explain Oracle Database tuning would therefore be difficult to read, contrary to the approach of this book and would not provide the reader with much practical advice. This book contains a lot of examples, with realistic databases and data; sometimes even very large amounts of data. After all, if your production database needs tuning, you probably have more data than you first expected. This book is written from not only an applications perspective but from the expert knowledge of the author, both in development and database administration roles.

A broad-based tutorial on the subject of tuning Oracle Database is much needed. Most database administrators have operating system administration experience and little SQL code or data-modeling experience. On the other hand, developers have the opposite. This book will target both developers and database administrators since it includes all three areas essential to tuning Oracle installations effectively. The important factor is that all tuning skills are required for best performance, both administration and development skills sets.

Being a broad-based tutorial this title is written to reach the widest possible audience, including data modelers, developers, database administrators, and system administrators. Each of these audiences is very specialized, but all are related and interdependent. There are no existing titles including tuning for data models, tuning of SQL code plus physical and configuration tuning, all in one book.

People who would benefit from reading this book would be database administrators, developers, data modelers, systems or network administrators and technical managers. Technical people with these different skills are often ignorant of the skills of each other. This is a great pity because all skill sets are very much dependent on each other for well-constructed databases and applications. Let's take a common example situation. Developers cannot simply hand off SQL code tuning to database administrators when application coding is complete. Database administrators more often than not know absolutely nothing about SQL code tuning. The result is no SQL code tuning is ever done and too much time is spent squeezing out an extra 10% of performance, with the database administrator doing physical and configuration tuning. Targeting a few hard- hitting SQL statements will probably result in much more than a 10% performance improvement, which is much more productive.

What is in this book?

Data Model Tuning

What is the data model?

The data model is the table structure and the relationships between those tables. Tuning the data model for performance involves Normalization and Denormalization. Different approaches are required depending on the type of database installation such as OLTP or data warehouse type databases. Inappropriate database design can make SQL code impossible to tune. If the data model is poor, changing the data model can have the most profound effect on database performance. All SQL code is constructed from the underlying tables. The big problem is that altering the data model after completion of development is expensive since application code may require extensive rework.

Tip 

OLTP refers to Online Transaction Processing. OLTP generally implies the Internet. Within the scope of this book OLTP is used to represent both OLTP architectures and perhaps Client-Server architectures as well.

What in the data model causes problems and what is data model tuning?

Data model tuning is most effectively performed by a combination of both database administrators and developers. It is seldom the case that both skill sets are involved. The result is that table structures are often either development centric (top-down) or administration centric (bottom-up) designed. Java development is often top-down and attempts to impose an object structure over a relational framework. Bottom-up design often results in over-Normalization and too much granularity. People with different skills should be working together.

What are the benefits of data model tuning?

Tuning the data model can often provide performance improvements in excess of 100% but it is expensive because application code can be drastically affected.

SQL Code Tuning

What is SQL code?

SQL code is the code directly accessing the database, either embedded in applications or in stored procedures. Sometimes generic SQL code is used, which is SQL code generated by an application on an ad hoc basis. Generic SQL code can cause serious performance issues.

What causes SQL code performance problems and what is SQL code tuning?

As with data modeling it is often confusing which personnel skill sets are responsible for SQL code tuning. This is one of the causes of poorly performing SQL code. Performance is served most effectively when developers and database administrators work together to a certain extent.

Poorly written SQL code is often the biggest culprit of performance problems; it is expensive to rectify but cheaper than changing the data model. SQL code tends to be contained inside independent blocks within applications or stored procedures. This containment is commonly known as embedded SQL code. Tuning SQL code is in general a two-step process.

• Isolation and recoding of the worst-performing SQL statements, perhaps the slowest-performing 10% of SQL code.

• General tuning of SQL code involving changes to SQL statements throughout applications and the database, plus adjustments to alternate (secondary) indexing. Alternate indexing is not specifically part of the steps of Normalization and Denormalization but can be designated as data modeling or SQL code tuning. It is important that database administrators have some involvement with respect to alternate indexing, in the very least in an advisory capacity. Too many or inappropriately constructed alternate indexes can completely destroy performance.

What are the benefits of SQL code tuning?

SQL code tuning can increase performance between 25% and 100%, sometimes much more. In rare situations I have seen enormous performance improvements when tuning SQL code. One or two projects I have worked on in the past have been sped up 30 to 500 times, for both individual SQL code statements and sometimes even the applications in general. Additionally SQL code is often embedded in applications in such a way that changing the SQL code does not affect application functionality.

Physical Database and Configuration Tuning

What is physical and configuration tuning?

Physical database tuning involves hardware resource usage, networking, and various other administration tasks such as configuration and file distribution.

What causes physical and configuration performance problems and what is physical and configuration tuning?

Physical configuration is usually a culprit of poor performance where Oracle software is installed with defaults and never altered by an expert. Quite often developers build table structures and SQL code. In this case physical tuning is relied upon to solve performance problems. This is usually a mistake since physical configuration tuning usually only provides at most 10-20% performance improvement.

What are the benefits of physical and configuration tuning?

Physical and configuration tuning usually only results in at most a 25% performance improvement, and usually a lot less. The relative cost of using physical and configuration tuning only is usually not cost effective. Hardware upgrades are common in these situations.

Hardware Upgrades

As a side issue there are always potential hardware upgrades, sometimes a short-term solution because this approach does not necessarily tackle the real problems. Sometimes a combination of hardware upgrades and Oracle installation tuning is the most cost effective option. Hardware upgrades can often be more expensive than tuning. Three months of SQL code tuning by an expert may be much more cost effective than purchasing new machines, RAID arrays and all the "other bits-and-pieces that go with it". Additionally an expert can teach developers and database administrators to build properly tuned databases and applications in the first place.

Sample Databases Used in this Book

A number of sample databases are utilized in this publication. Some are simple and some are highly complex, depending on their use when explaining aspects of tuning. All details and diagrams are included in full in Appendix A.

Please note that this book does not cover operating system tuning or data warehouse tuning, even though they may be mentioned or alluded to in many places. I do not claim to know everything about Oracle Database performance tuning. I do have 15 years of custom applications development and database administration experience. What I do know is shared in this book.

Let's get started.

---