Top 60 Oracle Blogs

Recent comments

August 2018

ATP vs ADW – the Autonomous Database lockdown profiles

By Franck Pachot

The Oracle database has always distinguished two types of workloads: transactional (OLTP) and datawarehouse (VLDB, DWH, DSS, BI, analytics). There is the same idea in the managed Oracle Cloud with two autonomous database services.

To show how this is old, here is how they were defined in the Oracle7 Tuning Book:


The definition has not changed a lot. But the technology behind DSS/DWH has improved. Now, with In-Memory Column Store, Smart Scan, Result Cache we can even see that indexes, materialized views, star transformation, hints,.. are disabled in the Autonomous Datawarehouse cloud service.

MERGE JOIN CARTESIAN: a join method or a join type?

By Franck Pachot

I’ll present about join methods at POUG and DOAG. I’ll show how the different join methods work in order to better understand them. The idea is to show Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian on the same query. I’ll run a simple join between DEPT and EMP with the USE_NL, USE_HASH, USE_MERGE and USE_MERGE_CARTESIAN hints. I’ll show the execution plan, with SQL Monitoring in text mode. And I’ll put some gdb breakpoints on the ‘qer’ (query execution rowsource) functions to run the plan operations step by step. Then I’ll do the same on a different query in order to show in detail the 12c adaptive plans.

Creating a RAC 12.1 Data Guard Physical Standby environment (3b)

Huh, what is this I hear you ask? Part 3b? Oracle 12.1? Well, there’s a bit of a story to this post. Back in December 2016 I started to write a series of blog posts (part 1 | part 2 | part 3 | part 4) about how I created a standby database on RAC 12.1. For some reason I forgot to post this part. Up until now the step where I am creating the broker configuration was missing. Thanks to a friend I discovered my mistake.

How much free space can be reclaimed from a segment?

By Franck Pachot

You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more, but you don’t know.

Conferences 2018

Here are the conferences where I speak this end of year

Join Methods: Nested Loop, Hash, Sort, Merge, Adaptive

This is an explanation of the join methods, with live demos on very simple cases, reading execution plan statistics, looking at how it is executed. And how to improve the query performance: hints, partitioning, indexing. The goal is to understand, with modern tracing tools (dbms_xplan and SQL Monitoring), the basic core stuff that is involved in all query tuning: the join methods, the estimations that drive the choice between full scan and index access, the transformations that avoid unnecessary joins.

  • POUG 2018 September 07, 2018

POUG 2018

CQRS, Event Sourcing and the Oracle Database

By Franck Pachot

This blog post relates my thoughts when reading about Command Query Responsibility Separation and Event Sourcing, in the context of the Oracle Database (but it can probably apply to any database). We see those terms in the new software architecture diagrams, but they are actually quite old:

Command-Query separation

Command-Query separation was defined by Bertrand Meyer 15 years ago, not for the database but for the Eiffel language. See page 22-44 of Eiffel: a language for software engineering.

Visual Studio- All the Warm and Fuzzy

So I haven’t opened Visual Studio in….oh….let’s just say it’s been a few years…:)

I had a project that I needed to run and was surprised when the Solution Explorer was missing from SSMS 2017.  Its only fair to say, there was also fair warning from Microsoft.

Oracle 18c preinstall RPM on RedHat RHEL

By Franck Pachot

The Linux prerequisites for Oracle Database are all documented but using the pre-install rpm makes all things easier. Before 18c, this was easy on Oracle Enterprise Linux (OEL) but not so easy on RedHat (RHEL) where the .rpm had many dependencies on OEL and UEK.
Now that 18c is there to download, there’s also the 18c preinstall rpm and the good news is that it can be run also on RHEL without modification.

This came to my attention on Twitter:

RAC One node databases are relocated by opatchauto in 12.2 part 2

In a previous post I shared how I found out that RAC One Node databases are relocated on-line during patching and I promised a few more tests and sharing of implications. If you aren’t familiar with RAC One Node I recommend having a look at the official documentation: The Real Application Clusters Administration and Deployment Guide features it prominently in the introduction. One of the things I like to keep in mind when working with RAC One Node is this, quoting from section 1.3 in said admin and deployment guide:

Building community via the speaker community

I recently participated in the Oracle Developer Community Yatra tour throughout India. It is a hectic event with 7 cities covered in a mere 9 days, so you can imagine how frantic the pace was. A typical day would be:

  • 7am – breakfast
  • 8am – check out of hotel and leave for the venue
  • 9am – speak all day, host Q&A
  • 6pm – leave straight from venue to the airport
  • 8pm – dinner at airport, and fly to next city
  • 1am – get to next hotel, grab some sleep before doing it all again in 6 hours time

Yet as a speaker in this event, it never felt that the logistics of the event were out of control.  This is mainly due to the incredible work of the people in the AIOUG (All India Oracle User Group) coordinated by Sai Penumuru. The smoothness of the organization prompted me to blog about how user groups could follow the AIOUG lead in terms of running successful events.