Within the past few days I was investigating one of the new features of SQL Server 2025: Optimized Locking. I was curious about the capabilities, the behaviour and as well the limits of the feature. Optimized Locking is based on two primary components: Here we have the transaction ID working as a unique identifier for a transaction. Each row which…
Within the past few days I was investigating another new feature of SQL Server 2025: OPTIMIZED_SP_EXECUTESQL. I was curious about the capabilities and the behaviour. OPTIMIZED_SP_EXECUTESQL is a database scoped configuration which allows us to get rid of the overhead of compiling and producing execution plans when we deal with dynamic SQL. The first user-session which launches a given dynamic…
Even if Ansible is powerful and flexible, it can be considered “slow”. It will be anyway faster, and more consistent, than doing the same steps manually. Nevertheless, we will experiment to make it even faster. I found few of them on the Internet, but rarely with figures of what to expect. In this blog post, I will cover one of…
The Oracle 23c new feature Lock Free Reservations allows to break with the traditional locking mechanism. Now multiple concurrent sessions can update the same row without be blocked !!! Let’s see how it works. In the traditional locking mechanism, when a first session modifies a row, oracle will hold 2 locks : If a second session tries to update the…
Quering database’s by using SQL in shell scripts can make the whole script unreadable. Very oftent the query is unredable too, due to the variable usage. This situation make the script debugging a pain.
Templating simplify the script by taking out from the soufce code file the SQL code. J2 is ideally built for that.
The Oracle Exchange Partition is a method allowing to : exchange the data segments from a non partitioned table to a partitioned table exchange the data segments from a partitioned table to a non partitioned table. This method is useful because: You can get data quickly in or out of partitioned table You can convert non partitioned table to a…
Introduction The second pre-conf I attended was about tuning Power BI. I was curious to hear and learn from Nikola Ilic, who presented the session, what his experience and technique are dealing to solve Power BI performance issues The agenda was the following over the 2 half days Data Modelling Nikola first explain why data modelling is so important and…
Since Tuesday, I have the chance to follow the SQLBits 2022 conference. Sadly, I have to do it remotely this year but I promise next time I will come in-person 😉 SQLBits is one of the largest SQL Server conference in the world with some famous speakers like Brent Ozar, Bob Ward, Erik Darling… In this blog post, I will…
For a customer, I had to check if partitioning improves performance of massive and concurrent inserts. The goal is to execute several Inserts in parallel via dbms_parallel_execute package (my previous blog “parallelize your Oracle Insert with DBMS_PARALLEL_EXECUTE” explains how to use dbms_parallel_execute). The idea is to insert more than 20 millions of rows in 2 tables : One table not…
As specified by Steven Feuerstein into the Oracle Blog Website the bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine. Using BULK COLLECT plus FORALL instead of standard Insert statement to insert data improve performance dramatically, let’s me show you : Here is…
The DUAL table is automatically created by Oracle and contains one column (DUMMY) and one row (x value). This table is often used by SQL developer in PL/SQL code (Package, Functions, Trigger) to initialize variables storing technical information such as for example SYSDATE, USER or HOSTNAME. Querying DUAL table is generally faster as we can see below: SQL> select sysdate…
From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process. Let’s start with the following PL/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a of sql statement to be…
