Failure Description

Customer feedback a set of business database often appear card slow, even input characters will appear delayed effect, 

restarted, the database query speed has been improved, but with the increase in the frequency of use, the speed of slow again. 

Check all JSP web pages found that the record set, statement and database connection have been released.

Troubleshooting

After a series of checks showed no abnormality, the engineer decided to generate an AWR report for in-depth analysis, 

and then found the problem, as shown below:

Look at the graph and analyze it according to AWR details: if soft parse % is close to 99% and Execute to Parse % is less than 90%, it means that the execution parse ratio is low, and you can reduce the soft parse by using static SQL, dynamic bindings, session_cached_cursor, open_cursors, and other methods.

l  Check session_cached_cursor and open_cursors, and find that the value of session_cached_cursor is 40, with a utilization rate of 100%, and the value of open_cursors is 300, with a utilization rate of 10%. l Change session_cached_cursor to 100, and the utilization rate of open_cursors is still 100%;

l  Change the value of session_cached_cursor to 100, the utilization rate is still 100%, and the utilization rate of open_cursors becomes about 35%, but the access system is still slow. l Try to change the value of session_cached_cursor to 100, the utilization rate is still 100%, and the utilization rate of open_cursors becomes about 35%;

l  Try to change the session_cached_cursor to 200, the utilization rate reaches 93%, and the open_cursors utilization rate reaches about 65%.

It can be deduced that the session_cached_cursor parameter is not set enough to cause the database to be stuck and slow, 

after resetting the parameter, the database's sticking and slowness can be recovered.

The previous problem of using Navicat to connect to the database locally on the server is also solved. 

The reference values are session cursor cache hit and parse count (total), the higher the hit rate, the less the parsing.

Lesson Learned

Engineers should try to collect system/database information correctly and comprehensively during daily inspections 

as well as during each troubleshooting process, so as to facilitate troubleshooting after encountering faulty problems, 

and to give as many references as possible for troubleshooting solutions.

For more information, please visit Antute's official website:cr93.wakatter.com