It was 12 years ago today…

It was exactly 12 years ago today that I first presented my Index Internals – Rebuilding The Truth presentation at a local ACT Oracle User Group event. And so my association with Oracle indexes started. It would be an interesting statistic to know how many people have subsequently read the presentation :) It would no doubt result in […]

KeePass 2.27 Released

I just noticed that KeePass 2.27 has been released.

I was introduced to KeePass at my current job and now I use it for everything at home too. You can read how I use KeePass here.

Happy upgrading…



KeePass 2.27 Released was first posted on July 7, 2014 at 8:41 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not (more...)

Recurring Conversations: AWR Intervals (Part 1)

I've seen plenty of blog posts and discussions over the years about the need to increase the default AWR retention period beyond the default value of 8 days. Experienced Oracle folk understand how useful it is to have a longer history of performance metrics to cover an entire workload period so that we can, for example, compare the acceptable performance of the last month end batch processes to the living hell of the current month (more...)

Why is P1 the only parameter populated in cell smart table scan?

Anyone who has looked at Exadata might ask the question, and I did so too. After all, cell smart table scan is in wait class User IO so there should be more, right? This is what you find for a smart scan:

NAME                           PARAMETER1           PARAMETER2           PARAMETER3                     WAIT_CLASS
------------------------------ -------------------- -------------------- ------------------------------ ---------------
cell smart table scan          cellhash#                                                                User I/O
cell smart index scan          cellhash#                                                                User I/O

Compare this to the traditional IO request:

NAME                           PARAMETER1           PARAMETER2            (more...)

Benefits of Single Tenant Deployments

While presenting at a database event, I had a question from one of the attendees on benefits of running Oracle databases in Single Tenant Configuration.  I thought this would be a nice if I post it on my blog as it would benefit others too.

From Oracle documentation, “The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A (more...)

How to Add a Mount Point to a VirtualBox VM

I’m in the process of building a test VM on my MacBook using Oracle VirtualBox.  I’ve started by downloading and installing VirtualBox and then downloading the ‘Developer Days  Database App Development appliance from this page:

After importing the appliance, but prior to starting it, I upped the RAM to 8G and changed the name of the Virtual Machine in the configuration page.  The new VM came up just fine but lacked (more...)

SQL Developer can’t start because MSVCR71.dll is missing (On Windows)

I have installed SQL Developer (with JDK) on Windows many times before, and almost all the time I receive the system error bellow when trying to execute it by the first time. Due to this, I decided to publish the

SQL Plan Baselines

Here’s a thread from Oracle-L that reminded of an important reason why you still have to hint SQL sometimes (rather than following the mantra “if you can hint it, baseline it”).

I have a query that takes 77 seconds to optimize (it’s not a production query, fortunately, but one I engineered to make a point). I can enable sql plan baseline capture and create a baseline for it, and given the nature of the (more...)

No Hakan factor for IOT

Sadly there seems to be no concept of the Hakan factor for an IOT.

I have an application which merges into an IOT, the merge incrementally populating a swag of initially null columns, hence growing the rows in size.  Some simple benchmarking shows the overhead of this versus merging into a table with pre-populated values:

SQL> create table T1
  2   ( x int primary key,
  3     y1 number(10),
  4     y2 number(10),
  5     y3 number(10),

Adjusting Histograms

This is a quick response to a question on an old blog post asking how you can adjust the high value if you’ve already got a height-balanced histogram in place. It’s possible that someone will come up with a tidier method, but this was just a quick sample I created and tested on in a few minutes.  (Note - this is specifically for height-balanced histograms,  and it’s not appropriate for 12c which has introduced (more...)

Speedy #em12c template export

Wither you use a single OEM and migrating to a new OEM or have multiple OEMs, the need to move templates between environments will arise.  I had this exact problem come up recently at a customer site between an OEM 11g and OEM 12c.  In order to move the templates, I needed to export the multiple monitoring templates using EMCLI.  The command that I used to do individual exports was the following:

./emcli export_template -name="<template  (more...)

Understanding Flash: SLC, MLC and TLC


The last post in this series discussed the layout of NAND flash memory chips and the way in which cells can be read and written (programmed) at the page level but have to be erased at the (larger) block level. I finished by mentioning that erase operations take substantially longer than read or program operations… but just how big is the difference?

Knowing the answer to this involves first understanding the different types of flash (more...)

Philosophy 22

Make sure you agree on the meaning of the jargon.

If you had to vote would you say that the expressions “more selective” and “higher selectivity” are different ways of expressing the same idea, or are they exact opposites of each other ? I think I can safely say that I have seen people waste a ludicrous amount of time arguing past each other and confusing each other because they didn’t clarify their terms (and one, or both, parties actually (more...)


Catching up (still) from the Trivadis CBO days, here’s a little detail which had never crossed my mind before.

where   (col1, col2) < (const1, const2)

This isn’t a legal construct in Oracle SQL, even though it’s legal in other dialects of SQL. The logic is simple (allowing for the usual anomaly with NULL): the predicate should evaluate to true if (col1 < const1), or if (col1 = const1 and col2 < const2). The thought that (more...)

Restore datafile from service: A cool #Oracle 12c Feature

You can restore a datafile directly from a physical standby database to the primary. Over the network. With compressed backupsets. How cool is that?

Here’s a demo from my present class Oracle Database 12c: Data Guard Administration. prima is the primary database on host01, physt is a physical standby database on host03. There is an Oracle Net configuration on both hosts that enable host01 to tnsping physt and host03 to tnsping prima


[oracle@host01 ~]$  (more...)

kksfbc child completion

I’ve run into the wait “kksfbc child completion” a few times over the past but found very little written about it. I don’t have an explanation, but I have something that might be as good – a way to reproduce it. By being able to reproduce I at least test theories about it.
I ran a tight loop of

insert into t value(1);
See anything strange? I put “value” instead of “values” so the (more...)

Leveraging Collective Knowledge and Subject Matter Experts to Improve the Quality of Database Support

The database engine plays a strategic role in the majority of organizations. It provides the mechanism to store physical data along with business rules and executable business logic. The database’s area of influence has expanded to a point where it has become the heart of the modern IT infrastructure. Because of its importance, enterprises expect their databases to be reliable, secure and available.

Rapid advances in database technology combined with relatively high database licensing and (more...)

Don’t delete your flashback logs manually

What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance. 
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.

Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba

SQL*Plus: Release Production on Tue Jul 1 09:34:18 2014

Copyright (more...)

What A Performance!

Performance is one of the big watchwords in tht IT industry; nowhere is it more often used than in the database arena, and it’s usually not a good sign. End users know nothing, really, of the inner workings of the database yet they are the first to point the ‘finger of blame’ at the database when the processing speed slows or queries take longer than they would like to return data. [For end users performance (more...)

Configure shared storage for #em12c Business Intelligence Publisher (BIP)

Oracle Enterprise Manager 12c Release 4 has a lot of new features; however, I quickly want to focus on a feature that has been in OEM12c for awhile.  This feature is Business Intelligence Publisher (BIP).  BIP has been a part of OEM12c since it was initially released; at first it was a bit cumbersome to get it installed. With the release of, the OEM team has done a great job at (more...)