Query drill-down
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.
- 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?
The statistical information is stored in a circular buffer, which is an in-memory pseudo table, called syssqltrace, that is stored in the sysmaster database. 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.
- 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.
- low-level tracing, which is enabled by default, captures the information shown in the example below. This information includes 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 is not 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.
select * from syssqltrace where sql_id = 5678;
sql_id 5678
sql_address 4489052648
sql_sid 55
sql_uid 2053
sql_stmttype 6
sql_stmtname INSERT
sql_finishtime 1140477805
sql_begintxtime 1140477774
sql_runtime 30.86596333400
sql_pgreads 1285
sql_bfreads 19444
sql_rdcache 93.39127751491
sql_bfidxreads 5359
sql_pgwrites 810
sql_bfwrites 17046
sql_wrcache 95.24815205913
sql_lockreq 10603
sql_lockwaits 0
sql_lockwttime 0.00
sql_logspace 60400
sql_sorttotal 0
sql_sortdisk 0
sql_sortmem 0
sql_executions 1
sql_totaltime 30.86596333400
sql_avgtime 30.86596333400
sql_maxtime 30.86596333400
sql_numiowaits 2080
sql_avgiowaits 0.014054286131
sql_totaliowaits 29.23291515300
sql_rowspersec 169.8958799132
sql_estcost 102
sql_estrows 1376
sql_actualrows 5244
sql_sqlerror 0
sql_isamerror 0
sql_isollevel 2
sql_sqlmemory 32608
sql_numiterators 4
sql_database db3
sql_numtables 3
sql_tablelist t1
sql_statement insert into t1 select {+ AVOID_FULL(sysindices) } 0, tabname
For an explanation of all table rows, see information about the syssqltrace table in the sysmaster database section of the IBM® Informix® Administrator's Reference.