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

OOW14 Day 1 – Dbvisit #repattack

By | Database management, Oracle | No Comments

Oracle Open World is not only conferences but also practice and networking. Today at the OTN lounge have installed the following demos on my laptop: a Dbvisit replicate #repattack with a Delphix cloning environement #cloneattack I’ll detail the former below and the latter tomorrow, but if you are at San Francisco and missed it, please come tomorrow to the same kind of session at the Oak Table World! You don’t even need the OOW registration…

 
Read More
Franck Pachot

How to measure Exadata SmartScan efficiency

By | Database management, Oracle | No Comments

A thread on OTN Forum about Exadata came to the following question: “But how can I monitor if it is effectively used or not?”. This is a common question. There are 3 exclusive features coming with Exadata, and instance statistics can show their usage. Even better: two of them can even be checked on your current (non-Exadata) system. And that is good to foresee how Exadata can improve your workload. Let’s find how to measure the…

 
Read More
Franck Pachot

Thinking about downgrading from Oracle Enterprise to Standard Edition?

By | Database management, Oracle | No Comments

You are using an Oracle Enterprise Edition and thinking about downgrading to the Standard Edition? In this case, you must be sure that your applications are compatible. It’s not something easy to check. Here are a few ideas. Why? Why do you want to downgrade to the Standard Edition? For licensing costs, of course. Today, it is difficult to find a server with only a few cores. And Oracle Enterprise Edition is licenced per number…

 
Read More
Franck Pachot

Oracle: How to move OMF datafiles in 11g and 12c

By | Database management, Oracle | One Comment

With OMF datafiles, you don’t manage the datafile names. But how do you set the destination when you want to move them to another mount point? Let’s see how easy (and online) it works in 12c. And how to do it with minimal downtime in 11g. Testcase Let’s create a tablespace with two datafiles. It’s OMF and goes into /u01: SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata’ scope=memory; System altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE…

 
Read More
Franck Pachot

Resize your Oracle datafiles down to the minimum without ORA-03297

By | Database management, Oracle | 32 Comments

Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent. But if you try to get lower, you will get: ORA-03297:…

 
Read More
Franck Pachot

What’s the consequence of NOLOGGING loads?

By | Database management, Oracle | No Comments

When you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you’ve loaded. So you probably run a backup as soon as the load is done. But what happens if you have a crash, with media failure, before the backup is finish? I encountered recently the situation but – probably because of…

 
Read More
Franck Pachot

Oracle 12.1.0.2.1 Set to Join Conversion

By | Database management, Oracle | No Comments

Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it’s there and you can use it if you set optimizer_features_enabled to 12.1.0.2.1 (that’s not a typo!). Yes, that number looks…

 
Read More
Franck Pachot

Oracle 12.1.0.2: Wait event histograms in μs

By | Database Administration & Monitoring, Oracle | 2 Comments

When an Oracle Database spends a high percentage of its DB time in User I/O, I usually check the wait event histograms in order to see if the storage system is working well. But today, with storage going to SSD, most I/O are less than 1 milliseconds and we have no details about those wait times. Here is what is exposed by V$WAIT_EVENT_HISTOGRAM:   select event,wait_time_milli,wait_count from v$event_histogram where event like ‘db file sequential read’…

 
Read More
Franck Pachot

PDB media failure may cause the whole CDB to crash

By | Database management, Oracle | 3 Comments

Do you remember last year, when 12c arrived with multitenant, David Hueber warned us about the fact that a single PDB can, under certain conditions, generate a complete system downtime? We are beta testers and opened a SR for that. Now one year later the first patchset is out and obviously I checked if the issue was fixed. It’s a patchset afterall, which is expected to fix issues before than bringing new features. So the…

 
Read More
Franck Pachot

SQL monitoring 12.1.0.2 shows adaptive plans

By | Database management, Oracle | No Comments

In a previous post, I have described Adaptive Plans. Even if I prefer to show plans with the SQL Monitor active html format, I had to stick with the dbms_xplan for that because SQL Monitoring did not show all information about adaptive plans. This has been fixed in the Patchset 1 and I have run the same query to show the new feature. First, an adaptive plan can be in two states: ‘resolving’ where all…

 
Read More