Oracle Datenbank Architektur – nicht nur für Einsteiger (DOAG Konferenz 2013)

Talk at DOAG 2013

Thanks everybody for attending my talk “Oracle Datenbank Architektur – nicht nur für Einsteiger” at DOAG Conference 2013. It was a great feeling to have a packed room there. As promised, here comes my presentation and whitepaper (both in German).


Feedback is always appreciated!

DOAG Presentation: Oracle Standard Edition RAC

Wednesday last week, I had a presentation for my regional Oracle Users Group (DOAG Regio Nürnberg). The month before I was asked to display the difference between Enterprise Edition and Standard Edition RACs.

Here comes the presentation (German), for questions and suggestions just let me know.

Stay highly available
Martin Klier

Martin Klier now on twitter

After ignoring the little bird telling things for quite a while, I decided to join the tweeters. Twitter might bring more color into my daily reading. :)

If you feel like, just follow me – @MartinKlierDBA

How to move a datafile as Oracle Managed File (OMF) before and after

Sometimes, you just have to move your data files away from where they sit. But when you love Oracle Managed Files as I do, you may want to have it being an OMF afterwards as well as before. Doing it the way you would have done it with ASM, fails.

(RMAN> copy datafile 10 to ‘/my/path’;)


(English: File already exists)

Stating the file name explicitly breaks the OMF spirit:

(RMAN> copy datafile 10 to ‘/my/path/O1_MF_USERS_76TDX9GH_.DBF;)


(File already has the name of Oracle managed Files)

So what’s the problem? Using deprecated syntax! Just use “backup as copy”, it gives you the TO DESTINATION keyword:

(RMAN> backup to copy datafile 10 to destination ‘/my/path’;)



Now you can switch the datafile to copy “as usual”:

(RMAN> switch datafile 10 to copy;)


(Datafile switched)

Lesson learned: Don’t become stuck on old syntax just because you know it by heart.

Stay careful
Martin Klier

PS: Windows isn’t my preferred platform by far. But as you can see from the screen shots, the solution works even there. :)

Oracle 12c: Change hostname for Grid Infrastructure / Oracle restart

Few days ago, we cloned a VM test system with ASM. Changing the hostname in this case is mandatory from our network, we have lots of issues if they are duplicate.

But changing an Oracle Restart or generally, Grid Infrastructure setup’s hostname isn’t so easy – it’s written many configs. The only way is to deconfigure and configure the Grid Infrastructure stack. If you have no special services configured, it’s quite straightforward, as my colleague Benedikt Nahlovsky describes.

If there are additional / self-made services, just recreate them with your scripts (don’t forget to change the hostname in them if you used it!)

Hope you are doing well
Martin Klier

Oracle 12c New Features – Look at’s for

A new product …

Last week I spent some days of intense investigation and testing the new database 12c. I found lots of great stuff (even about 11g :) ), and some for sure will make it into a separate blog post. So these are just my notes of a very interesting couple of days, and maybe it can be a guide for you when starting to dive deeper into the newest RDBMS coming from Redwood Shores. Just let me know your opinion, and maybe submit a comment.

Unfortunately, many of the new features are only available in Enterprise Edition or EE plus some options or additional products. As far as I knew the need, I marked my points accordingly. But don’t rely on my information, have a look at the most recent licensing guide!

One word before we start: Do nothing before you know WHAT you do. And with a new Oracle product, you CAN’T know what you do yet. So play happily, but don’t use it in production until second PSU or first patch set and its first PSU.

Oracle Database 12c Architecture

  • PROCESSOR_GROUP_NAME parameter — allows to bind the instance to a Linux CGROUP, it’s NUMA aware
  • THREADED_EXECUTION parameter — Enables Multithreaded Architecture (MTA), intended to speed up internal work and saves process spawning. No OS authentication allowed, need to use the password file (thus, “sqlplus / as sysdba” and “rman target /” do not work any more)
  • USE_DEDICATED_BROKER parameter — use threads, not dedicated servers
  • v$process STID column — holds reference to the system thread in multithreaded architecture
  • DEDICATED_THROUGH_BROKER_[LISTENERNAME] listener parameter — make the listener spawn threads, not dedicated servers
  • To be done: Investigate serialization waits in MTA vs. conventional process architecture
  • Smart Flash Cache allows more than one device — possible to deactivate one at runtime; if two, one is used for OLTP, one for OLAP (distinction by Parallel Execution)
  • SDU (Session Data Unit) of TNS allows max. 2MB — calculate 70 bytes of overhead per packet; use “trcasst -t” utility;

Generic Database Administration

  • catupgrd.sql deprecated — use now
  • DBMS_QOPATCH — query opatch information within RDBMS
  • DBMS_SCHEDULER now has a RMAN job type
  • option_usage.sql and used_option_details.sql scripts — from My Oracle Support (ID 1317265.1) for license-relevant infos
  • STATSPACK enriched with PDB awareness — but always install it into the PDB, not into the CDB
  • ADRCI improved — some menus allow interactive HOME selection (SHOW CONTROL does not)
  • varchar2(32k) / nvarchar2(32k) — are just LOBs, with all limitations (no ORDER BY / GROUP BY)

Multitenant Database

Read more…

DOAG Konferenz 2013: “Oracle Architektur – nicht nur für Einsteiger”





After speaking about mutex waits last year, this time DOAG accepted my lecture “Oracle Architektur – nicht nur für Einsteiger” about Oracle Database basic architecture for this year’s DOAG Konferenz 2013 in Nürnberg. It will be the first lesson in a brand-new stream of the conference: The so-called “Einsteigerpaket” (beginners package) allows seeing a full stream of introductory talks by only purchasing a one-day ticket for the last day (thursday).

“Oracle Architektur – nicht nur für Einsteiger” will cover basic but important concepts of the Oracle RDBMS: How it makes ACID happen, what are Redo Logs, Undo Records, Before Images, how is locking done, and where not, and why all of those old day’s features allowed Oracle to introduce sophisticated stuff like RAC, Dataguard and Flashback without breaking bones.

Nuts and bolts is the topic, but on a level every IT technican should understand. Hope to see you there: 21.11.2013, 0900h, Room 19

Direct link to the session:

Here the link to the papers and a photo from the session.

How NLS settings can affect Oracle’s join behavior – v$ example

Recently, I had a problem with accessing Active Session History being not fast enough on Oracle Database 11.2 and 12.1. Looking at the explain plan, wow, no doubt why: Accessing two fixed tables with TABLE ACCESS FULL and joining them with NESTED LOOP. This couldn’t have been “works as designed”, it would render gv$active_session_history nearly useless. But from the beginning.

My query

select * 
 from gv$active_session_history
 where SAMPLE_TIME>sysdate -1
 and sql_id='f29fxwd5kh2pq';

And it simply does not come back within an hour. So what?

Read more…

Oracle SQL: Optimizing a WHERE predicate inequality with a Function Based Index


Recently, on Oracle, I saw a join of three tables, using three times TABLE ACCESS FULL and HASH JOIN, without an obvious reason. During separating and understanding its components, the following scenario turned out to be the culprit.

select *

The optimizer is unable to calculate the cardinality of the “OBJECT_ID<DATA_OBJECT_ID“ predicate, and thus, it ends up with underestimating the cardinality, and using a mass access path. (The “gather_plan_statistics” hint was added to see details as real buffer gets and does not change the behavior.)

Execution Plan 1

Execution Plan 1

Fighting a loosing battle(?)

So how does this come? Oracle statistics (even histograms) only store information about the distribution of values in a column. The optimizer can’t combine two column statistics in a way, that would allow to quantify “one is smaller than the other” over the whole table. The CBO can’t combine column statistics anyway, that’s why we have to use extended statistics. But in this case, we simply can’t create an extended statistic representing an inequality predicate.

Read more…

Oracle Database 12c Release 1 available

Since today, Oracle allows downloading the newest release of the Database product 12c via

So far I only can see it for the Linux platform, and docs are still missing. But this may change rapidly.



Take care
Martin Klier