Indexing Null Values – Part 2

In the previous post I've demonstrated that Oracle has some problems to make efficient use of B*Tree indexes if an IS NULL condition is followed by IN / OR predicates also covered by the same index - the predicates following are not used to navigate the index structure efficiently but are applied as filters on all index entries identified by the IS NULL.

In this part I'll show what results I got when repeating the (more...)

Indexing Null Values – Part 1

Indexing null values in Oracle is something that has been written about a lot in the past already. Nowadays it should be common knowledge that Oracle B*Tree indexes don't index entries that are entirely null, but it's possible to include null values in B*Tree indexes when combining them with something guaranteed to be non-null, be it another column or simply a constant expression.

Jonathan Lewis not too long ago published a note that showed an (more...)

Compression Restrictions – Update: Wide Table Compression In 12.1 And Later

I do have a very old post that used to be rather popular here that listed various restrictions related to compression. One of the most obvious restrictions in older versions was that the basic / OLTP (Advanced Row) heap table compression based on symbol tables / de-duplication was limited to tables with 254 columns or less - or probably more general to rows with single row pieces.

This limitation was lifted with Oracle 12c which (more...)

I/O Benchmark Minor Update

I've recently published a new version 1.03 of the I/O benchmark scripts on my github repository (ideally pick the IO_BENCHMARK.ZIP containing all the scripts). The original post including some more instructions can be found here, and there is also a video on my Youtube channel explaining how to use the benchmark scripts.

The main change is a new version of the "Write IOPS" benchmark that should scale much better than the (more...)

Bloom Filter Efficiency And Cardinality Estimates

I've recently came across an interesting observation I've not seen documented yet, so I'm publishing a simple example here to demonstrate the issue.

In principle it looks like that the efficiency of Bloom Filter operations are dependent on the cardinality estimates. This means that in particular cardinality under-estimates of the optimizer can make a dramatic difference how efficient a corresponding Bloom Filter operation based on such a cardinality estimate will work at runtime. Since Bloom (more...)

Chinar Aliyev’s Blog

Chinar Aliyev has recently started to pick up on several of my blog posts regarding Parallel Execution and the corresponding new features introduced in Oracle 12c.

It is good to see that obviously Oracle has since then improved some of these and added new ones as well.

Here are some links to the corresponding posts:

New automatic Parallel Outer Join Null Handling in 18c

Improvements regarding automatic parallel distribution skew handling in 18c

Chinar has (more...)

Speaking At DOAG 2018 Conference And IT Tage 2018

I will be speaking at the yearly DOAG conference in December as well as at the IT Tage in November. My talk will be "Oracle Optimizer System Statistics Update 2018" where I summarize the history and current state of affairs regarding System Statistics and I/O calibration in recent Oracle versions like 12c and 18c.

Speaking At DOAG 2018 Exa & Middleware Days In Frankfurt

I will be speaking at the DOAG 2018 Exa & Middleware Days in Frankfurt on June 18th and 19th. My talk will be "Exadata & InMemory Real World Performance" where I discuss the different performance improvements you can expect from the super fast scans delivered by those technologies depending on the actual work profile of the SQL and data used.

Hope to see you there!

No Asynchronous I/O When Using Shared Server (Also Known As MTS)

I've recently had a case at a client where it was questioned why a particular application was seemingly not making full use of the available I/O capabilities - in comparison to other databases / applications using similar storage.

Basically it ended up in a kind of finger pointing between the application vendor and the IT DBA / storage admins, one side saying that the infrastructure used offers insufficient I/O capabilities (since the most important application (more...)

Oracle Database Physical I/O IOPS And Throughput Benchmark

General Information

I've used a similar set of scripts quite a few times now to provide feedback to customers that wanted to get a more realistic picture of the I/O capabilities from a database perspective, rather than relying on artificial benchmarks performed outside - or even inside, think of I/O calibration - the database. Although there are already several Oracle benchmark toolkits available, like Swingbench and in particular SLOB, that run inside the database I (more...)

DOAG 2017 and IT Tage 2017 Presentation Material

First of all Happy New Year to everyone and I want to thank the numerous people that attended my sessions "Calamities With Cardinalities" at DOAG 2017 and IT Tage 2017 last November and December.

You can find the slide deck here at slideshare.net.

Stay tuned for more publications this year - at least I have some interesting stuff upcoming.

DOAG Red Stack Magazin Artikelreihe "Oracle Database Cloud Performance" (German)

In der aktuellen Ausgabe des für Mitglieder der DOAG, SOUG und AOUG kostenlosen DOAG Red Stack Magazins wurde der erste Teil meiner zweiteiligen Artikelserie "Oracle Database Cloud Performance" veröffentlicht.

Die Artikelserie basiert auf den hier bereits publizierten Erkenntnissen in diesem Bereich und führt diese weiter fort.

Der erste Teil geht auf die verschiedenen Aspekte der maximal erreichbare Performance ein (CPU, Storage etc.), der zweite Teil wird in der nächsten Ausgabe zu lesen sein und (more...)

New workshop "Exadata For Developers"

Just a short note that I've developed a new two day course that covers all relevant features that a database application developer should know when dealing with the Oracle Exadata Database Machine platform.

It covers in detail Smart Scans, the Exadata Flash Cache, Hybrid Columnar Compression and all surrounding features like Storage Indexes, (serial) direct path reads etc. etc.. Of course it also includes features that were added in 12c, like Attribute Clustering and Zone (more...)

Oracle Database Cloud (DBaaS) Performance – Part 4 – Network

In the last part of this installment I'll have a brief look at the network performance measured in the Oracle DBaaS environment, in particular the network interface that gets used as private interconnect in case of RAC configuration. The network performance could also be relevant when evaluating how to transfer data to the cloud database.

I've used the freely available "iperf" tool to measure the network bandwidth and got the following results:

[root@test12102rac2 ~]# iperf3 (more...)

Oracle Database Cloud (DBaaS) Performance – Part 3 – Storage – 12.2 Update

Recently I repeated the I/O related tests on a 12.2.0.1 instance for curiosity and was surprised by the fact that I consistently got significantly better results as on 11.2.0.4 and 12.1.0.2.

Now you're probably aware that the version 12.2 so far is "cloud-only", so I can't tell / test whether the version 12.2 is generically providing that increased performance or whether Oracle has optimized (more...)

Oracle Database Cloud (DBaaS) Performance – Part 2 – Storage

In this second part of this installment I'll focus on the performance figures related to I/O encountered when the corresponding tests were performed on the platform.

IOPS

When running with minimum sized buffer cache, direct and asynchronous I/O enabled, the following average read-only IOPS figures were measured over a period of several days (this is the test described in part three of the "performance consistency" series) .

First, running on a 4 OCPU single instance configuration (more...)

Oracle Parallel Execution Deep Dive Session

Here is a recording of a session I did a while ago, covering how to understand the essentials of Oracle Parallel Execution and how to read the corresponding execution plans.


12c Adaptive Joins Plus Statistics Feedback For Joins Cardinality Estimate Bug

I've encountered a bug at several clients that upgraded to Oracle 12c - 12.1.0.2 - that requires the combination of several new adaptive features introduced with Oracle 12c.It needs an execution plan that makes use of adaptive join methods, plus at runtime the activation of the new "statistics feedback for joins" feature that was also introduced with Oracle 12c. Note that in 11.2 there was already the "cardinality feedback" (more...)

Oracle Database Cloud (DBaaS) Performance – Part 1 – CPU

After having looked at the performance consistency provided by the Oracle Database Cloud offering in the previous series, I'll focus here on the raw performance figures I've measured during my tests, starting with the CPU related performance findings.

One of the first surprises is related to the fact that Oracle uses a unit called "OCPU" to specify the CPU capacity provided, which is explained here:

So one "OCPU" is supposed to represent one core (more...)

DOAG.tv Interviews (German)

In den letzten Wochen sind zwei Interviews veröffentlicht worden, die die DOAG mit mir im Rahmen der jährlichen DOAG Konferenz in Nürnberg durchgeführt hat.

Das erste stammt noch von der DOAG Konferenz 2015 und bezieht sich auf meinen damaligen Vortrag über die neuen Parallel Execution Features von Oracle 12c:

DOAG.tv Interview 2015

Das zweite ist von der diesjährigen DOAG Konferenz und bezieht sich auf meine Performance-Tests der Oracle Database Cloud und dem dazugehörigen Vortrag:

(more...)