Re: Stored Procedure Performance

Joe, If you can do the following to obtain a stack trace of the server thread I might be able to tell you more: Use onstat -g ath to look at the state of the thread (Repeat it a couple of times to get an impression of any changes in state). If the state is running and you are running on a 7.x version do a "kill -7 vp-pid". Use <n>cpu from onstat -g ath and onstat -g glo to find vp-pid. If you are running version 9.x use "onmode -X stack vpid" instead of kill (vpid is the vp number n from <n>cpu). If the state is not running use "onstat -g stk <thread-id>" to get a stack trace. Send me the onstat -g ath and stack trace output. If you have problems doing this let me know. Michael Joe Pacelli wrote: > I've got a stored procedure which is called from a VB application on > the clients machine. This SPL takes about 2-3 seconds to run. I can > run this SPL over and over and recieve 2-3 second results. Then all of > sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It > will then eventually return back to the normal 2-3 seconds. > The client has 2 databases on this instance. One a live and one a > test. The live has no problems and does not see this issue. While > running this on test I've ran onstat and found no exclusive locks, yet > it just sits there. There is no one else hitting this test base other > than myself. > > Any ideas of what to look for. > > I've put set explain with stored procedure and it revealed nothing. > > Thanks, > Joe P > Senior Software Engineer > -- === Michael Mueller ================== Tel. + 49 8171 63600 Fax. + 49 8171 63615 Web: http://www.mm.kay-mueller.de http://www.planets.kay-mueller.de ====================================== sending to informix-list