Database Design Tuning

Design is the most important area of tuning. A good database design is critical for a well-tuned database and should be considered the first step in the tuning process. In fact, it is very difficult, if not impossible, to tune a database that does not have an efficient database design. Essentially, by database design we are referring to the logical and physical design of the Oracle tables, the presence of table indexes, and the design of the application that accesses the database. For an overview, we can separate Oracle database design into logical design, physical design, and application design.

The design of the application also has a great impact on Oracle performance. The proper use of PL/SQL stored procedures and database constraints, as well as the parallel processing demands of the application will have a great impact on the performance of the database. In this module, we will focus on the first two, logical and physical design.

This refers to the logical structure of the tables and the number of SQL join operations that are required to service a request. As a general rule, the more we pre-join tables together in design, the faster the tables will perform at run time. Also, good logical design will greatly minimize disk I/O, since less work is required to retrieve the desired information.
Note: Disk I/O tuning is the single largest component of Oracle response time. Anything that can be done to reduce disk I/O will make your Oracle database run faster.

Physical design is the implementation detail behind the database structure. This refers to the sizing of the table parameters, the placement of indexes, and the physical configuration of the Oracle instance.
In the next few lessons, we'll focus our attention on logical design.