SQL with SQL Plan Change

A live database is a constant moving target and we need to keep an open mind about changes which happen all the time, such as data changes, data stats changes, database parameter changes, database patch or upgrade, etc. Oracle’s CBO engine automatically evolves SQL plan as a mechanism to respond to those changes, which could lead to SQL plan deterioration and performance issue.

SQL plan change will cause database performance issue if the new plan requires more resources and/or more time for running the same SQL statement and processing the same amount of data. The time model analysis and resource consumption analysis need to be applied to both the old plan and new plan, in order to identify if SQL plan change is the cause of performance issue.

Detecting SQL plan change serves as the start of investigation, while ensuring an optimal SQL plan marks as the happy ending of SQL tuning.

 Case Study Summary

The case scenarios include:

 Case 1 : Detecting SQL plan change from both AWR and ASH data

Both AWR data and ASH data collect information about how SQL statements run on database. However, they have different ‘top SQL’ criteria and therefore there are differences between them. We need to check both AWR data and ASH data in order to get a better understanding about SQL plan change.

 Case 2 : Compare time model and resource consumption of old SQL plan with that of new plan

SQL plan may change to either positive or negative direction. The impact of SQL plan change depends on the time difference and resource consumption difference between the old plan and new plan.

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us