Tuesday, 31 May 2016

Mutating Table Error In Oracle: Why It Happens And What You Can Do About It

The mutating table error in Oracle can be a familiar sight throughout a DBA's career, along with the usual question of 'Why?'. The error itself is not difficult to understand nor is it hazardous to the table data. Let's look at why the error is thrown, why it's not going to create data problems and how to possibly fix it.

The first thing to realize is the table isn't actually mutating; the error is thrown because of the read consistency mechanism Oracle employs to ensure against 'dirty reads', which are reads of uncommitted changes. For all but the session performing the data modifications Oracle will use redo blocks to reconstruct the data image as of the starting time of the query.

Friday, 27 May 2016

Getting Started with the Query Store Feature in SQL Server 2016 – Part 1

Introduction


While doing performance troubleshooting, have you ever wished for a process that was easier and faster to follow, especially when your system had slowed down and business is being impacted? Did you wish you could compare the execution plans for a query from a different time frame easily and quickly? Did you wish you could regress the query plan easily and quickly instead of going through a lengthy process of using a plan guide to do that? Did you wish execution plans for the queries didn’t get lost even after a server restart or upgrade? If your answers to the above questions are yes, you will be happy to know that the SQL Server 2016 Query Store feature greatly simplifies performance troubleshooting, helps you in the scenarios mentioned above and many more. In this article series, I am going to talk about this new feature and how it greatly simplifies the performance troubleshooting process.

Monday, 23 May 2016

Oracle System Change Number: An Introduction

For many people working with Oracle database, SCN (System Change Number) is a topic that interests them greatly – but despite that it’s a fascinating topic, it’s confusing as well. In this article we will learn the nitty-gritty of SCN: what it is, where it is used, and how it works.

It’s worth mentioning that this is not and cannot be complete coverage of every detail about SCN. With that said, let’s start.

SCN- Why do we need it?

Assume it’s the end of the month and its payday. You are in the Payroll group and, with appropriate authority, have issued a query to pull the currently salary calculation of all the employees. Numbers are flowing across the screen, everything is going great.

Friday, 13 May 2016

Automatic Big Table Caching in RAC

I discussed Automatic big table cache (ABTC) which is a new feature introduced in Oracle Database 12.1.0.2c. Automatic big table cache is an optional section of the buffer cache reserved for storing large objects. It tracks only big tables and automatically caches scanned big objects in big table cache thereby avoiding direct path reads. It caches partial objects when objects cannot be fully cached. It is primarily designed to enhance performance for data warehouse workloads, but also improves performance in mixed workloads. Though it can be used for Serial as well as parallel full scans of large objects in Single Instance environments, it is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.

In this article, I will demonstrate ABTC for parallel scans in a 12.1.0.2c RAC database.

Current scenario:
  • Name of cluster: Cluster01
  • Number of nodes : 3
  • Name of RAC database (Non-CDB): orcl
  • Number of RAC database instances : 3