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