1. Introduction to Performance Tuning

This chapter provides an introduction to Altibase performance tuning.

• Introduction to SQL Tuning

• Database Server Tuning

• SQL Tuning

Introduction to SQL Tuning

Database system tuning indicates a series of operations that aim to enhance performance. More specifically, performance enhancement is as follows:

• Database server throughput maximization
• Query response time minimization
• Resource usage minimization

This guide provides tuning information for Altibase in the following categories:

• Database Server Tuning
• SQL Tuning

Database Server Tuning

Altibase server tuning consists of:

• Performance-related Properties
• Performance-related Statistics
• Operating System Resource Management
• Memory configuration and usage
• I/O Configuration and design

It is important to have a proper initial database design to avoid bottlenecks that can lead to performance issues. The following server operation issues are also consequential:

• Checkpoint I/O
• Logfile writing
• Service thread bottlenecks
• Memory ager
• Buffer
• SQL Plan Cache

The next chapter explains the tuning components related to server operation.

Performance-related Properties

The altibase.properties files must be modified accordingly for optimal performance. The following is a list of properties related to performance. For a detailed description of each property, please refer to the General Reference > Chapter 2. Altibase Properties > Performance Properties.

• AGER_WAIT_MAXIMUM
• AGER_WAIT_MINIMUM
• BUFFER_VICTIM_SEARCH_INTERVAL
• BUFFER_VICTIM_SEARCH_PCT
• BULKIO_PAGE_COUNT_FOR_DIRECT_PATH_INSERT
• CHECKPOINT_BULK_SYNC_PAGE_COUNT
• CHECKPOINT_BULK_WRITE_PAGE_COUNT
• CHECKPOINT_BULK_WRITE_SLEEP_SEC
• CHECKPOINT_BULK_WRITE_SLEEP_USEC
• CHECKPOINT_FLUSH_COUNT
• CHECKPOINT_FLUSH_MAX_GAP
• CHECKPOINT_FLUSH_MAX_WAIT_SEC
• CM_BUFFER_MAX_PENDING_LIST
• CM_DISPATCHER_SOCK_POLL_TYPE
• DATABASE_IO_TYPE
• DATAFILE_WRITE_UNIT_SIZE
• DB_FILE_MULTIPAGE_READ_COUNT
• DEDICATED_THREAD_CHECK_INTERVAL
• DEDICATED_THREAD_INIT_COUNT
• DEDICATED_THREAD_MAX_COUNT
• DEDICATED_THREAD_MODE
• DEFAULT_FLUSHER_WAIT_SEC
• DIRECT_IO_ENABLED
• DISK_INDEX_BUILD_MERGE_PAGE_COUNT
• EXECUTE_STMT_MEMORY_MAXIMUM
• EXECUTOR_FAST_SIMPLE_QUERY
• FAST_START_IO_TARGET
• FAST_START_LOGFILE_TARGET
• HASH_AREA_SIZE
• HASH_JOIN_MEM_TEMP_PARTITIONING_DISABLE
• HASH_JOIN_MEM_TEMP_AUTO_BUCKET_COUNT_DISABLE
• HIGH_FLUSH_PCT
• HOT_LIST_PCT
• HOT_TOUCH_CNT
• INDEX_BUILD_THREAD_COUNT
• INDEX_INITRANS
• INDEX_MAXTRANS
• INSPECTION_LARGE_HEAP_THRESHOLD
• LFG_GROUP_COMMIT_INTERVAL_USEC
• LFG_GROUP_COMMIT_RETRY_USEC
• LFG_GROUP_COMMIT_UPDATE_TX_COUNT
• LOB_CACHE_THRESHOLD
• LOCK_ESCALATION_MEMORY_SIZE
• LOG_IO_TYPE
• LOW_FLUSH_PCT
• LOW_PREPARE_PCT
• MAX_FLUSHER_WAIT_SEC
• MAX_THREAD_COUNT
• MEM_INDEX_KEY_REDISTRIBUTION
• MEM_INDEX_KEY_REDISTRIBUTION_STANDARD_RATE
• MULTIPLEXING_CHECK_INTERVAL
• MULTIPLEXING_MAX_THREAD_COUNT
• MULTIPLEXING_THREAD_COUNT
• NORMALFORM_MAXIMUM
• OPTIMIZER_FEATURE_ENABLE
• OPTIMIZER_MODE
• OPTIMIZER_UNNEST_AGGREGATION_SUBQUERY
• OPTIMIZER_UNNEST_COMPLEX_SUBQUERY
• OPTIMIZER_UNNEST_SUBQUERY
• OUTER_JOIN_OPERATOR_TRANSFORM_ENABLE
• PARALLEL_LOAD_FACTOR
• PARALLEL_QUERY_THREAD_MAX
• PARALLEL_QUERY_QUEUE_SIZE
• PREPARE_STMT_MEMORY_MAXIMUM
• QUERY_REWRITE_ENABLE
• REFINE_PAGE_COUNT
• SHM_PAGE_COUNT_PER_KEY
• SECONDARY_BUFFER_ENABLE
• SECONDARY_BUFFER_FILE_DIRECTORY
• SECONDARY_BUFFER_FLUSHER_CNT
• SECONDARY_BUFFER_SIZE
• SECONDARY_BUFFER_TYPE
• SORT_AREA_SIZE
• SQL_PLAN_CACHE_BUCKET_CNT
• SQL_PLAN_CACHE_HOT_REGION_LRU_RATIO
• SQL_PLAN_CACHE_PREPARED_EXECUTION_CONTEXT_CNT
• SQL_PLAN_CACHE_SIZE
• STATEMENT_LIST_PARTIAL_SCAN_COUNT
• TABLE_INITRANS
• TABLE_LOCK_ENABLE
• TABLE_MAXTRANS
• TABLESPACE_LOCK_ENABLE
• TEMP_STATS_WATCH_TIME
• THREAD_CPU_AFFINITY
• TIMED_STATISTICS
• TIMER_RUNNING_LEVEL
• TIMER_THREAD_RESOLUTION
• TOTAL_WA_SIZE
• INIT_TOTAL_WA_SIZE
• TOUCH_TIME_INTERVAL
• TRANSACTION_SEGMENT_COUNT
• TRX_UPDATE_MAX_LOGSIZE

Performance-related Statistics

When a server thread waits for an event in order to continue a task, wait event statistics increase. If the statistical values of a certain wait event are high, it could mean that the wait event is adversely affecting performance.

A list of performance views related to wait events is provided below. For more detailed information about each performance view, please refer to General Reference > Chapter 3. Data Dictionary.

• V$SESSION_EVENT

• V$SESSION_WAIT

• V$SESSION_WAIT_CLASS

• V$SYSTEM_EVENT

• V$SYSTEM_WAIT_CLASS

The following performance views store various statistics on Altibase (e.g., memory usage, process status, session, or buffer pool statistics). For more detailed information about each performance view, please refer to General Reference > Chapter 3. Data Dictionary.

• V$BUFFPAGEINFO

• V$BUFFPOOL_STAT

• V$DBMS_STATS

• V$FLUSHER

• V$FLUSHINFO

• V$LATCH

• V$LOCK_WAIT

• V$MEMSTAT

• V$SERVICE_THREAD

• V$SESSTAT

• V$SYSSTAT

• V$UNDO_BUFF_STAT

Operating System Resource Management

Resource usage in Altibase can be checked with the following commands and monitoring tools provided by operating systems.

	AIX	HPUX	LINUX
Performance Monitor	top topas nmon	top glance	top
System Activity Reporter	sar	sar	sar
Virtual Memory Statistics	vmstat	vmstat	vmstat
I/O Statistics	iostat	iostat	iostat
Error Log	errpt	dmesg	dmesg

SQL Tuning

An unoptimized query can degrade overall performance by increasing access to unnecessary data or resource usage. SQL tuning indicates a series of operations that optimize queries by eliminating these factors and includes:

• Rewriting SQL statements
• Redesigning database schemas
• Adjusting Altibase properties
• Adjusting operating system kernel parameters

Altibase's query processing methods and limitations affect query performance. Therefore, the user must understand how Altibase processes queries to rewrite SQL statements and redesign database schemas. The user must also understand how storage media characteristics affect query processing as both memory and disk tables are available for use.

This section briefly describes performance-related issues and explains the basic concepts of SQL tuning.

Performance-related Issues

The Execution Plan Tree

When a client requests the execution of a SQL statement, the query processor checks the syntax and validity of the statement, optimizes it and creates an execution plan (i.e. a definition of the execution steps). Since the execution plan is built in a tree structure, this manual uses the terms "execution plan tree" and "plan tree" interchangeably. The execution plan determined to be most efficient by the query optimizer has a significant impact on the execution time of a SQL statement.

SQL Plan Cache

In Altibase, the execution plans of SQL statements can be shared between sessions. This reduces the cost of creating an execution plan every time a SQL statement is executed. The SQL_PLAN_CACHE_SIZE property should to be set appropriately to use this functionality.

For more detailed information, please refer to Chapter 7. SQL Plan Cache.

Database Schema and Data Volume

Whether to deploy memory tables or disk tables depends on client application characteristics and resource efficiency. It is generally recommended to store frequently used data in memory tables and infrequently used data in disk tables.

When creating table schemas and indexes, due consideration should be given to SQL statement types used in applications so that the number of times a table is accessed and the number of records/disk pages that are accessed for each SQL statement are of minimum cost.

Moreover, the cost of comparing column values within predicates adversely affects performance for simple SQL statements or tables containing large numbers of records. It is important to select appropriate data types to minimize data conversion and comparison costs.

Therefore, SQL statements should be written so that they access the minimum number of records to reduce the number of comparison operations. The appropriate data types for columns and to-be-compared values should also be selected to avoid data conversion for comparison operations.

Application Logic (Table Access Order)

If several clients have established sessions with the Altibase database, the table access order of each client may affect system performance. If a single transaction uses several DML statements to access multiple tables but the application logic was poorly defined (i.e. inappropriate table access order), application performance can suffer due to the time spent waiting to acquire locks. Therefore, the user should exercise caution when defining application logic.

System Resources

When a SQL statement is executed, system resources such as the amount of available memory, memory buffer size, disk performance and CPU performance affect performance.

The query performance of memory tables is unaffected by disk performance, whereas query performance of disk tables is affected by both disk performance and the amount of available space in the memory buffer. Consequently, the user can expect consistent query response time for memory tables but not disk tables. This is because performance depends on the environment at the point in time at which the statement was executed for disk tables.

The query processor uses the sorting or hashing method to process queries that contain the ORDER BY or GROUP BY clause (or the like) and either memory temporary tables or disk temporary tables are used to store intermediate results.

When intermediate results are stored in memory temporary tables, they are stored in a memory area unavailable to the memory database. The user must be careful that memory swapping does not occur when executing queries (as large memory tables can require additional space) to avoid performance degradation.

When intermediate results are stored in disk temporary tables, disk space occupied by the disk temporary tablespace and memory buffer space are consumed. The amount of used memory buffer space has a big impact on performance.

In addition, query processing entails multiple operations for processing operators and this uses a lot of CPU. CPU usage and CPU performance also greatly affect query performance.

SQL Tuning Methodology

To minimize query response time, the user can take the following basic tuning steps with iSQL.

1. Find a query statement with a slow response time with the profiling feature.
2. Set the explain plan to be visible in iSQL.
3. Enable 'Set Timing On' in iSQL so it displays the query execution time.
4. Execute the query and check the execution plan.
5. Shorten query response time by changing the SQL statement, setting an index, using hints, etc.

This section briefly describes the tools available for SQL tuning and how to use them.

Profiling

The profiling functionality collects the following information about the SQL statement requested by the user:

• The client that requested the query
• The point in time at which the query was executed
• The query statement
• The length of time spent on execution
• The index which was used
• The cost of accessing the buffer/disk  The execution plan

The profiling functionality can be used by setting the QUERY_PROF_FLAG property with the ALTER SYSTEM SET .. statement. Different information is collected, depending on the value set for this property.

For more detailed information, please refer to General Reference > Chapter 2. Altibase Properties.

How to Measure SQL Performance

When developing an application using APRE, ODBC, or JDBC, the actual query execution time can be measured with a time measurement function within the application. The query execution time can also be measured with the following iSQL command.

SET TIMING ON;

For memory tables, the repeated execution of queries in iSQL offers almost consistent performance. However, performance improves with repeated executions for disk tables because fewer buffer replacements occur as queries are repeated. This means that if multiple queries are concurrently executed on disk tables, buffer replacements make it impossible to guarantee consistent performance.

Nevertheless, a SQL statement that has been tuned for better performance in iSQL generally leads to as much improvement in the actual application as well.

Analyzing Execution Plan Trees

The execution plan trees for DML statements (SELECT, INSERT, UPDATE or DELETE) can only be checked in iSQL. Since the DELETE, UPDATE, and MOVE statements are optimized in the same way as SELECT statements, the same execution plan trees are internally created.

For INSERT statements, only the execution plan tree for the SELECT portion of an INSERT INTO SELECT statement can be checked.

The user should check the following in an execution plan tree. Further information is provided in the next section

• Is the access method efficient?
• Are the joining method and order appropriate?
• Are the data types and operations suitable?
• Is any unnecessary hashing or sorting performed?

The user can solve performance degradation by analyzing the execution plan, finding its cause and taking appropriate action.

SQL Tuning Methodology

A basic SQL tuning methodology is provided below. For more detailed information about each method, please refer to Chapter 3. The Query Optimizer and Chapter 6. SQL Hints.

• Check whether an efficient index exists. If necessary, add an index.
• Optimize the join order and method.
• Minimize sorting and hashing operations.
• Use hints.

Query Processing Overview

When tuning the SQL statements, it is not always easy to suggest a definite rule that applies to all applications for optimal performance. There are many methods to tune SQL, depending on the design of the database or the business logic of the application. Due to the structure of the database or business logic of applications, the methods of SQL tuning are varied. To write optimal SQL statements, an understanding of "Altibase Query Processing" is essential.

In this section, it is explained how one of SQL statement processes through specific steps and what factors could influence the performance.

Steps in Query Processing

The query processor is the DBMS module that is responsible for processing SQL statements. The query processor checks the validity of the user-requested SQL statement, decides the optimum order and method to access the database, searches records that satisfy the conditions, performs the necessary operations and returns the processed values to the client.

The following figure displays the basic steps in query processing.

[Figure 1-1] The Basic Steps in Query Processing

Each step performs the following task:

1. Parsing

   The SQL statement is checked for correct syntax and a parse tree containing analytic information about the statement is created.

2. Validation

   The SQL statement is checked for semantic validation and the parse tree is expanded using data retrieved from meta tables.

3. Optimization

   An optimal execution plan based on various statistics and access costs derived from the parse tree is created.

4. Binding

   Host variables are bound to the execution plan.

5. Execution

   The SQL statement is executed according to the execution plan tree.

The user must have a good knowledge of Altibase's query optimization and query execution process to tune queries. This document explores query tuning methods based on the above.

Memory Tables and Disk Tables

Altibase (Hybrid Database) is a database management system which supports both memory and disk tables. The user must understand the differences between query processing methods for memory and disk tables to tune queries.

The fundamental differences between the query processor for memory and disk tables are as follows.

Query Processor	Item	Memory Table	Disk Table
Executor	Object identifier	Pointer	OID(RID)
	Buffer management	N/A	Limited buffer
	Join methods	One-pass algorithms	Multi-pass algorithms
Optimizer	Main cost	CPU	Disk
	Index selection	Minimize record access	Minimize disk I/O
	Cost factor	T(R), V(R.a), etc	+ B(R), M
Reference	T(R): Number of records in Table R V(R.a): Cardinality of values in column R.a B(R): Number of disk pages for Table R M: Number of available memory buffers

The query processor consists of the optimizer and the executor. The optimizer creates an execution plan tree by calculating the statement execution cost, whereas the executor actually executes the statement according to each node of the execution plan tree.

The Altibase executor and optimizer are designed to fully reflect differences in both storage media types while reproducing the storage media characteristics when creating execution plans and processing queries.

The Executor

As seen above, the basic concept and operation of the executor differ, depending on which storage medium the table is saved on.

The "object identifier" (which identifies records) is a pointer for memory tables, but is an identifier that can be converted to a particular disk address (e.g., a resource identifier (RID)) for disk tables. This indicates that a memory table record can be directly accessed, whereas a disk table access requires address conversion.

The server does not use buffers when processing queries on memory tables. In contrast, queries on disk tables are processed within a limited memory buffer size. If a desired record cannot be found (buffer miss), buffer replacement occurs and this causes disk I/O.

For join operations, intermediate results are stored when necessary. These results are processed using either a one-pass or multi-pass algorithm, depending on whether or not all the results can be loaded into a limited amount of memory in a single operation. A one-pass algorithm is used when all intermediate results can be loaded into the available memory, whereas a multi-pass algorithm is used when all intermediate results cannot be loaded into available memory (to minimize buffer replacement). For instance, a one-pass algorithm can be used for memory tables (because there is no buffer limit). However, disk tables may use either a one-pass or multi-pass algorithm (depending on the buffer limit).

As outlined above, the executor's query processing method differs according to the storage media type and also shows a drastic difference in performance.

The Optimizer

Like the executor, the basic concept and operation of the query optimizer differ, depending on which storage medium the table is saved on.

The optimizer creates an execution plan that minimizes CPU usage when querying memory tables. In contrast, the optimizer creates an execution plan that minimizes disk I/O when querying disk tables. That is, the optimizer creates an execution plan that minimizes the resource usage that has the biggest influence on query performance.

When selecting the access method for memory tables, the optimizer selects the index to minimize the number of records to be read. For disk tables, however, the optimizer selects the access method that minimizes disk I/O. This is because an index scan generally guarantees better performance than a full table scan for memory tables. This is not necessarily so for disk tables. Depending on the data distribution, a full table scan can result in lesser disk I/O than an index scan.

The optimizer uses various statistical data to calculate costs. For example, the number of records in a table [T(R)], the number of different values in a column [V(R.a)], and the maximum and minimum values in a column are used to calculate the cost of a memory table query. The optimizer requires additional statistical data to calculate the cost of a disk table query (i.e. the number of disk pages being used by a table [B(R)] and the number of available memory buffer pages [M]).