Infrastructure at your Service

Oracle Team

ROWNUM vs ROW_NUMBER() and 12c fetch first

By | Database management, Oracle | 4 Comments

By Franck Pachot . Prior to Oracle 12c, there were two ways to do ‘top-n’ queries: use rownum after sorting rows with “order by” use row_number() over (order by) Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these queries, user interactions are often stateless, so we cannot just open a cursor and fetch 10 rows on each user…

Read More
Oracle Team

Oracle 12c CDB – metadata & object links internals

By | Database management, Oracle | 23 Comments

By Franck Pachot . Warning: this is only geek stuff about internals on multitenant database dictionary, metadata, and object links. It has nothing to do with the operations that you can do on your database. Don’t try that in production or you can corrupt the whole dictionary. In 12 multitenant database (aka CDB) we know that each pluggable database is isolated in order to act as a standalone database. But they share some common resources…

Read More
Olivier Toussaint

AWR Formatter: plug-in Google Chrome pour rapports AWR

By | Database management | No Comments

Chacun d’entre nous à une façon bien différente d’appréhender le rapport AWR (Oracle Automatic Workload Repository). Nous utilisons nos propres processus de lecture et notre propre appoche. Dans ce blog je vous propose de découvrir AWR Formatter, un outil qui permet de concentrer les informations en quelques clics. Il s’agit d’un plug-in (AWR-Format.crx) pour Google Chrome fort sympatique, qui permet de reprendre un rapport AWR et de le rendre plus lisible à la première lecture….

Read More
David Barbarin

SQL Server 2014: In-Memory tables, Bw-Tree, and storage

By | Database management | No Comments

SQL Server 2014 introduces hash indexes with in-memory optimized tables. I described some of their aspects in a previous blog post.  These indexes are very efficient with lookup search operations but have some limitations with operations like range scans, inequality predicates or scan order operations. These limitations are linked to hash index design that stores rows in the index in a random order. Fortunately, nonclustered indexes for in-memory optimized tables (aka range indexes or Bw-Tree)…

Read More
Oracle Team

Oracle TNS_ADMIN issues due to bad environment settings

By | Database management | 2 Comments

Recently, I faced a TNS resolution problem at a customer. The reason was a bad environment setting: The customer called the service desk because of a DBLINK pointing to a bad database. The users were supposed to be redirected to a development database, and the DBLINK was redirecting to a validation database instead. The particularity of the environment is that development and validation databases are running on the same server, but on different Oracle homes, each…

Read More
Oracle Team

Oracle 12c Adaptive Plan & inflection point

By | Database management, Oracle | One Comment

By Franck Pachot . The Oracle 12c Adaptive Plan feature was already presented by Nicolas Jardot in OOW 2013: Solving customer issues with the 12c Optimizer. I recently had to answer several questions about its behavior at execution time. Maybe the term ‘adaptive’ is misleading. It’s not that a join will stop and restart to another join method. Even with adaptive plan there will only be one join method to be applied. The feature only…

Read More
Morgan Patou

How to install and manage a Kerberos Server

By | Application integration & Middleware | 5 Comments

For some time now, I have been working on how to set up a Single Sign-On (SSO) solution in my company. As a big fan of Open Source solutions, I have obviously proposed the implementation of a solution based on Kerberos. What I mean by that is a solution based on the true Kerberos, i. e. MIT Kerberos. Indeed, Kerberos was originally a research project at the Massachusetts Institute for Technology (MIT) in the early…

Read More
Oracle Team

Best practice for the sending of an Oracle execution plan

By | Database Administration & Monitoring, Oracle | 5 Comments

By Franck Pachot . You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer…

Read More
Morgan Patou

Kerberos SSO with Documentum D2 3.1 SP1

By | Application integration & Middleware | 4 Comments

Last week, when I was working on how to setup the Kerberos SSO for D2 3.1 SP1, I faced some issues due to a documentation that doesn’t seem to be up to date… In fact, our version is D2 3.1 SP1 and there is no specific documentation for SP1. In consequence, I read the D2 3.1 Installation Guide and the D2 4.0 Installation Guide. The first time I read the D2 3.1 documentation, I found…

Read More
David Barbarin

Transparent data encryption, key management and backup strategies

By | Database management | 7 Comments

Transparent Data Encryption requires the creation of a database key encryption. The database key is a part of the hierarchy of SQL Server encryption tree with at the top of the tree the DPAPI. Then if we traverse the tree from the top to bottom we can find the service master key, the database master key, the server certificate or the asymmetric key and finally the database encryption key (AKA the DEK). In this hierarchy…

Read More
Oracle Team

The consequences of NOLOGGING in Oracle

By | Database management, Oracle | No Comments

By Franck Pachot . While answering to a question on Oracle forum about NOLOGGING consequences, I provided a test case that deserves a bit more explanation. Nologging operations are good to generate minimal redo on bulk operations (direct-path inserts, index creation/rebuild). But in case we have to restore a backup that was made before the nologging operation, we loose data. And even if we can accept that, we have some manual operations to do. Here…

Read More
Alain Lacour

Oracle APEX: redirect all pages to one

By | Application integration & Middleware | 2 Comments

This blog will explain how to proceed in order to redirect all the application pages to one single page, prior to their processing and rendering, with Oracle APEX 4.0 and above. In some cases, like security checks, you may want to redirect all calls of any page in your APEX application to one single page whith particular processing before the target page is rendering or even processing. I ran into this need again, recently while…

Read More