Steps to Reproduce
- Create a new index hint query rewrite for a slow query and activate
2. Run the transactions associated to the query on which the index hint query rewrite was created. The performance of the query will be enhanced.
3. Upgrade the instance to Istanbul
4. Navigate to the module 'Active Query Index Hints', the index hint query rewrite that was created in step 1 will be present and active,
5. Run the same transactions from Step 2. The transactions take the same amount of time that were used to be before applying the index hint query rewrite. If a slow query is logged you will notice that it now has a different hash value as well from what is referenced in the Index Hint.
IMPORTANT: This may occur after upgrade to Helsinki+ as the hashing algorithm was changed. If you are planning to upgrade (or recently upgraded) make sure to run the workaround after to ensure all query re-writes are functioning as expected
To fix the mismatched hash values:
- Download the PRB1058404_recalculate_all_query_hashes.js attachment
- For a test run (will NOT update anything) run the script in Scripts Background, as is(dryRun = true)
- Once you have confirmed those are the hash values you need to update, set the "dryRun" variable to false and run the script again
Output from script similar to:
*** Script: Updating sys_query_pattern.33b83478136b7200efffb6004244b051 - old hash: -767213504 | new hash: 1079938139
*** Script: Updating sys_query_pattern.9299c43413e37200efffb6004244b036 - old hash: -56800851 | new hash: 1782245771
*** Script: Updating sys_query_pattern.a3ba007413e37200efffb6004244b059 - old hash: 983610782 | new hash: 424558347
*** Script: Updating sys_query_pattern.db13b5bc1363b200efffb6004244b0d9 - old hash: 83569871 | new hash: -685981076
*** Script: Table sys_query_category does not exist - skipping
*** Script: Flushing query rewrite provider cache
*** Script: Flushing query category reroute cache
For any questions or concerns running this workaround please contact Customer Support.
Related Problem: PRB1058404