Top 60 Oracle Blogs

Recent comments


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?)

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.

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”.

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):

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

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

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=

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=

12.2 Introduction to Real-Time Materialized Views (The View)

Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which […]


If you’re familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this feature:

  • It’s now official – the feature had been copied from package dbms_sqldiag_internal to package dbms_sqldiag.
  • The limitation of 500 characters has been removed from the hint text – it’s now a CLOB column.

H/T to Nigel Bayliss for including this detail in his presentation to the UKOUG last week, and pointing out that it’s also available for Standard Edition.