Abstract: 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.

Abstract: Capacity of a database system and/or a computing system that includes a database can be effectively changed from a current computing capacity to another computing capacity. This can be achieved by causing usage capacity of at least one of resource to be changed when the database is active. By way of example, capacity of a database system can be regulated by delaying the execution of the database work based on a target capacity. As a result, database work can take relatively longer to complete when the capacity of a database is effectively regulated to be below its full capacity. In effect, a portion of available capacity (excess capacity) can be made inaccessible to the database. However, excess capacity can be used to manage various services of the database in accordance with one or more service criteria (e.g., Service Level Goals or Agreements).

Abstract: A system, method, and computer-readable medium for automatic and dynamic control of optimizer settings in a large complex system environment are provided. The described mechanisms significantly increase the potential of achieving Service Level Goals for classes of queries where optimizer aggression is needed. Confidence threshold parameters are automatically controlled to facilitate generation of query execution plans to conform with associated Service Level Goals.

Abstract: A system, method, and computer-readable medium that facilitate the optimization of frequently executed queries via automated index creation are provided. Standard optimizer strategies for dynamic index creation are utilized with query frequency information taken from a system's request cache. The described mechanisms provide a process of determining what indexes would be beneficial to query performance based on decisions and information from the optimizer that is ultimately responsible for the choice of index usage. Further, the plan cache is utilized in a manner that provides reliable information about the historical and anticipated frequency of specific queries.

Abstract: A system, method, and computer-readable medium for automatic and dynamic control of optimizer settings in a large complex system environment are provided. The described mechanisms significantly increase the potential of achieving Service Level Goals for classes of queries where optimizer aggression is needed. Confidence threshold parameters are automatically controlled to facilitate generation of query execution plans to conform with associated Service Level Goals.

Abstract: A system, method, and computer-readable medium that provide automatic and dynamic control of optimizer settings in a large complex system environment are provided. The optimizer may pursue more aggressive query execution plans and switch to a more conservative approach in cases of actual observed inaccuracies.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: In an example embodiment, a method is illustrated as including defining a rule for executing a structured query language (SQL) statement, the rule including a resource threshold. A violation of the rule may be detected during an execution of the SQL statement. Further, SQL execution data is logged in a log table as a result of the rule being violated, the SQL execution data including a statistics collection recommendation. An estimated cost of gathering statistics indicated by the statistics collection recommendation is compared to an estimated resource savings and a data dictionary storing statistics on data objects associated with the SQL statement is updated as a function of the comparing.

Abstract: A computer-implemented method, apparatus and article of manufacture for optimizing a database query. A query execution plan for the database query is generated using estimated cost information; one or more steps of the query execution plan are executed to retrieve data from a database stored on the computer system. Actual cost information is generated for each of the executed steps, and the estimated cost information is re-calculated using the actual cost information. One or more resource allocation rules defined on one or more steps of the query execution plan are executed, based on the estimated cost information, wherein the resource allocation rules include one or more defined actions. The estimated cost information may be re-calculated using the actual cost information when confidence in the estimated cost information is low, but the estimated cost information may not be re-calculated when confidence in the estimated cost information is high.

Abstract: The subject mater herein relates to database management and, more particularly, to selective database statistics recollection. Various embodiments provide one or more of system, methods, and software that identify database column and index states when statistics become stale to a point where query execution plans against respective columns and indexes fail to be efficient. These and other embodiments provide a way to update column and index statistics only when the statistics are determined to be stale. As a result, adverse database performance as a result of needless updating of database statistics is mitigated.

Abstract: An improved system and method for storing, reusing and recompiling execution plans for SQL database queries. The system. The system validates the performance of a newly compiled plan prior to replacing a currently stored execution plan and compares the resource cost of the recompiled plan with the cost of the currently stored plan. In the event that the resource cost of the recompiled plan is not greater than the stored plan resource cost, the system replaces the currently stored execution plan with the recompiled plan.

Abstract: A system, method, and computer readable medium that automate the selection of sampling for statistics collection in a database system are provided. Various resource usage and savings evaluations may be made to determine if a column or index is a candidate for sampling during statistics recollections. If the column is successfully evaluated as a quality candidate for sampling using resource usage and savings evaluations, one or more statistics accuracy evaluations may be made to determine if inaccuracies introduced in the statistics by sampling are tolerable. If the column is successfully evaluated as a quality candidate for sampling using the statistics accuracy evaluations, the column may be designated for sampling during statistics recollections on the column. A column or index is thereby identified or eliminated for sampling and designated as such in an automated manner without manual designation or specification by a database management administrator.

Abstract: 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.

Abstract: The subject mater herein relates to database management and, more particularly, to selective database statistics recollection. Various embodiments provide one or more of system, methods, and software that identify database column and index states when statistics become stale to a point where query execution plans against respective columns and indexes fail to be efficient. These and other embodiments provide a way to update column and index statistics only when the statistics are determined to be stale. As a result, adverse database performance as a result of needless updating of database statistics is mitigated.

Abstract: An improved system and method for storing, reusing and recompiling execution plans for SQL database queries. The system. The system validates the performance of a newly compiled plan prior to replacing a currently stored execution plan and compares the resource cost of the recompiled plan with the cost of the currently stored plan. In the event that the resource cost of the recompiled plan is not greater than the stored plan resource cost, the system replaces the currently stored execution plan with the recompiled plan.

Abstract: 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.

Abstract: 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.

Abstract: A system and method of evaluating a materialized view relating to a base table for a database system. The method includes the steps of defining a simulated materialized view for the base table within a user session and storing the definition for the simulated materialized view in computer memory such that the definition is available only within the user session. The method further includes the step of maintaining the definition(s) for one or more active materialized views relating to the base table in computer memory such that the definition(s) is/are available outside the user session. An optimizer is invoked to evaluate the simulated materialized view within the user session as if the materialized view was available outside the user session. As an additional step, a simulated materialized view is then selected on detecting a potential improvement of performance.