ADAPTIVELY ROUTING TRANSACTIONS TO SERVERS

• US 20110276579A1
• Filed: 05/07/2010
• Published: 11/10/2011
• Est. Priority Date: 08/12/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Colrain, Carol Lyndall, Irudayaraj, Rajkumar Arockia, Zoll, Michael Ullrich, Begun, Vladimir, Bruns, Logan O'Sullivan
• Granted Patent: US 9,262,490 B2
• Time in Patent Office: Days
• US Class Current: 707/756
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/84 Mapping; Conversion
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: G06F 2212/7201 Logical to physical mapping or translation of blocks or pages
• CPC Class Codes: G06F 9/5011 the resources being hardware resources other than CPUs, Servers and Terminals
• CPC Class Codes: G06F 9/5027 the resource being a machine, e.g. CPUs, Servers, Terminals
• CPC Class Codes: H04L 67/60 Scheduling or organising the servicing of application requests, e.g. requests for application data transmissions using the analysis and optimisation of the required network resources admission control or resource allocation H04L47/70

Abstract

A method and apparatus is provided for dynamically generating data to dataset mappings and dataset to logical instance mappings and distributing these mappings to subscribers for the purpose of routing work. An advisory engine updates the mappings of data to data sets based at least in part on changing demands and patterns of access to data, and updates the mappings of datasets to logical instances based at least in part on anticipated capacity and change in conditions of the database servers. The subscribers receive and store the mapping information that maps the data to dataset identifiers, and dataset identifiers to logical database instances. The subscribers may also receive information that describes the current availability conditions of database instances, which are used to override the mapping of logical database instances to physical database instances. Subscribers may use the data to dataset mapping to map data identified from in an incoming database request to a dataset. Subscribers may use the dataset to logical instance mapping to map a dataset to a possible database server, and the logical instance to physical instance mapping to map a possible database server to an available database server. In this manner, the subscribers may route data efficiently even when access patterns and server conditions change.

Claims

1. One or more storage media storing instructions which, when executed by one or more computing devices, cause performance of:

sending a first data-to-dataset mapping to one or more mid-tier servers that provide connections to database servers;

receiving information that indicates at least one of a change to a pattern of data access or a demand for data;

using the information to automatically generate a second data-to-dataset mapping that is different from the first data-to-dataset mapping; and

sending the second data-to-dataset mapping to the one or more mid-tier servers.

2. One or more storage media as recited in claim 1, wherein the first data-to-dataset mapping accounts for a frequency of access to data from a particular dataset in same sessions before the change, wherein the information indicates the change to the pattern of data access, wherein the performance of the using the information comprises the performance of using an updated frequency of access to data from the particular dataset in same sessions, and wherein the second data-to-dataset mapping accounts for the updated frequency of access to data from the particular dataset in same sessions.

3. One or more storage media as recited in claim 2, wherein the instructions, when executed by the one or more computing devices, cause performance of detecting that the updated frequency of access to data from the particular dataset in same sessions satisfies a threshold frequency of access, and wherein the performance of the using the information to automatically generate the second data-to-dataset mapping is in response to the detecting.

4. One or more storage media as recited in claim 1, wherein the first data-to-dataset mapping does not account for the demand for a particular dataset, wherein the information indicates the demand for data from the particular dataset, and wherein the performance of the using the information comprises using a frequency of access to data from the particular dataset, and wherein the second data-to-dataset mapping accounts for the frequency of access to data from the particular dataset.

5. One or more storage media as recited in claim 4, wherein the instructions, when executed by the one or more computing devices, cause performance of detecting that the frequency of access to data from the particular dataset satisfies a threshold frequency of access, and wherein the performance of using the information to automatically generate the second data-to-dataset mapping is in response to the detecting.

6. One or more storage media as recited in claim 1, wherein the performance of the using the information to automatically generate the second data-to-dataset mapping that is different from the first data-to-dataset mapping comprises the performance of restructuring the first data-to-dataset mapping, based at least in part on the information, to group data frequently accessed in a same session into a same dataset.

7. One or more storage media as recited in claim 1, wherein the first data-to-dataset mapping maps a data key to a dataset, and wherein the second data-to-dataset mapping maps the data key to a different dataset.

8. One or more storage media as recited in claim 1, wherein the first data-to-dataset mapping indicates a rank order of two or more data keys to two or more datasets, and wherein the second data-to-dataset mapping indicates a different rank order of the two or more data keys to two or more datasets.

9. One or more storage media as recited in claim 1, wherein the information is a first information, wherein the instructions, when executed by the one or more computing devices, cause performance of:

sending a first dataset-to-logical-database-server mapping to the one or more mid-tier servers;

receiving a second information that indicates at least one of a change of a condition or a characteristic of one or more physical database servers;

using the second information to automatically generate a second dataset-to-logical-database-server mapping that is different from the first dataset-to-logical-database-server mapping.

10. One or more storage media storing instructions which, when executed by one or more computing devices, cause performance of:

sending a first dataset-to-logical-database-server mapping to the one or more mid-tier servers;

receiving information that indicates at least one of a change of a condition or a characteristic of one or more physical database servers;

using the information to automatically generate a second dataset-to-logical-database-server mapping that is different from the first dataset-to-logical-database-server mapping.

11. One or more storage media as recited in claim 10, wherein the first dataset-to-logical-database-server mapping accounts for a response time of the one or more physical database servers, wherein the information indicates the change of the condition, wherein the performance of the using the information comprises using an updated response time, and wherein the second dataset-to-logical-database-server mapping accounts for the updated response time.

12. One or more storage media as recited in claim 10, wherein the first dataset-to-logical-database-server mapping accounts for at least one of a load or availability of the one or more physical database servers, wherein the information indicates the change of the condition, wherein the performance of the using the information comprises using at least one of an updated load or an updated availability, and wherein the second dataset-to-logical-database-server mapping accounts for the at least one of the updated load or the updated availability.

13. One or more storage media as recited in claim 10, wherein the first dataset-to-logical-database-server mapping accounts for at least one of a cache miss rate or a cache hit rate of the one or more physical database servers, wherein the information indicates the change of the condition, wherein the performance of the using the information comprises using at least one of an updated cache miss rate or an updated cache hit rate, and wherein the second dataset-to-logical-database-server mapping accounts for the at least one of the updated cache miss rate or the updated cache hit rate.

14. One or more storage media as recited in claim 10, wherein the first dataset-to-logical-database-server mapping does not account for the characteristic of the one or more physical database servers, wherein the information indicates the characteristic, wherein the performance of the using the information comprises using at least one of a processor speed or an amount of memory of the one or more physical database servers, and wherein the second dataset-to-logical-database-server mapping accounts for the at least one of the processor speed or the amount of memory.

15. One or more storage media as recited in claim 10, wherein the instructions, when executed by the one or more computing devices, cause performance of detecting that the at least one of the condition or the characteristic satisfies a threshold, and wherein the performance of the using the information to automatically generate the second dataset-to-logical-database-server mapping is in response to the detecting.

16. One or more storage media as recited in claim 10, wherein the performance of the using the information to automatically generate the second dataset-to-logical-database-server mapping that is different from the first dataset-to-logical-database-server mapping comprises the performance of restructuring the first dataset-to-logical-database-server mapping, based at least in part on the information, to balance load across a plurality of physical database servers that include the one or more physical database servers.

17. One or more storage media as recited in claim 10, wherein the information is a first information, wherein the instructions, when executed by the one or more computing devices, cause performance of:

sending a first data-to-dataset mapping to the one or more mid-tier servers;

receiving a second information that indicates at least one of a change to a pattern of data access or a demand for data;

using the second information to automatically generate a second data-to-dataset mapping that is different from the first data-to-dataset mapping.

18. One or more storage media storing instructions which, when executed by one or more computing devices, cause performance of:

storing a first mapping of data to datasets, a second mapping of datasets to logical database servers, and a third mapping of logical database servers to physical database servers, wherein the third mapping accounts for one or more conditions of one or more physical database servers;

at a mid-tier server that routes requests to database servers, receiving a request to access a database;

in response to receiving the request, mapping the request to a physical database server by;

mapping the request to a dataset using the first mapping;

mapping the dataset to a logical database server using the second mapping; and

mapping the logical database server to a physical database server using the third mapping.

19. One or more storage media as recited in claim 18, wherein the instructions, when executed by the one or more computing devices, cause performance of:

receiving an update to the first mapping that accounts for at least one of a change to a pattern of data access or a demand for data; and

wherein the performance of mapping the request to the physical database server reflects the update and maps the request to a different physical database server than the request would be mapped without the update.

20. One or more storage media as recited in claim 18, wherein the instructions, when executed by the one or more computing devices, cause performance of:

receiving an update to the second mapping that accounts for at least one of a change of a condition or a characteristic of one or more physical database servers; and

wherein the performance of mapping the request to the physical database server reflects the update and maps the request to a different physical database server than the request would be mapped without the update.

21. One or more storage media as recited in claim 18, wherein the performance of mapping the logical database server to the physical database server re-maps a possible physical database server to another physical database server if the possible physical database server is associated with an unsatisfactory condition.

22. One or more storage media as recited in claim 18, wherein the performance of mapping the request to the dataset using the first mapping maps a data key from the request to the dataset;

wherein the performance of mapping the dataset to the logical database server using the second mapping maps the dataset to a bit in a bitmap that records the one or more conditions of the one or more physical database servers, wherein the bit represents a possible physical database server; and

wherein the performance of mapping the logical database server to the physical database server using the third mapping re-maps the possible physical database server to another physical database server if the bit indicates an unsatisfactory condition of the possible physical database server.

23. One or more storage media as recited in claim 18, wherein the instructions, when executed by the one or more computing devices, cause performance of providing an interface to specify a data key at connection checkout.

24. One or more storage media as recited in claim 18, wherein the performance of mapping the request to the dataset using the first mapping scans the request for data keys and uses the data keys in a rank order specified by the first mapping.

25. One or more storage media as recited in claim 18, wherein the performance of mapping the request to the dataset using the first mapping uses a property of the request as a data key to identify the dataset, wherein the property comprises at least one of a network domain, a client identifier, or a username.