Infrastructure at your Service

Category Archives: Oracle

Oracle Team

Never gather WORKLOAD stats on Exadata…

By | Database management, Oracle | 3 Comments

By Franck Pachot . For Exadata, oracle has introduced an ‘EXADATA’ mode which sets a high transfer rate (with IOTFRSPEED as in NOWORKLOAD statistics) and set a MBRC (as in WORKLOAD statistics). Those values are set rather than gathered because all the SmartScan optimization done at storage cell level, which makes the multiblock reads less expensive, is difficult to measure from the database. Here I will explain what I stated in a previous blog: direct-path…

Read More
Oracle Team

Oracle multitenant dictionary: rowcache

By | Database management, Oracle | No Comments

By Franck Pachot . I’ve not finished with my investigation on 12c multitenant dictionary. Here, I’m checking how metadata links are managed by the dictionary cache (aka rowcache). As I did previously, I’ll create a metadata link function in my lab environment (this is not for production. For the moment metadata/object links are supported only for oracle objects). SQL> connect / as sysdba Connected. SQL> create or replace function DEMO_MDL_FUNCTION sharing=metadata 2 return varchar2 as…

Read More
Oracle Team

IOUG Collaborate #C15LV

By | Operation systems, Oracle | No Comments

By Franck Pachot . The IOUG – Independant Oracle User Group – has a great event each year: the COLLABORATE. This year it’s in April 12-16, 2015 at The Mandalay Bay Resort & Casino in Las Vegas. I’ll be a speaker and a RAC Attack Ninja as well.  IOUG COLLABORATE provides all the real-world technical training you need – not sales pitches. The IOUG Forum presents hundreds of educational sessions on Oracle technology, led by…

Read More
Oracle Team

Flush one SQL statement to hard parse it again

By | Database management, Oracle | 3 Comments

By Franck Pachot . If you want a statement to be hard parsed on its next execution, you can flush the shared pool, but you don’t want all the cursors to be hard parsed. Here is how to flush only one statement, illustrated with the case where it can be useful. During the performance training, here is how I introduce Adaptive Cursor Sharing, here is how I show the bind variable peeking problem that is…

Read More
Oracle Team

Stay with non-CDB or go to CDB?

By | Database management, Oracle | 5 Comments

By Franck Pachot . 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…

Read More
Oracle Team

When to gather workload system statistics?

By | Database management, Oracle | 2 Comments

By Franck Pachot . 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…

Read More
Oracle Team

NLS_LANG on Windows in Europe: WE8MSWIN1252 or WE8PC850?

By | Database management, Oracle | 2 Comments

By Franck Pachot . 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…

Read More
Oracle Team

SOUG-Romand: Journée performance le 21 mai

By | Database management, Oracle | No Comments

By Franck Pachot . (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…

Read More
Oracle Team

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

By | Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . 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…

Read More
Oracle Team

Insert into GTT: bulk with APPEND_VALUES

By | Database management, Oracle | No Comments

By Franck Pachot . 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:…

Read More