By re-generating the execution plan, the execution plan deviation of Bound variables will be solved, resulting in the SQL Execution time being too long. The execution plan SQL

By re-generating the execution plan, the execution plan deviation of Bound variables will be solved, resulting in the SQL Execution time being too long. The execution plan SQL
Some SQL statements in the 11G environment of the Basic Elements (time, user, and problem) are very inefficient in the program, but they are very fast in plsql. by viewing the execution plan, it is found that different indexes are used, and the execution in the program is as follows:

The execution results in PLSQL are as follows:

We can see the difference. The outpatient expense record _ IX _ registration time index is the execution plan in plsql, And the outpatient expense record _ UQ_NO is the execution plan in the program, the SQL statements of the two are identical, but the only difference is that the former uses the bound variable, and the latter directly carries the parameter value for execution. The problem analysis problem is obvious. Because the execution plan generated by binding variables is different from the actual one, 11g originally had a function to bind variables, but it was obviously useless here, analysis is most likely due to a problem with the statistical information. You need to re-collect the statistical information of the relevant business tables, so that the corresponding SQL statement can re-generate the execution plan.
Solution steps: Re-collect the statistics of the relevant business tables. Here we should note that we recommend that you collect 100% of the data. If sampling is used, the histogram may not be generated accurately, which will also lead to execution plan deviation, execute the following statement:

Exec dbms_stats.gather_table_stats (ownname => 'zlhis ', tabname => 'outpatient expense record', estimate_percent => 100, method_opt => 'for all indexed columns size 254 ', no_invalidate => false, cascade => true, force => true, degree => 4 );

Here we add no_invalidate => false. This parameter indicates that after collecting statistics, the execution plan of the SQL statement designed for this object is regenerated. After the execution is complete, check that the execution plan is correct again, the system runs normally.
Key knowledge points include SQL statements bound to variables. If execution plan deviation occurs, we recommend that you collect the statistics of related business tables again.