All SQL statements executed on the Altibase server can be traced.
It is possible by setting/disabling Altibase properties, and in addition to SQL statements, it is possible to trace execution time, index/disk access information, PLAN information, session information, and ALTIBASE HDB system information.
This chapter describes how to profile Altibase and check the results.
Profiling related properties
Whether to profile and set logging level
0 : Disable profiling (default)
1 : Whenever an SQL statement is executed, the SQL statement, execution time, execution information, and index/disk access information are displayed.
2 : Output Bind Parameter value every time the prepared SQL statement is executed (limited to the case where there is variable value binding information.)
4 : Output execution plan every time SQL statement is executed
8 : Output session information (V$SESSTAT) every 3 seconds
16 : Output system information (V$SYSSTAT) every 3 seconds
32 : Ouput memory information (V$MEMSTAT) every 3 seconds
It is also possible to set by combining values in bit units.
For example, if it is set to 7 (1 + 2 + 4), information such as the plan, the value of the variable to be bound, the statement and execution time, etc. are displayed at each SQL statement execution.
If it is set to 63(1 + 2 + 4 + 8 + 16 + 32), all printable information is displayed. When executing each SQL statement, the SQL statement execution information is displayed in the order of [BIND] [PLAN] [STATEMENT].
Altibase status information is output every 3 seconds in the order of [SESSION] [SYSTEM] [MEMORY].
(6.5.1 or later)
Default value : $ALTIBASE_HOME/trc
Size of the buffer in which statistics information will be stored
Default value : 1MBytes
Range : 32KBytes ~ 4GBytes-1
Set the size to flush when the buffer is full
Default value : 32KBytes
Range : 512Bytes ~ 4GBytes-1
Choose whether to skip profiling and wait for all data to be recorded when the statistics buffer is full.
Default value : 1
Range : 0(wait) or 1(skip)
Maximum size of profiling log file
Default value : 100MBytes
Range : 0 ~ 4GBytes-1
0 : Limited by OS limits
> 0 : Log as much as the size is recorded, and after closing, a new file is created to continue logging. The log file name is created in the same format as alti-#time-#number.prof, and the number starts from 0 and increases by 1
How to start and stop profiling
Record information on all SQL statements executed after the next command is executed in the log file.
Value: Refer to the description of the QUERY_PROF_FLAG property above.
TIMED_STATISTICS: In version 5.1.5 or later, to check the execution time of an SQL statement, this property value should be set to 1 (default is 0). (In versions earlier that, there is no corresponding property, and you can check the execution time of all SQL statements by default.)
To stop profiling, execute the following command:
How to analyze the results
When profiling starts, a log file is created in the format $ALTIBASE_HOME/trc/alti#time-#number.prof. (In version 6.5.1 or later, the log file path can be set in the QUERY_PROF_LOG_DIR property)
Since the log file is in binary format, it must be converted to a text file using the altiProfile command. The method is as follows.
Since the log file conversion result is output to stdout, it is recommended to save it as a file as in the example above.
The following is a description of the contents of the converted result.
- When QUERY_PROF_FLAG = 1 is set
- When QUERY_PROF_FLAG = 2 is set
The variable value bound to the prepared SQL statement is output, but it is output in binary type and cannot be analyzed by the user.
- When QUERY_PROF_FLAG = 4 is set
Output the execution plan information created for the execution of the SQL statement.
- When QUERY_PROF_FLAG = 8 is set
For all sessions created in the current server, session information is recorded as follows, and it is the same as the result of executing select * from v$sesstat order by sid, seqnum.
- When QUERY_PROF_FLAG = 16 is setIt
Output the overall contents of the Altibase system and is the same as the result of executing select * from v$sysstat order by seqnum.
- When QUERY_PROF_FLAG = 32 is set
Output the memory usage of each Altibase module at that point, and is the same as the result of executing select * from v$memstat order by seqnum.
When profiling is enabled, execution information for all SQL statements executed in the Altibase server is recorded in the log file, and the Altibase status is profiled every 3 seconds according to the setting, which can affect Altibase performance as well as the load on the system.
In addition, there is a possibility that a disk pool may occur due to high disk usage due to log recording by profiling.
Therefore, it is not recommended to enable profiling on operation servers by default.
It is recommended to use it for a short time during testing, performance analysis, and tuning. When profiling, be sure to monitor the disk usage together and stop it appropriately.