How to deal with the problem of extremely slow business response caused by frequent changes in ORACLE DML execution plan

Recently, a very strange thing encountered in oracle rac maintenance is that the business is occasionally extremely slow, but the server load and database load are very low, there are no obvious errors in the database and host logs, and there is no session congestion within the database. This article is hereby recorded for later reference!

Background: since December, the application begins to give feedback, and it will be very slow to issue documents every time a new product is released. Later, in order to temporarily solve the problem, the business is directed to a single node, and it is found that the business distribution speed returns to normal. Later, after a week or so, the issuing speed of the documents was very slow again. However, the server load and database load are very low, there are no obvious errors in the database and host logs, and there is no session congestion within the database. In order to resume business as soon as possible, the database service was restarted

Business returns to normal for the time being. However, three or four days later, that is, today, there was another slow issuance of documents.

Problem analysis:

1. Based on the above background, there is no high load on the database and host, especially when there is no session congestion inside the database. What I can think of is that the SQL execution plan may have changed.

2. View the SQL statement executed by the service bearer node

3. View the execution plan of sql-5216pwt38ckhp

Excellent implementation plan, a document issued at 3-8 o'clock can be executed nearly 30 times

-- after the change, the inefficient implementation plan will be issued at 3-10:00 at a time, and only one time will be completed.