Skip to end of metadata
Go to start of metadata

 

Overview

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

Property NameDescription
QUERY_PROF_FLAG

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].

QUERY_PROF_LOG_DIR

(6.5.1 or later)

Directory path where the profiling result file will be saved

Default value : $ALTIBASE_HOME/trc

QUERY_PROF_BUF_SIZE

Size of the buffer in which statistics information will be stored

Default value : 1MBytes

Range : 32KBytes ~ 4GBytes-1

QUERY_PROF_BUF_FLUSH_SIZE

Set the size to flush when the buffer is full

Default value : 32KBytes

Range : 512Bytes ~ 4GBytes-1

QUERY_PROF_BUF_FULL_SKIP

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)

QUERY_PROF_FILE_SIZE

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

  • Start 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.)

  • Stop profiling
    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
Output format

[STATEMENT] Record time (session_ID/SQL statement_ID/transaction_ID)

   SQL statement

   User information :

   Time it took to execute

   Execution information: Success/failure status and number of success/failures

   Index access information

   Disk access information

Example
  • 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.
Example
  • When QUERY_PROF_FLAG = 4 is set
    Output the execution plan information created for the execution of the SQL statement.
Example
  • 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

Example
  • 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.
Example
  • 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.
Output format

[MEMORY STAT] recording time

Module name: (Current amount of memory used / number of unit memory / maximum amount of memory used)

Example

 

Precautions

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.

  • No labels