Procedure hanging

Hi,

I got a procedure that hangs on the production server but runs ok on the development server. The procedures are exactly the same. Could anyone tell me what is going on. Thanks
ebi168Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ggridleyCommented:
You could have a lock.  Check out this link for how to find out if there are any locks:

http://www.experts-exchange.com/Databases/Oracle/Q_21787127.html
0
ebi168Author Commented:
let me see...
0
Harish_RajaniCommented:
Locks could be one possibility:

select object_name, os_user_name, session_id from all_objects,
v$locked_object
where all_objects.object_id = v$locked_object.object_id
group by object_name, os_user_name, session_id
order by 1,3

If you see the same table against different session ids. You can be sure that there is a lock on table. In such case kill either of the session to continue.

Other possibility could be very slow performing queries in your procedure, which would work okay on development because of less data, but would simply fail to digest the production load.

You might like to put some DBMS_OUTPUT statments and direct the output to log file to establish exactly which part of procedure is underperforming

Rgds,
HR
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ebi168Author Commented:
After I killed the session, the locks are all gone, os process is also gone. So I don't know if performance might be one issue. It is however pretty quick last week.
0
ebi168Author Commented:
The other thing is if I schedule this procedure as a job in toad, the TOTAL_TIME parameter(which means the number of times the job has been executed), is growing like wild fire. In a minute it grows to over 100 times, and the FAILURES parameter is always empty (which should be 0 if last time is successful; or a number indicating times of failure)
0
ggridleyCommented:
Total_time is seconds I believe, so 100 just means that it has been running for 100 seconds.  So when you restart the instance or kill the locks and run it again, does it run?  

You could try what HR suggested if there aren't any locks and it appears to be running.  Put in some DBMS_OUTPUT statements to figure out where its at.  

You could also do have the DBA do a trace while its running to see what queries are poorly tuned.
0
ebi168Author Commented:
From my monitoring total_time increment by 1 after each run; any suggestion on what DBMS_OUTPUT statement to put there?
0
ebi168Author Commented:
Maybe you are right total_time is the seconds.
0
ggridleyCommented:
DBMS_OUTPUT.PUT_LINE will only work if you manually execute the procedure from sqlplus.  It won't output if you run it from a job.  And it will only output once the execution is done.  

Here is a link to DBMS_OUTPUT stuff ->http://www.experts-exchange.com/Databases/Oracle/Q_20281081.html

In our workplace we have an errorlog table which is inserted into (using autonomous_transaction) so we can see debug points in real time as the execution of a package is running.   You may want to consider that,   or get the DBA to run a trace while you run it to look for bad queries.
0
Harish_RajaniCommented:
well caught ggridley!!
DBMS_OUTPUT.PUT_LINE  output will not be shown unless sending procedure finishes execution,, which does not seem to happen in this case.


Why not try following query on sql prompt to identify the culprit:

select rows_processed, sql_text from v$sqlarea
where users_executing > 0;

Once we know what is the sql_text thatz causing the procedure to hang, we can suggest some solution.

I however suspect, it must be a bulk update query on the table which inturn has a trigger (on update) such that causing  a recursive lock.
You say the same procedure was running fine last week. Has been there any change to the schema or table since then ?

Rgds,
HR

0
ebi168Author Commented:
I however suspect, it must be a bulk update query on the table which inturn has a trigger (on update) such that causing  a recursive lock.

--There is something related to trigger, but I don't know if it is causing the problems. I am trying to load one table which has triggers to insert into a table on Monday morning, say T1. It is taking so long that I eventually killed it. This procedure is a scheduled job at that time(every 2 hours). It uses records in T1 to put into another table (insert into T2 select ...from T1...). So trigger and procedure could possible happened at the same time. Then I noticed the failures for the job is 3. So I dropped the job, then things are like what is now.
0
ebi168Author Commented:
I also rescheduled the job, it got completed but with failures. Again the same thing is fine on the development server.
0
ebi168Author Commented:
Now it looks like hanging again.
0
ebi168Author Commented:
Thanks for all your comments. I find out that it is a performance issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.