The most common cause of optimizer poor choice is inaccurate estimation of filtering.
Sometimes, it's due to insufficient statistics
Sometimes due to inability to use available statistics (SALES_TIMESTAMP BETWEEN ? AND ? - is it 0001-01-01 through 9999-12-31? or is 1 minute range, 1 day, week, month...)
Sometimes both.
So the first thing we recommend is to ensure sufficient statistics are collected.
I assume you'd be interested to a more advanced "deep analysis" of particular query and access path choice if that were available?
------------------------------
Patrick Bossman
------------------------------
Original Message:
Sent: Tue October 19, 2021 11:44 AM
From: Larry Jardine
Subject: Information Xchange - Query Tuning Question
We use query monitor to detect performance issues. Sometimes we find them in Performance testing (prior to Production) and sometimes when reacting to Production performance problems. We don't use the advisors... most of our problems are well-known issues with large tables, multiple joins, and several indexes to choose from. Getting the Optimizer to choose the right indexes and access the tables in the most optimal order requires some effort.
------------------------------
Larry Jardine
Original Message:
Sent: Tue October 19, 2021 11:33 AM
From: CALENE JANACEK
Subject: Information Xchange - Query Tuning Question
- Do you use query tuning capabilities like index advisor, query rewrite, and access path advisors?
When are you most likely to use query tuning capabilities?
- application development (new/change of application code)
- DBA review of application development change
- reactionary (performance problem in production or late phase testing)
------------------------------
CALENE JANACEK
------------------------------
#Db2Toolsforz/OS