Common Tuning Problems

The most common tuning problems:

1. Inefficient or high-load SQL statements
2. Suboptimal use of Oracle Database by the application
3. Undersized memory structures
4. Concurrency issues
5. I/O issues 
6. Database configuration issues
7. Short-lived performance problems
8. Degradation of database performance over time 
9. Unexpected performance regression after environment changes 
10. Locking issues

Tuning inefficient or high-load SQL statements has an wide impact that can reduce memory use, CPU, and IO resources. SQL tuning issues includes poorly written SQL, ineffective use of indexes, access path costs, and sorting. In this course, we assume that the DBA has little or no opportunity to change the SQL statements. 

Problems such as establishing new database connections repeatedly, excessive SQL parsing, and high levels of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. These are all poor use of the database by the application. 

Memory issues are high on the list of instance tuning problems. Proper sizing of the System Global Area (SGA) including the Shared pool and Buffer cache, and the Process Global Area (PGA) reduce contention for memory resources and indirectly reduces IO and CPU. 

A high degree of concurrent activities, multiple processes, or users might result in contention for shared resources that can be manifested in the forms of various types of waits. Many resources can only be accessed by only one process at a time. Several processes attempting to access the same resource creates contention.

In any database, I/O issues, such as database file layout on disk or RAID devices, can be a source of performance problems. In OLTP applications, the amount of redo and undo generated can create bottlenecks in memory or I/O.

Some problems are reported by users but may not be apparent from reports that span intervals of 30 minutes or longer. The Oracle Database has additional tools (Active Session History ASH) that allow the DBA to view statistics and metrics over small segments of time in the recent past. 

Many databases will have gradual changes: the number of user, the amount of data, the number of reports, and modules in use. These changes may lead to a degradation in performance. The proactive DBA will capture and save statistics sets from when the database is performing acceptably, to compare with statistics when the database performance is poor to identify the differences.

The environment of the database is seldom static. Patches, upgrades, new hardware, or changes to the instance parameters can change the performance of the database. Sometimes a change improves performance in one area and causes another area to degrade. 

Locking issues are not common problems, but when you have locking issues they become very important.