Oracle 11g: Switching to native compilation

One of the most celebrated features of Oracle 11g is supposed to be the PL/SQL native compilation feature. This allows PL/SQL code to be compiled directly into machine code that is also stored inside the database, thus eliminating the need for an external C complier of DLL loader.

Sam R. Alapati and Charles Kim in their Oracle Database 11g New Features for DBA's and Developers book published by APRESS, write that tests performed by Oracle showed an increase in performance of up to 20 times when using native SQL.

There is only one startup parameter that affects the compilation mode of new PL/SQL programs. This parameter is named plsql_code_type and its value can be either 'NATIVE' or 'INTERPRETED'. So doing an :

SQL> alter system set plsql_code_type = 'NATIVE' scope=both;

System altered.

SQL> 

... will effect all new PL/SQL programs that will be created or compiled from this point on. Switching the entire database to use native PL/SQL is a bit more complicated and is performed by following these steps :

1. Shutdown the database.
2. Start it up again in upgrade more. (startup upgrade)
3. Run the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script. This will set the execution mode of all database PL/SQL code blocks to native.
4. Shutdown the database and start it up again in normal mode.
5. Run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to recompile all invalid PL/SQL code units.

Be advised that the last step may take a considerable amount of time, depending on the number of PL/SQL objects in your database. Oracle provides the following query to test the number of objects left to be compiled.

SQL>  SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

  COUNT(*)
----------
      2055

SQL> r   
  1*  SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6)

  COUNT(*)
----------
      2005

SQL> r
  1*  SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6)

  COUNT(*)
----------
      1109

SQL>

One last thing. The process can be reversed, by following the steps above but instead of running dbmsupgnv.sql, run the dbmsupgin.sql also located in $ORACLE_HOME/rdbms/bin.

Linux: Getting passed rsync's most notorious error messages and determining whether a shell is interactive or not.

While trying to back up my personal data using rsync -aruvzh --delete /home/thanassis/jdevhome/ thanassis@192.168.1.68:/home/thanassis/jdevhome I run into the following error message.

protocol version mismatch - is your shell clean?
(see the rsync man page for an explanation)
rsync error: protocol incompatibility (code 2) at compat.c(69)

A little googling and a final look at the rsync manual revealed that :

DIAGNOSTICS
     rsync occasionally produces error messages that may seem a little cryp-
     tic.  The  one that seems to cause the most confusion is “protocol ver-
     sion mismatch — is your shell clean?”.

     This message is usually caused by your startup scripts or remote  shell
     facility  producing  unwanted garbage on the stream that rsync is using
     for its transport. The way to diagnose this  problem  is  to  run  your
     remote shell like this:

            ssh remotehost /bin/true > out.dat

     then  look  at out.dat. If everything is working correctly then out.dat
     should be a zero length file. If you are getting the above  error  from
     rsync  then  you  will probably find that out.dat contains some text or
     data. Look at the contents and try to work out what  is  producing  it.
     The  most  common cause is incorrectly configured shell startup scripts
     (such as .cshrc or .profile) that contain output  statements  for  non-
     interactive logins.

Needless to say that fortune was the one to blame. My .bashrc file had the following last statements since the days of SuSE 8.2 and although I am now using a completely different distro, I still refuse to get them out of my startup files.

# Something to remember the old SuSE days
if [ -x /usr/bin/fortune ] ; then
    echo
    /usr/bin/fortune
    echo
fi

So, the solution would be to display a fortune cookie only when the current shell is interactive and then it was time for the Bash Reference Manual to come to our rescue. Their answers are straightforward :

To determine within a startup script whether Bash is running interactively or not, examine the variable $PS1; it is unset in non-interactive shells, and set in interactive shells. Thus:

     if [ -z "$PS1" ]; then
        echo This shell is not interactive
     else
        echo This shell is interactive
     fi

Alternatively, startup scripts may test the value of the `-' special parameter. It contains i when the shell is interactive. For example:

     case "$-" in
     *i*)  echo This shell is interactive ;;
     *)    echo This shell is not interactive ;;
     esac

In my case I wanted to run fortune on interactive shells only. So again I modified my bashrc file to look like :

# Display a fortune cookie on interactive logins only
if [ -n "$PS1" ]; then
        # Some people don't like fortune. If you uncomment the following lines,
        # you will have a fortune each time you log in ;-)
        if [ -x /usr/bin/fortune ] ; then
                echo
                /usr/bin/fortune
                echo
        fi
fi

... and that did it.