SQL执行计划与直方图关系研究

Research on Relationship between SQL Execution Plan and Histogram

sql语句调优是数据库性能调优的重要方面.要达到同样的执行结果,sql语句有多种写法,不同的写法其性能差别很大.即使同一个sql语句,oracle也有多种途径去执行,即有多个执行计划.oracle比较这多个执行计划的性能优劣,耗费资源多少,来选择最优的执行计划.oracle在评估各个执行计划的性能时,需要借助sql语句执行的环境,即统计信息,来计算出每个执行计划耗费资源的多少.因此,尽可能收集准确的统计信息,对于oracle能否选择最优的执行计划,至关重要.其中,直方图的收集与否起着很重要的作用.本文通过实验来验证直方图对sql执行计划的影响,从而明确何种情况下需要收集直方图.

Sql statement tuning is an important aspect of database performance tuning. To achieve the same effects, sql statement has a variety of wording, with different performance for the different wording. Even with only one sql statement, the oracle also has a variety of ways to implement. That is, there are multiple execution plans. The oracle compares the performance of these multiple execution plans, the cost of resources, to select the optimal execution plan. In assessing the performance of each implementation plan, the oracle needs the implementation of sql statement with the environment, that is statistical information to calculate the cost of the number of resources for each implementation plan.Therefore, it is critically important for the oracle to choose the best implementation plan to collect as much as possible accurate statistical information. Among them, the collection of the histogram plays a very important role. The experiment verifies the impact of the histogram for the sql implementation plan, which clears the circumstances under which it needs to collect histograms.