Top 60 Oracle Blogs

Recent comments

May 2014

Subquery with OR

Prompted by a pingback on this post, followed in very short order by a related question (with a most gratifying result) on Oracle-L, I decided to write up a note about another little optimizer enhancement that appeared in 12c. Here’s a query that differs slightly from the query in the original article:

Enkitec + Accenture = Even More Awesomeness!

Enkitec is the best consulting firm for hands on implementation, running and troubleshooting your Oracle based systems, especially the engineered systems like Exadata. We have a truly awesome group of people here; many are the best in their field (just look at the list!!!).

This is why I am here.

This is also why Accenture approached us some time ago – and you may already have seen today’s announcement that Enkitec got bought!

Consider speaking at #ukoug_tech14

The call for papers is still open for UKOUG Tech 14 – a great event to speak at for sure!

UKOUG Tech 14The board explicitly encourages first-time speakers and women to submit an abstract.

Both doesn’t apply for me, but I have submitted abstracts in spite :-)

I can say only the best about the past annual conferences of the UK Oracle User Group. Great speakers, very good conditions and an excellent opportunity to get in touch with other Oracle enthusiasts.

Why did Google succeed? Yahoo shuts altavista down

#555555;" align="left">#2970a6;" href="">

Yahoo shut  #2970a6;" href="">Altavista down a couple of years ago. Why did Google succeed when Altavista had index the web before Google ever started?

Dan Rosensweig former COO at Yahoo responding to the question: Why did Google succeed in search over Yahoo?

Enterprise Manager 12c- Management Packs and Licensing Information

#444; font-family: 'Helvetica Neue',Helvetica,Arial,sans-serif; margin: 0 0 1em;">#000000;">I’m often asked what management pack is used by what feature and there is actually a very easy way to find out this information in the EM12c console.

Feature Bypass

Here’s a little tip that might be helpful occasionally when you’re trying to work out why the optimizer transformation you were expecting isn’t appearing

If you’ve ever checked the 10053 trace (and who wants to do that for a complex query) you may have noticed lines like:

SU: SU bypassed: Remote table referenced.

So now you know that SU – Subquery Unnesting – has limitations in distributed queries.

When I first saw a line like this, it crossed my mind that it would be useful to keep a reference list of features that could be reported as bypassed, which I do through a simple unix line:

strings -a oracle | grep -i bypassed > bypassed.txt

If you need a reference for the various short codes for transformations you can find it near the top of the 10053 trace, looking like this:

Date conversions

#555555;" align="left">#2970a6;" href="">

#555555;">Dates are a surprising pain for correlating data an at the same time being understandable to the human reader.

#555555;">For a generic form we can take seconds since 1970, on UNIX, with

#555555;">$ date '+%s'

#555555;">but then how to we convert it back? There is a neat trick for that, “date -d @”

#555555;">$ date -d @1311372938  +%H:%M:%S

#555555;">so in scripts we can load up the date into seconds, that can be subtracted to get deltas and at the same time converted easily to any date format :

brew install sqlplus

Gee, that didn’t work.

For those of you wondering about the title of this post, I’m referring to the brew package manager for Mac OS — a nice utility for installing Unix-like packages on Mac OS similar to how yum / apt-get can be used on Linux.

I particularly like the way brew uses /usr/local and symlinks for clean installations of software without messing up the standard Mac paths.

Unfortunately, there isn’t a brew “formula” for installing sqlplus and the instant client libraries (and probably never will be due to licensing restrictions), but we can come close using ideas from Oracle ACE Ronald Rood and his blog post Oracle Client 11gR2 ( for Apple Mac OS X (Intel).

Go there now and read up through “unzipping the files” — after that, return here and we’ll see how to simulate a brew installation.

Oracle ACE Director Self-Evaluation

I’ve just filled in my Oracle ACE Director Self-Evaluation…

The way the tour dates worked this year, I got the LAOTN 2013, Nordic 2013 and Yathra 2014 tours in a single assessment year, plus all the one-off events. It looks crazy-busy… :)

Most of my events this year have been packed into the first half of the year, so I think next years assessment will look a little sparse by comparison…




I/O wait histograms in Oracle

#555555;" align="left">#2970a6;" href="">

#555555;">DBA_HIST_EVENT_HISTOGRAM has a history of I/O latency histograms. DBA_HIST_EVENT_HISTOGRAM is only on 11g, so for 10g, it requires collecting the histogram deltas manually off of V$EVENT_HISTOGRAM, something like

set pagesize 150
col event format a25
col tm format a14
select event,
       to_char(sysdate,'J')||to_char(round(sysdate-trunc(sysdate),5)) tm,
from v$event_histogram
where event in ('log file parallel write' ,
                'db file scattered read' ,
                'db file sequential read' )
order by event;

#555555;">and collecting this information every N seconds and taking the deltas.