Automatic recognition and capture of SQL execution plans

• US 8,600,977 B2
• Filed: 08/08/2008
• Issued: 12/03/2013
• Est. Priority Date: 10/17/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dageville, Benoit, Das, Dinesh, Ziauddin, Mohamed
• Primary Examiner(s): Chen, Susan
• Application Number:
  US12/188,662
• Publication Number:
  US 20090106320A1
• Time in Patent Office: 1,943 Days
• Field of Search: None
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination

Abstract

Approaches, techniques, and mechanisms are disclosed for capturing and utilizing information related to query plans exhibiting interesting characteristics. A database server receives a request to execute a command. The database server executes the command according to a query plan. In response to determining that the command matches one or more pre-defined criteria, the database server captures information related to the execution of the first command. The criteria may include, for example, whether or not the command is repeatable, the existence of bind variables, access of a particular object, high resource utilization, receipt from a particular user, client, or application, etc. The information recorded may include, for example, performance statistics collected during execution of the first plan, data indicating the execution context during execution of the first plan, and properties of the first plan. The recorded information may subsequently be utilized by the database server in executing other database other database commands.

Claims

1. A method for executing database commands, the method comprising:

receiving a plurality of requests from a plurality of database clients, the plurality of requests requesting execution of a plurality of database commands;

executing the plurality of database commands according to plans identified for the plurality of database commands;

while executing the plurality of database commands according to the plans, collecting first information, wherein the first information is related to execution of the plurality of database commands according to the plans;

for each database command in the plurality of database commands, determining whether the database command matches one or more pre-defined criteria;

when it is determined that a database command, of the plurality of database commands, matches the one or more pre-defined criteria, persistently recording, in historical information for the database command, information collected in relationship with execution of the database command;

when it is determined that a database command, of the plurality of database commands, does not match the one or more pre-defined criteria, not persistently recording, in historical information for the database command, the information collected in relationship with execution of the database command;

receiving a particular request to execute a previously executed database command of the plurality of database commands;

in response to the particular request;

determining that information collected in relationship with one or more previous executions of the previously executed database command has been persistently recorded in historical information for the previously executed database command; and

executing the previously executed database command using, at least in part, the information collected in relationship with the execution of the previously executed database command;

wherein the steps are performed by one or more computing devices.

2. The method of claim 1 wherein determining that a database command matches the one or more pre-defined criteria comprises determining that the database command is a repeatable database command.

3. The method of claim 2 wherein determining that the database command is a repeatable database command comprises:

prior to receiving a first request to execute the database command, receiving an initial request to execute an initial database command; and

determining that the database command is equivalent to the initial database command.

4. The method of claim 3 wherein determining that the database command is equivalent to the initial database command comprises:

upon receipt of the initial request;

generating an initial signature based on the initial database command;

persistently maintaining the initial signature in a signature log;

generating a first signature based on the database command; and

comparing the first signature to the initial signature in the signature log.

5. The method of claim 1 wherein the one or more pre-defined criteria comprise at least one of:

(a) the existence of bind variables in the database command;

(b) receipt of the database command from a particular user, client, or application;

(c) the database command referring to a particular database or object;

or

(d) the crossing of a certain threshold by an indicator for a resource utilized while executing the database command.

6. The method of claim 1 wherein the first information comprises one or more outlines for the plans.

7. The method of claim 1 wherein the first information comprises information identifying one or more properties of an execution context that existed during execution of the previous database command.

8. The method of claim 1 wherein the first information comprises one or more performance statistics related to the execution of the previous database command.

9. The method of claim 1 wherein the first information comprises information indicating one or more properties of the plurality of requests.

10. The method of claim 1 wherein executing the previously executed database command in response to the particular request comprises:

determining a particular plurality of plans for executing the previously executed database command;

based at least on the first information, selecting a particular plan from the particular plurality of plans for executing the previously executed database command; and

executing the previously executed database command according to the particular plan.

11. The method of claim 10 wherein selecting the particular plan comprises:

estimating predicted costs for executing each plan in the particular plurality of plans;

wherein the predicted costs are based at least in part on statistics indicating actual costs for executing the particular plurality of plans, said statistics being recorded in the first information;

wherein selecting the particular plan comprises selecting from the particular plurality of plans, as the particular plan, a plan with the lowest predicted costs.

12. The method of claim 1, wherein executing the previously executed database command in response to the particular request comprises:

based at least on data indicating resource usage during the one or more previous executions of the previously executed database command, said data being recorded in the first information, allocating system resources for executing the previously executed database command.

13. The method of claim 1, wherein:

the one or more previous executions of the previous database command comprised executing the previously executed database command in a previous execution context; and

executing the previously executed database command in response to the particular request comprises;

executing the previously executed database command in a current execution context;

determining similarities between the previous execution context and a current execution context.

14. A method for executing database commands, the method comprising:

receiving a plurality of requests from a plurality of database clients, the plurality of requests requesting execution of a plurality of database commands;

for each database command in the plurality of database commands, determining whether the database command matches one or more pre-defined criteria;

executing the plurality of database commands according to plans identified for the plurality database commands;

when it is determined that a database command, of the plurality of database commands, matches the one or more pre-defined criteria, monitoring execution of the database command and capturing performance statistics related to the execution of the database command in historical information for the database command;

when it is determined that a database command, of the plurality of database commands, does not match the one or more pre-defined criteria, not capturing performance statistics related to the execution of the database command in historical information for the database command;

generating a report for one or more of the plans, based on performance statistics captured in historical information for a corresponding one or more of the plurality of database commands;

wherein the steps are performed by one or more computing devices.

15. The method of claim 14 wherein the one or more pre-defined criteria comprise at least one of:

(a) whether or not the database command has been received in a previous request;

(b) the existence of bind variables in the database command;

(c) receipt of the database command from a particular user, client, or application;

(d) access of a particular database or object;

or

(e) the crossing of a certain threshold by an indicator for a resource utilized while executing the database command.

16. The method of claim 14, wherein the performance statistics comprise at least one of execution time, processor usage, input/output usage, compilation environment, a bind variable value, a session setting, or a system configuration parameter.

17. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform:

receiving a plurality of requests from a plurality of database clients, the plurality of requests requesting execution of a plurality of database commands;

executing the plurality of database commands according to plans identified for the plurality of database commands;

while executing the plurality of database commands according to the plans, collecting first information, wherein the first information is related to execution of the plurality of database commands according to the plans;

for each database command in the plurality of database commands, determining whether the database command matches one or more pre-defined criteria;

when it is determined that a database command, of the plurality of database commands, matches the one or more pre-defined criteria, persistently recording, in historical information for the database command, information collected in relationship with execution of the database command;

when it is determined that a database command, of the plurality of database commands, does not match the one or more pre-defined criteria, not persistently recording, in historical information for the database command, the information collected in relationship with execution of the database command;

receiving a particular request to execute a previously executed database command of the plurality of database commands;

in response to the particular request;

determining that information collected in relationship with one or more previous executions of the previously executed database command has been persistently recorded in historical information for the previously executed database command; and

executing the previously executed database command using, at least in part, the information collected in relationship with the execution of the previously executed database command.

18. The non-transitory computer-readable storage medium of claim 17, wherein determining that a database command matches the one or more pre-defined criteria comprises determining that the database command is a repeatable database command.

19. The non-transitory computer-readable storage medium of claim 18, wherein determining that the database command is a repeatable database command comprises:

prior to receiving a first request to execute the database command, receiving an initial request to execute an initial database command; and

determining that the database command is equivalent to the initial database command.

20. The non-transitory computer-readable storage medium of claim 19, wherein determining that the database command is equivalent to the initial database command comprises:

upon receipt of the initial request;

generating an initial signature based on the initial database command;

persistently maintaining the initial signature in a signature log;

generating a first signature based on the database command; and

comparing the first signature to the initial signature in the signature log.

21. The non-transitory computer-readable storage medium of claim 17, wherein the one or more pre-defined criteria comprise at least one of:

(a) the existence of bind variables in the database command;

(b) receipt of the database command from a particular user, client, or application;

(c) the database command referring to a particular database or object;

or

(d) the crossing of a certain threshold by an indicator for a resource utilized while executing the database command.

22. The non-transitory computer-readable storage medium of claim 17, wherein the first information comprises one or more outlines for the plans.

23. The non-transitory computer-readable storage medium of claim 17, wherein the first information comprises information identifying one or more properties of an execution context that existed during execution of the previous database command.

24. The non-transitory computer-readable storage medium of claim 17, wherein the first information comprises one or more performance statistics related to the execution of the previous database command.

25. The non-transitory computer-readable storage medium of claim 17, wherein the first information comprises information indicating one or more properties of the plurality of requests.

26. The non-transitory computer-readable storage medium of claim 17, wherein executing the particular database command in response to the particular request comprises:

determining a particular plurality of plans for executing the previously executed database command;

based at least on the first information, selecting a particular plan from the particular plurality of plans for executing the previously executed database command; and

executing the previously executed database command according to the particular plan.

27. The non-transitory computer-readable storage medium of claim 26, wherein selecting the particular plan comprises:

estimating predicted costs for executing each plan in the particular plurality of plans;

wherein the predicted costs are based at least in part on statistics indicating actual costs for executing the particular plurality of plans, said statistics being recorded in the first information;

wherein selecting the particular plan comprises selecting from the particular plurality of plans, as the particular plan, a plan with the lowest predicted costs.

28. The non-transitory computer-readable storage medium of claim 17, wherein executing the previously executed database command in response to the particular request comprises:

based at least on data indicating resource usage during the one or more previous executions of the previously executed database command, said data being recorded in the first information, allocating system resources for executing the previously executed database command.

29. The non-transitory computer-readable storage medium of claim 17, wherein:

the one or more previous executions of the previous database command comprised executing the previously executed database command in a previous execution context; and

executing the previously executed database command in response to the particular request comprises;

executing the previously executed database command in a current execution context;

determining similarities between the previous execution context and a current execution context.

30. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform:

receiving a plurality of requests from a plurality of database clients, the plurality of requests requesting execution of a plurality of database commands;

for each database command in the plurality of database commands, determining whether the database command matches one or more pre-defined criteria;

executing the plurality of database commands according to plans identified for the plurality database commands;

when it is determined that a database command, of the plurality of database commands, matches the one or more pre-defined criteria, monitoring execution of the database command and capturing performance statistics related to the execution of the database command in historical information for the database command;

when it is determined that a database command, of the plurality of database commands, does not match the one or more pre-defined criteria, not capturing performance statistics related to the execution of the database command in historical information for the database command;

generating a report for one or more of the plans, based on performance statistics captured in historical information for a corresponding one or more of the plurality of database commands.

31. The non-transitory computer-readable storage medium of claim 30, wherein the one or more pre-defined criteria comprise at least one of:

(a) whether or not the database command has been received in a previous request;

(b) the existence of bind variables in the database command;

(c) receipt of the database command from a particular user, client, or application;

(d) access of a particular database or object;

or

(e) the crossing of a certain threshold by an indicator for a resource utilized while executing the database command.

32. The non-transitory computer-readable storage medium of claim 30, wherein the performance statistics comprise at least one of execution time, processor usage, input/output usage, compilation environment, a bind variable value, a session setting, or a system configuration parameter.