Who's online

There are currently 0 users and 29 guests online.

Recent comments


Sorted Hash Clusters RIP

Sorted Hash Clusters have been around for several years, but I’ve not yet seen them being used, or even investigated in detail. This is a bit of a shame, really, because they seem to be engineered to address a couple of interesting performance patterns.

The basic concept is that data items that look alike are stored together (clustered) by applying a hashing function to generate a block address; but on top of that, if you query the data by “hashkey”, the results are returned in sorted order of a pre-defined “sortkey” without any need for sorting. (On top of everything else, the manuals describing what happens and how it works are wrong).

Oracle CPU Time

There are 3 kinds of CPU in the Oracle stats.

  1. Oracle CPU used
  2. System CPU used
  3. Oracle demand for CPU

Starting in 10g Oracle records both the CPU used by the instance as well as the load on the system in v$sysmetric. This is awesome as we can see how busy the system is and how much of the CPU Oracle is responsible for:

Where to begin with Oracle and SQL

Seeing more and more questions on “where do I start with Oracle if I want to be a DBA?”  My perspective is a bit off since I’ve been surrounded by Oracle for over 20 years.  I hardly remember what it was like to start with Oracle and starting with Oracle now in 2013 is quite different than starting with Oracle in 1990.

Here is my list and everything on this list is excellent. I’m sure I missed a few good ones, but maybe people can add them in the comments.

Start with Oracle Docs, they are free and good!

Get the best books and read them

Index Sorting

From time to time I’ve mentioned the fact that the optimizer will sort indexes alphabetically by name as the last tie-breaker when two plans have the same cost. Thanks to an email that arrived a couple of days ago I’ve been alerted to event 10089 (which has been around since at least with the description: “CBO Disable index sorting”.

Setting this event will disable the index sorting (which may result in some tied execution plans changing, of course) and may reduce contention on the row cache objects latches if you’re running or later and doing a lot of hard parsing.

Quick Pluggable Database (PDB) Update…

I’ve been playing around with using SQL Developer and Cloud Control 12cR3 for managing pluggable databases (PDBs) this morning. I’ve added a couple of quick references to my PDB article.

If you understand the DBCA and SQL*Plus approach, the SQL Developer and Cloud Control screens are pretty self explanatory, so I’ve made my references to them brief. :)



TCP Trace Analysis for NFS

How do we know where latency comes from when  there is a disparity in reported I/O latency on  the I/O subsystem and that of the latency reported on the  client box requesting the I/O.

For example if I have an Oracle database requesting I/O  and Oracle says an 8Kb request takes 50 ms yet the I/O storage subsystem says 8Kb I/Os are taking 1ms (averages) , then where does the 49  extra ms come from?

When the I/O subsystem is connected to Oracle via NFS  then there are a lot of layers that could be causing the extra latency.

Screen Shot 2013-08-23 at 1.35.20 PM

Where does the difference in latency come from between NFS Server and Oracle’s timing of pread?

Oracle time units in V$ views

Oracle has a crazy mix of units of time in various v$ views

  • seconds
  • centi-seconds
  • milliseconds
  • microseconds

Some are straight forward such as time_waited_micro, but what unit is “TIME_WAITED”  or “WAIT_TIME” in? For example

WAIT_TIME -  centi

WAIT_TIME – centi




Oracle 12c Multitenant Option : CDBs and PDBs…

I started trying to play with the Oracle multitenant option (all that pluggable database stuff) a little while ago and gave up. It’s wasn’t that it was that difficult. More than anything my problem was I didn’t know what to focus on first. There is so much to it and it’s all interrelated, so you start to write about one small piece and before you know it your article has lost focus and is growing too big. As a result I decided to let it simmer in the background and start looking at some of the other smaller 12c new features first…

My next stumbling block was just about everything in 12c seems to relate back to pluggable databases in some way. So you either pretend it doesn’t exist and have gaping wholes in everything you write, or you have to bite the bullet and get to grips with pluggable databases. So back I came to pluggable databases…

Scalar Subqueries in Oracle SQL WHERE clauses (and a little bit of Exadata stuff too)

My previous post was about Oracle 12c SQL Scalar Subquery transformations. Actually I need to clarify its scope a bit: the previous post was about scalar subqueries inside a SELECT projection list only (meaning that for populating a field in the query resultset, a subquery gets executed once for each row returned back to the caller, instead of returning a “real” column value passed up from a child rowsource).

I did not cover an other use case in my previous post – it is possible to use scalar subqueries also in the WHERE clause, for filtering the resultset, so let’s see what happens in this case too!

Enkitec is a finalist for the UKOUG Engineered Systems Partner of the Year Award

Enkitec has made it to the shortlist of UKOUG Partner of the Year Awards, in the Engineered Systems category. So if you like what we have done in the Exadata and Engineered Systems space, please cast your vote! :-)

Note that you need to be an Oracle user – using your company email address in order to vote. (the rules are explained here).