Click on the action buttons in the action panel to get the query execution times for tables with and without MDC and generate a graph comparing the same

Note :

Results may vary depending on the volume of data. For optimal performance, use high volume of data.

To increase the volume of data, load the fact tables 'te_temp.subscriber_rated_usage' and 'te_temp.subscriber_dedicated_account_usage' with the corresponding .del files present in the LoadFiles directory.

Operation



Get Query Execution Times

Query execution times for tables enabled with DPF + Indexes + RP + MDC, DPF + Indexes + RP, DPF + Indexes, DPF + Indexes and DPF are computed

Performance Comparison

  1. A graph comparing the query execution time of DPF + Indexes + RP + MDC tables versus DPF + Indexes + RP tables versus DPF + Indexes versus DPF + Indexes versus DPF tables is generated.
  2. If you hover your cursor over the bars in the graph you will see the exact execution time for each run.


Result


The query execution time would reduce with the creation of a MDC by clustering data across multiple dimensions

Note :

The graph here might show an increase in the query execution time with MDC. This is because the amount of data in the tables is very low. This causes the MDC blocks to be inadequately occupied. Also, the cost of retrieval of the data from these blocks is not compensated by the amount of data.

In a typical data warehouse environment consisting of millions of rows of data, the MDC would show a great reduction in execution time

Explain Snapshot


An explain snapshot is compressed information that is collected when an SQL statement is explained. It contains

  1. The internal representation of the access plan (graphical as well), including its operators and the tables and indexes accessed
  2. The decision criteria used by the optimizer, including statistics for database objects and the cumulative cost for each operation.

Run the following steps on the DB2 CLP window to view the explain snapshot :

  1. Change to directory DB2_install_directory\sqllib\misc
  2. Connect to the database as db2 connect to designdb.
  3. Run db2 -tvf EXPLAIN.DDL. This creates the explain tables.
  4. Run db2 SET CURRENT EXPLAIN MODE=YES
  5. Go the Ad Hoc SQL tab, Select Options. Note the Default Schema used for this workshop.
  6. Change to directory TE_install_directory\tutorials\Workshops\DesignforQueryPerformance\MultidimensionalClustering
  7. In the file query_MDC.db2 replace 'SCHEMA' with the Default Schema.
  8. Run db2 -tvf query_MDC.db2
  9. Run db2 SET CURRENT EXPLAIN MODE=NO
  10. Run db2exfmt -d designdb -1 -o explain_MDC.out. The Explain Plan can be viewed in the output file explain_MDC.out.


Click here to view the sample output of the Explain Access Plan : Sample Explain Access Plan