Oracle performance tuning – an update

A tutorial is now available to perform Oracle performance tuning of applications and SQL statements. This tutorial has been expanded to include case studies, which will go a long way in better understanding of concepts explained.

Link to the PDF tutorial here: Tuning.pdf

This current posting is an extension to the existing post on performance tuning, which you can still refer to – for more resources on the topic.


Places of pilgrimage in Sikhism


Recently returned from a visit to Sachkhand Sri Hazur Sahib. This was the last residing place for the Tenth Guru. The journey was enjoyable, and the time spent very gainfully. I wished I could stay a few more days.

On the way, I spent some time thinking about what would constitute the most important place of pilgrimage. I share below some thoughts by the Gurus on this:

  1. The feet of the Holy
  2. Our mind and home
  3. The Lord’s name and discussion of the Divine Word
  4. The congregation of the Holy
  5. Satguru, the Divine Guide

The feet of the Holy:

ਜਨ ਪਾਰਬ੍ਰਹਮ ਜਾ ਕੀ ਨਿਰਮਲ ਮਹਿਮਾ ਜਨ ਕੇ ਚਰਨ ਤੀਰਥ ਕੋਟਿ ਗੰਗਾ ॥
Jan pārbarahm jā kī nirmal mahimā jan ke cẖaran ṯirath kot gangā.
Immaculate and pure are the praises of the humble servants of the Supreme Lord God; the feet of His humble servants are equal to millions of Ganges (sacred shrines of pilgrimage).


Our mind and home:

ਮਨੁ ਮੰਦਰੁ ਤਨੁ ਵੇਸ ਕਲੰਦਰੁ ਘਟ ਹੀ ਤੀਰਥਿ ਨਾਵਾ ॥
Man manḏar ṯan ves kalanḏar gẖat hī ṯirath nāvā.
The Lord’s Name enshrined within, my mind has become a temple. My body is the simple cloth of the humble seeker; and my home is the bathing of a sacred pilgrimage.


The Lord’s name and discussion of the Divine Word:

ਤੀਰਥਿ ਨਾਵਣ ਜਾਉ ਤੀਰਥੁ ਨਾਮੁ ਹੈ ॥
Ŧirath nāvaṇ jā▫o ṯirath nām hai.
Lets all bathe at sacred shrines: The Naam, the Name of the Lord, is the sacred shrine of pilgrimage.
ਤੀਰਥੁ ਸਬਦ ਬੀਚਾਰੁ ਅੰਤਰਿ ਗਿਆਨੁ ਹੈ ॥
Ŧirath sabaḏ bīcẖār anṯar gi▫ān hai.
My sacred shrine of pilgrimage is contemplation of Divine Words, which leads to spiritual wisdom within.


The congregation of the Holy:

ਰਾਮਦਾਸਿ ਸਰੋਵਰ ਨਾਤੇ ॥
Rāmḏās sarovar nāṯe.
By bathing in a pool of God Servants
ਸਭ ਲਾਥੇ ਪਾਪ ਕਮਾਤੇ ॥੨॥
Sabẖ lāthe pāp kamāṯe. ||2||
all the sins committed are washed off.


Satguru, the Divine Guide:

ਤੀਰਥੁ ਪੂਰਾ ਸਤਿਗੁਰੂ ਜੋ ਅਨਦਿਨੁ ਹਰਿ ਹਰਿ ਨਾਮੁ ਧਿਆਏ ॥
Ŧirath pūrā saṯgurū jo an▫ḏin har har nām ḏẖi▫ā▫e.
The True Guru is the Perfect Pilgrimage, Who remains ever absorbed in the Lord’s Name – night and day.


So, what does this mean? Should we not visit physical places of pilgrimage like Hazur Sahib?
We should because our mind has not yet been purified, and needs to understand the Guru’s Word. However, when we are at a place of pilgrimage, we should engage in these True Pilgrimage activities and not just enjoy visiting and babbling. Sikhism is perhaps unique in this respect.

Gurbani Plaque

The Plaque
The Plaque

It has been some time since my last post on this blog, things have been quite busy. Today I am sharing a Gurbani Plaque with you all, for the purpose of printing and putting at home or office.

Its available in SVG format as well, which means it can be enlarged to any size, as large as needed. For those without the knowledge on using this format, a JPG format is also available (which is a simple image):

Finally, a PDF format is also available – which also stores the image as a vector, and hence can be enlarged:

Please do share your thoughts, as always.



I have written about many Indian festivals, but never about Holi. It is the Hindu festival of colours, much like Diwali being the festival of lights.

For Sikhism, colours are strong depictions of mental state. There is no physical relevance to colours. A Sikh is expected to wear ordinary clothes that do not attract attention. Neither is playing with colours encouraged.

A key ingredient used during holi is called Gulal. Its a deep red coloured powder which is used to colour others. The Guru says this about Gulal:

ਚੀਤਿ ਆਵੈ ਤਾਂ ਰੰਗਿ ਗੁਲਾਲ ॥
When my mind is attached to Him, I am dyed in the deep crimson of His Love.

In the daily Sikh prayer, the first reference to the word colour appears in Japuji Sahib.

ਭਰੀਐ ਮਤਿ ਪਾਪਾ ਕੈ ਸੰਗਿ ॥
But when the intellect is stained and polluted by sins committed by us,

ਓਹੁ ਧੋਪੈ ਨਾਵੈ ਕੈ ਰੰਗਿ ॥
it can only be cleansed by the colour (Love) of His Name.

Sri Guru Granth Sahib recognises two colours: Kusumbh and Majith. Kusumbh is light orange in colour, while Majith is dark red, even crimson. Kusumbh is the color of a flower (Butea monosperma) called “flame of the forest” (called FF henceforth in this post). When its immersed in water for a few hours, the flower looses its colour, and the water becomes coloured.

When Guru Nanak was visiting Mecca, he was asked whether Hinduism was the greater religion or Islam. His reply was as under:

ਕਚਾ ਰੰਗੁ ਕਸੁੰਭ ਦਾ ਪਾਣੀ ਧੋਤੈ ਥਿਰ ਨ ਰਹੋਈ।
As the colour of FF flower is impermanent and is washed away in water, likewise the colours of religiosity are also temporary.

To the contrary, majith is permanent. Its effects are not physical, rather its colouring is for the soul:

ਮਨਮੁਖੁ ਰੰਗੁ ਕਸੁੰਭੁ ਹੈ ਕਚੂਆ ਜਿਉ ਕੁਸਮ ਚਾਰਿ ਦਿਨ ਚਾਗਾ ॥
The self-willed manmukh is like the false color of the FF flower, which fades away; its color lasts for only a few days.

ਖਿਨ ਮਹਿ ਬਿਨਸਿ ਜਾਇ ਪਰਤਾਪੈ ਡੰਡੁ ਧਰਮ ਰਾਇ ਕਾ ਲਾਗਾ ॥੨॥
He perishes in an instant; he is tormented, and punished by the Righteous Judge of Dharma. ||2||

ਸਤਸੰਗਤਿ ਪ੍ਰੀਤਿ ਸਾਧ ਅਤਿ ਗੂੜੀ ਜਿਉ ਰੰਗੁ ਮਜੀਠ ਬਹੁ ਲਾਗਾ ॥
The Lord’s Love, found in the Sat Sangat, the True Congregation, is absolutely permanent, and colorfast.

ਕਾਇਆ ਕਾਪਰੁ ਚੀਰ ਬਹੁ ਫਾਰੇ ਹਰਿ ਰੰਗੁ ਨ ਲਹੈ ਸਭਾਗਾ ॥੩॥
The cloth of the body may be torn to shreds, but still, this beautiful color of the Lord’s Love does not fade away. ||3||

ਹਰਿ ਚਾਰ੍ਹਿਓ ਰੰਗੁ ਮਿਲੈ ਗੁਰੁ ਸੋਭਾ ਹਰਿ ਰੰਗਿ ਚਲੂਲੈ ਰਾਂਗਾ ॥
Meeting with the Blessed Guru, one is dyed in the color of the Lord’s Love, imbued with this deep crimson color.

So how does the Guru celebrate Holi. The answer is obvious:

ਹੋਲੀ ਕੀਨੀ ਸੰਤ ਸੇਵ ॥
Holī kīnī sanṯ sev.
I celebrate the festival of Holi by serving the Saints.

ਰੰਗੁ ਲਾਗਾ ਅਤਿ ਲਾਲ ਦੇਵ ॥੨॥
Rang lāgā aṯ lāl ḏev. ||2||
I am imbued with the deep crimson color of the Lord’s Divine Love. ||2|

Finally I want to share another related hymn:

ਲਾਲ ਰੰਗੁ ਤਿਸ ਕਉ ਲਗਾ ਜਿਸ ਕੇ ਵਡਭਾਗਾ ॥
One is dyed in the (red) color of the Lord’s Love, by great good fortune.

ਮੈਲਾ ਕਦੇ ਨ ਹੋਵਈ ਨਹ ਲਾਗੈ ਦਾਗਾ ॥੧॥
This color is never muddied; no stain ever sticks to it. ||1||

ਪ੍ਰਭੁ ਪਾਇਆ ਸੁਖਦਾਈਆ ਮਿਲਿਆ ਸੁਖ ਭਾਇ ॥
He finds God, the Giver of peace, with feelings of joy.

ਸਹਜਿ ਸਮਾਨਾ ਭੀਤਰੇ ਛੋਡਿਆ ਨਹ ਜਾਇ ॥੧॥ ਰਹਾਉ ॥
The Celestial Lord blends into his soul, and he can never leave Him. ||1||Pause||

ਜਰਾ ਮਰਾ ਨਹ ਵਿਆਪਈ ਫਿਰਿ ਦੂਖੁ ਨ ਪਾਇਆ ॥
Old age and death cannot touch him, and he shall not suffer pain again.

ਪੀ ਅੰਮ੍ਰਿਤੁ ਆਘਾਨਿਆ ਗੁਰਿ ਅਮਰੁ ਕਰਾਇਆ ॥੨॥
Drinking in the Ambrosial Nectar, he is satisfied; the Guru makes him immortal. ||2||

Oracle: Snapshot too old?

Berlin Wall
Berlin Wall
Okay, so you have received the Oracle error ORA-01555 Snapshot Too Old and have no clue how to go about resolving it? This post is made for you then. (The first time an application developer has written about this rather than a DBA.)

First, why does this occur? When you run a query, Oracle retains that data in a “snapshot”. The underlying tables in that query might continue to get changed, but you will see the data as it was when you executed the query. You can keep moving back and forth (rows) within the snapshot using the cursor. However, as you might expect: Oracle cannot hold that snapshot for ever. For how long it retains the snapshot is defined via the UNDO_RETENTION parameter.

So one way to solve this problem might be to increase the limit defined by this parameter. However, that is not always the best solution.

This problem normally occurs when a process opens a cursor (by running the query), and processes each row one by one. For example, let’s assume the process runs a query that returns 10000 rows. Processing each row takes, on average, 10 seconds. It goes on to the next row after processing the previous. Hence the total processing of all these rows will take around 28 hours. If your UNDO_RETENTION is defined as 10 hours, this process will fail on the snapshot too old error.

One of the best ways to solve this problem is to execute performance tuning on this process. This should be carried out specifically on the part of the processes that runs within the query in question, and should be targeted at reducing the time it takes to process one row. For example, if we can get our processing time down to 3 seconds, we will be done within about 8.5 hours, which is below our current setting for UNDO_RETENTION. In most cases, this can actually be done. (Read more here and here.)

A second way to solve the problem is to use a temporary table. For example, suppose you want to analyse all open purchase orders. From the table containing POs, pull the ones that are open, and put them into the temporary table. Since the temporary table is being used only by your process, Oracle will not have to hold the “snapshot” for you. Again the main driver query is the candidate for putting into temporary table. This will also make your process faster overall if it’s based on a not-so-small subset of a large table.

However, a third solution is also possible. For our problem we had a process that had to run for days and days, rather than doing something and finishing. So obviously, we got this error.

To solve the problem, we exited the loop after every n rows, and then reentered it. For example, if the pseudocode looked as below prior to the fix:

select something from somewhere;
while (rows) {
  do process

We changed it as below:

hasAtleastOneRow = True;
while (hasAtleastOneRow) {
  hasAtleastOneRow = False;
  select something from somewhere where rownum<n;
  while (rows) {
    do process
    hasAtleastOneRow = True;

Note that the SELECT statement must have a mechanism to prevent picking up rows that have already been processed earlier. This could be a flag-condition or ‘check’ another table. For example:

select po_id from po_table where po_status='O' and rownum<100
and not exists(select 1 from po_temp where po_table.po_id = po_temp.po_id)

As part of the 'do process' then, we should insert into po_temp.

How do we select the value of 'n'? You will have to do some timing and hit-and-try here. Try to keep the highest value that is guaranteed to take lower processing time compared to the undo retention window.

Kabit Savaiye by Bhai Gurdas

Kabitt Bhai Gurdas
Kabitt Bhai Gurdas

Bhai Gurdas is well known for his writing called Varan Bhai Gurdas. I have written about this text before. Today I will talk about a lesser known text, also authored by him, called ‘Kabit Savaiye‘. Below is one of the popular hymns from this book which was composed when Bhai Gurdas was away from the Guru for his language learning:

ਸੁਪਨ ਚਰਿਤ੍ਰ ਚਿਤ੍ਰ ਬਾਨਕ ਬਨੇ ਬਚਿਤ੍ਰ ਪਾਵਨ ਪਵਿਤ੍ਰ ਮਿਤ੍ਰ ਆਜ ਮੇਰੋ ਆਏ ਹੈ ।
Himself holy and capable of making other pious – the friendly True Guru has come into my dream beautifully attired and adored. It is indeed a wonderful marvel for me.

ਪਰਮ ਦਇਆਲ ਲਾਲ ਲੋਚਨ ਬਿਸਾਲ ਮੁਖ ਬਚਨ ਰਸਾਲ ਮਧੁ ਮਧੁਰ ਪੀਆਏ ਹੈ ।
Ever and profoundly benevolent, big eyed – his utterances sweet like honey.

ਸੋਭਿਤ ਸਿਜਾਸਨ ਬਿਲਾਸਨ ਦੈ ਅੰਕਮਾਲ ਪ੍ਰੇਮ ਰਸ ਬਿਸਮ ਹੁਇ ਸਹਜ ਸਮਾਏ ਹੈ ।
He has honoured me by placing me on the throne of His chest. I am lost in the love filled trance of Nam Amrit that merged me into a state of equipoise.

ਚਾਤ੍ਰਿਕ ਸਬਦ ਸੁਨਿ ਅਖੀਆ ਉਘਰਿ ਗਈ ਭਈ ਜਲ ਮੀਨ ਗਤਿ ਬਿਰਹ ਜਗਾਏ ਹੈ ॥੨੦੫॥
Enjoying the bliss of divine dream, I was woken up by the voice of rain-bird. The awe and marvel of love-filled state disappeared reawakening the pangs of separation (like a fish feels out of water).

Enjoy a beautiful rendering below:

Releasing: Gurbani search for mobile


Gurbani searching on the mobile used to be tough. Symbian and Android phones do not support Unicode with Indic support, which is required to use gurmukhi websites. Opera Mini allowed us to read gurbani, but entering search text is a different matter.

I have developed a web page that allows you to do this, but rather by entering roman letters. For example, enter ‘k’ instead of ‘ਕ’.

To use this go to:


Mobile users can use this within Opera Mini (with a configuration setting change as shown here: enter config: as a URL which takes you to advanced settings page. Set Use bitmap fonts for complex scripts to Yes and then Save), for best results. Opera Mini uses cloud computing to render Gurmukhi text, and therefore doesn’t need that support on the mobile itself.

That being said, this tool can be used anywhere you like – on the desktop or any mobile browser. Detailed instructions for using it are on the page itself.

This is envisaged to be updated in future to allow search options powered by various other websites, as and when more powerful search is available. Click here for a short history of Gurbani search.

Bandi Chhor Diwas

Bandi Chhor Diwas
Bandi Chhor Diwas

Guru Hargobind Sahib Ji, sixth Sikh Guru emerging from Gwalior Fort where he was imprisoned by Emperor Jahangir. Along with him, 52 Hindu chieftains were also released, on request from the Guru. Sikhs celebrate Diwali festival as “Bandi Chhor Diwas” because the Guru reached Amritsar back on the Diwali day. For the full story visit

Very few people know that this day is also important for Sikhs because of martyrdom of Bhai Mani Singh. He transcribed the final version of Guru Granth Sahib dictated to him by Guru Gobind Singh in 1704.

Selecting affiliates


Learnt the hard way that selection of affiliates is something that should be carefully done. Especially on the web, where not many sites have a clear business model.

Why is the business model of the affiliate important for us? Should we not choose the one that costs us less, yet has good features? The answer is no. Sooner or later the service will be forced to shut shop, and you might end up without options. Especially if there is a use of a technology which will lock you out of options apart from redeveloping your product/service.

I have been using for several years for showcasing image galleries. The site did charge pennies and provided a rich feature set including ability to integrate with your own domain. Its closed now, and all links to those photos on my blog and at other places are out of order. I also have lost pictures – though I will have them in my archive, but searching for each and putting together galleries based on topics (rather than dates) will be a very uphill task.

At least I am wiser now!

We do need competition

Apple is in the news:

  • Apple, Samsung overtake Nokia in smartphone market
  • Apple Has More Money On Hand Than The US Government

Nokia also:

  • Nokia reports loss of €368m

While Nokia is still the leader in phone market, it has lost the top place in the smartphone market.

In any market, competition is good and monopoly is bad for customers. With the failure of Nokia to be that competition, it may goad Apple into being even more dollar hungry. Nokia always builds phone that integrate well with other non-Nokia technology: be it tethering, a much better file system (though this point is debatable), or support for memory cards. Apple builds devices that integrate badly with non-Apple hardware. Three versions of iPad, each with a different diskspace capacity could have been avoided simply by adding support for cards. Similarly iPad only offers a crippled version of Bluetooth.

I am not against Apple: at this time it definitely has the better product on its shelves. However, we should not write off Nokia either: with our support it can regain the slot that it had, and deserves.

Nokia – continue to build open devices please. In my opinion the following are the most important of the steps Nokia should take:

  1. Stop creating new, incompatible flavours of Symbian. Select one OS for all $200+ devices, and stick to that. Focussing on one platform will help improve it
  2. Support Unicode. iOS has done that for ages
  3. Improve the dialer: the phone is used most for making phone calls. Nokia dialer is from the stone age (Nokia recently pushed an update that improved this big time)
  4. Make the phone snappy even if it means having a faster processor
  5. Let users select which browser they want as default. Let them set Opera as default. This will be the winning stroke over iPhone, which allows only Safari as default

Licensing and information about the blog available here.