Skip to end of metadata
Go to start of metadata
Icon

Before testing the queries in this document, please make sure to set TIMED_STATISTICS property value to "1" as shown below.

Most of performance problems are caused by user application logic. Therefore, when facing performance problems, checking the application is always a good staring point.

The following are the most frequently observed  cases from applications side.

Preparing statement for every execution ( or using dynamic SQL)

When you prepare a statement, ALTIBASE HDB performs parsing , validating and optimizing for the given statement. (See also How a query is executed in ALTIBASE HDB)  The statement prepared earlier can be reused, so the overhead of preparing statement can be eliminated. In the same context, Dynamic SQL can cause performance problems.

Even if you may not have access to the application source code, you can find out how many times an application prepares statements. ALTIBASE HDB provides v$sysstat / v$sesstat performance view tables which contain the information of statements.

  • The number of prepare operations occurred
    Normally, "execute success count" is much bigger than "prepare success count". If not, you need to check your application.
    Icon

    You can get the various information from V$SYSSTAT or V$SESSTAT. Please refer the V$STATNAME explanation in the Administrators Manual to get more information in detail.

A bad SQL that consumes a lot resources

A SQL that performs full table or index scans can cause serious performance problems. You can locate "bad SQL statements" in your ALTIBASE HDB environment by using the following queries.

  • The longest "Top 5 queries" currently running in ALTIBASE HDB server
  • A query that has a "FULL SCAN" plan node.

Connecting and disconnecting for every DBMS request

The cost of creating a connection is expensive for both ALTIBASE HDB and applications. It can cause serious performance degradation. You can find out how many times an application makes connections by using the following query.

  • The cumulative connection count after a server start-up.
    Note that this value is a snapshot of the current cumulative connection count. In order to get meaningful values, you have to get the results for several times in short period of time.

Consider IPC connection if a client and a database both reside on the same machine

ALTIBASE HDB provides three communication protocols.

Connection Type

Description

TCP / IP (CON_TYPE=1)

Default connection type in ALTIBASE HDB. ALTIBASE HDB uses TCP/IP socket to communicate with the client. The client located on the different machine can connect to ALTIBASE HDB by using this connection.

Unix Domain (CON_TYPE=2)

Unix Domain uses "pipe" provided by Unix OS. It is faster than TCP/IP but application and ALTIBASE HDB have to be running on the same machine.

IPC (Inter-Process Communication) (CON_TYPE=3)

The fastest connection type between the client and ALTIBASE HDB. Like Unix domain, application and ALTIBASE HDB have to be running on the same machine.

Icon

Note that you must set the value of IPC_CHANNEL_COUNT in altibase.properties bigger than 0 before using IPC connection. IPC_CHANNEL_COUNT is the maximum concurrent connection counts for IPC connection type. Default value is 0. (IPC connection is disabled.)

In order to increase the performance of ALTIBASE HDB, you may want to consider using "IPC" connection type whenever possible. If you want to determine what connection type an application uses, you can use the following query.

  • The session count categorized by the connection type
  • No labels