Performance Feedback
Another form of re-optimization is Performance Feedback, which helps to improve the degree of parallelism chosen for repeated SQL statements when Automatic Degree of Parallelism (Auto DOP)1 is enabled with parallel_degree_policy = ADAPTIVE.
When Auto DOP is enabled in adaptive mode, during the first execution of a SQL statement, the optimizer determines if the statement should execute in parallel and if so what parallel degree should be used. The parallel degree is chosen based on the estimated performance of the statement. Additional performance monitoring is also enabled for the initial execution of any SQL statement the optimizer decides to execute in parallel.
At the end of the initial execution, the parallel degree chosen by the optimizer is compared to the parallel degree computed base on the actual performance statistics (e.g. CPU-time) gathered during the initial execution of the statement. If the two values vary significantly then the statement is marked for re-optimization and the initial execution performance statistics are stored as feedback to help compute a more appropriate degree of parallelism for subsequent executions.
If performance feedback is used for a SQL statement then it is reported in the note section under the plan as shown in Figure 14.
Figure 14:
No comments:
Post a Comment