How the log writer and foreground processes work together on commit.

(warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer)

After the Trivadis Performance days I was chatting to Jonathan Lewis. I presented my Profiling the log writer and database writer presentation, in which I state the foreground (user/server) process looks at the commit SCN in order to determine if its logbuffer contents are written to disk by (more...)

Delphix Express installation

A couple of blog post ago I announced that there is now a free version of Delphix.  Here is a little more information about the installation of Delphix Express.

Delphix Express

Delphix Express is a free version of Delphix limited to 25 GB of managed storage and 1 vCPU. Delphix Express is set up for small projects and not optimized for high throughput nor performance testing.


Landshark is a set of 2 virtual machines (more...)

Lock by reference

As you may know, not indexing foreign key can have a huge impact on lock behavior and hurt database scalability (Not only with Oracle).

But there is also some rules of thumbs (coming from Asktom) when non indexing the foreign key is considered as reasonable.

So, when do you NOT necessary need to index a foreign key ?

In general when the following conditions are met:

o you do NOT delete from the parent table. (especially with (more...)

Migrate to the Oracle Database Cloud Service

On the 6th of October the Oracle Cloud Day 2015 takes place in The Netherlands. iAdvise is proud to be a bronze sponsor on what promises to be a day filled with interesting sessions.

One of the main topics will be the Oracle Database Cloud service. In this blog post, which was published in the OGh Visie earlier this year, you can read what the Oracle Database Cloud Service is, which configuration possibilities there (more...)

Oaktable World Agenda 2015

Screen Shot 2015-09-25 at 9.06.26 AM Screen Shot 2015-09-25 at 9.06.17 AM


Monday Oct 26 Tuesday Oct 27
8 Coffee 8am

Intro with Mogens 8:30

Toon Koppelaars – hash joins and Bloom filters
9 Mark W. Farnham: Minimizing the concurrency footprint of transactions with Logical Units of Work stored in PL/SQL Kyle Hailey : Virtual Data
10 Kerry Osborne – SQL Translation Framework Kellyn Pot’Vin-Gorman and Stewart Bryson AWR Warehouse Trending and Analysis with OBIEE
11 Greg Rahn: Taming JSON with SQL: From Raw to Results Marco (more...)

exitcommit … or your career down the drain

I recently posted a little video on the ‘exitcommit’ option in SQL Plus, adding a theatrical drama to present a worst case scenario of potentially destroying a Production system.  Take 3 mins to have a chuckle here.

My predecessor Natalka Roshak tweeted an interesting followup that I thought I would share, namely, if you’re a regular SQL Plus user, you should definitely take the time to check out the (more...)

Delphix Express : Free version of Delphix available

Delphix Express is available!

Delphix Express is a free version of Delphix

Delphix Express differs from Delphix enterprise in that it is limited to 25 GB of managed storage.

Screen Shot 2015-09-23 at 9.58.01 AM

To get a copy of Delphix Express go to

and put “Express” for your title and I’ll send you the download info.

Here is a video on installing Delphix Express on VMware fusion on my Mac.

Delphix Express can also (more...)

5 Years of Delphix

Wow – 5 years have flown by. Five years ago I joined Delphix and five years ago Delphix began the virtual data industry. Like all successful technologies many other vendors have come into the domain.  Delphix for me is amazing compared to other virtual data solutions. Delphix is easy, fast and powerful. Check out this video of linking to a RAC database and provisioning a thin clone RAC database. How much work would it take to do the (more...)

Connection shortcuts with a wallet

I’m lazy when I connect to the database, especially on my laptop.  Anything that saves a few keystrokes I’m keen on Smile

So rather than type “sqlplus username/password@database” I like to take advantage of a wallet

In my private sqlnet.ora, or the global one if it makes sense to do so, I add the details of wallet


This tells Oracle, that when I begins a connection to the database, we will (more...)

Row transposition

Here’s a couple of simple examples to get you started with transposing rows to columns. The problem typically presents as:

How do you convert a listing in rows (eg, where people placed in a race), into some output that is across the page ?

For example, given a running race, how do you generate output which looks like this:


So here’s our source table

SQL>  (more...)

Forcing OEM Target Detail Discovery

The Problem – Missing Target Details

I was evaluating an Information Publisher report containing data about host targets. The report is based on the sysman.mgmt$os_hw_summary view that contains all sorts of information about your hosts.  It is an excellent data source for Information Publisher reports.  Unless the data is missing.

One of my hosts didn’t appear in that view, even though the host’s console page appeared healthy.  Then I looked at the details.  Instead (more...)

OTN Tour of Latin America 2015 : It’s a Wrap!


I just realised I didn’t write a closing post for the OTN Tour of Latin America 2015, so here goes.

Here are the links to all the posts I wrote during the two weeks that related to the main body of the tour.

Managing Impala and Other Mixed Workloads on the Oracle Big Data Appliance

One of our current client projects uses Cloudera Impala to provide fast ad-hoc querying to the data we’re loading into their Oracle Big Data Appliance Hadoop environment. Impala bypasses MapReduce to provide faster queries than Hive, but to do so it does a lot of processing in-memory and runs server processes on each node in the cluster, leading in some cases to runaway queries blocking other workloads in the same way that OBIEE queries on (more...)

AZORA – Arizona Oracle User Group new location

The Arizona Oracle User Group meeting has moved tomorrow’s meeting to Oracle’s offices on Camelback road:

Meetup link with meeting details


Lets Talk DB Perth

Last week in Perth, we held a “Lets Talk DB” event.  This was a free event organised by our local Perth Oracle office (big thanks to Dennis Ward for his organisational skills), where Richard Foote, Chris Muir and myself, spent the day describing some of the Oracle innovations to DBA and Developers alike.  Chris referred to the three of us as the “Coalition of the Delisted” for we are all former Ace (more...)

The importance of the GLOBAL_STATS statistic on partitioned tables

I am currently working in a team alongside a gentleman known as Roberto Rigliaco. You are unlikely to have heard of Roberto as he is not (yet) a blogger and not (yet) known on the conference circuit.

Nevertheless, Roberto has done some excellent work recently analyzing the use of object statistics in partitioned tables. I haven’t seen his findings published anywhere else and so I am glad to say that Roberto has accepted my offer (more...)

Longer strings in 12c

Most people are now aware that in 12c, a VARCHAR2 has been extended from a maximum size of 4000 to 32767 bytes.

Similarly, there’s many a note/blog post etc about needing to set the following parameter to enable it:

SQL> alter system set max_string_size = EXTENDED scope=spfile;

System altered.

and then bounce the database. But its important note that its NOT just as simple as that. Here’s what will happen if you only do that

5 Things I wish I knew about APEX when I just started (part 4)


Ever deleted or changed something by accident in your application and wished you could get it back?

You can! The oracle database has a feature called flashback, and APEX can take advantage of this feature. (Flashback needs to be enabled on the database, if you are not sure if it is enabled on your database, you can always ask your DBA).

Today I will show you some of the different places, where you can (more...)

Speaking in Portland at NWOUG Monday Sept 14

Looking forward to speaking at

Please come join me !

I lived in Portland between 2006-2008 and love city. It will be fun being back.

8:15 – 9:00 Keynote “EBS Strategy and Roadmap” – Vanessa Paskill

Technical Apps Functional Vendor Oracle
9:15 10:15 Practical Tips for Oracle Business Intelligence Applications 11g Implementations (Michael Raney, Rittman Mead) Lessons learned while Integrating Procurement Cards with Internet Expense (Ashish Nagarkar, AST (more...)

Plan change monitor prevents user impact from bad plan

This morning a colleague of mine noticed an alert from our plan change monitor and prevented any impact on the users by forcing a SQL’s plan to its normal fast plan before the users could be affected.  I want to share the monitor script and describe how we use its output.

I know this is long but I want to paste the SQL text of the monitor script here.  Review my comments on each step. (more...)