Collect and Evaluate Sampling of Runtimes for SQL Statements
Performance impact: Variable
Instead of tracing, the built-in procedures sample the runtime of SQL statements
during processing. The built-in procedures measure the runtime of SQL statements by timing the
processing within the SQLExecute API.
You can configure the sampling rate and how the runtimes are collected with the ttStatsConfig built-in procedure and the following name-value pairs:
Note:
See ttStatsConfig in the Oracle TimesTen In-Memory Database Reference.
Table 10-1 ttStatsConfig Parameter and Value Descriptions
| Parameter | Description |
|---|---|
|
|
Configures how often a SQL statement runtime sample is taken. The default is 0, which means that the sampling is turned off. For example, when set to 10, TimesTen captures the wall clock time of the SQL statement runtime for every 10th statement. |
|
|
Configures how often a SQL statement sample is taken for an individual connection. The value includes two parameters separated by a comma within quotes, so that it appears as a single value. The first number is the connection ID; the second is the same as the |
|
|
When set to a nonzero value, clears the SQL runtime histogram data. |
|
|
Sets the level of statistics to be taken. Values can be set to either |
The following are examples of how to set the name-value pairs with the ttStatsConfig built-in procedure:
Note:
You can achieve the best results by choosing representative connections with the ConnSampleFactor parameter in the ttStatsConfig built-in procedure, rather than sampling all transactions. Sampling all transactions with a small sample factor can affect your performance negatively.
For meaningful results, the database should remain in memory since unloading and re-loading the database empties the SQL command cache.
Sample every 5th statement on connection 1.
Command> call ttStatsConfig('ConnSampleFactor', '1,5');
< CONNSAMPLEFACTOR, 1,5 >
1 row found.Turn off sampling on connection 1.
Command> call ttStatsConfig('ConnSampleFactor', '1,0');
< CONNSAMPLEFACTOR, 1,0 >
1 row found.Sample every command:
Command> call ttStatsConfig('SqlCmdSampleFactor',1);
< SQLCMDSAMPLEFACTOR, 1 >
1 row found.Check whether sampling:
Command> call ttStatsConfig('SqlCmdSampleFactor');
< SQLCMDSAMPLEFACTOR, 1 >
1 row found.Check the current database statistics collection level.
Command> call ttStatsConfig('StatsLevel');
< STATSLEVEL, TYPICAL >
1 row found.Turn off database statistics collection by setting to NONE.
Command> call ttStatsConfig('StatsLevel','None');
< STATSLEVEL, NONE >
1 row found.Once you have configured the statistics that you want collected, the collected
statistics are displayed with the ttSQLCmdCacheInfo built-in procedure. To
display the runtime histogram at either the command or database levels, use the
ttSQLExecutionTimeHistogram built-in procedure.
The ttSQLCmdCacheInfo built-in procedure displays the following information relating to SQL runtime statistics:
-
Number of fetch executions performed internally for this statement.
-
The timestamp when the statement started.
-
The maximum wall clock run time in seconds of this statement.
-
Last measured runtime in seconds of the statement.
-
The minimum runtime in seconds of the statement.
In the following example, the display shows these statistics as the last five values:
Command> vertical call ttSQLCmdCacheInfo(135680792); SQLCMDID: 135680792 PRIVATE_COMMAND_CONNECTION_ID: -1 EXECUTIONS: 97414 PREPARES: 50080 REPREPARES: 1 FREEABLE: 1 SIZE: 3880 OWNER: SALES QUERYTEXT: select min(unique2) from big1 FETCHCOUNT: 40 STARTTIME: 2018-04-10 13:10:46.808000 MAXEXECUTETIME: .001319 LASTEXECUTETIME: .000018 MINEXECUTETIME: .000017 EXECLOC: 0 GRIDCMDID: 00000000000000000000 TEMPSPACEUSAGE: 0 MAXTEMPSPACEUSAGE: 0 1 row found.
See ttSQLCmdCacheInfo in the Oracle TimesTen In-Memory Database Reference.
The ttSQLExecutionTimeHistogram built-in procedure displays a histogram of SQL runtimes for either a single SQL command or all SQL commands in the command cache, assuming that sampling is enabled where SQLCmdSampleFactor is greater than zero.
The histogram displays a single row for each bucket of the histogram. Each row includes the following information:
-
The number of SQL statement runtime operations that have been measured since either the TimesTen database was started or after the
ttStatsConfigbuilt-in procedure was used to reset statistics. -
Accumulated wall clock runtime.
-
The runtime limit that denotes each time frame.
-
The last row shows the number of SQL statements that ran in a particular time frame.
The following example shows the output for the ttSQLExecutionTimeHistogram built-in procedure:
The following example of the ttSQLExecutionTimeHistogram built-in procedure shows that a total of 1919 statements ran. The total time for all 1919 statements to run was 1.090751 seconds. This example shows that SQL statements ran in the following time frames:
-
278 statements ran in a time frame that was less than or equal to .00001562 seconds.
-
1484 statements ran in a time frame that was greater than .00001562 seconds and less than or equal to .000125 seconds.
-
35 statements ran in a time frame that was greater than .000125 seconds and less than or equal to .001 seconds.
-
62 statements ran in a time frame that was greater than .001 seconds and less than or equal to .008 seconds.
-
60 statements ran in a time frame that was greater than .008 seconds and less than or equal to .064 seconds.
Command> call ttSQLExecutionTimeHistogram; < 1919, 1.090751, .00001562, 278 > < 1919, 1.090751, .000125, 1484 > < 1919, 1.090751, .001, 35 > < 1919, 1.090751, .008, 62 > < 1919, 1.090751, .064, 60 > < 1919, 1.090751, .512, 0 > < 1919, 1.090751, 4.096, 0 > < 1919, 1.090751, 32.768, 0 > < 1919, 1.090751, 262.144, 0 > < 1919, 1.090751, 9.999999999E+125, 0 > 10 rows found.