Skip to content
≡

Administrator

Developer

Tools

External Tools

altiProfile

altiProfile#

About altiProfile#

Altibase can write information about tasks that are executed on the server and server status to files for analysis. A file that contains information about server status is called a profile. altiProfile can convert a profile to character format or print STATEMENT statistics. The user can analyze the system status with this information.

altiProfile [-stat query|session] {profile_name [profile_name2 [profile_name3] ...}

Syntax#

Parameters#

Parameter Description
-h Displays help.
-stat query/session Builds statistics from profile STATEMENTs and prints them in text and CSV formats.
For more detailed information about statistics, please refer to How to use altiProfile.

Description#

altiProfile converts a server profile to character format or prints STATEMENT statistics.

Example#

iSQL> ALTER SYSTEM SET QUERY_PROF_FLAG = 1;
Alter success.
iSQL> ALTER SYSTEM SET TIMED_STATISTICS = 1;
Alter success.
iSQL>    --(Execute an SQL query here.)

$ cd $ALTIBASE_HOME/trc
$ altiProfile alti-1286503704-0.prof

$ altiProfile -stat query $ALTIBASE_HOME/trc/*.prof

How to use altiProfile#

The QUERY_PROF_FLAG property must be set to a value larger than 0 to write information about server status and tasks. The following information is logged for the QUERY_PROF_FLAG property:

Value Name Description
0 No logging.
1 [STATEMENT] Whenever a SQL statement is executed, the executed SQL statement, execution time, execution information, and information about index and disk access are output. The value for the TIMED_STATISTICS property must be set to 1 to print the proper execution time. For further information about the TIMED_STATISTICS property, please refer to the General Reference.
2 [BIND] Whenever a SQL statement is executed, BIND parameter(s) is/are output.
4 [PLAN] Whenever a SQL statement is executed, the execution plan is output
8 [SESSION STAT] Every 3 seconds, session information (i.e. the data in V$SESSTAT) is output.
16 [SYSTEM STAT] Every 3 seconds, system information (i.e. the data in V$SYSSTAT) is output.
32 [MEMORY STAT] Every 3 seconds, information about memory (i.e. the data in V$MEMSTAT) is output.

The above values can be combined to log the desired information. For example, if the property is set to 1+4+32=37, then whenever a SQL statement is executed, the execution information and execution plan for the SQL statement are output, and additionally, information about memory is output every 3 seconds.

If the QUERY_PROF_FLAG property is set to a nonzero value, the server will create and write to a profile file having a name that follows this convention:

Outputing Statistics#

altiProfile can use the -stat option to build and out statistical information about executed SQL statements. This information helps you find the SQL statement to tune.

The -stat query option builds statistics on the following:

  • COUNT: The number of times the query was executed.

  • AVG: The amount of time (in microseconds) the query took to execute, on average.

  • TOTAL: The amount of time (in microseconds) the query took to execute, in sum.

  • MIN: The minimum amount of time (in microseconds) the query took to execute.

  • MAX: The maximum amount of time (in microseconds) the query took to execute.

  • SUCCESS: The number of times the query succeeded.

  • FAIL: The number of times the query failed.

  • QUERY: The SQL statement that was executed.

When the -stat session option is used, SESSION ID is added to the statistics built using the query option.

The following is an example of altiProfile building statistics on SQL statements by analyzing profiles in the $ALTIBASE_HOME/trc directory.

$ altiProfile -stat query $ALTIBASE_HOME/trc/*.prof

## Processing [/altibase_home/trc/alti-1423543095-0.prof]...
100% [====================]

## Writing CSV File [alti-prof-stat-1423543711.csv]...

## Writing TEXT File [alti-prof-stat-1423543711.txt]...

## Successfully done.

In the example above, statistics are saved in text and CSV formats. The names of the files are automatically generated as 'alti-prof-stat-#time.csv' and 'alti-prof-stat-#time.txt'.

The following is an example of a text file. Statistics are displayed in order under the column TOTAL. 

$cat alti-prof-stat-1423543711.txt
COUNT      AVG          TOTAL         MIN         MAX    SUCCESS  FAIL   QUERY
===========================================================================================================
     5     0.003730     0.018650     0.003035     0.004640     5     0    DROP VIEW REVENUE
     5     0.003523     0.017616     0.003004     0.003745     5     0    CREATE VIEW REVENUE (
...

The following is an example of a CSV file. The content displayed is the same as that of a text file, except that it is in CSV format. CSV format allows the user to move data between programs.

$ cat alti-prof-stat-1423543711.csv
COUNT,AVG,TOTAL,MIN,MAX,SUCCES,FAIL,QUERY
5,  0.003730,  0.018650,  0.003035,  0.004640,5,0,"DROP VIEW REVENUE"
5,  0.003523,  0.017616,  0.003004,  0.003745,5,0,"CREATE VIEW REVENUE (
...

Precaution#

When the profiling function is activated, execution information is recorded for all SQL statements executed in the server, and the state of the server, such as session and system information, is recorded every 3 seconds. Therefore, the size of the profile file will increase rapidly, which could cause the disk to become full, consequently causing problems. So, care should be taken when considering whether to perform profiling.

Output#

Files are output in the follwing format.

[STATEMENT]
..
[BIND]
..
[PLAN]
..
[SESSION STAT]
..
[SYSTEM STAT]
..
[MEMORY STAT]
..

Each item is output as follows.

[STATEMENT]#

The following table shows the statement-related information that is logged.

Field Name Value Description
SQL String The SQL statement that was executed
User Info
User ID INTEGER The user identifier
Client PID BIGINT The identifier of the client process
Client Type VARCHAR(40) The type of the connected client
Client AppInfo VARCHAR(128) A string containing information about the client applicaiton
Elapsed Time for this SQL statemen
Total BIGINT The total query execution time
Parse BIGINT The time taken to parse the query
Valid BIGINT The time taken to validate the query
Optim BIGINT The time taken to optimize the query
Execu BIGINT The time taken to execute the query
Fetch BIGINT The time taken to fetch query results
Query Execute Info
EXECUTE Result INTEGER 0: failure 1: rebuild 2: retry 3: queue empty 4: success
Optimizer Mode BIGINT The optimization mode
Cost Mode BIGINT The optimization cost
Used Memory BIGINT Reserved for future use
SUCCESS SUM BIGINT The total number of successful executions
FAILURE SUM BIGINT The total number of failed executions
PROCESSED ROW BIGINT The number of processed records for this SQL statement
Result Set Info
FETCH Result INTEGER 0: failure 1: success 2: no results
Index Access Info
Memory Full Scan Count BIGINT The number of full scans that were performed on memory tables
Memory Index Scan Count BIGINT The number of index scans that were performed on memory tables
Disk Full Scan Count BIGINT The number of full scans that were performed on disk tables
Disk Index Scan Count BIGINT The number of index scans that were performed on disk tables
Disk Access Info
READ DATA PAGE BIGINT The number of disk pages that were read from disk for the query
WRITE DATA PAGE BIGINT not used
GET DATA PAGE BIGINT The number of buffers that were accessed for a disk page during query execution
CREATE DATA PAGE BIGINT The number of disk pages that were created during query execution
READ UNDO PAGE BIGINT The number of disk pages in UNDO tablespace that were read from disk during query execution
WRITE UNDO PAGE BIGINT not used
GET UNDO PAGE BIGINT The number of buffers in UNDO tablespace that were accessed for a disk page during query execution
CREATE UNDO PAGE BIGINT The number of disk pages in UNDO tablespace that were created during query execution

[BIND]#

Outputs information on variables that are bound to the SQL statement.

[PLAN]#

Outputs the execution plan for the executed SQL statement. For more information on execution plans, please refer to the Performance Tuning Guide.

[SESSION STAT]#

Outputs the data in the V$SESSTAT performance view every 3 seconds. For more information on the V$SESSTAT performance view, please refer to the chapter in the General Reference.

[SYSTEM STAT]#

Outputs the data in the V$SYSSTAT performance view every 3 seconds. For more information on the V$SYSSTAT performance view, please refer to the chapter in the General Reference.

[MEMORY STAT]#

Outputs the data in the V$MEMSTAT performance view every 3 seconds. For more information on the V$MEMSTAT performance view, please refer to the chapter in the General Reference.