Thrashing and Query Execution

Adjust the HASH_THRASHING_PERCENT option to limit thrashing during queries that involve hash algorithms.

Adjusting the HASH_THRASHING_PERCENT database option controls the percentage of hard disk I/Os allowed before the statement is rolled back and an error is returned. The default value of HASH_THRASHING_PERCENT is 10%. Increasing HASH_THRASHING_PERCENT permits more paging to disk before a rollback and decreasing HASH_THRASHING_PERCENT permits less paging before a rollback.

Queries involving hash algorithms that executed in earlier versions of SAP Sybase IQ may now be rolled back when the default HASH_THRASHING_PERCENT limit is reached. SAP Sybase IQ reports the error

Hash insert thrashing detected

Hash find thrashing detected

Take one or more of the following actions to provide the query with the resources required for execution:

Relax the paging restriction by increasing the value of HASH_THRASHING_PERCENT.
Increase the size of the temporary cache. Keep in mind that increasing the size of the temporary cache requires an equal size reduction in main buffer cache allocation to prevent the possibility of system thrashing.
Attempt to identify and alleviate why SAP Sybase IQ is not estimating one or more hash sizes for this statement correctly. For example, check that all columns that need an LF or HG index have one. Also consider if a multicolumn index is appropriate.
Decrease the value of the database option HASH_PINNABLE_CACHE_PERCENT.

To identify possible problems with a query, generate a query plan by running the query with the temporary database options QUERY_PLAN='ON' and QUERY_DETAIL ='ON'. Examine the estimates in the query plan.