Latches: What do we know ?

Latches are low level serialization mechanism which protects memory areas inside SGA. They are light wait and less sophesticated than enqueues and can be acquired and released very quickly.

Latch acquisition does not involve any complex algorithm and is based on test-and-set atomic instruction of a computer processor.

Latch Classification:

Latch can be classified in multiple ways:-

Shared latch and exclusive latch:

Shared latch is the one which can be shared by multiple processes/sessions.

Example (more...)

Brief about Workload Management in Oracle RAC

This is a brief article about workload management in RAC. I tried to cover different components of workload management in RAC and how they are configured at client side or server side. I haven’t gone into details of configuration steps but just mentioned in brief about how it can be done.

Readers are advised to refer Oracle documentation to understand details about configuration of workload management.

Workload management on RAC

There are 2 major components (more...)

How many checkpoints in Oracle database ?

This Question came to my mind when I was discussing “direct path reads” with DBA candidates. I was surprised that many DBAs were not aware of object level checkpoint that happens while doing direct path read. So I thought many DBAs may not be knowing different level of checkpoints that happens in the database and hence the question.

Well, the answer is 7 (as per my knowledge). Oracle does 7 different checkpoints at various stages. Lets check out what (more...)

Coverting MySQL database character set to UTF8

Recently I was engaged to convert the character set for few MySQL databases from latin1 to UTF8 collation utf8_general_ci. This article describes the approach taken for doing the same.

First I will describe various levels at which we can change the character set and collation and then we will see how to convert existing data in a database to required character set.

Backup your database:

Before even thinking about changing character set for your database, (more...)

Cassandra 2.0 Architecture

In this article, I will cover various key structures that makes up Cassandra. We will also see what structure resides in memory and what resides on disk.

In next article, I will give an overview of various key components that uses these structure for successfully running Cassandra. Further articles will cover more details about each structure/components in details

Cassandra Node Architecture:

Cassandra is a cluster software. Meaning, it has to be installed/deployed on multiple servers (more...)

Affect of object Statistics on SQL Execution statistics

This is a small article to demonstrate why correct statistics are important and how they affect execution statistics of same plan.
In the past we learned that changing table statistics or index statistics (or rebuilding index) can causes plan for a SQL to change. Because when statistics changes, optimizer will try to generate new plan based on changed statistics.
With 11g, oracle provided baseline to ensure stability in SQL plans. So if you have single (more...)

Tracing Single SQL in Oracle

Many times, while doing SQL tuning, we want to trace (event 10046) single SQL in database. Instead of going for module level tracing or session level using DBMS_MONITOR, we can simply use below alter system command to trace specific SQL

Example: I have a table T1 and index T_I_TABLE_NAME on that table.
I am running following SQL and I want to trace on this SQL

select * from T1 where table_name = 'SINGLE_PRODUCT_GROUPS';

I (more...)

direct path read behavior in Oracle 11.2

Prior to 11g, whenever optimizer goes for full table scan, Oracle used to show “db file scattered read” as wait event. But starting from 11g, a full table scan can show (depending on certain conditions) “direct path read” wait event.

db file scattered read – happens when blocks for a table is read from datafile into buffer cache in SGA

direct path read – happens when blocks for a table is read from datafile into (more...)

Pointing MySQL Read-Replica to new master after failover

Here I am describing a simple scenario where we have a read replica (other than slave) which is currently pointing to master mysql instance.
We have another slave which is also pointing to same master. What we are going to see is, how to point read replica to new master after failover.


To make you familier with environment, we have 3 instances of mysql

mysql_a -> Current master
mysql_b -> Current slave
mysql_c -> read replica  (more...)

Apache Cassandra – NoSQL storage solution

These days I am exploring another storage solution – Cassandra.

Apache Cassandra datastore was originally developed by Facebook as open source NoSQL data storage system. Its actually based on Amazon’s dynamoDB database. Apache Cassandra is an open source distributed database management system designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure. Cassandra offers robust support for clusters spanning multiple datacenters, with asynchronous masterless replication allowing low latency operations (more...)