What is the best relational database?

“I have the simplest tastes. I am always satisfied with the best.” Oscar Wilde

If we look at market figures, then “Gartner 2012 Worldwide RDBMS market share” reports 48.3 percent revenue share for Oracle.

Three major factors make the Oracle database a clear leader:

1. Remains #1 in worldwide RDBMS software revenue share
2. Holds a larger revenue share than four closest competitors combined
3. Leads next closest competitor revenue share by 29%

But why is that so? Let us have a look at the latest DB-Engines Ranking where Oracle is not only #1 in the DB-Engines Ranking of Relational DBMS but also in the Complete Ranking.

The DB-Engines Ranking are measured the popularity of a system by using the following parameters:

- Number of mentions of the system on websites, measured as number of results in search engines queries.
- General interest in the system.
- Frequency of technical discussions about the system.
- Number of job offers, in which the system is mentioned.
- Number of profiles in professional networks, in which the system is mentioned.

What is the best relational database? The best answer given in answers.yahoo.com is the following: “Define “best”. Oracle is like a BMW. Expensive but has all the fixings. But not everyone needs a BMW. MySQL is like a VW Beetle (the old model). Its cheap, and gets you where you need to go. But you have to tweak it to suit your needs.” Nice explanation!

Next, let us look at Gartner’s Magic Quadrant for Data Warehouse Database Management Systems from January 31st, 2013:

The reason Oracle is top on the “Ability to Execute” scale is simple. It can be described by me with just one word: Exadata. Of all the vendors in this analysis, Oracle reports the highest incidence of nontraditional analytics customers: sectors such as hospitality, energy trading, life sciences and food distribution show up in its reference base. According to Gartner, many of the vertical markets where Oracle has the greatest success contain traditional implementers or late adopters of data warehousing. Oracle’s customers range in annual revenue from $100 million to over $10 billion.

The Business Technology Forum raised the same question: Which is the best enterprise RDBMS database? The article is very much to the point coming to the conclusion that Oracle have the upper hand.

Mirror, Mirror on the Wall, which is the best RDBMS of all? You do not expect an answer from the Mirror “It depends” because anyone trying to be diplomatic will answer that way. Well, at least they say that Oracle is most scalable, most feature rich, and just a wonderful RDBMS.

More to read:

Why would you use Oracle database?
Why Oracle is the Preferred Database Platform?
Is Microsoft’s SQL Server really cheaper than Oracle?

The last one favors SQL Server over Oracle but a word of caution on the credibility on the Oracle side: the author calls PL/SQL “P-SQL” and refers to Active Data Guard as “Advanced Data Guard”.

It will take DB2, SQL Server, Sybase, PostgreSQL and all other key RDBMS players at least several more releases before they can approach the current self-management and tuning capabilities of Oracle Database 11g. But when that day comes, Oracle will not be still at version 11.2.0.

12c is knocking on the door.

DBA tips for tuning Siebel on RAC and Exadata

More than 100 years ago, Mark Twain said that “the elastic heart of youth cannot be compressed into one constrained shape long at a time”.

In modern technology, “the elastic cloud can be compressed with the constraints of long datatypes”. Let me explain the meaning of my statement in terms of Siebel applications running on RAC and Exadata.

Chained and migrated rows are often a big problem in a Siebel database. The issue of chained rows can be resolved by using big block size while migrated rows require segment reorganization with higher PCTFREE (20, 30 or even 50). Chained tables in Siebel are often S_ORG_EXT, S_ASSET, S_ORDER_ITEM and S_ORDER_ITEM_OM. At least these will require using a bigger block size.

For LONG and CLOB details in a Siebel CRM, check CLOB Physical Type in Siebel.

But as Joel Goodman noted in How Smart is Your Smart Scan?, there is an Exadata specific situation that causes migrated rows.

When a row is updated in a Hybrid Columnar Compressed (HCC) table, then it is migrated to another block in the segment that is managed using “OLTP compression“. Any HCC Compression Unit (CU) containing at least one migrated row, will also cause the block containing that row to be accessed by the server using a “cell single block physical read“.

Look at the “Top 5 Timed Foreground Events” in a Siebel on Exadata OLTP DB using HCC:

The situation with HCC in OLTP is tricky for the following 3 reasons:

1. Every update of a record stored in HCC format results in a migrated row
2. The new row is stored in a new block that is marked for OLTP compression
3. Non-direct path inserts will be loaded into OLTP compressed blocks as opposed to HCC format

For the above reasons, mixing HCC with DML is not recommended. Partitioning can provide a mechanism for avoiding these issues since each partition can have its own storage format.

Only after decompressing the OLTP tables, the event “cell single block physical read“ disappeared and the performance got significantly improved.

Another good tip for Siebel on RAC is the usage of high number of hash partitions for hot indexes. High means 256 and more (should be a power of 2).

Look at the situation with gc buffer busy waits before the high hash partitioning:

As you can see the “gc buffer busy acquire” was the top event:

Looking at issues with these events in MOS might incline you to believe that this is a bug. However, this was not the case as you can see what happened after making the number of hash partitions for some of the indexes 256, the database performance was back to normal:

Note that also non-partitioned tables can have hash partitioned indexes!

Another tip: SecureFiles is a feature introduced in Oracle Database 11g that is *specifically* engineered to deliver *high performance* for this type of unstructured data. I have seen several queries getting even 10 times faster after migrating LOBs to SecureFiles.

About sequences: in RAC/Exadata, using the CACHE and NOORDER options together results in the best performance for a sequence. For example, in a Siebel database the S_DOCK_TXN_LOG_S sequence is used to generate the transaction ID used by S_DOCK_TXN_LOG table. The default cache size for sequences in Oracle is 20. If you are having thousands of concurrent users, Siebel/Oracle suggest you increase the cache size to be at least 10000.

Last 2 things:

- For gathering Siebel database statistics use always the latest version of coe_siebel_stats.sql. As of now, the latest version is 11.4.4.5
- The script coe_siebel_profile.sql provides a list of columns that are not indexed but potentially are good candidates for indexing according to their usage by the Optimizer.

For additional tips, check Oracle’s white paper Siebel on Exadata!

And using Oracle Enterprise Manager makes all tuning so much easier!

The Oracle Platform Administrator

Question: what do the following two pairs have in common:
- Oracle database and non-Oracle hardware/software
- Rowan Atkinson and Audrey Hepburn?

My answer: they are both good but don’t fit very well into the same picture.

Creating a back-end database system based on multiple vendors’ components make less and less sense nowadays. Consider the following:

Which one would you go after, left or right? To make it more clear, choose between Exadata and IBM POWER7 with AIX OS on top of EMC being monitoring with BMC Patrol and backed up with HP Data Protector integration?

I have witnessed from my experience that hardware and software components from multiple vendors are complex to support. No one supplier is usually accountable at a system level.

In a consolidated database environment, a complete test of a major change often must include synchronous testing the multiple workloads in order to catch situations where a change in one area might cause an issue in another. Patching for example one component breaks sometimes another one.

With Exadata, Oracle provides a single point of support for the Oracle Exadata Database Machine both for hardware and software. Support problems are resolved much faster than where multiple vendors are involved and can be reported and managed by the DBA. But is the Exadata DBA still just a DBA?

Kevin Closson used the term Oracle Platform Administrator (OPA) in a response to an article from Martin Bach about the meaning of Exadata experience.

Let me quote Kevin:

“It’s nearing the point where an Oracle DBA cannot do everything that is expected. The acronym no longer really fits. Database Administrator? What does that have to do with cellsrv? DBA? What does that have to do with clusters (at the platform level [you mention fencing]), etc, etc, etc.

It’s nearing time that there should be two roles:

1) The Oracle Platform Administrator (OPA)
2) The Oracle Database Administrator

The DBA tends to the *contents* of the database and applications’ needs for data from the database, tuning, etc. The OPA deals with all the activity that was once the domain of the System Admin and Storage Admin and, moreover, the OPA deals with the crushing load of maintaining Oracle software (patching).

I can’t see it any other way. Oracle has moved their technology stack in a direction that essentially warrants this split in responsibilities. Think of any other RDBMS product where the DBA is expected to be a platform administrator as well. I’m not saying good or bad on that matter, but it is high time to consider the split.”

Uwe Hesse has just written an excellent blog post entitled “How Exadata will impact your IT Organization”. The difference between the Exadata Database Administration (EDBA) and the Oracle Platform Administrator (OPA) is probably close to zero. I see it more like that the EDBA is just a DBA managining also Exadata, while OPA is more of a general admin having excellent DBA skills. Semantics!

Obviously, additional training for the DBAs is needed but by no means the 285 days of new DBA training for existing Oracle DBA suggested by Allen Licitra and Mark Shainman. I wonder how that number was calculated and if we are talking calendar or workdays

I would highly recommend Enkitec’s Course on Oracle Exadata Administration!

Arup Nanda suggests that the role should have the following skillsets:

I would furthermore split the Database Administrator part as follows:

I do not know where the meetings and conf. calls fit in.

But as Andrew Carnegie said: “Concentrate your energies, your thoughts and your capital. The wise man puts all his eggs in one basket and watches the basket!” And who else to that on the Oracle side but the Oracle Platform Administrator.