Oracle International Corporation

Hong Su

Mohamed Zait

Sunil Parrokkaren Chakkappen

Benoit Dageville

Thierry Cruanes

Mohamed Ziauddin

Allison Waingold Lee

Uri Shaft

Rafi Ahmed

Dinesh Das

Yali Zhu

Yuying Zhang

Jiakun Li

Cetin Ozbutun

Khaled Yagoub

Peter Clayton Belknap

Suratna Budalakoti

Krishna Kantikiran Pasupuleti

Christoforus Widodo

Techniques are provided for gathering statistics in a database system. The techniques involve gathering some statistics using an 'on-the-fly' technique, some statistics through a “high-frequency” technique, and yet other statistics using a “prediction” technique. The technique used to gather each statistic is based, at least in part, on the overhead required to gather the statistic. For example, low-overhead statistics may be gathered “on-the-fly” using the same process that is performing the operation that affects the statistic, while statistics whose gathering incurs greater overhead may be gathered in the background, while the database is live, using the high-frequency technique. The prediction technique may be used for relatively-high overhead statistics that can be predicted based on historical data and the current value of predictor statistics.

Techniques for maintaining statistics in a database system

Disclosed is an approach for applying fine-grained hints to obtain optimal control over error handling during query compilation. Using fine-grained hints causes less disruption to the query plans generated by the optimizer as they constrain specific features while retaining scope for the rest of the optimizer functionality. This approach provides a query plan of better quality that avoid errors, particularly in comparison to coarse-grained approaches that restrict processing to earlier database versions or which disables cost-based processing.

Method and system to implement error handling with fine-grained SQL hints

Techniques are described for responding to aggregate queries using optimizer statistics already available in the data dictionary of the database in which the database object targeting by the aggregate query resides, without the user creating any additional objects (e.g. materialized views) and without requiring the objects to be loaded into volatile memory in a columnar fashion. The user query is rewritten to produce a transformed query that targets the dictionary tables to form the aggregate result without scanning the user tables. 'Accuracy indicators' may be maintained to indicate whether those statistics are accurate. Only accurate statistics are used to answer queries that require accurate answers. The accuracy check can be made during runtime, allowing the query plan of the transformed query to be used regardless of the accuracy of the statistics. For queries that request approximations, inaccurate statistics may be used so long as the statistics are 'accurate enough'.

Statistics based query transformation

Techniques are provided for gathering statistics in a database system. The techniques involve gathering some statistics using an 'on-the-fly' technique, some statistics through a 'high-frequency' technique, and yet other statistics using a &#x201c;prediction&#x201d; technique. The technique used to gather each statistic is based, at least in part, on the overhead required to gather the statistic. For example, low-overhead statistics may be gathered &#x201c;on-the-fly&#x201d; using the same process that is performing the operation that affects the statistic, while statistics whose gathering incurs greater overhead may be gathered in the background, while the database is live, using the high-frequency technique. The prediction technique may be used for relatively-high overhead statistics that can be predicted based on historical data and the current value of predictor statistics.

Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

Leveraging query-specific statistics in non-equivalent queries

Techniques are provided for merging (a) statistics associated with data added to a table in a bulk load operation with (b) statistics associated with data that existed in the table before the bulk load operation. The statistics associated with the bulk load data are generated on-the-fly during the bulk load, and are merged with the pre-existing statistics as part of the same transaction that is used to perform the bulk load operation. Consequently, execution plans for queries that are assigned snapshot times after the commit time of the bulk load transaction will be selected based on the new statistics, while execution plans for queries that are assigned snapshot times before the commit time of the bulk load transaction will be selected based on the pre-existing statistics.

Online optimizer statistics maintenance during load

A table is processed to determine an approximate NDV for a plurality of groups. For each row, a group based is identified based on one or more group-by columns. A hashed valued is generated by applying a uniform hash function to a value in an NDV column. The hashed value is assigned to a particular bucket based on the values at a first set of bit positions in a binary representation of the hashed value. A bit position value is determined based on for a remaining portion of the binary representation of the hashed value. The bit position value is based on a number of ordered bits in the hashed value that match a particular bit pattern. For each group identified, a maximum bit position (MBP) table is generated. The MBP table stores, for one or more buckets, the maximum bit position value determined for hashed values assigned to a particular bucket.

Approximate distinct counting in a bounded memory

Triggering hard parses

Approaches, techniques, and mechanisms are disclosed for maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.

SQL execution plan baselines

Approaches, techniques, and mechanisms are disclosed for maintaining a history of query plans executed for a database command, along with information related to each query plan. A database server receives a request to execute a particular command. The database server determines a plan for executing the particular command. The database server adds first information to a plan history associated with the particular command. The plan history comprises information related to a plurality of plans that have been generated for the particular command. The first information may include, for example, properties of the plan (including an outline of the plan) as well as statistics collected during execution of the plan. The database server may implement techniques for periodically refreshing information in a plan history. The database server may also implement techniques for purging old or less important plans.

Maintaining and utilizing SQL execution plan histories

Under a type of query transformation referred to herein as join factorization, the branches of an UNION/UNION ALL query that join a common table are combined to reduce accesses to the common table. The transformation can be expressed as (Tjoin T) union all (Tjoin T)=Tjoin (Tunion all T), where T, Tand Tare three tables. A given query may be rewritten in many alternate ways using join factorization. Evaluating each alternative can be expensive. Therefore, the alternatives are generated and evaluated in a way that minimizes the cost of evaluating the alternatives.

Efficient search space analysis for join factorization

Join factorization of union/union all queries

A method and apparatus for approximating a database statistic, such as the number of distinct values (NDV) is provided. To approximate the NDV for a portion of a table, a synopsis of distinct values is constructed. Each value in the portion is mapped to a domain of values. The mapping function is implemented with a uniform hash function, in one embodiment. If the resultant domain value does not exist in the synopsis, the domain value is added to the synopsis. If the synopsis reaches its capacity, a portion of the domain values are discarded from the synopsis. The statistic is approximated based on the number (N) of domain values in the synopsis and the portion of the domain that is represented in the synopsis relative to the size of the domain.

Approximating a database statistic

A method and apparatus for merging synopses to determine a database statistic, e.g., a number of distinct values (NDV), is disclosed. The merging can be used to determine an initial database statistic or to perform incremental statistics maintenance. For example, each synopsis can pertain to a different partition, such that merging the synopses generates a global statistic. When performing incremental maintenance, only those synopses whose partitions have changed need to be updated. Each synopsis contains domain values that summarize the statistic. However, the synopses may initially contain domain values that are not compatible with each other. Prior to merging the synopses the domain values in each synopsis is made compatible with the domain values in the other synopses. The adjustment is made such that each synopsis represents the same range of domain values, in one embodiment. After 'compatible synopses' are formed, the synopses are merged by taking the union of the compatible synopses.

Growing community of inventors

Foster City, CA, United States of America

Hong Su