Support sql binding takes effect immediately for query using prepared-plan-cache

[dbsid]

Enhancement

With prepared plan cache enabled, when a prepared statement is submitted to tidb server, the plan cache is checked if there is an existing plan to re-use, otherwise, tidb server will compile a plan for the query. If there is sql binding for the query, the binding will be used when compiling the plan.

In real-world cases, it's no uncommon that a sub-optimal cached plan is observed for a prepared statement. To fix this issue, the DBA want to creates a sql binding to force an optimal plan, but the new sql binding will not take effect immediately. Since the existing plan in prepared plan cache will not be evicted and the new query execution keep re-using the current plan. Usually the only way to fix wrong plans issue is to rebuild the application connection.

To improve usability of the prepared plan cache and sql binding, I propose add two functions(after prepared plan cache goes GA):

1. Add an option to invalidate all the entries in prepared plan cache for the specific query, so that the new queries request will be re-compiled, and the newly created sql binding takes effect immediately.
2. Add a general api to invalidate a specific sql digest in prepared plan cache. In cases when the cached plan is not optimal, DBA can call this API to evict the existing plan, after re-gather the statistics or create a sql binding.

[Reminiscent]

Close due to #30169