Posts

Commit, Rollback, Undo & Redo: Unveiling the Intricacies of Maintaining Data Integrity & Consistency in Oracle Database

Image
Oracle Relational Database has a comprehensive system to ensure data integrity and consistency. Various processes and mechanisms are in place to safeguard these aspects. In this article, we will delve into the following: 1. How a transaction happens in the Oracle relational database 2. How Oracle handles rollbacks, whether initiated by the client or caused by fatal errors 3. The roles of UNDO and REDO 4. Factors affecting rollback performance Let's start with the transaction process and what happens in the backend when a DML statement executes. Transaction Execution in Oracle Database When a DML statement is executed, the data to be modified in the table is first read into the Buffer Cache if it is not already there. The Data Buffer is then modified by a process called the Database Writer (DBWn), which marks the modified buffers as dirty. This means the version of data in the buffer cache is different from the data in the disk and needs to be written to the disk. Before the ...
Post a Comment
Read more

Enhancing Oracle SQL Performance: A Journey with SQL Baselines

Image
 As we all know, the performance of Oracle SQL queries plays a critical role in the overall performance of applications running on top of the Oracle Database. Performance tuning is essential, and while the Oracle Database Optimizer generally selects the best execution plan for SQL queries, it's not always perfect. This is where performance tuning becomes crucial. One common approach to tuning a SQL query is to add a hint, guiding the optimizer to follow a specific path. However, adding a hint to an existing SQL query, even without changing the query's logic, still alters the application code. In today’s ERP world, changing production application code is not a trivial task. Moreover, from an optimizer's perspective, it’s better to avoid adding explicit hints to the application code. So, how can we handle this effectively? The Solution: Oracle SQL Baselines This is where Oracle SQL Baselines come to our rescue. O...
Post a Comment
Read more