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.

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?

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.

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.
You can also specify escalating levels of information to include in the tracing, as follows:
  • 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.

The following example shows SQL tracing information:
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 HCL OneDB™ Administrator's Reference.