gpmilliken's blog

A Quick Method to Identify Performance Gains y Using Bind Variables

I'm on a quest to locate some performance gains in a clients database quickly. One of the key factors in performance and scalability is to avoid parsing and latching. As we all know BIND variables can help developers build robust scalable applications.


Oracle Database Administration
Technical Note
September 28, 2009
George Milliken, DBA


Background on the Benefits of Using CURSOR_SHARING=FORCE

Setting the init.ora CURSOR_SHARING=FORCE will work around source code that uses literal values instead of bind variables. (see below for proof of the performance improvement). However, there are some potential side effects of using CURSOR_SHARING=FORCE as follows:

1. Applications (report writers) that DESCRIBE cursors may get erroneous column widths
2. Soft parsing will continue to be higher than needed

Oracle saving users passwords before EXPORT IMPORT 10g and 11g

When you import an older dump file into a newer instance you will lose users and/or revert their passwords to older password (from say 90 days ago). This can be a headache. So the answer is, save the passwords from the current database by capturing the users and their hashes, then IMPORT and put the hashes back into the restored database. Here's how in 10g:


--before export 

-- do the IMPORT

-- restore passwords
spool recovermypasswords.sql

My Experiences Installing Oracle 11g on Ubuntu

In a nutshell my experience is that Ubuntu Hardy Heron 8 Server and Oracle 11g is painless and works very very well.

I do a bunch of Oracle work and need an Oracle database I can do development and testing on. I prefer Linux to Windows. My favorite version is Gentoo KDE but I have found Ubuntu (Gusty or Heron - 7 or 8) runs really really good on the cheap HP AMD64 Dual Core desktop boxes that Best Buy sells for around $600 with 200 gigs of disk and 3 gigs of RAM.

The Joy of DBMS_METADATA or How To Extract Your DDL From Oracle

Oracle delivered a great tool to us in the form of the DBMS_METADATA package. This package is documented here and allows us to extract the DDL needed to create an object from SQL PLUS quickly and easily.

Auto StatsPack Snapshots and Reports

We run Standard Edition, and in it's a license violation to pull AWRs. Most people don't know or don't care, but we do. So we implemented STATSPACK in all our SE databases some time ago. Lucky thing because AWRs don't quite work anymore in SE.

Linux HowTo - Automatically Find the Most Recent File in a Directory and Email it Out

On one of our systems we setup mpstat to generate load numbers to a file and email them out every 6 hours.

Some folks want the report every ten minutes, so they can keep a close eye on the system.

So we setup a cron job to run every ten minutes. it needs to find the most recent mpstats log file and email that out.

Here's the code in Linux korn (or bash):

cat `ls -rt $BACKUP_LOG_DIR/*mnpstat*log | tail -1` | mailx -s "XYZ Flash mpstat report",


Howto Upgrade Apex 3.02 to Apex 4.02 on RedHat Linux 5 Oracle 11g

1). Goto

2) Accept the license agree ment and click this link

Oracle Application Express 4.0.2 - All languages Download

3) Download the zip file

4) Transfer the zip file to your Oracle database server

5) unzip the zip file in a writeable directory (i.e. the directory must be wriable to the oracle user)

6) Log everyone out of Apex

7) Backup the applications of the database, depending on you rlevel of concern for the existing Apex install.

8) cd apex

Ubuntu Howto RabbitMQ Server on Maverick Meerkat 10.10 for python

To install RabbitMQ on Ubuntu 10.10 Maverick Meerkat do the following

sudo apt-get install python
sudo apt-get install python-pip git-core
sudo pip install -e git+
sudo apt-get install rabbitmq-server

vi and paste this in

#!/usr/bin/env python
import pika

connection = pika.AsyncoreConnection(pika.ConnectionParameters(
        credentials=pika.PlainCredentials('guest', 'guest')))
channel =


Syndicate content