AUTOMATICALLY DETERMINING AN OPTIMAL DATABASE SUBSECTION

• US 20140280373A1
• Filed: 03/15/2013
• Published: 09/18/2014
• Est. Priority Date: 03/15/2013
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Raitto, John, Lahiri, Tirthankar, Neimat, Marie-Anne, Wang, Chih-Ping
• Granted Patent: US 9,639,562 B2
• Time in Patent Office: Days
• US Class Current: 707/803
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

A method, apparatus, and system for automatically determining an optimal database subsection is provided. A database subsection is selected to optimize certain benefits when the database subsection is translated, transferred, and cached on an alternative database system, which may utilize a different technology or database engine that provides certain performance benefits compared to the original database system. Algorithms such as multi-path greedy selection and/or dynamic programming may provide optimal or near-optimal results. A host for the alternative database server may be shared with or otherwise located in close physical proximity to improve latency for a database application or client layer. Once the database subsection analysis is completed, a report may be generated and presented to the user, and an implementation script may also be created to automatically configure a client host to function as a cache or replacement system according various cache size configurations described in the report.

Claims

1. A method comprising:

storing a first schema definition describing a first plurality of database objects;

storing a first workload description describing access to the first plurality of database objects at a first database system;

generating a second schema definition describing a second plurality of database objects, wherein each database object of the second plurality of database objects is a counter-part to one or more database objects of the first plurality of database objects;

wherein the generating of the second schema definition includes, based on the first workload description and the first schema definition, selecting particular database objects in the first plurality of database objects, wherein one or more second database objects in the second plurality of database objects are counter-parts to the particular database objects.

2. The method of claim 1, wherein the selecting of the particular database objects is such that a size of the one or more second database objects fits within a storage constraint.

3. The method of claim 2, wherein the storage constraint is based on an available memory size of a second database system for storing the one or more second database objects, wherein the second database system is an in-memory database system.

4. The method of claim 1, wherein the selecting of the particular database objects is based on a type, a version, or a configuration of a second database system for storing the one or more second database objects.

5. The method of claim 1, wherein the selecting of the particular database objects is based on maximizing a reduction in execution time when a second database system services database queries on the particular database objects.

6. The method of claim 5, wherein the reduction in execution time is estimated based on statistics of the first database system.

7. The method of claim 1, wherein the first workload description comprises a plurality of queries, and wherein the selecting of the particular database objects comprises:

arranging the plurality of queries into a plurality of hierarchies, wherein;

each hierarchy includes a query set having a root query on a parent database object set, wherein all queries in the query set are on database objects within the parent database object set;

each hierarchy is associated with a value reflecting an execution time reduction when comparing an execution time of the query set on the first database system to an execution time of a corresponding query set on a second database system, wherein the corresponding query set is on a corresponding set of database objects in the second plurality of database objects that are counter-parts to the parent database object set;

determining an optimized hierarchy set from the plurality of hierarchies based on maximizing a total value of the optimized hierarchy set when the particular database objects are selected to include the parent database object sets from each of the root queries of the optimized hierarchy set.

8. The method of claim 7, wherein for each hierarchy of the plurality of hierarchies, the execution time of the query set and the execution time of the corresponding query set are measured by an actual execution of the query set on the first database system and an actual execution of the corresponding query set on the second database system.

9. The method of claim 8, wherein the actual execution of the query set on the first database system uses a test database system of the first database system.

10. The method of claim 8, wherein the actual executions are grouped according to the plurality of hierarchies to execute in-memory.

11. The method of claim 10, wherein actual executions are further grouped by hierarchies on shared database objects, wherein at least a portion of the shared database objects is retained in-memory between actual executions of the hierarchies on the shared database objects.

12. The method of claim 7, wherein the determining of the optimized hierarchy set utilizes one or more of a multi-path greedy selection algorithm and a dynamic programming algorithm.

13. The method of claim 7, wherein an algorithm for the determining of the optimized hierarchy set is chosen based on a size of the first workload description.

14. The method of claim 1, further comprising:

determining one or more database technologies of a second database system for storing the one or more second database objects.

15. The method of claim 1, further comprising outputting a report describing the particular database objects, a mapping from the first schema definition to the second schema definition, a list of potential compatibility issues, and a projected performance benefit when servicing the one or more second database objects on a second database system.

16. The method of claim 1, further comprising generating one or more scripts to configure a second database system as a caching system for the first database system, wherein a database query on the particular database objects of the first database system is serviced by the second database system translating the database query to the second schema definition.

17. A non-transitory computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, cause performing of:

storing a first schema definition describing a first plurality of database objects;

storing a first workload description describing access to the first plurality of database objects at a first database system;

generating a second schema definition describing a second plurality of database objects, wherein each database object of the second plurality of database objects is a counter-part to one or more database objects of the first plurality of database objects;

wherein the generating of the second schema definition includes, based on the first workload description and the first schema definition, selecting particular database objects in the first plurality of database objects, wherein one or more second database objects in the second plurality of database objects are counter-parts to the particular database objects.

18. The non-transitory computer-readable medium of claim 17, wherein a second database system stores the one or more second database objects, and wherein the selecting of the particular database objects is based on at least one of:

a size of the one or more second database objects fitting within a storage constraint;

a size of the one or more second database objects fitting within an available memory size of the second database system as an in-memory database;

a type of the second database system;

a version of the second database system;

a configuration of the second database system;

maximizing a reduction in execution time when the second database system services database queries on the particular database objects by translating the database queries to the second schema definition.

19. A database advisor system comprising one or more computing devices configured to:

store a first schema definition describing a first plurality of database objects;

store a first workload description describing access to the first plurality of database objects at a first database system;

generate a second schema definition describing a second plurality of database objects, wherein each database object of the second plurality of database objects is a counter-part to one or more database objects of the first plurality of database objects;

wherein the generating of the second schema definition includes, based on the first workload description and the first schema definition, selecting particular database objects in the first plurality of database objects, wherein one or more second database objects in the second plurality of database objects are counter-parts to the particular database objects.

20. The database advisor system of claim 19, wherein a second database system stores the one or more second database objects, and wherein the selecting of the particular database objects is based on at least one of:

a size of the one or more second database objects fitting within a storage constraint;

a size of the one or more second database objects fitting within an available memory size of the second database system as an in-memory database;

a type of the second database system;

a version of the second database system;

a configuration of the second database system;

maximizing a reduction in execution time when the second database system services database queries on the particular database objects by translating the database queries to the second schema definition.