Using SQL-Tracing in GBase 8s

1. Introduction to SQL-Tracing

SQL-Tracing provides statistical information about recently executed SQL statements, allowing you to track the performance of individual SQL statements and analyze historical ones. You can use SQL-Tracing to collect statistics for each SQL statement and analyze their history.

SQL-Tracing helps answer questions such as:

• How long does a SQL statement take?
• How many resources does a single statement use?
• What is the execution time of the statement?
• How long does it wait for each resource?

The statistics are stored in a circular buffer, a memory-resident pseudo-table called syssqltrace, which is in the sysmaster database. You can dynamically adjust the size of the circular buffer.

By default, SQL-Tracing is off but can be enabled for all users or a specific group of users. When SQL-Tracing is started with the default configuration, the database server tracks the last 1000 SQL statements executed and summarizes these statements. You can also disable SQL-Tracing globally or for specific users.

If you need to save a large amount of historical information, SQL-Tracing requires significant memory. The default amount of space required for SQL-Tracing is 2MB. You can increase or decrease the amount of storage as needed.

The information displayed includes:

• User ID running the command
• Database session ID
• Database name
• SQL statement type
• SQL statement execution duration
• Completion time of the current statement
  • e.g. SQL statement text or a list of function calls with statement types (also known as stack trace): procedure1() calls procedure2() calls procedure3()

The statistics include:

• Buffer read/write counts
• Pages read/written
• Number and type of locks requested and waited for
• Number of logical log records
• Index buffer reads
• Estimated number of rows
• Optimizer estimated cost value
• Rows returned
• Database isolation level

You can also specify tracing levels as follows:

• Low-Level Tracing (default): Captures statement statistics, statement text, and statement iterator information.
• Medium-Level Tracing: Includes all information from low-level tracing plus table names, database names, and stored procedure stack.
• High-Level Tracing: Includes all information from medium-level tracing plus host variables.

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

You can enable and disable tracing at any time and change the number and size of tracing buffers while the database server is running. If you resize the tracing buffer, the server attempts to maintain the buffer contents. Increasing parameters will not truncate data, but decreasing the number or size of buffers may truncate or lose data.

The number of buffers determines the number of SQL statements tracked. Each buffer contains information for a single SQL statement. By default, a single tracing buffer is of fixed size. If the text information stored in the buffer exceeds the size, the data is truncated.

Here’s an example illustrating 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

2. Configuring SQL-Tracing with SQLTRACE Parameters

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

Any user who can modify the onconfig file can change the value of the SQLTRACE configuration parameter, affecting the startup configuration. However, only the gbasedbt user, root, or a DBSA granted system administrator database connection privileges can modify the runtime state of SQL-Tracing using SQL management API commands.

Specifying SQL-Tracing Information at Server Startup

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

Example:

The following settings in the onconfig file specify that the database server collects low-level information for up to 2000 SQL statements executed by all users on the system, allocating approximately 4MB of memory (2000 * 2KB).

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

If only a percentage of the allocated buffer space is used (e.g., 42% of the buffer space), the allocated memory amount remains 2KB.

If you do not want to set the SQLTRACE configuration parameter and restart the server, you can run the following SQL management API command to provide the same functionality for the current session:

EXECUTE FUNCTION task("set sql tracing on", 100, "1k", "med", "user");

After enabling the SQL-Tracing system in user mode, you can enable tracing for each user.

3. Disabling SQL-Tracing Globally or in a Session

Even if the SQLTRACE configuration parameter specifies global or user mode, you can completely disable all user and global tracing and reallocate resources currently used by SQL-Tracing. By default, SQL-Tracing is disabled for all users.

You must connect to the system administrator database as the gbasedbt user or another authorized user.

To disable global SQL-Tracing, run the SQL management API task() or admin() function and set the SQL tracing parameter.

To disable SQL-Tracing for a specific session, run the SQL management API task() or admin() function, setting SQL tracing as the first parameter and the session ID as the second parameter.

Example:

The following example disables SQL-Tracing globally:

EXECUTE FUNCTION task('set sql tracing off');
(expression) SQL tracing off.
1 row(s) retrieved.

The following example disables SQL-Tracing for session ID 47:

EXECUTE FUNCTION task("set sql user tracing off", 47);

4. Enabling SQL-Tracing

After specifying users in the SQLTRACE configuration parameter mode, you must run the SQL management API task() or admin() function to track SQL history for specific users.

You must connect to the system administrator database as the gbasedbt user or another authorized user.

Global SQL-Tracing does not need to be enabled to trace specific users.

To trace SQL for specific users, run the SQL management API task() or admin() function, setting SQL tracing as the first parameter and the user session ID as the second parameter.

To trace SQL for all users except root or gbasedbt, run the task() or admin() function and use SQL to define user parameters and information.

Example:

The following example enables SQL-Tracing for user session ID 74:

EXECUTE FUNCTION task("set sql user tracing on", 74);

The following example tracks SQL statements for users currently connected to the system, as long as they are not logged in as root or gbasedbt.

dbaccess sysadmin -<<END
  execute function task("set sql tracing on", 1000, 1, "low", "user");
  select task("set sql user tracing on", session_id)
    FROM sysmaster:syssessions
    WHERE username not in ("root","gbasedbt");
END

5. Enabling Global SQL-Tracing for a Session

You can enable global SQL-Tracing for the current session by running the SQL management API task() or admin() function.

You must connect to the system administrator database as the gbasedbt user or another authorized user.

By default, global SQL-Tracing is not enabled. You can permanently enable global tracing by setting the SQLTRACE configuration parameter.

To track SQL history for global users for the current database server session, run the SQL management API task() or admin() function, setting SQL tracing on the parameter.

Example:

The following example enables low-level global SQL-Tracing for all users:

EXECUTE FUNCTION task("set sql tracing on", 1000, 1