After upgrade to Istanbul some customers have experienced query hints not being applied (that had previously been added to improve performance of specific queries. There is a mismatch in the query hash value for the example queries that are part of the 'Active Query Index Hints' causing the index hint to be ignored.
What symptoms will be experienced?
Queries that used to run fast will now be significantly slower.  The impact will vary on the specific queries that are no longer having their index hints applied as well as the frequency of which they run.

Steps to Reproduce


  1. 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:

  1. Download the PRB1058404_recalculate_all_query_hashes.js attachment
  2. For a test run (will NOT update anything) run the script in Scripts Background, as is(dryRun = true)
  3. 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

Seen In

There is no data to report.

Fixed In

Helsinki Patch 12
Istanbul Patch 9
Jakarta Patch 2

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-03-16 00:13:00