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.

POUG
Cracow (PL), Sept. 1st

12cR2 Statistics Advisor: Do you gather statistics in the way the optimizer expects them?
SOUG
Swiss Oracle User Group

Leman: Calculer les statistiques comme prévu par l'optimiseur
Baden: Single-Tenant 12.2
OOW17
San Francisco, Oct 1st

Multitenant: Trust It by Understanding Beyond What Is Documented
DOAG2017
Nürnberg, Nov, 21st

Multitenant internals
UKOUG TECH17
From Transportable Tablespace to Pluggable Databases
Join Methods: Nested Loop, Hash, Sort, Merge, Adaptive


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
Franck Pachot

Beyond In-Memory, what’s new in 12.1.0.2 ?

By | Database management, Oracle | No Comments

It’s just a patchset. The delivery that is there to stabilize a release with all the bug fixes. But it comes with a lot of new features as well. And not only the one that has been advertised as the future of the database. It’s a huge release. Let’s have a look at what’s new. First, it seems that it will be the only patchest for 12.1 Then, there is that In-Memory option awaited for…

 
Read More
Franck Pachot

Drilling down V$RECOVERY_AREA_USAGE

By | Database management, Oracle | 2 Comments

In a previous post I used X$KCCAGF to get more information about reclaimable archived logs in FRA, because there is a bug in standby (not opened) databases where archivelog deletion policy is ignored. I explained that the view V$RECOVERY_AREA_USAGE has only aggregated information about space reclaimable without the details about which files are reclaimable or not. Here I’ll explain how I came to X$KCCAGF and I’ll give the query to get all the detailed information…

 
Read More
Franck Pachot

Oracle EM agent 12c thread leak on RAC

By | Database management, Oracle | 4 Comments

In a previous post about nproc limit, I wrote that I had to investigate the nproc limit with the number of threads because my Oracle 12c EM agent was having thousands of threads. This post is a short feedback about this issue and the way I have found the root cause. It concerns the enterprise manager agent 12c on Grid Infrasctructure >= 11.2.0.2 NLWP The issue was: ps -o nlwp,pid,lwp,args -u oracle | sort -n…

 
Read More
Franck Pachot

Partial Join Evaluation in Oracle 12c

By | Database management, Oracle | One Comment

Do you think that it’s better to write semi-join SQL statements with IN(), EXISTS(), or to do a JOIN? Usually, the optimizer will evaluate the cost and do the transformation for you. And in this area, one more transformation has been introduced in 12c which is the Partial Join Evaluation (PJE). First, let’s have a look at the 11g behaviour. For that example, I use the SCOTT schema, but I hire a lot more employees…

 
Read More
Franck Pachot

Oracle Parallel Query: Did you use MapReduce for years without knowing it?

By | Database management, Oracle | No Comments

I’ve read this morning that MapReduce is dead. The first time I heard about MapReduce was when a software architect proposed to stop writing SQL on Oracle Database and replace it with MapReduce processing. Because the project had to deal with a huge amount of data in a small time and they had enough budget to buy as many cores as they need, they wanted the scalability of parallel distributed processing. The architect explained how…

 
Read More
Franck Pachot

Linux: how to monitor the nofile limit

By | Operation systems, Oracle | One Comment

In a previous post I explained how to measure the number of processes that are generated when a fork() or clone() call checks the nproc limit. There is another limit in /etc/limits.conf – or in /etc/limits.d – that is displayed by ‘ulimit -n’. It’s the number of open files – ‘nofile’ – and here again we need to know what kind of files are counted. nofile ‘nofile’ is another limit that may not be easy…

 
Read More
Franck Pachot

Linux: how to monitor the nproc limit

By | Database Administration & Monitoring, Oracle | 14 Comments

You probably know about ‘nproc’ limits in Linux which are set in /etc/limits.conf and checked with ‘ulimit -u’. But do you know how to handle the monitoring and be alerted when you’re close the fixed limit? Nproc and ps Nproc is defined at OS level to limit the number of processes per user. Oracle 11.2.0.4 documentation recommends the following: oracle soft nproc 2047 oracle hard nproc 16384 But that is often too low, especially when…

 
Read More
Franck Pachot

How to list all Oracle system schemas

By | Database management, Oracle | 2 Comments

Do you want to know which users come with the Oracle Database and which are the ones you have created? This is that not easy, especially prior to Oracle 12c. You know SYS and SYSTEM, but there are many others – especially when you have installed all options. And it is important to know them all, e. g. when you import or apply a password policy, etc. Let’s see the options we have. 12c shows…

 
Read More