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.

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.

					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.

					C:	emp>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.
				

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.