Identifying high risk database statements in changing database environments

• US 9,734,200 B2
• Filed: 05/06/2014
• Issued: 08/15/2017
• Est. Priority Date: 09/14/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gangloor, Prabhaker, Minhas, Mughees, Yagoub, Khaled, Ahmed, Waleed
• Primary Examiner(s): Uddin, Mohammed R
• Application Number:
  US14/270,556
• Publication Number:
  US 20150324432A1
• Time in Patent Office: 1,197 Days
• Field of Search: 707718, 707719, 707720, 707713, 707999002
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

High-risk database statements are identified. A DBMS performs a series of experiments by generating sets of statistics describing data over which particular database statements run. The DBMS submits each of these sets of statistics to a query optimizer, which returns information for an execution plan that the optimizer would use to implement the statements given the submitted set of data statistics. If the DBMS determines that the optimizer changes the established execution plan for the database statements for any of the submitted sets of statistics, the DBMS performs one or more actions to mitigate the risk of changing execution plans, such as automatically tuning the database statements or the entire workload, and/or sending information about the database statements to a database administrator. Techniques are also described for calculating diagnostic values based on the results of such experiments, which quantify the sensitivity of the execution plans to changes in data statistics.

Claims

1. A computer-executed method comprising:

based, at least in part, on a first set of statistics comprising a first hypothetical value for a particular attribute of particular data in a database, said particular attribute specifying how much data is hypothetically stored in one or more particular tables or indexes in the database, identifying a first execution plan that a database management system generates for particular database statements that run over the particular data;

after identifying the first execution plan, and based, at least in part, on a second set of statistics comprising a second hypothetical value, for the particular attribute of the particular data, identifying a second execution plan that the database management system generates for the particular database statements;

wherein the first hypothetical value is different than the second hypothetical value;

in response to detecting that the second execution plan is different than the first execution plan, automatically tuning the particular database statements;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein:

attributes, other than the particular attribute, of the first and second sets of statistics have the same values among corresponding attributes.

3. The method of claim 1, further comprising:

determining, based at least in part on historical statistics for the particular data, a likelihood that the particular attribute of the particular data will reach, during a future time period, the second hypothetical value for the particular attribute;

wherein automatically tuning the particular database statements is further based, at least in part, on determining that the likelihood, that the particular attribute for the particular data will reach, during the future time period, the second hypothetical value for the particular attribute, exceeds a particular threshold.

4. The method of claim 1, further comprising:

in response to detecting that the second execution plan is different than the first execution plan, further performing;

generating a group of sets of statistics for the particular data;

wherein each of the sets of statistics of the group includes a different hypothetical value for the particular attribute;

wherein the hypothetical values for the particular attribute in the sets of statistics in the group fall between the first hypothetical value for the particular attribute, and the second hypothetical value for the particular attribute;

identifying a third execution plan that the database management system generates for the particular database statements based, at least in part, on a third set of statistics from the group of sets of statistics;

identifying a fourth execution plan that the database management system generates for the particular database statements based, at least in part, on a fourth set of statistics from the second group of sets of statistics; and

in response to detecting that the third execution plan is different than the fourth execution plan, automatically recording information from the group of sets of statistics.

5. The method of claim 1, wherein the database management system generates the first and second execution plans in Explain Plan mode.

6. A computer-executed method comprising:

generating a plurality of execution plans for particular database statements;

wherein the plurality of execution plans are based on a plurality of sets of statistics that characterize particular data, stored in a database, over which the particular database statements are configured to run;

wherein the plurality of execution plans includes, for each set of statistics in the plurality of sets of statistics, an execution plan that is based on the set of statistics;

wherein a first set of statistics of the plurality of sets of statistics comprises a first hypothetical value for a particular attribute;

wherein a second set of statistics of the plurality of sets of statistics comprises a second hypothetical value for the particular attribute;

wherein the first hypothetical value and the second hypothetical value are different;

generating a diagnostic value for the particular database statements based, at least in part, on;

a change between (a) the first hypothetical value for the particular attribute and (b) the second hypothetical value for the particular attribute, and

a change between (a) a cost of a first execution plan, of the plurality of execution plans, that is generated based on the first set of statistics and (b) a cost of a second execution plan, of the plurality of execution plans, that is generated based on the second set of statistics; and

in response to determining that the diagnostic value exceeds a particular threshold, performing one or more of a set of actions comprising;

automatically tuning the particular database statements, and

including information for the particular database statements in a report set of information;

wherein the method is performed by one or more computing devices.

7. The method of claim 6, wherein the particular attribute represents volume of the particular data.

8. The method of claim 6, wherein:

the particular threshold is a first threshold;

the method further comprises;

in response to determining that the diagnostic value exceeds the first threshold, automatically tuning the particular database statements; and

in response to determining that the diagnostic value exceeds a second threshold;

including information for the particular database statements in the report set of information, and

sending the report set of information to a database administrator;

wherein the first threshold is lower than the second threshold.

9. One or more non-transitory computer-readable media storing one or more sequences of instructions which, when executed by one or more processors, cause:

based, at least in part, on a first set of statistics comprising a first hypothetical value for a particular attribute of particular data in a database, said particular attribute specifying how much data is hypothetically stored in one or more particular tables or indexes in the database, identifying a first execution plan that a database management system generates for particular database statements that run over the particular data;

after identifying the first execution plan, and based, at least in part, on a second set of statistics comprising a second hypothetical value, for the particular attribute of the particular data, identifying a second execution plan that the database management system generates for the particular database statements;

wherein the first hypothetical value is different than the second hypothetical value;

in response to detecting that the second execution plan is different than the first execution plan, automatically tuning the particular database statements.

10. The one or more non-transitory computer-readable media of claim 9, wherein:

attributes, other than the particular attribute, of the first and second sets of statistics have the same values among corresponding attributes.

11. The one or more non-transitory computer-readable media of claim 9, wherein the one or more sequences of instructions further comprise instructions which, when executed by one or more processors, cause:

determining, based at least in part on historical statistics for the particular data, a likelihood that the particular attribute of the particular data will reach, during a future time period, the second hypothetical value for the particular attribute;

wherein automatically tuning the particular database statements is further based, at least in part, on determining that the likelihood, that the particular attribute for the particular data will reach, during the future time period, the second hypothetical value for the particular attribute, exceeds a particular threshold.

12. The one or more non-transitory computer-readable media of claim 9, wherein the one or more sequences of instructions further comprise instructions which, when executed by one or more processors, cause:

in response to detecting that the second execution plan is different than the first execution plan, further performing;

generating a second group of sets of statistics for the particular data;

wherein each of the sets of statistics of the group includes a different hypothetical value for the particular attribute;

wherein the hypothetical values for the particular attribute in the sets of statistics in the group fall between the first hypothetical value for the particular attribute, and the second hypothetical value for the particular attribute;

identifying a third execution plan that the database management system generates for the particular database statements based, at least in part, on a third set of statistics from the group of sets of statistics;

identifying a fourth execution plan that the database management system generates for the particular database statements based, at least in part, on a fourth set of statistics from the group of sets of statistics;

in response to detecting that the third execution plan is different than the fourth execution plan, automatically recording information from the group of sets of statistics.

13. The one or more non-transitory computer-readable media of claim 9, wherein the database management system generates the first and second execution plans in Explain Plan mode.

14. One or more non-transitory computer-readable media storing one or more sequences of instructions which, when executed by one or more processors, cause:

generating a plurality of execution plans for particular database statements;

wherein the plurality of execution plans are based on a plurality of sets of statistics that characterize particular data, stored in a database, over which the particular database statements are configured to run;

wherein the plurality of execution plans includes, for each set of statistics in the plurality of sets of statistics, an execution plan that is based on the set of statistics;

wherein a first set of statistics of the plurality of sets of statistics comprises a first hypothetical value for a particular attribute;

wherein a second set of statistics of the plurality of sets of statistics comprises a second hypothetical value for the particular attribute;

wherein the first hypothetical value and the second hypothetical value are different;

generating a diagnostic value for the particular database statements based, at least in part, on;

a change between (a) the first hypothetical value for the particular attribute and (b) the second hypothetical value for the particular attribute, and

a change between (a) a cost of a first execution plan, of the plurality of execution plans, that is generated based on the first set of statistics and (b) a cost of a second execution plan, of the plurality of execution plans, that is generated based on the second set of statistics; and

in response to determining that the diagnostic value exceeds a particular threshold, performing one or more of a set of actions comprising;

automatically tuning the particular database statements, and

including information for the particular database statements in a report set of information.

15. The one or more non-transitory computer-readable media of claim 14, herein the particular attribute represents volume of the particular data.

16. The one or more non-transitory computer-readable media of claim 14, wherein:

the particular threshold is a first threshold;

the one or more sequences of instructions further comprise instructions which, when executed by one or more processors, cause;

in response to determining that the diagnostic value exceeds the first threshold, automatically tuning the particular database statements; and

in response to determining that the diagnostic value exceeds a second threshold;

including information for the particular database statements in the report set of information, and

sending the report set of information to a database administrator;

wherein the first threshold is lower than the second threshold.