Identifying high risk database statements in changing database environments

Patent metadata

Field	Value
Publication number	US-9734200-B2
Application number	US-201414270556-A
Country	US
Kind code	B2
Filing date	May 6, 2014
Priority date	Sep 14, 2007
Publication date	Aug 15, 2017
Grant date	Aug 15, 2017

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.

First claim

What is claimed is:

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 like

Assignees

• Oracle Int Corp

Inventors

• Gangloor Prabhaker
• Minhas Mughees
• Yagoub Khaled
• Ahmed Waleed

Classifications

• G06F16/217

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• Y10S707/99932

  Access augmentation or optimizing · CPC title

• G06F16/2462

  Approximate or statistical queries · CPC title

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/2455

  Query execution · CPC title