Oracle performance tuning -A Foolproof Query Execution plan with incorrect statistics

A Foolproof Query Execution plan with incorrect statistics

Introduction: The DBA team in a company gets many iritic visitors these days. A day in the life of an application DBA is to collect database table statistics during non-peak hours. The application of Capstone loads a lot of data into tables and truncates them which means the pattern of data in the tables change hysterically. Since the statistics are collected only once during the day, the query optimizer of the database comes to know about the table data patterning only post statistics collection. The data patterns see a lot of variances. Which is why the query execution plans revealed by the optimizer are not very reliable resulting in query performance to drop. Developers in company are alarmed by the query performance dropping without any prior signaling questioning, in turn, the DBA team’s competence to not being able to avert such dropping. The DBA team, on the other hand, are equally frustrated and wants avoidance of sampling statistics throughout the day which will eventually lead to a negative concussion on the overall database performance.

Solution: The solution to this problem lies in using adaptive plans in Oracle Database 12c.

Change in Plans

The adaptive plan concept can be explained by an example of the GPS devices in automobiles. The destination is fed to the GPS device and it comes out with the most faster route to reach the end-point. End-route if you experience a sudden traffic congestion or if your speed is slow, the GPS tracks to suggest a better route. The same methodology works on optimizer in the Oracle Database 12c. Optimizer relies on the latest available statistics to compute a query execution plan. The adaptive plan makes the optimizer during the query execution analyze if the actual conditions differ from existing statistics and come up with a better plan. Hence the optimizer starts following a separate plan post query execution. Not only that it comes with an array of alternative plans and chooses the best plan basis the data obtained on query execution.

Adaptive Plans

Adaptive plans hence come to the rescue. In the determination phase of a query, the optimizer computes an execution result basis the information gathered from statistics as per the usual practice of prior Oracle Database Releases. However, Oracle Database 12c optimizer is advanced as it computes substitute plans called subplans. Upon query execution, collector would cluster runtime statistics and any subplan which looks better than the resultant computed plan the optimizer makes a course correction and opts for the best subplan on offer.

The initialization parameter in the database_optimizer_adaptive_reporting_only controls such behavior. If the parameter is set to true, the optimizer will not adapt the execution plans (the default is false). The parameter was set to ‘true’ makes the optimizer plans not being adapted. Setting the parameter value to ‘true’ is an excellent way to inspect the behavior of the optimizer when it is not using adaptive plans using the following SQL:

alter session set optimizer_adaptive_reporting_only = true;

Conclusion

The computation by an optimizer results in the execution plan for a query. The execution plan determines the query’s performance. In previous releases of Oracle Database, statistics overshadowed quality of the plans made by the optimizer and unhealthy statistics often caused bad performance and further concerns. But with Oracle Database 12c, the optimizer is now smart to re-compute better plans by judging the effectiveness of the plan during query execution.