Identify 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:
Oracle logical table design tuning: Eliminating extra SQL join operations can dramatically improve performance of SQL queries.
Oracle physical table tuning: This can also have a huge impact on performance. Physical table tuning involves many areas:
Add indexes to eliminate full-table scans
Add multiple table freelists to eliminate table header contention
Cluster tables to improve query performance
Re-sequence physical rows to reduce disk I/O
Alter table parameters (PCTFREE, PCTUSED) to improve insert/update performance
Using parallel query to speed queries
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.
SGA tuning: Tuning the memory regions of Oracle, such as the library cache and buffer pool can help speed Oracle performance.
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.