Background processing to provide automated database query tuning

Patent metadata

Field	Value
Publication number	US-11232102-B2
Application number	US-201615295319-A
Country	US
Kind code	B2
Filing date	Oct 17, 2016
Priority date	Oct 17, 2016
Publication date	Jan 25, 2022
Grant date	Jan 25, 2022

Abstract

A database query is received at a first time when a database is in a first state. The database query is analyzed to determine whether it has one or more characteristics that fall within a historical top range of database queries. If the database query falls within the historical top range of database queries the query is stored for later query tuning activity. The query is executed against the database in the first state. Query tuning recommendations are generated for stored database queries that fall within the historical top range of database queries. A subsequent request is received at a second time when a database is in a second state. The subsequent database query is executed with at least one of the query tuning recommendations against the database in the second state.

First claim

What is claimed is:

1. A method comprising:
receiving, with a computing platform having one or more processors coupled with at least one physical memory device, a request comprising at least a database query at a first time when a database is in a first state, the database query having an associated query identifier (ID);
analyzing the database query with the one or more processors to determine whether the database query has one or more corresponding physical resource requirements that fall within a historical top number of database queries in terms of usage of one or more pre-selected physical resources by checking a query cache storing queries above a pre-selected threshold of consumption of physical resource usage;
storing at least the database query for later query tuning activity if the database query falls within the historical top number of database queries in terms of usage of one or more pre-selected physical resources;
executing, with the one or more processors, the database query against the database in the first state;
generating, with the one or more processors and asynchronously with respect to the executing of the database query, one or more query tuning recommendations for one or more stored database queries that fall within the historical top number of database queries in terms of consumption of physical resource usage;
providing, via a user interface, threshold selectivity input data for tables to which the one or more query tuning recommendations have been generated;
receiving, via the user interface, input to initiate creation of at least one custom index based on the one or more query tuning recommendations;
executing, with the one or more processors, a subsequent database query modified according to at least one of the query tuning recommendations against the database.

2. The method of claim 1 wherein the database query physical resource requirements comprise at least query running time.

3. The method of claim 1 wherein the database query physical resource requirements comprise at least a number of buffer gets corresponding to the database query.

4. The method of claim 1 wherein the database query physical resource requirements comprise at least processor consumption corresponding to the database query.

5. The method of claim 1 wherein the database query physical resource requirements comprise at least a number of disk reads corresponding to the database query.

6. The method of claim 1 wherein the historical top range of database queries is based on a percentile of rating of one or more selected physical resource requirements.

7. The method of claim 1 wherein the historical top range of database queries is based on a numerical rating of one or more selected characteristics.

8. A non-transitory computer-readable medium having stored thereon instructions that, when executed by one or more processors, are configurable to cause the one or more processors to:
receive, with a computing platform having one or more processors coupled with at least one physical memory device, a request comprising at least a database query at a first time when a database is in a first state, the database query having an associated query identifier (ID);
analyze the database query with the one or more processors to determine whether the database query has one or more corresponding physical resource requirements that fall within a historical top number of database queries in terms of usage of one or more pre-selected physical resources by checking a query cache storing queries above a pre-selected threshold of consumption of physical resource usage;
store at least the database query for later query tuning activity if the database query falls within the historical top number of database queries in terms of usage of one or more pre-selected physical resources;
execute, with the one or more processors, the database query against the database in the first state;
generate, with the one or more processors and asynchronously with respect to the executing of the database query, one or more query tuning recommendations for one or more stored database queries that fall within the historical top number of database queries in terms of consumption of physical resource usage;
provide, via a user interface, threshold selectivity input data for tables to which the one or more query tuning recommendations have been generated;
receive, via the user interface, input to initiate creation of at least one custom index based on the one or more query tuning recommendations;
execute, with the one or more processors, a subsequent database query modified according to at least one of the query tuning recommendations against the database.

9. The non-transitory computer-readable medium of claim 8 wherein the database query physical resource requirements comprise at least query running time.

10. The non-transitory computer-readable medium of claim 8 wherein the database query physical resource requirements comprise at least a number of buffer gets corresponding to the database query.

11. The non-transitory computer-readable medium of claim 8 wherein the database query physical resource requirements comprise at least processor consumption corresponding to the database query.

12. The non-transitory computer-readable medium of claim 8 wherein the database query physical resource requirements comprise at least a number of disk reads corresponding to the database query.

13. The non-transitory computer-readable medium of claim 8 wherein the historical top range of database queries is based on a percentile of rating of one or more selected characteristics.

14. The non-transitory computer-readable medium of claim 8 wherein the historical top range of database queries is based on a numerical rating of one or more selected characteristics.

15. A system comprising:
at least one memory device;
one or more processors coupled with the at least one memory device, the one or more processors to execute code configurable to receive, with a computing platform having one or more processors coupled with at least one physical memory device, a request comprising at least a database query at a first time when a database is in a first state, the database query having an associated query identifier (ID), to analyze the database query with the one or more processors to determine whether the database query has one or more corresponding physical resource requirements that fall within a historical top number of database queries in terms of usage of one or more pre-selected physical resources by checking a query cache storing queries above a pre-selected threshold of consumption of physical resource usage, to store at least the database query for later query tuning activity if the database query falls within the historical top number of database queries in terms of usage of one or more pre-selected physical resources, to execute, with the one or more processors, the database query against the database in the first state, to generate, with the one or more processors and asynchronously with respect to the executing of the database query, one or more query tuning recommendations for one or more stored database queries that fall within the historical top number of database queries in terms of consumption of physical resource usage, to provide, via a user interface, threshold selectivity input data for tables to which the one or more query tuning recommendations have been generated, to receive, via the user interface, input to initiate creation of at least one custom index based on the one or more query tuning recommendations, to execute, with the one or more processors, a

Assignees

• Salesforce Com Inc

Inventors

• Tariq Awais
• Mathur Rohitashva
• Dutta Arup
• Veeramani Prem
• Collins Jesse

Classifications

• G06F16/217

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

• G06F16/24534 Primary

  Query rewriting; Transformation · CPC title