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...)

A look into Oracle redo, part 5: the log writer writing

| Feb 27, 2018

This the the fifth blog in a series of blogposts about Oracle database redo. The previous blog looked into the ‘null write’ (kcrfw_do_null_write actually) function inside kcrfw_redo_write_driver, which does housekeeping like updating SCNs and posting processes if needed, this blog looks into what happens when the log writer is actually posted by a process or if public redo strand buffers have been written into. In part 3 of this blog series (the log writer working (more…)

Oracle Exadata Smart Flash Logging

What is Exadata Smart Flash Logging?

In an OLTP environment, it is crucial to have fast response times to redo log writes i.e. low latency.  When multiplexing redo logs for high availability i.e. to protect against hardware failure, redo log writes are only acknowledge when redo is written to all redo log members i.e when the slowest disk completes the write.  By this nature, whenever a disk slows down even (more...)

How to fix queries on DBA_FREE_SPACE that are slow

I found myself in a situation where OpsView a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.

Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:

SQL> set timing on
SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS';


Elapsed: 00:00:10.98

There are 60 tablespaces in this pluggable database, which the time varied (more...)

How to Enable Exadata Write-Back Flash Cache

Please check the following blog post “How to check if Exadata Write-Back Flash Cache is Enabled” for:

  • What is Exadata Write-Back Flash Cache?
  • What are the Performance Benefit of Exadata Write-Back Flash Cache?
  • How to check if Exadata Write-Back Flash Cache is Enabled?
  • Pre-requisites and minimum versions.

You can also get more info from My Oracle Support (MOS) note:
Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)
OTN Article: Oracle Exadata Database (more...)

How to check if Exadata Write-Back Flash Cache is Enabled

What is Exadata Write-Back Flash Cache?

Exadata Write-Back Flash Cache provide the ability to cache not only read I/Os but write I/O to the Exadata’s PCI flash on the storage cells.  Exadata storage software or higher and Grid Infrastructure and Database software or higher is required to use Exadata Write-Back Flash Cache, which is persistent across storage cell restarts.

The default since April 2017 for (more...)

Adapting and adopting SQL Plan Management (SPM)


This post is about: “Adapting and adopting SQL Plan Management (SPM) to achieve execution plan stability for sub-second queries on a high-rate OLTP mission-critical application”. In our case, such an application is implemented on top of several Oracle 12c multi tenant databases, where a consistent average execution time is more valuable than flexible execution plans. We successfully achieved plan stability implementing a simple algorithm using PL/SQL calling DBMS_SPM public APIs.

Chart below depicts a typical (more...)

Friday Fun SQL Lesson – union all

Our office kitchen is unavailable this Friday, so the call was put out for pub lunch.

After a couple of replies I decided to enter my reply, in full nerd mode.
select * from people_coming_to_lunch;


3 rows selected.
And of course one of the other SQL geeks (name redacted) replied to extend the data set.
select * from people_coming_to_lunch
select 'Shanequa'
from dual;
And I couldn't help myself. I (more...)

Adaptive Query Performance Fixes for #DB12c Release 1

We all know adaptive query feature introduced in Oracle Database Release 1 is a problem. Recently I had the opportunity to review few 12cR1 databases and found that to alleviate the performance problems, the DBA set parameter OPTIMIZER_FEATURES_ENABLE= in Oracle Database 12c Release 1 ( This observation is the catalyst for the blog “Adaptive Query …

Creating a SQL Plan Baseline from Cursor Cache or AWR

A DBA deals with performance issues often, and having a SQL suddenly performing poorly is common. What do we do? We proceed to “pin” an execution plan, then investigate root cause (the latter is true if time to next fire permits).

DBMS_SPM provides some APIs to create a SQL Plan Baseline (SPB) from the Cursor Cache, or from a SQL Tuning Set (STS), but not from the Automatic Workload Repository (AWR). For the latter, you (more...)

Purging a cursor in Oracle – revisited

A few years ago I created a post about “how to flush a cursor out the shared pool“, using DBMS_SHARED_POOL.PURGE. For the most part, this method has helped me to get rid of an entire parent cursor and all child cursors for a given SQL, but more often than not I have found than on 12c this method may not work, leaving active a set of cursors I want to flush.

Script below (more...)

Baselines – session creating privs v session running privs

A colleague Richard Wilkinson was telling me about an issue he had come across with baselines and I asked him to write it up as it was an interesting experience.

The following MERGE SQL runs once a day. It always uses the same plan and roughly takes between 30 and 80 minutes:

SQL_ID/ PHV 6zs5dk6t6pkfs / 3064471754

 SELECT ilv.bsns_unit_cd,
 ilv.cntnt_cd ,

ADF Performance on Docker – Lighting Fast

ADF performance depends on server processing power. Sometimes ADF is blamed for poor performance, but in most of the cases real issue is related to poor server hardware, bad programming style or slow response from DB. Goal of this post is to show how fast ADF request could execute and give away couple of suggestions how to minimize ADF request time. This would apply to ADF application running on any environment, not only Docker. I'm (more...)

ADF Performance Story – This Time Developer Was Wrong

ADF is fast. If ADF application is slow, most likely this is related to development mistakes. I would like to tell you one story, based on my ADF tuning experience. Problem description: ADF application runs fast in DEV, when DB size is small. Same application runs slow in TEST/PROD, when DB size is large. Question - what is slow. Answer - slow means forms are loading slow. Ok, lets go to the story.

Developer decides (more...)

Beware of intensive slow query logging when using – log_queries_not_using_indexes

MySQL slow query log is great for identifying slow queries that are good candidates for optimisation. Slow query logging is disabled by default, but it is activated by DBA's or developers on most environments.

You can use slow query log to record all the traffic but be careful with this action as logging all traffic could be very I/O intensive and could have negative impact on general performance. It is recommended to record all traffic (more...)

Oracle and the Autonomous Database (a personal perspective from afar)

Yeah, if you hadn’t seen that one coming, hmmm, what can I say… Lot’s of…

nVision Performance Tuning: Table of Contents

This post is an index for a series of blog posts that discuss how to get good performance from nVision as used in General Ledger reporting.  As the posts become available links will be updated in this post.
  • Introduction
  • nVision Performance Options
  • Indexing of Ledger, Budget and Summary Ledger Tables
  • Partitioning of Ledger, Budget and Summary Ledger Tables
  • Additional Oracle Instrumentation for nVision
  • Logging Selector Usage
  • Analysis of Tree Usage  with the Selector Log
  • Interval (more...)

Autonomous Online Index Rebuild for Oracle Multitenant

First things first: Do not rebuild Oracle indexes! … Unless you have to.

If you are even considering rebuilding indexes on an autonomous manner, please stop now, and first spend some time reading some of the many things Richard Foote has to say on his well-recommended blog.

A little side story: Many, many years ago, as I was supporting EBS at Oracle, one day I got a call from a guy with an aussie accent (more...)

Performance Analysis of a CPU-Intensive Workload in Apache Spark

Topic: This post is about techniques and tools for measuring and understanding CPU-bound and memory-bound workloads in Apache Spark. You will find examples applied to studying a simple workload consisting of reading Apache Parquet files into a Spark DataFrame.

Why are the topics discussed here relevant

Many workloads for data processing on modern distributed computing architectures are often CPU-bound. Typical servers underlying current data platforms have a large and increasing amount of RAM and (more...)

Parallel Reality

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

The following question was recently posted in an Oracle forum:

hi Friends,
I see  this wait event latch: parallel query alloc buffer, when a job meant for doing some cleanup ran this query.
Why does this wait event come happen? , i searched google,MOS no exact hit for explanation of the exact same event.
Looking at query does  (more...)