Skip to Main Content
  • Questions

  • Tuning performance of a plsql procedure

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: April 24, 2017 - 7:58 pm UTC

Last updated: April 27, 2017 - 1:21 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,
I was once asked in an interview, that a plsql procedure which was running smoothly and properly, suddenly went slow on a particular day and was not running the way it has been.

I tried answering and said

1.)To compute statistics of all tables being used in the procedure's SQL queries , so that if any of the indexes ate not working properly, they will start working using DBMS_STATS.
2.)To check and finding the query which is causing the trouble and to compare it's execution plan, with the preprod environment and if they are different then to tune the query causing trouble.


Is there a better method to find the problem in a plsql procedure, that was running properly before but, stopped running suddenly on a fine day.

Please provide your help.

and Connor said...

I dont know the entire context of your scenario, but I would *not* do 1 or 2 as a first step, because you are potentially immediately changing the problem (could make it better, could make it worse)

So some first steps might be:

a) trace the execution of the plsql. See *where* the slow down is occurring

b) focus on potential causes of *that* slow down. (I'll assume its a SQL)
- get the current plan
- do I have historical information on the SQL to compare against eg AWR information for the plan that it used to be
- if they are different, then why ?
- have the stats changed
- have the stats *not* changed when they should have
- has the data changed dramatically
- has there been a structural change
- has there been a higher level change (db patch, init.ora change, etc)

But notice all of that did *not* change anything... we *investigate* first and only change when we have a solid grounding of evidence to do so

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Joseph Vincent, April 28, 2017 - 5:21 am UTC

Hi,
I got the solution to my question, I will try to learn the stuff which I don't know like reading an AWR file and plsql trace.

Thank you

Regards
Joseph
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library