Tracking and maintaining expression statistics across database queries

• US 10,372,706 B2
• Filed: 05/04/2016
• Issued: 08/06/2019
• Est. Priority Date: 07/29/2015
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chavan, Shasank K., Mishra, Aurosish, Zait, Mohamed, Chakkappen, Sunil P., Tuzla, Can, Yan, Jiaqi
• Primary Examiner(s): Le, Thu Nguyet T
• Application Number:
  US15/146,798
• Publication Number:
  US 20170031967A1
• Time in Patent Office: 1,189 Days
• Field of Search: 707713
• CPC Class Codes: G06F 12/0802 Addressing of a memory level in which the access to the desired data or data block requires associative addressing means, e.g. caches
• CPC Class Codes: G06F 16/2255 Hash tables
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2454 Optimisation of common expressions
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 2212/60 Details of cache memory

Abstract

Techniques are described for maintaining an expression statistics store that stores and updates metadata values for query expressions based on the occurrence of those query expressions within queries. In an embodiment, a database server instance receives a database query. In response, the database server instance identifies expressions within the database queries. The database server instance then determines whether an expression statistics store includes an entry for the particular expression. Responsive to determining that the expression statistics store includes an entry for the particular expression, the database server instance updates at least one metadata value in the entry based on the occurrence of the particular expression. Responsive to determining that the expression statistics store does not include an entry for the particular expression, the database server instance adds an entry for the particular expression.

Claims

1. A method comprising:

maintaining, by a database server instance, an expression statistics store that includes metadata values for each expression of a plurality of expressions;

wherein each expression of the plurality of expressions, when evaluated by the database server instance, causes the database server instance to perform a corresponding operation using values from at least one column of a table in a database managed by the database server instance;

receiving, by the database server instance, a database query;

parsing, by the database server instance, the database query to identify expressions within the database query;

based on the parsing, identifying an occurrence of a particular expression within the database query;

in response to identifying the occurrence of the particular expression within the database query, the database server instance updating the expression statistics store to reflect the occurrence of the particular expression;

determining, from the metadata values maintained for the particular expression, a time-period-based cumulative cost for the particular expression;

wherein the time-period-based cumulative cost is determined, based at least in part, on a fixed cost per evaluation of the particular expression and on an evaluation count that tracks how many times the particular expression was evaluated;

based, at least in part, on the time-period-based cumulative cost for the particular expression, performing at least one of;

storing an evaluation result for the particular expression in an in-memory cache, and modifying an execution plan of a query that includes the particular expression to access the evaluation results in the in-memory cache rather than evaluate the particular expression;

rearranging an order in which expressions in a query that includes the particular expression are evaluated;

changing a level of compression applied to a column or database object referenced in the particular expression;

determining where to store a column or database object referenced in the particular expression;

or

determining a format for storing a column or database object referenced in the particular expression.

2. The method of claim 1 wherein updating the expression statistics store to reflect the occurrence of the particular expression comprises:

the database server instance performing the steps of;

determining whether the expression statistics store includes an entry for the particular expression;

responsive to determining that the expression statistics store includes an entry for the particular expression, updating at least one metadata value in the entry based on the occurrence of the particular expression; and

responsive to determining that the expression statistics store does not include an entry for the particular expression, adding to the expression statistics store an entry for the particular expression, wherein the entry includes metadata values that are based on the occurrence of the particular expression.

3. The method of claim 2, wherein the step of updating at least one metadata value in the entry based on the occurrence of the particular expression comprises updating a counter that tracks how many times the particular expression has occurred across different queries.

4. The method of claim 2, further comprising:

receiving a second query that requests records for expressions that satisfy a set of criteria;

in response to receiving the second query;

comparing the set of criteria to at least one metadata value in or derived from the entry to determine whether a cost associated with the particular expression satisfies a threshold;

responsive to determining that the cost associated with the particular expression satisfies the threshold, including the entry for the particular expression in a result set for the second query.

5. The method of claim 4, wherein the at least one metadata value corresponds to a cost that is based on how many times the particular expression was evaluated during a particular period, wherein the particular period is one of:

a period that begins when the particular expression was first recorded in the expression statistics store;

a period that begins a fixed period of time prior to current time;

a period that covers a range of snapshots of statistics maintained in the expression statistics store;

or

a period that begins at a point at which a client reset statistics in the expression statistics store.

6. The method of claim 1, the method further comprising:

performing at least one evaluation of the particular expression during execution of the database query; and

updating at least one metadata value in an entry, within the expression statistics store, for the particular expression based on the at least one evaluation of the particular expression.

7. The method of claim 6, wherein the step of updating at least one metadata value in the entry for the particular expression based on the at least one evaluation comprises updating at least one of:

an evaluation count that indicates how many times the particular expression has been evaluated,

a selectivity rate of the particular expression, or

timing information that indicates when the particular expression was last evaluated.

8. The method of claim 6 wherein the expression statistics store comprises a ranking of a plurality of predicates based upon a processing cost of evaluating each predicate during a query evaluation and wherein performing at least one evaluation of the particular expression during execution of the database query comprises evaluating the plurality of predicates based upon the ranking of the plurality of predicates.

9. The method of claim 1 wherein, based on metadata values stored in the expression statistics store for the particular expression, storing an evaluation result for the particular expression in an in-memory cache, and modifying an execution plan of a query that includes the particular expression to access the evaluation results in the in-memory cache rather than evaluate the particular expression.

10. The method of claim 1 wherein, based on metadata values stored in the expression statistics store for the particular expression, rearrange an order in which expressions in a query that includes the particular expression are evaluated.

11. The method of claim 1 wherein, based on metadata values stored in the expression statistics store for the particular expression, determine where to store a column or database object referenced in the particular expression.

12. The method of claim 1 wherein, based on metadata values stored in the expression statistics store for the particular expression, determine a format for storing a column or database object referenced in the particular expression.

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

maintaining, by a database server instance, an expression statistics store that includes metadata values for each expression of a plurality of expressions;

wherein each expression of the plurality of expressions, when evaluated by the database server instance, causes the database server instance to perform a corresponding operation using values from at least one column of a table in a database managed by the database server instance;

receiving, by the database server instance, a database query;

parsing, by the database server instance, the database query to identify expressions within the database query;

based on the parsing, identifying an occurrence of a particular expression within the database query;

in response to identifying the occurrence of the particular expression within the database query, the database server instance updating the expression statistics store to reflect the occurrence of the particular expression;

determining, from the metadata values maintained for the particular expression, a time-period-based cumulative cost for the particular expression;

wherein the time-period-based cumulative cost is determined, based at least in part, on a fixed cost per evaluation of the particular expression and on an evaluation count that tracks how many times the particular expression was evaluated;

based, at least in part, on the time-period-based cumulative cost for the particular expression, performing at least one of;

storing an evaluation result for the particular expression in an in-memory cache, and modifying an execution plan of a query that includes the particular expression to access the evaluation results in the in-memory cache rather than evaluate the particular expression;

rearranging an order in which expressions in a query that includes the particular expression are evaluated;

changing a level of compression applied to a column or database object referenced in the particular expression;

determining where to store a column or database object referenced in the particular expression;

or

determining a format for storing a column or database object referenced in the particular expression.

14. The one or more non-transitory computer-readable media of claim 13 wherein updating the expression statistics store to reflect the occurrence of the particular expression comprises:

the database server instance performing the steps of;

determining whether the expression statistics store includes an entry for the particular expression;

responsive to determining that the expression statistics store includes an entry for the particular expression, updating at least one metadata value in the entry based on the occurrence of the particular expression; and

responsive to determining that the expression statistics store does not include an entry for the particular expression, adding to the expression statistics store an entry for the particular expression, wherein the entry includes metadata values that are based on the occurrence of the particular expression.

15. The one or more non-transitory computer-readable media of claim 14, wherein the step of updating at least one metadata value in the entry based on the occurrence of the particular expression comprises updating a counter that tracks how many times the particular expression has occurred across different queries.

16. The one or more non-transitory computer-readable media of claim 14, wherein the metadata values identify a time-period-based cumulative cost for the particular expression;

wherein the time-period-based cumulative cost is determined, based at least in part, on a fixed cost per evaluation of the particular expression and on an evaluation count that tracks how many times the particular expression was evaluated.

17. The one or more non-transitory computer-readable media of claim 14, storing further instructions that cause:

receiving a second query that requests records for expressions that satisfy a set of criteria;

in response to receiving the second query;

comparing the set of criteria to at least one metadata value in or derived from the entry to determine whether a cost associated with the particular expression satisfies a threshold;

responsive to determining that the cost associated with the particular expression satisfies the threshold, including the entry for the particular expression in a result set for the second query.

18. The one or more non-transitory computer-readable media of claim 17, wherein the at least one metadata value corresponds to a cost that is based on how many times the particular expression was evaluated during a particular period, wherein the particular period is one of:

a period that begins when the particular expression was first recorded in the expression statistics store;

a period that begins a fixed period of time prior to current time;

a period that covers a range of snapshots of statistics maintained in the expression statistics store;

or

a period that begins at a point at which a client reset statistics in the expression statistics store.

19. The one or more non-transitory computer-readable media of claim 13, storing further instructions that cause:

performing at least one evaluation of the particular expression during execution of the database query; and

updating at least one metadata value in an entry, within the expression statistics store, for the particular expression based on the at least one evaluation of the particular expression.

20. The one or more non-transitory computer-readable media of claim 19, wherein the step of updating at least one metadata value in the entry for the particular expression based on the at least one evaluation comprises updating at least one of:

an evaluation count that indicates how many times the particular expression has been evaluated,

a selectivity rate of the particular expression, or

timing information that indicates when the particular expression was last evaluated.