2013 in review

In Uncategorized on January 4, 2014 by Kanchana Selvakumar

The stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 1,500 times in 2013. If it were a cable car, it would take about 25 trips to carry that many people.

Click here to see the complete report.


Cache Fusion ..contd

In Uncategorized on May 22, 2013 by Kanchana Selvakumar

The matrix below represents the buffer state of a three node setup;the image of a leaf relates to the block and therefore the buffer.


‘Cache Fusion Demystified’ by Arup Nanda.

Creative Commons License
Technical writes by KANCHANA SELVAKUMAR is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 India License.


Cache Fusion

In ORACLE on March 9, 2013 by Kanchana Selvakumar

The scope of this article is to describe ‘Cache Fusion’ and  concepts pertaining to it.Cache Fusion is a cache coherency mechanism where the copies of blocks are sent directly to the requesting instance’s memory cache from the holding  instance’s  memory  cache  without  involving  the  disk.Therefore the  performance  overhead  in  managing  shared data is  reduced significantly.CacheFusion is the logical fusion of the cache of each instance of the rac setup.

How does this happen?

In RAC environments ,each instance has a buffer cache in it ’s SGA. But, when data gets processed ,it appears as though there exists one single cache. Cache fusion enables to get this view of a  logically combined single cache. Therefore each instance’s view of the database is the same as the other in the cluster,ie a single system.

One  Cache Fusion scenario can be related to the model below.We have one image of a block as shown below.


Instance A is currently working on the picture to paint it red from the  tip. Only instance A has the exclusive rights to modify the image.

Instance B wants to have a look at the image.If it’s  a request for a consistent image it gets to see the image at a previous point in time.If its for a current one then its the above image with the work in progress.In which case, instance A after completion of work,registers the current version of the image and then ships it directly to instance B through an interconnect.Now instance B works on the image further as seen below.If instance A raises a request for disk write then it gets done by instance B as it has the most current version of the image and after the write is done the PI(s if any) get discarded.

The same  happens in a database world of rac .Multiple instances work together.Each instance has its own copy of blocks.But then they work to gether getting over contention to upkeep the integrity of the database.

The background processes :

LMS -Global Cache Service Process-This records datafile status and the information on cached blocks in a directory called Global Resource Directory.Therefore it handles the messages between the remote instances,manages the access of blocks and transmits the  images between caches of the instances.

LMON-Global Enqueue Service Monitor-This process monitors global enqueues and resources and also the recovery operations for the same.Enqueue literally means getting pushed at the end of  queue.An enqueue in this context,is a shared memory structure or  a lock,to  serialize  access to the database resources.In a cluster database setup global enqueues are used.An enqueue is qualified with a mode viz Null,Shared , Exclusive.A resource is any object that can get locked by  session(s) from different instances;the dictionary cache across the cluster is kept in sync using this GES, dictionary cache lock.Therefore the GRD has the data structures associated with resources and its available across instances in a cluster.

The GES,GCS and GRD work together to get cache fusion working.

Images and Contentions:

Consistent Read:Current block contains all the changes for  commited and yet to be commited transactions.Consistent read version is the consistent snapshot of the block in a particular point in time.This is created by applying the undo segment  to the past image of the block.

Past Image:When an instance requests a block for modification ,the holding instance that has just modified it retains an image of the block before shipping it to the requesting instance.This is the past image;several past images can exist across instances.When a write to disk is initiated the most recent PI is used and  in case of failure recovery these PI’s are used by GCS to reconstruct the current version of that block.

Read-read Contention:

Two instances try to read the same datablock.Multiple instances can read access datablocks on shared mode with no cache coherency conflicts.

Read-write contention:

When one instance writes to a block holding an exclusive lock on it and another tries to read the same block which is not yet written to disk-the request could be for current version or the consistent version.The GCS  transfers the blocks ,in either case,  to the requesting instance via the high speed interconnect.

Write-write contention:

When the same datablock is modified frequently by different instances,the holding instance completes the write ,GCS converts the resources to be global.This means the datablock’s resources get globally managed across the instances.The scope is no more local to the holding instance.It then creates a current version of the datablock and registers it in the holding instances memory ,also known as Past Image and then ships it to the asking instance.As the block’s resource has a global role,the GCS keeps track of every version of the datablock and there exists several PI’s across the cluster.On event of a failure the current version is reconstructed using the PI’s.When a disk write is initiated the GCS  will forward the request to the instance that holds the most recent version and when the write is done all instances that hold a PI of this written block delete them.

Dynamic Performance Views:

For every v$ view here there exists a gv$view.In rac environments a gv$ view gets the same information but from all instances.A few that help in understanding the concepts have been described below.


This retrieves information from the block header of each block in the SGA of the current instance.In the query below block#,status and kind ie pertaining to that particular database object is fetched.The status shows ‘scur’ that’s shared current and ‘cr’ that is consistent read as the status of the blocks.The other status could be ‘xcur’-exclusive ,’free’-not in use,’mrec’ -media recovery and ‘irec’ – instance recovery modes.



Wait events for RAC convey precious information used for performance analysis.These are used in ADDM which diagnose the effect of cache fusion.Here wait event means the wait time is related to an event  that is the outcome of  the  request .The wait events are classified under the ‘Cluster’ wait class.


‘gc buffer busy’ means there exists block contention.’gc cr block 2-way’ and ‘gc cr block 3’ way indicate that the block has been shipped from the remote instance to the local instance without any busy ,pinned or log flush requirement problems.2-way and 3-way mean the transfer happened with 1 message, 1 transfer and 2 messages , 1 transfer-ie from a third node.Likewise each of these events represent an outcome which can be interpreted to help performance monitoring.The ‘congested’ waits relate to issues in load balancing.These wait events are used in ADDM to get the effect of cache fusion.



The CLUSTER_WAIT_TIME shows the wait times for the global cache events.The SQL_TEXT column would help identify the queries that need to be tuned where the corresponding wait times listed is high.

To conclude cache fusion  is central to the efficient run of a rac setup.The statistics related to it help monitor and improve performance significantly.

Many thanks to Sampath Kattepogu for his value added contributions.

Oracle Rac 11gr2 Documetation.

Creative Commons License
Technical writes by KANCHANA SELVAKUMAR is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 India License.


Client Result Cache -11gr2

In ORACLE, Uncategorized on April 27, 2012 by Kanchana Selvakumar Tagged:

The purview of this article is to describe the feature ‘ Client Result Cache’ specific to 11gr2.

The client result cache is a memory area in the client process that stores sql query result sets of outer most query of OCI applications.Every client process has a result cache and that is shared by all sessions of that process.A client process is one that gets created by the operating system to run an application or Oracle tool code.Eg when Sql *plus is run by a user,a client process is  created for that.An OCI (Oracle Call Interface) application  is an API with which applications that make function calls can be created.Apparently these functions access the database server and control all phases of sql execution.This supports  the syntax and semantics of C and C++.

Therefore the feature Client Result Cache ,enables client side caching of sql result sets in the client memory to improve the response times of ‘repetitive’ queries.And applications that use this feature show performance improvement for queries that hit the cache.Oracle recommends caching for read-only/read mostly tables.

The idea of client result cache can be related to the example below.When asked to say

(a+b)(a-b) = ?

Copyrighted kanchana Selvakumar 2012

one can precisely recall from memory  or refer to books.Needless to say which is fast and easy.Though ,an oracle world query and access to its results may not be as simple as this,the analogy infers that, in the client memory  result sets get cached and therefore that reduces the response time by preventing the round trip to the server on queries that repeat.

The client process’s one result cache is shared among multiple sessions like this.

The Client process carrying one/multiple sessions has a one to one relationship with the result cache.When an sql query is executed by one session say sess 1 the results get cached. The cache is transparent to all application sessions of  the process.Therefore, if  the next session sess 2   executes the same query the results are fetched from the result cache.

Also the client result cache keeps the result set  consistent with the session state or database changes that may change the result set.When the OCI client makes the next round trip to the server it gets an invalidation message -if  a transaction has caused a change to the data or the metadata of the database objects that make the result set.

Setting the database initialization parameters: 

These are important parameters that influence the behavior of the client result cache.

CLIENT_RESULT_CACHE_SIZE is set to value greater than 32768 bytes enables the client result cache; Any  value lesser or default 0 disables it.Once enabled it can be overridden at the client side ie disabled or the value getting changed.

CLIENT_RESULT_CACHE_LAG helps in synchronizing the client result cache with the database by increasing the frequency of round trips to the server.The default value is 3 Secs.When set to a lower value the trips frequent.

COMPATIBLE mentions  the release for compatibility. for enabling the feature and for caching on queries on views.

Result caching control :

The result caching can be controlled at various levels  and they are

1.RESULT_CACHE_MODE  parameter

It takes two values  ‘MANUAL’ or ‘FORCE’;It is set using ‘ALTER SESSION’ ,’ALTER SYSTEM’ or in the sever parameter file.MANUAL is the default value.FORCE implies that result caching is forced on all select statements with one exception ;when the sql statement carries a ‘NO_RESULT_CACHE’ hint.It works across all tables/queries.

2.SQL statements




If this hint is specified at statement level  then this hint takes precendence over the mode parameter.

3.Annotations at the Table Level:

The  result caching can be applied at the table level by using ‘ALTER TABLE’ statements like shown below.This level takes precedence over statement level except when /*+NO_RESULT_CACHE*/ hint is used in the statement .

To change the mode lets check the mode of  Employees table  by ‘selecting’ from the USERS_TABLE.(It can also be checked in DBA_TABLES and ALL_TABLES).Then caching is enforced on  three  tables  like shown below.




See pic below

The query below  involves  ‘Employees , Jobs and Job_history’  tables.The first time the result set is cached.The next time the results are drawn from the cache, as ‘Time elapsed’ points out.See pics below.

All tables in the query have to be annotated.Even if one table is not then the results are not cached.The advantage of  using this method is that the sql statement at the application level need not be hinted.

When another session tries to execute the same query the results are fetched from the same cache.See pic below.

 For a ‘given query’  if there exists a sql hint or a session level parameter then that takes precedence over table annotation.

To check:

The CLIENT_RESULT_CACHE_STATS$ table can be queried to check which client process is caching.Even after enabling result cache,  to ensure that it does ,the result set should be read consistent.

To conclude its a matrix  of the above combinations that decides whats best for performance benefits with reference to a query for that table.Especially when an application loops certain queries ,the caching of results at clients side apparently discards repeating block reads and recomputing.As caching is at clients end ,inexpensive server is there to be scaled.As there are also query blocks- that get only server cached.


Oracle® Call Interface Programmer’s Guide 11g Release 2 (11.2)
Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)
Creative Commons License
Technical writes by KANCHANA SELVAKUMAR is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 India License.


Rac On One node.

In ORACLE, Uncategorized on March 30, 2012 by Kanchana Selvakumar Tagged: ,

Rac on one node is a new feature with 11gr2 Enterprise edition.Its a single instance of a rac database that runs on one node in a cluster.It allows for virtualization of databases, consolidation of databases and also to move on to a multi node rac database if there happens a need to do so,with no downtime.To use Rac On One Node ,Oracle 11gr2 enterprise edition with Oracle Real Application Clusters option should be installed.

A simple rac on one can be related to the pic above.Lets do a dissection.

Here we have a cluster of two servers and three Rac on one node instances running in the cluster .

 represents the common shared storage.We have three databases  viz db1 db2 and db3 in that order.db1 and db2 run on server one and db3 on server two as a cluster.A  cluster of interconnected servers appears as one server to the end user/application.

Database Consolidation

In the first server two databases db1 and db2 have been consolidated on a single os.Since only a OS needs to run on the same server resources,that makes way for more databases.

Online Database Relocation

If  the server  gets overloaded    ,the instance is  relocated to another server with a higher capacity.Therefore there’s no downtime for the application users.Oracle Rac One node instance can be relocated to another server also ,when  on maintenance requirements ,ensuring the continuity of services or rather better availabilty.The Server Control Utility performs this operation.The  ‘srvctl relocate database ‘ command initiates the relocation of the instance from one node to another.It also cleans up if the operation fails.

Figuratively it can be shown as this

As one can see the second server has failed and the instance db1 has been relocated to the first server.

Instance resource caging

Instance resource caging can be applied dynamically on the new server.Its yet another feature of  11gr2.The  individual database instance’s  CPU consumption can be limited using this facilty.Instance resource caging implies setting the memory initialization parameters dynamically,literally speaking ,for the instance to sit  in the new node amiably.

Moving on to Multi-node  rac.

When the applications on Rac on One Node outgrow the resource available , then it can be upgraded online to multi node Real Application Cluster ,with no downtime.When the scenario is mission critical then its most likely to use this feature .


Omotion is an utility specific to 11gr2.This is used to move the rac one node instance from one server to the other with zero downtime.This is useful for load balancing,os and database patching and maintenance  activities.It can be better explained with the  pictures below.

is the migrated instance of db1.

Connections are not accepted at both nodes at the same time,a maximum of 30 minutes is allowed to move the connections to the new location.Oracle supplied tools stage control the process and the migrated instance shuts down transactional after the movement like below where a patching happens.

Thus we have virtualisation of databases,consolidation of databases and also moving on from One node to Muiti node Rac in a Rac On One Node setup .

The sequel to this article will be on implementing the same.


Creative Commons License
Technical writes by KANCHANA SELVAKUMAR is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 India License.


RMAN and block corruption

In ORACLE, Uncategorized on December 17, 2011 by Kanchana Selvakumar

The purview of this article is to take a simple case of corruption and explain its subsequent recovery using RMAN.

RMAN is an utility.The name stands for Recovery Manager and it does so.Its a client application ,meant for backup and recovery operations.

The way in which RMAN works can be figuratively related to the pic below.If we assume that the current state of the database is full black,the last shade,which has been arduously and intricately  built, over a period of time, as each shade represents the state at different points in time.These shades (wrf  to  this pic)of the database is completely kept track of by RMAN whenever a backup is done.When a breakdown happens these shades  are applied appropriately to fix the disruption.

Copyrighted kanchana Selvakumar 2012

We connect to RMAN as shown

We take a  backup of the datafile as shown in pic below

The  sample table called ‘names ‘ is used to simulate block corruption .See pic below.

We corrupt it then.See pic

Lets list and see the corruption results

we get back to RMAN and recover it as below

We confirm the block media recovery with a ‘select’  operation from names.

Creative Commons License
Technical writes by KANCHANA SELVAKUMAR is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 India License.


Block corruption-DBMS_REPAIR

In ORACLE on November 7, 2011 by Kanchana Selvakumar

Oracle stores data in data blocks.Data block is an unit of storage measure at the lowest level.Block corruption is defined as a condition where the block does not confirm to what oracle identifies or understands.Thus  the block has changed and this change could be hardware  or software related.Block corruption is classified  into two types physical and logical.Physical corruption  is one where oracle cannot read the block at all whereas logical corruption implies that contents are inconsistent logically.Logical corruption can be  related to the picture below

 Where the index refers to the index of a book and when turned to page 23 we see contents that are irrelevant.

The aftermath of block corruption varies in dimensions as in some cases where it paralyses the system.Some instances, it is limited to a subset of rows that can be skipped and yet the system runs.Either way prevention is the first step.When that fails block corruption is detected,reported and repaired.

The scope of this article is limited to DBMS_REPAIR package and the procedures that can be exploited for this purpose.To understand this we simulate corruption and see the procedures that help us to detect and fix block corruption.Yes,its only a fix.

To begin with we create an user ‘Hacker’ and  allot a tablespace ‘Defademo’ to her. See pic below

We create a sample table called ‘hacktbl’ and insert the following rows.

Then we use the select statement to read from dba_segments the header block for ‘hacktbl’.See pic below

   Now we intend to write some junk in the  20th block such that the file gets corrupted.The command we use is the ‘dd’ command.The dd command should  be used after completely comprehending the usage and the end result.The man pages can be used for this purpose.Otherwise the results can be bizarre as, we write at the block level.The dd command to write at the 20th block can be seen in the pic below.

Here ‘of ‘ refers to the file written to which is default_demo.dbf,bs refers to block size.The system responds by saying the records written and information relevant to the write operation.Lets confirm our action.We perform a select on ‘hacktbl’.It returns an error.See pic below.

  Ora 01578 and ora 01110 ,not very interesting to see  in real time,say the block and the file that is corrupted in our operation,namely 20 and default_demo.dbf.

Now we start the fixing with DBMS_REPAIR  package.  This package belongs to SYS.The fix operation involves creating a Repair Table ,which gets populated when a procedure called check_object that belong to DBMS_REPAIR  is used on the corrupted file.The repair table again, is created by a procedure of DBMS_REPAIR called create_table.See Pic Below

The picture clearly shows the relationship between the admin_table,check_ object procedures , the repair table and the corrupted table.The admin_table proc is used to create the repair table whereas the check_object procedure populates  the repair table after reading the corrupted table.Lets see how we get it done.We execute  the statements needed to create a repair table called REPAI R_HACKTBL on the DEFADEMO tablespace.The statements are








See pic below

Then we use the check_object procedure as follows











END;See pic below

Selecting specific columns from REPAIR_HACKTBL we get the output above.

The block id is 20 and the block description says that the block is marked ‘true’ for corruption.  Check_object indeed has marked the corrupt block in this case. But to mark the block we use DBMS_REPAIR.FIX _CORRUPT_ BLOCKS  as shown below.












The corrupted block has to be marked corrupt using the above steps. But in our  case it seems  check_object itself is capable of the the pic shows 0 blocks marked on running this  procedure.

The last step to select the table after skipping the corrupted blocks using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.









On selecting  for rows, only after the above procedure is executed,we get to see the rows as in pic below.

The sequel to this article will be  ‘RMAN Backups to repair Block Corruption’ which will be posted next.

Creative Commons License
Technical writes by KANCHANA SELVAKUMAR is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 India License.