• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

Procedure taking long time to execute

Hello Experts,

I have a package under which I have a procedure with 6 input parameter and one as out parameter .
I have created a PLSQL block and executed with passing all correct values but it is taking very long time to execute but before two days it was executed in seconds with same parameters.

How to check what is causing the issue from database side.
Swadhin Ray
Swadhin Ray
4 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
speed is not about correct paramters, but about indexes and statistics.
you have to check the explain plan for the querie(s), and see what is eventually "missing" ...
you might be using tables that need analyse .
try using analyse [table] compute on the tables that you use on this procedure.
it should improve your perfomance (due to reindexing)
hope that helps
please feed back
Hi Slobaray,
please share the explain plan. that's the first thing you need to check. some of the possible causes is listed below.
1) may be the index is not working.
2) recent change ( bulk data insertion or deletion) in those tables which are being used in the code and don't have any recent statistic. so need to check the statistics.
3) change in init.ora file or in some parameters.
4) may be some background process was running at time you executed the procedure.
5) may be network issue, there is too many traffic in the network.
so once again, try to get the explain plan. also check the server is busy or not.

slightwv (䄆 Netminder) Commented:
What does the procedure do?  You might be running into an object lock.

There are a lot of scripts out there that show you how to track locks.

I would start in the online docs:
Swadhin RaySenior Technical Engineer Author Commented:
Thanks for all your inputs, for which I am sharing points to everyone .
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now