Sorting TO_CLOB

When a query coverts a column using TO_CLOB, a TEMPORARY LOB SEGMENT is created in the temporary tablespace.  If the result set is sorted, then this segment can become large and if the query is called concurrently by multiple sessions, it can be the cause of a lot of contention for the temp file headers, eg buffer busy waits.  The TEMPORARY LOB SEGMENT blocks are buffered and the DBWRs write them to the tempfiles (rather than the shadow process writing directly).

One way to reduce the performance impact is to avoid the sort by creating an index with the columns in the order by clause.  Alternatively, you might be able to sort the result set before using the TO_CLOB function, which allows efficient pipelining of the data.

If using UFS, then check the tempfiles are not sparse.

Consider using more tempfiles.

Monitoring Oracle Databases with SCOM

This post is about your options for monitoring your Oracle databases running on Windows with System Center Operations Manager (SCOM).  I’ll start with why you might want to rule out Oracle Enterprise Manager (OEM).

Why not OEM?

OEM is becoming more common, and it is appropriate for organisations with large Oracle estates, and possibly for smaller ones that don’t require high availability or disaster recovery.  However, some times is just isn’t a good fit.  These are the reasons I chose not to use OEM for monitoring a particular organisation’s Oracle databases:

  1. SCOM was already the main monitoring and alerting tool.  It could create tickets in the service desk system, send email notifications and phone on-call staff.
  2. OEM can plug into SCOM, but the Oracle connector costs extra, and would add complexity.
  3. To make OEM highly available (RAC) and recoverable in a disaster (Data Guard), two or three extra servers would be required.  In most cases, this configuration requires extra Oracle licences.
  4. OEM is a bloated solution at the best of times, with a lot of maintenance, bugs and quirks that result in unnecessary call outs for DBAs.  In this case the OEM installation would have been more complex (eg RAC) than the Oracle databases it would monitor.  The DBA team (new to Oracle) would need to learn a lot just to support and use OEM.

SCOM Management Packs for Oracle

At the time of writing, there were three commercially provided management packs (MPs) available on the market.  (I saw someone had also written their own MP, but it wasn’t freely available and seemed to be too much effort to create).

My colleague and I evaluated all three MPs, and were very disappointed.  None of them were fit for purpose.

  • Two MPs failed to detect the database instance not open.
  • Two MPs failed to detect errors in the alert log.  Both of them may have worked eventually if we’d spent more time with the support staff.
    I lost confidence in the first MP when told that the alert log had to be under 2MB until the relevant bug could be fixed (several weeks!?).
    The other MP required every ORA-nnnnn message had to be listed in a file next to a custom description, rather than using regular expressions!?
  • One MP was missing key sections / instructions in the documentation.  (Even important features were lacking from the marketing material).
  • One MP created too many sessions, so if the server was busy, the sessions could accumulate.
  • One MP ran extremely inefficient SQL, eg measuring segment size by adding up the size of the extents.  (Imagine doing that on a data warehouse).
  • One MP offered templates for custom monitors and rules, which seemed promising, but they were too inflexible and we couldn’t get them to work as intended.
  • Support was slow in our case, because of the time zone difference.

A Simple Solution

SCOM comes with the standard functionality to monitor Windows event logs.  If we can write Oracle database related notifications to a custom event log, then the SCOM integration is simple.

It didn’t take long to put together a simple but flexible Oracle monitoring solution using standard components:

  1. Task Scheduler – runs a Powershell Script
  2. Powershell script – runs SQL
  3. Powershell script – processes the output
  4. Powershell script – writes to the event log (and optionally sends emails)
  5. SCOM – raises alerts based on the event log records


  • The connection to SCOM is simple, fast and reliable (based on standard functionality)
  • The Oracle monitoring can have new metrics added and bugs fixed quickly
  • The Oracle monitoring can be calibrated to each instance to reduce unnecessary alerts
  • The Powershell code required is not complex.  It is basically parsing text files (from SQL*Plus output), so a DBA with UNIX shell scripting experience can put it together easily
  • It’s free!


Beware When Installing a New Oracle Home on Windows

When running Oracle databases on UNIX or Linux, it is easy to install a new Oracle Home without an outage.  The work can be done during working hours, long before any change window or outage to existing databases and applications.  The switch to a the new Oracle Home is also straight forward.

However, on Windows, there are a number of considerations for a cautious DBA, and a longer outage for upgrades may be unavoidable.

I’ve written some brief notes, based on my experience patching to on Windows Server 2008: Patching/Upgrading Oracle On Windows


OERR Finally Available on Windows

I have relied on oerr to quickly find the meaning of error messages (ORA-, TNS-, RMAN-) on UNIX and Linux for decades.

How annoying that Oracle on Windows doesn’t provide this functionality!  Sure, I can use the error documentation, or SQLCL (oerr is built in), but it wastes time.

I downloaded Oracle Database 12c Release 1 ( for Microsoft Windows (x64), and found oerr now exists, but the message files don’t!

There is an easy solution: OERR on Windows 12.1

SQL*Plus on Windows

Younger DBAs, or those that started on Windows, may rely on GUI tools like Enterprise Manager and SQL Developer.  However, this old DBA is entrenched in the command line world.  GUI tools have their uses, but using a suite of scripts designed for SQL*Plus is the most flexible and portable practice.  Independence from tools that are not universally installed is especially useful for contractors, consultants or anyone that wants to change organisations during his/her career.

The Windows GUI version of SQL*Plus disappeared with 11g, but we still have the command line (DOS) version available.  Until SQLCL is ready for me (and me for SQLCL), I’ll be using SQL*Plus.  See the first of my Oracle on Windows tips: SQL*Plus on Windows 7