Trace utility similar to Oracle's
Tung Nguyen asked for an Informix equivalent of Oracle session tracing with tkprof. Irwin Goldstein suggested SET EXPLAIN ON, which writes plans to sqexplain.out. Edward Rosenthal added onstat -g ses and -g sql, xtree, and sysmaster tables such as syssession. Randy Reiter pointed to a commercial SQL capture tool. No single answer was confirmed.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi there, First on all I am not comparing the two products. I am trying to learn Informix myself. With Oracle, I can trace a session to get all SQL statements executed for that session and then use a utility (tkprof) to get the execution plans for each of the traced SQL statements. Can I do something similar to that with any version Informix? If it is yes then please point me to some manual/book/articles. Thanks Tung Nguyen
I believe executing a "set explain on" will give you what you are looking for. This will write the query and plan for each SQL statement executed to a file named "sqexplain.out". -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com In article <VbAx4.31039$Jz3.242840@nnrp1.uunet.ca>, "Tung Nguyen" <tnguyen@fmco.com> wrote: > Hi there, > > First on all I am not comparing the two products. I am trying to learn > Informix myself. > > With Oracle, I can trace a session to get all SQL statements executed for > that session and then use a utility (tkprof) to get the execution plans for > each of the traced SQL statements. > Can I do something similar to that with any version Informix? If it is yes > then please point me to some manual/book/articles. > Thanks > Tung Nguyen > > Sent via Deja.com http://www.deja.com/ Before you buy.
there are many ways to look at a session; look at onstat -g ses <session id>
for starters.
if you do onstat -g sql you will see a list of session id's and the databases
they use.
another interesting way is to use xtree, if you can open it in an xterm client.
this gives you a picture of how the session is processing, how many rows are
being
filtered up the nodes to the solution set.
Another way to approach the session is thru the sysmaster tables such as
syssession and other relevant tables. look up the iiug webpages for more
information
on the use of the catalog tables.
if you want to trace the engine's use of indexes you can copy and paste the
user's session sql into your own dbaccess sql, and begin with 'set explain
on;'
then run the query, then examine the sqexplain.out file created in the
directory
in which you are currently positioned.
hth
Tung Nguyen wrote:
> Hi there,
>
> First on all I am not comparing the two products. I am trying to learn
> Informix myself.
>
> With Oracle, I can trace a session to get all SQL statements executed for
> that session and then use a utility (tkprof) to get the execution plans for
> each of the traced SQL statements.
> Can I do something similar to that with any version Informix? If it is yes
> then please point me to some manual/book/articles.
> Thanks
> Tung Nguyen
Refer to www.sqlpower.com. The Zero Impact Sql will capture all SQL text and performance with no impact upon the database server, network and end-users. Scales to thousands of users. Installation is a few minutes. The Zero Impact Top N Sql Analyzer will identify the SQL statements that run most frequently with associated end-user service levels by SQL statement. Tung Nguyen <tnguyen@fmco.com> wrote in message news:VbAx4.31039$Jz3.242840@nnrp1.uunet.ca... > Hi there, > > First on all I am not comparing the two products. I am trying to learn > Informix myself. > > With Oracle, I can trace a session to get all SQL statements executed for > that session and then use a utility (tkprof) to get the execution plans for > each of the traced SQL statements. > Can I do something similar to that with any version Informix? If it is yes > then please point me to some manual/book/articles. > Thanks > Tung Nguyen > >