Basic elements (time, user, problem) the user in the 11G environment has a section of SQL statements in the program is very inefficient execution, but in plsql execution is very fast, by looking at the execution plan, found that the use of different indexes caused, the program executes the following:
The effects performed in Plsql are as follows:
You can see the difference, the use of outpatient expense records _IX_ Registration time index is the execution plan in Plsql, the use of outpatient expense records _uq_no is the execution plan in the program, both SQL is exactly the same, the only thing is that the former uses the binding variable, the latter is directly with the parameter value execution. Problem analysisThe problem is obvious, because the binding variable generated by the execution plan and the actual deviation, 11g would have a binding variable snooping function, but obviously there is no use, the analysis is very likely to be a problem with the statistical information, need to re-collect the relevant business table statistics, the corresponding SQL to regenerate the execution plan.
Resolution StepsTo re-collect statistics on related business tables, we should note that the collection of recommendation 100%, if sampled, may not accurately generate histograms, and will also result in execution plan deviations, executing the following statement:
exec dbms_stats.gather_table_stats (ownname = ' zlhis ', tabname = ' outpatient fee record ', estimate_percent = 100,method_opt = ' For all indexed columns size 254 ', no_invalidate = False,cascade = True,force = True,degree =>4);
here we have addedno_invalidate =false, which indicates that the execution plan for the SQL statement that designed the object is regenerated after the statistics have been collected .Once the execution is complete, review the execution plan again and the system is working correctly.
Key points of knowledgeSQL statements that have bound variables, and if there is an execution plan bias, it is recommended that the statistics for the related business tables be re-collected.
Resolving binding variable execution plan skew by rebuilding execution plan causes SQL execution time to be too long
