Performance Tuning PLSQL – When, Where and How?

29 Aug

Seasons Greetings!
I am a newbie in the world of TUNING Oracle plsql application, though relatively experienced in Oracle Development and Administration.

Recently I have been exploring various options in tuning plsql application. I had come across many questions on this topic. Should we tune plsql application during development phase on Development evironment or should we consider postponing it for QA/PRODUCTION environment?

This post is on tuning options and techniques which I experienced so far in all these years.

I am surprised and astonished to find that tuning is such critical yet neglected aspect in PL/SQL Application.

Developers during development are more involved in implementing the functionality in their code. Trying to bridge the gap in the coding and functional requirement they tend to ignore the tuning aspect. This is worsened due to the fact that the development phase does not consider the timelines required for tuning.

Due to deadlines and shortened timelines tuning takes backseat during development phase.

I believe that tuning is more of an iterative process. Each environment requires different levels of performance tuning based on priority. My approach would be :

1. During Development first round of code to implement the functional requirements. Then second round to make sure that best coding practice are followed with proper indexes, FOR ALL replacing FOR cursors, BULL COLLECT where ever required, OPTIMIZING the SQLs in PLSQL code. Tuning is restricted in this environment due to the fact that we have less data to play with.
Dwayne I would say that inserting 10000 rows in one table is not a big deal but when you dealing with 100+ tables in a Datawarehouse environment it would become a nightmare in itself.

2. During QA with quiet number of data generated during testing it prepares a good platform to start tuning with application data.
It still has some bottlenecks as organization don’t give access to QA environment to one and all and developers are not allowed to play with the code that is released for testing until its complete. So limited access to development team is available.

I would like if people out there can suggest what would be the approach at this level and tools which can be used to perform tuning at this stage.

3. During Production code ought to be more stable with performance of the hardware being much better than QA/DEV box the code would run with better performance as compared to DEV/QA. Moreover developers don’t have control on production database as rights rests with DBA and hence it becomes their baby to look after.

My question here is whether DBA is required to have understanding of the functionality of code executing on Production or when deployed on production?
As functional understanding decides the formation of SQL in PLSQL and the tuning approach generally revolved around the SQL. To what extent DBA is required to tune the code on production?

Looking for replies and comments on this till I find them myself.
Leave a comment

Posted by on 29 August, 2009 in Oracle


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: