Top 60 Oracle Blogs

Recent comments


Conditional SQL- 6

An odd little anomaly showed up on the OTN database forum a few days ago where a query involving a table covered by Oracle Label Security (OLS) seemed to wrap itself into a non-mergeable view when written using traditional Oracle SQL, but allowed for view-merging when accessed through ANSI standard SQL. I don’t know why there’s a difference but it did prompt a thought about non-mergeable views and what I’ve previously called “conditional SQL” – namely SQL which holds a predicate that should have been tested in the client code and not passed to the database engine.

The thought was this – could the database engine decide to do a lot of redundant work if you stuck a silly predicate inside a non-mergeable view: the answer turns out to be yes. Here’s a demonstration I’ve run on 11g and 12c:

Uniquely parallel

Here’s a surprising (to me) execution plan from – parallel execution to find one row in a table using a unique scan of a unique index – produced by running the following script (data creation SQL to follow):

Merge Precision

This note is about a little detail I hadn’t noticed about the merge command until a question came up on the OTN database forum a few days ago. The question was about the impact of the clustering_factor on the optimizer’s choice of execution plan – but the example supplied in the question displayed an oddity I couldn’t explain. Here’s the code and execution plan as originally supplied:

Oracle Database Cloud (DBaaS) Performance Consistency - Part 1

As Oracle ACE Director I got an extended trial license for Oracle's Cloud offerings, in particular the "Database as a Service" offering. As part of the (ongoing) evaluation I try to get an idea how consistent the performance of such an service is, which might be one of the concerns one might have when considering cloud offerings in general.

For my tests I've set up a single instance database using "4 OCPUs" (Oracle CPUs) which ends up as an Oracle Linux 6 system showing 8 CPUs *and* 8 cores of type "Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz".

As edition for the database I've chosen the "Extreme Performance" Enterprise Edition which also shows up at the version banner (note the difference to regular database installations, not sure this might break some applications that don't know this banner):

Hotsos 2016 Recap

I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when call for papers was still open, I realized that there’s a good chance I can make it to Hotsos in 2016: I had a budget, visa is a doable thing, and most importantly I knew I had a topic to talk about which most likely will not be in competition with other speakers.

Oracle Management Cloud – Log Analytics

In this last overview post I will give you a first glance of the Oracle…

Oracle Management Cloud – IT Analytics

In this post I will give you a first glance of a demo environment of…

Oracle Management Cloud – Application Performance Monitoring

A while ago I created a first post about the Oracle Management Cloud ( #OMC

Index Usage

There are some questions about Oracle that are like the mythical Hydra – you think you’ve killed it, but for every head you cut off another two grow. The claim that “the optimizer will switch between using an index and doing a tablescan when you access more than X% of the data” re-appeared on the OTN database forum a little while ago – it doesn’t really matter what the specific value of X was – and it’s a statement that needs to be refuted very firmly because it’s more likely to cause problems than it is to help anyone understand what’s going on.


Can you spot anything that might appear to be a little surprising about this (continuous) extract from a trace file ? The example is from, but 11g and 12c could produce similar results (or direct path read equivalents):