RelationalDBDesign RelationalDBDesign 



Performance Tuning   «Prev  Next»
Lesson 7What parts of Oracle require tuning?
ObjectiveIdentify five Oracle components that can be tuned.

Five Primary Tuning Activities

There are many hundreds of tuning strategies in Oracle. This course will focus your attention on those tuning activities that will provide the largest performance gains.
As a general rule, Oracle tuning activities can be listed in order of importance, with the most important activities first:
  1. Oracle logical table design tuning: Eliminating extra SQL join operations can dramatically improve performance of SQL queries.
  2. Oracle physical table tuning: This can also have a huge impact on performance. Physical table tuning involves many areas:
    1. Add indexes to eliminate full-table scans
    2. Add multiple table freelists to eliminate table header contention
    3. Cluster tables to improve query performance
    4. Re-sequence physical rows to reduce disk I/O
    5. Alter table parameters (PCTFREE, PCTUSED) to improve insert/update performance
    6. Using parallel query to speed queries
  3. SQL tuning: The tuning of SQL is also an important aspect of Oracle performance. As a general rule, SQL tuning is used to improve the access method used by the SQL optimizer to get the requested data.
  4. SGA tuning: Tuning the memory regions of Oracle, such as the library cache and buffer pool can help speed Oracle performance.
  5. Environment tuning: You can change the amount of RAM memory on the server, dedicate additional CPU, and improve network speed to improve Oracle performance.

Now that we have reviewed the main tuning areas in order of importance, let's look at the metrics of Oracle performance and see how to tell if your database is properly tuned.