Infrastructure at your Service

All posts by Frank Pachot

Frank Pachot
Technology Leader
Senior Consultant
Franck 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 Pachot knows how to enable an efficient collaboration between the developers and the operational team when it comes to troubleshooting issues or performance tuning. He is "Oracle Certified Master 11g (OCM 11g)" and "Oracle Certified Expert for Oracle Database 11g Performance Tuning". Prior to joining dbi services, Franck Pachot was Oracle Consultant at Trivadis in Lausanne. Previously, he worked in several countries and environements, always as a consultant. Franck Pachot holds a Master of Business Informatics from the University of Paris-Sud. His branch-related experience covers Financial Services / Banking, Public Sector, Food, Transport and Logistics, Pharma, etc.
Frank Pachot

RESULT_CACHE hint expiration options

By | Database management | No Comments

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…

 
Read More
Frank Pachot

Multitenant vs. schema based consolidation

By | Database management | No Comments

If you want to install multiple instances of a software, for example you host the ERP for several companies or subsidiaries, you have 3 solutions: have one database and multiple schema have multiple databases have one database and multiple pluggable databases Of course, this is exactly the reason for pluggable databases: multitenant. You have good isolation but still share resources. A lot of reasons have been given why multiple schema – or schema based consolidation…

 
Read More
Frank Pachot

Oracle Log Writer and Write-Ahead-Logging

By | Database management | No Comments

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…

 
Read More
Frank Pachot

Oracle Database Cloud Service – My first trial

By | Database management | No Comments

The cloud has been annouced, I want to try. From the cloud.oracle.com/database website, there is Trial only for the ‘Database Schema Service’ so I asked fot it, received an e-mail with connection info and it works:   Good. The password was temporary, so I have to change it, and set answers to 3 within 4 questions in case I forgot my password. Java error: my user does not exist: Ok, I was too quick after…

 
Read More
Frank Pachot

RAC buffer states: XCUR, SCUR, PI, CI

By | Database management | No Comments

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. SCUR: shared current I connect to one instance (I have a…

 
Read More
Frank Pachot

Multithreaded 12c and ‘connect / as sysdba’

By | Database management | No Comments

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. Windows I start with Windows because Oracle has always been multithreaded on windows. Are you able to use operating system authentication then? You  think…

 
Read More
Frank Pachot

2 ways to move archivelogs – both need RMAN

By | Database management | No Comments

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: update the repository after you’ve moved the file from the OS or do the both: move and update the repository The syntax is a bit weird, so let’s…

 
Read More
Frank Pachot

Buffer pool advisory in AWR

By | Database management | No Comments

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.   I’m running the following query to compare the ‘physical reads cache’ from DBA_HIST_SYSSTATS and the value from the advisor in DBA_HIST_DB_CACHE: SQL> column sysstat_value format…

 
Read More
Frank Pachot

Does the block size matter?

By | Database management | No Comments

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. Test case I…

 
Read More
Frank Pachot

DOAG Database 2015

By | Database management | No Comments

It was my first time at DOAG Datenbank in Dusseldorf.  My understanding of German is very limited. I’m French, I’ve learned German at school but then didn’t practice. Now that I’m living in Switzerland it would help but when I tried to remember something from school time it failed with Ora-1555: Snapshot too old… So I was there because I presented SQL Plan Directives – in English – and I really appreciate that DOAG has…

 
Read More