Introduction to Intel Pin

This blogpost is an introduction to Intel’s Pin dynamic instrumentation framework. Pin and the pintools were brought to my attention by Mahmoud Hatem in his blogpost Tracing Memory access of an oracle process: Intel PinTools. The Pin framework provides an API that abstracts instruction-set specifics (on the CPU layer). Because this is a dynamic binary instrumentation tool, it requires no recompiling of source code. This means we can use it with programs like the Oracle (more...)

User ASADMIN does not have the privilege of deploying/undeploying composites

I am trying to deploy the SOAP service in ebs 12.2.6 with integrated SOA gateway. I can able to deploy the wsdl file but I can’t able to view wsdl files. The below error appeared when I try to open view wsdl file

“Failed to Load a composite for composite identified by default”

So I realized  the error seems to be part of SOA server. I looked the SOA server log from EM (more...)

Playing with SUBSTR and INSTR

hawk:(SYS@hawk):PRIMARY> r
  1  SELECT handle,
  2  SUBSTR(handle, INSTR(handle,'/',-1)+1)                extract_last_field,
  3  SUBSTR(handle, 1, (INSTR(handle,'/',-1,1)-1))         remove_last_field,
  4  SUBSTR(handle, 1, (INSTR(handle,'/',1,2))-1)          extract_first_field,
  5  SUBSTR(handle, INSTR(handle,'/',1,2), length(handle)) remove_first_field
  6* FROM v$backup_piece_details



hawk:(SYS@hawk):PRIMARY> r
  1  SELECT name,
  2  SUBSTR(name, INSTR(name,'/',-1)+1)              extract_last_field,
  3  SUBSTR(name, 1, (INSTR(name,'/',-1,1)-1))       remove_last_field,
  4  SUBSTR(name, 1, (INSTR(name,'/',1,2))-1)        extract_first_field,
  5  SUBSTR(name, INSTR(name,'/',1,2),  (more...)

Improving performance of top query

I’m on call this week. Here are the steps that I took to speed up a query today.

First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before the latest release and I found a similar top query.

I got a plan for both the new and old top queries – they both use the same plan which makes me think that the (more...)

Building Classrooms in the Cloud

Jumpbox Lab Server

Let’s face it: education without interaction is about as effective as shouting origami instructions at a lumberjack who is cutting down trees. Sure, your informative lessons will come in handy when the product of their work finally becomes paper, but it will be long forgotten and ultimately worthless by then. The only way a student is going to learn is if they can put (more...)

Executing Queries from the Application

Databases are designed to hold data and retrieve it, so they are optimized to run queries. Over the years I’ve seen quite a few cases where the developers did things on the application side that could be easily done on the database side, and almost every time moving it to the database improved the performance. … Continue reading Executing Queries from the Application

Creating a RAC cluster using Ansible (part 1)

In my previous blog post i explained how Vagrant can be used to create a 2-node VM environment for deploying a RAC cluster on it. In this post i want to dive into how to actually do the RAC install using ansible so you can easily create a RAC test environment on eg. your laptop.

I have created a git repo with the files that i am using for this blog post, this git repo (more...)

Creating Oracle Big Data Lite VM on Proxmox

The Oracle Big Data Lite VM available on Oracle technet, provides a pre built environment for learning about a number of key Oracle products, including Oracle 12c database, Big Data Discovery and Data integrator as well as Cloudera Distribution – Apache Hadoop (CDH 5.8.0).

The download ultimately delivers an OVA “appliance” file for use with Oracle VirtualBox, but there isn’t anything to stop you running this as a VM on proxmox 4, (more...)

Performing a large correlated update

We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal:

  • Serial update SQL
  • Parallel DML update SQL
  • PL/SQL batching (as long as sensible restart-after-error logic is readily available)

all of which will achieve the required outcome but they have a significant redo and undo cost associated with them.  Also, depending on the (more...)

Cloud Control 13c Release 2 (13cR2) : It’s Alive!

o-enterprisemgr-13c-clr-2769481I delayed our upgrade from Cloud Control 13cR1 to 13cR2 until I finished all my travelling. It’s not really fair to make a major change then swan off round the world. 🙂

Yesterday was the day. I got in and started the upgrade, following the process I documented here and it worked. 🙂


I guess I shouldn’t be surprised. I had tested it all at home anyway.

We now have Cloud Control 13cR2 monitoring all out Dev and (more...)

Redo a blogpost

This blogpost is about the Oracle redo log structures and redo efficiency in modern Oracle databases. Actually, a lot of subtle things changed surrounding redo (starting from Oracle 10 actually) which have gone fairly unnoticed. One thing the changes have gone unnoticed for is the Oracle documentation, the description of redo in it is an accurate description for Oracle 9, not how it is working in Oracle 10 or today in Oracle 12.1.0. (more...)

Oracle ACE Program : Some more opinions

I’ve written about the Oracle ACE Program a number of times over the years, including the following.

I’ve also recorded a video on the subject.

Some things have come up in conversation a few times recently that lead me to believe there are some misunderstandings about what the label “ACE” means and (more...)

OGG Activity Logging Tracing (Doc ID 1204284.1)

I just came across MOS Doc for tracing OGG processes.

Just thought I would compare the old versus new.

You can find comparison and my preference here

Optimizer Compatibility, Short Pants and Hot Sauce

OK, so I’m all over the map, (technology wise) right now.  One day I’m working with data masking on Oracle, the next it’s SQL Server or MySQL, and the next its DB2.  After almost six months of this, the chaos of feeling like a fast food drive thru with 20 lanes open at all times is starting to make sense and my brain is starting to find efficient (more...)

“That’s Not What I Wanted.”

"Let me try once more," Milo said in an effort to explain. "In other words--"
"You mean you have other words?" cried the bird happily. "Well, by all means, 
use them. You're certainly not doing very well with the ones you have now."
-- Norton Juster, The Phantom Tollbooth

A recent question in the Oracle database forums has a user wanting to open his physical standby database in READ WRITE mode but still (more...)

Characterset the email revenge

Now if you've read a few of my blog posts over the past few years you'll know i have an ongoing hate/hate relationship with anything to do with charactersets.

Things had been reasonable quiet recently until today where i got the all too familiar call about 'why does my data look funny?'

This time however was a little different to normal and actually it is maybe not really Oracle's fault although we use oracle (more...)

Distributed Trap

Here’s an interesting (and potentially very useful) observation from an OTN database forum thread that appeared at the end of last week. It concerns the problems of pulling data from remote systems, and I’ll start by building some data:

rem     Script:         remote_insert_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2016
rem     Last tested

create table t1
with generator as (

“If You Change This, Right Here …”

"changes are so frightening."
-- Norton Juster, The Phantom Tollbooth

It isn’t unusual for Oracle to expand functionality with each new release; a short list includes AWR, ADDM, the SYSAUX tablespace, Flashback Query, Flashback Database and Edition-based Redefinition. Oracle 12.1 is no different as it provides an interesting performance enhancement, the in-memory column store, that can increase performance by reducing physical I/O load by making tables memory-resident (to simplify the description somewhat). Inn (more...)

Initialization Parameters Set at the PDB Level Are Not Always Honored

Before describing the issue that lead to this post, let’s shortly review how the handling of initialization parameters works in a multitenant environment.

  • Initialization parameters exist at both the CDB level and the PDB level. This is a critical feature because it wouldn’t be acceptable to use the same set of initialization parameters for all PDB (of course, except if you have only one). For example, the initialization parameters used by the query optimizer primarily (more...)


Change is probably one of the few constants you can expect in our industry (if that’s not a contradiction in terms!), and one of the biggest changes I have made in my career is taking place right now. I’m moving on – what that means for the future remains unclear at this point in time.

I’ve really enjoyed all the things I did over the past 22 years (well, most of the time anyway! (more...)