Enable sql trace in global

Hi All, I have enable sql trace in global to understand the how the application query execution path and sql statments. Where do I take all the trace file? Thank you very much Regards Elisha --089e012287b863f39404f2d9c681

SQLTRACE doesn't create a file. I just creates a ring buffer of queries in memory with supporting meta-data. There is a task manager sensor that copies the new entries from that buffer into a set of tables in sysadmin, however, so you can see queries that have scrolled off the end of the ring. There are tables in sysmaster that are windows into the memory buffer. All have the word 'trace' in their names. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Thu, Feb 20, 2014 at 12:24 PM, medkba <medkba@gmail.com> wrote: > Hi All, > > I have enable sql trace in global to understand the how the application > query execution path and sql statments. > > Where do I take all the trace file? > > Thank you very much > > Regards > Elisha > > --089e012287b863f39404f2d9c681 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1136c0ba1d40ad04f2da60e9

Is there way to take from all the sql statments particular jobs? is it using session id or something else Becasuse, performance issue and would like to find out the bottleneck Thank you very much Regards Elisha On Fri, Feb 21, 2014 at 2:07 AM, Art Kagel <art.kagel@gmail.com> wrote: > SQLTRACE doesn't create a file. I just creates a ring buffer of queries in > memory with supporting meta-data. There is a task manager sensor that > copies the new entries from that buffer into a set of tables in sysadmin, > however, so you can see queries that have scrolled off the end of the ring. > There are tables in sysmaster that are windows into the memory buffer. > All have the word 'trace' in their names. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > 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 Thu, Feb 20, 2014 at 12:24 PM, medkba <medkba@gmail.com> wrote: > > > Hi All, > > > > I have enable sql trace in global to understand the how the application > > query execution path and sql statments. > > > > Where do I take all the trace file? > > > > Thank you very much > > > > Regards > > Elisha > > > > --089e012287b863f39404f2d9c681 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1136c0ba1d40ad04f2da60e9 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11348c0483656704f2da7cf3

The SQL Trace information whether you turn in on globally, for a database, a user or a session is stored in the memory buffer you define. The buffer is used in a circular fashion... meaning it will wrap around if it's too small. There is a task in sysadmin/dbscheduler that can copy the buffer into sysadmin tables. But you can only schedule it to run each minute... Technically you could make it run in cycles with a "sleep" so that it copies the buffer more frequently, but you must be careful if you think about doing it (it would imply changing the task) Also consider these tables can become very large. You can explore the "archived" data through OAT. You have the live and historical data... Regards On Thu, Feb 20, 2014 at 5:24 PM, medkba <medkba@gmail.com> wrote: > Hi All, > > I have enable sql trace in global to understand the how the application > query execution path and sql statments. > > Where do I take all the trace file? > > Thank you very much > > Regards > Elisha > > --089e012287b863f39404f2d9c681 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b3a97309fb87404f2da7eb6

Yes, I am using OAT. How do I check from OAT i.e. how to get archived? Please guide any option to see archived On Fri, Feb 21, 2014 at 2:15 AM, Fernando Nunes <domusonline@gmail.com>wrote: > The SQL Trace information whether you turn in on globally, for a database, > a user or a session is stored in the memory buffer you define. > The buffer is used in a circular fashion... meaning it will wrap around if > it's too small. > There is a task in sysadmin/dbscheduler that can copy the buffer into > sysadmin tables. But you can only schedule it to run each minute... > Technically you could make it run in cycles with a "sleep" so that it > copies the buffer more frequently, but you must be careful if you think > about doing it (it would imply changing the task) > > Also consider these tables can become very large. You can explore the > "archived" data through OAT. You have the live and historical data... > Regards > > On Thu, Feb 20, 2014 at 5:24 PM, medkba <medkba@gmail.com> wrote: > > > Hi All, > > > > I have enable sql trace in global to understand the how the application > > query execution path and sql statments. > > > > Where do I take all the trace file? > > > > Thank you very much > > > > Regards > > Elisha > > > > --089e012287b863f39404f2d9c681 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --047d7b3a97309fb87404f2da7eb6 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0115ff341cf5f704f2da8b3d

The sysmaster:syssqltrace table contains the column sql_sid which is the session id for that query. The other tables, syssqltrace_iter and syssqltrace_hvar, are linked to syssqltrace by the sql_id column. You can also set up SQLTRACE to only trace a single user's queries. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Thu, Feb 20, 2014 at 1:15 PM, medkba <medkba@gmail.com> wrote: > Is there way to take from all the sql statments particular jobs? is it > using session id or something else > > Becasuse, performance issue and would like to find out the bottleneck > > Thank you very much > > Regards > Elisha > > On Fri, Feb 21, 2014 at 2:07 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > SQLTRACE doesn't create a file. I just creates a ring buffer of queries > in > > memory with supporting meta-data. There is a task manager sensor that > > copies the new entries from that buffer into a set of tables in sysadmin, > > however, so you can see queries that have scrolled off the end of the > ring. > > There are tables in sysmaster that are windows into the memory buffer. > > All have the word 'trace' in their names. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > 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 Thu, Feb 20, 2014 at 12:24 PM, medkba <medkba@gmail.com> wrote: > > > > > Hi All, > > > > > > I have enable sql trace in global to understand the how the application > > > query execution path and sql statments. > > > > > > Where do I take all the trace file? > > > > > > Thank you very much > > > > > > Regards > > > Elisha > > > > > > --089e012287b863f39404f2d9c681 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1136c0ba1d40ad04f2da60e9 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11348c0483656704f2da7cf3 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c36858dd2fbf04f2dd587c

I have managed to setup on OAT but the I am trying to analysis query and I am looking Wait IO time anything longer then may be analysis more on this query. Am I right? Thank you On Fri, Feb 21, 2014 at 5:40 AM, Art Kagel <art.kagel@gmail.com> wrote: > The sysmaster:syssqltrace table contains the column sql_sid which is the > session id for that query. The other tables, syssqltrace_iter and > syssqltrace_hvar, are linked to syssqltrace by the sql_id column. > > You can also set up SQLTRACE to only trace a single user's queries. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > 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 Thu, Feb 20, 2014 at 1:15 PM, medkba <medkba@gmail.com> wrote: > > > Is there way to take from all the sql statments particular jobs? is it > > using session id or something else > > > > Becasuse, performance issue and would like to find out the bottleneck > > > > Thank you very much > > > > Regards > > Elisha > > > > On Fri, Feb 21, 2014 at 2:07 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > SQLTRACE doesn't create a file. I just creates a ring buffer of queries > > in > > > memory with supporting meta-data. There is a task manager sensor that > > > copies the new entries from that buffer into a set of tables in > sysadmin, > > > however, so you can see queries that have scrolled off the end of the > > ring. > > > There are tables in sysmaster that are windows into the memory buffer. > > > All have the word 'trace' in their names. > > > > > > Art > > > > > > Art S. Kagel, Principal Consultant > > > ASK Database Management > > > > > > 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 Thu, Feb 20, 2014 at 12:24 PM, medkba <medkba@gmail.com> wrote: > > > > > > > Hi All, > > > > > > > > I have enable sql trace in global to understand the how the > application > > > > query execution path and sql statments. > > > > > > > > Where do I take all the trace file? > > > > > > > > Thank you very much > > > > > > > > Regards > > > > Elisha > > > > > > > > --089e012287b863f39404f2d9c681 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a1136c0ba1d40ad04f2da60e9 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11348c0483656704f2da7cf3 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c36858dd2fbf04f2dd587c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01175e7dd9a55f04f2e1fcac

Unusual IO wait time is be one criteria you can use to select queries for further analysis, yes. There are others. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Thu, Feb 20, 2014 at 10:12 PM, medkba <medkba@gmail.com> wrote: > I have managed to setup on OAT but the I am trying to analysis query and I > am looking Wait IO time anything longer then may be analysis more on this > query. Am I right? > > Thank you > > On Fri, Feb 21, 2014 at 5:40 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > The sysmaster:syssqltrace table contains the column sql_sid which is the > > session id for that query. The other tables, syssqltrace_iter and > > syssqltrace_hvar, are linked to syssqltrace by the sql_id column. > > > > You can also set up SQLTRACE to only trace a single user's queries. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > 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 Thu, Feb 20, 2014 at 1:15 PM, medkba <medkba@gmail.com> wrote: > > > > > Is there way to take from all the sql statments particular jobs? is it > > > using session id or something else > > > > > > Becasuse, performance issue and would like to find out the bottleneck > > > > > > Thank you very much > > > > > > Regards > > > Elisha > > > > > > On Fri, Feb 21, 2014 at 2:07 AM, Art Kagel <art.kagel@gmail.com> > wrote: > > > > > > > SQLTRACE doesn't create a file. I just creates a ring buffer of > queries > > > in > > > > memory with supporting meta-data. There is a task manager sensor that > > > > copies the new entries from that buffer into a set of tables in > > sysadmin, > > > > however, so you can see queries that have scrolled off the end of the > > > ring. > > > > There are tables in sysmaster that are windows into the memory > buffer. > > > > All have the word 'trace' in their names. > > > > > > > > Art > > > > > > > > Art S. Kagel, Principal Consultant > > > > ASK Database Management > > > > > > > > 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 Thu, Feb 20, 2014 at 12:24 PM, medkba <medkba@gmail.com> wrote: > > > > > > > > > Hi All, > > > > > > > > > > I have enable sql trace in global to understand the how the > > application > > > > > query execution path and sql statments. > > > > > > > > > > Where do I take all the trace file? > > > > > > > > > > Thank you very much > > > > > > > > > > Regards > > > > > Elisha > > > > > > > > > > --089e012287b863f39404f2d9c681 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --001a1136c0ba1d40ad04f2da60e9 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a11348c0483656704f2da7cf3 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11c36858dd2fbf04f2dd587c > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e01175e7dd9a55f04f2e1fcac > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b3431ea1ded3904f2e8bc92