Learning Visually with Examples [Electronic resources] نسخه متنی

16.12. Case Study

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

[View full width]
C:\temp>db2advis -d posdb -i bad.sql 
Using user id as default schema name. Use -n option to specify schema 
execution started at timestamp 2005-03-28-12.51.39.570001 
found [1] SQL statements from the input file 
Recommending indexes... 
total disk space needed for initial set [   0.009] MB 
total disk space constrained to         [  33.322] MB 
Trying variations of the solution set. 
Optimization finished. 
2  indexes in current solution 
[ 13.0000] timerons  (without recommendations) 
[  0.1983] timerons  (with current solution) 
[98.47%] improvement 
-- 
-- 
-- LIST OF RECOMMENDED INDEXES 
-- =========================== 
-- index[1],    0.009MB 
CREATE INDEX "DSNOW   "."IDX403281751440000" ON "DSNOW   "."ORGX" ("C1" ASC) ALLOW
 REVERSE SCANS ; 
COMMIT WORK ; 
RUNSTATS ON TABLE "DSNOW   "."ORGX" FOR INDEX "DSNOW   "."IDX403281751440000"; 
COMMIT WORK ; 
-- RECOMMENDED EXISTING INDEXES 
-- ============================ 
-- =========================== 
-- 
11 solutions were evaluated by the advisor 
DB2 Workload Performance Advisor tool is finished.