January 2009

So ... not really 30 days (21 to be precise) but it feels like forever.Late November, early December (while I was in the U.K.) several sites were installed with an updated version of one of the tools. Everything seemed to be going well, until all the databases ran out of space roughly around the same time. (We lost a team member a few months ago and he used to monitor sites until they achieved

There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;
Table created.
SQL> create table t2 as select rownum+10 b from dual connect by level < 10;
Table created.
SQL>
SQL> select * from t1;

Lsof (list open files) is a really useful tool for troubleshooting open file decriptors which prevent a deleted file from being released or a shared memory segment from being removed.
Here’s a little situation on Linux where an Oracle shared memory segment was not released as someone was still using it.
$ ipcs -ma ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 393216 oracle 640 289406976 1 dest 0xbfb94e30 425985 oracle 640 289406976 18 0x3cf13430 557058 oracle 660 423624704 22 ------ Semaphore Arrays -------- key semid owner perms nsems 0xe2260ff0 1409024 oracle 640 154 0x9df96b74 1671169 oracle 660 154 ------ Message Queues -------- key msqid owner perms used-bytes messages The bold line should have disappeared after instance shutdown, but it didn’t.

Being inherently lazy, I am always a sucker for shortcuts, neat tricks to cut my work and, most important, not to do the same thing again and again. Here is a tip I find useful.

Have you ever been frustrated to find that some line has changed some important shell variable such as ORACLE_BASE inside a shell script? The list of variables that are important to safety and efficiency of your shell is a long one - PS1, ORACLE_BASE, PATH, and so on. Using this little known command, you can easily "protect" a variable. The trick is to make it readonly. First, set the variable:

# export ORACLE_BASE=/opt/oracle

Then make it readonly:

# readonly ORACLE_BASE

Now if you want to set it:

# export ORACLE_BASE=/opt/oracle1
-bash: ORACLE_BASE: readonly variable

You can't. You can't even unset the variable:

# unset ORACLE_BASE
-bash: unset: ORACLE_BASE: cannot unset: readonly variable

This is a cool way to protect important variables.

To get a list of variables that are readonly, use

# declare -r
declare -ar BASH_VERSINFO='([0]="3" [1]="00" [2]="15" [3]="1" [4]="release" [5]="i386-redhat-linux-gnu")'
declare -ir EUID="500"
declare -rx ORACLE_BASE="/opt/oracle"
declare -ir PPID="13204"
declare -r SHELLOPTS="braceexpand:emacs:hashall:histexpand:history:interactive-comments:monitor"