Monthly Archives: November 2009

Selectively purging the shared pool

Another very useful thread has developed on the oracle-l mailing list. On a side note, this is really _the_ list to be subscribed to-there are so many experienced Oracle DBAs out that it’s pure joy.

But I digress, back to the problem I often face when performance troubleshooting systems is to purge a SQL statement from the shared pool. Most often, bind variable peeking in conjunction with an empty partition (or otherwise inappropriate stats) caused the optimiser to choose a really inadequate plan for subsequent executions of the same SQL ID.

Oracle 11.1 extended DBMS_SHARED_POOL to include a “purge” function which can be used to manage the shared pool. It’s actually a lot more powerful than just purging SQL plans but that’s for another blog entry :) Anyway, if you are interested, additional documentation can be found in metalink notes 457309.1 and 751876.1. If anyone can shed more light on the various heaps you can pass as an argument, please comment here!

The new version of the package has been back ported and included in the patch set (where it is event protected) and can also be downloaded for and for patch 5614566 on metalink.

Prior to this, I usually used the “comment on table <owner>.<table_name> is ‘<some comment>’ to force an invalidation of the statement in v$sql. Next time the statement is executed you’d hope that you have escaped the bind variable peeking disaster.

Kerry Osborne, a great source for inspiration has posted an easy way to using it. The following is shamelessly taken from the mailing list, I won’t take any credit for it (but I’ll use it whenever I can!)

Beginning the quote, slightly amended to better fit into context:

Heap 0 flushes the whole statement, heap 6 flushes the plans (although I’ve never had a reason to flush just the plans. By the way, when heap=6, plans for all children are flushed.

The backport to seems to work fine as well, but it doesn’t appear to work in versions prior to ( for example).

Here’s the script I use (because I don’t like the funky “address,hash_value” format that the purge procedure requires:

-- flush_sql.sql
 name varchar2(50);
 version varchar2(3);
 select regexp_replace(version,'\..*') into version from v$instance;

 if version = '10' then
 execute immediate
 q'[alter session set events '5614566 trace name context forever']'; -- bug fix for backport
 end if;

 select address||','||hash_value into name
 from v$sqlarea
 where sql_id like '&sql_id';



If you want to have the flexibility to select the heap – change the call to purge like this:


Then just put in 0 or 6 when prompted for the heap.

End qoute. Credit where credit is due:

Kerry Osborne
blog: <;


My Oracle Support broken again

Some more grief today when trying to raise service requests through the flashy yet unreliable MOS frontend. Today has apparently seen the rollout of a patch only to break it some more. Check the oracle-l mailing list archives for some user feedback, it really has been horrendous. If you had to design a hack of the site, it couldn’t have been as bad.

But I digress. I had the problem that MOS didn’t accept my choice of product in the 2nd page of the wizard, even when selecting it from the drop-down list. Oracle Server – Enterprise Edition: how could this be more basic? Anyway, I phoned Oracle support and the first 3 people hung up in the cause of our conversation. Now that it creative support work! But it’s only a problem with the application, which can be worked around. First of all, don’t use the flash interface, for the obvious problems. Use the HTML version instead: and log in as normal

In order to raise a SR, use the following instructions after having clicked on “Create SR”:

  • Please fill in the first 2 pages only!
  • Then click on “save draft”
  • Sign out of metalink, then close your browser (not joking!)
  • Start another browser and log in again using the html interface
  • Next go to the SRs and find your draft
  • From there on, proceed as normal.

Hope that helps!

Oracle’s secure external password store

I have written a large number of nagios checks with various degrees of sophistication over the past, most of them perl scripts. The general problem one faces with these is the database login. Regardless of which way you chose, a password will be stored somewhere and nothing is worse than storing the sys password somewhere in cleartext for checking standby datatabases. I only found two ways acceptable:

  • Using the cryptographic API
  • Using Oracle’s secure password store

I freely admit that I never really attended cryptographic courses at the University and neither was I too keen on this as a solution was needed quickly. So sorry, I won’t go into the perl cryptography here (but I found this link useful:

When I saw that RUEI (Real User Experience Insight) uses exactly this to connect to the database, I thought it’s really worth spending time with. The secure password store is also something very few people seem to know about, but bear in mind it’s very basic. If I remember correctly I tested this with DBD::Oracle 1.21 on Linux and Oracle 32bit.

An example is more verbose than 1000 lines of text so without further ado here’s a stub that worked:


use strict;
use warnings;
use DBI;

my $dbh=DBI->connect("dbi:Oracle:secureTNSName");

my $sth = $dbh->prepare("select name from v\$database");

my $r;

while ($r = $sth->fetchrow_hashref('NAME_lc')) {
 print "$r->{name}\n";

$dbh->disconnect if defined $dbh;

The script uses Oracle’s perl which comes with every database, connects to the secure TNS name (note the lack of passwords!), queries and prints the database name. Before this works without a problem a few prerequisites are necessary.

First I created a new directory for my tnsnames.ora and sqlnet.ora files which didn’t interfere with the settings in $ORACLE_HOME/network/admin.

[oracle@devbox001 securePasswordStore]$ export TNS_ADMIN=`pwd`
[oracle@devbox001 securePasswordStore]$ echo $TNS_ADMIN

[oracle@devbox001 securePasswordStore]$ mkstore -wrl `pwd` -create
Enter password:
Enter password again:
[oracle@devbox001 securePasswordStore]$ ls -lrt
total 20
-rw-r--r--    1 oracle   oinstall      163 Jun  5 10:31 tnsnames.ora
-rw-------    1 oracle   oinstall     7912 Jun  5 10:32 ewallet.p12
-rw-------    1 oracle   oinstall     7940 Jun  5 10:32 cwallet.sso

This creates the wallet and secures it with a password. So far the wallet is empty. Let’s add credentials to it:

[oracle@devbox001 securePasswordStore]$ mkstore -wrl `pwd` \
 >  -createCredential secureTNSName martin somePassword
Enter password:
Create credential

This created a credential for user “martin” with password “somePassword” and an identifier “secureTNSName”. Let’s create the corresponding tns entry, we connect against single instance database “DEV”:

[oracle@devbox001 securePasswordStore]$ vi tnsnames.ora
secureTNSName =
      (ADDRESS = (PROTOCOL = TCP)(HOST = devbox001)(PORT = 1521))

[oracle@devbox001 securePasswordStore]$ tnsping secureTNSName

TNS Ping Utility for Linux: Version - Production on 05-JUN-2009 10:35:07

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
  (HOST = devbox001)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (0 msec)

The sqlnet.ora file needs some changes as well, for me it was only the wallet location:

[oracle@devbox001 securePasswordStore]$ cat sqlnet.ora

    (METHOD_DATA = (DIRECTORY=/home/oracle/martin/securePasswordStore)

With that we are now ready to connect! Note that the syntax requires you to specify the TNS name directly after the “/”, no spaces allowed.

[oracle@devbox001 securePasswordStore]$ sqlplus /@secureTNSName

SQL*Plus: Release - Production on Fri Jun 5 10:43:33 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
SQL> select name from v$database;


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

Metalink references:

  • 340559.1 – Using The Secure External Password Store
  • 403744.1 – How to Use an External Password Store With The JDBC Driver

Sometimes it requires a training course to get to know these features, I picked this one up when I did the security training which really is more a course for developers but never mind.