The onstat -g his command displays SQL trace information
The level setting of the SQLTRACE configuration parameter affects what SQL
trace information is stored and displayed by the set of syssqltrace tables,
and what information onstat -g his displays. Each row of the syssqltrace
table describes a previously executed SQL statement. By default, only
the DBSA can view the syssqltrace information from the onstat -g his command.
However, when the UNSECURE_ONSTAT configuration parameter is set to 1, all users can view this information.
Statement history:
Trace Level Low
Trace Mode Global
Number of traces 2000
Current Stmt ID 28
Trace Buffer size 4096
Duration of buffer 182 Seconds
Trace Flags 0x00001611
Control Block 0x4c2f0028
....
Statement # 28 @ 0x4c2f3028
Database: sysmaster
Statement text:
select count(*) from t9000dbaudit t9000, t9010dbname t9010 where c9000custid = ?
and t9000.c9000id = t9010.c9000id
SELECT using tables [ t9000dbaudit, t9010dbname ]
....
Iterator/Explain
================
ID Left Right Est Cost Est Rows Num Rows Partnum Type
3 0 0 17 42 146 1048579 Index Scan
4 0 0 5249 2366 2366 1048580 Seq Scan
2 3 4 5266 99372 345436 0 Nested Join
1 2 0 1 1 1 0 Group
....
Host Variables
==============
1 integer 69
....
Statement information:
Sess_id User_id Stmt Type Finish Time Run Time TX Stamp PDQ
237892 1092 SELECT 09:44:23 0.0017 340a6e9 0
....
Statement Statistics:
Page Buffer Read Buffer Page Buffer Write
Read Read % Cache IDX Read Write Write % Cache
1285 19444 93.39 0 810 17046 95.25
Lock Lock LK Wait Log Num Disk Memory
Requests Waits Time (S) Space Sorts Sorts Sorts
10603 0 0.0000 60.4 KB 0 0 0
Total Total Avg Max Avg I/O Wait Avg Rows
Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec
1 0.0027 0.0027 0.0027 0.0010 0.0010 2895.8959
Estimated Estimated Actual SQL ISAM Isolation SQL
Cost Rows Rows Error Error Level Memory
102 1 1 0 0 CR 32608
| Field | Description |
|---|---|
| Trace Level | Amount of information traced. Valid values are LOW, MED, HIGH, and OFF. |
| Trace Mode | Type of tracing performed. Global refers to all users on the system User refers to only those users who have tracing enabled by an SQL administration API function. |
| Number of traces | The number of SQL statements that are being traced. This is the value set in your onconfig file unless the ntraces parameter is changed dynamically through SQL Administration API functions. The range is 500 to 2147483647. If you have 100,000 trace buffers and your organization runs 1000 SQL statements a second, and are tracing all of the statements, then the buffers would last for 100 seconds before they would begin being overwritten. |
| Current Stmt ID | The ID for the current SQL statement. Each statement being traced gets a unique ID. |
| Trace Buffer size | The amount of data each trace buffer will capture, in bytes. If you set the size to 2KB, but have an SQL statement that is 12KB, the statement is truncated by at least 10KB. More data might be truncated, depending on what else is being traced. |
| Duration of buffer | The amount of time, in seconds, that the trace data in the current trace buffer spans. This is not how long the sqltrace feature has been running. In the above example Duration of buffer is 293 seconds which indicates the number of seconds between the first and last SQL statement that are traced. |
| Trace Flags | The current SQL trace flags that are set. |
| Control Block | The memory address of the SQL trace control block. |
| Database | The name of the database or part number of the systables entry for the database. |
| Statement text | The statement text for this SQL statement. If the statement is a stored procedure, then the statement text would display the procedure stack trace. The statement text might be truncated if the statement and the numeric statistics are larger than the trace buffer |
| ID | SQL iterator ID |
| Left | ID of the left input to the iterator |
| Right | ID of the right input to the iterator |
| Est Cost | Estimated cost of this iterator |
| Est Rows | Estimated rows for this iterator |
| Num Rows | Actual number of rows for this iterator |
| Partnum | The table or index partition number. |
| Type | Type of operation |
| Host Variables |
|
| Sess_id | The session ID |
| User_id | The operating system user ID |
| Stmt Type | The type of SQL statement |
| Finish Time | The time of day that the SQL statement finished |
| Run Time | The total amount of time consumed by the virtual processors or threads used to process the statement. For example, if the Finish Time is 1:15:00 and the Run Time is 9 minutes and the start time is not necessarily 1:06:00. There might be multiple virtual processors or threads involved in processing parts of the statement in parallel. |
| TX Stamp | The time the BEGIN WORK statement was logged in this transaction |
| PDQ | The SQL statement PDQ level |
| Page Read | Number of pages that have been read from disk for this SQL statement |
| Buffer Read | Number of times a page has been read from the buffer pool and not read from disk for this SQL statement |
| Read % Cache | Percentage of times the page was read from the buffer pool |
| Buffer IDX Read | This Currently not implemented |
| Page Write | Number of pages written to disk |
| Buffer Write | Number of pages modified and sent back to the buffer pool |
| Write % Cache | Percentage of time that a page was written to the buffer pool but not to disk |
| Lock Requests | Total number of locks required by this statement |
| Lock Waits | Number of times this SQL statement waited on locks |
| LK Wait Time (S) | Amount of time the statement waited for application locks, in seconds |
| Log Space | Amount of storage space that the SQL statement used in the logical log |
| Num Sorts | Total number of sorts used to execute the statement |
| Disk Sorts | Number of sorts which required disk space to execute the sort for this SQL statement |
| Memory Sorts | Number of sorts executed which executed entirely in memory for this SQL statement |
| Total Executions | Total number of times this prepared statement has been executed, or the number of times this cursor has been re-used |
| Total Time (S) | Total time this prepared statement ran, in seconds |
| Avg Time (S) | Average time this prepared statement required to execute, in seconds |
| Max Time (S) | Total time to run the prepared SQL statement, in seconds, excluding any time taken by the application. If you prepare a query then run the query 5 times, each time the query is run a trace is added to the trace buffer. The Max Time is the maximum time any one execution took. |
| Avg IO Wait | Average amount of time the statement waited for I/O, excluding any asynchronous I/O |
| I/O Wait Time (S) | Amount of time the statement waited for I/O, excluding any asynchronous I/O, in seconds |
| Avg Rows Per Sec | Average number of rows a second produced by this statement |
| Estimated Cost | The query optimizer cost associated with the SQL statement |
| Estimated Rows | Number of rows returned by the statement, as estimated by the query optimizer |
| Actual Rows | Number of rows returned for this statement |
| SQL Error | The SQL error number |
| ISAM Error | The RSAM or ISAM error number |
| Isolation Level | Isolation level this statement was run with |
| SQL Memory | Number of bytes this SQL statement required |
To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.