Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Procedure taking long time to execute

Posted on 2011-09-19
5
Medium Priority
?
764 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36559525
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" ...
0
 
LVL 13

Assisted Solution

by:stergium
stergium earned 500 total points
ID: 36559536
hello.
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
0
 
LVL 2

Assisted Solution

by:mehuje
mehuje earned 500 total points
ID: 36559537
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.

Regards,
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36559647
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:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/manproc.htm#sthref764
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 36571637
Thanks for all your inputs, for which I am sharing points to everyone .
0

Featured Post

URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question