COMING SOON: JOIN. LEARN. ROCK.

A recent query I had to tune had a WHERE clause similar to this: where t.myno = 1 and t.jz1 not in(3,4,5)           and (nvl(t.amount,0) != 0)           and nvl(t.status,'!') != 'J' Notice the NVL functions.  Let’s look at the first one. nvl(t.amount,0) != 0 In...

Sooner or later you come across a poor performing statement that you just know you could fix…if only you could add a hint.  There’s one index it should use, a join order you prefer, or perhaps you are upgrading and would prefer that THIS statement not use the new...

Every so often I will look at an AWR report for a slow system, and updates to the data dictionary table SEQ$ will show up as a top resource consumer.  This is usually because sequences are defined using NOCACHE.  In this post we show why this matters and how to avoid...

Today we talk about the purpose of Oracle undo and give a demonstration of its use and little bit of what Oracle is doing under the covers.  We also show how query performance can be affected by what else is going on in the database. In the last post on transactions,...

In this post we’ll do a short discussion about Oracle transactions, and a simple demonstration of the principles discussed. The term transaction in Oracle means to take some data in the database from one consistent state to another.  The work done in a transaction by...

In this post we’ll walk through the stages of an Oracle database startup and a few important notes along the way. At the simplest level there are three main stages of an Oracle startup. NO MOUNT:  The database instance is started.  The spfile or parameter file is...