Abstract: A database query optimizer includes an access plan storage mechanism that stores execution detail corresponding to a query statement in an access plan cache. By storing execution detail in the access plan cache along with the query statement, the execution detail is available when needed to debug database problems, even after a problem occurs. The execution detail stored in the access plan cache may be mined and analyzed after-the-fact, producing a very powerful tool for debugging database problems. Snapshots of the execution detail may also be created by a user. In addition, the user may setup autonomic snapshots and comparisons so that a user may be alerted from a degradation in database performance between two snapshots.

Abstract: A database query optimizer includes an access plan storage mechanism that stores execution detail corresponding to a query statement in an access plan cache. By storing execution detail in the access plan cache along with the query statement, the execution detail is available when needed to debug database problems, even after a problem occurs. The execution detail stored in the access plan cache may be mined and analyzed after-the-fact, producing a very powerful tool for debugging database problems. Snapshots of the execution detail may also be created by a user. In addition, the user may setup autonomic snapshots and comparisons so that a user may be alerted from a degradation in database performance between two snapshots.

Abstract: Optimizers must work with numeric data from a variety of sources including column statistics, estimated filter factors, record counts estimated costs and the like. Embodiments provided herein define and represent any such numeric measurements as a Vector of N dimensions, where n is a number of aspects of measurements that a particular optimizer is configured to consider. A particular embodiment provides a 4-dimensional vector where the dimensions represent magnitude, confidence, variance interval, and penalty. Examples of measurements considered by the optimizer, and which may be represented as a vector, include cost, estimated selectivity of a predicate, estimated number of records returned from part or all of a query, estimated record fanout when joining one file to another, etc.

Abstract: A metadata manager is used in a database management system to collect and maintain metadata associated with a database. Multiple optimizer instances are permitted to access the metadata maintained by the metadata manager, often eliminating the need for individual optimizer instances to retrieve and process metadata directly from the database. As such, the overhead associated with operations such as collecting, refining, retrieving and/or maintaining of metadata can be off-loaded from the optimizer instances, often accelerating individual cost estimation calculations by optimizer instances, facilitating reuse of metadata calculations and refinements, and improving metadata consistency between multiple related cost estimates.

Abstract: A metadata manager is used in a database management system to collect and maintain metadata associated with a database. Multiple optimizer instances are permitted to access the metadata maintained by the metadata manager, often eliminating the need for individual optimizer instances to retrieve and process metadata directly from the database. As such, the overhead associated with operations such as collecting, refining, retrieving and/or maintaining of metadata can be off-loaded from the optimizer instances, often accelerating individual cost estimation calculations by optimizer instances, facilitating reuse of metadata calculations and refinements, and improving metadata consistency between multiple related cost estimates.

Abstract: Optimizers must work with numeric data from a variety of sources including column statistics, estimated filter factors, record counts estimated costs and the like. Embodiments provided herein define and represent any such numeric measurements as a Vector of N dimensions, where n is a number of aspects of measurements that a particular optimizer is configured to consider. A particular embodiment provides a 4-dimensional vector where the dimensions represent magnitude, confidence, variance interval, and penalty. Examples of measurements considered by the optimizer, and which may be represented as a vector, include cost, estimated selectivity of a predicate, estimated number of records returned from part or all of a query, estimated record fanout when joining one file to another, etc.