Infrastructure at your Service

All posts by Franck Pachot

Franck Pachot
Technology Leader
Principal Consultant

OCM 12cFranck Pachot has 20 years of experience in Oracle databases. Through his expertise as a DBA, Oracle expert, data architect, and performance specialist, he is able to cover all database areas: architecture, data modeling, database design, tuning, operation, and training. Franck knows how to enable an efficient collaboration between the developers and the operational team when it comes to troubleshooting issues or performance tuning.

RSSOracle ACEOracle ACE Franck is Oracle Certified Master (OCM 12c), Oracle ACE Director and proud member of Oraworld-team and Oak Table.

Next public appearance:
C15LV
Las Vegas (US), Apr 25th

From Transportable Tablespaces to Pluggable Databases
12.2 Multitenant New Security Features to Clarify DevOps and DBA role separation
DOAGDB18
Düsseldorf (DE) , May 14th


12cR2/18c security features to clarify DBA role in DevOps
POUG
Sopot (PL), Sept. 7th

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


Franck Pachot

Stay with non-CDB or go to CDB?

By | Database management, Oracle | 5 Comments

This is a question that starts to be raised quite frequently. Oracle released the multitenant architecture 1.5 year ago. And now says that the previous architecture – known as non-CDB – is deprecated. What does it mean? Do we have to go to the CDB architecture even if we don’t want to use/pay multi-tenant? It started like that on twitter: Non-CDB architecture of Oracle databases is DEPRECATED since Oracle Database 12.1.0.2 https://t.co/7mDO93lGee via Mike Dietrich…

 
Read More
Franck Pachot

When to gather workload system statistics?

By | Database management, Oracle | 2 Comments

This month we started to give our Oracle Tuning Workshop. And with a new workshop comes new questions. We advise to give to the optimizer the most accurate statsistics that we can have. That suggests that WORKLOAD statistics are better than NOWORKLOAD ones because they gather the average number of blocks read in multiblock read, rather than using default values. But then, the question is: which time period do you choose to gather workload statistics,…

 
Read More
Franck Pachot

NLS_LANG on Windows in Europe: WE8MSWIN1252 or WE8PC850?

By | Database management, Oracle | 2 Comments

In europe we have accents and non US7ASCII characters. We need special characterset. I’m not talking about Unicode here that solves all the problems. If you have a Java application, you have no problem: it’s Unicode. You can store all characters in one multi-byte characterset. But for other applications, on Windows, you have 2 possible charactersets for Western Europe WE8MSWIN1252 and WE8PC850. WE8MSWIN1252 is the one that is set by default in the registry, but…

 
Read More
Franck Pachot

SOUG-Romand: Journée performance le 21 mai

By | Database management, Oracle | No Comments

(english below) Bonne nouvelle pour les francophones: le SOUG-R est de plus en plus actif. Le 21 mai 2015 une journée performance est organisée sur Lausanne. Mon call for paper a été accepté: je vous parlerais des SQL Plan Directives – une nouvelle fonctionnalité 12c accessible aussi en Standard Edition! Voici l’abstract: SQL Plan Directives – la mémoire de l’optimizeur L’optimiseur 12c peut évaluer la précision de son estimation, et se souvenir de la raison…

 
Read More
Franck Pachot

Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace

By | Database Administration & Monitoring, Oracle | No Comments

in my previous post I’ve reproduced an issue where some bulk insert into a GTT was generating too much undo. But the real case I encountered was worse: the insert was reading lot of UNDO. And lot of this undo was read from disk. Jonathan Lewis has pointed me to a possible explanation he has given (here) I’ve reproduced the testcase from the previous post after setting the database to do flashback logging. Here are…

 
Read More
Franck Pachot

Insert into GTT: bulk with APPEND_VALUES

By | Database management, Oracle | No Comments

This post is about the case I had where a GTT was generating too much undo. The effects were amplified by the fact that the database was in flashback logging (see Jonathan Lewis answer in my question on OTN forum about that, but that’s for the next post. Nothing here is specific to GTT, but generating undo (and the related redo) is even more awkward on GTT. Here is the query: INSERT INTO SCDAT.INLISTS_INT_SINGLE(INCOUNT,INNUM) VALUES…

 
Read More
Franck Pachot

Oracle multitenant dictionary: upgrade

By | Database management, Oracle | No Comments

This is a second part of the previous post about metadata link. I’ve shown how a sharing=metadata function becomes a sharing=none function when it is changed in the pdb – i.e when not having the same DDL, not having a different signature. Here is another experimentation doing the opposite: change the function in root and see what happens in the pdb. Again playing with internals in order to understand the ‘upgrade by unplug-plug’ feature available…

 
Read More
Franck Pachot

Oracle multitenant dictionary: metadata links

By | Database management, Oracle | 2 Comments

As in previous posts (1 and 2) I’m still playing with multitenant dictionary. You may wonder why I’m spending time to do unsupported things that we will never be able to use in real life. Of course, playing with internals is fun But it’s not only that. We will implement CDB databases (multi-tenant, and single-tenant as I think non-CDB will be deprecated in future versions). We will operate pluggable databases (clone, upgrade, downgrade,…), encounter issues,…

 
Read More
Franck Pachot

Happy New Year 27, 104, 2015 and 2558

By | Database management, Oracle | No Comments

calendar today tomorrow message —————– —— ——— ————– Arabic Hijrah 1436 1436 English Hijrah 1436 1436 Gregorian 2014 2015 Happy New Year Japanese Imperial 0026 0027 Happy New Year Persian 1393 1393 ROC Official 0103 0104 Happy New Year Thai Buddha 2557 2558 Happy New Year   According to Oracle calendars… SQL> l 1 with cal as ( 2 select ‘Arabic Hijrah’ name from dual union all select ‘English Hijrah’ from dual 3 union all…

 
Read More
Franck Pachot

Oracle multitenant dictionary: object links

By | Database management, Oracle | 3 Comments

I’ve described Oracle 12c metadata and object links internals in a previous post. But before that, the first time I investigated on it, I made a wrong assumption because I was looking at AUDIT_ACTIONS which is not correctly implemented. That investigation came from a question on dba-village. And recently Ivica Arsov (@IvicaArsov) has made an interesting comment about AUDIT_ACTIONS object link table, so I’ll explain here what is special with it. AUDIT_ACTIONS Here is how…

 
Read More