Query explain plan in a distributed data management system

• US 8,868,546 B2
• Filed: 03/14/2012
• Issued: 10/21/2014
• Est. Priority Date: 09/15/2011
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Beerbower, Tom, Lee, Robert H.
• Primary Examiner(s): LU, KUEN S
• Application Number:
  US13/420,324
• Publication Number:
  US 20130073538A1
• Time in Patent Office: 951 Days
• Field of Search: 707/741, 707/714, 707/718
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

A query explain plan is described for use with a distributed data system in order to help developers and IT experts to detect bottlenecks and to determine which queries are to blame for a slow running system. In accordance with an embodiment, the distributed data grid utilizes a facility to provide a distributed explain plan. This distributed explain plan provides visibility into how a query was answered by each node in the cluster. For example, one node may have applied the filters of the query in one sequence, while another node may have used a different sequence. Additionally, the distributed query explain plan can provide execution tracing, rendering information about the execution time of each step, total execution time and the like.

Claims

1. A system for explaining queries in a distributed data grid, said system comprising:

one or more microprocessors;

a cluster running on the one or more microprocessors, including a plurality of cluster nodes storing data distributed throughout the cluster, each cluster node being capable of executing a structured query language (SQL) query on the data stored on said cluster node,

wherein the cluster operates to

receive, from a client connected to the cluster, a request that includes a SQL query;

submit the SQL query to each node in the cluster and wherein each node in the cluster operates to

evaluate a query plan for the SQL query using local data,

determine an optimal query plan that is specific to said each node in the cluster, and

return a set of information regarding how the SQL query is executed at said each node; and

use an aggregator to generate a query record object, wherein the query record object aggregates the information from all nodes in the cluster, wherein the query record object contains information regarding how the SQL query is executed by each node in the cluster.

2. The system of claim 1, wherein the query record object further provides execution tracing, rendering information about an execution time of each step and total execution time.

3. The system of claim 1, wherein to obtain the query explain plan, a method is invoked passing an SQL query as a parameter to said method.

4. The system of claim 1, wherein a facility is provided to compare and contrast different query plans and to reconcile results of different query invocations.

5. The system of claim 1, wherein the system is executable in at least two modes:

an explain mode and a trace mode, wherein the explain mode produces a query record object that contains an estimated cost of the query execution, and wherein the trace mode contains the actual cost of query execution.

6. The system of claim 1, wherein an index is generated by employing the query record object.

7. The system of claim 1 wherein each node in the cluster executes a different query plan depending on a partition of data stored on the node.

8. The system of claim 1, wherein the aggregator operates to serve as a signal to each node of the plurality of cluster nodes in the cluster that a query explain plan is requested.

9. The system of claim 1, wherein the aggregator operates to combine partial results into the query record object via an interface.

10. The system of claim 1, wherein a first optimal query plan for the SQL query that is specific to a first cluster node is different from a second optimal query plan for the SQL query that is specific to a second cluster node.

11. The system of claim 10, wherein the first optimal query plan applies one or more filters of the SQL query in a first sequence, while the second optimal query plan applies said one or more filters of the SQL query in a second sequence, and wherein the first sequence is different from the second sequence.

12. A method for explaining queries in a distributed data grid, said method comprising:

storing data on a plurality of nodes in a cluster, each node being capable of executing a structured query language (SQL) query on the data stored on said node;

receiving a request containing the SQL query from a client connected to the cluster;

submitting the SQL query to each node in the cluster, wherein each node in the cluster operates to

evaluate a query plan for the SQL query using local data,

determine an optimal query plan that is specific to said each node in the cluster, and

return a set of information regarding how the SQL query is executed at said each node; and

using an aggregator to generate a query record object, wherein the query record object that aggregates the information from all nodes in the cluster, wherein the query record object contains information regarding how the SQL query is executed by each node in the cluster.

13. The method of claim 12, wherein the query record object further provides execution tracing, rendering information about an execution time of each step and total execution time.

14. The method of claim 12, wherein to obtain the query explain plan, a method is invoked passing an SQL query as a parameter to said method.

15. The method of claim 12, wherein a facility is provided to compare and contrast different query plans and to reconcile results of different query invocation.

16. The method of claim 12, wherein the system is executable in at least two modes:

an explain mode and a trace mode, wherein the explain mode produces a query record object that contains an estimated cost of the query execution, and wherein the trace mode contains the actual cost of query execution.

17. The method of claim 12, wherein an index is generated by employing the query record object.

18. The method of claim 12, wherein each node in the cluster executes a different query plan depending on a partition of data stored on the node.

19. A non-transitory computer readable storage medium storing one or more sequences of instructions executed by one or more processors to perform a set of steps comprising:

storing data on a plurality of nodes in a cluster, each node being capable of executing a structured query language (SQL) query on the data stored on said node;

receiving a request containing the SQL query from a client connected to the cluster;

submitting the SQL query to each node in the cluster, wherein each node in the cluster operates to

evaluate a query plan for the SQL query using local data,

determine an optimal query plan that is specific to said each node in the cluster, and

return a set of information regarding how the SQL query is executed at said each node; and

using an aggregator to generate a query record object, wherein the query record object that aggregates the information from all nodes in the cluster, wherein the query record object contains information regarding how the SQL query is executed by each node in the cluster.