Liquid data services

• US 20060218117A1
• Filed: 01/27/2006
• Published: 09/28/2006
• Est. Priority Date: 03/28/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: BEA Systems Incorporated (Oracle Corporation)
• Inventors: Gupta, Naveen
• Granted Patent: US 7,778,998 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

In accordance with embodiments of the present invention, there are provided mechanisms and methods for using query plans for building and performance tuning services accessed on behalf of a requestor. These mechanisms and methods for using query plans for building and performance tuning services makes it possible to examine the query plan and response times for query execution. The ability to examine the query plans and response times for query execution makes it possible to improve query efficiency and apply caching more effectively.

Claims

1. A computer-readable medium carrying one or more sequences of instructions for preparing a query plan for tuning a service, which instructions, when executed by one or more processors, cause the one or more processors to carry out the steps of:

receiving a query from a requestor;

determining from at least one of a plurality of possible ways for satisfying the query, a preferred way for satisfying the query; and

providing the preferred way as at least a portion of the query plan.

2. The computer-readable medium as recited in claim 1, further comprising instructions for carrying out the steps of:

accessing a service according to the query plan to receive a result set;

providing the result set and information about time or resources usage to perform the query to the requestor; and

receiving from the requestor input specifying a change to the query plan for improving query efficiency.

3. The computer-readable medium as recited in claim 2, wherein determining from at least one of a plurality of possible ways for satisfying the query, a preferred way for satisfying the query comprises instructions for carrying out the steps of:

determining a way that meets a performance criteria.

4. The computer-readable medium as recited in claim 3, wherein a performance criteria is a condition placed upon a time or resources usage.

5. The computer-readable medium as recited in claim 2, wherein instructions for providing the result set and information about time or resources usage to perform the query to the requestor further comprises instructions for carrying out the steps of:

displaying the query plan to an operator; and

receiving from the operator an input indicating at least one of;

an acceptance of the query plan and a modification to the query plan;

thereby enabling the operator to examine the query plan and response time for query execution.

6. The computer-readable medium as recited in claim 1, wherein the instructions for carrying out the step of determining from at least one of a plurality of possible ways for satisfying the query, a preferred way for satisfying the query further comprise instructions for carrying out the steps of:

determining at least one of a plurality of ways capable of satisfying the query;

selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria; and

providing the preferred way to satisfy the query in the query plan.

7. The computer-readable medium as recited in claim 6, wherein instructions for determining at least one of a plurality of ways capable of satisfying the query, comprise instructions for carrying out the steps of:

determining, for a join operation on a plurality of database tables, at least one of;

reading each of the plurality of tables into memory and then performing a join operation;

reading a smaller one of the plurality of tables into memory and then requesting values from remaining tables as needed to complete a join operation; and

requesting values from each of the plurality of tables as needed to complete a join operation.

8. The computer-readable medium as recited in claim 7, wherein instructions for selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria, comprise instructions for carrying out the steps of:

reading each of the plurality of tables into memory and then performing a join operation, if speed performance is preferred over memory usage performance;

9. The computer-readable medium as recited in claim 7, wherein instructions for selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria, comprise instructions for carrying out the steps of:

reading a smaller one of the plurality of tables into memory and then requesting values from remaining tables as needed to complete a join operation, if memory capacity would be constrained by at least one table.

10. The computer-readable medium as recited in claim 7, wherein instructions for selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria, comprise instructions for carrying out the steps of:

requesting values from each of the plurality of tables as needed to complete a join operation, if memory capacity would be constrained by at least two tables.

11. The computer-readable medium as recited in claim 6, wherein instructions for selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria, comprise instructions for carrying out the steps of:

selecting a preferred technique for preparing a query plan for satisfying the query from at least one of;

SQL pushdown techniques, batched join processing techniques, index join techniques and parallel data source requests.

12. The computer-readable medium as recited in claim 6, wherein instructions for selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria, comprise instructions for carrying out the steps of:

selecting a preferred way for satisfying the query and complying with at least one of;

a maximum query response time, an average response time for data queries, a peak usage and a maximum degradation of performance.

13. The computer-readable medium as recited in claim 6, wherein instructions for selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria, comprise instructions for carrying out the steps of:

selecting a preferred way for satisfying the query and meeting at least one of;

a type of join, a size of tables on which a join operation is requested and an historical performance of the service.

14. A computer-readable medium carrying one or more sequences of instructions for tuning a service, which instructions, when executed by one or more processors, cause the one or more processors to carry out the steps of:

sending a query to the server;

receiving a result set of one or more services from the server;

wherein the server has prepared a portion of the result set of the service(s) according to the server'"'"'s determination of a preferred way for satisfying the query.

15. An apparatus for tuning a service, the apparatus comprising:

a processor; and

one or more stored sequences of instructions which, when executed by the processor, cause the processor to carry out the steps of;

sending a query to the server;

receiving a result set of one or more services from the server;

wherein the server has prepared a portion of the result set of the service(s) according to the server'"'"'s determination of a preferred way for satisfying the query.