Section 16.12. Case Study
16.12. Case StudyIn this case study assume that your database POSDB is supporting a point-of-sale system. Since you will be processing simple, single row selects, updates, and deletes, do not enable intra-partition parallelism. Check the INTRA_PARALLEL instance-level parameter to ensure it is disabled.On Linux and UNIX you can use the grep tool to retrieve the intra-partition parallelism line as follows: get dbm cfg | grep I intra_parallel The -I option after the grep command is used to ignore the case. NOTE On Windows you may need to parse the output manually. You can page through the output or redirect it to a file so you can search the file for the INTRA_PARALLEL string if needed. If intra-partition parallelism is enabled, disable it using update dbm cfg using intra_parallel off Although you know there are 100 cash registers in your store, you do not know how many are normally active at the same time. To determine this information, capture some database manager snapshots over a period of time using the following statement: SELECT rem_cons_in_exec , local_cons_in_exec, (rem_cons_in_exec + local_cons_in_exec) as total_executing FROM TABLE(SNAPSHOT_DBM(-1 )) as SNAPSHOT_DBM; After capturing these snapshots over a period of time, calculate the average for the total_executing column in the output. If this average turns out to be 17, you can set the average number of active applications for your database to 17: update db cfg for POSDB using avg_appls 17 You then notice that the performance of the system seems to slow down when there are a number of users using the application. Take a snapshot of the important performance related information using this statement: SELECT db_name, rows_read, rows_selected, lock_waits, lock_wait_time, deadlocks, lock_escals, total_sorts, total_sort_overflows FROM table (snapshot_database ('POSDB ', -1) ) as snapshot_database If this statement shows that a large percentage of the sorts are causing sort overflows, you need to examine the setting for your sort heap and sort heap threshold. Since intra-partition parallelism is disabled, there is no need to worry about the sort heap threshold for shared sorts. get db cfg for posdb | grep I sort get dbm cfg | grep I sort From the output of the above commands, look at the following lines in particular: Sort list heap (4KB) (SORTHEAP) = 256 Sort heap threshold (4KB) (SHEAPTHRES) = 1000 In this case you can see that the sort heap threshold is less than four times the value of the sort heap, so if there are more than three concurrent sorts, any subsequent sorts will have their sort heap reduced and are much more likely to overflow. Since the average number of concurrently executing applications you found earlier was 17, you should set the sort heap threshold to at least 17 times the sort heap. In this case you can choose 20 times the sort heap for ease of calculation (20 x 256 = 5120). update dbm cfg using sheapthres 5120 Assume that you then retest the application and recapture the snapshots. In the snapshot you see that this did improve the percentage of overflowed sorts, but the percentage is still too high. Therefore, the sort heap itself is likely too small for the amount of data that is being sorted. If you then increase the sort heap, you should also increase the sort heap threshold accordingly to keep it at 20 times the sort heap. NOTE Having an excessively large sort heap makes sorts cost less to the DB2 optimizer, so do not increase the sort heap too much. Make this change iteratively, increasing the sort heap and sort heap threshold by small increments until you see the desired change in the percentage of overflow sorts and performance. update db cfg for posdb using sortheap 400 update dbm cfg using sheapthres 8000 After retesting and recapturing the snapshots, you see that although this has improved the overall performance of your server, one of your applications still appears to be sluggish. Since this is specific to one application, it may be caused by poorly performing statements within the application. If the application is an embedded static SQL application, you can get the statements from your developers. If it is a dynamic SQL application, you can capture the SQL statements using the Snapshot Monitor or the Event Monitor. You can run the application and then examine the performance of the SQL statements: SELECT (case when num_executions >0 then (rows_read / num_executions) else 0 end) as avg_rows_read, (case when num_executions >0 then (rows_written / num_executions) else 0 end) as avg_rows_written, (case when num_executions >0 then (stmt_sorts / num_executions) else 0 end) as avg_sorts, (case when num_executions >0 then (total_exec_time / num_executions) else 0 end) as avg_exec_time, substr(stmt_text,1,200) as SQL_Stmt FROM table (snapshot_dyn_sql ('sample', -1) ) as snapshot_dyn_sql If you notice that there is one particular statement in the output of this SQL that has a long average execution time and performs three sorts per execution, you can use the Design Advisor to help tune this statement. If you extract the statement text from the output above, and put it into the file bad.sql, you can run the Design Advisor from the command line using: db2advis d posdb i bad.sql If an index will help the performance of the query, the Index Advisor will tell you the definition of the index or indexes it recommends, as well as the new cost of the query and the percent improvement in the cost.
You can run the create index and runstats statements from the Design Advisor output, and rerun your tests to make sure that this does improve you application's performance. |
REVERSE SCANS ;
EAN: 2147483647
Pages: 313