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...)
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...)
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...)
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...)
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';
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...)
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...)
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...)
Recently I got an opportunity to dig into performance of database. We were not facing any performance issue, but we wanted to understand whats happening under the hood. What are the bottlenecks and how do we prepare and scale our database further.
In future the load on the databases are going to be crazy and we want to make sure that we look into every consumer of our database and optimize the usage.
I went (more...)
If you have multiple MySQL slaves or complex topology, its difficult to remember what slaves are replicating from which master.
We can go to slave servers and check “show slave status” which list master host, but if we want to find out other way, we have can run “show slave hosts” from master.
This will list down all the slaves that are getting replicated from this master.
Only problem I faced was, “show slave (more...)