How to configure command history within SQL*Plus

Refer to the following blog for a full description.

http://sysdba.wordpress.com/2006/10/08/how-to-use-rlwrap-to-get-a-command-history-in-sqlplus/

For a short summary, the rlwrap (readline wrapper) utility can be used to wrap the sqlplus command to allow for command history.

1. Download the rlwrap utility from here.

http://utopia.knoware.nl/~hlub/uck/rlwrap/

2. After downloading and unpacking the tar.gz run the following as root.

./configure
make
make check
make install

3. The rlwrap utility can now be used to call sqlplus.

$ rlwrap sqlplus user/password

4. Create an alias.

alias sqlp ='rlwrap sqlplus'

Automatic Startup of Oracle Instances, Listeners and Enterprise Managers on Linux

This process will go through the setup and configuration to automate the startup and shutdown of the Oracle instances, listeners and Enterprise Managers (Database Control). We will be configuring the use of ssh as it is more secure than rsh. A lot of Linux distributions do not have rsh configured for their default installation. If we use ssh the data will be encrypted and we can avoid installing and configuring rsh.


1. Determine the state of the SSH service.

To determine if the sshd daemon is running use the following command.

# chkconfig --list sshd
sshd            0:off   1:off   2:on    3:on    4:on    5:on    6:off

The daemon is configured to run in system state 2, 3, 4 and 5. Ensure the sshd daemon is configured to automatically start with the system. To start the sshd daemon run the following command as root.

# chkconfig sshd on

As ssh uses port 22, ensure this port is accessible through the firewall.


2. Configure SSH for the root and oracle users. As the root user will need to ssh to the oracle user to startup the Oracle instances, listeners and Enterprise Managers.

a. Login as the root user and generate the private and public keys. Use the default file name of id_rsa.pub and do not enter a passphrase as this will not allow for automatic login.

[root@server ~]# cd $HOME/.ssh
[root@server .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): ENTER
Enter passphrase (empty for no passphrase): ENTER
Enter same passphrase again: ENTER
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
92:20:c7:8f:86:69:43:80:9a:48:a1:68:62:c5:03:62 root@server
[root@server .ssh]#

b. Secure the root user's .ssh directory and contents.

[root@server .ssh]# chmod go-w $HOME
[root@server .ssh]# chmod 700 $HOME/.ssh
[root@server .ssh]# chmod 600 $HOME/.ssh/*

c. Copy the id_rsa.pub file to the oracle user's .ssh directory.

[root@server .ssh]# scp id_rsa.pub oracle@server:/home/oracle/.ssh
The authenticity of host 'server (127.0.0.1)' can't be established.
RSA key fingerprint is 24:8b:b0:70:b1:00:81:13:a7:38:91:5a:39:c6:1c:ef.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server' (RSA) to the list of known hosts.
id_rsa.pub                                    100%  412     0.4KB/s   00:00  
[root@server .ssh]# 

d. Login as the oracle user and navigate to the .ssh directory and copy the contents of the id_rsa.pub file into a file called authorizated_keys2 and delete the id_rsa.pub file.

[oracle@server ~]$ cd $HOME/.ssh
[oracle@server .ssh]$ cat id_rsa.pub >> authorized_keys2
[oracle@server .ssh]$ rm id_rsa.pub

e. Secure the oracle user's .ssh directory and contents.

[oracle@server .ssh]$ chmod go-w $HOME
[oracle@server .ssh]$ chmod 700 $HOME/.ssh
[oracle@server .ssh]$ chmod 600 $HOME/.ssh/*

f. Login as root and test ssh to the oracle user. It should not prompt for a password or passphrase.

[root@server ~]# ssh oracle@server
Last login: Thu Apr 22 14:24:40 2010 from server
[oracle@server ~]$

3. Create startup and shutdown scripts.

a. Create two files in $ORACLE_BASE called dbora_start.sh and dbora_shut.sh. The dbora_start.sh will be used to start the instances, listeners and Enterprise Managers. The dbora_shut.sh will be used to shutdown the instances, listeners and Enterprise Managers. Change the permissions to allow them to be executed only by the Oracle owner and group.

$ chmod 750 dbora_start.sh
$ chmod 750 dbora_stop.sh

b. Edit the dbora_start.sh and use the following script as an example. This script will start the following:

• Oracle 9i instance and listener
• Oracle 10g instance, listener and Enterprise Manager
• Oracle 11g instance, listener and Enterprise Manager

$ORACLE_BASE/dbora_start.sh

#!/bin/bash
#
# Start the listeners and Enterprise Manager Database Control.
#
# Start the Oracle 9i listener.
#
ORACLE_SID=ora9; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl start LISTENER

#
# Start the Oracle 11g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora11; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl start LISTENER
$ORACLE_HOME/bin/emctl start dbconsole

#
# Start the Oracle 10g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl start LISTENER
$ORACLE_HOME/bin/emctl start dbconsole

#
# Start the instances. The dbstart script will look in the oratab
# and will start all instances having a Y in the third column.
#
$ORACLE_HOME/bin/dbstart
exit

c. Edit the dbora_shut.sh script and use the following script as an example. This script will shutdown the following:

• Oracle 9i instance and listener
• Oracle 10g instance, listener and Enterprise Manager
• Oracle 11g instance, listener and Enterprise Manager

$ORACLE_BASE/dbora_shut.sh

#!/bin/bash
#
# Stopping the listeners and Enterprise Manager Database Control.
#
# Stop the Oracle 9i listener.
#
ORACLE_SID=ora9; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl stop LISTENER

#
# Stop the Oracle 11g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora11; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl stop LISTENER
$ORACLE_HOME/bin/emctl stop dbconsole

#
# Stop the Oracle 10g listener and Enterprise Manager Database Control.
#
ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

$ORACLE_HOME/bin/lsnrctl stop LISTENER
$ORACLE_HOME/bin/emctl stop dbconsole

#
# Stop the instances. The dbshut script will look in the oratab
# and will stop all instances.
#
$ORACLE_HOME/bin/dbshut
exit

4. Create a Linux script to be automatically called during system start and shutdown.

a. Login as the root user change to the /etc/init.d directory and create a file called dbora. Change the owner to the Oracle owner and the dba group. Ensure the permissions are set to 750.

# touch dbora
# chown oracle:dba dbora
# chmod 750 dbora

b. Edit the dbora file and use the following script as an example. This script will be configured to run during system startup and system shutdown. The script will determine if a startup or shutdown is in progress and then call the appropriate $ORACLE_BASE/dbora_start.sh or $ORACLE_BASE/dbora_shut.sh script. Change the ORACLE_BASE, ORACLE_HOME, ORACLE variables to match your $ORACLE_BASE, $ORACLE_HOME and Oracle owner respectively. Notice that the script uses SSH to login as the oracle to run the scripts.

/etc/init.d/dbora

#! /bin/sh -x
#
# Change the value of ORACLE_BASE and ORACLE_HOME to specify the
# correct Oracle base and home directories for your installation.
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

#
# Change the value of ORACLE_OWNER to the login name of the
# oracle owner.
#
ORACLE_OWNER=oracle; export ORACLE_OWNER
PATH=${PATH}:$ORACLE_HOME/bin; export PATH
HOST=`hostname`
PLATFORM=`uname`

if [ ! "$2" = "ORA_DB" ] ; then
    ssh $ORACLE_OWNER@$HOST $0 $1 ORA_DB
fi

case $1 in
'start')
    ssh $ORACLE_OWNER@$HOST $ORACLE_BASE/dbora_start.sh
    ;;
'stop')
    ssh $ORACLE_OWNER@$HOST $ORACLE_BASE/dbora_stop.sh
    ;;
*)
    echo "usage: $0 {start|stop}"
    exit
    ;;
esac

exit

c. Create symbolic links to the dbora script within the appropriate run level directories.

# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

5. Finally, do not forget to edit the oratab file and change the last column to Y. Otherwise the instances will not be started by the $ORACLE_HOME/bin/dbstart script nor shutdown by the $ORACLE_HOME/bin/dbshut script. Edit the /etc/oratab file as the root user and change the last column to Y for the SIDs you want to automatically startup and shutdown.

/etc/oratab

# more /etc/oratab
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ora10:/opt/oracle/product/10.2.0/db_1:Y
ora9:/opt/oracle/product/9.2.0/db_1:Y
ora11:/opt/oracle/product/11.1.0/db_1:Y
#

Changing Oracle Environment Variables Using A Bash Script

I have a Linux machine with three different Oracle versions installed. Oracle 9i, Oracle 10g and Oracle 11g. All have been installed under one Linux account. Here are some scripts to set the environment variables for the instance to be accessed.

Oracle 9i Environment: SID ora9

ORACLE_SID=ora9; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

echo
echo 'Environment variables for' $ORACLE_SID
echo '------------------------------'
echo 'ORACLE_SID='$ORACLE_SID
echo 'ORACLE_BASE='$ORACLE_BASE
echo 'ORACLE_HOME='$ORACLE_HOME
echo 'TNS_ADMIN='$TNS_ADMIN
echo 'LD_LIBRARY_PATH='$LD_LIBRARY_PATH
echo 'PATH='$PATH
echo '------------------------------'
echo

Oracle 10g Environment: SID ora10

ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

echo
echo 'Environment variables for' $ORACLE_SID
echo '------------------------------'
echo 'ORACLE_SID='$ORACLE_SID
echo 'ORACLE_BASE='$ORACLE_BASE
echo 'ORACLE_HOME='$ORACLE_HOME
echo 'TNS_ADMIN='$TNS_ADMIN
echo 'LD_LIBRARY_PATH='$LD_LIBRARY_PATH
echo 'PATH='$PATH
echo '------------------------------'
echo

Oracle 11g Environment: SID ora11

ORACLE_SID=ora11; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH

echo
echo 'Environment variables for' $ORACLE_SID
echo '------------------------------'
echo 'ORACLE_SID='$ORACLE_SID
echo 'ORACLE_BASE='$ORACLE_BASE
echo 'ORACLE_HOME='$ORACLE_HOME
echo 'TNS_ADMIN='$TNS_ADMIN
echo 'LD_LIBRARY_PATH='$LD_LIBRARY_PATH
echo 'PATH='$PATH
echo '------------------------------'
echo

To run the scripts one must use the "source" command in order to set the environment variables in your own environment. Without running the script through source the environment variables will be set in the script's environment and not your own.

[oracle@oratest ~]$ source ora10.sh

Environment variables for ora10
------------------------------
ORACLE_SID=ora10
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
TNS_ADMIN=/opt/oracle/product/10.2.0/db_1/network/admin
LD_LIBRARY_PATH=/opt/oracle/product/10.2.0/db_1/lib
PATH=/opt/oracle/product/10.2.0/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin
------------------------------

[oracle@oratest ~]$

Another method is to create aliases to set the enviroment variables. The tricky part is the PATH. Specify a PATH_BASE variable to use when setting the new paths. Here's a sample bash_profile file. The ora10 instance will be the default, with ora9, ora10 and ora11 aliases to switch to different environments.

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
umask 022

ORACLE_SID=ora10; export ORACLE_SID
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
PATH_BASE=/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin; export PATH_BASE
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH

alias ora9='ORACLE_SID=ora9; export ORACLE_SID; 
ORACLE_HOME=$ORACLE_BASE/product/9.2.0/db_1; export ORACLE_HOME;
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN;
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH; 
ORACLE_OEM_JAVARUNTIME=/usr/local/jre1.3.1_19; export ORACLE_OEM_JAVARUNTIME;
PATH=$ORACLE_HOME/bin:$PATH_BASE; export PATH; 
echo ORACLE_SID=$ORACLE_SID; echo ORACLE_HOME=$ORACLE_HOME'

alias ora10='ORACLE_SID=ora10; export ORACLE_SID; 
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME;
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN;
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH;
PATH=$ORACLE_HOME/bin:$PATH_BASE; export PATH; 
echo ORACLE_SID=$ORACLE_SID; echo ORACLE_HOME=$ORACLE_HOME'

alias ora11='ORACLE_SID=ora11; export ORACLE_SID; 
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME;
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN;
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH;
PATH=$ORACLE_HOME/bin:$PATH_BASE; export PATH; 
echo ORACLE_SID=$ORACLE_SID; echo ORACLE_HOME=$ORACLE_HOME'

Oracle Installation Issues on Linux

I ran into a couple issues when installing Oracle 10g on Oracle Enterprise Linux 5.

The first problem was the runInstaller would not start the Oracle Universal Installer. The problem ended up being the libXp. Installing libXp-1.0.0.8.i386.rpm solved the problem. The package can be downloaded from http://rpm.pbone.net/.

The second problem occurred during installation where the Network pre-installation check failed due to the /etc/hosts not containing the correct information. Changing the /etc/hosts to match the following format solved the problem.

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.1.115   myhost.mydomain.com myhost

Installing Oracle 9i on Oracle Enterprise Linux 5 (which is based on Red Hat Enterprise Linux 5) is not supported by Oracle. However, thanks to nirajkvinit's blog, I was able to install Oracle 9i on Oracle Enterprise Linux 5 with success.

http://nirajkvinit.blogspot.com/2008/10/installing-oracle-9i-on-rhel5.html

Literal vs Bind Variables, Bind Variable Peeking and Cursor Sharing

Literals vs Bind Variables

I'll start with the difference between Literals and Bind Variables. A literal means the values being compared in the SQL statement are hard coded in the statement itself. An example of a literal value would be, select name from emp where id=10.

A bind variable is created and set to a particular value, which is then used by a SQL statement. This allows the developer to assign any value to the bind variable and the SQL statement will use the new value. For example, select name from emp where id=:id. The :id is the bind variable and the values being passed into the statement can change as the developer needs.

The use of literal values will cause many unique statements to be cached as each literal value causes the statement to be different. This will cause more space to be used in the Share Pool. With the use of bind variables the statement remains the same, therefore there is only one statement cached as opposed to many.

When the Oracle server process receives a new SQL statement from the client it generates a new cursor and places it in the Shared Pool (cached). The Oracle server process will also generate an execution plan to be used by this new cursor. Once this cursor and execution plan have been initially set, they are not changed unless some operation invalidates the cursor in the Shared Pool, or the Shared Pool is flushed, or the cursor is aged out of the Shared Pool. All these conditions would cause the Oracle server process to re-create the cursor and regenerate the execution plan. When using literal values in SQL statements, there will be many cursors in the Shared Pool as the statements are all different. With each cursor in the Shared Pool there will be an execution plan. When using bind variables there will be only one cursor and one execution plan in the Shared Pool regardless of the value being send to the bind variable. If the bind variable is selecting from a column which is highly skewed then the execution plan may not be optimal.


Bind Variable Peeking

Bind Variable Peeking was introduced in Oracle 9i. Prior to Bind Variable Peeking the Oracle optimizer did not know the value being used in the statement and could end up choosing a poor execution plan. Bind Variable Peeking will look at the value of the bind variable the very first time the statement is executed and generate an execution plan. The cached execution plan will be optimal based on the first value sent to the bind variable. Subsequent executions of this statement will use the same execution plan regardless of the value of the bind variable. Using Bind Variable Peeking is good if the bind variable is selecting from a column which is not skewed. This means the initial execution plan for the statement will be optimal for all values of the bind variable. For example, a bind variable for the emp.id is a good idea as the value is unique in the table. The initial execution plan will be good for all statements against id. On the other hand, using a bind variable for emp.deptid could pose problems. Let say there are two departments. Department 20 has 3 employees and department 21 has 10,000 employees. As the emp.deptid data is skewed, the initial execution and execution plan of the statement may not be optimal for subsequent executions of the statement. Looking at select name from emp where deptid=:deptid, with deptid set to 20 returns 3 rows. If this was the initial statement, the optimizer would create an execution plan which would use an index. If deptid is then set to 21, where 10,000 rows are returned, the optimizer will still use the initial plan and use the index. Using the index in this case is not optimal as a large percentage of the table will be returned. A full table scan would be better. So you see the problem with bind variable peeking. Oracle 11g overcomes the Bind Variable Peeking problem with the new Adaptive Cursor Sharing feature. Due to the Bind Peeking problem, some developers will purposely use literal values, for fields with highly skewed data, to avoid the Bind Variable Peeking problem. When they use literal values it forces Oracle to create a single cursor with its own execution plan. This ensures the query will be executed optimally.


Cursor Sharing

The cursor_sharing parameter can be set to one of three values, FORCE, EXACT or SIMILAR. This parameter is really telling the Oracle server process how to handle statements which have literal values. If the parameter is set to FORCE or SIMILAR the Oracle server process will strip out the literal values in the SQL statements and generate system generated bind variables. With FORCE or SIMILAR all statements will go through the Bind Variable Peeking process. At first I though both SIMILAR and FORCE will expose and amplify the Bind Peeking problem. In my testing I determined that SIMILAR does not expose the Bind Peeking problem, but FORCE does. If the parameter is set to EXACT, the Oracle server processes the query as it is and generates an execution plan based on the query. With EXACT literal values are maintained. Here are the tests I performed. Notice that both SIMILAR and EXACT act the same when literal and bind variables are used.

SQL> connect bvp/oracle
Connected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter optimizer;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     90
optimizer_index_cost_adj             integer     25
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE

SQL> show parameter cursor_sharing;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

SQL> -- Generate statistics with histograms.
SQL> exec dbms_stats.gather_table_stats(ownname=>'BVP',tabname=>'DTREE',estimate_percent=>NULL,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, num_buckets from user_tab_columns;

TABLE_NAME                     COLUMN_NAME                    NUM_BUCKETS
------------------------------ ------------------------------ -----------
DTREE                          SUBTYPE                                  3
DTREE                          DATAID

SQL> select subtype, count(*) from dtree group by subtype;

   SUBTYPE   COUNT(*)
---------- ----------
       144      74998
       141          1
         0          1

SQL> -- Test 1: EXACT + Literals
SQL> select count(*) from dtree where subtype=144;

  COUNT(*)
----------
     74998

SQL> set linesize 120
SQL> set pagesize 50000
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  b199gz2s1x8zn, child number 0
-------------------------------------
select count(*) from dtree where subtype=144

Plan hash value: 3375002384

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 |   292K|    38   (6)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SUBTYPE"=144)


19 rows selected.

SQL> select count(*) from dtree where subtype=141;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  90gn0x6fjjccv, child number 0
-------------------------------------
select count(*) from dtree where subtype=141

Plan hash value: 1248639843

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| DTREE_SUBTYPE |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBTYPE"=141)


19 rows selected.

SQL> -- Test 2: EXACT + Bind Variable
SQL> variable subtype number;
SQL> exec :subtype := 141;

PL/SQL procedure successfully completed.

SQL> select count(*) from dtree where subtype=:subtype;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype

Plan hash value: 1248639843

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| DTREE_SUBTYPE |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBTYPE"=:SUBTYPE)


19 rows selected.

SQL> exec :subtype := 144;

PL/SQL procedure successfully completed.

SQL> select count(*) from dtree where subtype=:subtype;

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype

Plan hash value: 1248639843

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| DTREE_SUBTYPE |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBTYPE"=:SUBTYPE)


19 rows selected.

SQL> -- Test 3: SIMILAR + Bind Variable
SQL> alter system set cursor_sharing='SIMILAR' scope=memory;

System altered.

SQL> show parameter cursor_sharing;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      SIMILAR
SQL> alter system flush shared_pool;

System altered.

SQL> print subtype;

   SUBTYPE
----------
       144

SQL> select count(*) from dtree where subtype=:subtype;

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype

Plan hash value: 3375002384

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 |   292K|    38   (6)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SUBTYPE"=:SUBTYPE)


19 rows selected.

SQL> exec :subtype := 141;

PL/SQL procedure successfully completed.

SQL> select count(*) from dtree where subtype=:subtype;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  gv59k4a054s34, child number 0
-------------------------------------
select count(*) from dtree where subtype=:subtype

Plan hash value: 3375002384

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 |   292K|    38   (6)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SUBTYPE"=:SUBTYPE)


19 rows selected.

SQL> -- Test 4: SIMILAR + Literals
SQL> select count(*) from dtree where subtype=144;

  COUNT(*)
----------
     74998

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8xh6udukqrd0u, child number 0
-------------------------------------
select count(*) from dtree where subtype=:"SYS_B_0"

Plan hash value: 3375002384

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| DTREE_SUBTYPE | 74998 |   292K|    38   (6)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SUBTYPE"=:SYS_B_0)


19 rows selected.

SQL> select count(*) from dtree where subtype=141;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8xh6udukqrd0u, child number 1
-------------------------------------
select count(*) from dtree where subtype=:"SYS_B_0"

Plan hash value: 1248639843

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| DTREE_SUBTYPE |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBTYPE"=:SYS_B_0)


19 rows selected.

Statistics

During the Bind Variable Peeking process, the initial bind variable value will be used to generate an execution plan. If there are histograms the optimizer takes these into account when generating an execution plan. If there are no histograms then the optimizer will assume an even distribution of the values in the table and generate an execution plan based on this fact.

To generate statistics without histograms use the following.

execute dbms_stats.gather_schema_stats
(
 ownname=>'SCHEMAOWNER',
 estimate_percent=>NULL,
 cascade=>TRUE,
 degree=>dbms_stats.default_degree,
 method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1'
)

To generate statistics and histograms use the following.

execute dbms_stats.gather_schema_stats
(
 ownname=>'SCHEMAOWNER',
 estimate_percent=>NULL,
 cascade=>TRUE,
 degree=>dbms_stats.default_degree,
 method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'
)

Oracle LONG to CLOB Performance Tests

I have seen performance problems when converting a LONG column to a CLOB. I decided to determine the underlying problem and see if I can get it to convert faster. Here are the test results.

To get started, here is the platform and table information.

OS: Windows 2003 Enterprise Edition SP2 32-bit
Memory: 4G
CPU: 3Ghz
Oracle: 10.2.0.4
Table: 298,809 records
Table Size: 5.186 GB
Prior to conducting the tests the following conditions where met:

• I ran a conversion to set the size of the tablespace's datafiles to avoid the overhead of autoextending.
• The database was in archive log mode.
• Flashback Database was not enabled.

ALTER TABLE MODIFY (column CLOB)

The initial test was to provide a base conversion time in which to compare subsequent tests.

Time: 2713 seconds

********************************************************************************
alter table klong modify (segment clob)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1    422.53    2713.20    1399930    5548436    9620418      298809
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    422.53    2713.21    1399930    5548437    9620418      298809

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 118

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
control file sequential read                    4        0.00          0.00
db file scattered read                      27507        0.46        364.71
direct path write                         1092663        0.40        672.39
db file sequential read                    721900        0.42        931.23
direct path read                           298752        0.40        160.06
log file switch completion                    652        0.50         78.82
log buffer space                              208        0.26         10.91
rdbms ipc reply                               202        0.47          0.49
latch: object queue header operation            1        0.00          0.00
log file sync                                   1        0.00          0.00
SQL*Net message to client                       1        0.00          0.00
SQL*Net message from client                     1        0.00          0.00
********************************************************************************

The second test performs the conversion with table logging and lob segment logging turned off. To my surprise the suppression of the redo generation did not result in a faster time. In fact it was slower. This was due to the fact that Oracle records the unlogged blocks in the control file. This caused wait events on writing and read from the control files.

Time: 3888 seconds

********************************************************************************
alter table klong modify (segment clob) lob (segment) store as (nocache nologging)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1    734.59    3888.37    1356793    5548407    9620018      298809
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    734.59    3888.37    1356793    5548409    9620018      298809

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 118

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
control file sequential read              3287560        0.36       1880.21
direct path write                         1100750        0.22        215.74
control file parallel write               1200108        0.22        589.89
db file sequential read                    728089        0.24        362.76
direct path read                           298752        0.17         54.59
rdbms ipc reply                               149        0.11          0.12
log file switch completion                     16        0.15          1.03
enq: CF - contention                          226        0.63          6.68
db file scattered read                      20648        0.09        163.87
latch: object queue header operation            1        0.00          0.00
latch: enqueue hash chains                      1        0.00          0.00
enq: RO - fast object reuse                     2        0.15          0.15
log file sync                                   1        0.00          0.00
SQL*Net message to client                       1        0.00          0.00
SQL*Net message from client                     1        0.02          0.02
********************************************************************************

TO_LOB
Results using TO_LOB coming soon.DBMS_REDEFINITIONInitial test without parallelism.

Time: 2732 seconds

create table klong_lob
(
  ownerid number(10),
  longid number(10),
  segmentid number(10),
  segment clob
);

declare
  col_mapping varchar2(1000);
begin
  col_mapping := 'ownerid ownerid, ' ||
                 'longid longid, ' ||
                 'segmentid segmentid, ' ||
                 'to_lob(segment) segment';
  dbms_redefinition.start_redef_table('DBUSER',
                                      'KLONG',
                                      'KLONG_LOB',
                                      col_mapping,
                                      dbms_redefinition.cons_use_rowid,
                                      null,
                                      null);
end;
/

declare
  error_count pls_integer := 0;
begin
  dbms_redefinition.copy_table_dependents('DBUSER',
                                          'KLONG',
                                          'KLONG_LOB',
                                          1,
                                          true,
                                          true,
                                          true,
                                          false,
                                          error_count);
  dbms_output.put_line('errors := ' || to_char(error_count));
end;
/

exec dbms_redefinition.finish_redef_table('DBUSER',
                                          'KLONG',
                                          'KLONG_LOB');

drop table klong_lob;

The second test is performed using parallel DML and parallel QUERY using a degree of 2. The process took longer but I suspect it is due to a single CPU and single disk. I will conduct these tests on a server with more resources.

Time: 3855 seconds

create table klong_lob
(
ownerid number(10),
longid number(10),
segmentid number(10),
segment clob
);

alter session force parallel dml parallel 2;

alter session force parallel query parallel 2;

declare
col_mapping varchar2(1000);
begin
col_mapping := 'ownerid ownerid, ' ||
               'longid longid, ' ||
               'segmentid segmentid, ' ||
               'to_lob(segment) segment';
dbms_redefinition.start_redef_table('DBUSER',
                                    'KLONG',
                                    'KLONG_LOB',
                                    col_mapping,
                                    dbms_redefinition.cons_use_rowid,
                                    null,
                                    null);
end;
/

declare
error_count pls_integer := 0;
begin
dbms_redefinition.copy_table_dependents('DBUSER',
                                        'KLONG',
                                        'KLONG_LOB',
                                        1,
                                        true,
                                        true,
                                        true,
                                        false,
                                        error_count);
dbms_output.put_line('errors := ' || to_char(error_count));
end;
/

exec dbms_redefinition.finish_redef_table('DBUSER',
                                        'KLONG',
                                        'KLONG_LOB');

drop table klong_lob;