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
How to start and stop profiling
Record information on all SQL statements executed after the next command is executed in the log file.
ALTER SYSTEM SET QUERY_PROF_FLAG = value;
ALTER SYSTEM SET TIMED_STATISTICS = 1;
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:
ALTER SYSTEM SET QUERY_PROF_FLAG = 0;
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.
$ altiProfile alti-#time-#number.prof> #number.out
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
[STATEMENT] Record time (session_ID/SQL statement_ID/transaction_ID)
User information :
Time it took to execute
Execution information: Success/failure status and number of success/failures
Index access information
Disk access information
- 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.
[SESSION STAT] Record time (Session_ID)
Field name = value
- 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.
[MEMORY STAT] recording time
Module name: (Current amount of memory used / number of unit memory / maximum amount of memory used)
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.