Home » RDBMS Server » Performance Tuning » procedure running with no end
procedure running with no end [message #361215] Tue, 25 November 2008 06:50 Go to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


Hi to all,

I am using oracle 10.1 version in windows 2003 platform. the problem is when i try to run a procedure in the command prompt it is running continuously with no error or output. Normally this procedure will take half an hour to execute. Four days back it has ran successfully. But from that day it has been creating problem.

I have checked for any session block and there is no such blocks.

Can any one please guide me, where to check why this procedure is running for long time and what is going on in the database.

Thanks in Advance,
Seenu.
Re: procedure running with no end [message #361236 is a reply to message #361215] Tue, 25 November 2008 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does v$session_wait say that the session is waiting on?
Re: procedure running with no end [message #361237 is a reply to message #361236] Tue, 25 November 2008 08:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at all_tables for the tables accesseb by this procedure - were any of them analyzed at around the point when the performance deteriorated?
Re: procedure running with no end [message #361362 is a reply to message #361215] Wed, 26 November 2008 01:28 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
You might have to do a google search for each of these items if you dont know how to do them, but the first steps are:-

Look in V$SQL where users_executing > 0 and see what SQL it is running.

use DBMS_XPLAN to get the current execution plan of that SQL (using the cursor id from V$SQL).

analyze the tables involved inthe sql and re-run the query and check its explain plan.
Previous Topic: Performance tuing using FIRST_ROWS
Next Topic: dbms_stats.gather_table_stats
Goto Forum:
  


Current Time: Sat Dec 10 05:05:00 CST 2016

Total time taken to generate the page: 0.05310 seconds