Oracle Database 11gR2 Performance Tuning Cookbook

This is a very detailed text by Fiorillo on how to improve the performance of your 11gR2 database. It presupposes that you are a database administrator already versed in the basics of 11g. The first chapter is the simplest, and possibly offers the quickest improvements to your system. It has many guidelines. Like warning that an academic design of an abstract database could map badly into the relational structure of an actual Oracle database. Beware of over normalisation, we are told. Where too many table joins happen. This can sometimes be traced back to having too many tables. The latter can arise due to an elegant factoring of a problem, or to the mapping of an object oriented design to a relational database. In general, fewer tables can be better, even if this incurs extra columns as it usually will. You should also look at and understand how the alert log works. A vital feedback if you want to adjust your table structures and measure actual performances. The second chapter contains sections that can seem like heresy to those of you taught to observe or create the Normal Forms. The text warns that sometimes poor performance can result. Oracle lets you define tables that do not even follow the 1NF! A very interesting section explains the differences between static and dynamic SQL. The former is hardcoded in application source code, while the latter is calculated (interpreted) at runtime. You are cautioned to only occasionally run dynamic SQL. It can be very slow. Later chapters delve into other topics. As in how to optimise storage under Oracle. Or to optimise SQL code. Each of these warrants its own chapter, as an indication of their importance. While the chapters are certainly detailed enough, it may well be that some readers will wish for more examples in each. You should also take a close look at the chapter on tuning memory. Avoid or minimise having the operating system page in and out of memory the Oracle runtime data. Very expensive because the disk accesses are several orders of magnitude slower than memory access. Just buying more memory for your machine might not be feasible. So pay attention to the chapter's remarks on how to tune memory usage.