Offloading statistics collection

By offloading statistics generation to an accelerator database system, the method addresses the issue of outdated statistics in database systems, ensuring efficient and accurate query plan optimization.

JP7588649B2Active Publication Date: 2024-11-22INTERNATIONAL BUSINESS MACHINE CORPORATION
View PDF 6 Cites 0 Cited by

Patent Information

Application Number
JP2022549078
Authority / Receiving Office
JP · JP
Patent Type
Patents
Current Assignee / Owner
Priority Date
2020-03-24
Filing Date
2021-02-24
Publication Date
2024-11-22
Estimated Expiration
2041-02-24

AI Technical Summary

Technical Problem

Existing database systems face challenges in maintaining the accuracy of statistical data used for query plan optimization due to the deviation of initially collected statistics over time, leading to suboptimal query execution plans.

Method used

The method involves offloading the generation of statistics to an accelerator database system, which uses techniques like columnar layout, data compression, and parallel processing to efficiently generate and update statistics, reducing CPU consumption in the source database system.

Benefits of technology

This approach ensures timely and efficient generation of statistics, optimizing query plans by using up-to-date data, thereby reducing CPU consumption and improving query execution performance.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure 0007588649000001
    Figure 0007588649000001
  • Figure 0007588649000002
    Figure 0007588649000002
  • Figure 0007588649000003
    Figure 0007588649000003
Patent Text Reader

Abstract

A method and system for generating database statistics are provided. Table statistics in a metadata catalog of a source database system are observed, a statistics generation cost using a target database system is estimated, and a source statistics generation cost using the source database system is estimated. The statistics generation costs are compared, and a statistics generation query is triggered by the target database system in response to the statistics generation cost using the target database system having a predefined relationship with the source statistics generation cost using the source database system. In response to the triggering by the source database system, the statistics generation query is executed by the target database system. The generated statistics are sent from the target database system to the source database system, the table statistics in the metadata catalog are updated based on the generated statistics, and the updated table statistics are used to optimize query plans.
Need to check novelty before this filing date? Find Prior Art

Description

[Technical field]

[0001] The present invention relates to electrical, electronic, and computer technologies, and more particularly to relational databases. [Background technology]

[0002] Database systems typically schedule database queries into query plans. Traditionally, a query plan is represented as a tree of relational operators. Optimizers are often used to generate more optimal plans for executing queries, either to reduce cost or improve performance. For example, statistics about the distribution of data in the tables of the database may be used to help optimize the query plan. Plans may be optimized based on cost, with the goal of determining the least costly query plan for executing a received query.

[0003] Statistics can be based on the distribution of data and indexes in the database tables, commonalities of data, etc. Statistics can be generated based on a single column or multiple columns. Statistics can include histograms that represent the distribution of values ​​in columns, and sometimes correlations of values ​​between columns. Most relational databases today rely heavily on this statistical information to calculate the fastest execution plan for processing queries. Traditional database products implement some functionality for query tracking and query analysis that relies on this specific data. Therefore, the quality of the plan chosen by the optimizer depends on the accuracy of the statistical data collected. As queries change the state of the database, the initially collected statistical data deviates over time. As a result, the quality of the chosen plan deteriorates.

[0004] To ensure a high quality of the plan chosen, statistical data needs to be updated periodically. These updates have associated costs, so the database system may, for example, select the tables on which to gather statistics, sample the data of the selected tables, and extrapolate the collected information based on the size of the tables. This data then allows the optimizer to estimate or predict the number of rows in a query result. This in turn allows the optimizer to generate efficient query plans. For example, the optimizer may choose a more effective index scan operator in the query plan. Summary of the Invention

[0005] The principles of the present invention provide techniques for offloading the collection of statistics. In one aspect, an exemplary method includes the acts of observing table statistics in a metadata catalog of a source database system, estimating a statistics generation cost utilizing a target database system, estimating a source statistics generation cost utilizing the source database system, comparing the statistics generation cost utilizing the target database system and the source statistics generation cost utilizing the source database system, triggering a statistics generation query by the target database system by the source database system in response to the statistics generation cost utilizing the target database system having a predefined relationship with the source statistics generation cost utilizing the source database system, executing the statistics generation query by the target database system 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 execution of the statistics generation query, updating the table statistics in the metadata catalog based on the generated statistics, and using the updated table statistics to optimize a query plan.

[0006] In one aspect, a non-transitory computer readable medium includes computer executable instructions that, when executed by a computer, cause the computer to perform a method including observing table statistics in a metadata catalog of a source database system; estimating a statistics generation cost utilizing a target database system, estimating a source statistics generation cost utilizing the source database system; comparing the statistics generation cost utilizing the target database system and the source statistics generation cost utilizing the source database system; triggering by the source database system a statistics generation query by the target database system in response to the statistics generation cost utilizing the target database system having a predefined relationship with the source statistics generation cost utilizing the source database system; executing by the target database system the statistics generation query 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 execution of the statistics generation query; updating the table statistics in the metadata catalog based on the generated statistics; and using the updated table statistics to optimize a query plan.

[0007] In one aspect, a system includes a source database system having at least one first processor coupled to a first memory and a target database system having at least one second processor coupled to a second memory, the system being operable to perform operations, the operations including observing table statistics in a metadata catalog of the source database system, estimating a statistics generation cost utilizing the target database system, estimating a source statistics generation cost utilizing the source database system, comparing the statistics generation cost utilizing the target database system to the source statistics generation cost utilizing the source database system, and estimating a source statistics generation cost utilizing the target database system. triggering, by the source database system, a statistics generation query by the target database system in response to a statistics generation cost utilizing the target database system having a predefined relationship with a source statistics generation cost utilizing the source database system; executing, by the target database system, the statistics generation query 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 execution of the statistics generation query; updating table statistics in the metadata catalog based on the generated statistics; and using the updated table statistics to optimize a query plan.

[0008] As used herein, "facilitating" an action includes performing an action, facilitating an action, assisting in the performance of an action, or having an action performed. Thus, by way of example and not limitation, instructions executed on one processor may facilitate an action performed by instructions executed on a remote processor by sending appropriate data or commands to cause or assist in the performance of an action. For the avoidance of doubt, an action is performed by some entity or combination of entities even if an actor facilitates an action in a way other than performing the action.

[0009] One or more embodiments of the invention or elements thereof may be implemented in the form of a computer program product including a computer readable storage medium having computer usable program code for performing the illustrated method steps. Furthermore, one or more embodiments of the invention or elements thereof may be implemented in the form of a system (or apparatus) including a memory and at least one processor coupled to the memory and operative to perform the illustrated method steps. Still further, in other aspects, one or more embodiments of the invention or elements thereof may be implemented in the form of a means for performing one or more of the method steps described herein, which may include (i) hardware module(s), (ii) software module(s) stored in a computer readable storage medium(s) and implemented on a hardware processor, or (iii) a combination of (i) and (ii), any of (i)-(iii) implementing certain techniques described herein.

[0010] The techniques of the present invention can provide highly beneficial technical effects. For example, one or more embodiments may provide one or more of the following: An accelerator database system optimized for processing statistical queries across large data sets. Efficient accelerator-based generation of database statistics for a source database system by an accelerator (target) database system. Reducing central processing unit (CPU) consumption of the source database system. Ability to generate query-specific runtime statistics before the actual query is executed (the tables used by a particular query are known, so statistical correlation can be tailored). No additional or dedicated system is required to collect statistics.

[0011] These and other features and advantages of the present invention will become apparent from the following detailed description of illustrative embodiments thereof, which is to be read in connection with the accompanying drawings. [Brief description of the drawings]

[0012] [Figure 1] FIG. 1 illustrates a cloud computing environment according to one embodiment of the present invention. [Diagram 2] FIG. 2 illustrates abstraction model layers according to one embodiment of the present invention. [Diagram 3] FIG. 1 is a block diagram of an exemplary database system in accordance with an exemplary embodiment. [Figure 4] 4 is a flowchart of an exemplary method for generating and utilizing database statistics, according to an exemplary embodiment. [Diagram 5] FIG. 1 illustrates a computer system that may be useful in implementing one or more aspects and / or elements of the present invention, and may represent a cloud computing node, in accordance with one embodiment of the present invention. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0013] Although this disclosure includes detailed descriptions of cloud computing, it should be understood that implementation of the teachings recited herein is not limited to a cloud computing environment. Rather, embodiments of the invention may be implemented in conjunction with any other type of computing environment now known or later developed.

[0014] Cloud computing is a model of service delivery for enabling convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, network bandwidth, servers, processing, memory, storage, applications, virtual machines, and services) that can be rapidly provisioned and released with minimal administrative effort or interaction with a service provider. The cloud model may include at least five characteristics, at least three service models, and at least four deployment models.

[0015] Its features are as follows.

[0016] On-Demand Self-Service: Cloud consumers can unilaterally provision computing capacity, such as server time and network storage, automatically as needed, without the need for human interaction with the provider of the service.

[0017] Broad Network Access: Capabilities are available over the network and are accessed through standard mechanisms that facilitate use by heterogeneous thin or thick client platforms (e.g., cell phones, laptops, and PDAs).

[0018] Resource Pooling: Pooling provider computing resources to serve multiple consumers using a multi-tenant model where various physical and virtual resources are dynamically allocated and reallocated according to demand. Consumers generally have no control over or knowledge of the exact location of the resources provided to them, although there is a sense of location independence in that the location may be identifiable at a higher level of abstraction (e.g., country, state, or data center).

[0019] Rapid Elasticity: Capacity can be rapidly and elastically provisioned, sometimes automatically, to quickly scale out and rapidly release to quickly scale in. To the consumer, provisionable capacity often appears to be unlimited and can be purchased in any quantity at any time.

[0020] Metered Services: Cloud systems leverage metering capabilities at some level of abstraction appropriate to the type of service (e.g., storage, processing, bandwidth, and active user accounts) to automatically control and optimize resource usage. Resource usage can be monitored, managed, and reported to provide transparency to both providers and consumers of the services utilized.

[0021] The service model is as follows:

[0022] Software as a Service (SaaS): The capability offered to the consumer is to use the provider's applications running on a cloud infrastructure. The applications are accessible from a variety of client devices through thin-client interfaces such as web browsers (e.g., web-based email). The consumer has no management or control over the underlying cloud infrastructure, including networks, servers, operating systems, storage, or even individual application features, with the possible exception of limited user-specific application configuration settings.

[0023] Platform as a Service (PaaS): The ability provided to a consumer is to deploy applications that the consumer creates or acquires, written using programming languages ​​and tools supported by the provider, onto a cloud infrastructure. The consumer does not manage or control the underlying cloud infrastructure, including networks, servers, operating systems, or storage, but does control the deployed applications and possibly the application hosting environment configuration.

[0024] Infrastructure as a Service (IaaS): The ability offered to consumers is to provision processing, storage, network, and other basic computing resources onto which the consumer can deploy and run any software, which may include operating systems and applications. The consumer does not manage or control the underlying cloud infrastructure, but does control the operating systems, storage, deployed applications, and possibly limited control over selected networking components (e.g., host firewalls).

[0025] The deployment model is as follows:

[0026] Private Cloud: The cloud infrastructure is operated exclusively for an organization. It can be managed by the organization or a third party and can be on-premise or off-premise.

[0027] Community Cloud: The cloud infrastructure is shared by several organizations to support a specific community with common concerns (e.g., mission, security requirements, policies, and compliance considerations). It may be managed by the organization or a third party and may exist on-premise or off-premise.

[0028] Public Cloud: The cloud infrastructure is made available to the general public or large industry organizations and is owned by an organization that sells cloud services.

[0029] Hybrid Cloud: A cloud infrastructure is a composite of two or more clouds (private, community, or public) that remain unique entities but are joined by standardized or proprietary technologies that enable data and application portability (e.g., cloud bursting for load balancing between clouds).

[0030] Cloud computing environments are service-oriented with an emphasis on statelessness, low coupling, modularity, and semantic interoperability. At the core of cloud computing is an infrastructure that includes a network of interconnected nodes.

[0031] Referring now to FIG. 1, an exemplary cloud computing environment 50 is shown. As shown, the cloud computing environment 50 includes one or more cloud computing nodes 10 with which local computing devices used by cloud consumers, such as, for example, a personal digital assistant (PDA) or mobile phone 54A, a desktop computer 54B, a laptop computer 54C, or an automobile computer system 54N, or combinations thereof, may communicate. The nodes 10 may communicate with each other. They may be physically or virtually grouped (not shown), for example, in one or more networks, such as the private, community, public, or hybrid clouds described above, or combinations thereof. This enables the cloud computing environment 50 to provide infrastructure-as-a-service, platform-as-a-service, and / or software-as-a-service services without the need for cloud consumers to maintain resources on their local computing devices. It should be understood that the types of computing devices 54A-N illustrated in FIG. 1 are intended to be exemplary only, and that the computing node 10 and cloud computing environment 50 can communicate with any type of computerized device via any type of network and / or network-addressable connections (e.g., using a web browser).

[0032] Referring now to Figure 2, a set of functional abstraction layers provided by cloud computing environment 50 (Figure 1) is shown. It should be understood in advance that the components, layers, and functions shown in Figure 2 are intended to be exemplary only, and embodiments of the present invention are not limited thereto. As shown, the following layers and corresponding functions are provided:

[0033] The hardware and software layer 60 includes hardware and software components. Examples of hardware components include mainframes 61, RISC (Reduced Instruction Set Computer) architecture based servers 62, servers 63, blade servers 64, storage devices 65, and networks and networking components 66. In some embodiments, the software components include network application server software 67 and database software 68.

[0034] The virtualization layer 70 provides an abstraction layer from which the following examples of virtual entities may be provided: virtual servers 71, virtual storage 72, virtual networks including virtual private networks 73, virtual applications and operating systems 74, and virtual clients 75.

[0035] In one example, the management layer 80 may provide the following functions: Resource provisioning 81 provides dynamic procurement of computing and other resources utilized to execute tasks within the cloud computing environment. Metering and pricing 82 provides cost tracking as resources are utilized within the cloud computing environment and accounting or billing for the consumption of these resources. In one example, these resources may include application software licenses. Security provides identity verification of cloud consumers and tasks as well as protection for data and other resources. User portal 83 provides consumers and system administrators with access to the cloud computing environment. Service level management 84 provides allocation and management of cloud computing resources such that requested service levels are met. Service level agreement (SLA) planning and fulfillment 85 provides advance arrangement and procurement of cloud computing resources anticipated to be required in the future according to SLAs.

[0036] The workload layer 90 provides examples of functionality for which a cloud computing environment may be utilized. Examples of workloads and functions that may be provided from this layer include mapping and navigation 91, software development and lifecycle management 92, virtual classroom instructional delivery 93, data analytics processing 94, transaction processing 95, and database manager 96. It should be emphasized that embodiments of the present invention may be implemented in a cloud environment (e.g., in the database software 68 elements of layer 60, or in the database manager 96 of layer 90, or both), in a non-cloud environment, or in a hybrid manner having portions in the cloud and portions in a non-cloud (e.g., local) manner.

[0037] Generally, apparatus, systems, and methods for offloading statistics generation and collection are disclosed, which are useful, for example, for generating and utilizing database statistics. The generated statistical data is offloaded, for example, to an accelerator database system, which is optimized for statistics-like queries by using columnar layouts of database tables, caching data in memory, utilizing data compression techniques, parallel processing, special indexing techniques, and the like. In one exemplary embodiment, the accelerator database system (also referred to herein as a target database system) incrementally maintains and updates a statistics store of a production database system (also referred to herein as a source database system) by capturing snapshots of ongoing transactions of one or more database systems (e.g., Db2® database system) (a registered trademark of International Business Machines Corporation, Armonk, NY, USA).

[0038] A reliable indicator of whether the statistical data for a particular table is out of date is the number of insert, update, and delete statements executed on the table. This can be determined by counting the corresponding queries per table. In one exemplary embodiment, sampling is used to perform cardinality calculations quickly. (In the database context, cardinality refers to the uniqueness of the data values ​​contained in a column. High cardinality means that the column contains many completely unique values. Low cardinality means that the column contains many "repetitions" within its data range.) In one exemplary embodiment, larger sub-plans are executed on the target database system and run-time cardinalities are used that are collected during execution on the target database system (e.g., tools such as the Learning Optimizer (LEO) collect such statistics; see Stillger, Michael et al. "LEO-DB2's LEarning Optimizer." VLDB (2001)). If a subplan takes too long to execute, thresholding can be applied to pick a local estimate instead of waiting for the exact cardinality.

[0039] Statistics may be generated incrementally so that the statistics reflect the most recent data, or when a statistic update is required. In one exemplary embodiment, the amount of changes applied to the table is tracked, and a statistics gathering task is triggered when a larger percentage of the table (e.g., 15%, but this is a non-limiting example and the percentage can be configurable) has changed. In one exemplary embodiment, background tasks are scheduled to check if the table has changed at regular intervals, to check the amount of changes to the table, etc.

[0040] In one or more embodiments, statistical data change information is extracted from transactions and used to update a local statistics store that is accessible by production database systems to support query plan selection.

[0041] In one exemplary embodiment, the accelerator database system performs the generation of statistics for the production database system. For example, if the cost of performing the generation of statistics by the source database system is higher than the accelerator database system, the generation of statistics can be offloaded to the accelerator database system. The statistics generated by the accelerator database system are used by the source database system, the accelerator database system, or both.

[0042] FIG. 3 is a block diagram of an exemplary database system 300 according to an exemplary embodiment. A source database system 304 processes database queries 352 (referred to herein as queries 352) received from applications and other entities. A query scheduler 312 generates query plans that are executed locally (within the source database system 304) or remotely (within the target database system 308, as described in more detail below). In one exemplary embodiment, an optimizer in the query scheduler 312 optimizes the query plans, such as to determine the least costly query plan for executing the received queries. For example, a cost-based optimizer may utilize database statistics to facilitate optimizing the query plans. The statistics may relate to the distribution of data in tables 316 of the source database system 304, commonality of data, etc.

[0043] Once the query plan is generated, it is executed by either the source database system 304 or the target database system 308. Local query execution is performed against one or more of the tables 316 in the source database 320. Accelerator-based query execution is performed against one or more of the tables 336 in the target database 340. The data synchronization system 348 synchronizes the tables 336 in the target database 340 with the tables 316 in the source database 320, which is a prerequisite for collecting statistical data to improve the selection of the query plan. For example, the data synchronization system 348 receives a trigger from the source database 320 indicating that the data of the tables 316 in the source database 320 should be fully or partially transferred to update the target database 340. In one exemplary embodiment, the query scheduler 312, the table statistics generator 332, the data synchronization system 348, and the statistics offload unit 356 are implemented in computer system executable instructions, such as program modules, executed by a computer system, such as a server. For example, the query scheduler 312 includes code implementing an optimizer (e.g., cost-based) that optimizes query plans, such as to determine the least costly query plan for executing a received query. The generator 332 includes code implementing functionality discussed in the next paragraph. The system 348 includes code that responds to triggers, such as those just mentioned. The statistics offload unit 356 is responsible for detecting whether statistics need to be refreshed. In one exemplary embodiment, the statistics offload unit 356 abstracts the actual collection execution, for example, by offloading to the target (accelerator) database system 308 or by collecting locally at the source database system 304. That is, it implements various communication protocols for refreshing statistics in the source database system 304.

[0044] When a query is executed, statistics are created or updated in the source statistics table 328 of the metadata catalog 324. The statistics are then provided to the query scheduler 312 for use in optimizing the query plan. In one exemplary embodiment, the statistics offload unit 356 offloads tasks related to generating statistics to a table statistics generator 332 in the target database system 308. The table statistics generator 332 is sent, for example, a list of tables to be considered and the type of statistics to be generated. In one exemplary embodiment, this is converted into a statistics query by the table statistics generator 332. Alternatively, the statistics offload unit 356 may implement the query generation component of the table statistics generator 332 itself and simply send the statistics query to update the source table statistics 328.

[0045] In one exemplary embodiment, the generation of statistics is offloaded to the table statistics generator 332 only if the cost of generating statistics on the target database system 308 is lower than generating statistics on the source database system 304. In one exemplary embodiment, the generation of statistics is always offloaded to the table statistics generator 332, while other embodiments may take different approaches. In one exemplary embodiment, the same mechanism that determines when the generation of statistics should be offloaded is used for application query execution. If the statistics offload unit 356 generates the statistics queries itself, it can simply schedule the queries via the query scheduler 312 (as in the case of user queries). The query scheduler 312 analyzes the query statements and determines the best system to execute them on (the source database system 304 or the target database system 308). In one exemplary embodiment, the statistics offload unit 356 functions like a normal database client that receives the statistics query results and updates the statistics regardless of where the statistics were collected. In one exemplary embodiment, further details of this particular internal query execution use case are taken into account, e.g., a special cost function for statistical queries is provided (which may have different optimization goals, e.g., minimizing execution time / minimizing resource utilization on the source database system 304, etc.). In either case, the offloading strategy may be modeled with a cost function that indicates the "expensiveness" of offloading the corresponding task in terms of the cost of resources required for the task.

[0046] In the target database system 308, as queries are executed, statistics are created or updated in the target table statistics 344. In one exemplary embodiment, the target database system 308 includes a metadata catalog (not shown) that performs similar functionality for the target database system 308 as the metadata catalog 324 performs for the source database system 304. Also, statistics are created and / or updated in the target table statistics 344 in response to triggers generated by the statistics offload unit 356. Triggers may be generated periodically, such as when a certain amount of changes to a table are detected in the source database system 304. In one exemplary embodiment, the target database system 308 pushes statistics to the source database system 304 periodically, such as when the target database system 308 generates or updates statistics in response to an internal request. In either case, the statistics generated in the target database system 308 are transferred to the source statistics table 328 for use by the query scheduler 312 in optimizing the query plan.

[0047] FIG. 4 is a flow chart of an example method 400 for generating and utilizing database statistics, according to an example embodiment. In one example embodiment, table statistics in the metadata catalog 324 of the source database system 304 are observed (act 404). A check is performed to determine if statistics are missing or if there are major data changes (decision block 408). If nothing exists in the metadata catalog 324 for the table, such as cardinality information, statistics should be generated. The "significant" threshold (e.g., changes to 15% of the table) is a way to minimize the additional cost incurred by generating statistics. Of course, this could be done (incrementally in the best case) after each table change, but this may consume more resources than would be saved by a better query plan (due to more accurate cost estimates). If statistics are not missing and there are no major data changes (NO branch at decision block 408), the existing statistics are used by the source database system 304 (act 412) and the method 400 ends.

[0048] If statistics are missing or there are large data changes, or both (YES branch at decision block 408), the cost of generating statistics is estimated by the source database system 304 and the target database system 308 individually (operation 416).

[0049] A check is performed (decision block 420) to determine whether the cost of generating statistics on the target database system 308 is less than the cost of generating statistics on the source database system 304. If the cost of the target database system 308 is greater than or equal to the cost of the source database system 304 (NO branch at decision block 420), statistics are generated at the source database system 304 (act 424) and the method 400 proceeds to act 440.

[0050] If the cost of the target database system 308 is less than the cost of the source database system 304 (YES branch at decision block 420), generation of statistics is triggered via the target database system 308 (act 428), a statistics generation query is executed at the target database system 308 (act 432), and the generated statistics are sent from the target database system 308 to the metadata catalog 324 of the source database system 304 (act 436). Statistics are generated on the target database system 308 to: 1) gather special statistics and histograms that are important for queries executed on the source database system 304, and / or 2) gather specific statistics, such as column group statistics targeted to specific types of queries, to optimize frequently executed queries and / or to prepare for specific complex queries. Statistics may also be generated for use by the target database system 308.

[0051] Returning to act 440, after the statistics are generated, the source statistics table 328 is updated in the metadata catalog 324 (act 440) and the updated statistics are used, for example, to optimize the query plan (act 444). For example, a plan that best matches the filter attributes may be selected by executing a subset of the original query, focusing on cardinality, in the target database system 308 before initiating query execution in the Db2® software. In one exemplary embodiment, a dynamic structured query language (SQL) is used for each query. One or more embodiments in the relational database domain, where SQL is standard, may be utilized. However, there are other database systems that use different query languages, such as XQuery for XML data or graph queries for graph databases, and the techniques disclosed herein are also applicable to these databases.

[0052] In light of the foregoing discussion, generally, an exemplary method according to an aspect of the present invention includes observing table statistics 328 in the metadata catalog 324 of the source database system 304 (operation 404); estimating a cost of generating statistics using a target database system 308 and estimating a source cost of generating statistics using the source database system 304 (operation 416); comparing the cost of generating statistics using the target database system 308 to the source cost of generating statistics using the source database system 304 (decision block 420); and, in response to the cost of generating statistics using the target database system 308 having a predefined relationship with the source cost of generating statistics using the source database system 304, estimating a cost of generating statistics using the target database system 308 and estimating a source cost of generating statistics using the source database system 304 (decision block 420). It will be understood that the method includes the operations of triggering a statistics generation query by the source database system 304 (operation 428), executing the statistics generation query by the target database system 308 in response to the triggering by the source database system 304 (operation 432), sending the generated statistics from the target database system 308 to the source database system 304 in response to the triggering by the source database system 304 and the execution of the statistics generation query (operation 436), updating table statistics 328 in the metadata catalog 324 based on the generated statistics (operation 440), and using the updated table statistics 328 to optimize the query plan (operation 444).

[0053] In one exemplary embodiment, the observing, estimating, and comparing operations are repeated, and in response to the statistics generation cost utilizing the target database system 308 being equal to or greater than the source statistics generation cost utilizing the source database system 304 for the repeated steps, a statistics generation query is executed by the source database system 304 (operation 424). In one exemplary embodiment, the observing table statistics 328 operation further includes executing one or more of query execution, monitoring incoming changes processed on the table of the source database system 304, and an automated background task. In one exemplary embodiment, in response to determining that no statistics are missing and that there are no major data changes, existing statistics on the source database system 304 are used (operation 412). In one exemplary embodiment, a check is performed to determine whether statistics are missing from the metadata catalog 324 (decision block 408), and in response to determining that statistics are missing, the operations of estimating, comparing, triggering, executing statistics generation queries, sending, and updating are performed.

[0054] In one exemplary embodiment, a check is performed to determine if there is a large data change in the table of the source database system 304 (decision block 408) and in response to the determination of a large data change, the operations of estimation, comparison, trigger, run statistics generation query, send, and update are performed. In one exemplary embodiment, the predefined relationship is that the statistics generation cost using the target database system 308 is less than the source statistics generation cost using the source database system 304. In one exemplary embodiment, the target database system 308 is optimized for statistics-like queries.

[0055] In one aspect, a non-transitory computer readable medium includes computer executable instructions that, when executed by a computer, cause the computer to perform a method, the method including observing table statistics 328 in the metadata catalog 324 of the source database system 304 (operation 404); estimating a statistics generation cost using the target database system 308 and estimating a source statistics generation cost using the source database system 304 (operation 416); comparing the statistics generation cost using the target database system 308 to the source statistics generation cost using the source database system 304 (decision block 420); and determining whether the statistics generation cost using the target database system 308 is greater than the source statistics generation cost using the source database system 304. In response to having the predefined relationship, triggering a statistics generation query by the target database system 308 by the source database system 304 (act 428); executing the statistics generation query by the target database system 308 in response to the triggering by the source database system 304 (act 432); sending the generated statistics from the target database system 308 to the source database system 304 in response to the triggering by the source database system 304 and the execution of the statistics generation query (act 436); updating table statistics 328 in the metadata catalog 324 based on the generated statistics (act 440); and using the updated table statistics 328 to optimize a query plan (act 444).

[0056] In one aspect, a system comprises a source database system 304 comprising at least one first processor coupled to a first memory and a target database system 308 comprising at least one second processor coupled to a second memory, the system being operable to perform operations including observing table statistics 328 in a metadata catalog 324 of the source database system 304 (operation 404); estimating a statistics generation cost using the target database system 308 and estimating a source statistics generation cost using the source database system 304 (operation 416); comparing the statistics generation cost using the target database system 308 to the source statistics generation cost using the source database system 304 (decision block 420); triggering a statistics generation query by the target database system 308 by the source database system 304 in response to the cost having a predefined relationship with the source statistics generation cost utilizing the source database system 304 (act 428); executing the statistics generation query by the target database system 308 in response to the triggering by the source database system 304 (act 432); sending the generated statistics from the target database system 308 to the source database system 304 in response to the triggering by the source database system 304 and the execution of the statistics generation query (act 436); updating table statistics 328 in the metadata catalog 324 based on the generated statistics (act 440); and using the updated table statistics 328 to optimize the query plan (act 444).

[0057] One or more embodiments of the present invention or elements thereof may be implemented in the form of an apparatus including a memory and at least one processor coupled to the memory and operative to execute exemplary method steps. FIG. 5 illustrates a computer system useful in implementing one or more aspects and / or elements of the present invention, according to an embodiment of the present invention, and which may represent a cloud computing node. With reference now to FIG. 5, the cloud computing node 10 is merely one example of a suitable cloud computing node and is not intended to suggest any limitation as to the scope of use or functionality of the embodiments of the present invention described herein. In any event, the cloud computing node 10 may implement and / or execute any of the above-described functionality.

[0058] Cloud computing node 10 includes computer system / server 12, which is operable in many other general purpose or special purpose computing system environments or configurations. Examples of well-known computing systems, environments, or configurations, or combinations thereof, that may be suitable for use with computer system / server 12 include, but are not limited to, personal computer systems, server computer systems, thin clients, thick clients, handheld or laptop devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputer systems, mainframe computer systems, and distributed cloud computing environments that include any of the above systems or devices.

[0059] The computer system / server 12 may be described in the general context of computer system executable instructions, such as program modules, executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. The computer system / server 12 may be practiced in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computer system storage media, including memory storage devices.

[0060] 5, computer system / server 12 in cloud computing node 10 is shown in the form of a general purpose computing device. Components of computer system / server 12 may include, but are not limited to, one or more processors or processing units 16, a system memory 28, and a bus 18 that couples various system components, including system memory 28, to processor 16.

[0061] Bus 18 represents any one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of a variety of bus architectures, including, by way of example and not limitation, an Industry Standard Architecture (ISA) bus, a Micro Channel Architecture (MCA) bus, an Enhanced ISA (EISA) bus, a Video Electronics Standards Association (VESA) local bus, and a Peripheral Component Interconnects (PCI) bus.

[0062] Computer system / server 12 typically includes a variety of computer system readable media. Such media may be any available media that is accessible by computer system / server 12 and includes both volatile and nonvolatile media, removable and non-removable media.

[0063] The system memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. The computer system / server 12 may further include other removable / non-removable volatile / non-volatile computer system storage media. By way of example only, a storage system 34 may be provided for reading from and writing to a non-removable, non-volatile magnetic medium (not shown, typically referred to as a "hard drive"). Although not shown, a magnetic disk drive may be provided for reading from and writing to a removable, non-volatile magnetic disk (e.g., a "floppy disk"), and an optical disk drive may be provided for reading from and writing to a removable, non-volatile optical disk, such as a CD-ROM, DVD-ROM, or other optical medium. In such an example, each may be connected to the bus 18 by one or more data media interfaces. As further illustrated and described below, the memory 28 may include at least one program product having a set (e.g., at least one) of program modules configured to implement the functions of an embodiment of the present invention.

[0064] A program / utility 40 having a set (at least one) of program modules 42 may be stored in memory 28, as well as, by way of example and not limitation, an operating system, one or more application programs, other program modules, and program data. Each of the operating system, one or more application programs, other program modules, and program data, or any combination thereof, may comprise an implementation of a networking environment. The program modules 42 generally implement the functionality and / or methodology of embodiments of the invention described herein.

[0065] The computer system / server 12 may also communicate with one or more external devices 14, such as a keyboard, pointing device, display 24, one or more devices that allow a user to interact with the computer system / server 12, or any device that allows the computer system / server 12 to communicate with one or more other computing devices (e.g., network cards, modems, etc.), or combinations thereof. Such communication may occur via an input / output (I / O) interface 22. Furthermore, the computer system / server 12 may communicate with one or more networks, such as a local area network (LAN), a general wide area network (WAN), or a public network (e.g., the Internet), or combinations thereof, via a network adapter 20. As shown, the network adapter 20 communicates with other components of the computer system / server 12 via a bus 18. It is understood that other hardware and / or software components, not shown, may be used with the computer system / server 12. Examples include, but are not limited to, microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data archive storage systems.

[0066] As such, one or more embodiments may utilize software running on a general purpose computer or workstation. With reference to FIG. 5, such implementations may employ, for example, a processor 16, memory 28, and input / output interface 22 to a display 24 and external device(s) 14, e.g., a keyboard, pointing device, and the like. As used herein, the term "processor" is intended to include any processing device, e.g., including a CPU (Central Processing Unit), or other forms of processing circuitry, or both. Additionally, the term "processor" may refer to two or more individual processors. The term "memory" is intended to include memory associated with a processor or CPU, e.g., RAM (random access memory) 30, ROM (read only memory), fixed memory devices (e.g., hard drive 34), removable memory devices (e.g., diskettes), flash memory, and the like. Also, as used herein, the phrase "input / output interface" is intended to contemplate an interface to, for example, one or more mechanisms for inputting data into a processing unit (e.g., a mouse) and one or more mechanisms for providing results related to the processing unit (e.g., a printer). The processor 16, memory 28, and input / output interface 22 may be interconnected, for example, via a bus 18 as part of the data processing unit 12. Suitable interconnections, such as via the bus 18, may also be provided to a network interface 20, such as a network card, which may be provided for interfacing with a computer network, and a media interface, such as a diskette or CD-ROM drive, which may be provided for interfacing with suitable media.

[0067] Thus, computer software containing instructions or code for carrying out the inventive methodologies described herein may be stored in one or more of the associated memory devices (e.g., ROM, fixed or removable memory) and loaded partially or entirely (such as into RAM) and executed by the CPU when ready for use. Such software may include, but is not limited to, firmware, resident software, microcode, etc.

[0068] A data processing system suitable for storing and / or executing program code includes at least one processor 16 coupled directly or indirectly to memory elements 28 via a system bus 18. The memory elements may include local memory utilized during the actual execution of the program code, bulk storage, and cache memory 32 that provides temporary storage of at least some of the program code to reduce the number of times the code needs to be retrieved from bulk storage during execution.

[0069] Input / output or I / O devices (including but not limited to keyboards, displays, pointing devices, etc.) can be coupled to the system either directly or through intervening I / O controllers.

[0070] Network adapters 20 may also be coupled to the system to enable the data processing system to become coupled to other data processing systems or remote printers or storage devices through intervening private or public networks. Modems, cable modems, and Ethernet® cards are just a few of the currently available types of network adapters.

[0071] As used herein, including in the claims, a "server" includes a physical data processing system (e.g., system 12 shown in FIG. 5) that executes a server program. It will be understood that such a physical server may or may not include a display and keyboard.

[0072] One or more embodiments may be implemented at least in part in the context of a cloud or virtual machine environment, which is by way of example and not limitation (see Figures 1-2 and the accompanying text).

[0073] It should be noted that any of the methods described herein may include the additional step of providing a system including separate software modules embodied on a computer-readable storage medium, where the modules may include any or all of the appropriate elements, e.g., as shown in the block diagrams and / or described herein, and may include, by way of example and not limitation, any one, some or all of the described modules / blocks and / or sub-modules / sub-blocks. And the method steps may be performed using separate software modules and / or sub-modules of the above-mentioned system running on one or more hardware processors, such as 16. Additionally, a computer program product may include a computer-readable storage medium having code configured to be implemented to perform one or more method steps described herein, including providing a system having separate software modules.

[0074] One example of a user interface that may be used in some cases is hypertext markup language (HTML) code served by a server or other device to a browser on a user's computing device, which is parsed by the browser on the user's computing device to generate a graphical user interface (GUI).

[0075] Exemplary System and Product Details

[0076] The present invention may be a system, method, or computer program product, or combination thereof, at any possible level of technical detail integration. The computer program product may include a computer-readable storage medium (or media) having computer-readable program instructions thereon for causing a processor to implement aspects of the present invention.

[0077] A computer readable storage medium may be any tangible device capable of holding and storing instructions for use by an instruction execution device. A computer readable storage medium may be, for example, but not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. A non-exhaustive list of more specific examples of computer readable storage media includes portable computer diskettes, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disc read-only memory (CD-ROM), digital versatile disk (DVD), memory stick, floppy disk, mechanically encoded devices such as punch cards or grooved ridge structures having instructions recorded thereon, and any suitable combination thereof. A computer-readable storage medium, as used herein, should not be construed to be a transitory signal per se, such as, for example, radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through a waveguide or other transmission medium (e.g., light pulses passing through a fiber optic cable), or electrical signals transmitted over a wire.

[0078] The computer readable program instructions described herein may be downloaded from a computer readable storage medium to the respective computing / processing device or to an external computer or storage device over a network, such as, for example, the Internet, a local area network, a wide area network, or a wireless network, or a combination thereof. The network may include copper transmission cables, optical transmission fiber, wireless transmission, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. A network adapter card or network interface of each computing / processing device receives the computer readable program instructions from the network and transfers the computer readable program instructions for storage in a computer readable storage medium within the respective computing / processing device.

[0079] The computer readable program instructions for carrying out the operations of the present invention may be source or object code written in any combination of one or more programming languages, including assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for an integrated circuit, or object-oriented programming languages ​​such as Smalltalk®, C++, and procedural programming languages ​​such as the “C” programming language or similar programming languages. The computer readable program instructions may be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or wide area network (WAN), or a connection may be made to an external computer (e.g., via the Internet using an Internet Service Provider). In some embodiments, electronic circuitry including, for example, a programmable logic circuit, a field-programmable gate array (FPGA), or a programmable logic array (PLA), may be personalized by utilizing state information of the computer-readable program instructions to execute the computer-readable program instructions to perform aspects of the invention.

[0080] Aspects of the present invention are described herein with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer readable program instructions.

[0081] These computer-readable program instructions may be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine such that, when the instructions are executed by the processor of the computer or other programmable data processing apparatus, means are generated for implementing the functions / acts specified in one or more blocks of the flowcharts and / or block diagrams. These computer-readable program instructions may also be stored on a computer-readable storage medium capable of directing a computer, programmable data processing apparatus, or other device, or combination thereof, to function in a particular manner, such that the computer-readable storage medium on which the instructions are stored constitutes an article of manufacture including instructions that implement aspects of the functions / acts specified in one or more blocks of the flowcharts and / or block diagrams.

[0082] Furthermore, computer-readable program instructions may be loaded into a computer, other programmable data processing apparatus, or other device to cause the computer, other programmable apparatus, or other device to perform a series of operational steps, thereby generating a computer-implemented process that, when executed on the computer, other programmable apparatus, or other device, implements the functions / acts specified in one or more blocks of the flowcharts and / or block diagrams.

[0083] The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in the flowcharts or block diagrams may represent a module, segment, or portion of instructions that includes one or more executable instructions for implementing the specified logical function(s). In some alternative implementations, the functions described in the blocks may occur out of the order shown. For example, depending on the functionality involved, two blocks shown in succession may in fact be executed substantially simultaneously, or the blocks may possibly be executed in the reverse order. It will also be noted that each block in the block diagrams and / or flowchart illustrations, and combinations of blocks in the block diagrams and / or flowchart illustrations, may be implemented by a dedicated hardware-based system that performs the specified functions or acts or executes a combination of dedicated hardware and computer instructions.

[0084] The description of various embodiments of the present invention is presented for illustrative purposes, but is not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terms used in this specification are selected to best explain the principles of the embodiments, practical applications, or technical improvements to the technology found in the market, or to enable those skilled in the art to understand the embodiments disclosed in this specification.

Claims

1. Observing table statistics in a metadata catalog of a source database system; estimating a statistics generation cost utilizing a target database system and estimating a source statistics generation cost utilizing a source database system; comparing the statistics generation cost utilizing the target database system with the source statistics generation cost utilizing the source database system; triggering, by the source database system, a statistics generation query by the target database system in response to the statistics generation cost utilizing the target database system having a predefined relationship with the source statistics generation cost utilizing the source database system; executing the statistics generating query by the target database system in response to the trigger by the source database system; in response to the trigger and the execution of the statistics generating query by the source database system, transmitting the generated statistics from the target database system to the source database system; updating the table statistics in the metadata catalog based on the generated statistics; using the updated table statistics to optimize a query plan; A method comprising:

2. repeating said operations of observing, estimating, and comparing; and executing, by the source database system, a statistics generation query in response to the statistics generation cost utilizing the target database system being greater than or equal to the source statistics generation cost utilizing the source database system for the repeated steps; The method of claim 1 further comprising:

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

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

5. 2. The method of claim 1, further comprising: performing a check to determine whether statistics are missing from the metadata catalog, and in response to determining that statistics are missing, the operations of estimating, comparing, triggering, executing the statistics generating query, sending, and updating are performed.

6. 2. The method of claim 1, further comprising: performing a check to determine whether there is a major data change in a table of the source database system, and in response to determining a major data change, the operations of estimating, comparing, triggering, executing the statistics generating query, sending, and updating are performed.

7. 2. The method of claim 1, wherein the predefined relationship is that the cost of generating statistics utilizing the target database system is less than the cost of generating source statistics utilizing the source database system.

8. A non-transitory computer readable medium comprising computer executable instructions that, when executed by a computer, cause the computer to perform a method, the method comprising: Observing table statistics in a metadata catalog of a source database system; estimating a statistics generation cost utilizing a target database system and estimating a source statistics generation cost utilizing a source database system; comparing the statistics generation cost utilizing the target database system with the source statistics generation cost utilizing the source database system; triggering, by the source database system, a statistics generation query by the target database system in response to the statistics generation cost utilizing the target database system having a predefined relationship with the source statistics generation cost utilizing the source database system; executing the statistics generating query by the target database system in response to the trigger by the source database system; in response to the trigger and the execution of the statistics generating query by the source database system, transmitting the generated statistics from the target database system to the source database system; updating the table statistics in the metadata catalog based on the generated statistics; using the updated table statistics to optimize a query plan; 16. A non-transitory computer readable medium comprising:

9. The method comprises: repeating said operations of observing, estimating, and comparing; and executing, by the source database system, a statistics generation query in response to the statistics generation cost utilizing the target database system being greater than or equal to the source statistics generation cost utilizing the source database system; 10. The non-transitory computer readable medium of claim 8, further comprising:

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

11. 10. The non-transitory computer-readable medium of claim 8, wherein the method further includes performing a check to determine whether statistics are missing from the metadata catalog, and wherein the operations of estimating, comparing, triggering, executing the statistics generating query, sending, and updating are performed in response to determining that statistics are missing.

12. 9. The non-transitory computer-readable medium of claim 8, wherein the method further includes performing a check to determine whether there is a major data change in a table of the source database system, and in response to determining a major data change, the operations of estimating, comparing, triggering, executing the statistics generating query, sending, and updating are performed.

13. A computer program causing a computer to carry out the method according to any one of claims 1 to 7.

14. a source database system comprising at least a first processor coupled to a first memory; a target database system comprising at least one second processor coupled to a second memory; said system being operable to perform an operation comprising: observing table statistics in a metadata catalog of the source database system; estimating a statistics generation cost utilizing a target database system and estimating a source statistics generation cost utilizing a source database system; comparing the statistics generation cost utilizing the target database system with the source statistics generation cost utilizing the source database system; triggering, by the source database system, a statistics generation query by the target database system in response to the statistics generation cost utilizing the target database system having a predefined relationship with the source statistics generation cost utilizing the source database system; executing the statistics generating query by the target database system in response to the trigger by the source database system; in response to the trigger and the execution of the statistics generating query by the source database system, transmitting the generated statistics from the target database system to the source database system; updating the table statistics in the metadata catalog based on the generated statistics; using the updated table statistics to optimize a query plan; Including, the system.

15. The operation includes: repeating said operations of observing, estimating, and comparing; and executing, by the source database system, a statistics generation query in response to the statistics generation cost utilizing the target database system being greater than or equal to the source statistics generation cost utilizing the source database system; The system of claim 14 further comprising:

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

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

18. 15. The system of claim 14, wherein the operations further include performing a check to determine whether statistics are missing from the metadata catalog, and in response to determining that statistics are missing, the operations of estimating, comparing, triggering, executing the statistics generating query, sending, and updating are performed.

19. 15. The system of claim 14, wherein the operations further include performing a check to determine whether there is a major data change in a table of the source database system, and in response to determining a major data change, the operations of estimating, comparing, triggering, executing the statistics generating query, sending, and updating are performed.

20. The system of claim 14, wherein the target database system is optimized for statistical-like queries where the cost of generating statistics using the target database system is less than the cost of generating source statistics using the source database system.

Citation Information

Patent Citations

  • Database Query Optimization Utilizing Remote Statistics Collection

    US20080256025A1

  • Offloaded, incremental database statistics collection and optimization

    US20150149405A1

  • Multi-system query execution plan

    US20160292167A1

  • Query Optimization in Hybrid DBMS

    US20180075085A1

  • Automatic Query Offloading to a Standby Database

    US20190392067A1