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.

Report message to a moderator

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?

Report message to a moderator

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?

Report message to a moderator

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.

Report message to a moderator

Previous Topic: Performance tuing using FIRST_ROWS
Next Topic: dbms_stats.gather_table_stats
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 10:41:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.