Analytic Top-N Queries

One of the more advanced tricks I like to exploit are analytic Top-N queries. Although I am using them for quite a while, I recently discovered a “limitation” that I was not aware of. Actually—to be honest—it’s not a limitation; it is a missing optimization in a rarely used feature that can easily worked around. I must admit that I ask for quite a lot in that case.

The article starts with a general introduction into Top-N queries, applies that technique to analytic queries and explains the case where I miss an optimization. But is is really worth all that (more...)

RDBMS events

RDBMS events are often used to do additional tracing and for debug purposes.
Most of them are listed in $ORACLE_HOME/rdbms/mesg/oraus.msg
One such event I use quite often to determine which locks/enqueues a session is requesting is the following.
For example the below trace indicates that an innocuous looking query on v$flash_recovery_area_usage takes a controlfile lock in mode 4 which might not be the best thing to happen on a high throughput multi node RAC environment with a huge number of flashback logs.
SQL> alter session set events '10704 trace name context forever, level 10';

Session altered.

SQL> oradebug setmypid

My OpenWorld 2010 Planning

I am supposed to be working on the next post for my PeopleSoft/RAC project, but decided to take a break from that and talk about OpenWorld.  As just about anyone in the Oracle universe is aware, OpenWorld is September 19th to 23rd.  This year I am doing three sessions along with helping to organize the User Group Forum on Sunday.  Before that all gets going though, I'm hoping to catch the Milwaukee Brewers at the San Francisco Giants.  I am a huge Brewers fan and I have never seen a game outside of Milwaukee.  The Brewers are in San Francisco (more...)

Oracle Forever? Re-consider perpetual licenses

Traditionally, Oracle software is sold “forever,” i.e. on a perpetual license. However, it is also available on what is called a term license – for a limited number of years at some fraction of the cost of a perpetual license.

The Oracle price list states:

Term licensing available for (more...)


We do Guesstimations (calculated or not) once in a while… and this is an interesting read about it…

In the Oracle side… we also use this back-of-the-envelope-calculations for coming up with man-days, capacity planning, hardware recommendations, or just simply knowing what’s happening. And I like these phrases from the article…

“But how can we know the actual figure? We cannot. We can only get closer and closer approximations by measuring things more and more accurately (the volume, not just of the building, but of everything in it, which must be subtracted). It’s not like there’s an (more...)

CP7 for released

Just wanted to let you know that on June 4, 2010, Oracle has released CP7 for You will find it on MetaLink as patch number 9112482. When compared to CP6, 9 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • HP-UX PA-RISC (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Oracle Solaris on SPARC (64-bit)
If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch for bug 4398431(release needs to be (more...)

CP6 for released

Just wanted to let you know that on November 18, 2009, Oracle has released CP6 for You will find it on MetaLink as patch number 8746296:. When compared to CP5, 19 enhancements or bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • HP-UX Itanium
  • HP-UX PA-RISC (64-bit)
  • IBM AIX on POWER Systems (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Sun Solaris SPARC (32-bit)
If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch for (more...)

Graphing the AAS with Perfsheet a la Enterprise Manager

On this previous blog post I was able to take advantage of the AWR repository particularly the DBA_HIST tables to have a far better workload information and nice correlation of the Database Server’s Capacity, Requirements, and Utilization on a single output… and yes… easily going through all the SNAP_IDs!

Having this info made it easier for me to notice trends (text or visualization) and play around with the data (some statistics out of it)… which I can say a big help on finding the root cause of the problem (here and here)

Before going any (more...)

10gR2 – 11gR2, ASSM Space Management “Bug”

ASSM (Automatic Segment Space Management) has an issue when trying to re-use ”deleted” space created by another session.  There is a very specific set of circumstances which must occur for this issue to show itself, but will result in tables (and I suspect indexes) growing significantly larger than they need to be. I am aware that the problem exists in versions through to the current 11gR2 inclusive although I don’t know which Oracle release first introduced the problem.

The conditions required to cause the issue

My site has a number of daemon style jobs running permanently on the database loading data into a (more...)

Dictionary long application

Two versions of an application to encapsulate the data dictionary views that have LONG columns. LONGs are converted to CLOBs in pipelined functions, enabling us to search, copy and manipulate these columns with string functions. A static version supports 9.2 and 10.1 and a dynamic ANYDATASET version supports 10.2 onwards. August 2007 (updated July 2010)

Explosion of Virtualization and the Cloud in Data Centers

Two years ago, after the IOUG Collaborate conference, Steve Lemme and I sat down and discussed what we felt were the hot areas we should focus on for the Oracle user community.   We focused on open source, virtualization and Cloud Computing.  We launched a number of initiatives and had a number of virtualization and open source activities in the last two IOUG Collaborate conferences.

So it's been an amazing journey watching the continued explosion of open source, the Cloud and the use of virtualization in data centers this last year.  I've been talking to a number of (more...)

A first look at OBIEE 11g with Oracle OLAP

For those who missed it, the global launch for the 11g release of the Oracle Business Intelligence Enterprise Edition suite (OBIEE) took place in London on July 7th.

And the fantastic news for Oracle OLAP customers is that OBIEE 11g will work out-of-the-box with Oracle OLAP in almost exactly the (more...)

Micropayments opening gate to the fortune at the bottom of the pyramid

Late C. K. Prahalad wrote a path breaking book showing to the world that there are great opportunities in the emerging as well as poor nations. Those potential markets would need innovation to realize.

When he wrote The Fortune at the Bottom of the Pyramid, he may not have (more...)

Loosely typed interfaces – The Normalizer Pattern and Oracle SOA Suite

When getting two or more systems working together, the making a connection part is generally the easiest, identity propagation is a bit trickier, but many times, the syntax and semantics of one system is at odds with another one. Over the decades this has been manifest in many ways and (more...)

JDeveloper Overview and (book) Review

Dear visitors, I will pretend I forgot that I'm about a year without posting anything and I will go straight to the subject that I owe to you: The review of the book "Processing XML documents with...

This is a summary only. Please, visit the blog for full content and (more...)

Retropay by Employee

I'm posting the last time a lot about Apex. While in reality I'm mainly busy with Oracle Payroll.If your legislation uses advanced or enhanced retropay, there is the RetroNotifications process to run, to identify which employees have retrospective changes.And in a next step, you run the RetroPay process. The enhanced version does not have any assignment set parameter anymore. In other words, the

unplumb (or unbinding) NICs on Linux

I’ve been quiet for a long time now, but this entry hopefully will shake the cobwebs off and get me back into the habit.

I recently had a need to “unplumb” (from Solaris fame) or make interfaces on Linux “disappear” from the ifconfig list. It could be that I don’t know how to completely deconfigure an interface, but I didn’t find any methods to unassign an IP address from a Linux Ethernet interface after it was assigned. You can take interfaces down (ifconfig eth3 down) and reconfigure them to assign different addresses, but not remove the address completely.

After many (more...)

APEX 4.0 Known Issues

Since releasing APEX 4.0 we have identified a number of issues.
Many are directly related to upgrading applications from previous releases.
The known issues are outlined on OTN here:
As with any upgrade we strongly recommend you test your applications in a development environment (more...)

Quick Notes

Just a bit of housekeeping.

APEX 4.0 Builder Hotkeys
This is just a quick note on the APEX 4.0 Builder Hotkeys. I have updated the script because not all previous/next buttons were included in the last version. Alt + PageUp/PageDown should now give the right response.

You can (more...)

Table Comparison Signature

We've been working on a data archiving project to move old data from the production schema in one database into an archive schema in a different tablespace, on a remote server.

We copy data from a production table (let's call it PROD.FINANCE_TAB into a pre-existing table in an archive schema (e.g. ARCH.FINANCE_TAB).  The fact that they are in a different database is actually irrelevant to the problem at hand.

If  PROD.FINANCE_TAB were to change structure (due to an added column or a datatype change), the archive transaction may fail.  So we need to (more...)