Network considerations on an ODA X5-2

When you buy an Oracle Database Appliance (ODA) X5-2 off the shelve, you will get a machine with four times 10Gb copper Ethernet (bonded into two interfaces) for public communication and two 40Gb InfiniBand (bonded into one interface) for interconnect communication between the two ODA_BASE’s. You would think that should be more than enough. Well in most cases it is. The interconnect running through InfiniBand is stunning for RAC and the public interface should be (more...)

Granular detail from a summary

We had an interesting question on AskTom a few days ago.  Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period.  Now it is perhaps a little dubious to “invent” detailed data out of summarised data, but we can come up with a reasonable algorithm for doing so.

We could simply divide each (more...)

Plan Shapes

There are a number of articles, webinars, and blogs online about how to read execution plans, but many of them seem to stop after the the minimum description of the simplest type of plan, so I thought I’d throw out a brief comment on a couple the slightly more complicated things that are likely to appear fairly commonly because you sometimes find plans with very similar shapes but extremely different interpretation.

First: select with scalar (more...)

LOGGING and temporary space

We had an interesting question on AskTom this week.  The poster had been told by their DBA that the reason their large INSERT-AS_SELECT statement was consuming lots of temporary segment space, was because the database had been recently altered to enable FORCE LOGGING, presumably to ensure easier consistency in a physical standby node.

So … here’s a simple test case to demonstrate that this assertion is wrong.

First we build up table, and then (more...)

Graph frequently executed SQL by FORCE_MATCHING_SIGNATURE

I made a new graph in my PythonDBAGraphs program. Here is an example with real data but the database name blanked out:


My graphs are all sized for 1920 x 1080 monitors so I can see all the detail in the lines using my entire screen. The idea for this graph is to show how the performance of the queries that matter to the users changes as we add more load and data to this (more...)

SELinux blocked my .Xauthority

I was attempting to install an OEM management server on a new host in the lab using runInstaller.  Of course the installer is an X-windows app so I need to configure port forwarding to get the display back to MacBook.

I added the new host and its bastion to my ~/.ssh/config file to set up port forwarding:

ConnectTimeout 60
StrictHostKeyChecking ask
ProxyCommand none
UserKnownHostsFile ~/.ssh/known_Hosts
User oracle

Host newlaboms. (more...)

Subtleties – Part 3 (more workarounds for COLLECT DISTINCT in PL/SQL)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL, and saw two workarounds – using dynamic SQL and using the SET function.
In Part 2 we saw that the SET function can operate on Nested Table but not on Varray.
In this post we’ll see two more workarounds.

Inline View

We can first remove the duplicates in an inline view, and then use the (more...)

AWR analysis: another case study

A few weeks ago, I received a request to review an AWR report for a database suffering from instance-level performance issues. Here are the the key parts of that report (with some masking):


DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
XXXX           XXXXX     XXXXX               1  NO  XXXX

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------

Providing Oracle Stream Analytics 12c environment using Docker

The past 2 days I spent some time to upgrade the docker support I have created for Oracle Stream Explorer to work for Oracle Stream Analytics (which is the new Oracle Stream Explorer).

I guess Docker I don’t have to present anymore, it’s so common today!


You can find the corresponding docker project on my GitHub:

Due to the Oracle licensing agreement, the Oracle software itself can not be provided in (more...)

Moving to Seattle! Our beautiful rental available in San Francisco


More news coming, but for now, we (family and I) are moving to Seattle! We will be giving up our gorgeous rental house in San Francisco thus it will be available to the next lucky family.

Elegant mediterranean style family home on a quiet tree lined street in a sweet residential neighborhood just minutes from downtown and the Peninsula. 

Photos of house


Perfect location for commuting to Peninsula on 280  or downtown SF (more...)

Caching with a ServiceWorker

Next up, caching..

This is the second part of a three part blog series. You can find the first part here. In this part, we will talk about caching.

Ok, so we’ve seen:

  • How to register a ServiceWorker
  • What you can do to intercept an HTTP request
  • How to answer a request from within the ServiceWorker.

This is nice, but you don’t want to copy every resource into the source code of your ServiceWorker.
So (more...)

Parsing … no big deal eh ?

Most of us have probably seen the standard demo when it comes to emphasizing the need for sharable SQL, aka, using bind variables where appropriate.  The demo traditionally compares two similar scripts, where one of them generates a lot of SQL statements with literals, and the other recasts the same script with bind variables for dramatic improvement.

Here’s a simple version I’ve whipped up:

SQL> create table t ( x int primary key) organization  (more...)


A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset.

Here’s a simple example showing the syntax

SQL> select *
  2  from t
  3  order by 1
  4  fetch first 8 rows only;


8  (more...)

AMIS25 Beyond the Horizon Conference – Session Resource Repository

amis25bthBelow you will find an overview of all the sessions that took place at the AMIS25 Beyond the Horizon conference (1-3 June 2016, Katwijk, The Netherlands) along with the associated session resources – such as the presentation slides and where applicable the source code.


Presenter(s) Title Session Resources
Aino Andriessen

Aino Andriessen

Deploy with joy: automate the build and deployment of your ADF Fusion application
Alan Arentsen

Ultimate Node.js countdown the coolest APEX examples
Alex (more...)

Amsterdam to Zeist

I had a lazy morning, then it was time to start the journey to Zeist. It was a pretty straight forward journey from Amsterdam Central, via Utrecht to Driebergen-Zeist station. I had planned to get a bus from Driebergen-Zeist station to the hotel, but looking on the map it was only a couple of miles so I walked it. It was a little annoying dragging my suitcase, but no big drama. You can check out (more...)

OEM Preferred Connect Strings

You can specify a connection string for database targets in two ways:  fill in the blanks and let OEM create the connection string (my preference) or paste a Preferred Connect String on the bottom of the page.


Our default setting in our shop is to use multi-threaded servers. On the rare occasion when we need to change if, we paste the connect string from tnsnames.ora in place and add the SERVER=DEDICATED parameter.

Some things to (more...)

SQL statements using literals

16 years ago, someone “Ask-ed Tom” how to find those SQL statements that were not using bind variables.   You can see the question here (because we don’t delete stuff ever Smile) but I’ll paraphrase the answer below:

Tom took the following approach

  • take a copy of SQL statements in the library cache
  • create a routine that would hunt for constants in the SQL text (that is, numbers and anything within quotes) and replace them (more...)


I wrote a few years back that for single row operations, MERGE might in fact have a large overhead than the do-it-yourself approach (ie, attempt an update, if it fails, then do an insert).

Just to show that it’s always good to revisit things as versions change, here’s the same demo (scaled up now because my laptop is faster Smile)

As you can see, there is still a little difference between between the two operations. (more...)

Datatypes for DATES

Richard Foote has written a post about not using the DATE datatype for storing dates.

So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype.

Here’s a demo

SQL> create table t ( x raw(7) );

Table created.

SQL> create or replace
  2  procedure store_date(p_yyyymmddhh24miss varchar2) is
  3  begin
  4    insert into t
  5    values
  6      (
  7        hextoraw(
  8         to_char(to_number(substr(p_yyyymmddhh24miss,1,2))+100,'FM0X')||
  9         to_char(to_number(substr(p_yyyymmddhh24miss,3,2))+100,'FM0X')||
 10         to_char(to_number(substr(p_yyyymmddhh24miss,5,2)),'FM0X')||
 11          (more...)

Let me START WITH sequences

It’s always cool that you can learn stuff every single day, even on the most simple of topics.  This one came from an AskTom question.  We define a sequence to start with 100, and then alter it to modify the INCREMENT BY.

SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;

Sequence created.

SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     :  (more...)