Oracle International Corporation

Gregory Scott Smith

Todd Guay

Karl Dias

Mohamed Ziauddin

Alex Tsukerman

Ari Wolfe Mozes

Leng Leng Tan

Shilpa Lawande

Jay Rossiter

Geoffrey Wilson

Gaylen D Royal

Sangam Pant

An access structure analysis method is interspersed with the query optimization process. The method can determine the ideal combination of access structures, including both materialized views and indexes, for a given database workload. The interspersed structure analysis method can include advanced transformations like view merging, star transformation, bitmap access plans, query rewrite using materialized views, for example. The method may be performed using the query optimizer's rules as heuristics to guide the index candidate generation process.

Method for index tuning of a SQL statement, and index merging for a multi-statement SQL workload, using a cost-based relational query optimizer

Various embodiments of the invention provide solutions that can offer a consistent framework for tools that assist in the configuration, tuning, and/or troubleshooting of a database and/or an RDBMS. Merely by way of example, one set of embodiments provides a software framework for an advisor component of a database and/or RDBMS. In accordance with some embodiments, the framework might specify a common data model for such advisor components. The data model can include, merely by way of example, a set of one or more findings (which might, in some cases, describe the result of an analysis of a circumstance in the database, RDBMS, and/or a mid-tier application used with the database) and/or a set of one or more recommendations (which might provide suggestions for addressing the circumstance). In particular embodiments. In particular embodiments, the data model might include a set of on or more rationales, which can explain the recommendations. In some cases, the software framework might include a task associated with the advisor component and/or one or more interfaces, which can include, inter alia, user interfaces and/or APIs. Other sets of embodiments can provide methods, systems and/or software programs for implementing an advisor framework.

Advisor framework, systems, methods and software for implementing the same

A method evaluates a plurality of candidate index sets for a workload of database statements in a database system by first generating baseline statistics for each statement in the workload. An index superset is formed by combining an existing or current index set and a proposed index set. A candidate index set is derived from the index superset, the candidate index being one of the plurality of candidate index sets. Statistics for a statement are generated by first creating an execution plan which represents an efficient series of steps for executing the statement given the candidate index set. The execution plan is evaluated, and statistics based on the evaluation of the execution plan are generated and recorded. The cost of the execution plan is then determined and statistics are generated. Statistics for each candidate index set are rolled up and presented to a user or an index tuning mechanism.

Database index validation mechanism

A workload reduction mechanism for index tuning is described that selects relevant database statements from a database workload, standardizes a format of the relevant database statements and reduces the formatted, relevant database statements into a representative set of database statements. These statements may then be provided to an index tuning mechanism.

Workload reduction mechanism for index tuning

Design of indexes in a relational database management system is based on a workload analysis of all requests in a system. Each request is assigned a value of importance, and requests are broken into expressions, contexts and columns to facilitate identification of candidate indexes. Candidate indexes are compared to existing indexes to determine whether the existing indexes can be reused or modified. Candidate indexes are classified as hashed or sorted. Based on the index design, record placement is selected to be hashed, sorted or by default. Related indexes and tables are clustered and the clusters are sized to fit file areas. Cache buffers are also defined.

Growing community of inventors

Merrimack, NH, United States of America

Gregory Scott Smith