Jonathan Lewis's picture


In the spirit of Cary Millsap’s comment: “The fastest way to do anything is to not do it at all”, here’s my take (possibly not an original one) on solving problems:

“The best time to solve a problem is before it has happened.”

I spend quite a lot of my “non-contact” time thinking about boundary cases, feature collisions, contention issues, and any other things that could go wrong when you start to implement real systems with (new) Oracle features. The benefit of doing this, of course, is that when I’m looking at a client’s system I can often solve problems because I recognise symptoms that I’ve previously created “in the lab”. The strange thing about this is that there have been times when I’ve pushed Oracle to a breaking point, documented it, and then dismissed the threat because “no one would do that in real life” only to find that someone has done it in real life.

connor_mc_d's picture

Identity columns in 12c … just a sequence ?

This question came to me over Twitter, so I thought I’d whip out a quick post on it



Yes, we do implement the IDENTITY column via a sequence.  (Digression – I think this is a smart thing to do.  After all, we’ve had sequences for 20 years, so we know how they work, they are mature, tested, and rock solid, so why invent something new?)

Jonathan Lewis's picture

Nested MVs

A recent client was seeing a very large redo penalty from refreshing materialized views. Unfortunately they had to be refreshed very frequently, and were being handled with a complete refresh in atomic mode – which means delete every row from every MV then re-insert every row.  The total redo was running at about 5GB per hour, which wasn’t a problem for throughput, but the space for handling backup and recovery was getting a bit extreme.

The requirement consisted of two MVs which extracted and aggregated row and column subsets in two different ways from a single table; then two MVs that aggregated one of the first MVs in two different ways; then two MVs which each joined one of the first level MVs to one of the scond level MVs.

Jonathan Lewis's picture

ASSM argh!

Here’s a problem with ASSM that used to exist in older versions of Oracle had disappeared by and then re-appeared in – disappearing again by It showed up on MoS a few days ago under the heading: “Insert is running long with more waits on db file sequential read”.

Jonathan Lewis's picture

12c Parse

Following on from a comment to a recent posting of mine about “bad” SQL ending up in the shared pool and the specific detail that too much bad SQL could cause contention problems while staying virtually invisible, there’s a related note today on the ODC (formerly OTN) forum of a little change in 12.2 that alerts you to the problem.

Try executing the following anonymous block (on a non-production system):

Kamil Stawiarski's picture

Direct path insert and IOTs

(Please tell me that I’m not the only one who thinks "Index Organized Table" instead of "Internet Of Things" when hearing IOT…)

This post is inspired by Connor McDonald and his blog post from a year ago about direct mode operations and IOTs.
You can read it here: https://connor-mcdonald.com/2016/07/04/direct-mode-operations-on-iots/amp/

While writing a redo parser for V00D00 I had to investigate this subject very closely from a redo log perspective. And this will be the subject of my 10-minute lightning talk at Oak Table World 2017 at Oracle Open World!

rshamsud's picture

Oracle Open World 2017 presentation

I will be presenting about ASM internals in Oracle Open World 2017 conference on Sunday October 1st. Following are the details:

Session ID: SUN5682

Session Title: Oracle Automatic Storage Management and Internals

Room: Moscone South – Room 155 Date: 10/01/17
Start Time: 12:45:00 PM
End Time: 01:30:00 PM

See you there!

Update: Added the presentation file. ASM_internals_Riyaj_OOW2017

Kamil Stawiarski's picture

rollback internals

While researching redo log internals for V00D00 we had to face the fact, that we know shit about real transactional behavior. When I say "real", I mean – under the hood.
Even with a very simple stuff like COMMIT and ROLLBACK we were constantly amazed by the internal mechanisms.

Today let’s take ROLLBACK under the investigation. According to documentation:

The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction.

Cool. But what it means? First of all, you have to realize that all changes in redo logs are in a form of REDO RECORD which has its own address, known as RBA or RS_ID.

Sample RS_ID (RBA) looks like this: 0x00000a.00008c0f.006c

Kamil Stawiarski's picture

When UPDATE becomes an INSERT

During a research for VOODOO, we came across a lot of interesting stuff inside REDO.
One of my favourites is an UPDATE, becoming an INSERT </p />
    <div class=»

oakroot's picture

When UPDATE becomes an INSERT

During a research for VOODOO, we came across a lot of interesting stuff inside REDO.
One of my favourites is an UPDATE, becoming an INSERT </p />
    <div class='clearfix'></div>  </div>
<div class=

To prevent automated spam submissions leave this field empty.
Syndicate content