SUM is better than DISTINCT

There is a good chance that (based on this blog post title) that you’re expecting a post on SQL, and that’s understandable. But I’ll come clean nice and early – that was just to lure you in Smile 

The post is about SUM and DISTINCT, but not in the technical sense.

A few days ago, fellow OakTable member Jonathan Lewis put a post on his blog: where he is launching a mechanism (more...)

Question: Table names singular or plural?

Yesterday I put a question on twitter, to find out what others prefer: table names using singular or plural form. I expected one or two replies, not an avalanche :-)

As expected there were all kind of replies going either singular or plural. Some (more...)

_small_table_threshold=1000000 results in > 5x query speedup

Today I sped a query up by over 5 times by setting _small_table_threshold=1000000.

Here is the query elapsed time and a piece of the plan showing its behavior before setting the parameter:

Elapsed: 00:28:41.67

| Id  | Operation                                              | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
|  69 |              PARTITION RANGE ITERATOR                  |                        |   9125 |      1 |   9122 |00:13:02. (more...)

Apex Interactive Grid and IOT’s

I love the interactive grid in Application Express.  And here’s why… (Warning: Ranting mode is now on Smile)

You can tell people

  • here’s an application built with almost no code, in fact, you probably could have built it yourself
  • it’s multi-user, with optimistic locking built in for you
  • it’s secure
  • it’s backed up and recoverable,
  • it’s scales ridiculously well,
  • it doesn’t need any complicated middle tier, or software libraries,
  • it can be accessed anywhere (more...)

toad and oracle client are getting ora-12560 tns protocol adapter error after upgraded windows 10

Yesterday morning I came to office, One of my colleague complaint, My toad not working , I am getting “ora-12560 tns protocol adapter error” while connecting to target database through toad.

I asked him when was this issue started , He said, After upgraded my pc to windows 10. I am getting this issue. I told him, My pc also upgraded to windows 10 but I am not getting this issue. I can able to (more...)

Jetpack 4.8 outage

I just got my blog back online. It seems that an automatic update of Jetpack to version 4.8 took it down last night. I thought I would post a quick comment on how I resolved it because it is slightly different from what the Jetpack support site says.

When I tried to get into my blog today I just got a white screen saying there was a problem. I could not get (more...)

Optimizer Bugs and Issues

I wrote before about optimizer changes after upgrades, so this is a quick post about some more information related to this topic. As I wrote in the post I mentioned above, most of the issues we run into after upgrading are performance related. I guess this is the case because it’s one of the most … Continue reading Optimizer Bugs and Issues

Information Technology Jobs Posted

My company, US Foods, has posted a number of information technology jobs that are in Chicago (Rosemont)  or Phoenix (Tempe). Here is the web site:

Enter Information Technology as the Job Field to see all the posted IT jobs.


Short Best Practices Presentation for Oracle Partner Event

The book’s authors presented a brief session on the best practices at a packed Oracle Partner event. The following presentation – shows the material that was presented. Some of these points are expressed in the book already, others are our Definitive Tips and Guides posts.

The post Short Best Practices Presentation for Oracle Partner Event appeared first on Implementing Oracle Integration Cloud Service.

Apex patch for 5.1

If you are running Application Express, there is a new patch available.  Lots of fixes which you can read about here

I just downloaded patch 25341386 and followed the installation instructions and it went through with no problems at all in just a few minutes.

Verifying Application Express version...
...have version

PL/SQL procedure successfully completed.

Verifying Application Express version...
...have version (more...)

Using Delphix to support Oracle upgrade

I’m working on upgrading a very buggy unpatched Oracle database to a fully patched version. I’m using Delphix to support the upgrade and it has been a big help so far. This is on the HP-UX 11.31 Itanium platform.

The great thing about using Delphix to support an upgrade is that my very first pass through the upgrade scripts was with a full-sized clone of production. In (more...)

End of an era …

Four years ago I wrote about a little volunteer project that my partner did.  A small association that provided outdoor experiences and facilities for kids with physical impairments needed a system to record member and volunteer details, plus a few other bits and pieces.  We built an Apex solution running on XE.  This week, they became part of a larger government initiative, and thus their Apex application was no longer needed and (more...)

benchmarking your disks


While at Delphix, we did a lot of storage benchmarking. The I/O response times of Delphix depends, as one would logically imagine, heavily on the underlying disks. Sure Delphix can cache a lot ( with 1 TB of ram and 3x compression that’s 3TB and that 3TB can be shared by 10 or a 100 copies being the equivalent to 30TB or 300TB of databases) but really there will always be important I/O coming (more...)

Should you learn Spring or Oracle ADF?

My answer on Quora to “Should I learn Spring or Oracle ADF?”:

Oracle ADF is a specialty skill, Java Spring is a general skill. Java Spring should be your default choice, unless:

  1. You work in an company that has made Oracle ADF their strategic choice, or
  2. You come from a 4GL tool like Oracle Forms and don’t know much Java yet

With ADF, you get higher productivity but less control. It was created by (more...)

12c Release 2, set feedback enhancement in SQL PLus

There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example

$ sqlplus hr/hr

SQL*Plus: Release Production on Tue Mar 14 22:59:15 2017


Speaking at DOAG and RMOUG rocked

As I wrote a while back I was accepted to speak both at the User Group Leader summit at DOAG16 and at RMOUG Training Days.

The first one was a short presentation where I talked about a large bug in Oracle security and the need to patch and upgrade to not have that exposure. It was great fun as it was limited to a four-minute talk. I learned a lot from preparing for it as (more...)

Little things worth knowing: Executing RDA on RAC

Result! I have finally been able to gather a complete RDA (Oracle Remote Diagnostic Agent) output on my 2 node RAC system. After consulting the relevant documentation on MOS-which is spread over at least 42 Doc IDs-I found them not to be very helpful to the degree that some of what I read is actually wrong or contradicting. I put together a short note, primarily to myself, to remind me of the process. I hope you find it useful, (more...)

Remove OEM 13c Management Repository


Removing the SYSMAN schema and other OEM-related objects from a database consisted of disabling constraints and running a series of DDL scripts in earlier versions of OEM.  Always use RepManager to perform this cleanup for OEM 13c.

RepManager ‘-drop’ command drops the SYSMAN, SYSMAN_MDS, SYSMAN_OPSS, SYSMAN_RO, and SYSMAN_BIPLATFORM schemas and removes their artifacts, including tablespaces and datafiles.


Note:  The following run-time example has been simplified from actual stdout and uses a reference to $MW_HOME (more...)

Best of Both Worlds with ADF

I’m seeing a lot of interest in developing client-side applications based on one JavaScript framework or another. Today, the leading contenders are AngularJS (the old version) and Angular2 (the new version, of course, incompatible with the old). Oracle is also entering the game with Oracle JET.

For some strange reason, developers tend to compare Javascript frameworks to full stack frameworks like ADF, forgetting that someone, somewhere, must build the REST web services that JavaScript applications (more...)

Get your virtual mojo on

I have for a few years created a play environment in a similar way. Getting a database installed in a VM is not too hard. But I found that I did it fairly often and not always in the same way and not always without having to resolve problems I know I had solved before but since forgotten the solution for.

Right, write everything down. Yes, it was time for that.

So I now have (more...)