Automatic Recognition and Capture of SQL Execution Plans

• US 20090106320A1
• Filed: 08/08/2008
• Published: 04/23/2009
• Est. Priority Date: 10/17/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Das, Dinesh, Dageville, Benoit, Ziauddin, Mohamed
• Granted Patent: US 8,600,977 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 first request to execute a first command;

executing the first command according to a first plan;

while executing the first command according to the first plan, capturing first information, wherein the first information is related to the execution of the first command according to the first plan;

determining that the first command matches one or more pre-defined criteria;

in response to determining that the first command matches one or more pre-defined criteria, persistently recording the first information;

receiving a second request to execute a second command;

in response to the second request;

determining that the second command is equivalent to the first command; and

executing the second command in a manner that is based, at least in part, on the first information.

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

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

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

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

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

upon receipt of the initial request;

generating an initial signature based on the initial command;

persistently maintaining the initial signature in a signature log;

generating a first signature based on the first 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 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 first command.

6. The method of claim 1 wherein the first information comprises information indicating the first plan.

7. The method of claim 1 wherein the first information comprises information identifying one or more properties of a current execution context that exists during execution of the first plan.

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

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

10. The method of claim 1 wherein executing the second command in a manner that is based, at least in part, on the first information comprises:

determining a plurality of plans for executing the second command;

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

executing the second command according to the second plan.

11. The method of claim 10 wherein determining the second plan comprises:

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

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

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

12. The method of claim 1, wherein executing the second command in a manner that is based, at least in part, on the information related to the execution of the first command comprises:

based at least on data indicating resource usage during execution of the first command, said data being recorded in the first information, allocating system resources for executing the second command.

13. The method of claim 1, wherein:

executing the first command comprises executing the first command in a first execution context; and

executing the second command in a manner that is based, at least in part, on the first information comprises;

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

17. A 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 the method recited in claim 1.

18. A 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 the method recited in claim 2.

19. A 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 the method recited in claim 3.

20. A 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 the method recited in claim 4.

21. A 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 the method recited in claim 5.

22. A 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 the method recited in claim 6.

23. A 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 the method recited in claim 7.

24. A 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 the method recited in claim 8.

25. A 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 the method recited in claim 9.

26. A 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 the method recited in claim 10.

27. A 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 the method recited in claim 11.

28. A 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 the method recited in claim 12.

29. A 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 the method recited in claim 13.

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

receiving a first request to execute a first command;

determining that the first command matches one or more pre-defined criteria;

executing the first command according to first plan;

in response to said determining, while monitoring execution of the first command, capturing performance statistics related to the execution of the first plan;

generating a report for the first plan, based on the captured performance statistics.

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

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

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

(c) receipt of the 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 first 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.

30. A 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 the method recited in claim 14.

31. A 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 the method recited in claim 15.

32. A 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 the method recited in claim 16.