The Workload Table Organization Advisor recommends converting row-organized tables to
column-organized tables and creating shadow tables for row-organized tables that are used in
analytical queries.
About this task
The advisor examines the all of the tables that are referenced by the statements that are in a
query workload. The advisor presents its analysis and rationales so that you can see the tables that
are recommended for conversion, as well as those tables that are not. You can see the cardinalities
of the tables, how many statements in the query workload reference them, the cumulative cost of
running the statements that reference a table, the statements themselves, and more.
You can view and save the DDL for implementing the changes that are necessary to convert the
tables. You can even click a button to import the DDL into the Data Studio client's
Convert Tables to Column Organization feature, where you can edit the DDL,
and then run it or save it.
Tip: Run this advisor only after you find that recommendations from the Workload
Statistics Advisor, the Workload Index Advisor, or both do not improve performance enough. You might
be able to reach the required performance for the query workload by gathering statistics, creating
or modifying indexes, or both. Moreover, the Workload Table Organization Advisor relies on correct
statistics when it generates its recommendations.
Restriction: The advisor can recommend the creation of shadow MQTs only if the connected
DB2 for Linux, UNIX, and Windows database is at version 10.5.4.
Procedure
To generate and act on recommendations for BLU
Acceleration:
- Create a workload of the SQL statements that you want to tune
together.
- Capture the SQL statements that you want to tune. See Locations from which you can capture SQL statements
to create query workloads that run on DB2 for Linux, UNIX, and Windows.
- Click the Create Query Workload from All Statements button and then
give the query workload a name and description when you are prompted.
The
Manage and Tune Workloads page of the Manage section opens. The new query
workload appears in the table on this page. The status of the query workload is
CAPTURED.
- Run the Workload Statistics Advisor twice on the query
workload and implement its recommendations both times.
- In the Manage section of the workflow
assistant, select the query workload and click the Invoke
Advisors button.
- Optional: Set values for the preferences for
this advisor that override the global preferences. Select Set
Advisor Options on the left side of the Invoke section.
Then, select the Table Organization tab.
- On the left side of the workflow assistant, select Run
Workload Advisors.
- Select the option Re-collect EXPLAIN information
before running workload advisors.
- Click Select What to Run.
- Select Table Organization and click OK. The Review section of the workflow
assistant opens to the Review Workload Advisor Recommendations page.
If there are recommendations, the Table Organization tab
appears.
- Click the Table Organization tab.
Results
This
section displays the tables that are and are not recommended for migration to column organization,
as well as those tables for which shadow tables are recommended.
At the top of
the section is an estimation of the improvement to the performance of the entire query workload.
The advisor calculates the estimated performance improvement by
following these steps: - Assume that all of the recommended table conversions are performed, and that the indexes on
those tables are dropped.
- Find the product of the following two values for each statement in a query workload: Number of
executions * Statement cost
- Add the products together.
These columns are displayed in the table at the top of this section:
- Table
- The name of a row-organized table that is either recommended for conversion to column
organization or is recommended to remain as is.
- Creator
- The creator of the table.
- Current Organization
- Indicates whether the table is currently row-organized or column-organized.
- Recommended Organization
- Indicates whether the table is recommended for conversion to column organization, for a shadow
table to be defined on it, or to remain as is.
- Warning
- For tables that are recommended for conversion, describes changes that can occur as a result of
the conversion.
- A shadow table will be defined on the table
- The recommendation is to define a shadow table on the table to improve the performance of
analytic queries. A shadow table is an MQT that is maintained through replication.
- Data partitioning will be lost.
- You can restore data partitioning only if you convert the table back to row-organization.
- ENFORCED CHECK constraints will change to NOT ENFORCED.
- It will be possible for errant data to be inserted into the table.
- ENFORCED RI constraints will change to NOT ENFORCED.
- It will be possible for errant data to be inserted into the table.
- Indexes will be removed.
- Indexes are not allowed on column-organized tables.
- MQTs will be removed.
- Even after the removal of MQTs, SQL statements will be able to access data in the table
faster.
- User-maintained, deferred-refresh MQTs will be converted; any other MQTs will be dropped
- All user-maintained deferred-refresh MQTs that are defined on the table will be converted to
column-organization when the table is converted from row-organization to column-organization.
- Table space will be changed.
- After the conversion, the table will be in the table space that is specified in the preferences
for the Workload Table Organization Advisor or in a table space that the advisor selected. You can
modify the conversion script to use a different table space.
- Cardinality
- The number of rows that are in the table.
- References to Table
- The number of time that the SQL statements that are in the query workload reference the
table.
- Cumulative Total Cost
- The sum of the CPU costs in timerons of each access to data in the table by the SQL statements
that are in the query workload.
- Affected Statements
- The number of SQL statements in the query workload that reference the table.
- Finding
- Explains why the table is or is not recommended for conversion.
- Adding a shadow table can help analytical queries on the table run faster.
- Analytical queries against this table would be routed automatically to the shadow table to take
advantage of BLU Acceleration.
- Column-organization restrictions do not allow conversion.
- The following table types cannot be converted to column-organized tables: MQTs (other than
user-maintained, deferred-refresh MQTs), declared global temporary tables, created global temporary
tables.
There are other restrictions, too. For example, tables with LOB columns cannot be
converted, not can tables that are used in MERGE statements or in statements that declare cursors.
For the full set of restrictions, see the documentation for DB2 10.5 for Linux, UNIX, and Windows or
later.
- Conversion would improve performance.
- The performance of the query workload would improve if the table were converted to
column-organization.
- Conversion would not improve performance.
- The performance of the query workload would not improve if the table were converted to
column-organization.
- Missing statistics do not allow conversion.
- The table is missing statistics, so conversion cannot be done reliably. Run the Workload
Statistics Advisor to collect statistics for the query workload, run the RUNSTATS statements that
the advisor recommends, and then run the Workload Table Organization Advisor again.
- The table is already column-organized.
- No conversion is necessary.
- The table already has a shadow table.
- An MQT that is maintained by replication is already defined on this row-organized table.
These columns appear in the table SQL Statements
Affected. The table lists all of the SQL statements in the query workload that would be
affected by the conversion of the listed tables to column-organization.
- Execution Count
- The number of times that the SQL statement is run in the query workload.
- Weight
- The relative weight of the statement in the query workload. The value is calculated by
multiplying the value in the Cost Before column by the execution count, and
then dividing the product by the total "cost before" for the query workload.
- Estimated Performance Gain
- The estimated increase in the performance of the statement that accesses the selected table, if
the recommendation for conversion is implemented.
- For example, an estimated performance gain of 90.61% means that 90.61% less CPU time is required
for the statement to access the selected table.
- Cost Before
- The cost of the statement in timerons before the conversion of the referenced table to a
column-organized table.
- Cost After
- The cost of the statement in timerons after the conversion of the referenced table to a
column-organized table.
- SQL Statement
- The text of the statement. To see the full text of the statement, right-click the row and select
Show SQL Statement.
What to do next
Evaluate the estimated improvements in the performance of the query workload to find out whether
they might help you to meet your performance requirements. If you decide to follow the
recommendations, ensure that your database is backed up before you run the script that the advisor
generated. Also, be aware that referential integrity constraints on tables will be removed during
the conversion to column-organization.
If you follow any recommendations to create shadow tables, ensure that InfoSphere
Change Data Capture for DB2 for Linux, UNIX, and Windows is installed, an instance is created, and
that all shadow tables in your database are managed by a single subscription.
When you generate an access plan graph for any SQL statement that references a column-organized
table or shadow MQT, the graph will include an operator with the label
CTQ wherever there is a transition between column-organized processing and
row-organized processing. For more information about the CTQ operator, see New explain information for column-organized tables.