Common performance problems in Oracle databases

Through the following Oracle performance tuning method, you should be able to avoid these problems. If you encounter these problems, repeat the Oracle Performance Tuning steps mentioned above, or read appropriate chapters to solve these problems.
 
CPU bottleneck
 
Is the performance of applications low because the system is limited by CPU? The performance problems caused by CPU can be diagnosed by ADDM. You can also use the performance page in the Enterprise Manager to identify CPU bottlenecks.
 
Insufficient memory structure
 
Oracle memory structure-such as system global zone (SGA), program global zone (PGA) and buffer cache is large enough? The performance problem caused by insufficient memory structure can be diagnosed by ADDM. You can also use the performance page of the Enterprise Manager to confirm memory usage problems.
 
I/O capacity problems
 
Has the performance of the I/O subsystem reached the expected value? Performance problems caused by I/O capacity problems can be diagnosed by ADDM. You can also use the performance page of the Enterprise Manager to confirm Disk/I/O problems.
 
Oracle databases used by applications do not meet the best standards
 
Does the application use the Oracle database below the best standards? For example, the problem of establishing a new database connection multiple times, SQL over-resolution, and advanced competition for a small amount of data may greatly reduce the performance of the application. These problems can also be diagnosed by ADDM, you can also use the performance page of the Enterprise Manager to monitor top-level activities in different directions-including SQL, session, service, module, and behavior.
 
Concurrency Problems
 
Does the database cause performance problems due to highly concurrent activities? High-concurrency activities may cause shared resource contention. If a lock is generated or a buffer cache is waiting, ADDM can also diagnose performance problems caused by concurrency problems, you can also use "top-level sessions" in the Enterprise Manager to confirm concurrency issues.
 
Database Configuration Problems
 
Is the database configured in the optimal state? For example, is there an error in the log file size, archiving problems, excessive checkpoints, or inadequate configuration of parameters? ADDM can diagnose performance problems caused by database configuration.
 
Transient Performance problems
 
Have users complained about transient or intermittent performance problems? Depending on the gap between two snapshots in AWR, transient performance problems may not be captured by ADDM. You can use the Active session history report to identify transient performance problems.
 
Database performance deteriorated over time
 
Does the database performance problem deteriorate over time? For example, have you or your users noticed that the database performance is not as good as six months ago? You can generate an AWR period comparison report, with a relatively low performance period and a stable performance period to check whether the configuration, workload configuration files, and statistics vary between these two periods, this technology will help you identify the cause of performance degradation.
 
Low-efficiency or high-load SQL statements
 
Does SQL statements over-use system resources cause system performance degradation? Performance problems caused by high-load SQL statements can be captured by ADDM. In the Enterprise Manager, you can use "top-level SQL" to find high-load SQL statements, once these high-load SQL statements are identified, you can use the SQL adjustment consultant to optimize these high-load SQL statements.
 
Object Competition
 
Is the database object the source of the bottleneck? Because they are continuously accessed, ADDM can diagnose performance problems caused by object contention. You can also use SQL access advisor to optimize the data access path of these objects.
 
Unexpected performance degradation after adjusting SQL statements
 
After the SQL statement is adjusted, is its performance regressing? Adjusting SQL statements may change the execution plan of SQL statements, resulting in a significant reduction in SQL performance. In some cases, changing SQL statements may improve SQL Performance, changes may result in SQL statement performance degradation. Before the changes expire in the production system, you can analyze and adjust the impact of SQL statements on system performance in a test system.

Author: ERDP Technical Architecture"