Automatically determining an optimal database subsection

• US 9,639,562 B2
• Filed: 03/15/2013
• Issued: 05/02/2017
• 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
• Primary Examiner(s): Coby, Frantz
• Application Number:
  US13/842,843
• Publication Number:
  US 20140280373A1
• Time in Patent Office: 1,509 Days
• Field of Search: 707737, 707802, 707803, 707804, 707806, 707807, 707808, 707809, 707713, 707718, 707682
• 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, wherein the first workload description comprises information about execution of queries against at least a portion of the first plurality of database objects;

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 and is compatible with a second database system;

wherein the generating of the second schema definition includes, based on the first workload description and the first schema definition, selecting a subset of database objects to translate to a particular form that is described by said second schema definition and that is compatible with the second database system, wherein the subset of database objects belong to said first plurality of database objects.

2. The method of claim 1, wherein the selecting of the subset of 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 the 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 subset of database objects is based on a type, a version, or a configuration of the second database system for storing the one or more second database objects.

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

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

14. The method of claim 1, further comprising:

determining one or more database technologies of the 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 subset of 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 the second database system.

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

24. The method of claim 1, wherein the second schema definition utilizes a columnar-store data format.

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

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 set 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 a first actual execution of the query set on the first database system and a second actual execution of the corresponding query set on the second database system.

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

10. The method of claim 8, wherein at least one of the first actual execution or the second actual execution is grouped according to the plurality of hierarchies to execute in-memory.

11. The method of claim 10, wherein the at least one of the first actual execution or the second actual execution is further grouped by hierarchies on shared database objects, wherein at least a portion of the shared database objects is retained in-memory between the at least one of the first actual execution or the second actual execution 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.

21. The method of claim 10, wherein the at least one of the first actual execution or the second actual execution utilizes a virtual cache to load and unload the particular database objects into a memory.

22. The method of claim 21, wherein the particular database objects are unloaded from the memory when no longer necessary for the at least one of the first actual execution or the second actual execution.

23. The method of claim 21, wherein a size of the virtual cache does not exceed a largest parent database object set selected from the parent database object set of each of the plurality of hierarchies.

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

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, wherein the first workload description comprises information about execution of queries against at least a portion of the first plurality of database objects;

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 and is compatible with a second database system;

wherein the generating of the second schema definition includes, based on the first workload description and the first schema definition, selecting a subset of database objects to translate to a particular form that is described by said second schema definition and that is compatible with the second database system, wherein the subset of database objects belong to said first plurality of database objects.

18. The non-transitory computer-readable medium of claim 17, wherein the second database system stores the one or more second database objects, and wherein the selecting of the subset of 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 subset of database objects by translating the database queries to the second schema definition.

25. The non-transitory computer-readable medium of claim 18, wherein the reduction in execution time is estimated based on statistics of the first database system.

33. The non-transitory computer-readable medium of claim 17, wherein the one or more sequences of instructions which, when executed by one or more processors, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause:

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

34. The non-transitory computer-readable medium of claim 17, wherein the one or more sequences of instructions which, when executed by one or more processors, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause:

outputting a report describing the subset of 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 the second database system.

35. The non-transitory computer-readable medium of claim 17, wherein the one or more sequences of instructions which, when executed by one or more processors, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause:

generating one or more scripts to configure the second database system as a caching system for the first database system, wherein a database query on the subset of database objects of the first database system is serviced by the second database system translating the database query to the second schema definition.

39. The non-transitory computer-readable medium of claim 17, wherein the second schema definition utilizes a columnar-store data format.

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, wherein the first workload description comprises information about execution of queries against at least a portion of the first plurality of database objects;

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 and is compatible with a second database system;

wherein the generating of the second schema definition includes, based on the first workload description and the first schema definition, selecting a subset of database objects to translate to a particular form that is described by said second schema definition and that is compatible with the second database system, wherein the subset of database objects belong to said first plurality of database objects.

20. The database advisor system of claim 19, wherein the second database system stores the one or more second database objects, and wherein the selecting of the subset of 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 subset of database objects by translating the database queries to the second schema definition.

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

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;

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 set from each of the root queries of the optimized hierarchy set.

27. The non-transitory computer-readable medium of claim 26, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause for each hierarchy of the plurality of hierarchies, the execution time of the query set and the execution time of the corresponding query set to be measured by a first actual execution of the query set on the first database system and a second actual execution of the corresponding query set on the second database system.

28. The non-transitory computer-readable medium of claim 27, wherein the first actual execution uses a test database system of the first database system.

29. The non-transitory computer-readable medium of claim 27, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause at least one of the first actual execution or the second actual execution to be grouped according to the plurality of hierarchies to execute in-memory.

30. The non-transitory computer-readable medium of claim 29, wherein the at least one of the first actual execution or the second actual execution is further grouped by hierarchies on shared database objects, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause at least a portion of the shared database objects to be retained in-memory between the at least one of the first actual execution or the second actual execution of the hierarchies on the shared database objects.

31. The non-transitory computer-readable medium of claim 26, wherein the determining of the optimized hierarchy set utilizes one or more of a multi-path greedy selection algorithm and a dynamic programming algorithm.

32. The non-transitory computer-readable medium of claim 29, wherein the one or more sequences of instructions include instructions that, when executed by one or more processors, cause an algorithm for the determining of the optimized hierarchy set to be chosen based on a size of the first workload description.

36. The non-transitory computer-readable medium of claim 27, wherein the at least one of the first actual execution or the second actual execution utilizes a virtual cache to load and unload the particular database objects into a memory.

37. The non-transitory computer-readable medium of claim 36, wherein the particular database objects are unloaded from the memory when no longer necessary for the at least one of the first actual execution or the second actual execution.

38. The non-transitory computer-readable medium of claim 36, wherein a size of the virtual cache does not exceed a largest parent database object set selected from the parent database object set of each of the plurality of hierarchies.