Tuning from Development to Production

---

Remember this. Probably most Oracle client installations are very small databases, even for some large companies. For instance, many databases for a lot of the Silicon Valley based dot-com companies of the late nineties were well under 10 Gb, sometimes even as small as being less than a single gigabyte. They expected growth. The point is this. Many databases are small. A lot of Oracle clients are small companies. The result is that Oracle installation software and database creation tools tend to cater to those small companies. Oracle Corporation's strategy in this respect is perfectly sensible since the smaller companies lack the funds for highly skilled staff. The smaller companies need more done for them. Large end user software vendor companies often take this approach. The result is that in the Oracle installation software, most of the configuration parameters and physical settings for files are much too small for any database which experiences a reasonable amount of growth. If a database is over 10 Gb, is highly active or is growing rapidly then configuration created by Oracle installation software and database creation tools is probably inappropriate.

Tuning Oracle Database is not just tuning the database. As we can see from the stages of tuning already discussed tuning the database also includes tuning the data model, SQL code, and thus applications. Tuning Oracle Database is a combination of tuning both the Oracle database server and the applications accessing that database. There is a process of tuning an Oracle Database environment including a large number of separate steps or phases. These phases are repetitive but should be performed as closely as possible to the sequential order as shown in Figure 1.

The Steps in Tuning

Tuning is a set of distinct steps, which can be repeated in any order but are preferably completed in the order shown in Figure 1. The order and existence of these steps could vary depending on the application type, skills resources, time for development, and the capacity of available hardware.

Figure 1: The Steps in Tuning an Oracle Installation

The steps in tuning an Oracle installation should more or less follow the same path as in the development cycle of software development; namely analyze, design, build, test, implement, and verify.

Data Model Tuning

• A data model is used to represent a set of business rules. Business rules are implemented using entities (tables) and the enforcement of relationships between those entities. Additionally business rules can be implemented using database encapsulated stored procedures plus event or state change triggers.

  Tip 

  Using triggers can cause serious performance problems.

• Business rules can be tuned into a more mathematically correct design using Normalization and Referential Integrity. Referential Integrity ensures that relationships between data items are conformed to.

• Denormalization is the removal of the more granular results of Normalization. Granularity causes complex mutable multiple table joins. Multiple table joins can be difficult to tune effectively.

• Alternate or secondary indexing to cater for SQL code not complying directly with a Normalized structure can cause problems. This is common for object applications. This step is part of both the data modeling and the applications coding stages, not either. Alternate indexing is generally enhanced in the applications development stage but should be strictly controlled. Creating too many indexes can cause as many problems as they resolve.

• Constraints, PL/SQL stored procedures, functions and event or state change triggers should be tuned for performance in accordance with entities, relationships, and alternate indexing structures. Triggers are best avoided. Database level coded PL/SQL will perform better in some situations than others. PL/SQL should only cover business rule functionality and database access code, not applications functionality. Business rules sometimes match relational database structure where applications functionality often does not.

• Implementing Referential Integrity.

  • Should Referential Integrity be implemented? Not necessarily. Referential Integrity can ensure the correctness of data but it will slow down data changes somewhat due to verification.

  • How should Referential Integrity be implemented? It can be implemented using constraints or triggers. Constraints are the faster and much more reliable method. All sorts of things can go wrong with triggers and the performance of triggers is highly questionable.

  • Can Referential Integrity be partially implemented? Yes it can. Very small tables containing static, referential data can often have their foreign keys removed. Additionally noncritical tables or highly used tables can avoid Referential Integrity to help performance. Generic static tables, when used, probably should avoid Referential Integrity as well. An example of a generic static table is shown in Figure 2. These types of tables, in the interests of performance at the database level, are best avoided.

    
    Figure 2: Using Static Generic Entities

  • Where should Referential Integrity be implemented? Referential Integrity can be enforced in the database or at the application level. The benefit of implementing Referential Integrity at the database level is simple implementation in one place and one place to change in the future. Developers may not necessarily agree with this philosophy but database administrators generally would.

SQL Code Tuning

SQL coding requirements should fit the specifications of the data model based on entities, relationships, and alternate indexing. SQL code can be in both database based PL/SQL coding and applications embedded SQL code. What are the steps in tuning SQL code?

• Identify the worst-performing SQL code and tune only those SQL statements.

• When the worst-performing code is tuned SQL code can be tuned in general if there is still a problem.

• Create, remove, and tune alternate indexing to suit SQL code performance requirements without jeopardizing the data model. When matching SQL code to indexing it is best to attempt to map SQL code filtering, sorting and joining to primary and foreign key index structures (Referential Integrity). Using already-existing Referential Integrity keys will reduce the number of alternate indexes. More indexes on tables will speed up data selection but will slow down data updates, sometimes drastically. The fact is if SQL code is not utilizing Referential Integrity indexing there may be a mismatch between the data model and application requirements or the data model is simply inappropriate.

• How are individual SQL code statements tuned in Oracle Database?

  • Often the most effective method is to examine the SQL code by hand and make sure that the SQL statements conform to indexing in the database, namely in selecting, filtering, sorting, and grouping clauses. The more complex SQL code is, the more difficult it will be to tune in this manner. In fact complex SQL code is sometimes beyond the capabilities of the Optimizer.

  • Use the Oracle Database EXPLAIN PLAN command to examine the Optimizer's best execution path for SQL code. EXPLAIN PLAN will show where potential improvement can be made. A simple query plan is shown in Figure 3.

    
    Figure 3: A Simple Query Plan from EXPLAIN PLAN

  • Trace files and TKPROF can be used to tune SQL code but tracing produces excessive amounts of information. Tracing should be a last resort method of tuning SQL code.

  • Make sure coders use bind variables in both PL/SQL and applications embedded SQL code. Bind variables are not as significant after Oracle8i Database Release 1 (8.1.6) because of the cursor sharing configuration parameter but forcing cursor sharing lowers statistical accuracy and deteriorates Optimizer performance.

  • Beware of generic or generated SQL code, common at the applications level. This type of embedded SQL code can be very difficult to tune.

Configuration and Physical Tuning

Configuration Tuning

Possibly one of the most effective tuning practices, especially as far as configuration tuning is concerned, is proper initial Oracle installation and configuration of your databases. Do not leave your Oracle installation as Oracle installation software and database creation tools create it. It might be expedient to use the Oracle Database creation tool for a first experimental database creation only.

Tip 

 Oracle Database 10 Grid   The database creation tool (Database Configuration Assistant) has become more sophisticated over time. However, most database administrators still prefer to use scripting since it can be modified at will and executed in parts. This approach is no longer necessary except for highly complex installations.

The database creation tool creates a fairly well-organized physical and configured structure but its default settings are geared toward very small databases. Take time for properly organizing the database and its configuration initially and then you will be less likely to have large amounts of downtime later on.

Physical Tuning

Physical tuning involves the removal of competition for resources. Physical tuning covers the following areas:

• Physical space usage and proper storage structure usage in terms of how blocks are used and reused. Tuning at the block level depends on the application type.

• Growth management of the database and capacity planning must be monitored.

• Setting files such as log files and rollback segments to sizes and frequencies as appropriate to database usage.

  Note 

   Oracle Database 10 Grid   Manual rollback is deprecated. Use Automated undo.

• There can be contention between processes, memory usage, and data. Some of this type of tuning falls within the scope of both configuration and physical tuning.

• I/O contention can be dealt with in the file system by spreading the I/O load using multiple disks or RAID arrays. RAID arrays are most efficiently used using random access on data spaces and sequential access on index spaces. In non-RAID systems it is sensible to separate data and index spaces since both are read more or less concurrently. Oracle Partitioning is a classic example of this type of tuning.

---