Performance impact of SQLTRACE

Hi all, Pretty much still an informix newbie but I've searched and haven't found an answer. On Informix 11.10, is there a significant performance impact to turning on global SQLTRACE through onconfig? We've got problem queries that need to be addressed and it seems like this might be a way to start. I've been tinkering with SQLTRACE by turning it on through the task API on our development database but that isn't giving me a feel for what it will do to our production server. I've been using 'execute function task("set sql tracing on", 3000, 2, "med", "global");' to test but would really prefer to keep more than 3000 queries if possible, at least until we get a better handle on this. We have 256G of ram on the production server with about 50G usually free. Oh, in testing I haven't answered this next question: Does syssqltrace report queries while they are in process? We'd initially like to look for queries that are taking 10s of seconds or longer to finish so if sql_begintxtime is not null and sql_finishtime is null I can do that. Thank you! Jeff Ross CargoTel, Inc.

The overhead of SQLTRACE for LOW and MEDIUM level is not bad, a few percentage of CPU. Yes it shows active queries. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 8, 2015 at 4:47 PM, JEFF ROSS <rossj@cargotel.com> wrote: > Hi all, > > Pretty much still an informix newbie but I've searched and haven't found an > answer. > > On Informix 11.10, is there a significant performance impact to turning on > global SQLTRACE through onconfig? We've got problem queries that need to be > addressed and it seems like this might be a way to start. > > I've been tinkering with SQLTRACE by turning it on through the task API on > our > development database but that isn't giving me a feel for what it will do to > our production server. I've been using 'execute function task("set sql > tracing > on", 3000, 2, "med", "global");' to test but would really prefer to keep > more > than 3000 queries if possible, at least until we get a better handle on > this. > > We have 256G of ram on the production server with about 50G usually free. > > Oh, in testing I haven't answered this next question: Does syssqltrace > report > queries while they are in process? We'd initially like to look for queries > that are taking 10s of seconds or longer to finish so if sql_begintxtime is > not null and sql_finishtime is null I can do that. > > Thank you! > > Jeff Ross > CargoTel, Inc. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bd757101b892a05266a02ba

Thank you, Art. Jeff

I used SQLTRACE (both high/med) in a prd env a few months ago to track down costly sql ; I would turn it on for a few mins then toggle it off at various times over a few week period of time using the sql api. I was surprised at what I consider a low impact it had on overall performance therefore I would not be apprehensive of using it in a prd env, reserve using high mode only if you need to see the values of the host variables on the $ sql you identify . As for the buffer size, be careful as the engine may dynamically allocate segments if you set it too high. 11.50.FC7 engine. Mark

Mark, Can you please contact me at ddmueller@west.com re... this subject? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK JALKIEWICZ Sent: Wednesday, December 09, 2015 10:27 AM To: ids@iiug.org Subject: Re: Performance impact of SQLTRACE [36193] I used SQLTRACE (both high/med) in a prd env a few months ago to track down costly sql ; I would turn it on for a few mins then toggle it off at various times over a few week period of time using the sql api. I was surprised at what I consider a low impact it had on overall performance therefore I would not be apprehensive of using it in a prd env, reserve using high mode only if you need to see the values of the host variables on the $ sql you identify . As for the buffer size, be careful as the engine may dynamically allocate segments if you set it too high. 11.50.FC7 engine. Mark ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.