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

12c SQL Plan Directive: state has changed between 12.1.0.1 and 12.1.0.2

By | Database management, Oracle | 9 Comments

SQL plan Directives have been introduced in 12c. If you have scripts that check their states (and I have that as I prefer to monitor closely the features that are new) you probably have seen a difference when going from 12.1.0.1 – the first release of 12c and the only one avilable yet in Standard Edition – and 12.1.0.2 – the first patchest. I’ll explain here what are the SQL Plan Directive states and how…

 
Read More
Franck Pachot

AWR does not store explain plan predicates

By | Database management, Oracle | One Comment

What is the most important part in an execution plan? It’s probably the predicate section. It helps to see implicit conversions. It helps to understand why an index is used or not. Or to see additional predicates coming from constraints. When you get an execution plan from shared pool, the dbms_xplan.display_cursor() shows the predicates. But when you retrieve a plan from the past, dbms_xplan.display_awr() does not show them. Statspack With Statspack, you can store execution…

 
Read More
Franck Pachot

12.1.0.2 CDB views are now using CONTAINERS()

By | Database management, Oracle | 2 Comments

I’ve blogged about multitenant internals at the time when 12.1.0.1 was just released. Something has changed in 12.1.0.2 and blogging about it was in my todo list for a long time. Now the occasion to do it has been triggered by a question on Oracle Forums about CDB_DATA_FILES not showing PDB$SEED datafiles because there is an important change we must be aware of. In the previous blog, I have described how you can query PDB…

 
Read More
Franck Pachot

Oracle SQL Profile: why multiple OPT_ESTIMATE?

By | Database management, Oracle | 5 Comments

In a previous blog I’v shared my script to retrieve the OPT_ESTIMATE hints from a SQL Profile. In the example I made, I had two lines for each table: — PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak: /*+ OPT_ESTIMATE(@”SEL$2CBA5DDD”, TABLE, “EMPLOYEES”@”SEL$1″, SCALE_ROWS=2) OPT_ESTIMATE(@”SEL$58A6D7F6″, TABLE, “EMPLOYEES”@”SEL$1″, SCALE_ROWS=2) OPT_ESTIMATE(@”SEL$6AE97DF7″, TABLE, “DEPARTMENTS”@”SEL$1″, SCALE_ROWS=5.185185185) OPT_ESTIMATE(@”SEL$58A6D7F6″, TABLE, “DEPARTMENTS”@”SEL$1″, SCALE_ROWS=5.185185185) */ The reason is that when the optimizer do some transformations to the query, then the query block identifiers can change….

 
Read More
Franck Pachot

Oracle SQL Profiles: Check what they do before accepting them blindly

By | Database management, Oracle | 8 Comments

People often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don’t take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is just the CBO with more time to optimize, and more ways to optimize. The fact is that when a customer calls me, they expect…

 
Read More
Franck Pachot

Using tmux for semi-interactive demos

By | Database management, Oracle | 5 Comments

You are a speaker and like to show some demos. Which kind of demo do you do? you script everything so that you just have to press ‘enter’ during the demo you prepare everything in a text file and copy/paste during the demo you type everything in live? I don’t like the first one because it’s too basic. What do you do if you need to copy/paste a previous result in a future command? Playing…

 
Read More
Franck Pachot

The future of SQL*Plus and some oddities

By | Database management, Oracle | One Comment

Rumors are coming after OOW14. Oracle is working on a new SQL*Plus version. Not only a new version. It’s a completely new development. Here is what we know currently about the future of sqlplus an sqldeveloper. But I start with some very old stuff. SQL*Plus sqlplus is an old tool. With many features that look as oddities today. You want to see some oddities? Good idea because it can be error-prone. Without knowing them, an…

 
Read More
Franck Pachot

How to disable all database links

By | Database management, Oracle | 4 Comments

A frequent scenario: you refresh test from production with a RMAN duplicate. Once the duplicate is done, you probably change dblinks so that they address the test environment instead of the production one. But are you sure that nobody will connect in between and risk to access production from the test environement? You want to disable all db links until you have finished your post-duplicate tasks. I know two solutions for that. The first one…

 
Read More
Franck Pachot

Oracle cloud control / SQL Details / Statistics

By | Database management, Oracle | No Comments

A question that I had several times: in Enterprise Manager, in the screen about one SQL statement, the ‘statistics’ tab shows the number of executions, elapsed time, etc. Question is: which time window does it cover? There is a one hour chart above, and two timestamps displayed as ‘First Load Time’ and ‘Last load Time’, and we don’t know which one is related with the execution statistics numbers. I’ll explain it clearly on an example….

 
Read More
Franck Pachot

Oracle: an unexpected lock behavior with rollback

By | Database Administration & Monitoring, Oracle | No Comments

Here is an odd Oracle behavior I observed when a transaction that acquired a lock is rolled back. Note that this is related to a specific combination of locks that should not be encountered in production. So it’s not a bug. Just something unexpected.   First session In my first session I lock the DEPT table in share mode (RS) 20:56:56 SQL1> lock table dept in row share mode; Table(s) Locked. My first session (SID=53)…

 
Read More