Query tuning utilizing optimizer hints

Patent metadata

Field	Value
Publication number	US-11100104-B2
Application number	US-201916379350-A
Country	US
Kind code	B2
Filing date	Apr 9, 2019
Priority date	Apr 9, 2019
Publication date	Aug 24, 2021
Grant date	Aug 24, 2021

Abstract

A device may identify a query for a database system, wherein the query is a candidate for optimization. The device may determine a first execution plan according to a first hint for executing the query and a second execution plan according to a second hint for executing the query, wherein the first execution plan is associated with a first execution time and the second execution plan is associated with a second execution time. The device may select the first execution plan or the second execution plan as a selected execution plan. The device may generate, based on selecting the selected execution plan, a profile for the query, wherein the profile is based on the selected execution plan. The database system may execute the query according to the profile.

First claim

What is claimed is:

1. A method, comprising:
identifying, by a device, a first query and a second query for a database system,
wherein each of the first query the second query is a textual statement and a candidate for optimization;

determining, by the device and based on identifying the first query and the second query, that the first query and the second query have a same structure and a different literal value associated with a particular filter;
generating, by the device and based on determining that the first query and the second query have the same structure and the different literal value, a combined textual statement having the same structure and including a bind variable in place of the different literal value;
applying, by the device and based on generating the combined textual statement, a first hint to the combined textual statement and a second hint to the combined textual statement,
wherein the first hint and the second hint are for executing the first query and the second query;

determining, by the device, a first execution plan according to the first hint and a second execution plan according to the second hint,
wherein the first execution plan is associated with a first execution time and the second execution plan is associated with a second execution time;

selecting, by the device, the first execution plan or the second execution plan as a selected execution plan,
wherein the first execution plan is to be selected as the selected execution plan when the first execution time is less than the second execution time,
wherein the second execution plan is to be selected as the selected execution plan when the second execution time is less than the first execution time; and

generating, by the device and based on selecting the selected execution plan, a profile for at least one of the first query or the second query,
wherein the profile is based on the selected execution plan,
wherein the database system is to execute the at least one of the first query or the second query according to the profile.

2. The method of claim 1 , wherein the first query and the second query, prior to being identified as the candidate for optimization, are executed according to an initial execution plan that is associated with an initial execution time,
wherein the first query and the second query are identified as the candidate for optimization when the initial execution time satisfies a threshold value.

3. The method of claim 1 , wherein determining the first execution plan and the second execution plan comprises:
determining the first execution plan and the second execution plan using a query optimizer of the database system.

4. The method of claim 1 , wherein applying the first hint and the second hint comprises:
altering the combined textual statement with the first hint to obtain a first altered query and the combined textual statement with the second hint to obtain a second altered query, and
wherein determining the first execution plan and the second execution plan comprises:
determining the first execution plan based on processing the first altered query; and
determining the second execution plan based on processing the second altered query.

5. The method of claim 1 , further comprising:
determining a driving table associated with the at least one of the first query or the second query,
wherein the at least one of the first hint or the second hint, corresponding to the at least one of the first query or the second query, is related to the driving table.

6. The method of claim 1 , further comprising:
determining one or more indexes of a driving table associated with the at least one of the first query or the second query,
wherein the at least one of the first hint or the second hint is related to the one or more indexes.

7. The method of claim 1 , wherein the first hint provides a first directive to determine the first execution plan, and the second hint provides a second directive to determine the second execution plan, according to one or more of:
a particular number of rows that are to be returned,
a rule based optimization,
a particular table that is to be used first in a table join order,
a particular index that is to be used for an index scan,
a parallel processing using a particular number of processors,
a particular table that is to be used as an inner table of a nested loop join operation,
a particular access path, or
a second particular index that is to be used for a second index scan in a particular sort direction.

8. A non-transitory computer-readable medium storing instructions, the instructions comprising:
one or more instructions that, when executed by one or more processors, cause the one or more processors to:
identify a first query and a second query for a database system,
wherein each of the first query and the second query is a textual statement and a candidate for optimization;

determine, based on identifying the first query and the second query, that the first query and the second query have a same structure and a different literal value associated with a particular filter;
generate, based on determining that the first query and the second query have the same structure and the different literal value, a combined textual statement having the same structure and including a bind variable in place of the different literal value;
alter, based on generating the combined textual statement, the combined textual statement with a first hint to obtain a first altered query and the combined textual statement with a second hint to obtain a second altered query;
determine a first execution plan based on processing the first altered query and determine a second execution plan based on processing the second altered query;
select the first execution plan or the second execution plan as a selected execution plan;
generate, based on selecting the selected execution plan, a profile for at least one of the first query or the second query,
wherein the profile is based on the selected execution plan; and

execute the at least one of the first query or the second query according to the profile.

9. The non-transitory computer-readable medium of claim 8 , wherein the first query and the second query, prior to being identified as the candidate for optimization, are executed according to an initial execution plan that is associated with an initial execution time,
wherein the first query and the second query are identified as the candidate for optimization when the initial execution time satisfies a threshold value.

10. The non-transitory computer-readable medium of claim 8 , wherein the one or more instructions, when executed by the one or more processors, further cause the one or more processors to:
store the profile in association with the at least one of the first query or the second query,
wherein the one or more instructions, that cause the one or more processors to execute the at least one of the first query or the second query, cause the one or more processors to:
receive an instruction to execute the at least one of the first query or the second query;
obtain the profile; and
execute the at least one of the first query or the second query according to the profile.

11. The non-transitory computer-readable medium of claim 8 , wherein the one or more instructions, that cause the one or more processors to select the first execution plan or the second execution plan as the selected execution plan, cause the one or more processors to:
select neither of the first execution plan and the second execution plan as the selected execution plan; and
generate a notification indicating that an execution plan was not selected f

Assignees

• Accenture Global Solutions Ltd

Inventors

• Nanal Mihir S
• Sunodh Vinitha
• Jain Deepak
• Chaudhari Kiran
• Khan Junaid M

Classifications

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/2435

  Active constructs · CPC title