Yes,it is my first presentation after joining the company.Initially I thought I am not the correct person for giving a presentation on BEST practices of PL/SQL,which I think can be given only by Mr.Steven.Since the audience were a set of freshers, joining our project .I made up my mind to share the experience in PL/SQL so that they wont follow any bad practices in PL/SQL.
I was very clear that what ever the bad practices I have seen and which is screwing the performance of the system should not be repeated.So I prepared my presentation with points like
• Take more time in designing
• Follow coding standard
• Avoid hard coding
• Avoid writing more SQL
• Write tiny chunk of code
• Don’t repeat anything
I was happy to share what I have learned from Steven's presentation.Since all were freshers I was not getting more questions from the audience.But they were listening well and responded, whenever I asked then some questions.It was a bit of tumbling start(coz its my first time) but as I move on to concepts like FORALL and BULKCOLLECT I was pretty comfortable and I started delivering steadily.I have given examples of codes I have written and problem we have faced while supporting our database.I explained them the importance of modularizing the logic into small blocks of code and reusing it where ever required."please don't dump your logic inside a single procedure",I urged.
I mentioned the importance of having repeated SQLs inside a function.So that hard parsing of frequently executed queries will be avoided and performance will improve.I remembered the fact that one SQL has executed 20 thousand time and still was in the data buffer cache(mentioned by Steven). I stressed them to have a separate exception handling and error logging procedure.I substantiate my point with some examples.I suggest them to log as more data as possible when an exception has occurred which could help them in debugging and problem.At the end I stressed with high vocal,
"Don't repeat anything and write tiny chunk of code ".
It was a like sound of a guy who got screwed by worst design and bad coding practice.It gives you real satisfaction if you share something you know to others.I thanked Anoop , my friend for giving this opportunity.