Database management

ROWNUM vs ROW_NUMBER() and 12c fetch first

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(…)

Oracle 12c CDB – metadata & object links internals

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(…)

AWR Formatter: plug-in Google Chrome pour rapports AWR

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(…)

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

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(…)

Oracle TNS_ADMIN issues due to bad environment settings

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(…)

Oracle 12c Adaptive Plan & inflection point

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(…)

Transparent data encryption, key management and backup strategies

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(…)

The consequences of NOLOGGING in Oracle

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(…)

SQL Server 2014: Buffer pool extension – an interesting new feature

Buffer pool extension (aka BPE) is an interesting feature of SQL Server 2014. Database administrators already know that memory is one of the key assets available for SQL Server. This is especially true for buffer pool memory, which plays(…)

Investigating Oracle lock issues with event 10704

By Franck Pachot . Did you ever encounter unexplained Oracle lock issues? They may be coming from unindexed foreign keys (which is worse in 11g). It’s not easy to monitor. Of course you can check Oracle locks from V$LOCKED_OBJECT, but(…)