Teradata US, Inc.

Frank Roderic Vandervort

Louis Martin Burger

Douglas Paul Brown

Thomas Patrick Julien

Stephen Andrew Brobst

Sanjay Nair

Hoa Thu Tran

Jeffrey S Shelton

Arthur Vargas Lopes

Matthew Edward Antoun

Emiran Curtmola

Chrisopher James Antoun

Amaranatha Reddy Gangarapu

A computer system executes a database management system (DBMS). The DBMS manages a database comprised of DBMS resources. The DBMS receives a request to be executed. The request is a DBMS action to be executed using the DBMS resources. The request includes a predicate specifying a maximum cost for executing the request, and a deadline, specifying a deadline by which the request is to be completed in its execution. The DBMS determines a plurality of workloads under which the request is qualified to execute. Each workload of the plurality of workloads includes a respective set of requests that have common characteristics. Each workload of the plurality of workloads includes a respective cost criterion and a respective elapsed time criterion. The DBMS selects a selected workload from among the plurality of workloads. The selected workload has a selected cost criterion and a selected elapsed time criterion. The DBMS begins execution of the request using the selected workload.

Managing cloud pricing and what-if analysis to meet service level goals

A database system receives a query. The database system retrieves an old query execution plan (QEP), OldPlan, for the query. The database system submits the query to an optimizer. The optimizer returns a new QEP, NewPlan, for the query. The database system submits the OldPlan and the NewPlan to a machine learning classifier (ML classifier). The ML classifier predicts that executing the NewPlan will result in a performance regression as compared to executing the OldPlan. The database system executes the OldPlan instead of the NewPlan.

Reducing query optimizer plan regressions with machine learning classification

An apparatus, method and computer program product for estimating as-a-Service (aaS) query prices in a relational database management system (RDBMS). An optimizer of the RDBMS inserts an EXPLAIN modifier into a query, wherein the EXPLAIN modifier results in the optimizer generating a summary of a query execution plan for the query that includes one or more cost estimates for the RDBMS to perform the query. A price estimate for the query is then generated based on the cost estimates, wherein the price estimate is generated using one or more configurable pricing formulae. The price estimate is merged into the summary of the query execution plan for the query. Moreover, a price guarantee may be generated for the price estimate, wherein the price guarantee is honored when the query is subsequently invoked for execution by the RDBMS.

Estimating as-a-service query prices within optimizer explained plans

An apparatus, method and computer program product for physical database design and tuning in relational database management systems. A relational database management system executes in a computer system, wherein the relational database management system manages a relational database comprised of one or more tables storing data. A Deep Reinforcement Learning based feedback loop process also executes in the computer system for recommending one or more tuning actions for the physical database design and tuning of the relational database management system, wherein the Deep Reinforcement Learning based feedback loop process uses a neural network framework to select the tuning actions based on one or more query workloads performed by the relational database management system.

Physical database design and tuning with deep reinforcement learning

A database system receives a query to be processed. The database system has resources. A user assigns the query to a tier of resource allocation priorities in a hierarchy of tiers. The tier has been designated as being automatically managed by the database system. The tier has a plurality of levels of priority for resource allocation (LPRAs). The database system decomposes the query into a first step and a set of subsequent steps. The first step has a beginning and each of the set of subsequent steps has a respective beginning. The database system assigns the first step to a first LPRA, wherein executing the query at the first LPRA is projected by the database system to satisfy a service level goal (SLG) within a on_schedule_range of the SLG. The database system determines during execution of the set of subsequent steps that the query is no longer projected to satisfy the SLG within the on_schedule_range of the SLG and, as a result, assigns one of the set of subsequent steps to a second LPRA different from the first LPRA, wherein executing the query at the second LPRA is projected by the database system to return execution of the query to within the on_schedule_range of the SLG.

Automatic resource allocation design for satisfying service level goals of mixed workload queries in a database system

Excess capacity (or excess resources) can be used in a capacity controlled environment to effectively manage data storage of a database. In this context, excess capacity can, for example, be controlled used as needed, as configured and/or on a temporary basis. Management of data storage in a database can, for example, include operations associated with moving or relocating data of the database. Relocation of data can, for example, be performed to relocate data deemed to be 'hot' data to a location that can provide a faster access time in order to improve the overall efficiency of the database system.

Data storage management by using excess capacity in a capacity controlled environment

Excess capacity of a database system can be used for learning activities in a controlled computing environment. In this context, excess capacity of a database system can be used as needed and/or on a temporary basis. Furthermore, learning activities can be performed without requiring the use of the capacity that is configured or has been allotted for various other database operations, including those deemed, especially by the users of database system, to serve a main function or a more important purpose. As a result, learning can be performed without adversely affecting other operations deemed to be more critical, especially by the users of databases. Learning activities associated with a database environment can, for example, include learning operations directed to optimization of database queries, for example, by using a basic feedback or an expanded or active learning.

Learning by using excess capacity in a capacity controlled environment

Excess capacity available to a database system in a capacity controlled environment can be effectively managed. In particular, excess capacity that is not made available for normal operations of a database system can be used to manage errors, especially situations that may hinder expected performance of the database system. In addition, excess capacity can be used to optimize or further optimize database queries, especially those that meet a criterion (e.g., not fully optimize, not optimized as expected).

Managing excess capacity of database systems in a capacity controlled computing environment

A system, method, and computer-readable medium for grouping of database object access counts per processing module in a parallel processing system. Each processing module manages an intermediate cache allocated thereto that stores database object access counts local to the respective processing module. The processing modules are configured in a hierarchical configuration. The intermediate caches managed by the processing modules at the lowest level of the configuration are forwarded to a parent processing module and merged with the intermediate cache allocated to the parent processing module. A root processing module merges the intermediate caches received from one or more parent processing modules with the root processing module's intermediate cache to generate a final cache. An objects access count data structure is then generated by the root processing module.

System, method, and computer-readable medium for grouping database level object access counts per processing module in a parallel processing system

Excess capacity available to a database system in a capacity controlled environment can be effectively managed for maintenance and/or tuning activities. In this context, excess capacity can be used as needed and/or on a temporary basis. Furthermore, maintenance and/or tuning activities can be performed without requiring the use of the configured or allotted capacity. As a result, these operations can be performed without adversely affecting other operations which may be deemed more critical by the users of databases. System maintenance and tuning (or fine tuning) of a database environment can, for example, include operations associated with 'Physical Tuning,' and 'Index Tuning'. Some exemplary operations include collecting Statistics, analyzing a database workload, sampling, analyzing an execution plan, creating indexes.

System maintenance and tuning of databases by using excess capacity in capacity controlled environment

An improved workload management system and method for a database system. The improved workload management system evaluates certain workload management rules dynamically at key points during database query execution rather than relying on enforcement prior to query activation. In the case of product join operations, the workload management system evaluates the potential impact on system performance by the product join operation after input tables have been populated, but prior to the execution of the join operation. Delaying workload management enforcement to this point ensures that the sizes of inputs and outputs are reliably known and improves system performance impact estimates at a point early enough to prevent any serious performance degradation from an inefficient product join.

Database system workload management method and system

A method and system for eliminating unnecessary statistics collections for query optimization in a database stored on a computer. Statistics are unnecessary when a re-generated query execution plan that does not use the statistics is equivalent to an original query execution plan that uses the statistics. To determine this, an original query execution plan is created for each query in a specified workload using the statistics in the database. A search is performed of the statistics in order to enumerate one or more candidate sets of statistics to be eliminated. One or more of the candidate sets of statistics are removed from consideration prior to creating the re-generated query execution plan for each query in the specified workload. The re-generated query execution plan, which was created with one or more of the candidate sets of statistics removed from consideration, is compared with the original query execution plan, which was created using the statistics, in order to determine whether the re-generated query execution plan and the original query execution plan are equivalent. The one or more of the candidate sets of statistics removed from consideration are identified as unnecessary when the re-generated query execution plan and the original query execution plan are equivalent.

Eliminating unnecessary statistics collections for query optimization

A system and method for identifying a selection of index candidates for a relational database. The system includes a Predicate Analyzer for examining database queries in workload and recording column and predicate information; a Search Engine for performing a combinatorial search of index candidates; an Index Emulator for emulating the existence of a set of candidate indexes by making necessary 'fake' entries into the relational database management system data dictionary; and a Query Optimizer for estimating the cost of a given query within a given index configuration. In accordance with the method, data indicative of a workload comprising one or more queries is received, and predicates are then analysed and organized into column groups for each basic predicate type. Statistics are collected on each of these groups. Non-predicate portions of each query are scanned to obtain additional covering columns. A combinational search is performed on the column groups to identify covering and non-covering candidates. These are then used in conjunction with collected statistics and baseline costs are calculated to perform a cost bases analysis of the candidates and generate index candidate recommendations.

System and a method for identifying a selection of index candidates for a database

A system and method for selectively recompiling stored execution plans within a database system. In one technique the execution plans include respective version numbers of database software operable within the database system during compilation of the respective plans. A request is received to execute one of the stored execution plans, and the stored execution plan is retrieved from computer memory. A version number is obtained of database software operable within the database system during retrieval of the stored execution plan. The obtained version number is compared with the version number associated with the retrieved execution plan. On detecting a mismatch between the obtained version number and the retrieved version number, the stored execution plan is recompiled. In another technique the stored execution plans include respective cost parameters representing the hardware capabilities of the system at the time of plan compilation. A set of cost parameters representing the cost of executing the stored execution plan within the database system is compared with the stored cost parameters associated with the retrieved execution plan. On detecting a significant difference between the defined cost parameters and the retrieved cost parameters, the stored execution plan is recompiled.

Selective automatic refreshing of stored execution plans

Techniques for partitioning indexes are provided. A predefined data type associated with a column of a database table is acquired. Column value statistics for the column are acquired. The values are analyzed to produce multiple partitions for the values. Each partition includes each of the values and each partition includes multiple groupings of the values. Also, each grouping of a particular partition includes a subset or range of the values. Furthermore, each grouping of a partition includes a same distribution for the values as other groupings of that partition. Each partition represents a potential partitioning index for the column and its values.

Growing community of inventors

Ramona, CA, United States of America

Frank Roderic Vandervort