We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Procedure hanging

ebi168
ebi168 asked
on
Medium Priority
1,431 Views
Last Modified: 2010-05-18
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
Comment
Watch Question

Commented:
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

Author

Commented:
let me see...
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.

Author

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)
Commented:
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.

Author

Commented:
From my monitoring total_time increment by 1 after each run; any suggestion on what DBMS_OUTPUT statement to put there?

Author

Commented:
Maybe you are right total_time is the seconds.
Commented:
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.
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

Author

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.

Author

Commented:
I also rescheduled the job, it got completed but with failures. Again the same thing is fine on the development server.

Author

Commented:
Now it looks like hanging again.

Author

Commented:
Thanks for all your comments. I find out that it is a performance issue.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.