Liquid data services

• US 7,778,998 B2
• Filed: 01/27/2006
• Issued: 08/17/2010
• 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
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): KOO, GARY J
• Application Number:
  US11/342,111
• Publication Number:
  US 20060218117A1
• Time in Patent Office: 1,663 Days
• Field of Search: 707/104, 707 3- 5
• US Class Current: 707/714
• 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 storage medium storing 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 portal application to be executed against a system, said query associated with a service model, wherein the service model provides a plurality of structured views of available services to the portal application;

determining at least one of a plurality of ways capable of satisfying the query, wherein at least one of the plurality of ways includes accessing at least two tables in a database;

selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria wherein selecting includes determining whether speed of query execution is more important than memory usage by employing parameters that correlate the relative importance of speed, memory and resource usage on the system that the query is executed against wherein selecting the preferred way for satisfying the query includes;

if speed is determined to be more important than memory usage;

retrieving both of said at least two tables from the database and writing said at least two tables into memory;

if speed is determined not to be more important than memory usage;

determining whether memory capacity is constrained by only one of said at least two tables and if the memory capacity is constrained by only one of said at least two tables, then retrieving a smaller table of said two tables from the database and writing said smaller table into the memory;

otherwise, if speed is determined not to be more important than memory usage and if both tables are too large to be brought into the memory;

requesting values from each of said at least two tables as needed from the database;

generating the query plan and providing the preferred way as at least a portion of the query plan; and

accessing a service according to the query plan to receive a result set, wherein the result set is mapped to one of the plurality of structured views that is specifically suited to the format of the portal application that has submitted the query.

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

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

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

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

4. The computer-readable storage 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 portal application 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.

5. The computer-readable storage medium as recited in claim 1, 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 a plurality of tables into memory and then performing a join operation, if speed performance is preferred over memory usage performance.

6. The computer-readable storage medium as recited in claim 1, 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 in a plurality of tables, as needed to complete a join operation, if memory capacity would be constrained by at least one table.

7. The computer-readable storage medium as recited in claim 1, 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 a plurality of tables as needed to complete a join operation, if memory capacity would be constrained by at least two tables.

8. The computer-readable storage medium as recited in claim 1, 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;

structured query language pushdown techniques, batched join processing techniques, index join techniques and parallel data source requests.

9. The computer-readable storage medium as recited in claim 1, 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.

10. The computer-readable storage medium as recited in claim 1, 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.

13. The computer readable storage medium of claim 1, wherein determining whether the speed of query execution is more important than memory usage further includes:

inspecting the parameters correlating the relative importance of speed, memory and resource usage encoded in a data structure.

14. The computer readable storage medium of claim 1, wherein determining whether the speed of query execution is more important than memory usage further includes:

automatically executing a set of test cases on the system in order to determine physical limitations that include one or more of the following;

installed memory, processor clock speed, input/output (I/O) devices and configurations of the system.

11. A method for tuning one or more services, comprising:

receiving a query from a portal application to be executed against a system, said query associated with a service model, wherein the service model provides a plurality of structured views of available services to the portal application;

determining at least one of a plurality of ways capable of satisfying the query, wherein at least one of the plurality of ways includes accessing at least two tables in a database;

selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria wherein selecting includes determining whether speed of query execution is more important than memory usage by employing parameters that correlate the relative importance of speed, memory and resource usage on the system that the query is executed against wherein selecting the preferred way for satisfying the query includes;

if speed is determined to be more important than memory usage;

retrieving both of said at least two tables from the database and writing said at least two tables into memory;

if speed is determined not to be more important than memory usage;

determining whether memory capacity is constrained by only one of said at least two tables and if the memory capacity is constrained by only one of said at least two tables, then retrieving a smaller table of said two tables from the database and writing said smaller table into the memory;

otherwise, if speed is determined not to be more important than memory usage and if both tables are too large to be brought into the memory;

requesting values from each of said at least two tables as needed from the database;

generating the query plan and providing the preferred way as at least a portion of the query plan; and

accessing a service according to the query plan to receive a result set, wherein the result set is mapped to one of the plurality of structured views that is specifically suited to the format of the portal application that has submitted the query.

12. An apparatus for tuning one or more services, 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;

receiving a query from a portal application to be executed against a system, said query associated with a service model, wherein the service model provides a plurality of structured views of available services to the portal application;

determining at least one of a plurality of ways capable of satisfying the query, wherein at least one of the plurality of ways includes accessing at least two tables in a database;

selecting, from the plurality of ways to satisfy the query, a preferred way for satisfying the query and meeting a performance criteria wherein selecting includes determining whether speed of query execution is more important than memory usage by employing parameters that correlate the relative importance of speed, memory and resource usage on the system that the query is executed against wherein selecting the preferred way for satisfying the query includes;

if speed is determined to be more important than memory usage;

retrieving both of said at least two tables from the database and writing said at least two tables into memory;

if speed is determined not to be more important than memory usage;

determining whether memory capacity is constrained by only one of said at least two tables and if the memory capacity is constrained by only one of said at least two tables, then retrieving a smaller table of said two tables from the database and writing said smaller table into the memory;

otherwise, if speed is determined not to be more important than memory usage and if both tables are too large to be brought into the memory;

requesting values from each of said at least two tables as needed from the database;

generating the query plan and providing the preferred way as at least a portion of the query plan; and

accessing a service according to the query plan to receive a result set, wherein the result set is mapped to one of the plurality of structured views that is specifically suited to the format of the portal application that has submitted the query.