Oracle PL/SQL Tuning for the verdant Application Developer

Hi,

Based on my personal experiences, I have written the following article – Oracle PL/SQL Tuning for the verdant Application Developer, wherein I have mentioned few interesting things I learned on the job from March 2009 to December 2009.

(Photograph courtesy Arjun Suri)

My purpose of articulating this piece is to share something that could be of use to those who are new to PL/SQL development, those who wish to know how to spruce up your applications without minimal involvement of the DBA, those who wish to know what basic PL/SQL tuning is about, those who have done PL/SQL tuning at some point of time in history and wish to brush up their concepts, and finally those who aren’t into Oracle but feel that by referring this article to the relevant people in their organization / institution they can reap potential benefits in this field.

If you have general interest in computers and don’t have much idea about what databases are, it could be of use to you as well.

Following are few excerpts from the article.

• Encounter 1 – A reporting application takes two and a half hours to compute results, only to return message that no data was present for the specified criteria’s. The application gets under scanner, upon analysis by an Application Developer, he ____ ____ ___ ____ ___ ___ ___ and the output comes in half a second.
• Encounter 3 – By adding just a small thing that _____ ____ _ _______, the Application Developer improved query’s time four-fold.
• It isn’t necessary that you need to have an _____ on all the columns.
• This would result in severe performance implications as the table grows with time, as the cost would be high due to join_date getting _______ every time and the existing _____ getting bypassed, resulting in a FTS.
• Setting the value too high could affect other _____ ____ on your database.
• As the ______ grow in size, their cost of maintenance increases proportionately.
• There are cases wherein size of the _______ exceed the
  size of the _____ .
• The third answer is given by selected few, and they happen to be the ones who ______ .
• This increases your program’s overall execution time as it _______ .
• Till the data commit isn’t performed, your data is saved onto a _____.

If these blanks interest you, and are curious to know what reside in them, please go ahead and click on the link above. 🙂

(Photograph courtesy Arjun Suri)

The things written in this article are entirely my own, I haven’t copied them from anywhere. I myself worked on those things before recommending them here.

Special Thanks to the people who taught me about PL/SQL tuning when on the job – Divya Gupta, Biswajit Barua, Anindya Lahiri, Venugopal Veldurthy, Abhijeet Ekashinge, Rajesh Shastri, Tom Kyte ;).  And the biggest thanks to Sandeep Jain, for giving me chance to work for him when I was nothing. He took out time from his busy schedule and reviewed this article as well. Without his feedback and words of encouragement, I wouldn’t have been able to take this forward.