Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure Running Continuously (Oracle 10g)
Stored Procedure Running Continuously [message #356970] Mon, 03 November 2008 06:35 Go to next message
madhava.maganti
Messages: 4
Registered: February 2008
Location: Mumbai
Junior Member

We have one preparation stored procedure with set of DDL (Insert,Update Delete) and TCL(Commit, Rollback) statements. This stored procedure is calling by one of the Informatica workflow. Now the problem is stored procedure sometimes will take long time(upto 6hrs) to finish the process and the same stored procedure sometimes will also finish the execution in between 15 to 20 mins.

We are having this issue from past 6 months but from past 6 months we have a temporary solution to rebuilding indexes and analyzing the statistics manually. But now we automate that process (Rebuilding Indexes) it in one stored procedure. Even though we are facing same problem(take long time to finish the process). Please advice how should I overcome this issue.

Thanks in Advacne!
Madhava
Re: Stored Procedure Running Continuously [message #356984 is a reply to message #356970] Mon, 03 November 2008 07:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1) Trace the session to see WHAT takes this long.
2) Have a look at the open waits while the procedure is running.
3) Have a look at the open locks while the procedure is running.

More information about that can be found in the sticky post of the "Performance Tuning" section of the forum.

Insert,Update Delete are DML by the way, not DDL.

Re: Stored Procedure Running Continuously [message #357216 is a reply to message #356984] Tue, 04 November 2008 05:33 Go to previous messageGo to next message
madhava.maganti
Messages: 4
Registered: February 2008
Location: Mumbai
Junior Member

Thanks for your reply and sorry for my typing mistake for DDL instead of DML.

1) Trace the session to see WHAT takes this long.
[Madhava]-> In my observation Cursor will take long time. Logic behind this cursor is to calculate the book value amount. One thing I would like to say here...Cursor use the Historical table data..Historical table contains up to 2 lakhs of records.
2) Have a look at the open waits while the procedure is running.
[Madhava]->I am not having a idea how I should look open wait please suggest me how should I find it.
3) Have a look at the open locks while the procedure is running.
[Madhava]-> In open locks I am able to see list of Open cursor statements.

Re: Stored Procedure Running Continuously [message #357224 is a reply to message #357216] Tue, 04 November 2008 05:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
On the open waits, this is the query I use when someone calls me and tells me "my application hangs!" :

select DISTINCT s.sid,s. SERIAL#, event, p1text, p1, p2text, p2, 
       p3text, p3, wait_time, seconds_in_wait, state,
       osuser, program,client_info, s.MODULE, sql_text
  from v$session_wait e
  join v$session      s ON s.sid        = e.sid
  join v$sql          q ON q.hash_value = s.sql_hash_value
 where event not in (
      'SQL*Net message from client', 'SQL*Net message to client', 
      'jobq slave wait','smon timer','rdbms ipc message')
   AND event not like 'PX Deq Credit%';


Execute that every few seconds, and see which SQLs are responsible for waits at that moment.

I have a hunch it might be a "db file scattered read" or "db file sequential read" wait on the datafiles the historical table is in.

Then have a look at the execution plan of the query in the SQL_TEXT column. See if it can be sped up with indexes or maybe rewriten in another way.
Re: Stored Procedure Running Continuously [message #357265 is a reply to message #356970] Tue, 04 November 2008 08:58 Go to previous message
madhava.maganti
Messages: 4
Registered: February 2008
Location: Mumbai
Junior Member

Cool
I was tried to ran procedure thrice but fortunately (or) unfortunately procedure has finished it in 11 mins..Thanks for your assistance and I will continue the questioning if procedure will give trouble..
Previous Topic: ERROR at line 1: ORA-00947: not enough values
Next Topic: Issue with Definer's Rights
Goto Forum:
  


Current Time: Sat Dec 03 06:13:20 CST 2016

Total time taken to generate the page: 0.07377 seconds