The table `StatementExecutions` holds one row per statement executed in the current process, whether the statement was handed over to a data container, handled by Invantive Script itself or run as an operating-system command. The columns follow the order in which they are needed: first what identifies the statement and where it sits in the script, then the statement itself, then the measurements, then the outcome and only last the technical context. ## Identification The following columns place the statement in the script: - `ID` (`int64`): identifier of the statement, unique within the process and ascending in order of starting. - `RUN_ID` (`int64`): identifier of the run the statement belongs to, referring to [[Runs]]. - `PARENT_STATEMENT_ID` (`int64`): identifier of the statement which started the script this statement is part of. Empty in the outermost script. - `NESTING_LEVEL` (`int32`): depth of the script the statement is part of. Zero in the outermost script. - `SOURCE_FILE_NAME` (`varchar2`): name and path of the script file the statement was read from. Empty when the statements did not come from a file. - `SEQUENCE_IN_SOURCE` (`int32`): sequence number of the statement within its run, starting at one. ## Statement The following columns describe what was executed: - `STATEMENT_TYPE` (`varchar2`): kind of statement. One of `SQL` for a statement handed over to a data container, `LOCAL` for a statement handled by Invantive Script itself and `OS` for an operating-system command. - `LOCAL_STATEMENT_TYPE` (`varchar2`): kind of local statement, such as `Define` or `Export`. Filled only when `STATEMENT_TYPE` reads `LOCAL`. A statement which starts another script file reads `RunScriptFile`. - `SQL_OPERATION` (`varchar2`): kind of SQL operation. One of `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `SET`, `USE`, `PSQL` and `OTHER`. Filled only when `STATEMENT_TYPE` reads `SQL`. - `STATEMENT_TEXT` (`varchar2`): the statement as executed, with the scripting variables replaced by their values. - `STATEMENT_TEXT_RAW` (`varchar2`): the statement as written, before the scripting variables were replaced. - `STATEMENT_HASH` (`varchar2`): SHA-256 hash of the statement as written, which groups repeated executions of the same statement. - `IS_ANONYMIZED` (`boolean`): whether the text was withheld because the statement handles a secret. When `true`, `STATEMENT_TEXT` reads `(anonymized)` and `STATEMENT_TEXT_RAW` is empty, while `STATEMENT_HASH` stays filled. ## Measurements The following columns state what the statement cost: - `STARTED_UTC` (`datetime`): moment at which the statement started (UTC). - `ENDED_UTC` (`datetime`): moment at which the statement ended (UTC). Empty while the statement is busy. - `DURATION_MS` (`double`): duration in milliseconds, measured up to the point at which the statement was closed off and therefore without the time spent rendering results on screen. - `CPU_MS` (`double`): processor time in milliseconds. - `IO_MS` (`double`): time in milliseconds spent on input and output. - `SLEEP_MS` (`double`): time in milliseconds spent waiting, such as on a rate limit of a platform. - `HEAP_BYTES_ALLOCATED` (`int64`): number of bytes of memory the statement asked for. Unlike `BYTES_RECEIVED` and `BYTES_SENT`, which are payload moved to and from a platform, this is memory the process itself allocated. It counts memory asked for and not memory still in use, so the figure only grows: memory released again immediately afterwards is counted in full. Where `DURATION_MS` of the same statement over the same data moves by ten per cent or more between runs, this figure repeats, which makes it the better of the two to compare two versions of a script on. A statement with a `parallel` clause includes the memory its workers asked for on their own threads. - `THREAD_CPU_TIME_NS` (`int64`): processor time in nanoseconds, kernel plus user, which the operating system charged to the statement. This is a measurement, where `CPU_MS` above it is the duration minus the measured input, output and waiting and therefore also absorbs every wait nobody measured. A statement with a `parallel` clause includes the processor time of its workers, which makes the figure processor time spent rather than time passed: it can exceed `DURATION_MS`, and that is the point of running workers. The resolution of the figure is the resolution of the per-thread clock of the platform, which is coarser than a nanosecond on some of them; a statement short enough may therefore report zero rather than a small number. - `ROWS_AFFECTED` (`int64`): number of rows returned or changed. - `EXECUTION_STEPS` (`int32`): number of steps in the execution plan. - `SESSION_IOS` (`int32`): number of individual I/Os on the data containers. The I/Os themselves are available in `SessionIOs` of the [[Invantive UniversalSQL/Service Drivers/Data Dictionary Data Model/Data Model|Data Dictionary Data Model]]. - `ROUND_TRIPS` (`int64`): number of round trips to a platform. - `BYTES_RECEIVED` (`int64`): number of bytes received. - `BYTES_SENT` (`int64`): number of bytes sent. ## Outcome The following columns state how the statement ended: - `SUCCESSFUL` (`boolean`): whether the statement completed without an error. Empty while the statement is busy. - `ERROR_MESSAGE_CODE` (`varchar2`): message code of the error, such as `itgengpr015`. Empty when the error carried no code of its own, since a code which names nothing groups unrelated failures under one heading. - `ERROR_MESSAGE` (`varchar2`): text of the error. - `ON_ERROR_MODE` (`varchar2`): behaviour in effect on an error. One of `EXIT`, `CONTINUE`, `IGNORE` and `DEFAULT`. ## Technical Context The following columns tie the statement to the rest of the diagnostics: - `DATABASE_FULL_NAME` (`varchar2`): full name of the database the statement ran on. - `PROVIDER_NAME` (`varchar2`): name of the driver which executed the statement. - `AUDIT_ID` (`varchar2`): identifier of the audit record of the execution. - `FINGERPRINT` (`varchar2`): fingerprint of the execution. - `REQUEST_ID` (`varchar2`): identifier of the request. - `MANAGED_THREAD_ID` (`int32`): identifier of the thread which executed the statement. - `TASK_ID` (`int32`): identifier of the task which executed the statement. The query which reads this table is itself a statement and therefore appears in its own results, with `ENDED_UTC`, `DURATION_MS` and `SUCCESSFUL` still empty. Add `where ended_utc is not null` to leave it out. The following example lists the ten slowest statements which have ended: ```sql select sen.id , sen.statement_type , sen.sql_operation , sen.duration_ms , sen.rows_affected , substr(sen.statement_text, 1, 60) statement_text from StatementExecutions@script sen where sen.ended_utc is not null order by sen.duration_ms desc limit 10 ```