DB2 Performance Tuning design and configuration of relevant database practices

Today we will mainly talk about the DB2 Performance Tuning design and configuration of your database. There are many database design and configuration options that can affect the query performance. For more suggestions on Database Design, see the "Planning your Physical Database Design" Best Practices article.

Use constraints of DB2 performance optimization to improve query optimization

Consider the uniqueness of definitions, check and refer to consistency constraints. These constraints provide semantic information that allows the DB2 optimizer to override queries to evaluate connections and reduce aggregation and fetch first n rows through connections, remove unnecessary DISTINCT options and some other optimizations. When an application can ensure its own relationship, Information constraints can also be used to check and reference consistency constraints. The same optimization is also possible.

When updating (inserting or deleting) rows, mandatory constraints from the Database Manager may cause high system overhead, especially when updating many rows with consistency constraints. If an application has verified the information before updating a row, the use of information constraints is more effective than normal constraints.

For example, consider two tables DAILY_SALES and CUSTOMER. Each row in the CUSTOMER table has a unique CUSTOMER key value (CUST_KEY ). DAILY_SALES contains a CUST_KEY column and each row references the CUSTOMER key in a CUSTOMER table. You can create a reference consistency constraint to prevent a 1: N relationship between CUSTOMER and DAILY_SALES.

If the application needs to force this relationship, you can create an information constraint. The following query avoids the connection between CUSTOMER and DAILY_SALES, because no column is obtained from CUSTOMER, and each row from DAILY_SALES can find matching rows in CUSTOMER, therefore, the query optimizer automatically deletes the connection.

 
 

  
  
SELECT AMT_SOLD, SALE PRICE, PROD_DESC   
  
  
FROM DAILY_SALES, PRODUCT, CUSTOMER   
  
  
WHERE   
  
  
DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY AND   
  
  
DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY  
 
 

The application must execute Information constraints, otherwise the query may return incorrect results. In the preceding example, if the row exists in DAILY_SALES and the corresponding CUSTOMER key cannot be found in the CUSTOMER table, the rows returned by the preceding query may be incorrect.

DB2 Performance Tuning: Using REOPT binding options and input variables in complex queries

In an online transaction processing (OLTP) environment, it is critical that the input variable has a good statement preparation time. In such an environment, the statement is often relatively simple and the query plan is also very easy to choose. Using different input variables to run the same statement multiple times can reuse the access fragments compiled in the dynamic statement cache, this avoids expensive SQL statement compilation overhead due to changing input values at any time.

However, input variables also cause problems for complex query loads, and their query plan selection is very complicated. Therefore, the optimizer needs more information to make a good decision. In addition, the statement Compilation Time is usually a small part of the total running time. Because BI queries do not usually repeat, it does not benefit from dynamic statement caching.

If you need to use input variables in a complex query workload, consider using the REOPT (ALWAYS) BIND option. When the input variable value is known, the reopt bind option delays statement compilation from PREPARE to OPEN or during execution. The variable value is passed to the SQL Compiler, so that the optimizer can use these benefits to calculate a more accurate selection evaluation.

REOPT (ALWAYS) indicates that all execution statements should be precompiled. REOPT (ALWAYS) can also be used for complex queries involving special registers, such as "WHERE TRANS_DATE = current date-30 DAYS ". If the input variable causes poor access Plan Selection for the OLTP workload and the REOPT (ALWAYS) option causes excessive overhead due to statement compilation, use REOPT (ONCE) for the selected query ).

REOPT (ONCE) Delays statement compilation until the first data variable is bound. Use the first input variable value to compile and optimize the SQL statement. Subsequent statements run with different values will reuse the query segment compiled based on the first input. This is a good method. If the first input variable represents the subsequent input value and the input value is unknown, different values are used for evaluation than the optimizer, it provides a better query access plan