MERGE and IOT’s ….. unhappy bedfellows

Anyone who has used Oracle for a while will be familiar with the Parent/Child locking "issue" when it comes to tables and indexes on foreign keys.  For many years you’d hear people crying "bug" etc but thankfully most now know the reason, and accept it as sensible behaviour.

But lets take a look at a slight variation on that theme.

Lets start with a table called "LOC" which will be our parent table in (more...)

UKOUG 2014 Elections

I noticed from Debra Lilley's blog post that there are some UKOUG elections at the moment, with voting closing on 1st September 2014.

Although not an active member or supporter of UKOUG any more (at least partly because I'm based in Singapore!), I've had a pretty long association with the user group and a lot of my friends have been involved over the years, so I still take an interest in what's going on (more...)

Monitoring a Microsoft OS Failover Cluster

I know, I know-  none of you are using Microsoft Windows.  This is why I get so many questions on this topic and why there is so much interest in a white paper that no one thought I needed to write.  Well, while that pesky ol’ white paper is in review, I’m going to go onto a secondary topic of how to monitor a Microsoft Active/Passive cluster with Enterprise Manager 12c, release 4.


Clobbering grub.conf is Bad

I’m sharing this in the hope of saving someone from an unwelcome surprise.


I recent upgraded an Exadata system from to Apart from what turns out to be a known bug[1] that resulted in the patching of the InfiniBand switches “failing”, it all seemed to go without a snag. That’s until I decided to do some node failure testing…

Having forced a node (more...)

SQL Mini-challenge entry: 1 buffer for 1st execution

by Vlado Barun


This solution has buffers=1 for the first execution, and buffers=0 for the second execution (using results cache as Timo Raitalaakso demonstrated previously).


The solution uses the query rewrite feature to retrieve the rows from an index on a materialized view. Since the index has blevel=0 and leaf_blocks=1, the execution plans shows buffers=1.


1. I do NOT consider this solution appropriate for PRODUCTION environments. It is too narrow and complex (more...)

SQL Mini-challenge entry: Kim Berg Hansen v4

by Kim Berg Hansen

Anything goes that is not explicitly disallowed? Okay, then let’s get down to three buffer gets ;-)

As a DBA I grant rights to HR:

grant on commit refresh to hr

And then in HR I create some materialized views and logs:

create materialized view log on employees with rowid, commit scn
   (employee_id, job_id, first_name, last_name, department_id)
   including new values;
create materialized view log on departments with rowid, commit scn
   (department_id,  (more...)

In-memory limitation

I’ve been struggling to find time to have any interaction with the Oracle community for the last couple of months – partly due to workload, partly due to family matters and (okay, I’ll admit it) I really did have a few days’ holiday this month. So making my comeback with a bang – here’s a quick comment about the 12cR2 in-memory feature, and how it didn’t quite live up to my expectation; but it’s also a comment (more...)

Why you Need Delphix for Oracle E-Business

Dear readers,

As I mentioned in my last post, I've been working with an exciting technology called Delphix
for database and ERP applications recently.

With Delphix you can reduce the pain points involved with performing a traditional recovery operation for Oracle and other database platforms. Now when it comes to large ERP systems such as SAP and Oracle E-Business Suite, the challenge of backup and recovery and well as system and application refresh operations and (more...)

Exadata Zone Maps

Just a quick post on a new Exadata feature called Zone Maps. They’re similar to storage indexes on Exadata, but with more control (you can define the columns and how the data is refreshed for example). People have complained for years that storage indexes provided no control mechanisms, but now we have a way to exert our God given rights as DBA’s to control yet another aspect of the database. Here’s a link to the (more...)

SQL Mini-challenge entry: Timo Raitalaakso

by Timo Raitalaakso

Here is my approach to the problem. I will be using the “Anything not explicitly disallowed by the problem statement is also permissible.” approach.

One key to query performance is the correct organization of the rows according to the access path. One way to get performance is to use indexing.

create index ej_idx on hr.employees(job_id,department_id) compress 2;
create index e_idx on hr.employees(first_name,last_name,department_id);
create unique index ejh_idx on hr.employees(employee_id,department_id);

SQL Mini-challenge entry: Craig Martin v3

by Craig Martin

Blasted.. after looking at the solution from Jimmy Brock, I see that adding one more hint to use index fast full scan to get to the Jobs row gets me down to 10 buffers. Great addition! So my newest solution is:

select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id,
from dual
    left outer join jobs j
        on j.job_title = 'President'
    left outer  (more...)

OGG-00212, what a frustrating error.

Normally, I don’t mind errors when I’m working with Oracle GoldenGate (OGG); I actually like getting errors, keeps me on my toes and gives me something to solve.  Clients on the other hand do not like errors…LOL.  Solving errors in OGG is normally pretty straight forward with the help of the documentation.  Although today I can almost disagree with the docs.

Today, as I’ve been working on implementing a solution with OGG 11.1.x (more...)

Delphix An exciting new way of managing your data

Dear Readers,

 Recently I have been working with an exciting technology called Delphix.

What is Delphix you might ask? Well Delphix is a data virtualization appliance that provides you with the ability to quickly clone, refresh and provision virtualized copies of database and application environments in record time compared to traditional copy, clone and refresh operations for Oracle and SQL Server as well as for SAP and Oracle EBS application ERP systems.

 In addition we (more...)

SQL Mini-challenge entry: CTE v2

by Jimmy Brock

Modified data set “curr_job” to return anyone that currently has the job_title of ‘President’ – even if his or her name is not Steven King.

Modify data set “job_hist” to return any employee that has ever had the job_id ‘AD_PRES’.

Buffer gets are now 9.

jbrock@ORA1> set serveroutput off
jbrock@ORA1> alter session set statistics_level=all ;

jbrock@ORA1> col version for a20
jbrock@ORA1> select distinct version from product_component_version;

-------------------- (more...)

SQL Mini-challenge entry: Craig Martin v2

by Craig Martin

My original entry is here. In the comments, Kim Berg Hansen made a great point that there was a flaw in my logic where if there were no President, the correct results wouldn’t be returned. This is an excellent catch, and a perfect example of why all code should be peer reviewed! While looking back through the code to fix this, I found a way to get the buffer count down to (more...)

SQL Mini-challenge entry: Kim Berg Hansen v3

by Kim Berg Hansen

I just keep playing with this inspired by the other great entries :-)

Emmanuel Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to look at the constraints of the tables in order to see if some assumptions can safely be made that enables more efficient code – like for example that it can safely be assumed that (more...)

SQL Tuning Health Check (SQLHC)

What is SQL Tuning Health Check? The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs. It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed. The script generates an [...]

The post SQL Tuning Health Check (SQLHC) appeared first on Oracle DBA Scripts and Articles (Montreal).

Can you justify your data ?

People ask me to justify use of Delphix. I can understand. Delphix is pretty new and often, most of my friends who are DBAs respond with “I can copy a database, so what, I can do it a little faster with Delphix.” Well that’s missing the whole boat. The question won’t be why you should use Delphix but “can you  justify working without Delphix?”

I see Delphix as amazingly positioned at nexus of data (more...)

New Oracle Bug alert (Bug 19384287)

Heads up to all the folks running and above if you're using function-based indexes! There's a new Oracle bug 19384287. I'll fill you in with a complete post over at Toad World.


Oracle introduced this view in version 10. It looks like this in version 11:
SQL> desc dba_feature_usage_statistics
Name                       Null?    Type
-------------------------- -------- ------------------
DBID                       NOT NULL NUMBER
NAME                       NOT NULL VARCHAR2(64)
VERSION                    NOT NULL VARCHAR2(17)
CURRENTLY_USED                      VARCHAR2(5)
FIRST_USAGE_DATE                    DATE
LAST_USAGE_DATE                     DATE
AUX_COUNT                           NUMBER
FEATURE_INFO                        CLOB
LAST_SAMPLE_DATE                    DATE
SAMPLE_INTERVAL                     NUMBER
DESCRIPTION                         VARCHAR2(128)
As its name suggests, it allows you to see (more...)