Monday, May 25, 2009

Errors while installing Oracle9i in Redhat 5.3 (

Today, I was trying to install Oracle9i on Redhat 5.3 (2.6.18 kernel), got the following error when runInstaller was involved:

$ Initializing Java Virtual Machine from /tmp/OraInstall2009-05-09_06-44-18AM/jre/bin/java. Please wait...
/tmp/OraInstall2009-05-09_06-44-18AM/jre/bin/i386/native_threads/java: error while loading shared libraries: cannot open shared object file: No such file or directory

When check for libstdc++- library, I found the following to be installed by default on RH 5.3:

[root@alps tmp]# rpm -qa |grep libstdc++-
[root@alps tmp]#

Looks like it is looking for libstdc++-lib6.1-1.

When I did the following the initial error message disappeared:

# cd /usr/lib
# ln -s

Then I got the following error:

$ ./runInstaller
$ Initializing Java Virtual Machine from /tmp/OraInstall2009-05-09_06-50-11AM/jre/bin/java. Please wait...
Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2009-05-09_06-50-11AM/jre/lib/i386/ symbol __libc_wait, version GLIBC_2.0 not defined in file with link time reference

When applied the oracle patch: 3006854 - it fixed it:

[root@alps 3006854]# sh
Applying patch...
Ensuring permissions are correctly set...
Patch successfully applied
[root@alps 3006854]#

Tuesday, May 19, 2009

Adaptive/Intelligent Cursor Sharing in 11g - turning-off

Here is how the cursor sharing works at a high level with bind variables, especially in conjunction with Histograms (prior to oracle 11g):
  • Optimizer peeks value for the bind on initial parse
  • Initial value of the bind determines the plan
  • Same plan is used/shared regardless of future bind values
  • One plan not always appropriate and optimal for all bind values and results in following problems or side-effects:
- Performance problems
- Instability issues
- Unhappy end users

Adaptive Cursor Sharing address this problem. it shares execution plans ONLY when bind values are "equivalent" - means if it doesn't result in performance degradation. This is the default behavior in Oracle11g.

More information on adaptive cursor sharing can be found at: - The intention of this post is not to talk about this feature - but how to turn-off this default behavior. I can't really think of a reason why anyone would like to turn this feature off, but it is possible, if at all we need it for some reason. This can be achieved by using NO_BIND_AWARE hint.

I believe this hint is meant to be an alternative for using the hidden parameter - _optim_peek_user_binds=false which is used to turn-off bind peeking.

Here is the example:

The default behavior:

SQL> variable lv_id number;

SQL> exec :lv_id :=100;

PL/SQL procedure successfully completed.

SQL> Select count(*) from sh.sales where prod_id = :lv_id;


----if the bind values are peeked, we would see records in V$SQL_CS_STATISTICS:

SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;

-------- ------------ - ----------
34C54884 0 Y 1

Now, let's turn off bind peeking.

SQL> alter system flush shared_pool;

System altered.

SQL> exec :lv_id :=1000;

PL/SQL procedure successfully completed.

SQL> Select /*+ NO_BIND_AWARE */ count(*) from sh.sales where prod_id = :lv_id;

----With the above hint, the sql is not bind aware, therefore we don't see any records in v$SQL_CS_STATISTICS view.

SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;

no rows selected


I guess, this is a better and elegant way to turn-off bind-peeking at the SQL level instead of turning-off bind-peeking at the instance level using the hidden parameter - _optim_peek_user_binds. Again, this parameter is typically used to prevent the side/ill-effects of bind-peeking and the same is now (with 11g) obviated with the introduction of Adaptive Cursor Sharing feature in 11g.

BTW, this hint is available in

Monday, May 18, 2009

Oracle11g CRS new command

Just found out that the functionality of the "crsctl" command has been expanded in Oracle11g. Prior to Oracle11g, we did not have the ability to check the status of the cluster on other (non-local) nodes in the cluster using "crsctl" - but oracle11g makes it now possible:

[root@racnode01 bin]# ./crsctl check cluster
racnode01 ONLINE
racnode02 ONLINE
[root@racnode01 bin]#

Of course, we could very well use "crsstat" command to determine the status of CRS on non-local nodes - but not straight-forward and simple.