A short video that I did at the OTN lounge at RMOUG a couple of years ago has just been posted on YouTube. It’s about the improvements that appear in histograms in 12c. I’ll move this link into a more suitable posting in the near future.


Changing OEM BI Publisher Host

Changing the name of the server or load-balancing server handling your BI Publisher workload for OEM can be done with a single EM CLI command.  The values you need to know are:

  • Protocol, either http or https
  • The host or LBS name
  • The BIP port
emcli login -username=sysman
emcli sync
emcli setup_bipublisher -protocol=https -host=demo_oms01.demo.com -port=9702 -uri=xmlpserver -force
BI Publisher "https://demo_oms01.demo.com:9702/xmlpserver" has been registered for use with Enterprise Manager and the Enterprise  (more...)

You’re On A Roll(back)

"I know one thing for certain; it is much harder to tell whether you are lost than whether 
you were lost, for, on many occasions, where you are going is exactly where you are. On 
the other hand, if you often find that where you've been is not at all where you should have 
gone, and, since it's much more difficult to find your way back from someplace you've never 
left, I suggest you go  (more...)

Oracle ASM Rebalance – Turn it up. To 11?


If you’ve ever seen or heard of the movie This is Spinal Tap then you have likely heard the phrase Turn it up to 11.

Why bring this up?

When ASM was introduced as a method for configuring storage for Oracle, one of the features was the ability to rebalance the data across all disks when disks were added or replaced.  The value used to control how aggressively Oracle rebalances the disks is the REBALANCE (more...)

Upgrading Oracle with Unknown Errors


I recently experienced a problem when upgrading an old Oracle database to that had no matches in a My Oracle Support (MOS) or Google search. The problem presented itself initially when upgrading as the following error was reported by the upgrade script:

ERROR at line 1:
ORA-20001: invalid column name or duplicate columns/column groups/expressions
in method_opt
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4


Pragma UDF – Some Current Limitations

There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions.

In my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you call a PL/SQL function from SQL.

In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds (more...)

Delphix versus Storage Snapshots

4333881004_ff0835e8cc_zphoto by Gonzalo Iza

This article lists some of the key capabilities that Delphix provides over and above Storage Snapshot based cloning solutions to meet the increasing business demand for Agile Development.

I’ve blogged about this before in

First it is useful to contrast the distinct goals and implementation behind Storage Snapshots and Delphix.


Storage Snapshots


The primary use for storage (more...)

Troubleshooting Another Complex Performance Issue – Oracle direct path inserts and SEG$ contention

Here’s an updated presentation I first delivered at Hotsos Symposium 2015.

It’s about lots of concurrent PX direct path insert ant CTAS statements that, when clashing with another bug/problem, caused various gc buffer busy waits and enq: TX – allocate ITL entry contention. This got amplified thanks to running this concurrent workload on 4 RAC nodes:

When reviewing these slides, I see there’s quite a lot that needs to be said in addition to what’s on (more...)

Just XFS Things

$ uptime

16:36:42 up 4 days, 12:28, 6 users, load average: 1029.20, 995.42, 865.77

See https://www.centos.org/forums/viewtopic.php?f=47&t=52412 and https://access.redhat.com/solutions/532663 for details. And defrag your XFS volumes.


I used to think that a user with SELECT ANY TABLE and SELECT ANY DICTIONARY could see anything in a database. I found out today that these 2 privileges do not allow you to SELECT from another user’s sequence. You can see what I mean in the example below, which I tested in an Oracle 11.1 database. First I created a user to own a sequence:
SQL> conn / as sysdba

Application Performance Monitoring- First View

Working with test data is always fun and teaches those of us inside Oracle how best to use the new products that will be offered to benefit customers.  To me, it’s pretty straight forward and I didn’t first understand why it was necessary, but as I sit in on calls as we do performance testing of these great, new products, I understand why they need to direct so many, so often.  Anything performance is as much (more...)

A new line on NEWLINE

Recently I was doing a simple external table load using a CSV file, but was getting an interesting error. My file looked simple enough

"2012348048","john@anon.com","05/02/2000","Subject 1","5","09/04/2007"
"2412348048","mike@anon.com","05/02/2000","Subject 1","5","09/16/2002"
"348543169051","sue@anon.com","03/10/2001","Subject 1","5","03/24/2008"
"348396029762","mary@anon.com","03/10/2001","Subject 1","5","03/10/2001"
"1212348047","sam@anon.com","05/02/2000","Subject 1","5","05/02/2000"
"1612348048","vincent@anon.com","05/02/2000","Subject 1,"5","06/02/2006"

So it should have been a fairly straightforward external table definition to access it

SQL> create table T
  2        ( ID int
  3         ,EMAIL varchar2(255)
  4         ,TIMESTAMP date
  5         ,SUBJECT varchar2(512)
  6          (more...)

Enterprise Manager at OOW 2015

Well, another Oracle Open World has bit the dust, and as is always the case, there was a lot of great material presented, both by Oracle employees and customers. To help you identify the material and get your hands on the presentations where possible, I’ve taken a stab at grouping all the material together into sections. These are the areas the presenters identified the material as covering, so if the title doesn’t seem to match (more...)

Direct path and buffered reads again: compressed tables.

In the previous post on the decision between buffered and direct path reads I showed the decision is depended on the version. Up to and including version the size of a segment needs to be five times small table threshold in order to be considered for direct path reads, and starting from the database starts considering direct path reads starting from small table threshold. The lower limit (more...)

NOUG Session: How Cache Fusion Works

For all those attended my session at Northeast Oracle User Group at Babson College in Wellesley, MA. Have you ever wondered how Cache Fusion knows where to get the block from? Or, how block locks vary from row locks? Or you are confused about the meaning and purpose of various Global Cache Service (GCS), Global Resource Directory (GRD) and Global Enqueue Service (GES). The session was meant to explain how all these actually work under the covers with live (more...)

Second wave of cloud migration

A second wave of cloud migration is now happening after the initial hiccups of


Reaction to Shadow IT

  • Misaligned Expectations
  • Increased Data Exposure and Administrative Surface Area of Risk
  • Choice of newer better cloud options now available after initial “wrong choice”
Reaction to Shadow IT: A significant amount of public cloud use over the past few years was done without IT oversight and approval. As companies develop their formal cloud (more...)

Data Driven or Application Driven Development?

I'm the kind of person who believes that Data is really important and needs to be treated and dealt with properly. Of course we also need Applications that use that Data - one without the other is no use at all. But a battle I often have with clients now is that Data needs to be modelled and designed at the same stage as the Application is. Ignore the data model and database design and (more...)

Wrong Results

Here’s a little gem in 12c that arrived in my email a few days ago: a query where the result depends on the SQL*Plus arraysize!

The email had a short description, and a script to create a small data set that would demonstrate the problem. I’m not going to show you the query, or the result set, but here’s a sample of the output from an SQL*Plus session after creating the data. This is, by (more...)

UK OUG Conference 2015

I went to the UK OUG Conference for the first time around 12 years ago, in 2003 if I remember correctly. I enjoyed all the presentations but the two which stuck in my mind were by Jonathan Lewis and Connor McDonald.

This year I will be chairing 8 sessions there, which means I will be introducing the speaker and making sure nothing goes wrong.

Two of these sessions are on 6th December 2015, which is (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change.(read more)