Currently browsing

August 2009

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.…

fast refresh of join-only materialized views – algorithm summary

This post investigates how Oracle fast refreshes materialized views containing only joins of master tables:

create materialized view test_mv
build immediate
refresh fast on demand
select test_t1.*, test_t1.rowid as test_t1_rowid,
       test_t2.*, test_t2.rowid as test_t2_rowid,
       test_t3.*, test_t3.rowid as test_t3_rowid
  from test_t1, test_t2, test_t3
 where test_t1.j1_2 = test_t2.j2_1
   and test_t2.j2_3 = test_t3.j3_2

The fast refresh algorithm is simple and very easy to understand – so trivial in fact that once examined and understood, the possible tuning techniques follow naturally.…