“ASH math” of time_waited explained with pictures and simulation

As explained by John Beresniewicz, Graham Wood and Uri Shaft in their excellent overview ASH architecture and advanced usage, avg( v$active_session_history.time_waited ) is not a correct estimate of the average latency (the “true average”) esperienced by a wait event, the reason being that short events are less likely to be sampled. In order to correct this, the authors propose a formula that gives an unbiased estimate of the “true average”.…

Fast refresh of aggregate-only materialized views with MAX – algorithm

In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the MAX aggregate function:

[sql light=”true”]
create materialized view test_mv
build immediate
refresh fast on demand
with rowid
as
select gby as mv_gby,
count(*) as mv_cnt_star,
max (dat) as mv_max_dat
from test_master
–where whe = 0
group by gby
;
[/sql]

The where clause is commented to enable fast refresh whatever type of DML occurs on the master table, in order to investigate all possible scenarios; the case having the where-clause is anywhere a sub-case of the former and we will illustrate it as well below.…

Fast refresh of aggregate-only materialized views with SUM – algorithm

In this post I will illustrate the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only the SUM aggregate function:

[sql light=”true”]
create materialized view test_mv
build immediate
refresh fast on demand
with rowid
as
select gby as mv_gby,
count(*) as mv_cnt_star,
sum (dat) as mv_sum_dat,
count(dat) as mv_cnt_dat
from test_master
where whe = 0
group by gby
;
[/sql]

Note that count(dat) is specified – you could avoid that if column dat is constrained to be not-null (as stated in the documentation), but I’m not covering that corner case here.…

Fast refresh of aggregate-only materialized views – introduction

This post introduces a series about the algorithm used by Oracle (in 11.2.0.3) to fast refresh a materialized view (MV) containing only an aggregate:

[sql light=”true”]
create materialized view test_mv
build immediate
refresh fast on demand
with rowid
as
select gby as mv_gby,
count(*) as mv_cnt_star,
AGG (dat) as mv_AGG_dat,
count(dat) as mv_cnt_dat
from test_master
where whe = 0
group by gby
;
[/sql]

Where AGG is either SUM or MAX, the most important aggregates.…

“alter session force parallel query”, and indexes

This post is a brief discussion about the advantages of activating parallelism by altering the session environment instead of using the alternative ways (hints, DDL). The latter ways are the most popular in my experience, but I have noticed that their popularity is actually due, quite frequently, more to imperfect understanding rather than informed decision – and that’s a pity since “alter session force parallel query” can really save everyone a lot of tedious work and improve maintainability a great deal.…

fast refresh of outer-join-only materialized views – algorithm, part 2

In this post, we are going to complete part 1 illustrating the (considerably more complex) general case of a fast refresh from a master inner table without a unique constraint on the joined column(s).

To recap, now the outer slice can be composed of more than one row, for example:

ooo inn1
ooo inn2

and hence, both the DEL and INS step must consider (and read) the whole outer slice even if only a subset of the inner rows have been modified.…

fast refresh of outer-join-only materialized views – algorithm, part 1

In this series of posts we will discuss how Oracle refreshes materialized views (MV) containing only OUTER joins, covering only 11.2.0.3. We will use the very same scenario (MV log configuration, DML type, etc) as in the inner join case, “just” turning the inner join into an outer join:

[sql light=”true”]
create materialized view test_mv
build immediate
refresh fast on demand
as
select test_outer.*,…

OLTP compression: migrated rows are compressed

In his articles Compression in Oracle – Part 2: Read-Only Data and Compression in Oracle – Part 3: OLTP Compression, Jonathan Lewis has shown that block (re)compression is never attempted on updates – it is attempted only on inserts (and, of course, only if the used space crosses the PCTFREE threshold).
Now, since a row migration could be considered a row re-insertion – does it trigger a compression attempt of the block that the row is migrated into?…

overlapping ranges with priority

A customer of ours (a leading Italian consumer goods retailer) has asked us to solve the following problem, that occurs quite frequently and that is not trivial to solve efficiently – and that is very interesting to design and fun to blog about!
the problem
Prices of skus (i.e. goods) have validity ranges (time intervals) and can overlap; on an overlapping range, the strongest priority (lower number) wins.…

refresh “fast” of materialized views optimized by Oracle as “complete”

In my current “big” project, I am building a network of nested materialized views to transform rows of one schema into rows of another (very different) schema. The former is used by the old (but still live) version of an application of ours, the latter by the new version; our idea is to incrementally (aka “fast”) refresh the network daily in order to have the new schema ready when the new version goes live.…

Bitnami