This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).
Shape the future of IBM!
We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:
Search existing ideas
Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updateson them if they matter to you. If you can't find what you are looking for,
Post your ideas
Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,
Post an idea
Upvote ideas that matter most to you
Get feedback from the IBM team to refine your idea
Specific links you will want to bookmark for future use
Optimizer should account for bufferpool hit-ratio when estimated page access time.(RIMS)
In PMR 00962,7TD,000, Goldman Sachs had a scenario where optimizer was choosing a more expensive HSJN over a less expensive NLJN. Shu Lin summaries the problem well: 'In terms of CPU cost and number of IOs, we correctly estimate that NLJN is better. We end up not favoring NLJN because we estimate NLJN requires a lot more seeks than HSJN. We prefetch NLJN outer (table scan) but not inner (ixscan-fetch) because we estimate the access patten on inner is random: the index has low clustering ratio. That is, for every inner page accessed, we estimate a seek. For HSJN, we prefetch both the probe side (list prefetch) and the build side (table scan). The number of seeks is (the number of page accessed) / (block_size). block_size is calculated based on prefetch size and extent size. As such, NLJN requires more seeks than HSJN, which drives up its total cost, and make it not favorable. In reality, the tables are cached in bufferpool, these seeks don't happen. That is why NLJN runs faster, which is consistent with our estimation (recall that in terms of CPU cost and number of IOs, NLJN is better). Shu Lin also recommend a method to get the optimizer to not estimate seeks: 'Regarding if there is a switch to get optimizer to not estimate seeks... We can alter tablespace overhead to some low value. Since we compute seeking cost based on overhead, a smaller overhead reduces our seeking cost estimation. However, if overhead is set too low, it can cause optimizer to pick a really bad plan if the pages indeed need to be read from disks. If you want to adjust overhead setting, it is recommended you test it out on your test system in a controlled manner. e.g. move selected tables to a dedicated tablespace, and alter overhead of that tablespace.' However, since Goldman deploys hundreds of instances, and queries are written by tens of thousands of app developers, performing any kind of hand-tuning of queries is always difficult. Goldman DBAs often push for DB2 deployments over other vendors because the DB2 optimizer requires less optimizer-hints versus other vendors, so reducing the total cost of ownership in this regard is a strategic advantage for DB2. We'd like to propose that Optimizer take into account the bufferpool hit ratio. Ideally automatically, but a config/registry option might also make sense if it is granular enough. This is the basis of the new requirement.
Do not place IBM confidential, company confidential, or personal information into any field.