Manually Generating AWR Report

One of my customers asked me to check performance on his production database server but could not allow any access to the server itself. He asked if I could generate the AWR reports from his client machine and since it’s not really trivial (or hard) I created this script.

When we want to generate the AWR report, we usually run the awrrpt.sql script. We don’t need to be on the database server for that (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...)

Skip LOV Validation for ADF BC Bulk Insert

This post is about ADF BC LOV. I will describe how you could optimize bulk insert of new rows into VO, when some of the attributes are assigned with LOVs. By default ADF would validate new row attribute value through LOV (LOV Validation and Programmatic Row Insert Performance) for each new row. This will lead to bad performance, especially if you insert a set of new rows programmatically - there will be multiple (more...)

eProseed Oracle Open World 2016 debrief – Oracle Database & Oracle Management Cloud

For people interested, here the slide desks used internally to debrief my colleagues regarding Oracle…

Scanning an Index

The internet is full of information about indexes, and for a reason. Indexes in a database is probably the most important performance related topic. There are so many cases, properties, and different ways to use indexes that there is simply a lot to write about. In this post I’d like to talk about a specific … Continue reading Scanning an Index

Investigating IO Performance on Amazon RDS for Oracle

I’ve recently been involved in quite a few database migrations to Oracle RDS. One thing that I had noticed when dealing with post-migration performance issues was related to queries that used TABLE SCAN FULL in their execution. It seemed, that in many cases, it just took a single query to max out the allocated IOPS (IOs per second) or bandwidth, which in turn would caused overall slowness of the RDS instance.

The search in documentation (more...)

Why oh Why Do Will Still Not Have a Fast Bulk “SQL*Unloader” Facility?

Way back in 2004 I was working at the UK side of the Human Genome project. We were creating a massive store of DNA sequences in an Oracle database (this was one of two world-wide available stores of this information, for free & open use by anyone {* see note!}). The database was, for back then, enormous at 5-6TB. And we knew it would approx double every 12 months (and it did, it was (more...)

Do you suffer from Storage Stockholm Syndrome?

The last year at DSSD (now a part of Dell EMC) has been an extremely interesting one for me, and I’ve learned a great deal, which is always good. Some of the lessons have been surprising, though… One of them is what I will rather dramatically refer to as Storage Stockholm Syndrome. Stockholm Syndrome is … Continue reading "Do you suffer from Storage Stockholm Syndrome?"

Friday Philosophy – Your Experience can Keep You Ignorant

This week I was in an excellent presentation by Kerry Osborne about Outlines, SQL profiles, SQL patches and SQL Baselines. I’ve used three of those features in anger but when I looked at SQL Patches I just could not understand why you would use them – they looked to me like a very limited version of SQL Profiles.

There is a prize for spotting Kerry without a baseball cap

There is a prize for spotting Kerry without a baseball cap

So I asked Kerry about it (more...)

#DOAG2016 wrap up


Another DOAG is over and here are my summary.

Day 1

Unfortunately the early train from duesseldorf arrives not before 9:30, so I missed the first morning slot. But on the other hand I this was the slot where I had to decide between 8 presentations, which I’m interested in.

The first soft skill presentation was about creative techniques beyond brain storming. The mentioned 3 techniques was all unknown for me, maybe I give it (more...)

“If You Change This, Right Here …”

"changes are so frightening."
-- Norton Juster, The Phantom Tollbooth

It isn’t unusual for Oracle to expand functionality with each new release; a short list includes AWR, ADDM, the SYSAUX tablespace, Flashback Query, Flashback Database and Edition-based Redefinition. Oracle 12.1 is no different as it provides an interesting performance enhancement, the in-memory column store, that can increase performance by reducing physical I/O load by making tables memory-resident (to simplify the description somewhat). Inn (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 7

This part of the series is supposed to cover the results of I/O related tests performed on the Amazon RDS Oracle cloud instance.
As mentioned in the previous part of this series I've only used the "General Purpose SSD" storage type since the "Provisioned IOPS" storage was simply to expensive to me and it wasn't possible to get a trial license for that storage type.

Now one key aspect of the "General Purpose SSD" storage (more...)

HotSpot JVM garbage collection options cheat sheet (v4)

After three years, I have decided to update my GC cheat sheet.

New version finally includes G1 options, thankfully there are not very many of them. There are also few useful options introduced to CMS including parallel inital mark and initiating concurrent cycles by timer.

Finally, I made separate cheat sheet versions for Java 7 and Java 8.

Below are links to PDF versions

Oracle Database Cloud (DBaaS) Performance Consistency – Part 6

In the next parts of this series I'll have a look at the results of similar performance consistency tests performed on a comparable Amazon RDS Oracle cloud database instance.

Let's start with the configuration used, which was an Amazon RDS "db.m4.2xlarge" instance - the latest RDS generation available at time of testing. This is an 8 VCPUs resp. Hyper Threads / 4 cores configuration with 32GB of RAM.
As storage I've used (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 5

This is the fifth part of this installment, and before coming to comparisons to other cloud providers, in this part I show the results of the read-only test that I've already described in part three of this series, but repeated at a later point in time. The test setup was identical and can be checked in the mentioned previous part.

The reason for running the test again was the fact that I was informed during (more...)

Visualizing System Statistics in SQL Developer – OTN Appreciation Day

Tim Hall inspired me with his #ThanksOTN (or “OTN Appreciation Day“) campaign to continue my mini-series on leveraging SQL Developer Reports for DBA tasks. Today with visualizing historical System Statistics from Statspack for performance analyses. #ThanksOTN I have three favourite Oracle features to rave about today: Analytic SQL Statspack SQL Developer (particularly the reports)! System Statistics As the […]

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.

Well, it's one thing to say 'create all new tables like this', (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 4

This is the fourth part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time the previous read-only test was modified to be a 100% update read-write test. So every row read was updated in the following way:

    for rec in (
          select /*+

Spark 2.0 Performance Improvements Investigated With Flame Graphs

Topic: This post is about performance optimizations introduced in Apache Spark 2.0, in particular whole-stage code generation. A test case is introduced and investigated with diagnostic tools.

Introduction: performance troubleshooting of a slow query using parallel query execution in a Hadoop cluster

The idea for this post comes from a performance troubleshooting case that has come up recently on our database services. It started with a user reporting slow response time from a query (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 3

This is the third part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using logical I/O only (see part 2) the test harness executes a SQL statement that has to perform physical I/O mostly, and to be more precise this round is a read-only test, so doesn't include any modification to data (except the logging and any other background (more...)