Download chapter 4: 'Database tuning: Making it sing'
Excerpted from the book "Everyday Oracle DBA," ISBN 0072262087, Copyright 2005. Written permission from McGraw-Hill is required for all other uses. Copyright © 2005 McGraw-Hill. All rights reserved..
Chapter Excerpt:
Database Design
Optimally, if you can (that is to say if you're lucky enough to have a say in the design process), the biggest bang for the tuning buck is typically at database design time. Knowing the design, being able to put structures in place from the get-go, and normalizing the design to the extent it is practical (even third normal form is too normal for some databases) will go a long way when it comes to tuning. Understanding how users will use the data also helps, and not being afraid of employing a lot of Oracle's new features (new as of Oracle 8, Oracle 8i, Oracle 9i, or even Oracle 10g) even if they are scary new features like materialized views, dimensions, and partitioning can also benefit a design, either from the planning stage or even later, after the database is in place. Also, a word to the wise: work with your system administrators to lay the files out on disk as optimally as possible. They know the disks that will perform better, if your organization segregates job responsibilities between system administration and database administration, and you know the tables and tablespaces that are likely to be more active. Many that don't know end up putting the most active data on the least well performing disks.
Application Tuning
If you can't tune the database design, the next best option is to tune the
application and the application code. In many cases, the 80-20 rule applies.
Eighty percent of all performance problems can be resolved with coding
more optimal SQL or appropriately scheduling batch jobs during off-peak
hours. Of course, if you're in a global organization, finding an off-peak time
may be next to impossible, but it's still worth a try. The majority of this chapter
will cover this kind of tuning.
Memory Tuning
Properly sizing the SGA, your database buffers, and pools can go a long way
towards maintaining an efficiently running database. Having sufficient space
to allow you to pin objects into memory, in particular those frequently used
on large objects, can limit the amount of disk access needed. Of course, it's
difficult by any stretch of the imagination to justify pinning a few billion-row
tables into memory, even if it were possible, but in this, as with all things,
moderation is the key.
Disk I/O Tuning
The proper placing of datafiles and aptly sizing them to provide the maximum
throughput of data from disk to the application can be an important step in
tuning. Again, placing active files on controllers with the best throughput
can mean your users won't notice the application slowing down. The best
throughput possible can mean your application won't be noticeably slowed
down.
Chapter 4: 'Database tuning: Making it sing'
Visit the McGraw-Hill website for a detailed description and to learn how to purchase this title.
This was first published in February 2006