Currently browsing

Page 4

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

Bind Variables Checker for Oracle – now install-free

I’ve finally managed to implement an install-free version of my utility to check for bind variables usage. The new script is named bvc_check.sql and when run, it examines the SQL statements stored in the library cache (through gv$sql) and dumps the ones that would be the same if the literals were replaced by bind variables.

An example of the output:
statements count : 0000000003
bound : select*from t where x=:n
example 1: select * from t where x = 2
example 2: select * from t where x = 3
So we have 3 statements that are the same once literals are replaced with bind variables.…

Tuning Oracle for Siebel – SQL template

The time has come to write down some of the most relevant discoveries I’ve made so far while being part of a team that is tuning a huge Siebel installation for a leading Italian company (“huge” especially because of the user base dimension and secondarily because of the hardware deployed, a three-node RAC on pretty powerful SMP machines).

This blog entry is about the structure of the Siebel queries and the effect of the settings of some CBO-related parameters – settings made by the Siebel clients by altering the session at connect time, or required as mandatory in the Siebel installation notes.…

Optimizing SQL statements with xplan

Xplan is a utility to simplify and automate the first part of every SQL statement tuning effort, that is, collecting the real plan of the statement, its execution statistics (number of executions, number of buffer gets performed, etc), getting the definition of all the accessed tables (and their indexes), and, last but not least, the CBO-related statistics of the accessed tables (and their indexes and columns) stored in the data dictionary by dbms_stats or ANALYZE.…

Order of keys inside index blocks

In this post we are going to illustrate how index keys are ordered inside a leaf block of an Oracle B+tree index.

It is well known that index blocks share most of their structure with “regular” (heap) table blocks; in particular, they share most of the way entries are stored inside the block. In tables, a row can be placed anywhere in the bottom part of the block (which is essentially managed like an “heap”, in which the exact memory address that the row is stored at is not important); its row address is recorded into one of the slots of a vector named “row directory”, located near the beginning of the block.…

Why blogging ?

Mainly because I like to write, and people usually like my writings. So, why not ?

Also – because it is a good way of learning. When I was a student, I remember that my favorite reharsal technique was to pretend I was explaining the topic to an imaginary person that knew nothing about it – and that technique worked very well, because when explaining, I very frequently discovered flaws in my knowledge that prompted me to improve even more.…