QUERY EXPLAIN PLAN IN A DISTRIBUTED DATA MANAGEMENT SYSTEM

• US 20130073538A1
• Filed: 03/14/2012
• Published: 03/21/2013
• 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.
• Granted Patent: US 8,868,546 B2
• Time in Patent Office: Days
• 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; and

a client connected to the cluster that submits a request that includes the SQL query, wherein upon receiving the request for the query explain plan, the SQL query is submitted to each node in the cluster and wherein each node in the cluster evaluates a query plan for the SQL query using local data as said each node and returns a set of information regarding how the SQL query is executed at said node; and

wherein a query record object is generated 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.

2. The system of claim 1, wherein the query record object further provides execution tracing, rendering information about the 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 the SQL query is sent to all cluster nodes in the cluster and wherein each cluster node can return the determined optimal query plan specific to said each cluster node.

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

6. 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.

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

8. 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.

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

storing data on a plurality of nodes in a cluster running on one or more microprocessors, 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 evaluates a query plan for the SQL query using local data as said each node and returns a set of information regarding how the SQL query is executed at said node; and

generating a 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.

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

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

12. The method of claim 9, wherein the SQL query is sent to all cluster nodes in the cluster and wherein each cluster node can return the determined optimal query plan specific to said each cluster node.

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

14. The method of claim 9, 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.

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

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

17. 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 evaluates a query plan for the SQL query using local data as said each node and returns a set of information regarding how the SQL query is executed at said node; and

generating a 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.

18. The non-transitory computer readable storage medium of claim 17, wherein the query record object further provides execution tracing, rendering information about the execution time of each step and total execution time.

19. The non-transitory computer readable storage medium of claim 17, wherein to obtain the query explain plan, a method is invoked passing an SQL query as a parameter to said method.

20. The non-transitory computer readable storage medium of claim 17, wherein the SQL query is sent to all cluster nodes in the cluster and wherein each cluster node can return the determined optimal query plan specific to said each cluster node.