IDS is busy all the time

Hi, I need a hint. Maybe somebody has had the same or a similar problem: We have an IDS 10 Server which has been running with a load of about 15 % until some days ago. Suddenly (without a config change), the load is > 50 % all the time. We shut down the engine and the load went away, so it is definitely the db and not any other process which is producing the load. How can I find out what is keeping the DB busy ? I tried to go through all sessions in onstat -g sql to see if there is anything uncommon, but I could not find anything. When calling onstat -g act, I can see that the machine is handling mostly only one sqlexec at a time. Other active threads are only sm_poll (1) and tlitcppoll (2). How can I trace, which sqlexec is causing the load ? I can see that the tid shown in onstat -g act is the same for subsequent requests. In onstat -a the tid occurs only in the onstat -g act/ath output. How can I find out what this thread is doing ? IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram Marcus

The obvious starting point (to me anyway) is your stats - are they upto date ? Also do you use Optimiser Directives ? I love optimiser directives, the best performance on a system that has grown siginificantly is to remove them :-) Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Failure is not as frightening as regret. Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Marcus Haarmann > Sent: 09 January 2007 04:37 > To: ids@iiug.org > Subject: IDS is busy all the time [8153] > > > Hi, > > I need a hint. Maybe somebody has had the same or a similar problem: > > We have an IDS 10 Server which has been running with a load > of about 15 % until some days ago. > Suddenly (without a config change), the load is > 50 % all the time. > We shut down the engine and the load went away, so it is > definitely the db and not any other process which is > producing the load. > > How can I find out what is keeping the DB busy ? > I tried to go through all sessions in onstat -g sql to see if > there is anything uncommon, but I could not find anything. > > When calling onstat -g act, I can see that the machine is > handling mostly only one sqlexec at a time. > Other active threads are only sm_poll (1) and tlitcppoll (2). > How can I trace, which sqlexec is causing the load ? > I can see that the tid shown in onstat -g act is the same for > subsequent requests. > In onstat -a the tid occurs only in the onstat -g act/ath > output. How can I find out what this thread is doing ? > > IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram > > Marcus > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the > discussion forum.

Hi, We use update_stats from Art Kagel. Running every night. So stats should not be a problem. Optimiser directives are not the problem, since we are not using them. The application itself performs good, no problematic costs etc. The point is the IDS uses one CPU all the time and I cannot find a user process which is causing this. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Paul Watson Sent: Tuesday, January 09, 2007 11:43 AM To: ids@iiug.org Subject: RE: IDS is busy all the time [8154] The obvious starting point (to me anyway) is your stats - are they upto date ? Also do you use Optimiser Directives ? I love optimiser directives, the best performance on a system that has grown siginificantly is to remove them :-) Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Failure is not as frightening as regret. Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Marcus Haarmann > Sent: 09 January 2007 04:37 > To: ids@iiug.org > Subject: IDS is busy all the time [8153] > > > Hi, > > I need a hint. Maybe somebody has had the same or a similar problem: > > We have an IDS 10 Server which has been running with a load of about > 15 % until some days ago. > Suddenly (without a config change), the load is > 50 % all the time. > We shut down the engine and the load went away, so it is definitely > the db and not any other process which is producing the load. > > How can I find out what is keeping the DB busy ? > I tried to go through all sessions in onstat -g sql to see if there is > anything uncommon, but I could not find anything. > > When calling onstat -g act, I can see that the machine is handling > mostly only one sqlexec at a time. > Other active threads are only sm_poll (1) and tlitcppoll (2). > How can I trace, which sqlexec is causing the load ? > I can see that the tid shown in onstat -g act is the same for > subsequent requests. > In onstat -a the tid occurs only in the onstat -g act/ath output. How > can I find out what this thread is doing ? > > IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram > > Marcus > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

What we found out in the meantime: There was a crash of the DB some days ago. Since then, a single session is in the session list, which uses the CPU. The db did start up after the crash (CTX_MEM) without any errors. The session is in the session list even after a server restart (the client-process is long gone). It has no database opened, but a transaction connected (in onstat -x I found it). This session cannot be killed by onmode -z, the transaction cannot be killed with onmode -Z (transaction busy). So, if not anybody knows a trick how to kill this "zombie" session (e.g. rebuild sysmaster in single user ?), I have to unload all the DBs and re-setup the instance. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Marcus Haarmann Sent: Tuesday, January 09, 2007 12:02 PM To: ids@iiug.org Subject: RE: IDS is busy all the time [8155] Hi, We use update_stats from Art Kagel. Running every night. So stats should not be a problem. Optimiser directives are not the problem, since we are not using them. The application itself performs good, no problematic costs etc. The point is the IDS uses one CPU all the time and I cannot find a user process which is causing this. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Paul Watson Sent: Tuesday, January 09, 2007 11:43 AM To: ids@iiug.org Subject: RE: IDS is busy all the time [8154] The obvious starting point (to me anyway) is your stats - are they upto date ? Also do you use Optimiser Directives ? I love optimiser directives, the best performance on a system that has grown siginificantly is to remove them :-) Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Failure is not as frightening as regret. Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Marcus Haarmann > Sent: 09 January 2007 04:37 > To: ids@iiug.org > Subject: IDS is busy all the time [8153] > > > Hi, > > I need a hint. Maybe somebody has had the same or a similar problem: > > We have an IDS 10 Server which has been running with a load of about > 15 % until some days ago. > Suddenly (without a config change), the load is > 50 % all the time. > We shut down the engine and the load went away, so it is definitely > the db and not any other process which is producing the load. > > How can I find out what is keeping the DB busy ? > I tried to go through all sessions in onstat -g sql to see if there is > anything uncommon, but I could not find anything. > > When calling onstat -g act, I can see that the machine is handling > mostly only one sqlexec at a time. > Other active threads are only sm_poll (1) and tlitcppoll (2). > How can I trace, which sqlexec is causing the load ? > I can see that the tid shown in onstat -g act is the same for > subsequent requests. > In onstat -a the tid occurs only in the onstat -g act/ath output. How > can I find out what this thread is doing ? > > IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram > > Marcus > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Bodgan, Yes, I am sure. The session was started at 09:27, last access was 09:33, which mets the crash timepoint. No locks. According to onstat -g sql, the session does nothing. In onstat -g ses, I can see that the session is not connected to any DB, but has two memory pools. Even the information about client-process, Terminal (was a dbaccesss, I suspect), are there. The syssesprof has 0 in all columns. Marcus -----Original Message----- From: Bogdan Botez [mailto:bmbogdan@gmail.com] Sent: Tuesday, January 09, 2007 2:39 PM To: Marcus Haarmann Subject: Re: IDS is busy all the time [8159] On 1/9/07, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > > What we found out in the meantime: > There was a crash of the DB some days ago. Since then, a single > session is in the session list, which uses the CPU. > The db did start up after the crash (CTX_MEM) without any errors. > > The session is in the session list even after a server restart (the > client-process is long gone). > It has no database opened, but a transaction connected (in onstat -x I > found it). > This session cannot be killed by onmode -z, the transaction cannot be > killed with onmode -Z (transaction busy). > So, if not anybody knows a trick how to kill this "zombie" session (e.g. > rebuild sysmaster in single user ?), I have to unload all the DBs and > re-setup the instance. > > Marcus > Hello Marcus, Are sure 100% that the (same) session is visible after restarting the instance? More information about that session? Connect time, last read/last write (onstat -g ntt)? onstat -g ses/sql <SID>? select * from sysmaster:syssesprof where sid=<SID> (at least 2 samples,separated by 20-30s)? Locks on the session? Which objects locked? Have you tried to enable the dynamic explain for that session? Regards, Bogdan BOTEZ.

One thing to try, try shutting down the instance and restarting it again. It looks like the ADM VP which cleans up after defunct sessions crashed as part of the original AF event. Perhaps a proper shutdown will clean up. If that doesn't work, then it's time to call IBM tech support. Art S. Kagel ----- Original Message ----- From: Marcus Haarmann <ids@iiug.org> At: 1/09 8:54:00 Hi Bodgan, Yes, I am sure. The session was started at 09:27, last access was 09:33, which mets the crash timepoint. No locks. According to onstat -g sql, the session does nothing. In onstat -g ses, I can see that the session is not connected to any DB, but has two memory pools. Even the information about client-process, Terminal (was a dbaccesss, I suspect), are there. The syssesprof has 0 in all columns. Marcus -----Original Message----- From: Bogdan Botez [mailto:bmbogdan@gmail.com] Sent: Tuesday, January 09, 2007 2:39 PM To: Marcus Haarmann Subject: Re: IDS is busy all the time [8159] On 1/9/07, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > > What we found out in the meantime: > There was a crash of the DB some days ago. Since then, a single > session is in the session list, which uses the CPU. > The db did start up after the crash (CTX_MEM) without any errors. > > The session is in the session list even after a server restart (the > client-process is long gone). > It has no database opened, but a transaction connected (in onstat -x I > found it). > This session cannot be killed by onmode -z, the transaction cannot be > killed with onmode -Z (transaction busy). > So, if not anybody knows a trick how to kill this "zombie" session (e.g. > rebuild sysmaster in single user ?), I have to unload all the DBs and > re-setup the instance. > > Marcus > Hello Marcus, Are sure 100% that the (same) session is visible after restarting the instance? More information about that session? Connect time, last read/last write (onstat -g ntt)? onstat -g ses/sql <SID>? select * from sysmaster:syssesprof where sid=<SID> (at least 2 samples,separated by 20-30s)? Locks on the session? Which objects locked? Have you tried to enable the dynamic explain for that session? Regards, Bogdan BOTEZ. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Marcus, first update_stats isn't mine, dostats is my UPDATE STATISTICS utility, but that's just an asside. Next, post your configuration info (# & type of CPUS, memory, OS w/version,.. Wait, I see this at the end of your post: IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram good. Please also post your ONCONFIG file and the following onstat output: onstat -g glo onstat -g iof onstat -g iov onstat -p onstat -P onstat -d onstat -F onstat -R Also the time since the server was bounced or stats were last zero'd (onstat -z) and we'll see what we can find. Art S. Kagel ----- Original Message ----- From: Marcus Haarmann <ids@iiug.org> At: 1/09 6:12:17 Hi, We use update_stats from Art Kagel. Running every night. So stats should not be a problem. Optimiser directives are not the problem, since we are not using them. The application itself performs good, no problematic costs etc. The point is the IDS uses one CPU all the time and I cannot find a user process which is causing this. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Paul Watson Sent: Tuesday, January 09, 2007 11:43 AM To: ids@iiug.org Subject: RE: IDS is busy all the time [8154] The obvious starting point (to me anyway) is your stats - are they upto date ? Also do you use Optimiser Directives ? I love optimiser directives, the best performance on a system that has grown siginificantly is to remove them :-) Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Failure is not as frightening as regret. Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Marcus Haarmann > Sent: 09 January 2007 04:37 > To: ids@iiug.org > Subject: IDS is busy all the time [8153] > > > Hi, > > I need a hint. Maybe somebody has had the same or a similar problem: > > We have an IDS 10 Server which has been running with a load of about > 15 % until some days ago. > Suddenly (without a config change), the load is > 50 % all the time. > We shut down the engine and the load went away, so it is definitely > the db and not any other process which is producing the load. > > How can I find out what is keeping the DB busy ? > I tried to go through all sessions in onstat -g sql to see if there is > anything uncommon, but I could not find anything. > > When calling onstat -g act, I can see that the machine is handling > mostly only one sqlexec at a time. > Other active threads are only sm_poll (1) and tlitcppoll (2). > How can I trace, which sqlexec is causing the load ? > I can see that the tid shown in onstat -g act is the same for > subsequent requests. > In onstat -a the tid occurs only in the onstat -g act/ath output. How > can I find out what this thread is doing ? > > IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram > > Marcus > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

When Assert fail, I tried to ONCHECK the infected table, but it refused so many times. So, I had nothing to do but backing up whole the database and restoring it again. I opened a case with IBM, and still waiting for their reply. Regards, Mohammad Marwan. Senior DBA and Unix administrator IBM - DB2 - Certified Solutions Expert Oracle Certified Associate Mediterranean Smart Cards Company 92 Eltahrir st., Dokki, Cairo, Egypt. Tel: +20(2) 3331457 Mobile: +2 0122449825 Mail: mmarwan@mscc.com.eg www.mscc.com.eg -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN Sent: Tuesday, January 09, 2007 7:02 PM To: ids@iiug.org Subject: RE: IDS is busy all the time [8162] One thing to try, try shutting down the instance and restarting it again. It looks like the ADM VP which cleans up after defunct sessions crashed as part of the original AF event. Perhaps a proper shutdown will clean up. If that doesn't work, then it's time to call IBM tech support. Art S. Kagel ----- Original Message ----- From: Marcus Haarmann <ids@iiug.org> At: 1/09 8:54:00 Hi Bodgan, Yes, I am sure. The session was started at 09:27, last access was 09:33, which mets the crash timepoint. No locks. According to onstat -g sql, the session does nothing. In onstat -g ses, I can see that the session is not connected to any DB, but has two memory pools. Even the information about client-process, Terminal (was a dbaccesss, I suspect), are there. The syssesprof has 0 in all columns. Marcus -----Original Message----- From: Bogdan Botez [mailto:bmbogdan@gmail.com] Sent: Tuesday, January 09, 2007 2:39 PM To: Marcus Haarmann Subject: Re: IDS is busy all the time [8159] On 1/9/07, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > > What we found out in the meantime: > There was a crash of the DB some days ago. Since then, a single > session is in the session list, which uses the CPU. > The db did start up after the crash (CTX_MEM) without any errors. > > The session is in the session list even after a server restart (the > client-process is long gone). > It has no database opened, but a transaction connected (in onstat -x I > found it). > This session cannot be killed by onmode -z, the transaction cannot be > killed with onmode -Z (transaction busy). > So, if not anybody knows a trick how to kill this "zombie" session (e.g. > rebuild sysmaster in single user ?), I have to unload all the DBs and > re-setup the instance. > > Marcus > Hello Marcus, Are sure 100% that the (same) session is visible after restarting the instance? More information about that session? Connect time, last read/last write (onstat -g ntt)? onstat -g ses/sql <SID>? select * from sysmaster:syssesprof where sid=<SID> (at least 2 samples,separated by 20-30s)? Locks on the session? Which objects locked? Have you tried to enable the dynamic explain for that session? Regards, Bogdan BOTEZ. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This e-mail (including attachments) is classified as Mediterranean Smart Cards Company confidential and proprietary information The recipient hereby is committed to hold in strict confidence the contents of this (e-mail, document, and information) and not to disclose to any third party without the prior written consent of Mediterranean Smart Cards Company. Recipient will be held liable for any unauthorized disclosure. It is intended solely for the addressee. Unless you are the addressee, you may not read, copy, use or store this e-mail in any way, or permit others to. If you have received it in error, please notify the sender by return e-mail and delete the message in its entirety, including any attachments * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Art, Thanks for offering this help, I now solved the problem by restoring the database. And: We do use you dostats, was a misspelling of mine. What I found out, which might interest others is the following: We had a crash of the server, at a point in time where the session in question was obviously in an odd state. It must have been a dbaccess process, which was active when the crash did take place (but was not the cause of the crashing). The database crashed with MT_EX_OS (Context mem). After the restart, which was performed without errors, the database began to start some activity, which lead to 100% usage of one processor (thank goodness we have more of these and normal activity was about 15-20 % only). This could be viewed on onstat -g act, where always the same session/thread pushed up. To identify the session, you can take the value from rstcb and search in onstat -u.This session could not be killed by onmode -z, the transaction was not killable by onmode -Z. A restart of the whole machine did not help ! Normally, I would say that a single session cannot survive this, but this session did, and started all over again after reboot. The only legal thing that I did not try would have been to call a buildsmi, though this might not have helped. If anybody has a hint, how MT_EX_OS can be prevented in the future (server was running stable for some months), I would appreciate a hint to make sure this wont happen again. Thanks for all your suggestions, Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN Sent: Tuesday, January 09, 2007 6:02 PM To: ids@iiug.org Subject: RE: IDS is busy all the time [8163] Marcus, first update_stats isn't mine, dostats is my UPDATE STATISTICS utility, but that's just an asside. Next, post your configuration info (# & type of CPUS, memory, OS w/version,.. Wait, I see this at the end of your post: IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram good. Please also post your ONCONFIG file and the following onstat output: onstat -g glo onstat -g iof onstat -g iov onstat -p onstat -P onstat -d onstat -F onstat -R Also the time since the server was bounced or stats were last zero'd (onstat -z) and we'll see what we can find. Art S. Kagel ----- Original Message ----- From: Marcus Haarmann <ids@iiug.org> At: 1/09 6:12:17 Hi, We use update_stats from Art Kagel. Running every night. So stats should not be a problem. Optimiser directives are not the problem, since we are not using them. The application itself performs good, no problematic costs etc. The point is the IDS uses one CPU all the time and I cannot find a user process which is causing this. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Paul Watson Sent: Tuesday, January 09, 2007 11:43 AM To: ids@iiug.org Subject: RE: IDS is busy all the time [8154] The obvious starting point (to me anyway) is your stats - are they upto date ? Also do you use Optimiser Directives ? I love optimiser directives, the best performance on a system that has grown siginificantly is to remove them :-) Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Failure is not as frightening as regret. Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Marcus Haarmann > Sent: 09 January 2007 04:37 > To: ids@iiug.org > Subject: IDS is busy all the time [8153] > > > Hi, > > I need a hint. Maybe somebody has had the same or a similar problem: > > We have an IDS 10 Server which has been running with a load of about > 15 % until some days ago. > Suddenly (without a config change), the load is > 50 % all the time. > We shut down the engine and the load went away, so it is definitely > the db and not any other process which is producing the load. > > How can I find out what is keeping the DB busy ? > I tried to go through all sessions in onstat -g sql to see if there is > anything uncommon, but I could not find anything. > > When calling onstat -g act, I can see that the machine is handling > mostly only one sqlexec at a time. > Other active threads are only sm_poll (1) and tlitcppoll (2). > How can I trace, which sqlexec is causing the load ? > I can see that the tid shown in onstat -g act is the same for > subsequent requests. > In onstat -a the tid occurs only in the onstat -g act/ath output. How > can I find out what this thread is doing ? > > IDS 10.00FC4 on Solaris/Sparc, 2 CPU, 4 GB Ram > > Marcus > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.