SQL Tracing

NOTE:
SQL Tracing function is available only for IBM Informix Dynamic Server 11.x. Previous versions of Dynamic Server do not support this function.

You can use query drill-down, or SQL tracing, to gather statistical information about each SQL statement that was run and to analyze statement history. SQL tracing helps you answer questions such as:

• How long do SQL statements take?
• How many resources are individual statements using?
• How long did statement execution take?
• How much time was involved waiting for each resource?
• Immediately extend the size of a chunk by a specified minimum amount.

The statistical information is stored in a circular buffer, which is an in-memory pseudo table. You can dynamically resize the circular buffer.

By default SQL tracing turned off, but you can turn it on for all users or for a specific set of users. When SQL tracing is enabled with its default configuration, the database server tracks the last 1000 SQL statements that ran, along with the profile statistics for those statements. You can also disable SQL tracing globally or for a particular user.

The memory required by SQL tracing is large if you plan to keep much historical information. The default amount of space required for SQL tracing is two megabytes. You can expand or reduce the amount of storage according to your requirements.

Information displayed includes:

• The user ID of the user who ran the command
• The database session ID
• The name of the database
• The type of SQL statement
• The duration of the SQL statement execution
• The time this statement completed
• The text of the SQL statement or a function call list (also called stack trace) with the statement type, for example: procedure1() calls procedure2() calls procedure3()
• Statistics including the:
  – Number of buffer reads and writes
  – Number of page reads and writes
  – Number of sorts and disk sorts
  – Number of lock requests and waits
  – Number of logical log records
  – Number of index buffer reads
  – Estimated number of rows
  – Optimizer estimated cost
  – Number of rows returned
  
• Database isolation level
• The duration of the SQL statement execution
• The duration of the SQL statement execution
• The duration of the SQL statement execution

You can also specify escalating levels of information to include in the tracing, as follows:

• Low level tracing, which is enabled by default, captures statement statistics, statement text, and statement iterators.
• Medium level tracing captures all of the information included in low-level tracing, plus the list of table names, database name and stored procedure stacks.
• High level tracing captures all of the information included in medium-level tracing, plus host variables.

The amount of information traced affects the amount of memory required for this historical data.

You can enable and disable the tracing at any point in time, and you can change the number and size of the trace buffers while the database server is running. If you resize the trace buffer, the database server attempts to maintain the content of the buffer. If the parameters are increased, data will not be truncated. However, if the number or the size of the buffers are reduced, the data in the trace buffers might be truncated or lost.

The number of buffers determines how many SQL statements are traced. Each buffer contains the information for a single SQL statement. By default, an individual trace buffer is a fixed size. If the text information stored in the buffer exceeds the size of the trace buffer, then the data is truncated.

Specifying startup SQL tracing information by using the SQLTRACE configuration parameter

Use the SQLTRACE configuration parameter to control the default tracing behavior when the database server starts. By default, this parameter is not set. The information you set includes the number of SQL statements to trace and the tracing mode.

Any user who can modify the onconfig file can modify the value of the SQLTRACE configuration parameter and effect the startup configuration. However, only user informix, root, or a DBSA who has been granted connect privileges to the sysadmin database can use Server Studio GUI to modify the runtime status of the SQL tracing.

To specify SQL tracing information when the database server starts:

1. Set the SQLTRACE configuration parameter in the onconfig file.
2. Restart the database server

For more information about the SQLTRACE configuration parameter, including minimum and maximum values for some fields, see the IBM Informix Administrator's Reference.

Example

The following setting in the onconfig file specifies that the database server gathers low-level information on up to 2000 SQL statements executed by all users on the system and allocates approximately four megabytes of memory (2000 * two kilobytes).

SQLTRACE level=LOW,ntraces=2000,size=2,mode=global

If you use only a percentage of the allocated buffer space (for example, 42 percent of the buffer space), the amount of memory that is allocated is still two kilobytes. If you do not want to set the SQLTRACE configuration parameter and restart the server, you can use Server Studio SQL Trace Explorer (Trace Admin tab) that uses SQL API commands to perform this operation on running IDS server.

Opening SQL Trace Explorer in Server Studio

There are several ways to open SQL Trace Explorer in Server Studio:

• Choose Tools -> SQL Manager -> SQL Trace Explorer menu and connect to IDS server as user informix or another authorized user.
• Choose any SQL report from Reports folder under IDS server node in the Object Explorer tree. Press Open SQL Trace Explorer button located at the top of the report panel. Only one SQL Trace Explorer document can be opened per IDS server. If you attempt to open second SQL Trace Explorer for the same database server, Server Studio simply brings existing SQL Trace Explorer panel into a focus.

SQL Trace Explorer Panel

SQL Trace Explorer panel has four tabs:

Activity Summary tab

Activity Summary tab displays aggregation information per SQL statement type and per transaction type. Double-click or use popup menu on the grid to drill-down to a list of SQL statements of selected type. For example, double-click on statement type SELECT to see all SELECT statements in SQL Trace buffer. You can filter these statements further down by using filter field provided in SQL Type Summary panel.

SQL tab

SQL tab shows either all unique SQL statements in SQL Trace buffer or only statements filtered by statement type or particular word in SQL statement. Use filter fields located at the top of this panel to filter information. For example, if you want to see all SQL statements that have a reference to table tbl_order, select ALL in Filter By Type dropdown listbox and type tbl_order in Filter By Statement field. If you want to see only INSERT statements that have a reference to table tbl_order, type tbl_order in Filter by Statement field and choose INSERT in Filter by Type drop-downlist box.

The list of statements shows only unique SQL statements and aggregated statistical information, such as count of executions for this statement, average execution time, lock wait time, etc. If you want to see list of executions for this SQL statement including execution time, user, rows processed per each execution, double-click on the statement in SQL Type Summary grid (or use popup menu on this grid and choose Show SQL Executions). It will open SQL Statement View that shows list of executions for selected statement and other details.

You can still drill-down to individual executions to see SQL Profile for this execution, including optimizer query plan graph, host variables with which this statement executed, detail statistics and also configure Optimizer External Directives. To drill-down to individual SQL statement execution, double-click on execution in the Executions grid or use popup menu and select Show SQL Profile. Top area of the SQL Trace Explorer panel shows the navigational links so that you can easy navigate back to the lists from which you drilldown to the detail views.

Transactions tab

Transactions tab shows list of transactions. You can drilldown to see SQL statements in the selected transaction by double-clicking on the transaction in the list.

Trace Admin

Trace Admin tab allows viewing SQL Trace options and also modifying SQL Trace options as well as enabling and disabling SQL Trace operation while IDS server is running.

Please note that the changes that you make in SQL Trace Admin tab will be effective only while IDS server is running. Once IDS server is restarted, all changes will be reset to values set in SQLTRACE parameter of ONCONFIG file. See "Specifying startup SQL tracing information by using the SQLTRACE configuration parameter" section above.

SQL Trace Settings group box contains options that you can change:

• Trace State – ON to enable SQL Tracing, OFF to disable SQL Tracing
• Trace Mode – this option is available only on IDS 11.5.TC5 and higher: GLOBAL captures SQL for all users, USER allows to select a list of users to trace in User Tracing listbox. Older versions of IDS 11.5 and 11.1 allow only GLOBAL tracing mode.
• Number of traces – number of captured SQL statements that can be stored in the circular buffer.
• Trace Size – the size of a single captured SQL statement in a buffer
• Advanced Trace Options – allow you to choose if database name, table names, stored procedures stacks and host variables will be captured along with SQL statement. Clear Trace Buffer checkbox allows you to clean current trace buffer.
• Database Tracing – allows to specify list of databases in which SQL statements will be traced. This option is available for IDS 11.5.TC5 and higher

Press Save button to apply changes to a database.