Franck's Oracle blog
Blog of Franck Pachot, Consultant at dbi services
I'm a Senior Consultant, and Oracle Technology Leader at dbi services (Switzerland).
Certified DBA (OCM 11g, OCP 12c, Performance Tuning Expert, Exadata Implementation) I cover all database areas: architecture, data modeling, database design, tuning, operation, and training.
My preferred area is troubleshooting oracle and performance tuning, especially when I acheive to enable an efficient collaboration between the developers and the
As an Oracle Ace, I participate in the Oracle Community in forums, blogs, articles and presentation. You can follow my activity on this blog: and my twitter account: @FranckPachot
The result cache is very nice when base tables are static: get the result without any buffer get, without any join or sorts. But as soon as any DML occurs on one of the tables the result is based on, then result cache is invalidated. And cache misses are quite expensive. You can think of it as a materialized view in memory. But with materialized views, you can accept to see stale results in order to avoid frequent refreshes. Let's see how we can control stale results with undocumented options.
I posted a tweet with a link to a very old document - 20 years old - about 'internals of recovery'. It's a gem. All the complexity of the ACID mecanisms of Oracle are explained in a very simple way. It was written for Oracle 7.2 but it's incredible to see how much the basic things are still relevant today. Of course, there is a reason for that: the mecanisms of recovery are critical and must be stable. There is one more reason in my opinion: the Oracle RDBMS software was very well designed, then the basic structures designed 20 years ago are still able to cope with new features, and to scale with very large databases, through the versions and the years.
The cloud has been annouced, I want to try.
In RAC, blocks are copied across instances by the Global Cache Service. In single instance, we have only two status: CR for consistent read clones where undo is applied, and CUR for the current version that can be modified (then being a dirty block). I'ts a bit more complex in RAC. Here is a brief example to show the buffer status in Global Cache.
In Oracle 12c you can run Oracle processes as operating system threads, lowering the number of OS processes. But you can't use OS authentification: you need to provide a password. Here is a way to set an environment so that you can still 'connect / as sysdba' to a multithreaded instance.
The database must know where are the files. If you move them from the OS, the database will not be aware of it. But there is a database tool to copy or move files and update the database repository (controlfile). It's RMAN. With RMAN you can either:
In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption.
The default block size is 8k in Oracle. This is the most common. Sometimes, people create the database with 16k block size for datawarehouses. You can also find some 32k block size, but less common which means more bugs. In our tuning workshop we recommend the default, 8k, for most of the cases. I'll show here 3 tests to show what different block size change for full table scan and index access.
It was my first time at DOAG Datenbank in Dusseldorf.