Lesson 3 | Tuning with database design |
Objective | Describe how database design effects performance |
Database Design Tuning
Good design prevents Performance Problems
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.
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.
Logical 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
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.