Search

Top 60 Oracle Blogs

Recent comments

April 2014

OUGN : Day 1

The journey to Norway was pretty straight forward, but during the second flight, from Amsterdam to OSLO, my nose and eyes started to stream. I didn’t feel ill, but I was starting to worry I might be getting ill right before a conference. I landed in Norway, got the train to the centre of OSLO and walked to my hotel. I was meant to go out to dinner, but I figured bed might be a better option…

The next day we met up and headed off to the boat to begin the conference. After boarding, we went to the keynotes. Since these were in Norwegian, a few of use ended up at the back of the room chatting. :) As soon as I got access to my room I headed on up to check it out. I’ve been on one of these ferry/cruise ships before and I think they are kind of cute.

Analysing Parallel Execution Skew - Data Flow Operations (DFOs) And DFO Trees

This is the second part of the video tutorial "Analysing Parallel Execution Skew". In this part I introduce the concept of "Data Flow Operations (DFOs)" and "DFO Trees", which is what a Parallel Execution plan is made of. DFOs / DFO Trees are specific to Parallel Execution and don't have any counterpart in a serial execution plan.

Understanding the implications of DFOs / DFO Trees is important as prerequisite for understanding some of the effects shown in the later parts of the video tutorial, hence I covered this as a separate topic.

Note that this tutorial also demonstrates some new 12c features regarding Parallel Execution, in particular how Oracle 12c now lifts many of the previous limitations that lead to the generation of multiple DFO Trees.

Here is the video:

Using EMCLI (Enterprise Manager Command Line Interface)

I received an email on a distribution list I’m on from someone that was looking at scripting a bunch of stuff that can be done via the Enterprise Manager 12c user interface. The writer had looked at the Enterprise Manager Command Line Interface (EMCLI) documentation, and was asking for additional materials, blogs etc. that might […]

Rose-Marie Davidson passed away March 31, 2014

Rose-Marie Davidson, 87, passed away Monday, March 31, 2014 at Manatee Memorial Hospital in Bradenton, FL. She was born April 30, 1926 in New Rochelle, NY, the daughter of Ernest H. and Rose (Bormann) Vogel. She grew up in Larchmont, NY, Fairfield, CT and Fort Wayne, IN, where she graduated from South Side High School. She attended Indiana University and was a member of Alpha Chi sorority.  She received her BFA from Cranbrook Academy of Art in Bloomfield Hills, MI.

 

At Cranbrook she met her future husband, Robert Edwin Davidson, and they were married December 20, 1947. Robert’s career as a painter and art professor took them to Oregon, Canada, western New York and Mexico, and they raised four children along the way. In 1975 they moved to Redington Beach, FL, so that Rose-Marie could care for her elderly mother. After her husband’s death in 2000, Rose-Marie lived in Madeira Beach and Seminole before moving to her son’s home in Palmetto in 2006.

 

NVL() change

One of the problems of functions is that the optimizer generally doesn’t have any idea on how a predicate based on function(col) might affect the cardinality. However,  the optimizer group are constantly refining the algorithms to cover an increasing number of special cases more accurately. This is a good thing, of course – but it does mean that you might be unlucky on an upgrade where a better cardinality estimate leads to a less efficient execution plan. Consider for example the simple query (where d1 is column of type date):

select	*
from	t1
where	nvl(d1,to_date('01-01-1900','dd-mm-yyyy')) < sysdate

Now, there are many cases in many versions of Oracle, where the optimizer will appear to calculate the cardinality of

nvl(columnX,{constant}) operator {constant}

as if it were:

NVL() change

One of the problems of functions is that the optimizer generally doesn’t have any idea on how a predicate based on function(col) might affect the cardinality. However,  the optimizer group are constantly refining the algorithms to cover an increasing number of special cases more accurately. This is a good thing, of course – but it does mean that you might be unlucky on an upgrade where a better cardinality estimate leads to a less efficient execution plan. Consider for example the simple query (where d1 is column of type date):

select	*
from	t1
where	nvl(d1,to_date('01-01-1900','dd-mm-yyyy')) < sysdate

Now, there are many cases in many versions of Oracle, where the optimizer will appear to calculate the cardinality of

nvl(columnX,{constant}) operator {constant}

as if it were:

Cache anomaly

Just a quick heads-up for anyone who likes to play around with the Keep and Recycle caches.

In 11g Oracle introduced the option for serial direct path reads for tablescans on tables that was sufficiently large – which meant more than the small_table_threshold – provided the table wasn’t already sufficient well cached.  (The rules mean that the choice of mechanism can appear to be a little random in the production environment for tables that are near the threshold size – but if you try testing by doing “alter system flush buffer_cache” you find that you always get direct path reads in testing.)

Cache anomaly

Just a quick heads-up for anyone who likes to play around with the Keep and Recycle caches.

In 11g Oracle introduced the option for serial direct path reads for tablescans on tables that was sufficiently large – which meant more than the small_table_threshold – provided the table wasn’t already sufficient well cached.  (The rules mean that the choice of mechanism can appear to be a little random in the production environment for tables that are near the threshold size – but if you try testing by doing “alter system flush buffer_cache” you find that you always get direct path reads in testing.)

Easy – Oops.

A question came up on OTN today asking for suggestions on how to enforce uniqueness on a pair of columns only when the second column was not null. There’s an easy and obvious solution – but I decided to clone the OP’s example and check that I’d typed my definition up before posting it; and the result came as a bit of a surprise. Here’s a demo script (not using the OP’s table):

Easy – Oops.

A question came up on OTN today asking for suggestions on how to enforce uniqueness on a pair of columns only when the second column was not null. There’s an easy and obvious solution – but I decided to clone the OP’s example and check that I’d typed my definition up before posting it; and the result came as a bit of a surprise. Here’s a demo script (not using the OP’s table):