Long Running Procedure

[oraclevijay]

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,
vijay

[davey23uk]

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,
vijay

trace it, see what it is doing - plus enable the debug logging you put it in so you know where it is failing

[Mr.Hanky]

10G OEM can be very useful for stuff like this, trap the SQL, run the advisor on it and see what pops up. Sometimes it's very useful, sometimes you are better off baking brownies.

[sqlany]

1、you should check the alert file
2、query some views (v$session_wait,v$session_longops,etc.)
3、see your os system and find the bottle-neck, whether IO or CPU,or others.
first of all,you can do these.
without these information,i believe few person could real help you.

[oraclevijay]

hi Sqlany,

As per your guidance i have checked my alert log file and i found nothing related to this session hangs. And i have queried V$session_longops and i found no long running sql in the database. When i checked for OS process oracle has not exceeded 400MB of memory.

Our Database configuration is,

version : 10.1.0.2.0
Os : Windows 2003 Server
Ram Size : 8GB.

The session hangs when we try to run a package. Actually this package will take 30 minutes to complete the process.

Please let me know what are the parameters to be focussed on V$session_wait view.

I have generated statspack report at the time of the process. Please let me know what are things i have to look in the report to find the root cause of the problem.

Thanks in Advance,
Vijay.

[oraclevijay]

trace it, see what it is doing - plus enable the debug logging you put it in so you know where it is failing

HI Davey,

I have traced the session and found that some of the insert and update queries running in that particular session and i don't find procedure completion sign. What might be the problem.

Also please tell me know how to enable the debug logging.

Thanks in Advance,
Vijay.

[davey23uk]

HI Davey,

I have traced the session and found that some of the insert and update queries running in that particular session and i don't find procedure completion sign. What might be the problem.

Also please tell me know how to enable the debug logging.

Thanks in Advance,
Vijay.

surely you put some logging into your code so you know what happens when it is going wrong?

[malay_biswal]

Part from trace cheking i would suggest to check the AWR report also and compare with AWR report (when last time thix procedure executed 30 min). You also run perfmon to monitor CPU, Memory utilization in your D/B.