Page History

ALTER SYSTEM SET TIMED_STATISTICS = 1;

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

  Code Block
  lang sql
  SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS') CUR_TIME, RPAD(NAME, 50) NAME , VALUE FROM V$SYSSTAT WHERE SEQNUM IN (27, 29);

  Normally, "execute success count" is much bigger than "prepare success count". If not, you need to check your application.

  Tip
  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

  Code Block
  lang sql
  SELECT EXECUTE_TIME/ 1000000 EXEC_SECOND, RPAD(QUERY, 500) QUERY FROM V$STATEMENT WHERE EXECUTE_FLAG = 1 ORDER BY 1 DESC LIMIT 5;

• A query that has a "FULL SCAN" plan node.

  Code Block
  lang sql
  SELECT RPAD(QUERY,200) , COUNT(*) CNT FROM V$STATEMENT WHERE (SESSION_ID, ID) IN ( SELECT SID , STMT_ID FROM V$PLANTEXT WHERE TEXT LIKE '%FULL%' ) GROUP BY QUERY

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.

  Code Block
  lang sql
  SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS') CUR_TIME, RPAD(NAME, 50) NAME , VALUE FROM V$SYSSTAT WHERE SEQNUM = 1;

  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.

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

  Code Block
  lang sql
  SELECT SUBSTR(COMM_NAME, 1,4) CON_TYPE, COUNT(*) CNT FROM V$SESSION GROUP BY SUBSTR(COMM_NAME, 1,4) ;