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?
 
Harish_RajaniConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
ggridleyConnect With a Mentor 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.
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
 
ggridleyConnect With a Mentor 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.
0
 
Harish_RajaniConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.