In most organizations that have upgraded to DB2 LUW version 10.5 with BLU Acceleration, discussions are underway about using column-oriented tables to help improve performance of large queries. Column-oriented tables are becoming commonplace in some shops, but maybe your DBA and development staffs are new to the concept. One of the challenges of taking advantage of new performance features built into V10.5 is to find those tables that might benefit from conversion from row-oriented to column-oriented organization. Some tools might help you.
What if your DB2 performance monitoring tool that is alerting you to problems on the DB2 database or instance, could help you spot good column-oriented table candidates? As key performance indicators are checked regularly (every few minutes, for example), it would be optimal and efficient if the tool could save some manual analysis time and simply automate the identification of those tables.
Quest’s Foglight does identify tables that are candidates for column-orientation. Here’s how:
- When you deploy a Foglight DB2 agent (remote collector), Foglight detects the DB2 version and certain 10.5 specific columns of information are collected at each monitoring interval.
- One such column is Number of Columns Accessed, a metric that Foglight stores for each of your top N accessed tables on a monitored database.
- The result will look something like this screen capture snippet (notice that all of these tables are currently R, or row, organization):
One of the tables on the Foglight dashboard snippet above shows a table with 84 columns, but the average SQL statement accessing that table only accesses 3 columns. Since that’s significantly lower than the number of columns in the table, and the table is heavily accessed, then perhaps you’ve found a table that could be changed to column organization. This organization change just might speed up some large important queries that use that table.
For more information, visit our Foglight for DB2 product page.
To ask questions or share feedback about the material in this post, please join us on the Foglight Community forum or reach out to me at tim.fritz@quest.com.
