Currently browsing author

Alberto Dell'Era, Page 3

11gR2: materialized view logs changes

In this post we are going to discuss some 11gR2 changes to materialized view logs that are aimed at increasing the performance of the fast-refresh engine of materialized views (MVs), especially the on-commit variant.

The MV logs, in 10gr2, now comes in two flavours: the traditional (and still the default) timestamp-based one and the brand new commit SCN-based one; you choose the latter type by specifing the “WITH COMMIT SCN” clause at MV log creation time.…

CBO: NewDensity for Frequency Histograms,11g- (densities part IV)

As we have seen in the previous posts of this series, in 11g a new figure named “NewDensity” has been introduced as a replacement for the “density” column statistic for columns whose histogram has been collected; this change has been backported in also.

In the previous post we discussed how NewDensity influences the CBO cardinality estimate for Height-Balanced histograms; in this one we are going to investigate the same for Frequency Histograms.…

CBO: “NewDensity” replaces “density” in 11g, (densities part III)

In this post we are going to explore and explain the rationale for the formula used by the CBO to compute the “NewDensity” figure that replaces, from onwards, the “density” column statistic in the cardinality estimation formulae for columns with height-balanced (HB) histograms defined.

In a previous post, we already discussed the pre- scenario: we saw how and when the “density” column statistic is used in the cardinality formula for equality filter predicates, we explained its statistical rationale and defining formula, introduced the concept of the NPS (Not Popular Subtable), and built a test case.…

CBO: the formula for the “density” column statistic (densities part II)

In this post we are going to explore and explain the rationale for the formula used by dbms_stats to compute the “density” column statistic, used by the CBO in versions less than to estimate the cardinality of a class of SQL statements. In the next post, we will speak about its replacement, named “NewDensity” in 10053 trace files.

We will consider only the non-trivial case of Height-Balanced histograms, since for Frequency Histograms density is a constant (0.5 / num_rows) and for columns without histogram, it is simply 1/num_distinct.…

CBO: about the statistical definition of “cardinality” (densities part I)

Let’s explore the concept of cardinality from the point of view of the statistician; this is both to get a clearer vision of the matter (i.e. for fun) and to path the way for understanding the rationale for the “density” statistics as calculated by dbms_stats (the topic of an upcoming post).

Let’s consider a statement with input parameters (bind variables), and consider the most fundamental of them all, the one with a filter predicate:
select …
from t
where x = :x;
the cardinality “card” of the set of rows retrieved depends on the table possible values and the actual inputs provided by the client as bind variable values.…

An interview with Mark Townsend

While attending the 11gR2 launch event in Milan last Thursday, I had the distinguished opportunity (invited, as a blogger, by the Oracle team that was organizing the event) to meet Mark Townsend and exchange a few words about the new features of 11gR2 and the Oracle database in general as well.

For those who don’t know, Mark is (among other things) the Vice President in charge of coordinating the Product Managers and a technical expert at the same time, and this rare combination has the advantage that you can ask him about any feature you like at whatever granularity you like, from the strategic level down to the technical gory details.…

CBO: the “non-empty result set” assumption

The CBO assumes that SELECT statements are always going to retrieve at least one row – even if this is not necessarily the case, of course. Understanding why this is done is both useful and fascinating.

We must start from the very beginning and remember that one of the most important tasks of the CBO is estimating the statement cardinality, that is, to make a guess about the number of rows that will be fetched.…

NoCOUG’s “First international SQL challenge”

Just a short note to tell my friends that I have been bestowed the August Order of the Wooden Pretzel, that is, that I won the NoCOUG’s “First international SQL challenge” with this solution.

I’m especially happy to see that, after (way too) many years since graduation, I am still able to use my math skills to solve problems … :).…

fast refresh of single-table materialized views – algorithm summary

Today we are going to investigate how Oracle fast refreshes materialized views (MVs) of a single master table, containing no aggregate but, at most, filter predicates and additional column definitions:
create materialized view test_mv
build immediate
refresh fast on demand
with rowid
— with primary key
select test_t1.*, x1+x2 as x1x2
from test_t1
where x1 != 0.42;
This kind of MVs might be considered a degenerate case of a join-only MV, a topic that we investigated in an earlier post, and one could expect the same algorithm.…

Xplan 2.0

A lot of new features have been added in version 2.0 of xplan, the sqlplus script I use to investigate about SQL statements performance (I spoke about version 1.0 in this post). Here’s a brief description.

wait profile (from ASH)

For each statement, its wait profile is calculated fetching wait information from Active Session History:
|ash event |cnt |% |
|enq: HW – contention |2606|61.0|
|enq: TX – row lock contention| 875|20.5|
|db file sequential read | 344| 8.0|
|enq: TX – index contention | 158| 3.7|
|gc current grant busy | 152| 3.6|
|cpu | 56| 1.3|
|gc current block 2-way | 34| 0.8|
|gc current block busy | 13| 0.3|
|gc buffer busy | 10| 0.2|
|gc cr block 2-way | 7| 0.2|
|gc current grant 2-way | 5| 0.1|
|read by other session | 5| 0.1|
|direct path write | 3| 0.1|
|gc cr block busy | 3| 0.1|
|gc cr grant 2-way | 1| 0.0|
|SQL*Net more data from client| 1| 0.0|
|cr request retry | 1| 0.0|

By default this feature is on in 10g+ and inspects a window of ash_profile_mins=15 minutes from v$active_session_history.…