IP Library › Granted Patent US 11,182,386
Granted Patent B2
US 11,182,386 · App. 16/827,891 · Granted Nov 23, 2021

Offloading statistics collection

Inventors: Dennis Butterstein (Stuttgart, DE); Oliver Benke (Stuttgart, DE); Tobias Ulrich Bergmann (Weinstadt, DE); Felix Beier (Haigerloch, DE); Terence P. Purcell (Springfield, IL)
Assignee: International Business Machines Corporation
G06F16/24545
View Patent ↗
Loading inventors, assignments & file history…
Monitor This Case
Get email alerts when status or documents change.
Order Certified Copies
Most orders are placed with the USPTO same day — all within 24 business hours.
Order via The Patent Place →
Pre-filled with this patent's details
Quick Facts
Patent No.
US 11,182,386
App. No.
16/827,891
Granted
Nov 23, 2021
Kind
B2
Abstract

Methods and systems for generating database statistics. Table statistics in a metadata catalog of a source database system are observed, statistics generation costs utilizing a target database system are estimated, and source statistics generation costs utilizing a source database system are estimated. The statistics generation costs are compared and statistics generation queries by the target database system are triggered in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system. The statistics generation queries are performed by the target database system in response to the triggering by the source database system. The generated statistics are sent from the target database system to the source database system, the table statistics in a metadata catalog are updated based on the generated statistics, and the updated table statistics are used to optimize a query plan.

Claims (52)

1. A method comprising:

observing table statistics in a metadata catalog of a source database system;

estimating statistics generation costs utilizing a target database system and estimating source statistics generation costs utilizing a source database system;

comparing the statistics generation costs utilizing the target database system and the source statistics generation costs utilizing the source database system;

triggering, by the source database system, statistics generation queries by the target database system in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system;

performing, by the target database system, the statistics generation queries in response to the triggering by the source database system;

sending the generated statistics from the target database system to the source database system in response to the triggering by the source database system and the performance of the statistics generation queries;

updating the table statistics in the metadata catalog based on the generated statistics; and

using the updated table statistics to optimize a query plan.

2. The method of claim 1 , further comprising:

repeating the observing, estimating, and comparing operations; and

performing, by the source database system, statistics generation queries in response to the statistics generation costs utilizing the target database system being greater than or equal to the source statistics generation costs utilizing the source database system for the repeated steps.

3. The method of claim 1 , wherein the observing the table statistics further comprises performing one or more of query execution, monitoring incoming changes that are processed on tables of the source database system, and an automated background task.

4. The method of claim 1 , further comprising using existing statistics on the source database system in response to determining that the statistics are not missing and there are no significant data changes.

5. The method of claim 1 , further comprising performing a check to determine if statistics are missing from the metadata catalog, wherein the estimating, comparing, triggering, performing the statistics generation queries, sending, and updating operations are performed in response to a determination of missing statistics.

6. The method of claim 1 , further comprising performing a check to determine if there are significant data changes to a table of the source database system, wherein the estimating, comparing, triggering, performing the statistics generation queries, sending, and updating operations are performed in response to a determination of significant data changes.

7. The method of claim 1 , wherein the predefined relationship is the statistics generation costs utilizing the target database system being less than the source statistics generation costs utilizing the source database system.

8. A non-transitory computer readable medium comprising computer executable instructions which when executed by a computer cause the computer to perform the method of:

observing table statistics in a metadata catalog of a source database system;

estimating statistics generation costs utilizing a target database system and estimating source statistics generation costs utilizing a source database system;

comparing the statistics generation costs utilizing the target database system and the source statistics generation costs utilizing the source database system;

triggering, by the source database system, statistics generation queries by the target database system in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system;

performing, by the target database system, the statistics generation queries in response to the triggering by the source database system;

sending the generated statistics from the target database system to the source database system in response to the triggering by the source database system and the performance of the statistics generation queries;

updating the table statistics in the metadata catalog based on the generated statistics; and

using the updated table statistics to optimize a query plan.

9. The non-transitory computer readable medium of claim 8 , the operations further comprising:

repeating the observing, estimating, and comparing operations; and

performing, by the source database system, statistics generation queries in response to the statistics generation costs utilizing the target database system being greater than or equal to the source statistics generation costs utilizing the source database system.

10. The non-transitory computer readable medium of claim 8 , wherein the predefined relationship is the statistics generation costs utilizing the target database system 308 being within a specified threshold of the source statistics generation costs utilizing the source database system.

11. The non-transitory computer readable medium of claim 8 , the operations further comprising using existing statistics on the source database system in response to determining that the statistics are not missing and there are no significant data changes.

12. The non-transitory computer readable medium of claim 8 , the operations further comprising performing a check to determine if statistics are missing from the metadata catalog, wherein the estimating, comparing, triggering, performing the statistics generation queries, sending, and updating operations are performed in response to a determination of missing statistics.

13. The non-transitory computer readable medium of claim 8 , the operations further comprising performing a check to determine if there are significant data changes to a table of the source database system, wherein the estimating, comparing, triggering, performing the statistics generation queries, sending, and updating operations are performed in response to a determination of significant data changes.

14. A system comprising:

a source database system, the source database system comprising at least one first processor, coupled to a first memory;

a target database system, the target database system comprising at least one second processor, coupled to a second memory, and the system operative to perform operations comprising:

observing table statistics in a metadata catalog of the source database system;

estimating statistics generation costs utilizing a target database system and estimating source statistics generation costs utilizing a source database system;

comparing the statistics generation costs utilizing the target database system and the source statistics generation costs utilizing the source database system;

triggering, by the source database system, statistics generation queries by the target database system in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system;

performing, by the target database system, the statistics generation queries in response to the triggering by the source database system;

sending the generated statistics from the target database system to the source database system in response to the triggering by the source database system and the performance of the statistics generation queries;

updating the table statistics in the metadata catalog based on the generated statistics; and

using the updated table statistics to optimize a query plan.

15. The system of claim 14 , the operations further comprising:

repeating the observing, estimating, and comparing operations; and

performing, by the source database system, statistics generation queries in response to the statistics generation costs utilizing the target database system being greater than or equal to the source statistics generation costs utilizing the source database system.

16. The system of claim 14 , wherein the observing the table statistics further comprises performing one or more of query execution, update processing, and an automated background task.

17. The system of claim 14 , the operations further comprising using existing statistics on the source database system in response to determining that the statistics are not missing and there are no significant data changes.

18. The system of claim 14 , the operations further comprising performing a check to determine if statistics are missing from the metadata catalog, wherein the estimating, comparing, triggering, performing the statistics generation queries, sending, and updating operations are performed in response to a determination of missing statistics.

19. The system of claim 14 , the operations further comprising performing a check to determine if there are significant data changes to a table of the source database system, wherein the estimating, comparing, triggering, performing the statistics generation queries, sending, and updating operations are performed in response to a determination of significant data changes.

20. The system of claim 14 , wherein the target database system is optimized for statistics-like queries.

Assignments (1)
ASSIGNMENT OF ASSIGNOR'S INTEREST Recorded Mar 24, 2020
From: BUTTERSTEIN, DENNIS; BENKE, OLIVER; BERGMANN, TOBIAS ULRICH; BEIER, FELIX; PURCELL, TERENCE P.
To: INTERNATIONAL BUSINESS MACHINES CORPORATION
Reel/Frame 052206/0831 →
Continuity (1)
Related Publication 20210303575A1 · Sep 30, 2021