PSU lists

All the PSU lists are available with Doc ID 756388.1 on Metalink.
In particular Oracle Database Recommended Patches are at Doc ID 756671.1.

Virtual Circuit Wait event

I have found few explanations connected with this event. It is specific for "shared server" configuration of the Oracle RDBMS.

1. Dick Goulet suggests it is connected with periods of inaccessibility of shared servers due to easy to miss short bursts of load. The solution was suggested for the database in version 9.2.0.4
2. Jonathan Lewis describes this event in similar way and suggests to check the v$reqdist view in order to get a histogram for duration of calls
3. Very detailed description is available at Igor Usoltsev site and most up-to-date as it refers to the 11g version. Most important observations are:
   
   • old event virtual circuit status was split to shared server idle wait (which is recognized as an idle one) and virtual circuit wait (which consists actually of 2 parts: idle one and network non-idle one - the event will be probably split into such 2 components in future versions of the RDBMS)
   • the virtual circuit wait event is strongly connected with the SQL*Net message from client event (which is recognized usually as an idle one) and the existence of one or the other depends on the size of the array used for fetching rows - the bigger the array the more SQL*Net message from client the less virtual circuit wait

In our case we have had this occurred mostly on long-running queries over a db link, so very inconvenient, because even if this event is "partially idle", then for us a response time always includes this event. The solution for us is to tune every query over this db link as well as possible.

Auto-start of an Oracle database

The traditional way to run/stop automatically an Oracle RDBMS is to call appropriately the script dbstart/dbshut. This script takes one optional parameter, which is an ORACLE_HOME of a listener to run. The following script does the job and should be configured to run at the proper runlevels (usually start on 3, shut down on 0 and 6).
Of course one needs to keep oratab up to date in required configuration.


#!/bin/bash

## date : 2011-11-22
## desc : starts|stops all the instances with enabled autostart named in oratab
## desc : usually to be used on dev/test environments
## os : Linux, possibly Solaris, other may need improvements
## loc : /etc/init.d

CMD=$1
LOG='log/oracle_db.log'
su -l oracle -c 'if [ ! -d $HOME/log ]; then mkdir -p $HOME/log; fi;'
case $CMD in
start)
su -l oracle -c '$ORACLE_HOME/bin/dbstart $ORACLE_HOME &> $HOME/$LOG'
;;
stop)
su -l oracle -c '$ORACLE_HOME/bin/dbshut $ORACLE_HOME &> $HOME/$LOG'
;;
*)
echo "Usage: $0 start|stop"
;;
esac
if [ $? -ne 0 ]; then
echo 'Command failed. More details in /log/oracle_db.log'
fi

exit 0


Of course Windows does the autostart in a different way - one needs to set proper flag/s in the registry.

Shrinking temporary tablespaces in an Oracle database

This new in 11g feature is very similar to resizing temporary tablespace files. The main difference is that while resizing the RDBMS is able to cut off only not allocated space, while shrinking additionally is able to deallocate space, which is free and a subject to reuse.

So there is no analogy to shrinking tables for example. There is no data movement. If in the area of a cut there exists an object used at the moment, shrinking is of course able to return back only the space between the end of a last such object and the end of a file.