Oracle Database Speed Optimization Solution

04 Mar

1. Failure Description

The customer reported that a business database frequently experienced performance issues, where operations became slow, and even typing characters resulted in delayed responses. After restarting the system, the query speed improved temporarily, but as usage increased, the slowdown returned. Upon reviewing all JSP web pages, it was confirmed that record sets, statements, and database connections were properly released.

2. Troubleshooting

After performing a series of checks without identifying any abnormalities, the engineer decided to generate an AWR (Automatic Workload Repository) report for further analysis. The report revealed the following issues:

The analysis of the AWR details showed that the soft parse % was close to 99%, while the Execute to Parse % was less than 90%. This indicates a low execution-to-parse ratio, which can be mitigated by using static SQL, dynamic binding, session_cached_cursor, open_cursors, and other techniques.
Upon checking the values for session_cached_cursor and open_cursors, it was found that:
- session_cached_cursor was set to 40, with a utilization rate of 100%.
- open_cursors was set to 300, with a utilization rate of 10%.
The following adjustments were made:
- Increasing session_cached_cursor to 100 led to 100% utilization of session_cached_cursor and a 35% utilization rate for open_cursors. However, system performance remained slow.
- Further increasing session_cached_cursor to 200 resulted in a 93% utilization rate for session_cached_cursor and approximately 65% utilization for open_cursors.

From this, it was deduced that the initial session_cached_cursor parameter was too low, causing the database to experience delays and performance issues. After adjusting the parameter, the database performance improved, and the slowness was resolved. Additionally, the previous issue of using Navicat to connect to the database locally on the server was also resolved. The key reference metrics were session cursor cache hit rate and parse count (total)—a higher hit rate reduces the need for parsing.

3. Lesson Learned

Engineers should ensure that system and database information is collected thoroughly during routine inspections and troubleshooting processes. This helps identify issues quickly and provides valuable reference points for diagnosing and resolving problems efficiently.